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: , ,

01 September 2008

Locatable roadmap and feature planning

I thought I'd give an update on where I'm going with Locatable and I'd love to hear feedback or suggestions for new functionality. I've just tested a better technique for saving data to the client-side database from the Locatable application which means that the app itself should not need any network connectivity. This is good news as there's a risk of settings becoming unsynchronized between the app and the database the way it's working right now (e.g. if the connection fails). This means rewriting all the preferences-saving and preferences-loading code and won't have any visible effect on the UI. Preferences anyway are about to get more complex. Now that Locatable is tracking per-site preferences (so you can choose to only be prompted once or twice per site to share location), there needs to be a UI to see what sites are currently in the list and manage their individual settings. I'm thinking of it a lot like the cookie manager in Firefox — you should be able to blacklist and whitelist individual sites that you visit, as well as alter their settings individually. So that's the task for Locatable 0.4. The other item on the TODO list requires a schema upgrade, which is why I've been procrastinating on it. It would be good to store the horizontal accuracy of the reading so this can be shared (via the W3C API and redirector). It would be nice to have altitude and vertical accuracy as well, though I'm not sure if these even work (certainly not on an iPod Touch!). Once those are in there, the plan is to submit to the AppStore. If accepted, the only difference between the jailbroken version and the AppStore one will be the inclusion of Relocatable, which of course won't work. (I won't be discontinuing development on it, but it may become its own package in Cydia.) In the meantime I'm open to suggestions on other functionality that should be included. Primarily what I've been hearing about is people using Relocatable to do their own lojack-type apps. I'd really like to get some feedback on using Locatable on your web sites to integrate positioning data, do maps mash-ups, and so on. Private feedback is fine, public is even better (and I'll be happy to put links on the Featured Sites page).

Labels: , , , ,

Copyright (C) 2001-2008 Tralfamadore.com