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.

AnDevCon III

Had a really great time speaking at AnDevCon III, yesterday!  The audience was awesome and turned what I expected to be a 40 minute talk into 70+ minutes.

All the materials from the talk are on GitHub, here:

https://github.com/bmeike/AnDevCon.git

Singletons in Android

There was a surprisingly acrimonious discussion about this, in the Android Developer’s Google Group, recently. Apparently the topic can be confusing.

The issue is that developers occasionally find themselves in need of a singleton. As a pattern, I would say that use of singletons has pretty well been discredited (see, e.g., WhySingletonsAreControversial ). Still, in Android, in a muli-activity application, it is entirely likely that there will be shared data and the desire to store that shared data in some place that is accessible to all application activities.

The problem with Singletons is that they really don’t exist. An instance of some kind of state is only a singleton within some context. A “singleton” on your phone is, clearly, not the same as the instance of the same singleton on my phone. It is even possible that the instance of the singleton on my phone this morning isn’t the same as the instance that was there last night, before I rebooted the phone. This only becomes a problem if the number or lifespan of the singleton surprises someone.

Before Java 5, the typical definition of a singleton, in Java, looked like this:

public final class Singleton {
    private static final Singleton instance = new Singleton();
    public static Singleton getInstance() { return instance; }

    private Singleton() {}
    // ...
}

After Java 5, the recommended implementation of a singleton looked like this:

public enum Singleton {
    SINGLETON;

    // ....
}

The idea is that there is only a single instance of the definition of a class (or enum) so we make that single class instance hold and return the singleton instance of the class. There are tricky extensions of this — usually ways to make it initialize lazily — but this is the gist.

In the Java community, the surprises typically showed up in applications that used multiple ClassLoaders. ClassLoaders are the things that read class definitions from, usually, the file system, and convert them into running code. The surprise comes when a developer doesn’t realize that a class definition is unique per ClassLoader (an object of which they may never even have heard) not per application. The Tomcat application server, for instance, loads webapps (.war files) each in its own ClassLoader. If you make changes to an app and reload it, it gets loaded in a new ClassLoader. If the previous version of the application hasn’t terminated yet it is entirely possible to have two distinct copies of the “same” singleton, one in each ClassLoader. …and that’s surprising.

It is entirely possible to for the same thing to happen, in Android. Android’s similarity to Java absolutely extends to its definition of classes as unique per ClassLoader. Multiple ClassLoaders: multiple instances of your singleton. You can demonstrate this with an app of about 10 lines. At least at this point, though, most Android applications don’t make use of ClassLoader magic and so most developers aren’t getting this particular surprise.

When Android developers want a singleton — perhaps the DAO or a place to remember a running AsyncTask — they might consider keeping the reference to it in a custom subclass of Application. If you read the official documentation for the Application class, though, you’ll find this comment, barred for emphasis:

There is normally no need to subclass Application. In most situation, static singletons can provide the same functionality in a more modular way. If your singleton needs a global context (for example to register broadcast receivers), the function to retrieve it can be given a Context which internally uses Context.getApplicationContext() when first constructing the singleton.

This is all fine, so far. But here comes a surprise. The Android OS may completely terminate your application and then recreate it. When the new process is created, your classes are loaded and initialized. Exactly how to describe this terminate/reload event was the source of significant heat in the Google Groups discussion. I’ll resist the urge to poke fun at the guys that insisted that this was not a suprise because the reload happens in a new process [The classes are not reloaded! Rather there is a new "process". What is a "process"? It is an undetectable entity that makes it appear that your classes have been reloaded...] because they are exactly right. An Android application will, frequently, span several processes. This is not something that is common in, say, the desktop world. It may be surprising.

The point is that you cannot put program state into class data members and expect it to stay there. Frequently, this isn’t a problem. If you use a class data member to store a reference to something idempotent, e.g., your DAO, you don’t care which specific instance you happen to use. That is, this code works just fine:

public class MyApplication extends Application {
   private static final MyDAO db = new MyDAO();
   public static MyDAO getDB() { return db; }

   // ...
}

This code, however, won’t work:

public class MyApplication extends Application {
    private static long lastUpdate;
    public static void setLastUpdate(long udate) { lastUpdate = udate; }
    public static long getLastUpdate() { return lastUpdate; }

    // ...
}

… or, at least, you may be surprised to find that getLastUpdate returns 0, even though the code has called setLastUpdate() at some point. Suppose an Activity in the application looks like this:

// ...
setContentView(R.layout.main);
((TextView) findViewById(R.id.myText))
    .setText(String.valueOf(getLastUpdate()));
((Button) findViewById(R.id.myButton)).setOnClickListener(
    new Button.OnClickListener() {
        @Override public void onClick(View v) {
            MyApplication.setLastUpdate(System.currentTimeMillis());
        } } );
// ...

If I push the button, then the Home key and then run a bunch of other apps (on my Nexus S, about a dozen), when I return to this app, the text view will read 0, despite the fact that I definitely pushed the button.

I don’t think anyone is claiming that this is a bug. …and it certainly helps to understand why it happens. As my colleagues point out, it happens because the application survives process boundaries. It is definitely surprising, though, and it puts that quote from the documentation, cited above, in a new light.  Neither the application object nor a static singleton can be expected to hold persistent state for the life of the application! It just won’t work.

What might be a bug is that it is hard to tell when a process ends (thus my jibe about processes being undetectable). I have empirically determined that Application.onTerminate() is not always called, and I have from an authority I respect, that it is, actually, never called (documentation to the contrary). It is probably best to design so that you don’t depend on knowing, anyway. To do that, your static fields must appear to have a value that is constant as of creation. Making them final is a good start.

In some ways, this is the opposite of the problem in Tomcat.  Whereas, in affected web apps, a single object might, at some point in its lifetime, have references to multiple, inconsistent copies a given singleton, in Android a single object, over its lifetime, can’t get a reference to even one instance that stays consistent.  So, don’t be surprised.  It is really easy to create an application in which an Activity, for instance, retains some state for much longer than fields in the Application object, or its own static fields. Nothing new: we’ve all initialized apps from the file system or from a database, before. Usually, though, we do it at “startup” and we have some definite ideas about when “startup” happens.. That’s not how Android works. Serialize your state into a Bundle or Sqlite, if you need to keep it around.

Multi-Platform Fragments, the Webcast

I’m going to be presenting the Multi-platform Fragments topics that I blogged about, here, previously, in two webcasts.  The first is part of the AT&T Developer program on Aug 25, 10AM PDT.

I’ll be presenting some of the same material one more time on Friday, Sept 9 as part of the O’Reilly Webcasts series.

Stop by and ask questions.

 

 

It’s in the stores!

The new book is out!  You can get your copy here!

The combination of perspectives from the 4 authors have, I believe, built something way better than might have happened if we’d each written our own.  Obviously, I’m prejudiced.  Check it out for yourself.

Multi-platform fragments, Part II

In Part I I describe a pretty well-known way of using resources to make an application look good on a variety of devices.  It works pretty well as far as it goes, but 20% of Android users out there are still running Eclair, and some of them are using HVGA screens.  For them we’ll need, reluctantly, to back away from fragments.  It turns out that that isn’t so bad either.

First move the existing layout directories, from Part I, “layout” and “layout-port” so that they apply only to large screens.  Rename them “layout-large” and “layout-large-port”.  The application will continue to behave as it did in Part 1, as long as the device screen qualifies as “large”.   BTW, here’s some handy code that will let you know what kind of screen Android thinks it is dealing with:

Configuration config = getResources().getConfiguration();

Log.d(TAG, "Orientation: " + config.orientation);

Log.d("TAG, "Size: " + (config.screenLayout & Configuration.SCREENLAYOUT_SIZE_MASK));

Here’s a new version of the main layout.  Put it in “layout”, the default for normal (and small) platforms:

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout

xmlns:android="http://schemas.android.com/apk/res/android"

android:orientation="vertical"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

>

 

<ListView

android:id="@+id/contacts"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

android:layout_weight="2"

/>

</LinearLayout>

The trick is that this version is completely missing the FrameLayout that the previous versions used to position the fragment. Here’s a new version of onCreate that determines whether to use fragments, or not:

@Override

public void onCreate(Bundle state) {

super.onCreate(state);

 

setContentView(R.layout.main);

 

final boolean useFrag

= null != findViewById(R.id.contact_detail);

 

if (useFrag) { installFragment(); }

 

//...

}

It only installs the visible fragment showing the contact details if there is a place to put it.  On small screens, then, no fragment will get created.

There’s just one more piece: what to do when there’s a click in the list of contacts.  In the old version we created a new fragment and put it on the stack.  In this version, though, we either stack a fragment or a new activity, depending on whether there’s a place to put the fragment or not.  The activity’s onClickHandler calls launchDetails to navigate to the contact details, how ever they are represented in the UI.  It looks like this:

void launchDetails(int pos, boolean useFrag) {

//... get the name and id from a cursor

 

if (useFrag) { stackFragment(id, name); }

else { stackActivity(id, name); }

}


private void stackFragment(String id, String name) {

FragmentTransaction xact

= getSupportFragmentManager().beginTransaction();


xact.replace(

R.id.contact_detail,

ContactDetailFragment.newInstance(id, name),

FRAG_TAG);

xact.addToBackStack(null);

xact.setTransition(FragmentTransaction.TRANSIT_FRAGMENT_OPEN);

xact.commit();

}


private void stackActivity(String id, String name) {

Intent intent = new Intent();

intent.setClass(this, ContactDetailActivity.class);

intent.putExtra(ContactDetails.TAG_ID, id);

intent.putExtra(ContactDetails.TAG_CONTACT, name);

startActivity(intent);

}

That’s it!  When this application runs on a small screen, it will launch a new activity into the back-stack, instead of a new fragment.  It looks like this:

No frags1 No frags2

This idea comes from one of Diane Hackborne’s posts on the Android Developer’s blog.  It’s so tasty, though that it bears repeating.  The rest of the docs for fragments are here.

There are a couple of other things about this app that are worthy of note.  First, as is, it will treat an xlarge screen like a small or a normal one.  That’s probably not right.  Also, I suppose, how it is that the demo has an Activity and a Fragment with the same behavior could use a little explanation.

You can find all the code for this demo application here.

Multi-platform fragments, Part I

This is going to be a bit long.  I’ve been away from Android for a while, mostly looking at embedded Linux and Meego… but that’s another story. I’ve been meaning to write about some clever tricks for building applications that can work on a wide variety of platforms.  I’ll do it in two posts.

The advent of tablets/slates/pads, brings the problem of making an application’s UI look good on a wide variety of devices to a whole new level.  Until Honeycomb, with device independent pixels and a couple of fine tuned bitmaps, you could probably cover most of the devices out there.  Now that Fragments have arrived, an app may actually have substantially different behaviours on different platforms.  On a small screen it may show only a single window at a time and use the back button for navigation.  On a larger screen, however, it might work need multiple windows to make use of the space.  Sounds like a nightmare…

It turns out that it isn’t so bad.  A little experimentation and a close read of the docs reveal couple of cute tricks will contain the whole issue in just a few lines of code.

First off, use the ACL (Android Compatibility Library).  An app that is based on Fragments, without the ACL cannot run on pre-Honeycomb Android.  Game over.  In order to support a wide variety of platforms, you’ve got to code to the ACL.

To use it, you just copy it from its home:

$ANDROID_SDK/extras/android/compatibility/v4/android-support-v4.jar

… to a directory named “lib” in your project 9and add it to your Eclipse build path).

Consider an example app that displays information about your Contacts. It uses fragments and is meant for a tablet in landscape orientation Here’s its layout, the file “main.xml” in the directory “res/layout”.

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout

xmlns:android="http://schemas.android.com/apk/res/android"

android:orientation="horizontal"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

>

<ListView

android:id="@+id/contacts"

android:layout_width="0dp"

android:layout_height="fill_parent"

android:layout_weight="1"

/>

<FrameLayout

android:id="@+id/contact_detail"

android:layout_width="0dp"

android:layout_height="fill_parent"

android:layout_weight="2"

android:background="@color/blue"

/>

 </LinearLayout>

The FrameLayout will be replaced by a fragment, in the code.  That looks like this:

public class ContactViewer extends FragmentActivity {

private static final String FRAG_TAG

= ContactViewer.class.getCanonicalName() + ".fragment";

public void onCreate(Bundle state) {

super.onCreate(state);


setContentView(R.layout.main);

 

installFragment();

 

// ...

}


private void installFragment() {

FragmentManager fragMgr = getSupportFragmentManager();

 

if (null != fragMgr.findFragmentByTag(FRAG_TAG)) { return; }

  FragmentTransaction xact = fragMgr.beginTransaction();

xact.add(

R.id.contact_detail,

ContactDetailFragment.newInstance(null, null),

FRAG_TAG);

xact.commit();

}

Pretty straighforward fragment code.  ContactDetailFragment is the fragment class and R.id.contact_detail is where it goes, the FrameLayout.  I’ve mentioned, previously, using fragment’s tagging facility to prevent leaking them.

If you run this on a tablet, landscape WXGA, it looks pretty good:

Landscape

Running it on a phone, in portrait WVGA800, is another story:

Smooshed landscape

The screen is acutually still big enough to support two windows but the proportions have to be different and they have to be layed out vertically.  This turns out to be dead simple.  In the “res” directory, create a new sub-directory named “layout-port”, next to the original “layout”.  Copy “main.xml” into it and reorient it for the smaller portrait screen:

<?xml version="1.0" encoding="utf-8"?>

<LinearLayout

xmlns:android="http://schemas.android.com/apk/res/android"

android:orientation="vertical"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

>

<ListView

android:id="@+id/contacts"

android:layout_width="fill_parent"

android:layout_height="0dp"

android:layout_weight="2"

/>

 

<FrameLayout

android:id="@+id/contact_detail"

android:layout_width="fill_parent"

android:layout_height="0dp"

android:layout_weight="1"

android:background="@color/blue"

/>

</LinearLayout>

We might copy “contact_detail.xml” over as well and tweak font sizes and such a little so that everything looks nice.  With no code changes the app UI will now look pretty good on a wide variety of screens — including the Honeycomb tablet rotated to portrait.

Portrait

The Android system allows you to group resources according to the configuration of the runtime device screen.  The documentation has the details of how this works.  Basically, Android will prefer resources from a subdirectory of “res” the that most closely corresponds to the device on which your app is running.  It is all covered here:

http://developer.android.com/guide/practices/screens_support.html

All of this was introduced back in the Eclair days.  In Part II, though, I have a neat trick that’s buried in the Honeycomb docs that makes this same code compatible with even older and smaller phones.

Follow

Get every new post delivered to your Inbox.