Android SQLite for the DBA

As mobile devices become more and more important in the enterprise, you, as an enterprise database architect, may find yourself asked to participate in the design of the persistent data store in an Android application. Before you wade in, there are a couple of things you should know.

Introduction to SQLite

Android uses the open source database engine, SQLite. It is a small, server-less library and has features that are pretty attractive in the mobile environment. Data stored in SQLite databases is persistent across processes, power-cycling, and, usually, across upgrades and re-installs of the system software.
SQLite is an independent, self-sustaining OSS project. Originally developed in 2000 by D. Richard Hipp, it was quickly and broadly adopted as a way for applications to manage their persistent, structured data. A group of dedicated developers supports the large user community. SQLite is embedded in high-profile projects like Apple Mail, the Firefox Web Browser and Intuit's TurboTax.
Reliability is a key feature of SQLite. More than half of the project code is devoted to testing and each release of SQLite is tested very carefully, especially under failure conditions. The library is designed to handle many different failures modes gracefully, including low memory, disk errors, and power outages. This is very important on a mobile platform where life is just way less predictable than it is on a device confined to a server room. If something goes wrong - the user removes the battery or a buggy app hogs all available memory - SQLite managed databases are unlikely to be corrupted and any user data is likely safe and recoverable.
There is comprehensive and detailed documentation about SQLite at the project website:
http://www.sqlite.org/docs.html.
On the downside, however SQLite is really not an RDBMS. Although you talk to it using SQL, many of the key features that you'd expect from a relational system are completely missing. As built for Android, SQLite does support transactions but it does not support either referential integrity or strong typing. It's own documentation suggests that one should think of SQLite "not as a replacement for Oracle, but as a replacement for fopen()"

Datatypes in SQLite

Consider, for instance, the following interaction:

sqlite> create table test (
...> c1 biginteger, c2 smalldatetime, c3 float(9, 3));
sqlite> insert into test values("la", "la", "la");
sqlite> select * from test;
la|la|la
sqlite>

Although SQLite syntax supports a wide variety of data types -- TINYINT, BIGINT, FLOAT(7, 3), LONGVARCHAR, SMALLDATETIME and so on -- the column type is really no more than a hint to help SQLite choose an efficient internal representation for the data stored in the column. SQLite determines the internal storage for a column using a handful of simple rules that regulate "type affinity". These rules are very nearly invisible when inserting or recovering data from the database. They appear to affect only the amount of space that a given dataset occupies on disk. There are full details for type affinity at:
http://www.sqlite.org/datatype3.html#affinity
In practice, many developers just restrict themselves to four primitive internal storage types used by SQLite: integer, real, text, and blob, and explicitly represent booleans as integers and timestamps as text. Especially the latter, representing timestamps as text, can be tricky. There are details here:
http://www.sqlite.org/lang_datefunc.html

Primary Keys SQLite

One of the most important concepts in relational databases is the primary key. No, no... don't worry: SQLite does support primary keys! It even supports composite (multi-column) primary keys. Beware, though, of primary keys that are not integer typed. In addition to implying that a column is UNIQUE the primary key constraint should also imply that it is NOT NULL. Unfortunately, because of an oversight in its early versions, perpetuated for backwards compatibility, SQLite allows any primary key type other than integer to contain NULL values. As shown below -- because each NULL is a distinct value different from any other NULL -- SQLite permits a primary key column to contain multiple NULLs and thus multiple rows that cannot be distinguished by their primary key. If your table needs a primary key, consider making it an integer primary key.

sqlite> create table test (key text primary key, val text);
sqlite> insert into test(val) values("foo");
sqlite> insert into test(val) values("bar");
sqlite> select * from test where key is not null;
sqlite> select * from test where key is null;
|foo
|bar
sqlite>

A related issue arises from a common use of of SQLite databases in Android. The typical way of viewing a dataset in Android employs a Cursor, the standard class that an application uses to get data from a table, combined with a ListAdapter, a class that relates each row in a cursor to its representation in a cell on the screen. The implementation of ListAdapter requires a column named "_id" that uniquely identifies each row in the cursor: essentially a primary key. This is not a requirement that every table contain a primary key column named "_id". It is necessary only that every join/projection viewed through a ListAdapter have such a column.
There are several ways to do this. If the table contains a primary key whose name is "_id" no further work is required. Not only that, but naming the row "_id" gives future developers a pretty good hint about the way the table is being used.
Another possibility, though, is that a primary key with another name can be renamed to "_id" in the query:

select part_num _id, supplier, price from parts;

This works and may allow db columns with more descriptive names.
One other possibility come from the fact that every SQLite table has an implicit column named "rowid". This column -- unless it is explicitly redefined at table creation -- contains a 64 bit integer that identifies the corresponding row: an implicit integer primary key. That provides another way of fulfilling the need for an _id column:

select rowid _id, supplier, price from parts;

Foreign Keys in SQLite

SQLite doesn't support the FOREIGN KEY constraint at all, as demonstrated here:

sqlite> create table people (
   ...> name text, address integer references addresses(id));
sqlite> create table addresses (id integer primary key, street text);
sqlite> insert into people values("blake", 99);
sqlite> insert into addresses(street) values ("harpst");
sqlite> select * from people;
blake|99
sqlite> select * from addresses;
1|harpst
sqlite> select * from people, addresses where address = id;
sqlite>

In a database engine that supported referential integrity, the first insert statement would fail with a foreign key constraint violation. In fact, the attempt to create the table in the first create table statement would fail for the same reason.
There is good news here. Android programs must create their databases programatically. The method onCreate and onUpdate in the SQLiteOpenHelper class are responsible for running the DDL necessary to create any database objects required by the application. The process of creating and deleting tables is much less likely to cause errors if the error checking is neither incremental nor part of the database system. The code that creates the database can -- and probably should -- verify the schema in its entirely, once it is complete.
Because SQLite doesn't support referential integrity, code using the database must be prepared to handle error cases. For example, when designing a schema for an Android application it may well make sense to replace the standard practice of defining dimension tables -- tables that enumerate the values legal in the column of some other table -- with checks in code.

Data safety in SQLite

Finally, it is well to remember that data stored in a SQLite database is not automatically encrypted. The Android data sandboxing mechanism is a pretty good first line of defense. Data that belong to one application is protected by Unix file permissions from access by all other non-root applications. For truly sensitive data, though -- data that must be protected even if a device is lost or stolen -- you might consider encrypting the data as it is inserted. If encrypting seems advisable, have a look at the SQLCipher project: http://sqlcipher.net/

In conclusion...

As a tool embedded on portable devices, SQLite is simply not an RDBMS. Many of the features that make a true relational system useful are completely absent: strong typing, reliable primary keys, and referential integrity. Even as simple repositories for structured data, however, SQLite databases systems can benefit from the skills that an experienced DBA brings to the table. Good organization and reasonable normalization can be a big help in creating an application that isn't already painted into a corner as it expands to support new features.

Sources:- http://marakana.com/s/android_sqlite_for_the_dba,1213/index.html