How to ship an Android application with a database?

Question

If your application requires a database, and comes with builtin data, what is the best way to ship that application?

1) Precreate the sqlite database and include it in the apk?

2) Include the SQL commands with the application and have it create the database and insert the data on first use?

The drawbacks I see with 1): possible sqlite version mismatches might cause problems, and I currently don't know where the database should go and how to access it.

The drawbacks with 2) It may take a really long time to create and populate the database on the device.

Your thoughts? Pointers to documentation regarding any issues would be great.

Answer 1

From what I've seen you should be be shipping a database that already has the tables setup and data. However if you want (and depending on the type of application you have) you can allow "upgrade database option". Then what you do is download the latest sqlite version, get the latest Insert/Create statements of a textfile hosted online, execute the statements and do a data transfer from the old db to the new one.

Answer 2

Currently there is no way to precreate an SQLite database to ship with your apk. The best you can do is save the appropriate SQL as a resource and run them from your application. Yes, this leads to duplication of data (same information exists as a resrouce and as a database) but there is no other way right now. The only mitigating factor is the apk file is compressed. My experience is 908KB compresses to less than 268KB.

The thread below has the best discussion/solution I have found with good sample code.

http://groups.google.com/group/android-developers/msg/9f455ae93a1cf152

I stored my CREATE statement as a string resource to be read with Context.getString() and ran it with SQLiteDatabse.execSQL().

I stored the data for my inserts in res/raw/inserts.sql (I created the sql file, 7000+ lines). Using the technique from the link above I entered a loop, read the file line by line and concactenated the data onto "INSERT INTO tbl VALUE " and did another SQLiteDatabase.execSQL(). No sense in saving 7000 "INSERT INTO tbl VALUE "s when they can just be concactenated on.

It takes about twenty seconds on the emulator, I do not know how long this would take on a real phone, but it only happens once, when the user first starts the application.

Answer 3

I just found a way to do this in ReignDesign blog in an article titled Using your own SQLite database in Android applications. Basically you precreate your database, put it in your assets directory in your apk, and on first use copy to "/data/data/YOUR_PACKAGE/databases/" directory.

Answer 4

If the required data is not too large (limits I don´t know, would depend on a lot of things), you might also download the data (in XML, JSON, whatever) from a website/webapp. AFter receiving, execute the SQL statements using the received data creating your tables and inserting the data.

If your mobile app contains lots of data, it might be easier later on to update the data in the installed apps with more accurate data or changes.

Jaco