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!)

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).

The plunge

... I've taken it. Over the next 18 months, I will pay O2 UK £664 (plus the £159 I laid down at the Apple Store today) for the privilege of owning an iPhone 3G. When I look at it that way it seems, um, a lot, but as I can remember the time when I was paying £15/mo. for a bundle of 5 megabytes of data access (that was 2002, on Orange, if memory serves), I guess I can see the bright side of an unlimited data plan.

As you might expect, the first step before syncing music and photos was Pwnage. Can't wait to try some of the iPhone-specific apps out there, particularly Saurik's new video recorder. Oh, and to see how Locatable works in the field using actual GPS.

One annoyance: I couldn't get PwnageTool (the latest, 2.0.3.1) to add in a custom boot image. I wanted to get the classic multicolor Apple image on there, but every time I went to add it, the tool crashed. Oh well — next upgrade.

The W3C Geolocation API on iPhone with Locatable

I've ported the W3C's draft Geolocation API so it can be used from an iPhone with Locatable installed (my Javascript skills are far from elite, but with enough prodding and old-school alert() debugging, I got there). This means that in addition to the redirect API (which is nice for embedding static links to pages that can take lat/long coordinates), you can now get at location information on demand via Javascript, through what is likely to become the standard API in future browsers.

To use it, just include this in your HEAD:

<script type="text/javascript"
src="http://lbs.tralfamadore.com/w3c-api.js">
</script>

You can download and install the script locally if you like (but please check back for new versions from time to time).

Then, to use it, just use the standard W3C-prescribed approach via a global object called Locatable, e.g.

// Callback handler
function gotLocation(position) {
alert('You are at (' + position.latitude + ','
 + position.longitude + ').');
}

// Use this anywhere you like
Locatable.getCurrentPosition(gotLocation);

The only addition to the W3C API is an isEnabled() method. This will attempt to figure out if the API will work on the current browser. Right now this merely checks if someone is on an iPhoneOS device, but might be more sophisticated in the future.

There's a test page up at http://www.tralfamadore.com/test-w3c.html that demonstrates this functionality.

Some implementation notes:

• The same logic applies when sharing location as with the redirector. Depending on user preferences, an alert will ask them to confirm if they want to share their location. If they decline, you'll get an error callback if you provide the second argument to getCurrentPosition.
  
• If the location needs to be refreshed, the app will launch, update the reading, and then return to Safari. This can take some time (in Locatable 0.3, up to 20 seconds, depending on the user accuracy setting). On jailbroken phones with default Locatable settings this is unlikely to occur as the daemon will be updating location in the background, but an AppStore version will not have this advantage, so be mindful of this.
• W3C PositionOptions (the accuracy hint) does nothing at the moment.
• watchPosition() is "implemented" (that is, the function exists), but you'll only ever get one reading, so it's not entirely useful.
• The accuracy reading in the position object is currently the user-set minimum accuracy level (a round number like 10, 100, or 1000 meters), not the device-reported accuracy of the reading itself. This is likely to change in future versions.
• Altitude and velocity are not implemented yet and yield null values.
  
• w3c-api.js will attempt to detect if you're running an iPhone or iPod Touch and not install itself otherwise. It also won't overwrite navigator.geolocation if it's already implemented (non-null).
• It probably goes without saying, but you should include the w3c-api.js script on every page you want to use it in.
  

I'd consider this a beta version — I've done some basic testing but haven't tried too many use cases. Let me know how it works for you in the comments.

Update (31 Aug 08): Upon some reflection, I decided it's best not to try to automatically install as the navigator.geolocation global, so the script has been updated to use a global called Locatable (capital L) instead. You're free to assign it to navigator yourself (i.e. navigator.geolocation = Locatable). Also added the isEnabled() method.

Changelog for Locatable 0.3

I'm just about to submit the new version of Locatable. Here are the changes:

Locatable

• More accurate GPS readings. When retrieving a location, wait until the accuracy is within the specified range, or 20 seconds, whichever comes first (i.e. if you set it to "Best", it'll spend the full 20 seconds).
• New option "Expire after" specifies how stale of a location is allowed to be sent to web sites. Default is 10 minutes. If you set this to "each request", Locatable will pop up every time a web site wants to read your location.
• New option "Ask permission" defines if and how many times you'll be prompted to allow a site to see your location. Default is to always ask. Set this to "Twice per site" to mimic the behaviour of iPhone native apps.

Relocatable

• Relocatable is now a daemon process. You can control how often and how fast it runs by editing /System/Library/LaunchDaemons/com.tralfamadore.locatable.plist.
• You can also run it from the command line (it now lives in /usr/libexec/relocatable), which gives you the following options:

~ root# /usr/libexec/relocatable/Relocatable -h
Usage: Relocatable [-v] [-t SECONDS] [-d SECONDS] [-e CMD]
-d SECONDS      run as a daemon, delay specified seconds between fixes
-e CMD          execute given program (with args) after each location fix,
                 can include @lat@, @long@, and @hacc@ tokens
-t SECONDS      spend specified seconds waiting for a fix, default 30
-v              turn on verbose logging

Let me know if you encounter any issues.

Update (30 Aug 08): A few people had questions about getting the command execution piece to work. Here's an example — the important bit is to wrap the whole command in single quotes, otherwise the shell gets confused.

First, if Relocatable is already running as a daemon (it will be by default), you need to stop it:

~ root# launchctl unload /System/Library/LaunchDaemons/com.tralfamadore.locatable.plist

Once that's done, you can run Relocatable as a one-off from the command line:

~ root# /usr/libexec/relocatable/Relocatable -v \
> -e 'curl "http://my.site.com/savepos.pl?lat=@lat@&long=@long@&acc=@hacc@"'
Opened LBS database for read...
Started updates...
newLocation: <+51.xxxxxxxxx, -0.xxxxxxxx> +/- 93.21m @ 2008-08-30 13:17:44 +0100
newLocation: <+51.xxxxxxxxx, -0.xxxxxxxx> +/- 93.21m @ 2008-08-30 13:18:29 +0100
Stopped updates
Opened LBS database for write...
* About to connect() to my.site.com port 80 (#0)
*   Trying xxx.xxx.xxx.xxx... connected
* Connected to my.site.com (xxx.xxx.xxx.xxx) port 80 (#0)
> GET /savepos.pl?lat=51.xxxxxxxxxx&lng=-0.xxxxxxxxxxxx&acc=93.21 HTTP/1.1
> User-Agent: curl/7.17.1 (arm-apple-darwin9) libcurl/7.17.1 OpenSSL/0.9.8g zlib/1.2.3

Note the use of the single quotes (to bracket the command passed to Relocatable) and double quotes (to bracket the URL, so it can contain characters that would otherwise confuse the shell, like the ampersand).

The same rules regarding quoting apply to editing the daemon plist. Once you're happy with your settings and have edited the plist to your satisfaction, remember to start it back up:

~ root# launchctl load /System/Library/LaunchDaemons/com.tralfamadore.locatable.plist

Xsstc: Cross-site scripting through CSS

I've been doing a lot of reading on cross-domain scripting approaches. Generally speaking, the browser is sandboxed by the same-origin policy, and mashups that want to incorporate data from external sites, even if those sites are cooperating, need to provide server-side proxies. There are a couple of popular workarounds: (1) using the hash (#) portion of the URL, which can be read between frames, and (2) cross-domain JSON, or in other words, directly importing live scripts from a third party site into your own. Other more fanciful techniques include using the Flash plugin; obviously this fails if you try to run the code on any device without Flash installed, regardless of its Javascript capabilities (the iPhone comes to mind).

Ideally, a client script just wants to directly invoke a server-side method and get a response back. Due to popular demand, there's work underway in the standards bodies to make this happen, but it will be a long while before it reaches ubiquity.

I started to wonder about other pieces of data in the browser that might enable the basic use case, and after some long hours of experimentation, I finally found a way in: externally loaded cascading style sheets (CSS).

It turns out CSS leaks data in a very subtle way. Properties set by an external stylesheet (that is, one that is loaded using a LINK REL="STYLESHEET" tag) are used to style the elements of the host page, and at runtime the page can introspect itself to see what styles have been applied. Most of these tend to be strictly prescribed data, such as background colours for block elements, or some multiple choice items, like left/center/right alignment for text. While you could conceivably come up with a binary (or ternary) system based on that, it would be a pretty nasty job to try to make those into a general-purpose data channel. Fortunately, there are a few places where CSS lets you specify essentially free-text attributes: image URLs.

n.b.: I did a lot of searching on the topic but it was only after I got this technique working that I found the proposal posted by Gideon Lee on the OpenAjax mailing list, advocating much the same approach. I'm not sure if that work is still in progress as the last message on the list dates from October '07, but Gideon deserves credit for coming up with the basic idea.

I chose to work with the background-image attribute, and verified that a location hash for an image URL set in the CSS, though meaningless to the browser, is still visible by introspection via the getComputedStyle() method (currentStyle attributes in IE). There's some complexity in reliably reading this value, and in dynamically loading stylesheets, but the long and short of it is that on top of this system I've created a cross-browser Javascript library for cross-site requests.

First, check out the test page I've set up. You might want to view source, and also check out the two CSS "response documents" it references. Then read on for how to do it yourself.

The Client

Using the library is straightforward. You can get the current version at http://www.tralfamadore.com/xsstc.js, or a minified version that's a mere 777 bytes at http://www.tralfamadore.com/xsstcx.js. Stick it on your server somewhere or feel free to link to the copy here directly.

On your page, you need the following:

1. A SCRIPT tag in the header referencing xsstc.js (or xsstcx.js)
2. An empty DIV tag in the body with id="Xsstc". No other attributes required.
3. Javascript that calls Xsstc.exec(functionURL, callback). This method loads the specified URL and expects it to be formatted as described below (The Server). Once it has finished loading, it calls the specified callback function, which takes one argument, the string containing the parsed response.

The simplest HTML page looks something like this (using the HelloWorld example from the test page):

<html>
<head>
<title>Xsstc Sample</title>
<script type="text/javascript" src="xsstcx.js"></script>
<script type="text/javascript">
function showResponse(retval) {
alert('Return value: ' + retval);
}
</script>
</head>
<body>
<form>
<input type="button" value="Test Me"
onClick="Xsstc.exec('http://lbs.tralfamadore.com/test.css', showResponse)" />
</form>
<div id="Xsstc"/>
</body>
</html>

The key pieces are bolded above. In this example, http://lbs.tralfamadore.com/test.css serves as the server-side endpoint. Now let's look at...

The Server

The server's job is straightforward. It receives a normal HTTP GET request, that might have various arguments (these can be encoded in the usual way, via query string parameters, pathinfo, or whatever you like; Xsstc doesn't prescribe the notation), and must respond with a valid CSS stylesheet document.

The trick is in embedding the method response in the CSS background-image value. I've found that "about:blank" (which causes the browser to show a blank screen) is a good placeholder value for a background image that the response can then be appended to after the hash (#) character. If you use a real image URL, it will most likely get loaded by the browser, which isn't really what we want. The response proper needs to be URL-encoded, as it's, well, part of a URL.

In order for the client side to read the value that gets set by the stylesheet, it needs to attach to an actual element in the HTML document. That's why we added the do-nothing DIV called Xsstc. The CSS simply targets this DIV by its ID, and sets its background image. So a response document that wanted to embed "Hello World" would look like this:

#Xsstc {
 background-image: url('about:blank#Hello%20World');
}

Because this response format is so simple, it's easy to create in just about any server-side programming language. And because of the data seepage inherent in stylesheets, the server can be any site on the Internet that has chosen to expose its services in this way — you're not limited by the same-domain policy the browser applies to other external requests.

Xsstc and JSON

There's a natural fit between Xsstc and JSON, as one of the examples on the test page alludes to. I've taken a sample JSON response straight off of www.json.org/example.html, URLencoded it, and slapped it into the Xsstc response format. This is not to say that Xsstc is dependent on JSON in any way: the Xsstc.exec() method generates a callback that returns whatever string is in the response, however it's formatted. But JSON is a nice compact way of representing datasets that can be easily worked with in Javascript, so a JSON library on top of the Xsstc communications channel seems like a natural fit.

Compatibility

This is the first release and while I'm sure there will be something broken, I've tested the examples (minimal though they are) on recent versions of Mozilla, Safari and Internet Explorer (IE is of course the worst to work with, but with a little bit of switching logic it seems to be doing well). It should also work on modern versions of Opera, and hopefully anything else that's W3C compliant.

Limitations

There are a few limitations that are worth being aware of. The first is that because the response string needs to be embedded in a URL, some browsers (you know which) are likely to cap the possible length of a response. While there are ways to work around this (for example, you could split one response into several consecutive method calls), it might mean that Xsstc is overly painful for implementing methods that have a bulky response.

At the moment the library is also single-threaded, though this can be remedied in time. This means that only one Xsstc.exec() method can be in progress at a given time, or you're likely to have untoward side effects. In a similar vein, there's virtually no error-handling going on in the current version, and the script will happily wait until the end of time for a response from a server that might be down.

Finally, because there's no onLoad event for stylesheet loading, the script is set up to poll for the availability of the response. In my tests this hasn't caused significant problems (there's a 50ms pause between each check), but sites that have a lot of other activities going on may want to look at how to best tune the performance of the timers.

Security

My understanding is that you cannot specify javascript: URLs for CSS background-image attributes and expect them to execute, which should mitigate any concerns of remote scripts stealing data. In the OpenAjax discussion, it was mentioned that on FF2 you can apparently execute javascript in this mode but it runs in a very sandboxed manner, without access to the document object. Taking a broader view, if there is a vulnerability here, it exists already with the ability to load foreign stylesheets, and will not be something new exposed by Xsstc.

Because both the client and server systems must cooperate on the request/response cycle, and the Xsstc DIV element is the only item singled out for data transfer, there's very little likelihood of "rogue" code. In addition, Xsstc doesn't rely on script loading and is therefore naturally immune to the trust issues that plague cross-domain implementations of JSON.

License

Xsstc (pronounced, if you'll indulge me, "Ecstasy") is licensed BSD-style. The relevant text is in the xsstc.js file. I'm happy to incorporate worthwhile changes and additions — just reply in the comments or email me (my address is wes at this blog's domain).

As always, happy hacking!

Locatable: Some stats

Locatable has been on Cydia for about a week now, and thanks to BigBoss I can see that there have been over 5,000 downloads (some of these are people upgrading from 0.1 to 0.2, of course).

I've also done some analysis of the visitors to the Featured Sites page. First of all, I'm impressed by how far around the globe the jailbroken iPhone has traveled. Within just the last 12 hours there have been visitors from over 50 countries -- here are the top ones:

1. U.S. (18%)
2. France (7%)
3. Mexico (7%)
4. Brazil (6%)
5. U.K. (6%)
6. Spain (4%)
7. Canada (4%)
8. Slovakia (3%)
9. Italy (3%)
10. Norway (3%)

Traffic is overwhelmingly (95%) coming from iPhones as opposed to iPods Touch, as you might expect for an app that is most useful with GPS when you're out and about; on the other hand, so far usage is still fairly evenly split between WiFi and mobile networks.

In other news: I've got a heavily reworked version of Relocatable just about ready to go that makes it very easy to do the location tracking hack posted previously. It also does a far better job of getting accurate GPS readings, though it takes a little longer. I'm working on incorporating the same technique into Locatable and I'd like to start adding some more management preferences, such as the ability to have trusted sites that you aren't continually prompted for (much the same as the way the iPhone works for applications that request to read your location: after a couple of checks, it assumes you're fine with it). If you have other feature ideas please comment!

n.b. If you have a working iphone-gcc toolchain installed, the Makefiles are set up so you can build your own copies of Locatable and Relocatable from source now, and the changes mentioned above for Relocatable are checked in.