[whatwg] Comments on updated SQL API

[Scott Hess]

On 10/17/07, Aaron Boodman <aa at google.com> wrote:
> This is the first thing that makes me question the current implicit
> transaction API :-(. Maybe adding a executeSqlInTransaction() would be
> smarter. You can then separate the two meanings of SQLCallback:
>
> - getting results of a sql call
> - linking sql calls together into transactions

Since opening an SQL transaction is itself an SQL call which may fail,
I think it would be more reasonable to just have two calls,
executeSql() which simply passes the SQL statement down to the driver,
and transaction() which provides a transaction context in the
callback.  If executeSql() is called within the transaction()
callback, that extends the transaction into the newly registered
callback.  If transaction() is called within a callback, that opens a
nested transaction (*) and extends the containing transaction.
Successfully completing the entire tree of callbacks results in
commit, while an uncaught exception causes rollback (**).

(*) This could either be a true nested transaction, if your SQL driver
supports that, or a fake transaction using a stack to track nesting
(with the consequent inability to rollback a nested transaction
without rolling back through the outermost transaction).

(**) I'm not sure how real nested transactions would work, here.
Maybe you have to catch exceptions from the nested transaction() call?

-scott