Async query progress callback

[nbdamian]

Should we implement a way to check how far alasql is through a stack of queries? For example if we had:

var sql = "CREATE INDEXEDDB DATABASE IF NOT EXISTS test;"
+"ATTACH INDEXEDDB DATABASE test;"
+"USE test;"
+"CREATE TABLE cities (city string, population number);"
+"INSERT INTO cities VALUES ('Rome',2863223),('Paris',2249975),('Berlin',3517424), ('Madrid',3041579);"
+"SELECT * FROM cities WHERE population < 3500000 ORDER BY population DESC;";

alasql(sql, [], function(res) {
    print(res);
}, function(error) {
    print(error);
});

We could add a third callback, which would be called after each query is called, and return the number of queries executed.

E.g:

var sql = 
"CREATE INDEXEDDB DATABASE IF NOT EXISTS test;"
+"ATTACH INDEXEDDB DATABASE test;"
+"USE test;"
+"DROP TABLE cities;"
+"CREATE TABLE cities (city string, population number);"
+"INSERT INTO cities VALUES ('Rome',2863223),('Paris',2249975),('Berlin',3517424), ('Madrid',3041579);"
+"SELECT * FROM cities WHERE population < 3500000 ORDER BY population DESC;";

alasql(sql, [], function(res) {
}, function(error) {
}, function(progress) {
    print(progress)
});

Would Print:

1
2
3
4
5
6
7

If this was to be implemented, we would have to check whether it is possible to return something like progress in promises, similar to https://api.jquery.com/deferred.progress/

[mathiasrw]

Good input.

Is this for debugging or are you thinking this for production?

Had an idea to implement it via .promise but saw that

ECMAScript 6 promises lack two features that are sometimes useful:

• You can’t cancel them.
• You can’t query them for how far along they are (e.g. to display a progress bar in a client-side user interface).

Ugly quickfix until we get this supported: Use a user defined function.

var n = 0;
alasql.fn.progress(){
    print(++n)
}

var sql = 
"CREATE INDEXEDDB DATABASE IF NOT EXISTS test; select progress();"
+"ATTACH INDEXEDDB DATABASE test; select progress();"
+"USE test; select progress();"
+"DROP TABLE cities; select progress();"
+"CREATE TABLE cities (city string, population number); select progress();"
+"INSERT INTO cities VALUES ('Rome',2863223),('Paris',2249975),('Berlin',3517424), ('Madrid',3041579);select progress();"
+"SELECT * FROM cities WHERE population < 3500000 ORDER BY population DESC; select progress();";

alasql(sql, [], function(res) {
}, function(error) {
});

[nbdamian]

I was thinking it would be nice in the event that someone had a long running query chain, it could be used to implement something like a progress bar, so if you had say a hundred or so queries, taking a 10-20 seconds to execute them all, you could keep the user up to date with how far through the chain you are.

[nbdamian]

Perhaps we just add a function build into alasql, like the user defined one you supplied stored in the options, the user can override it by using alasql.options.progress = function(){}; Force the parameters of "numberofqueriestoexecute", "currentqueryindex".

alasql.options.progress = function(a, i){
    print(Math.round((i / a) * 100)+'%');
}

var sql = 
"CREATE INDEXEDDB DATABASE IF NOT EXISTS test;"
+"ATTACH INDEXEDDB DATABASE test;"
+"USE test;"
+"DROP TABLE cities;"
+"CREATE TABLE cities (city string, population number);"
+"INSERT INTO cities VALUES ('Rome',2863223),('Paris',2249975),('Berlin',3517424), ('Madrid',3041579);"
+"SELECT * FROM cities WHERE population < 3500000 ORDER BY population DESC;";

alasql(sql, [], function(res) {
}, function(error) {
});

Note, that instead of returning 0 for the first, it would have to return 1 in order to prevent a dividing by 0 issue.

[mathiasrw]

Thats a really good idea

[nbdamian]

Ok, So I did some playing round in the source code and have got it working in the above query, although my code is probably messy as hell.

On line 4138 I have:

alasql.options.progress = false; // Callback for async queries

I have overridden the alasql.adrun function with - line 4340:

/**
  Run multiple statements and return array of results async
 */
alasql.adrun = function (databaseid, ast, params, cb, scope) {
  var idx = 0;
  var noqueries = ast.statements.length;
  if (alasql.options.progress !== false)
    alasql.options.progress(noqueries, idx++);
//  alasql.busy++;
    var useid = alasql.useid;
    if(useid !== databaseid) {
        alasql.use(databaseid);
    }
    var res = [];

    function adrunone(data) {
        if(data !== undefined){
            res.push(data);
        }
        var astatement = ast.statements.shift();
        if(!astatement) {
            if(useid !== databaseid){
                alasql.use(useid);
            }
            cb(res);

        } else {
            if(astatement.compile) {
                var statement = astatement.compile(alasql.useid);
                statement(params, adrunone, scope);
        if (alasql.options.progress !== false)
          alasql.options.progress(noqueries, idx++);
            } else {
                alasql.precompile(ast.statements[0],alasql.useid,params);
                astatement.execute(alasql.useid, params, adrunone);
        if (alasql.options.progress !== false)
          alasql.options.progress(noqueries, idx++);
            }
        }
    }

    adrunone(); /** @todo Check, why data is empty here */
};

It runs progress when it starts, returning 1 as the index, then after each iteration it executes the function if it is defined.

I will have a look and see if I can get it working with promises as well.

OK, Promises CAN use the function, although it is a bit hard. We would have to add 2 extra parameters, or (1 as an array/object) for the number of queries and the current index.

I added them as separate variables, but feel free to convert them to an array

var promiseChain = function(idx, noqueries, sqlParamsArray, lastPromise){
    var active, sql, params;
    if(sqlParamsArray.length<1){
        return lastPromise;
    }

    active = sqlParamsArray.shift();

    if(typeof active === 'string'){
        active = [active];
    }

    if(!utils.isArray(active) || active.length<1 || 2<active.length){
        throw new Error('Error in .promise parameter');
    }

    sql = active[0];
    params = active[1]||undefined;

  if (alasql.options.progress !== false)
    alasql.options.progress(noqueries, idx++);

    if(typeof lastPromise === 'undefined'){
        return promiseChain(idx, noqueries, sqlParamsArray, promiseExec(sql, params));
    }

    return promiseChain(idx, noqueries, sqlParamsArray, lastPromise.then(function(){return promiseExec(sql, params)}));

}

alasql.promise = function(sql, params) {
    if(typeof Promise === "undefined"){
        throw new Error('Please include a Promise/A+ library');
    }

    if(typeof sql === 'string'){
        return promiseExec(sql, params);
    }

    if(!utils.isArray(sql) || sql.length<1 || typeof params !== "undefined"){
        throw new Error('Error in .promise parameters');
    }
  var idx = 0;
  var noqueries = sql.length;
  if (alasql.options.progress !== false)
    alasql.options.progress(noqueries, idx++);
    return promiseChain(idx, noqueries, sql);
};

[mathiasrw]

Nice work. Im only on my phone but it looks really good. Lets get the changes into the code.

We might want a switch so we can turn it off if its not wanted AND speed is important

[nbdamian]

By default, it is turned off, via the alasql.options.progress = false; To turn it back off, the user would just have to set the variable to false.

[mathiasrw]

I get it :)

[nbdamian]

Hmm, I am working on implementing the changes, although I cant seem to find in what file promiseChain = function is inside, are you aware of where this is?

[mathiasrw]

src/18promise.js line 39 is what you are looking for

[nbdamian]

Ouch, I'm an idiot, my fork was out of date xD

[mathiasrw]

Thanks for great work.