[whatwg] SQL API error handling

[Scott Hess]

On 10/15/07, Ian Hickson <ian at hixie.ch> wrote:
> On Mon, 15 Oct 2007, Scott Hess wrote:
> > 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.
>
> Could you elaborate on this? What would code running into this situation
> look like? How should we expose it?

A common case is wanting to update a row, if present, or insert it, if
not.  You can handle this in three ways.  You can use a transaction to
keep things consistent:

  BEGIN;
    SELECT COUNT(*) FROM t WHERE id = ?;
    -- if == 0
      INSERT INTO t VALUES (?, ...);
    -- if == 1
      UPDATE t SET c = ?, ... WHERE id = ?;
  END;

[Note that the above gets you the "read lock upgraded to write lock" case.]

This style is generally avoided, because in a server environment, you
have four round trips from when the transaction is opened to when it's
closed, plus whatever contention for CPU is present at both ends, so
it is not great for concurrency.  Instead, you can just try the insert
and rely on a unique or primary key to cause a constraint violation:

  INSERT INTO t VALUES (?, ...);
  -- if constraint violation on id
    UPDATE t SET c = ?, ... WHERE id = ?;

That variant is best if you expect to usually succeed, and sometimes
fall through to the update (for instance when inserting a new user
record).  Otherwise, you could do:

  UPDATE t SET c = ?, ... WHERE id = ?;
  -- if no rows affected
    INSERT INTO t VALUES (?, ...);

That might be more appropriate for updating a user record where the
user is known to exist but the record is not known to exist (say a
record of the last time the user performed a particular operation).

The first and third cases should work fine within the spec.

---

Another example would be a table like:

  CREATE TABLE UniqueName (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE
  );

You might run:

  INSERT INTO UniqueName (id, name) VALUES (null, ?);

If it succeeds, the id is in insertId.  If there's a constraint
failure, the programmer knows that it's because the value provided for
name was already take.  You can of course rewrite this like:

  BEGIN;
    SELECT count(*) FROM UniqueName WHERE name = ?;
    -- If != 0, fail.
    INSERT INTO UniqueName (id, name) VALUES (null, ?);
  END;

Using the INSERT directly is the standard idiom for this type of thing, though.

---

Unfortunately, offhand I'm not coming up with any cases which aren't
fundamentally server-side.  Part of the justification for using SQL in
the spec was so that developers could use similar/identical code
server-side and client-side, but I'll admit that the kinds of cases
above are probably no more or less annoying to work around than the
differences between any chosen server-side database and SQLite would
be.

-scott