[whatwg] Comments on updated SQL API

[Ian Hickson]

On Sat, 22 Sep 2007, Timothy Hatcher wrote:
>
> The callback syntax is nice but the implicit thread-global transaction 
> is confusing and can lead to programmer error and unneeded database 
> locking.

There isn't really a thread-global transaction, the transactions are 
per-sequence-of-executeSql-calls, as in:

   executeSql('sql1', function (result) {
     executeSql('sql2', function (result) {
       executeSql('sql3', function (result) {
         // this is all in one transaction
       });
     });
   });
   executeSql('sql4', function (result) {
     executeSql('sql5', function (result) {
       executeSql('sql6', function (result) {
         // this is all in another transaction
       });
     });
   });

...where the likely order of execution is sql1 and sql4 at the same time, 
then sql2 and sql5, then sql3 and sql6, assuming all the statements take 
the same amount of time.


> It doesn't look like you can rollback the implicit transaction (one of the big
> features of transactions.)

You can rollback just by throwing an exception in the callback.


> Also what access locks does the implicit transaction take? What if you 
> need exclusive access to the database temporarily? These cases would be 
> solved by adding explicit transactions with control to access locks and 
> rolling back.

There are various ways of solving these, indeed. Another way would be to 
support 'BEGIN TRANSACTION EXCLUSIVE' as the first statement of a 
transaction, which would set the transaction type. (e.g.)


> Here is an example:
> 
>     transation1 = db.beginTransation();
>     transation1.executeQuery("SELECT * FROM document", function(result1) {
>         if(...) {
>             result1.transation.executeQuery("UPDATE document WITH ... WHERE
> document = ?", result1.namedItem("id"), function(result2) {
>                 ...
>             });
>         }
>     });
> 
>     transation2 = db.beginTransation(Database.ExclusiveTransactionLocks);
>     transation2.executeQuery("UPDATE user WITH ... WHERE user = ?", "123",
> function(result1) {
>         if(...) {
>             result1.transation.executeQuery("UPDATE user WITH ... WHERE user =
> ?", result1.namedItem("id"), function(result2) {
>                 result1.transation.commit();
>             });
>         } else {
>             result1.transation.rollback();
>         }
>     });

This is basically what the spec supports now, except without any explicit 
mentioning of the transactions.


> I think an explicit commit should be required for the transactions. So 
> you can keep the transcript object around and add to it over time.

Currently the spec commits if you don't add a new query to the 
transaction. Could you elaborate on your use case?


> Some other comments:
> 
> You might notice that I have been using executeQuery() instead of 
> executeSql(). I think it is weird and unneeded to say Sql in the 
> function name. If anything, it should be executeSQL().

The name "sql" is there so that we can add other types of queries at a 
later date. The capitalisation is based on typical capitalisation of other 
members in the DOM.


> I think the ResultSet object should have a property to get the Database 
> and the Transaction objects. As I mentioned earlier, not everyone will 
> use closures and will need access to these to do anything else.

You don't need access to the transaction object if anything executed 
during the callback is added to the transaction, as far as I can tell; and 
the database would just be the database... you can pass that around 
however you like, no need for an explicit member.

-- 
Ian Hickson               U+1047E                )\._.,--....,'``.    fL
http://ln.hixie.ch/       U+263A                /,   _.. \   _\  ;`._ ,.
Things that are impossible just take longer.   `._.-(,_..'--(,_..'`-.;.'