Synchronise/update sqlite databases

Question

We've got an android app and an iPhone app (same functionality) that use sqlite for local data storage. The apps initially come with no data, then on the first run they receive data from a remote server and store it in a sqlite database. The sqlite database is created by the server and the apps download it as one file, which is then used buy the apps. The database file is not very large by today's standards, but not a tiny one either - about 5-6 MB.

Now, once in a while, the apps need to refresh the data from the server. There a few approaches I can think of:

1. Download a new full database from the server and replace the existing one. This one sounds like the simplest way to deal with the problem were it not for a repeated 5-6 MB downloads. The apps do prompt the user whether they want to download the updates, so this may not be too much of a problem.

2. Download a delta database from the server, containing only the new/modified records and in some form information about what records to delete. This would lead to a much smaller download size, but the work on the client side is more complicated. I would need to read one database and, based on what is read, update another one. To the best of my knowledge, there's not way with sqlite to do something like insert into db1.table1 (select * from db2.table1) where db1 and db2 are two sqlite databases containing table1 of the same structure. (The full sqlite database contains about 10 tables with the largest one probably containing about 500 records or so.)

3. Download delta of the data in some other format (json, xml, etc.) and use this info to update the database in the app. Same as before: not to much problem on the server side, smaller download size than the full database, but quite a painful process to do the updates.

Which of the three approaches you recommend? Or maybe there's yet another way that I missed?

Many thanks in advance.

Corduroys

#4008515 – Gomphosis

Answer 1

After much considerations and tries-and-errors, I went for a combination of options (2) and (3).

1. If no data is present at all, then the app downloads a full database file from the server.

2. If data is present and an update is required, the app downloads some database from the server. And checks the content of a particular value in a particular table. That value will state whether the new database is to replace the original or whether it contains deletions/updates/inserts

This turns out to be the fastest way (performance-wise) and leaves all the heavy lifting (determining whether to put everything into one database or just an update) to the server. Further, with this approach, if I need to modify the algorithm to, say, always download the full database, it would only be a change on the server without the need to re-compile and re-distribute the app.

Corduroys

could you post any samples or tutorial? – Spondylitis

I would also like to see some examples - I'm trying to synchronise sqlite databases - one central database stored on Google Drive, and any number of offline databases stored on individual Android devices (which may not always have access to the internet). – Expound

@Expound if the device doesn't have access to internet, you cannot synchronise anything. In my case, the synchronisation is always one way - from the server to devices - and the structure of the database doesn't change, only the data. – Corduroys

@AleksG In this case it would still be a one way synchronisation, but from multiple sources. The problem is when two devices clash, i.e. both devices make a change to the same row of data. Any suggestions there? – Expound

@Expound there is no simple answer. What you do depends on your scenarios and your requirements. – Corduroys

Answer 2

Is there a way you can have a JSON field for each of the tables? For instance, if you got a table named users, have a column named "json" that stores the JSON for each of the users. In essence, it would contain the information the rest of the fields have.

So when you download the delta in JSON, all you got to do is insert the JSON's into the tables.

Of course with this method, you will need to do additional work in parsing the JSON and creating the model/object from it, but it's just an extra 3-4 small steps.

Bobbette

This is not as simple as it sounds. The tables in the database are linked with foreign keys, so the data in one table makes little sense on its own without data from other tables. The data really needs to be all together. When I query the data, I use joins to get the required information. – Corduroys

Answer 3

I will recommend approach 3, because app will download the json file more fast and local db will be updated more easily avoid overhead of more internet usages.

Just create a empty db initially according to server db and then regularly updated the same by fetching json

Compute

I'm not sure about your wording of "download the json file more fast". I did some testing/prototyping: while the full DB is about 5 MB, a json file containing all of the data is about 8 MB. Apart from 1.5 times the size, parsing such a huge json may also be very time-consuming. – Corduroys

the json will contain all the records of the individual tables in the form of field name and value. Also there will be multiple json for each tables which will will fetched separately. – Compute

Ok, in that case I fail to see how it will be more efficient than downloading a complete database from the server. – Corduroys

among one time download of db among all the three approaches , may be some time difference will be there, but for regular updating the db ,third approach will be better. – Compute