tralfamadore.com
get unstuck, in time

22 September 2008

Schema migration with Safari's Javascript Database

Locatable relies on Safari's SQLite-based Javascript database implementation for its client-side data storage. As anyone who has worked with Gears or similar technologies can attest, this is pretty slick stuff. The only thing that's been hard for me to get used to is the callback/closure paradigm for dealing with asynchronous query results (really? they couldn't make a synchronous method for selects?) Coming from a more linear JDBC (and before that, though it pains me to say it, ODBC... and before that, Oracle Pro*C... the list goes on...) aesthetic, I have to say the code ends up looking a bit pasta-ish. But it works. What Safari has not yet provided, though, is any semantics around schema migration. It's easy to create and drop tables from code, and to add (but not delete) columns from existing tables (subject to the usual constraint checking). The openDatabase command lets you specify a version number, but it's not particularly useful; its only impact is to throw an exception if you try to open a database while specifying a different version than then one it was created with. Which means it's down to application code to handle upgrades that require schema modifications. One way to do this would be to have a table with a row that specifically tracks the version number or code. I'm guessing this is how Gearshift works for Google Gears. When starting up, you could query this; if it doesn't exist, you're running for the first time, otherwise you could apply the right schema mods. But that's too much work (for someone as lazy as me). Fortunately, JSDB does include transactions, and of course a transaction will bomb out if it gets an error. So if all you want to do is make sure you get the user upgraded to the latest schema, an easier way looks something like this: in your initialization function:
var db = openDatabase('mydb', '1.0', 'mydb', 65536);

db.transaction(function (transaction) {
    transaction.executeSql(
      "CREATE TABLE version001 (id integer primary key)",
      []);
    // Do table creation required for version 001
   transaction.executeSql("CREATE TABLE mytable ...", []);
});

db.transaction(function (transaction) {
    transaction.executeSql(
      "CREATE TABLE version002 (id integer primary key)",
      []);
    // Do schema migration to version 002
    transaction.executeSql("ALTER TABLE mytable ..."), []);
});
And so on. When this runs, the create table statements for dummy tables version001 and version002 act like assertions: the transaction fails (and the rest of the SQL statements within it are skipped) if the table already exists. (It's not strictly necessary to have the version001 table in this example, as the create table line for "mytable" has the same effect; it's just there for clarity). If a user has a version001 table, the code skips to the version002 piece. You can put inserts, updates, and so on in there, none of which will have any effect unless it's the first run through (be careful of other side effects, though; Javascript variables have no transaction boundaries). Doubtless there are other (and perhaps better) ways of doing this; this one certainly has the time drawback of testing against each version dummy table, which could get slow if you have a lot of versions to check through. If you only have a few, it's probably just as fast as (and a lot less complicated than) asynchronously handling a callback that tells you what version number you're at, and going from there (in which case you'd need all the same code, just broken into a bunch of different functions). (And yes, this does mean that altitude, horizontal accuracy and vertical accuracy will be part of the schema in the next release of Locatable!)

Labels: , ,

0 Comments:

Post a Comment

<< Home

Copyright (C) 2001-2008 Tralfamadore.com