Alasql: DELETE FROM table WHERE error

[nbdamian]

Using FileStorage and WebWorker.
Running the query "DELETE FROM table WHERE column = 'value'"
I get the error:

Uncaught TypeError: Cannot read property 'length' of null(…)
statement   @   alasql.js:12750
alasql.dexec    @   alasql.js:4249
alasql.exec @   alasql.js:4231
alasql  @   alasql.js:120
(anonymous function)    @   VM197:2
InjectedScript._evaluateOn  @   VM144:904
InjectedScript._evaluateAndWrap @   VM144:837
InjectedScript.evaluate @   VM144:693

Subtract 2 from any line after 12747 (I had 2 console.log() for testing).

Code to replicate:

alasql("CREATE TABLE IF NOT EXISTS `preferences` (`id` int(11) NOT NULL, `tab` varchar(45) DEFAULT NULL, `tab_id` int(11))", [], function() {
alasql("INSERT INTO `preferences` (`id`, `tab`, `tab_id`) VALUES (1, 'where', 1), (2, 'when', 8)", [], function() {
alasql('DELETE FROM preferences WHERE tab = "where"')
});
}); 

According to console logs,
console.log(table); has no object of data
console.log(table.data); returns undefined.

[nbdamian]

The issue is to do with FileStorage. (if I don't use FileStorage it works)

[nbdamian]

Updated error with version v0.2.4-develop-1235:

Uncaught TypeError: Cannot read property 'length' of undefined(…)
statement @ alasql.js:12749
alasql.dexec @ alasql.js:4249
alasql.exec @ alasql.js:4231
alasql @ alasql.js:120
(anonymous function) @ VM157:4
FS.intoTable @ alasql.js:16806
statement @ alasql.js:12565
alasql.dexec @ alasql.js:4249
alasql.exec @ alasql.js:4231
alasql @ alasql.js:120
(anonymous function) @ VM157:3
yy.CreateTable.execute @ alasql.js:10732
alasql.dexec @ alasql.js:4286
alasql.exec @ alasql.js:4231
alasql @ alasql.js:120
(anonymous function) @ VM157:2
InjectedScript._evaluateOn @ VM149:904
InjectedScript._evaluateAndWrap @ VM149:837
InjectedScript.evaluate @ VM149:693

[mathiasrw]

interesting. Ill have a look

[mathiasrw]

I just realised you say you are using filestorage from webworker.

Does it change anything if you do not use webworker?

---

update:

looks like something is running async but treated like sync:

var table = db.tables[tableid];
var orignum = table.data.length;

The second line has the problem. Table.data does not exists (yet). I have to dig further...

[nbdamian]

Sorry, I'm not running webworker (I think it was late when I stopped using the webworker, so that's a fail on my behalf sorry).
It seems this async issue is in quite a lot of places, my issue of #596 most likely has the same problem.

[mathiasrw]

I have the same feeling: that something in the logic of how filestorage is implemented is misfittting the async nature.

I have a feeling we need to get @agershun to have a look at it when he has the time.

[mathiasrw]

Does it work better if you do:

alasql.promise('CREATE TABLE IF NOT EXISTS `preferences` (`id` int(11) NOT NULL, `tab` varchar(45) DEFAULT NULL, `tab_id` int(11))')
      .then(function(res){
            return alasql.promise('INSERT INTO `preferences` (`id`, `tab`, `tab_id`) VALUES (1, 'where', 1), (2, 'when', 8)')
      }).then(function(res){
            return alasql.promise('DELETE FROM preferences WHERE tab = "where"')
      }).then(function(res){
            console.log('All done');
      }).catch(function(reason){
            console.log('Error: ', reason);
      })

?

[nbdamian]

alasql.promise('CREATE TABLE IF NOT EXISTS `preferences` (`id` int(11) NOT NULL, `tab` varchar(45) DEFAULT NULL, `tab_id` int(11))')
      .then(function(res){
            return alasql.promise('INSERT INTO `preferences` (`id`, `tab`, `tab_id`) VALUES (1, "where", 1), (2, "when", 8)')
      }).then(function(res){
            return alasql.promise('DELETE FROM preferences WHERE tab = "where"')
      }).then(function(res){
            console.log('All done');
      }).catch(function(reason){
            console.log('Error: ', reason);
      })

Returns:

Uncaught TypeError: Promise is not a function(…)
alasql.promise @ alasql.js:4423
(anonymous function) @ VM153:2
InjectedScript._evaluateOn @ VM145:904
InjectedScript._evaluateAndWrap @ VM145:837
InjectedScript.evaluate @ VM145:693

Though that is strange since it is not throwing the
'Please include a Promise library' Error.

Even with:
https://www.promisejs.org/

is still returns the error.

[mathiasrw]

Ok. to sum up the issue - The following works:

alasql.promise([
    'CREATE FILESTORAGE DATABASE IF NOT EXISTS test1("./test1.json")', 
    'ATTACH FILESTORAGE DATABASE test1("./test1.json")', 
    'USE test1', 
    'CREATE TABLE IF NOT EXISTS `preferences` (`id` int(11) NOT NULL, `tab` varchar(45) DEFAULT NULL, `tab_id` int(11))', 
    'INSERT INTO `preferences` (`id`, `tab`, `tab_id`) VALUES (1, "where", 1), (2, "when", 8)',
    //'DELETE FROM preferences WHERE id = 1',    
    'SELECT * FROM preferences'    
]).then(function(res){
    console.log('Result from last query:',res)
}).catch(function(reason){
    console.trace(reason)
})

but if we uncomment the DELETE FROM preferences WHERE id = 1 part we get an error: TypeError: Cannot read property 'length' of undefined

Any inputs from @agershun ?

[nbdamian]

Well, No... I haven't tested with your latest update, but I am unable to use Promise at all, it returns that Promise is not a function, even using a promise library.

[mathiasrw]

Interesting. What is your setup? browser? Cordova? version?

[nbdamian]

Cordova running Crosswalk12

[mathiasrw]

Aw... im not in the Cordova world yet...

[nbdamian]

Ok, With a promise library working (Whooop).
Your code works with no errors.

[mathiasrw]

Nice!!!

[nbdamian]

Sorry but this still isn't resolved. You had the DELETE FROM commented out.

[mathiasrw]

Wups...

[nbdamian]

I was scanning through 72delete.js and 94filestorage.js and from the looks of it there is no direct check in 72delete.js that handles filestorage in the where block.
I will do some testing tomorrow if I find the time, but should FileStorage have something like
deleteFromTable(databaseid, tableid, wherefn, params, cb) instead of running the default?

[mathiasrw]

no direct check in 72delete.js that handles filestorage in the where block

nicely spottet

should FileStorage have something like
deleteFromTable(databaseid, tableid, wherefn, params, cb) instead of running the default

I have a feeling that data operations are done in mem and then pushed to the file like https://github.com/agershun/alasql/blob/2ee7f79c28a89a6342badee875376232f9d2aa5d/src/94filestorage.js#L197 - but I must admit I dont know how its structured

[nbdamian]

I will run through the code some more today.
so far, I have found that when using filestorage (I don't know if this is the same in other engines), but the data is not stored in the db.tables[tableid] object.

I believe that the db is not being read correctly, so db.tables[tableid].data is undefined (where alasql is checking for the item to delete)

[umasudhan]

The table was not populated correctly in the case of filestorage- the attached patch fixes it

load_table_correctly_in_case_of_filestorage.patch.txt

[mathiasrw]

OMG - how embarrassing... thank you!