Deploy SQLite Database on Android

Android provides full support for SQLite databases. However, Android recommends that you create the database tables along with any pre-populated data when you first run your application. This method may not be very desirable because the developer has to write a huge number of SQL statements using Java strings, which is time consuming and prone to errors. To avoid this complexity, there are some solutions that ship a SQLite database file with application and then copy this file on Android data path. This solution is not very safe because there may be incompatibility issues between the packaged SQLite database and Android. Therefore there should be some easy way to ship complex tables along with data in Android applications. Or better yet, Android should be able to download and install the database, thereby reducing the application size drastically.

Welcome to SQLiteDBDeploy!!! SQLiteDBDeploy is an easy to use utility to deploy SQLite databases without any compatibility issues. The main idea is you design and develop your database and once you are done, you create a dump file of the database. This dump file is compressed to reduce its size and is either packaged with application or hosted on a website. This compressed dump file is then deployed on Android using SQLiteDBDeploy utility class.

For demonstration purposes, I will now deploy NorthWind database and use it in my Android application. Before you try this tutorial, please download SQLite shell from http://www.sqlite.org/download.html

Step 1
Create a SQLite database using SQLite database browser or sqlite3 command line shell. You can create complex tables and insert as much data as you wish. If you already have a database on Android and wish to use it, then pull this database using adb utility. For example, to pull database "northwind.db" in my package "com.ingeniouscamel.droid.mydbapp", I would issue the following command.

$ adb pull /data/data/com.ingeniouscamel.droid.mydbapp/databases/northwind.db 

Step 2
Once you have the database on your local file system, create a dump file using the sqlite3 command-line shell as follows:

sqlite3 <your sqlite database file that you wish to deploy> .dump > <dump file name>

$ sqlite3 northwind.dp .dump > northwind.dmp

Step 3
Compress the dump file using any zip utility. Do NOT password protect the file.

$ zip northwind.zip northwing.dmp
  adding: northwind.dmp (172 bytes security) (deflated 59%)

Step 4
If you wish to package this dump file in your application, then copy the file to assets folder under your Android project in eclipse and continue to step 5.

$ cp northwind.zip /projects/MyDBApp/assets/.  

Another packaging method is to directly access the dump file from a remote web server. There are several pros and cons for this method. First of all your application size is small, because you are not including the dump file in your application. Second, you can release upgrades or new versions of databases without reinstalling the application. However, the database is not safe because the dump file is not encrypted and anyone can access it over the internet. However, you can make this operation safer by employing encryption and authentication.

I have uploaded my sample northwind dump file to google source control server and could be accessed by using the following URL.

http://ingenious-camel.googlecode.com/svn/trunk/SQLiteDBDeployer/assets/northwind.zip

Don’t forget to grant Internet permission to your android application. In file AndroidManifest.xml, add the following line:

.
.

.
.

Step 5
Deploy the compressed dump file in your Android application using the SQLite deploy utility class. Download the Java source code for this utility class from here and copy it under src folder of your Android eclipse project. Depending on where you have the compressed dump file, call the deploy function on your database helper's OnCreate event as follows:

To deploy a packaged dump file in your application, call function deploy in class SQLiteDBDeploy using context, SQLiteDatabase and filename parameters.

.
.
 @Override
 public void onCreate(SQLiteDatabase sqlLiteDb) {
  SQLiteDBDeploy.deploy(this.context, sqlLiteDb, "northwind.zip");
 }
.
.

To deploy a dump file on a remote web server, call function deploy in class SQLiteDBDeploy using SQLiteDatabase and dump file URL parameters.

.
.
 @Override
 public void onCreate(SQLiteDatabase sqlLiteDb) {
  SQLiteDBDeploy.deploy(sqlLiteDb, "http://ingenious-camel.googlecode.com/svn/trunk/SQLiteDBDeployer/assets/northwind.zip");
 }
.
.

That is it. You are all set. Now, when you run the application for the first time, SQLiteDBDeploy utility will load the compressed dump file and run SQL statements to recreate the database. You can see the running SQL statements in eclipse's LogCat.

You can download my sample Android application that loads packaged dump file from here. This Android application displays shippers from northwind database.