Best way to update a Sqlite DB from a server?

Question

I have an android app with a Sqlite database (it's about 800Mb), sometimes I need to insert, modify or delete database rows from an external server (via internet) in order to update the database.

Is there a way to update the database from the server without having to download the entire database (800mb)?

I was thinking of a homemade solution that consists of adding a new column to the server database that indicates if said row needs to be inserted, deleted or modified by the android app, but I don't know if something is already implemented.

Answer 1

First question- does the database also change locally on the Android device? If so, you're basically into cache coherency. There's an old joke that the two hardest problems in CS are cache coherency and naming things. It's not totally wrong.

If you do need to keep local changes, especially if you need to sync local changes up, this needs to be a small book, so I'm going to assume not for the rest of the answer.

Honestly, if your db needs to scale at all or you need to make changes frequently, downloading a new db is the way to go. Doing any sort of diff against the db is going to cost you a lot of DB processor time, which translates to bigger or faster db servers, which equals money. Or a big perf hit on any other use of the db.

If you do decide you need to do this you need two extra columns. One- an isDeleted flag. That way you can easily check for deleted rows (the only other way to do so is to download all rows and see what's missing, which is a very bad idea). Please note you'll need to change every db query you make anywhere to add "and isDeleted=false" as a condition so you don't return delete rows.

The second column isn't an "isModified" field, its a "modifiedTime" timestamp. Why a timestamp? Because you can't be assured that a client downloading the db was only 1 version behind. He could be 2. Or 10. You need to be able to get all the changes in all the previous versions as well, so an isModified isn't good enough. With a modifiedTime field, you can find the max modifiedTime in the local db, then ask the server for all rows with a modifiedTime greater than yours. You'll then either need to change all your inserts and updates to also set modifiedTime, or use a trigger to do so.

There are a few other ways to do it- a migration file approach (a file with the SQL commands to alter the data) can work if your changes are small. Really though, just download the db. It's so much simpler and less likely to break things. And if you're doing large updates, it may even be less bandwidth. Most importantly, if you just download the file you know the data is correct- if you try and do some kind of diff like above, you have to worry about bugs or inconsistencies in the data for various reasons (did your app get killed while processing the changes? Do you have a bug? Did you do a query mid change and get broken data, with only half the changes you need? Downloading a new file and swapping the dbs when done fixes all those things).