Synchronous query to Web SQL Database

Question

I'm working on a bit of JavaScript that interacts with a client-side SQLite database, via the newish window.openDatabase(...), database.transaction(...) and related APIs. As most of you know when you execute a query in this way it is an asynchronous call, which is typically good. You can make the call and handle the results as appropriate with callbacks.

In my current situation I'm working on an algo for a client that does some hierarchy walking in the locally stored database. The part of the algo I'm having trouble with requires starting at some row, which has a reference to a "parent" (by id) that is another row further up in the table. I have to keep walking up this tree until I reach the root.

The problem is that I'm at a point where I'm not sure how to use an asynchronous style query with a callback to keep feeding the loop parent ids. Ideally I could get the query to block so that I can do it all in the loop. Here's the key parts of my current setup:

    for (i in search.searchResults.resultsArray)
    {
        hierarchyArr = new Array();
        pageHierarchyArr = new Array();
        id = search.searchResults.resultsArray[i].ID;

        while (id != null && id != "")
        {
            var hierarchySql = "SELECT ID, parentID, type, content FROM content WHERE ID = " + id;

            // This is a prettied up call to database.transaction(...)
            var rs = db.getRS(hierarchySql);

            // Ideally the code below doesn't execute until rs is populated

            hierarchyArr.push(rs[0]);

            if (rs[0].type == "page")
            {
                pageHierarchyArr.push(rs[0]);

                // Do some additional work
            }

            id = rs[0].parentID;
        }
    }

As you might imagine, it doesn't work well. hierarchyArr gets an "undefined" pushed into it, and then the script crashes when it tries to check the type of rs[0].

When I try to set it up with a callback (db.getRSAndCallback(sql, callbackFunc), which I used for the earlier, non-interdependent queries just fine) it's worse: the inner loop takes off like crazy because id isn't getting updated; presumably because the loop is keeping the JavaScript interpreter so busy that it never actually fills rs. In some artificial testing where I forced the inner loop to break after a few iterations all the callbacks started coming through all at the end, after the loop finished.

The "standard" (such as it is right now) at http://dev.w3.org/html5/webdatabase/#synchronous-database-api seems to indicate that there is a synchronous API, but I haven't seen any sign of it on any WebKit based browsers.

Can anyone offer suggestions on how I might either, a. properly formulate these iterative, interdependent queries using callbacks or, b. somehow get the call to actually happen in a synchronous or apparently synchronous manner.

Many thanks in advance for anyone who takes a crack at this seemingly tricky little problem.

Naim

P.S. Here's the client's implementation of db.getRS for reference:

.
.
.
getRS: function(sql)
{
    var output = [];
    db.database.transaction(function(tx)
    {
        tx.executeSql(sql, [], function(tx,rs)
        {
            for(i = 0; i < rs.rows.length; i++)
            {
                output.push(rs.rows.item(i));
            }
        },
        function(tx, error) { ... }
    )});
    return output;
},
.
.
.

Legislation

Oh, I also wanted to mention: If I'm debugging the script and I set a breakpoint right on hierarchyArr.push(rs[0]); the script works just fine. Stepping through it the recordset is populated and id is updated and it walks the hierarchy. Disable the breakpoint and let it run and it crashes where I mentioned above, no doubt because the pause in the current execution lets it finish the query. – Legislation

Answer 1

I used callbacks and a closure to solve a similar problem, consider:

function getFolder(id, callback) {
var data = [];
ldb.transaction(function (tx) {
tx.executeSql('SELECT * FROM folders where id=?',
    [id],
    function (tx, results) {
        if (results.rows && results.rows.length) {
            for (i = 0; i < results.rows.length; i++) {
                data.push(results.rows.item(i));
            }
        }
        if (typeof(callback) == 'function')
            callback(data);
    },
    function (tx, error) {
        console.log(error);
    });
});
}

In the continuation of this example, folder has a property parent to define it's relation to other folders. As does a document. The following will get you the path of a document using a closure (success):

  function getDocPath(doc, callback) {
      var path = [];
      var parent = doc.parent;
      var success = function(folder) {
         var folder = folder[0];
         parent = folder.parent;
         path.push({'id':folder.id,'name':folder.name});
         if (parent != "undefined")
             getFolder(parent, success);
         else
             if ( typeof(callback) == 'function' ) callback(path.reverse());
      }
      getFolder(parent, success);
  }

Cyclopentane

That ended up being just about exactly how I ended up implementing it. Thank you for taking the time to put your code here for others! I had forgotten to do that. :) – Legislation

I am sorry, please could you explain if there is any difference with the above solution between having the data[] variable in the outer scope as opposed to being in the callback function? – Macmahon

Answer 2

You could use callbacks with a closure to your stack of remaining queries. Or you could use recursion, passing the stack as parameters.

Pithead

Yes, those are both good options. I think recursion will likely be the way to go in this situation, but wanted to hold out for other ideas. Luckily, my processing of the full data set isn't interdependent between records, so it won't be too messy. – Legislation