The Mobile Developer

UltraLite: Relational Databases Made Small

First, a disclaimer. I work for Sybase (in its new iAnywhere Solutions subsidiary) and in this column I'm discussing a product developed by my group and to which I've made a small contribution.

Over a year ago, Sybase released version 6.0.2 of SQL Anywhere Studio, a suite of software built around a mobile- and workgroup-centered relational database called Adaptive Server Anywhere (which you may know of by its former names, SQL Anywhere or Watcom SQL). ASA made its mark by providing a small, fast, self-tuning database engine perfect for use in mobile applications like sales force automation or for more general-purpose applications where a high-end database server isn't required. What was new in 6.0.2 was the addition of UltraLite, a deployment technology that lets you bring the power of relational databases to small devices that are too limited to run a complete database server.

SQL Anywhere Studio is now at version 7.0. If you want to explore UltraLite, you can visit the link above and download a free evaluation copy of the Studio.

What UltraLite does is build a custom relational database engine for you based on the tables and SQL statements your application uses. You then embed that custom database into your application and link the application with the UltraLite runtime library. UltraLite takes care of storing, indexing, sorting and otherwise managing the data -- to your application it looks as if you're talking to a regular database server. There are some limitations to what you can do -- the SQL statements have to be known ahead of time (but placeholders are allowed), triggers and stored procedures aren't supported -- but an UltraLite database supports all the basic features of a relational database, including transactions and referential integrity. The footprint of an UltraLite database engine is very small, typically in the 50K-100K range for the C/C++ version.

A very important feature of UltraLite is its built-in support for database synchronization. An UltraLite database can exchange data with any ODBC-compliant database via the intermediary of the MobiLink server, which is also included with SQL Anywhere Studio. This frees you from having to write your own synchronization code, which can be quite complex. MobiLink also includes sophisticated conflict resolution capabilities, which is extremely important if you need to share data across hundreds or thousands of disconnected clients.

UltraLite currently supports several C/C++ platforms: Palm OS, Windows CE, VxWorks and others. It also supports Java, both Java 1.1 (so it can run on the EPOC platform) and Java 2, in applications or applets.

The UltraLite development process is fairly straightforward. First you define a reference database, which is an ASA database whose tables correspond to the tables in the "real" or consolidated database. Unlike the reference database, the consolidated database doesn't have to be an ASA database, it just has to support ODBC. Out-of-the-box support is provided for Sybase, Microsoft, IBM and Oracle databases.

Once you've defined the reference database, you then define the SQL statements your application uses. There are several ways to do this, either by embedding SQL statements directly into your C/C++ code, using a C++ table-based object API, or by inserting them directly into special tables in the reference database. The latter is in fact the only choice for Java, since UltraLite will then define a custom JDBC driver based on those statements.

The next step is to generate the UltraLite database engine. The generation process uses the tables in the reference database and the statements you've defined as its input. The result is a small database engine containing only the features necessary to support what your application is using.

If your application needs to synchronize its data with an external database, you would then configure the MobiLink server appropriately.

The synchronization protocol used by UltraLite to synchronize with a MobiLink server is a stream-based send-reply-send model with minimal chattiness. All data is packed to keep the stream size down.

The three steps of synchronization are simple to explain: the UltraLite application first sends all its changes (updated rows, new rows, and deleted rows) to the MobiLink server. This is the upload stream. The server processes the changes and then sends back its own changes, the download stream. The UltraLite application applies those changes and then sends a short acknowledgement back to the server.

For security, the synchronization streams can be encrypted and certificates exchanged. UltraLite currently uses Certicom's security libraries.

To communicate with the server, an UltraLite application can use a variety of methods. The simplest is to open a direct socket connection to the server. Or it can open an HTTP connection. The C/C++ version can also use direct serial connections where available -- and on Palm devices synchronization can occur automatically during a HotSync.

UltraLite works well with wireless applications that don't need a continuous server connection. In other words, if your application is content to mostly work offline from a local data cache -- whether it's to save on expensive airtime or to handle out-of-range situations -- then UltraLite is a good solution. All you need is a wireless TCP/IP stack for the client device, such as the one sold by NetTech. (A version of UltraLite for RIM pagers is currently in beta and it supports direct radio synchronization.) For a golf tournament, for example, we've demonstrated UltraLite running on Palm devices equipped with radio sleds and synchronizing wirelessly back to a server that tracked everyone's scores.

Where UltraLite doesn't work is in browser-based devices where the application actually resides on the server and the user interface is delivered to the client using a page description language like WML or HDML. UltraLite is meant to reside in a client-side application, so for now it won't work with the current generation of cellphones. But as cellphones evolve and allow custom applications to be installed, that will change.

At some point in the near future I'll be talking about the iAnywhere Wireless Server and other wireless initiatives from Sybase, but my next column will be a report on what I saw at the JavaOne conference.

Eric Giguère is the author of Palm Database Programming: The Complete Developer's Guide and an upcoming book on the Java 2 Micro Edition. He works as a developer for iAnywhere Solutions, a subsidiary of Sybase, Inc. Visit his website at www.ericgiguere.com or send him mail at ericgiguere@ericgiguere.com.