[whatwg] SQL API error handling

[Scott Hess]

On 10/15/07, Ian Hickson <ian at hixie.ch> wrote:
> On Fri, 5 Oct 2007, Scott Hess wrote:
> > Reviewing SQLite's error list, the things that MAY have utility to
> > report more finely might be:
> >
> >  * LOCKED, where you failed because someone else has things locked.
> > Presumably if a single thread of control tries to open the same database
> > via two objects and start two transactions, one of them is going to
> > lose.  Having a transaction fail for this reason seems materially
> > different from having it fail because the SQL was invalid or something
> > of that nature, because the appropriate response might be to retry.
>
> Wouldn't we just want the transaction to wait for the lock to go away?

Hmm.  Right, this API is async, so we can spin for as long as
necessary.  [I notice that there is no way to cancel a long-running
statement!]

Under SQLite, there are cases where retrying might work, in which case
you can retry.  There are other cases where retry will never work, you
need to rollback your transaction and start over.  If you don't do so,
you can cause a deadlock.  Much of this can be addressed by using
BEGIN IMMEDIATE rather than BEGIN DEFERRED (the default for BEGIN).
I'm not certain we can address this kind of issue at the level of this
API, if multiple connections to the same database are allowed.

> >  * CORRUPT, insofar as the Database API lets you delete databases (it
> > doesn't currently, but we've thought of adding that to Gears).
>
> Do we expect authors to actually test for this? Wouldn't the better
> behaviour upon finding that the database was corrupt just be to inform
> the user and wipe it clean? I don't think we want random sites dealing
> with user-side corruption, surely.

You may be correct that authors shouldn't be dealing with this.
Guaranteeing the integrity of the database at open is prohibitive (you
may have to scan the entire database), and no guarantee in practice,
so it's possible that you can detect corruption at any arbitrary
statement.

I'm considering two classes of error, here.  One the one hand are
statements which are just incorrect, either syntactically or
structurally.  They will never execute, your app is broken.  On the
other hand are statement which fail, but are otherwise correct.  I
think these cases are reasonable to distinguish, but it may be that
the author actions for either statement would be identical, making
distinguishing them bootless.

Whoa!  I just realized that there's another group, constraint
failures.  These are statements which will sometimes succeed,
sometimes fail.  As currently spec'ed, it looks like a constraint
failure will cause rollback.  This is probably not appropriate,
constraint failures are generally expected to be benign and
detectable.  Arguing against myself, I've seen plenty of code which
just treats any errors from something which allows for a constraint
failure as a constraint failure, so maybe this also can be lumped
under a single big error code.

-scott