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) {
        super();
        this.dbFile = dbFile;
        this.activity = activity;

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

    @Override
    protected void onPreExecute() {
        super.onPreExecute();
        originalRequestedOrientation = activity.getRequestedOrientation();
        activity.setRequestedOrientation(ActivityInfo.SCREEN_ORIENTATION_NOSENSOR);
        wakeLock.acquire();
        progressDialog.show();
    }
    
    @Override
    protected Object doInBackground(FileDownloader... params) {
        FileDownloader downloader = params[0];
        try {
            downloader.downloadTo(dbFile);
        } catch (IOException e) {
            throw new AndroidRuntimeException(e);
        }
        
        return null;
    }
    
    @Override
    protected void onPostExecute(Object result) {
        super.onPostExecute(result);
        progressDialog.dismiss();
        wakeLock.release();
        activity.setRequestedOrientation(originalRequestedOrientation);
    }
}

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(
            Environment.getExternalStorageDirectory(),            
            "Android/data/com.mycompany.myapp/db");
        if (!appDbDir.exists()) {
            appDbDir.mkdirs();
        }
        this.dbFile = new File(appDbDir, dbFileName);
     }
     
     public boolean databaseFileExists() {
        return dbFile.exists();
     }
     
     private void open() {
         if (dbFile.exists()) {
             database = SQLiteDatabase.openDatabase(
                 dbFile.getAbsolutePath(), 
                 factory, 
                 SQLiteDatabase.OPEN_READONLY);     
         }
     }
        
     public synchronized void close() {
         if (database != null ) {
            database.close();
            database = null;
        }
     }

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

     private SQLiteDatabase getDatabase() {
        if (database==null) {
            open();
        }
        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.

Conclusion

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.