Tuesday, August 3, 2010

Loading large reference database in Android

Reference data normally refers to (mostly) read-only data that is used to validate or resolve other pieces of data. For example, a list of postcodes and suburbs, that can be used to provide auto-suggestions in a UI or validate a user's address.

I recently developed an Android (version 2.1 / Eclair) application that used a large set of reference data, stored in a SQLite database. The fact that this was targeted for Android smartphones introduced some constraints in the loading of this data that would not be present for a desktop or Web application, primarily processing time and storage space.

The initial design was based on my prior experience working on Web applications. It involved packaging a largish (~10MB) text file into the apk archive. The application performed a one-time initialization step that loaded this file, parsed it and executed SQL statements to insert rows into the database. The application then accessed the reference data via a subclass of SQLiteOpenHelper. It soon became apparent that this would not work in the Android world, due to the following reasons.
  • Loading the database, even if its a once-off, took too long. People are not likely to want to wait for many more minutes, especially after waiting for the 10MB download.

  • The database storage took up 10+MB, and when combined with the input text file, totalled 20+MB. Android 2.1 required the application to be installed onto the phone's limited internal memory, where 20MB was a relatively massive chunk.

  • The input text file in the apk archive could not be removed after initialization, so it just uselessly consumed precious phone memory.
After a few tries, I arrived at the following final design that seemed to solve these problems.

Preload database

The database loading code had to be removed from the Android code and rewritten as a separate program. This program read the text input file and inserted the rows into a SQLite database, with the database file stored on my desktop. Note that the primary key column of the reference table should be named _id and the following must be added to the database for it to be usable by Android:

CREATE TABLE android_metadata (locale TEXT);
INSERT INTO android_metadata VALUES('en_US');

This preloaded database file was then uploaded to a Web site so that it could be downloaded by the Android application.

Download database

The Android application didn't need to be packaged with the text input file any more, hence trimming the apk archive from 10+MB to a few hundred KB. The application's starting Activity would check and download the database file if it didn't already exist on the SD-Card. According to the Android Dev Guide on Data Storage, the proper place to save the database file would be:

  • for Android 2.2 and above :- the directory returned by getExternalFilesDir()

  • for Android 2.1 :- the directory starting with getExternalStorageDirectory(), then appended with /Android/data/<package_name>/<file_type>, which resolved to something like /sdcard/Android/data/com.mycompany.myapp/db

I had to use the latter option as I was targeting 2.1.

It was important to ensure that the UI was not frozen during the few minutes it took to download the 10+MB database file. That meant that the downloading code must be executed in a separate thread to the main UI thread. The best way to achieve this was to create a subclass of AsyncTask and put the downloading code in its doInBackground() method. This class also displayed a ProgressDialog to keep the user informed, and acquired a WakeLock (SCREEN_DIM_WAKE_LOCK) to prevent the phone from going to sleep. Another important thing to remember was to prevent the screen orientation from changing when the phone was flipped over while the ProgressDialog was showing, otherwise the application would crash when the dialog was dismissed. This was achieved by temporarily setting the requestedOrientation property of the activity to "no-sensor". Here is the skeleton code of the AsyncTask:

// FileDownloader is my own delegate class that performs the
// actual downloading and is initialized with the source URL.
public class InitializeDatabaseTask extends
AsyncTask<FileDownloader, Integer, Object> {
private ProgressDialog progressDialog;
private File dbFile;
private PowerManager.WakeLock wakeLock;
private Activity activity;
private transient int originalRequestedOrientation;

public InitializeDatabaseTask(Activity activity, File dbFile) {
this.dbFile = dbFile;
this.activity = activity;

wakeLock = ...; // Obtain a wakelock for SCREEN_DIM_WAKE_LOCK
progressDialog = ...; // Create a ProgressDialog instance with title, message ,etc

protected void onPreExecute() {
originalRequestedOrientation = activity.getRequestedOrientation();

protected Object doInBackground(FileDownloader... params) {
FileDownloader downloader = params[0];
try {
} catch (IOException e) {
throw new AndroidRuntimeException(e);

return null;

protected void onPostExecute(Object result) {

The main Activity used InitializeDatabaseTask like so:

File dbFile = ...; // File pointing to /sdcard/Android/data/com.mycompany.myapp/db
new InitializeDatabaseTask(this, dbFile).execute(new FileDownloader(DOWNLOAD_DB_URL));

Mount SD-Card in emulator

I developed the application mainly using the Android SDK emulator in Eclipse. The emulator did not mount any SD-Card by default. In order to test the downloading code, the emulator must be set up with a SD-Card image file, like so (on my Linux system):

$ android-sdk/tools/mksdcard 64M ~/.android/avd/Android.2.1.avd/sdcard.img

This created a 64MB image file (more than enough for the data) in a special Android avd directory, so that it would be automatically mounted by the emulator.

Implement custom SQLite helper

Finally, I had to write my own custom version of the SQLiteOpenHelper to access the database file from the SD-Card, because the standard one would only read from the default phone internal memory. Given that the reference data would be read-only, this custom class was a lot simpler than SQLiteOpenHelper and only needed to open a read-only database connection. The code:

public abstract class ExternalStorageReadOnlyOpenHelper{
private SQLiteDatabase database;
private File dbFile;
private SQLiteDatabase.CursorFactory factory;

public ExternalStorageReadOnlyOpenHelper(
String dbFileName, SQLiteDatabase.CursorFactory factory) {
this.factory = factory;

if (!Environment.getExternalStorageState().equals(Environment.MEDIA_MOUNTED)) {
throw new AndroidRuntimeException(
"External storage (SD-Card) not mounted");
File appDbDir = new File(
if (!appDbDir.exists()) {
this.dbFile = new File(appDbDir, dbFileName);

public boolean databaseFileExists() {
return dbFile.exists();

private void open() {
if (dbFile.exists()) {
database = SQLiteDatabase.openDatabase(

public synchronized void close() {
if (database != null ) {
database = null;

public synchronized SQLiteDatabase getReadableDatabase() {
return getDatabase();

private SQLiteDatabase getDatabase() {
if (database==null) {
return database;

A concrete subclass of ExternalStorageReadOnlyOpenHelper was created to query the reference data via the SQLiteDatabase object returned by the getReadableDatabase() method.

The databaseFileExists() method allowed the main Activity to check if the database file already existed to decide whether to initiate download.


This final design had greatly improved the application in the following ways :
  • The user only had to download a small apk file initially, greatly reducing the barrier to installation.

  • The separate download step for the database file presented an opportunity to tell the user what was happening and to use Wi-Fi if available.

  • Most of the data resided on the SD-Card, where space was much more abundant.

  • There was ever only one copy of the reference data on the phone, no redundant duplication.

  • The main application code could be updated without having to download the reference data.


  1. Thanks for the in-depth explanation of how to get the database onto an SD card. Very useful stuff.

    I've got a situation where my app is out on the Market, and the database has been populated for users. Do you know a way to transfer the internal database onto one that was created on the SD card?

  2. The internal memory database is stored inside the
    directory. Your app cannot access the top level /data directory, but not sure if you can directly access the db file using a fully qualified path.

    If you can, then I guess you can publish a new version of your app that first checks if the database is already on the SD-Card. If not, copy the db file from internal memory to the SD-Card, then drop all tables from the internal memory database.

    WARNING: I have not actually tried this, so please test this first carefully! Good luck.

  3. Many thanks for the blog. This is the first time I have seen it mentioned that an app can access a database stored on the SD card. For an app for personal use only (not for distribution) where the data will be updated frequently this saves much hassle.

  4. Thanks for this post. What would be the best way to upgrade the database if you have a new version of it available to download?

  5. @kenny
    If its important for your app to match the right version of the database, its best to put the version number inside the db file name. Your app will be coded to look for a specific version of the db file. So when a new app version is updated, it won't find the matching db file, hence will initiate download.

  6. Hi,
    I believe the solution you created will be very similar to what I will need to do for my app. May I use your code freely (with small changes here and there)?

  7. Anyone is free to adapt the skeleton code in this blog for own use. That's the point of writing a blog, right?... to share ideas. Besides its just plumbing code, not any unique business logic.

  8. Thanks for sharing this great & informative experience.I am new to android development & it helped me a lot.

  9. Thanks for a great blog...I have the following questions: Can the database on sdcard be used also for writing data into it and you are using it as read-only only because of your application requirements? And second: do you have any application on market or example project that is using this pattern of separate download, to see it in action?

  10. @Sebastijan I don't see any reason why this approach won't work for a writable database. You'll need to open the database using SQLiteDatabase.OPEN_READWRITE and basically implement your own version of SQLiteOpenHelper. However, I find it easier to manage if I separate read-only reference data from application written data. It was a design decision for my app.

    My app is on the market (free) but it targets a very specific niche so its almost certainly useless for most people. If you just want to see the download in action, its called FuxiDroid.

  11. This one of the well organised and developed post.I like your blog clarity.This is one of the user friendly post.

  12. Hey, very nice blog! It will surely help me a lot. But I will surely having a problem in mounting SD-card in emulator. How can I do this in Windows? Can you send me the codes? I will appreciate it very much.

  13. @android_help The current version of Android SDK (on Eclipse) now automatically creates the SDCard image file for you as part of creating a Virtual Device. Really easy.

  14. Great Blog.
    I really enjoyed reading it. Very clear.
    Thanks a lot for sharing.

  15. Hi, First I should comment great post, Can you please post the code of delegate FileDownloader it will be really helpful.

    1. FileDownloader simply uses java.net.HttpURLConnection to make a GET request to the URL (passed in from the constructor). Then gets the InputStream and iterates through the content while writing it to a FileOutputStream.

  16. Thanks for your effort of writing this down!