Update local Android database

Question

I'm currently in trouble with a SQLite database problem on Android.

My application have an local database which inside an apk file. When the application starts it will check for the new version, and download entirely new database if available (although between two database version, changes are very little). But the database is too large now. So it takes very long time when new database available. So any solution for this problem?

Answer 1

Basic idea

Here's how I'd do it. I'm assuming here that the client app doesn't make changes to the local database (except when it downloads a new version), so that there are only a few possible versions of the database in existence (one for every time you've made a change at the server end).

1. Add a column to every table called LastModified, with a default value of NOW(). That means that every time you add something to your master copy, it'll get an updated LastModified setting. You will have to make sure your updates (rather than inserts) change the LastModified field too.
2. Store somewhere in the database (a Settings table or something) a field that tracks the date that this version of the database was published on the server (call it PublishDate).
3. When the client wants to check for a new version, it sends its PublishDate to the server. The server then checks each table, and finds every row where LastModified comes after PublishDate. It sends SQL to the client to insert or update these rows on the client. It also sends the new PublishDate so that the client can update that in its local copy.

This deals with inserts and updates. It doesn't deal with deletions. It might be that they aren't an issue in your case; if they are:

4. Add either a separate table to log deletions, where you also track LastModified, so that you can tell the client which rows to delete; or preferably have a setup where you don't ever actually delete any rows, but just update them to be marked as "deleted".

Finally, this won't handle schema changes. Again, hopefully that isn't an issue in your case: hopefully you have a stable schema. But if you do need to add or drop tables or indexes or something, that will have to be done separately:

5. Create a SchemaChanges table on your master, and whenever you make structural changes, put the relevant details into the SchemaChanges table, along with a LastModified date, so that you can send this to the client on request too. If you're doing this, you'll want to send schema changes to the client first, because they might affect the meaning of other changes.

Now the nice thing about doing it this way is that you can pre-process everything on the server (because there are only a few versions in existence). For every old version, you can calculate the changes (based on the details above) that would take that old version up to the new version, and then store the resulting SQL on the server. If you do that, you avoid the need for generating the SQL on the fly: when the client sends the PublishDate, you just look up the SQL you've already calculated that transforms the version from that PublishDate to the latest version.

Alternative implementation

There is a nice and easy way of pushing the changes that the above scheme gives you, even with a slight simplification that doesn't require LastModified times, or indeed any changes to your existing structure. At the server end, where you already have the old version (because you have all the old versions) and the new version, you create an SQL dump of both databases, and then run diff over them to generate a patch file that you can send to the client app. The client app will use the same Java library to generate the SQL dump of the old version, and will then apply the diff patch to it to create a full SQL dump for the new version. At that point, it can drop the old database and create the new one from the SQL dump.

This will be very efficient if the changes aren't wholesale changes (in which case you might as well just push the new .db file).

It's fairly easy to do this by invoking the SQLite binary to create the dumps. You will need to modify the approach slightly for Android, according to this way of executing an external command.

You can use this Google library to calculate the diff patches at the server end and apply them at the client end.

Answer 2

Instead of getting an entire new database file, get the changes. Changes may be in the form of an SQL script. Let server generate the change script for every update, and then you can download the SQL scripts and run them on local database in sequence.