Android updating preloaded database

So.. to use a preloaded database in our app, we have to put it in our assets (or similar) and copy it over to the correct directory. But how do we update it when the contents change and we do an update of the app for someone who has the old version?

There is the class that extends SQLiteOpenHelper and is used to create and update the database. It has a method called onUpgrade(…) that is to be called by the Android OS whenever the database version changes. And the database version is considered to have been changed when the database stored on the device has a different version number than what we give in the constructor of the class we extend from SQLiteOpenHelper.

The obvious solution is to delete the old DB file when onUpgrade() is called and copy the new version over. Too bad it does not work. You get an error something like this:

Caused by: android.database.sqlite.SQLiteReadOnlyDatabaseException: attempt to write a readonly database (code 1032)

This happens after you have replaced the file in onUpgrade() and next time you try to get a database reference with getWritableDatabase() or getReadableDatabase(). It only happens the first time, so once the app has crashed on doing the update, it will actually work when started the next time.

Reason: I assume the Android sqlite implementation opens the database file when calling onUpgrade() and assumes it is the same file forever after that. This is hinted at by the following log entries:

SQLiteLog﹕ (1032) statement aborts at 4: [PRAGMA user_version = 2]
SQLiteLog﹕ (28) file unlinked while open: /data/data/my.package/databases/my_db.db

Looking around the net for “sqlite unlinked file” finds some sqlite documentation also mentioning this possibility. This is the latter of the above lines. The former seems to indicate this happens when the sqlite implementation tries to upgrade the database version with the command “PRAGMA user_version = 2” after doing the onUpgrade() call (where we deleted the file it tries to change version on now).

The solution? Never change the DB version number given to sqlite. I instead stored it as part of user preferences on the device. Since copying even a large database file takes maybe less than a second for me, I could always copy it and forget the upgrade check. But I figured it wouldn’t be nice on the users filesystem and also could have random issues if the disk/memory is too full later. So I keep my own versioning in user preferences.

Which brings me to the last point. I prepared a large file taking too many hours trying to get it right and to upload it to the device. This then had the db file version number 2. If I upload it to the device, the system is not always too happy about having previously had a version 1 database and suddenly getting shoved a version 2 database while claiming to the SQLiteOpenHelper class that it is a version 1 file (to avoid the doUpgrade call).

So how do we change the version of an existing database file? The solution is the one in one of the above errors. Load up the database file in an sqlite client (I used the Firefox extension) and run the SQL command “PRAGMA version;” to get the version it is currently at. Then run the SQL command “PRAGMA version = 1;” to set it to version 1. Now uploading this works just fine, Android sqlite does not try to upgrade, and the app upgrades work with the preferences trick…