Best practices for synchronizing a SQL database with a REST remote server on Android

Question

I have a custom Android ContentProvider which stores and retrieves data from a SQLite database.

Let's assume one of the DB tables has an _ID column and a NAME column and a content like this:

|==========|==========|
|   _ID    |   NAME   |
|==========|==========|
|    1     |    d1    |
|    2     |    d2    |
|    3     |    d3    |
|    4     |    d4    |
|==========|==========|

This SQLite DB is kept in sync with a remote database and new data is fetched periodically over the network. The possible operations on the table are as follows:

1. Existing rows can be deleted
2. New rows can be added
3. The NAME column of existing rows can be modified

Let's now assume that all the possible operations happen at once and, after fetching some up-to-date data from a remote server, the new content of this table has to be set as follows:

|==========|==========|
|   _ID    |   NAME   |
|==========|==========|
|    1     |    d7    |
|    3     |    d3    |
|    4     |    d6    |
|    5     |    d5    |
|==========|==========|

There can be two different approaches to do that:

1. Query the database and check each existing rows to see if they need to be updated, then add any new rows and delete any missing rows - though this method can be a bit tricky in case we want to update the database with a pagination approach and not with a single network fetch
2. Delete the entire table with a single DELETE SQL command and then add all the rows received from the server

On Android, I am currently implementing the second method with batch operations to maximize the performance:

final ArrayList<ContentProviderOperation> operations = new ArrayList<ContentProviderOperation>();

// with this URI, the content provider deletes all rows
operations.add(ContentProviderOperation.newDelete(Users.CONTENT_URI).build());

final ContentValues values = new ContentValues();
values.put(ID_COLUMN, 1);
values.put(NAME_COLUMN, "d7");
values.put(ID_COLUMN, 3);
values.put(NAME_COLUMN, "d3");
values.put(ID_COLUMN, 4);
values.put(NAME_COLUMN, "d6");
values.put(ID_COLUMN, 5);
values.put(NAME_COLUMN, "d5");

operations.add(ContentProviderOperation.newInsert(Users.CONTENT_URI).withValues(values).build());

getApplicationContext().getContentResolver().applyBatch(MyContentProvider.AUTHORITY, operations);

Is this the best approach, or would method 1 (or some other method) be better in terms of performance?

EDIT: for example, taking approach 2, an overridden ContentProvider#bulkInsert which uses database transactions could speed up the batch-insert operation a lot: see this question.

Answer 1

The best option requires proper API implementation - when you should store some db_version(int or timestamp of last update, or whatever).

And during update server responds with data, and operation type - add, update, delete.