Selecting from a table immediately out of a create table transaction

[ghominejad]

After creating a table i must wait a few milliseconds until transaction done otherwise i can't use db tables out of this transaction.

db.init(); // makes db schema if not exist
db.checkSomething(); // i use a table and encounter with txLock error

Transaction error :
"db":{"openargs":{"name":"mydb"},"dbname":"mydb"},"txlock":true,"readOnly":false,"executes":[]}
I think a sync transaction is very useful.

db.transactionSync(function(){
    db.executeSql('CREATE TABLE IF NOT EXISTS tbl_settings (key text primary key, data blob)'); 
});

Then we can use async transactions immediately after that :

db.transaction(function(tx){
    tx.executeSql('select data from tbl_settings....')
});

[ghominejad]

exec function waits until initialization is done. also using queries is simpler via exec function

    var ready = false;

    function exec(command, vals, callback, errCallback) {
        if (Object.prototype.toString.call(vals) === "[object Function]") {
            errCallback = callback;
            callback = vals;
            vals = undefined;
        }

        if (!ready) {
            setTimeout(function (){
                exec(command, vals, callback);
            }, 100);
        } else  {
            db.transaction(function (tx) {
                tx.executeSql(command, vals, callback);
            }, errCallback);    
        }
    }

    function dbInit() {
        db = openDatabase();
        db.transaction(function (tx) {
            db.executeSql('CREATE TABLE IF NOT EXISTS tbl_settings (key text primary key, data text)'); 
            ready = true;
        });
    }

    dbInit();
    // here we can't use tbl_settings via db.transaction

    // exec waits until create table transaction is done
    exec('select * from tbl_settings where key like ?;', ['key1'], function (tx, res) {
        // callback(null, res.rows);
    });

[brodybits]

The right thing to do workaround is: wait for the success callback from the sqlitePlugin.openDatabase function. This needs to be documented. Whether or not this issue shows up is due to timing.

[brodybits]

This issue also shows a deviation from how the Web SQL API works. The CoffeeScript/Javascript should be updated to wait until the database is actually opened before attempting to post the transaction.

[brodybits]

To fix this issue in CoffeeScript/Javascript will take some restructuring. I am planning to work on this in the next 1-2 weeks or so.

[ghominejad]

Thank you very much, but what about ddl queries (create/alter, ...)? after database ready we have to wait until these queries become complete. i think its good idea we have two type of commands, first ddl commands that are executed normal and other queries that should honor ddl commands completion.

function onDatabaseOpened() {
     if (!isDatabaseConfigured) {
         executeSql('create table...'); // this is ddl command and runs as normal
     }
}
// other section of the app that runs on application startup
executeSql('select * from ...'); // a ddl command is running so this should wait until ddl command is done

[brodybits]

Thank you very much, but what about ddl queries (create/alter, ...)? after
database ready we have to wait until these queries become complete. i think
its good idea we have two type of commands, first ddl commands that are
executed normal and other queries that should honor ddl commands

@ghominejad it is a nice idea but I would not bother with it.

My idea is that all db operations should be queued until it is possible to execute them.

It should just work, like it does when you open a normal Web SQL database!

[ghominejad]

yes, you're right, its better idea :)
thank you

[brodybits]

Hi @ghominejad I have now pushed a quick fix to the master branch. Can you give it a try, if you have a chance, and let me know whether or not it solves your issue?

I am planning to restructure the CoffeeScript/Javascript to solve some more issues with managing the internal state.