Tuesday, February 14, 2012

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.

12 comments:

  1. Hi I recieve the following error using your sample app:
    "02-22 23:37:46.333: E/AndroidRuntime(331): java.lang.RuntimeException: Unable to start activity ComponentInfo{com.ingeniouscamel.droid.mydbapp/com.ingeniouscamel.droid.mydbapp.MyDBAppActivity}: android.database.sqlite.SQLiteException: cannot start a transaction within a transaction: BEGIN TRANSACTION;"

    I also recieve a similar error by creating my helper and using your class as explained above."

    Please help.
    Thanks

    ReplyDelete
    Replies
    1. Dear Ben - Thank you for pointing out this issue. I was able to simulate the problem and have fixed the code appropriately. Please try again by clicking the above links and downloading the latest files.

      Sam

      Delete
    2. Thanks =) Works like a charm now. It would be great if it could handle database updates however. For instance if a new version of the application is released with an updated database.

      Thanks again,
      Ben

      Delete
    3. This comment has been removed by the author.

      Delete
  2. Thanks Sam. It works like a charm now. It would be awesome if it could handle database updates however if a newer version of the database is deployed with an updated application. Thanks for your work.

    p.s. I've replied to this before but the post has disappeared so there may be something wrong with this comments box on your blog.

    Thanks

    ReplyDelete
  3. This is great! thanks for the post. I was copying my database file to the application which worked 99% of the time but some devices didn't like it. Your method seems to work more universally. One issue for me is my db is quite large and it take a good 10 seconds to process the database, I'd like to display a progress dialog but the methods are blocking. I need to wrap you're deploy so it runs on a separate thread I suppose. I'm new to java so I'll go digging on how to do that.

    ReplyDelete
    Replies
    1. Figured out how to run it in a second thread, all is well. Thanks for this solution!

      Delete
  4. thanks for the article! another good article on the topic http://www.enterra-inc.com/techzone/handling_sql_issues/

    ReplyDelete
  5. superb it is really useful! thanks :)

    ReplyDelete
  6. Very useful but there is a problem : if text values of a column is containing a LF (this is my case, a fiels is storing html content), the function crashes

    ReplyDelete
  7. Thanks a lot. Very useful

    ReplyDelete
  8. Very good tutorial, however, when I use a file in my github repository like myUsername.github.io/dmpFile.zip, it doesn't work. Can anyone help me with that?

    ReplyDelete