Saturday, 22 April 2017

WebSQL bulk insert

I've been playing with WebSQL of late (after all, why wouldn't you play with a deprecated standard?). And I'm loving it for inserting data via Papa Parse, the thing is, it was proving to be really, really slow... right up until I clocked that I was doing it wrong!

The thing is, I was iterating over my data and firing a transaction with an SQL payload for each row of the CSV file I was parsing where I should've been firing a transaction and then iterating over my data and firing off multiple SQL payloads within the single transaction.

This was my first effort:

for (let record of results.data) {
    mydb.transaction(
        t => {
            t.executeSql(`
                INSERT INTO
                  accounts (
                    Date,
                    Company,
                    Account,
                    Amount
                  )
                VALUES (
                  ?,
                  ?,
                  ?,
                  ?
                )`,
                [
                    record.Date,
                    record.Company,
                    record.Account,
                    record.Amount
                ],
                t => {
                    let query = "SELECT COUNT(*) AS total FROM accounts";
                    t.executeSql(
                        query,
                        null,
                        (t, result) => {
                            if (~~result.rows[0].total === tableMetaData.total) {
                                createTable();
                            }
                        },
                        null
                    );
                },
                null
            );
        }
    );
}

Pretty, eh? But it took silly amounts of time to execute and I was planning on inserting 250,000 records. It simply wasn't practical to do it that way as it took something like 20 seconds to insert 1000 rows:

RowsTime
100.218
1001.711
100021.111
500090.703

Changing the code to this:

mydb.transaction(t => {
    for (let record of results.data) {
            t.executeSql(`
                INSERT INTO
                  accounts (
                    Date,
                    Company,
                    Account,
                    Amount
                  )
                VALUES (
                  ?,
                  ?,
                  ?,
                  ?
                )`,
                [
                    record.Date,
                    record.Company,
                    record.Account,
                    record.Amount
                ],
                null,
                null
            );
        }
    },
    error => {
        console.log("error", error);
    },
    () => {
        createTable();
    }
);
Produced these results:
RowsTime
100.081
1000.058
10000.083
50000.223

Not only quicker but I've also halved the number of queries as I don't need to check that all the transactions have completed after each insert, sweet eh? After figuring this out I came across a number of posts where the same thing had been discovered by others. Including RICAL and Gavin Pickin. Just a shame it's deprecated.

Thursday, 13 April 2017

JavaScript and checking for 0

A friend and colleague who's new to the joys of JavaScript asked about checking for a value which is zero and I automatically suggested the old double tilde trick.

I worked up a JSFiddle to illustrate the concept:

let myArray = [
    0, "0", '0', [0],["0"],['0'], 
    1, "1", '1', [1],["1"],['1']
];

for (let value of myArray) {
    console.log(value, typeof value)
    console.info(
        "~~" + value + " === 0:",
        ~~value === 0);
}

But then I got to thinking about what else the value might be, like an object, string or an array of strings: {"name":"Fred"}, "Fred", 'Fred', [{"name":"Fred"},{"name":"Fred"}], ["Fred"], ['Fred'].

These, oddly, also equated to true when compared to 0. So I started thinking about checking the type and doing something interesting (complicated) but then I remembered the toString() method and all the issues I've had in the past with seeing [Object, Object] in pages I've been working on.

Perhaps I could use that and reverse the solution?

So instead of checking for 0 as a number I'd instead check for it as a string. This, then, is a solution that seems to work quite well:

let myArray = [
    0, "0", '0', [0], ["0"], ['0'], 
    1, "1", '1', [1], ["1"], ['1'], 
    {"name":"Fred"}, "Fred", 'Fred', [{"name":"Fred"},{"name":"Fred"}], ["Fred"], ['Fred']
];

for (let value of myArray) {
    console.log(value, typeof value)
    console.info(
        value + ".toString() === '0':", 
        value.toString() === '0');
}

Hope that helps someone, it's something I'll keep in mind in future!

Monday, 3 April 2017

Avoiding Uncaught TypeError: Cannot read property '*' of undefined in JavaScript

I've lost count of the number of times I've run into issues diving into a deep JSON structure only to be confronted with the error TypeError: Cannot read property '*' of undefined. It's one of my major bugbears, and I've spent so many hours staring at it that I've finally come up with a way of avoiding it, the thing is, it uses eval...

I'm pretty sure that this use isn't overly evil but I'm not 100% sure TBH

Let me illustrate my use case and solution. Given this JavaScript object:

let obj = {
    "one": 1,
    "two": 2,
    "three": {
        "threeOne": 31,
        "threeTwo": 32
    },
    "four": 4
};

We can interrogate it in all sorts of ways:

console.log("obj:", obj) // Object {one: 1, two: 2, three: Object, four: 4}
console.log("obj.three.threeOne === 31:", obj.three.threeOne === 31); // true
console.log("obj.three.threeTwo === 31:", obj.three.threeTwo === 31); // false
console.log("obj.three.threeThree === 31:", obj.three.threeThree === 33); // false
console.log("obj.five === 31:", obj.five === 31); // false
console.log("obj.five:", obj.five); // undefined

But if we start digging a little deeper we run into trouble:

console.log("obj.five.fiveOne === 51:", obj.five.fiveOne === 51); 
// EXCEPTION: Uncaught TypeError: Cannot read property 'fiveOne' of undefined
console.log("obj.five.fiveOne:", obj.five.fiveOne); 
// EXCEPTION: Uncaught TypeError: Cannot read property 'fiveOne' of undefined

Except that's not 100% accurate as the browser throws its hands up after the first error and the second won't be shown as all bets are off!

It's not the fact that we're asking about an undefined thing but that we're asking about a property of an undefined thing. Should this then mean that we should check to see if the parent element has such a property before interrogating it, like this?

if(obj.hasOwnProperty("five") && obj.five.hasOwnProperty("fiveOne") && obj.five.fiveOne === 51){
    console.log("obj.five.fiveOne === 51:", true); 
}else{
    console.log("obj.five.fiveOne === 51:", false);
} // false
if(obj.hasOwnProperty("five") && obj.five.hasOwnProperty("fiveOne") ){
    console.log("obj.five.fiveOne:", obj.five.fiveOne);
}else{
    console.log("obj.five.fiveOne:", undefined);
} // undefined

This works though, depending upon the depth of the object this can get really ugly really fast, perhaps we should take advantage of the errors and do something with them. This we can do using a try...catch::

try {
    console.log("obj.five.fiveOne === 51:", obj.five.fiveOne === 51);
} catch (ex) {
    console.log("obj.five.fiveOne === 51:", false);
} // false
try {
    console.log("obj.five.fiveOne:", obj.five.fiveOne);
} catch (ex) {
    console.log("obj.five.fiveOne:", undefined);
} // undefined

This works, and it works well, but it is almost as ugly! To tidy it up I started writing a simple function but soon ran into a problem where, if I passed something which didn't have the property then the call to the function failed and caused a similar error. Then I clocked I could use a string and the eval function within a try...catch to do the work, I also added in some default checking of the value for my particular use case, but this is not necessary really:

let testObject = (objPath, val) => {
    val = typeof val !== 'undefined' ? val : "Completed";
    var result = false;
    try {
        result = (eval(objPath) === val) ? true : false;
    } catch (e) {}
    return result;
};

The above testing can then be replaced with this:

console.log("testObject('obj.three.threeOne', 31):", testObject('obj.three.threeOne', 31)); // true
console.log("testObject('obj.three.threeTwo', 31):", testObject('obj.three.threeTwo', 31)); // false
console.log("testObject('obj.three.threeThree', 31):", testObject('obj.three.threeThree', 31)); // false
console.log("testObject('obj.five', 31):", testObject('obj.five', 31)); // false
console.log("testObject('obj.five.fiveOne', 51):", testObject('obj.five.fiveOne', 51)); // false
console.log("testObject('obj.five.fiveOne'):", testObject('obj.five.fiveOne')); // false

This is already helping me; I hope you might find it useful as well. Here's a working JSFiddle with the code. I've made use of the marvellous console-log-div to output the results to a div. Otherwise, you'd need to open the DevTools on the JSFiddle to see the results. Hope it helps someone, and if you have any comments, please do chip in below.