Packaging Existing SQLite Databases With Your Google Android Application

Many examples of Android applications on the Internet assume that the apps you develop will, by default, create a new, blank MySQL database at its first run-time.  However, there aren't many examples of a situation where one would create a separate MySQL database which would then be used by the Android application.  A potential solution, borrowed from databases on other platforms that use Continuous Integration, would be to embed seeding SQL statements in the application to run on creates or upgrades, but this may be prohibitive for some developers in terms of practicality and time, in addition to the fact that Android does some backend wizardry with SQLite databases to have them work properly with its SQLiteOpener class.  So, how would one embed an existing SQLite database to the application's assets folder and use it at runtime?  Stay tuned for the solution after the jump!




A fellow blogger, Juan-Manuel Fluxa, was able to distill a simple solution to this conundrum.  It involves a few manual steps on your existing MySQL database.  To perform these steps, either have the SQLite command line handy or download a GUI-based tool such as SQLite Database Browser.  Mimicking the original blog post, we are using SQLite Database Browser on our existing database, which on the filesystem exists as SampleDatabase.db.

Subversion Checkout Source for the Example Below: https://edg.sourcerepo.com/edg/Geekstentialism/Geekstentialism.AndroidWithDB/

Step 1: SQLite Manual Steps

1. Execute this SQL Script: CREATE TABLE "android_metadata"("locale" TEXT DEFAULT 'en-US')
2. On ALL of the tables in your database, add a column called _id which will be an INTEGER PRIMARY KEY column.
3. Compact and save your database

Step 1 Recap: What the Heck Did We Just Do?
The first SQL statement creates an embedded table expected by Android.  The android_metadata table is used by Android's SQLite helper objects to facilitate connectivity to an app's database.

The second task has you create the expected primary key field by Android's SQLite helper objects.  Now, you obviously must be asking the question, "what if I already have a Primary Key field defined"?  Unfortunately, due to the behavior of the Android SQLite helper objects, whatever your existing primary key may be will need to take a backseat to the "_id" field.  To ensure that your data can still be retrieved quickly using your database's originally intended keys, I would highly recommend creating indices on those columns.

Step 2: Android Magic

1. Add the database you created above to your application's assets folder.
2. Within your application's data namespace (or however you've arranged your tiered architecture), create an object representation of your database and have it implement SQLiteOpenHelper.  For brevity's sake, click here to see the source of the example project above.  This code example was taken from Juan's original blog post above.

Step 2 Recap: What the Heck Did We Just Do?
Adding your MySQL database to the assets folder ensures that it becomes part of your application on deployment.

The SampleDatabase.java class above implements SQLiteOpenHelper to modify the default behavior of this class.  As you can see in the code, it first checks for the existence of the database on the Android device's filesystem.  If the database does not exist, it calls the getReadableDatabase function of the SQLiteOpenHelper to create a default, blank database for your application.

After the blank database is created, the copyDatabase method streams in the database you dropped in the assets folder of your application, overwriting the blank skeleton.  

To use the SampleDatabase.java class above in your UI (or, preferably, your Data Provider class), after instantiating it, call the createDatabase method to have it perform the magic above.  Voila, your MySQL database is now deployed as part of your Android application!

The example above does not follow the generally recommended pattern of an Android Data Provider -- we'll get to that in a later blog post.  If you're familiar with the pattern, the implementation above should be very simple to integrate with your Data Provider solution.

Gotchas

• The path to your application's database on the Android device's filesystem will always be /data/data/com.yourandroidapp/databases, where com.yourandroidapp is obviously the package name you have chosen for your app.
• Remember that Google Play has a 50 MB file size limitation for your .apk.  Because you are dropping your MySQL database to the assets folder, it becomes part of the .apk.
• Indices are your friends, but remember they also take up disk space.  If your application has a fairly large database that retrieves its records using your originally intended primary keys (versus the embedded "_id" fields we created above), create indices on those columns, but be cognizant of the disk space growth.