Alasql: LS DB, CURRENT_TIMESTAMP after refresh?

[jdtechsol]

Note: since @mathiasrw confirmed this as being a bug, I have modified the contents of this issue post to reflect that (before, I posted this as the question "am I doing something wrong?")

Note: a possible fix has been found, see the end of this issue's body.

The LOCALSTORAGE engine does not store the CURRENT_TIMESTAMP function in the localStorage object for the table (or anywhere in LS for that matter).
[Steps to Reproduce Bug]

//Create the DATABASE 'MyDB' and add the TABLE 'myTable' to it
alasql('CREATE LOCALSTORAGE DATABASE IF NOT EXISTS MyDB');
alasql('ATTACH LOCALSTORAGE DATABASE MyDB');
alasql('USE MyDB'); 
alasql('CREATE TABLE IF NOT EXISTS myTable (id INT(10) AUTO_INCREMENT, val VARCHAR(30), tstamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)');
alasql('INSERT INTO myTable (val) VALUES("test")');

myDB.myTable's data section would be as follows (the date will be different, obviously):

0: {tstamp: "2019.01.07 08:41:39.755", val: "test", id: 1}

reload the page, and then do this...

//Reattach the LS DB 'MyDB', set it as the active DATABASE, and insert another row. 
alasql('ATTACH localStorage DATABASE MyDB');
alasql('USE MyDB'); 
alasql('INSERT INTO myTable (val) VALUES("test 2")');

localStorage myDB.myTable's data section would now be as follows:

0: {tstamp: "2019.01.07 08:41:39.755", val: "test", id: 1}
1: {val: "test 2", id: 5}

UPDATE: Upon further investigation, if you carry out the first part and in addition to inserting one row, you did that insert again, you'll find that the second entry does not contain a timestamp. I assume that this is linked to main issue.

[UPDATE 1/9/19 @ 1:34 AM CST]
A possible fix has been found. I re-downloaded the develop branch of alasql so I had a fresh copy. A single line of code was added to the file ./dist/alasql.js,, to the function "LS.storeTable". The line added has the comment "<--- The Fix for LS defaultfns" . Shown below is the function as a whole.

LS.storeTable = function(databaseid, tableid) {
    var db = alasql.databases[databaseid];
    var table = db.tables[tableid];
    // Create empty structure for table
    var tbl = {};
    tbl.columns = table.columns;
    tbl.defaultfns = table.defaultfns; //<---The Fixf for LS defaultfns
    tbl.data = table.data;
    tbl.identities = table.identities;
    // TODO: May be add indexes, objects and other fields?
    LS.set(db.lsdbid + '.' + tableid, tbl);
};

It appears that everything else is already in place. Upon table creation, the following line can be found in the localStorage key "MyDB.myTable":

defaultfns: "'tstamp':alasql.stdfn.CURRENT_TIMESTAMP()"

which also appears in...

alasql.databases["MyDB"].tables["myTable"].defaultfns

as....

"'tstamp':alasql.stdfn.CURRENT_TIMESTAMP()"

which results in, with my example, the CURRENT_TIMESTAMP function executing as expected. The defaultfns entry in the alasql object survives usage of the insert command and I have yet to come across a situation where this fix fails. This also survives page refresh / reload.

Full disclosure: I am... at best, a hobbyist in terms of how I define myself as a programmer and web page designer. So, I am unfamiliar with what it will require to verify what I believe to be the "fix" for this issue or how to properly implement it in the source code. I'm sure someone is willing to walk me through the process.

So, how do we go about implementing this fix?

[UPDATE 1/9/19 @ 4:52 AM CST]
An issue still exists that happens after a reload. It results in DEFAULT and CURRENT_TIMESTAMP values not being set if INSERT INTO is the FIRST statement after the ATTACH & USE statements. If executed in this manner, any INSERT INTO's that come after it (in a different alasql call, I imagine), will work fine. A workaround would be to fire off a SELECT FROM on the table so that the table object for that table gets populated. This only needs to happen once.

[jdtechsol]

I have found a workaround..
First, do a SELECT FROM myTable (what data you grab is not important, so as long as it is a valid SELECT FROM).
Second, using the example as a base,

alasql.databases["MyDB"].tables["myTable"].defaultfns = "'tstamp':alasql.stdfn.CURRENT_TIMESTAMP()";

And then it will do what it is supposed to from that point on (again, this wont survive a reload/refresh).
CORRECTION: NO, it will not. It will work once and you'll need execute the code above, again.

As a note, how I came across this issue was: I noticed there was no way to dump an SQL file which could be used to backup (and then restore) the database. I started writing my own, which took the LS data and converted it. I noticed though that I had no DEFAULT and no CURRENT_TIMESTAMP in the output. So I started looking into it, and here we are.

To anyone wondering why I'm not trying to get my "SQL Dumper" code committed, it is because it is very simplistic and works for my situation, at best (it doesnt even use alasql).

[jdtechsol]

I believe to have found a fix. I updated the body of the issue.

[jdtechsol]

Possible issues have been found with the fix. What _was_ working, now seems to not. The fix was first verified on local mod of alasql. I need to check some things.

[jdtechsol]

Following a reload, and following attach and use, if the next alasql command is an INSERT, it will not carry out the function call for CURRENT_TIMESTAMP (or set a default value if one exists). I tried tracking this down but stepping through code execution gets very hairy. What I do know is that S.intoTable gets sent the values which includes the timestamp. any inserts that follow this will work fine. What needs to happen is alasql.databases["MyDB"].tables["myTable"] needs to receive it's alasql.Table value BEFORE the alasql("INSERT INTO... gets executed. My workaround for this is going to be calling a SELECT FROM on the table before any other statements (just once).

[mathiasrw]

Thank you so much for looking into this. Very valuable information.

Im on vacation so will not be able to look into this the next 3 weeks!

[jdtechsol]

Have fun. I'm done with this project. For the last few weeks I've ran into issue after issue after issue. I'd love to stay and help but at the moment, I need a solution, not a task.

[mathiasrw]

Hi @jdtechsol

Thank you so much for looking into this and trying to improve on the beast. Sorry the code is not in a better state.

[jdtechsol]

I apologize. I'm back. I needed a break.