Client-server synchronization via REST

Question

This question concerns what I consider to be a very common problem in the development of Android / iOS, but have not yet found a "standard" solution.

Say we have a normal REST API. The server database contains (among others) tables countries and towns with a ratio of 1: N.

The client (mobile application) wants to save a local snapshot of these two tables. Thus, when it is disabled, it can fulfill requests that are usually performed on REST, for example: "get a list of Austrian cities with a population> = 100"?

How to do it?

First problem: consistency. The client must have a snapshot of the two tables. If a client downloads updates to the towns table and goes offline, some cities may refer to a country that is not in the local copy of the countries table.

The second problem: the client should only load new / deleted / changed lines. Disable REST and use some kind of custom RPC call like get_updates_since(...) ?

The third problem: how should the local changes in the client copy of the database (possibly offline) be synchronized with the server? Custom RPC Calls?

Answer 1

I don't think there is a silver bullet, but the pattern you are looking for is caching. In past projects, I copied items from the server to the local storage (SQLite or flat file) and maintained metadata about records for updating / uploading / cleaning based on simple rules. This is not a simple problem and ends up with a lot of code.

Consistency: in your example, either make sure that you first load the country table, or make two atomic operations — for example, make copies of the “new” tables and replace only your cached versions if both copies are completed successfully (doubles storage).

Download only new / deleted / changed: yes, this requires client / server integration - check all entries (with GMT), ask for metadata from the server and go through local metadata that will decide what to do. (UUID hints for strings are useful).

Synchronize local changes: Yes, more client / server integration. See paragraph above.

Handling all exceptions and edge cases is challenging. Take a look at the issues with iCloud syncing CoreData . This may not be a fair comparison, as Apple is trying to solve this problem for a fully distributed database, but nonetheless interesting to read.

Answer 2

I am currently working on the same task: creating an Android application that will synchronize information with a central SQL database using Azure Mobile Services . The synchronization strategy is to support bi-directional synchronization from multiple clients to ensure data consistency, while only incremental changes will be replaced.

Let me provide a solution to your problems. I recently wrote a blog post about a synchronization algorithm to support these scenarios.

The synchronization logic will be controlled on the client side due to the REST API connection. Each table participating in the synchronization process will have corresponding Api REST methods for CRUD operations.

For your first problem (consistency), the solution is to upload the data to the client in the correct order. For instance. first parents and then children to avoid referential integrity issues. In the event of a network problem, the client will need to synchronize again to receive the rest of the information.

The second problem (downloading only incremental changes) is the solution is a timestamp (as Kevin mentioned in his answer below). But I would suggest using a globally increasing value supported on the server side to avoid problems with differences between timestamps between clients and server. The version of rowversion SQL Server is a good fit for this.

The third problem (data integration with the client) - the use of Dirty flags in client tables will help to differentiate the records necessary for loading.

You may also need to enter the “Delete” flag on both sides to handle deletions between multiple clients.