Going Mobile: Data Management Solutions for Mobile Devices

Feature

*** This option is free for
development and evaluation use. Installation requires a registration
key.

Legend:

	– Included feature
O	– Paid option
n/a	– Option not available in this edition

Licensing for SQL Anywhere is highly
dependent on what you need to do with it, who will be using it, how many will
be using it, and what platform you are targeting. For instance, a single CPU
license of SQL Anywhere Advanced for Sun Solaris SPARC is $21,999. That doesn’t
include an Upgrade Subscription Plan (USP) or a support level. A SQL Anywhere
Advanced 25 user license (only available for internal use) on the same platform
is $11,549. In contrast, a SQL Server Workgroup Single CPU license on Windows
is $2725. However, the developer edition is free. This information is for SQL
Anywhere 11.0.1 as pricing is not yet available for SQL Anywhere 12 at the time
of this writing.

SQLite

SQLite is an embedded database engine,
which is public domain. It consists of a single cross-platform file and a library
that can be called dynamically or integrated into the application.
Additionally, it can be configured to run in-memory. Popular applications like
Apple’s iTunes, Google Gear, Mozilla Firefox, and Adobe AIR utilize this
database engine.

In its current revision, 3.6.23.1,
SQLite supports most of the SQL-92 standard. It has limitations in areas such
as ALTER TABLE, trigger support, and writes to views (see http://www.sqlite.org/omitted.html
for a complete list). The user community has contributed to the product to
increase the feature set such as Google’s contribution to full-text search. The
SQLite Consortium supports the continued growth of the product. This includes
companies such as Symbian, Adobe, Bloomberg, Oracle, and Mozilla.

A fully transactional database engine,
SQLite has the drawback of needing to lock the entire database for a write
operation. Because of the database being a single file, it can allow multiple
concurrent reads, but only one write operation at a time. Write operations will
fail if any other operation prevents the locking, but retry behavior can be
configured.

SQLite does not provide for
replication or synchronization of its data with another database. It is less
suited for sales force automation, for instance, than it would be for a
consumer application on a smart phone. Its tiny footprint of less than 275K and
maximum database size of a tera- or even peta-byte make it a compelling choice
where simplicity is key.

SQLite deployment is also simplified.
No installation is required and no server is created. The SQLite executable can
simply be copied to the target device and executed. The database file itself
can exist anywhere on the file system.

An interesting aspect of SQLite is
dynamically typed columns, called manifest typing. In this database engine, the
columns have a declared type, but any type of data can go in any column (with
the exception of integer primary keys). This means a string value can go into a
datetime column. The engine will attempt to turn the string into a datetime,
but if it fails, the string is still stored in that datetime column.

Sqlite3 is an interface for creating
databases and tables, querying, and data manipulation in SQLite. It runs as a
Tool Command Language (TCL) command. Multiple commands are implemented as
options for sqlite3 such as eval or collate.

Documentation for the database engine
is provided on the official SQLite site, http://www.SQLite.org
. In addition to the general documentation, the supporters have also provided
sample code, upgrade guidance, and API references.

Whereas SQLite is free to develop with
and deploy, technical support will cost. There are also extensions to SQLite
that are not public domain such as the encryption library. This library has
support for reading and writing encrypted databases using these algorithms:
RC4, AES-128 in OFB mode, AES-128 in CCM mode, and AES-256 in OFB mode.

Conclusion

SQLite is by far the simplest and
smallest of the mobile database and is already heavily used by many well-known
companies and devices. However, it should only be used in situations where a
complex engine is not needed and data synchronization is not a concern.
Concurrency should also be a top consideration due to the database locking on
writes.

Microsoft SQL Server is another free
database and is more complex and capable than SQLite, however it is limited in
its capabilities such as a lack of support for stored procedures and XML data
types. Its integration with other products from Microsoft (such as SQL Server
Management Studio, SQL Server Analysis Services, and Visual Studio) makes it a
strong contender depending on your needs. Plus you can’t really beat free.

DB2 Everyplace has one definite
advantage over SQL Server Compact with its cross-platform support. There is a
slight cost for a license of either of its two editions. Database edition is
good for solutions not requiring synchronization or frequent updates.
Otherwise, the Enterprise edition should be used.

The most robust and therefore most
expensive choices for embedded databases are Oracle and SQL Anywhere. Both
vendors supply a scaled-down lighter engine as well as the robust offering.
However, cost could be prohibitive. The needs of the system would have to
justify the increased footprint and cost.

As always, the devil is in the
details. This is just an overview of the capabilities of each of these mobile
database offerings. Requirements of your system should drive further
examination of a hopefully narrower set of possible mobile solutions. For
instance, if your system needed to work with spatial data, you could narrow the
list of possible mobile databases and research those further to determine the
extent of the support for spatial data.

For More Information

SQL Server Compact 3.5

Wikipedia: SQLite

SQLite

Oracle Database Lite 10g

Oracle Database Lite Client

Oracle Database Lite Mobile Server

Sybase UltraLite

Sybase SQL Anywhere

DB2 Everyplace Database Edition

Gain the Competitive Advantage: Data Management Solutions for Mobile Devices

»


See All Articles by Columnist
Deanna Dicken