Wandering Data

Today’s announcement that RIM will provide SQLite support on the BlackBerry here at the BlackBerry Developer Conference keynote is certainly an eye-opener. Several people have asked me what it means for UltraLiteJ (our “SQL Anywhere on BlackBerry” mobile database). So here’s a go at answering that question.
The main thing is that SQLite solves half the problems that many people look to a mobile database to solve. That is, you can use SQLite to store data, and you can use SQLite to retrieve data. It does this well and it does it through a flexible SQL interface. So if you have an application and want to save state when you quit, then retrieve state when you start up, then SQLite will do the trick fine. As its web site says, SQLite is designed to replace fopen(), not to replace Oracle.
But if you want to build a mobile application that runs off a database, you want to keep the data on device in sync with that on a server, and SQLite doesn’t address this data synchronization problem at all. As Chris Kleisath quoted a partner of ours recently here, explaining why they chose UltraLiteJ:

Our feedback is that most mobile projects collapse due to lack of reliability in the synchronization process.

Sync really is a big part of the problem, and that’s why SQLite support gives you a local datastore on device, but does not really give you a mobile database.
For the on-device piece of a mobile application, you need two things to do synchronization: change tracking (if you delete a row, you need to send that delete to the server, so you have to keep a record of that delete somewhere) and state tracking (if you send a change to the server, you need to know if it got there or not so you can send it again or not). A big chunk of the code of our UltraLite and UltraLiteJ database products is dedicated to managing these change tracking and state tracking aspects of the problem and to handling the network communications with our synchronization server. SQLite is not designed for this purpose - it has no programmatic access to a transaction log for example - so any synchronization will have to be added on, both at the device and at a synchronization server, where some piece of software has to handle and process these changes.
While there are some attempts (for example, in frameworks built around the Gears project) to deal with synchronization at the application level by developing replay logs and so on within the application, I doubt that these will be robust enough to deal with large-scale implementations. Also, there is no server component along with these frameworks. And finally, as we’ve heard repeatedly over the last couple of days here, efficient use of the network is a key to application performance so a rough-and-ready application-level sync will usually not be the thing to use.
There’s one other way in which UltraLite has different design goals to SQLite, which may be important for BlackBerry users. SQLite is not geared for concurrent operation - it locks the database at a file level so two threads can’t make changes at once. This is fine - it’s a conscious part of the SQLite design, and you’re not going to get lots of apps hitting a database on a phone. But there is one case where having two threads work on the database at once can be very important, and that’s during synchronization. A BlackBerry user expects data changes to be applied “behind the scenes” while they carry on working with an application or with other applications, and SQLite’s concurrency model is not designed for that kid of operation. UltraLiteJ uses row-level locking so that you can synchronize in the background.
So SQLite on the BlackBerry might have a couple of effects on us at Sybase iAnywhere. First, some developers of apps that don’t need to run on existing devices will use SQLite instead of our UltraLiteJ database. That’s too bad. But it also means that a lot of BlackBerry developers get used to the benefits of a SQL database for storing and retrieving data in an efficient manner, so when they look to develop a real mobile application and want to move data on and off device, it will be a smaller jump for them to work with us at Sybase. Which way will the balance tip? I don’t know.

I guess I feel a bit like RIM must feel with the iPhone. Everyone talks about the devices, but RIM keeps arguing that it’s not just the device, there is a whole wireless infrastructure behind the BlackBerry that is every bit as important. For us, everyone talks about the databases, but there is a whole synchronization infrastructure that is every bit as important.

Will SQLite be made available only through a Gears framework, or will there be a Java API also? Will the Java API expose all the features of SQLite or just some, and will it be like the Android API or different? Lots of questions for tomorrow.