Sync / update sqlite databases

Question

We have an Android app and an iPhone app (same functionality) that use sqlite to store data locally. Initially, applications do not have data, then at the first start they receive data from a remote server and save them in the sqlite database. The sqlite database is created by the server, and applications download it as a single file, which is then used to purchase applications. The database file is not very large by today's standards, but not tiny - about 5-6 MB.

Now, from time to time, applications must update data from the server. There are several approaches that I can think of:

• Download the new full database from the server and replace the existing one. This sounds like the easiest way to deal with the problem, if not for 5-6 MB re-downloads. Applications tell the user if they want to download updates, so this may not be too many problems.

• Download the delta database from the server, containing only new / changed records and in some form information about which records to delete. This will result in significantly smaller downloads, but client-side work will be more complex. I will need to read one database and based on what I read, update another. As far as I know, there is no 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 the same structure. (A complete sqlite database contains about 10 tables, with the largest of which probably containing about 500 records or so.)

• Download the data delta in a different format (json, xml, etc.) and use this information to update the database in the application. Same as before: not a big problem on the server side, smaller download size than the full database, but rather painful process for updating.

Which of the three approaches do you recommend? Or maybe there is another way that I skipped?

Thank you very much in advance.

Answer 1

After many considerations and attempts and errors, I went for a combination of options (2) and (3).

• If there is no data at all, the application downloads the full database file from the server.

• If data is present and an update is required, the application downloads a part from the server. And it checks the contents of a specific value in a specific table. This value will indicate whether the new database should replace the original or contain delete / update / insert

This turns out to be the fastest way (in terms of performance) and leaves all the heavy lifting (determining whether everything needs to be done in one database or just an update) to the server. In addition, with this approach, if I need to change the algorithm, say, always load the full database, it will only be a change on the server without the need to re-compile and redistribute the application.

Answer 2

Is there a way that you can have a JSON field for each of the tables? For example, if you have a table called users, you have a column named "json" that stores JSON for each of the users. Essentially, it will contain information, the rest of the fields.

So, when you load the delta in JSON, all you have to do is insert JSON into the tables.

Of course, with this method you will need to do additional work on parsing JSON and creating a model / object, but these are just 3-4 additional small steps.

Answer 3

I will recommend approach 3, because the application will load the json file faster, and the local db will be updated more easily, avoiding unnecessary expenses for using on the Internet.

Just create empty db initially according to db server and then update it regularly by extracting json