[whatwg] Comments on updated SQL API

[Timothy Hatcher]

The callback syntax is nice but the implicit thread-global transaction  
is confusing and can lead to programmer error and unneeded database  
locking.

Commonly there is a implicit transaction per query if there is no  
explicit transaction created. I think this is what developers will  
expect, after using server side databases. In a complex web  
application, an implicit thread-global transaction will cause  
questions on when it began. The code can be spread all over, and will  
not always be written out using closures for the callbacks.

It doesn't look like you can rollback the implicit transaction (one of  
the big features of transactions.) 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.

If explicit transactions are not added developers will take advantage  
of what they have access to using the implicit transactions. (I assume  
each Database object will have it's own implicit thread-global  
transaction.) So you could open two Database objects for the same  
database and execute two simultaneous implicit transactions. But you  
would need to write your own rollback code if you need to  
programatically bail out of a transaction and restore any previous  
values.

I propose that the implicit transaction be scoped to only one query at  
a time, like I mentioned earlier. Then add explicit transactions that  
you create from a Database object. Then you can start two simultaneous  
transactions on the same Database object, which is nicer than holding  
two Database objects for the same database just for concurrent access.

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();
         }
     });

My example executes queries on the transaction object. The transaction  
object would internally keep track of the database connection it  
holds. Transactions can come in a couple of forms, shared and  
exclusive access locks (read http://www.sqlite.org/ 
lang_transaction.html for details on these.)

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.

Above I have beginTransation taking a constant  
Database.ExclusiveTransactionLocks. Passing no argument would get  
shared access locks. Also in my example is an explicit rollback. Any  
query that fails will naturally rollback the transaction, but having  
progrmatic way to rollback is a necessity.

The closeTransaction Database object function should be remove if  
explicit transactions are added and the implicit transaction is  
limited to one query.

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().

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.

— Timothy Hatcher


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.whatwg.org/pipermail/whatwg-whatwg.org/attachments/20070922/088a0da9/attachment-0001.htm>