Ragtag

Creating and referencing an SQLite database is straightforward in an Android app. The documentation you'll find at the Android Developer site and around the web is more than enough to get up and running rapidly.

But as you can imagine, there are a few nasty pitfalls awaiting behind such a simple API. As I was playing with Android's database classes, I rapidly came across situations that required a deeper appreciation of their stucture and roles.

Database References

The docs caution that SQLiteOpenHelper's getReadableDatabase() method "may take a long time to return". I took this to heart and experimented with a database connection that stayed open across multiple queries.

I accessed a database of foods via getReadableDatabase() and queried it to populate a Cursor with all the records in the food table and display them on screen. However, I did not close() the database object after use, intending to call Cursor's requery() method if I needed to refresh the listing.

To check this was a valid way to proceed, I then added a new food to the table and requeried. I didn't get the output I was expecting, and the following error message turned up in the logs:

Invalid statement in fillWindow()

After a bit of head-scratching, I found my problem. In the code that added the new record, I was calling getWritableDatabase(), using the database that came back to save the new record, and then dutifully closing it. Since this was the only close() call, I wondered if it was somehow closing my 'readable' database too. This is the snippet that confirmed my suspicion:

SQLiteOpenHelper openHelper = new MySQLiteOpenHelper(); SQLiteDatabase writableDb = openHelper.getWritableDatabase(); SQLiteDatabase readableDb = openHelper.getReadableDatabase(); Log.d((writableDb == readableDb) ? "same" : "different");

Sure enough, "same" was logged. I never had readable and writable databases, just two differently named references. Calling close() shuts down both, because there was only ever one.

A dive into the SQLiteOpenHelper source confirms this. Each instance of this helper class creates and manage one instance of SQLiteDatabase at a time. There is a small implementation detail distinguishing getWritableDatabase() and getReadableDatabase(). The second method calls the first and returns the resulting database if it can. However, if this fails, it will then try to open the database in read-only mode.

Consequently, it is still good practice to call getReadableDatabase() if you will only be retrieving information, because there might just be an occasion when this will succeed where a request for a read-write database instance would fail. Whenever you're doing a mix of reading and writing, however, you need to keep it in mind that you're dealing with the same instance every time.

Concurrent access

While I was looking at the Android source I noticed that each SQLiteDatabase uses a ReentrantLock to allow only one operation at a time on the underlying database. This means that if you only ever create one SQLiteOpenHelper, and you're the only client of your database on the system, your queries will never encounter conflicts. Even if two threads try to query simultaneously SQLiteOpenHelper, they will not conflict. Instead, one will block until the other has finished.

Should you want to, you can get round this by disabling this conservative locking strategy, or by creating multiple instances of SQLiteDatabase which reference the same database. But in any normal application, I don't think you'll hit performance bottlenecks that require you to do this. Instead, play it safe, keeping one database reference which you can share between threads pretty freely.

So how should you manage your SQLiteOpenHelper to make sure there's only ever one of it? As is often the case with Android, there are conflicting recommendations out there, which I will try to summarise below. The decision over which one to use is going to come down to your own unique requirements.

The decision is also bound up with a choice of context. In case you're not familiar, an Android Context represents aspects of the system on which an application runs. It's used to access files and resources, so Android classes will often take one as a constructor argument. SQLiteOpenHelper is no exception. It needs a context so it can get hold of the physical file that represents your SQLite database. There are two key contexts, Application and Activity.

1. Tie it to an Activity context

This is a straightforward and very natural place to start. Instantiate an SQLiteOpenHelper in your current Activity's onCreate() method supplying a reference to the activity itself as the Context. As long as you close it down in onPause(), you can avoid leaking memory and leaving unclosed resources around the place. public class DatabaseActivity extends Activity { private SQLiteOpenHelper helper; /* Other code*/ @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); helper = new MySQLiteOpenHelper(this); } @Override protected void onPause() { super.onPause(); helper.close(); } }

As long as Android keeps to its contract regarding management of Activities, we know we can't have more than one active database connection. This feels like quite an Android-appropriate way to do things. We don't know the preceeding and subsequent Activities for sure, so it seems neat and tidy to restrict the database reference to just that activity which it exists to serve.

There's only one concern here, and that's performance. Opening a database can be a heavyweight operation, especially if you do any data copying or alteration at this point. Performing this operation every time you start a new activity could be too often. If you got to the point where responsiveness suffered, you'd need to find somewhere else to put your database.

2. Tie it to the Application context

An Application has a context that spans multiple Activities. In fact, Android guarantees that your Application context will exist as a single instance across your application. Grab it anytime using Context.getApplicationContext().

This post on StackOverflow tells you how to cause Android to return your own custom sublcass of android.app.Application when you call this method. In this subclass, you can keep references to objects which you want to manage across the whole life of your application.

Subclassing Application is quite similar to subclassing Activity. However, Android's guarantees about calling methods are weaker:

public class DatabaseApplication extends Application { private MySQLiteOpenHelper databaseHelper; /** * Called when the application is starting, before any other * application objects have been created. Implementations * should be as quick as possible... */ @Override public void onCreate() { super.onCreate(); databaseHelper = new MySQLiteOpenHelper(this); } /** * Called when the application is stopping. There are no more * application objects running and the process will exit. * Note: never depend on this method being called; in many * cases an unneeded application process will simply be killed * by the kernel without executing any application code... */ @Override public void onTerminate() { super.onTerminate(); databaseHelper.close(); } public SQLiteOpenHelper getSQLiteOpenHelper() { return databaseHelper; } }

I have not tried this in any serious setting yet, so I can't make any proclamations about the safety or performance of this method. I think, given the much weaker guarantees about onTerminate() being called, you would need to be much more diligent about manually closing database connections after use.

3. Tie it to a Singleton

Debates rage about whether or not you should use singletons at all, let alone in Android. They are considered an anti-pattern in some quarters. With specific reference to Android, you'll find contrasting opinions: here and here, for example.

I'm instinctively disinclined towards this method. It feels a bit like fighting the Android framework. Android asserts more control over objects in memory than you might be used to. It doesn't seem to like object that try to stay alive independent of the Application lifecycle. Consequently, I have steered clear of Singletons to date.

It's worth pointing out one thing you definitely don't want to do. Don't instantiate the SQLiteOpenHelper with the Activity Context and then try to keep it around in a singleton object. Holding on to an Activity context reference after Android has killed the activity results in large memory leaks, because the context is linked to all the UI objects which were created for it. Bad idea all round.

One final, basic tip, borne of much frustration

Beware of catching Exception when you try to access the database. Instead, catch the more specific SQLiteException, so that you know something's gone wrong with your database rather than your shonky businesss logic. Trust me on this and save yourself a facepalm.