[whatwg] SQL API and Transactions

[Ian Hickson]

On Thu, 1 Nov 2007, Scott Hess wrote:
> On Oct 31, 2007 5:20 PM, Ian Hickson <ian at hixie.ch> wrote:
> > On Wed, 31 Oct 2007, Scott Hess wrote:
> > >
> > > var success = true;
> > >
> > > db.transaction(function (t) {
> > >   t.executeSql('insert into x values (?)', [y]);
> > >   db.transaction(function (t) {
> > >     if (success) {
> > >       // This should be called after the outer transaction has completed.
> > >     }
> > >   }
> > > }, function (e) {
> > >   success = false;
> > > });
> >
> > Actually that wouldn't work because nested transactions are completely 
> > independent of the initial one, so unless the transaction blocked the 
> > entire database, the nested one above would finish immediately.
> 
> Hmm.  Is this because we're allowing for what SQLite calls BEGIN 
> DEFERRED, rather than requiring BEGIN IMMEDIATE?

No, it's because transactions don't necessarily block the whole database. 
It's possible for database engines to implement row-level locking, for 
instance.


> [I'm basing this on error code 5.] In that case, yes, the above won't 
> work.  I'd like to argue that BEGIN IMMEDIATE should be required, but I 
> don't think that's at all portable.

The exact behaviour of BEGIN IMMEDIATE is very SQLite-specific. While I do 
believe we will eventually have to lock down the SQL dialect, I don't 
think we should do this yet, since no content relies on SQL yet, and 
implementations might still find better SQL engines than SQLite.


On Thu, 1 Nov 2007, Aaron Boodman wrote:
>
> Also, I'm thinking more and more that the standard should take a firmer 
> hand.

Eventually, it will. I just don't want to constrain implementors too 
early. For the SQL dialect to be locked down, we need more experience, 
IMHO. Implementors are likely to ignore whatever the spec says if they're 
the first to market anyway, and I don't want to spend two months 
describing SQLite's semantics only to find that the market has decided 
that Microsoft SQL Server's SQL semantics are what the Web will be using.


> I haven't written this up yet, but something like explicitly only 
> allowing the SELECT,INSERT,UPDATE,DELETE statements. For creating 
> schema, you could have API that defines in terms of the standard DOM 
> datatypes (eg DOMString).
> 
> It would be up to the implementation to figure out what these 
> definitions mean in terms of the native types. I think in SQLite at 
> least, the conversion would be pretty straightforward. Seems like it 
> would give some flexibility to the other dbs and give a real shot at 
> interoperability as well.

This was discussed in an earlier thread. The conclusion was that it was 
better to have a thin API and leave the table construction up to the SQL 
dialect, than to move more and more into the API. (Also, it's a slippery 
slope -- why not move SELECT into the API too? And so on.)

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