How to track database changes on local SQlite before updating web database

I am developing a mobile application and I am not开发者_JAVA百科 sure how to do this. I am going to use Appcelerator Titanium for iPhone.

I don't want the user to make calls to online database whenever there is a change in the local SQLite database on users phone.

I want to do this so that users can work offline.

Later, when the user press a button, I want all the changes to be copied to an online database (only things that have changed).

I dont know what would be the best way to go about it.

One obvious way is, everytime I sync, I copy the entire database from phone to the webserver. But somehow it seems illogical or maybe not.

Looking for suggestions.

---

1. Use a 'last updated' column in your database.

2. Keep a table of 'synch' events, recording the timestamp of each synch event.

3. When you sync, filter rows that were changes since 'last synched'.

I would structure my database so that each record has a UUID - this is so that you can match the local database rows against mirrored ones on the server.

Hope this gives you some ideas.

---

When comparing, you can also take action. What is suggested here with the "last updated" column is the most common approach: the newest record (identified via the timestamp) wins over the older one.

But sometimes, the same record can be changed at the same time on both the source and the destination. What do you do?

1- You can check each record's column and change only modified values that are not modified in the other one

ex: (changes are marked *)
Mobile side:
Last: Doe
First: Jogn
*Age: 58
Timestamp: 2020-10-10 20:00:00

Server side:
Last: Doe
*First: John
Age: 56
Timestamp: 2020-10-10 20:00:00

That would merge data and change only John and the age to 58
Last: Doe
First: John
Age: 58
Timestamp: 2020-10-10 20:00:00

2- Now what if you changed the same column at the same time (believe me, it happens, in data acquisition systems for example)

ex: (changes are marked *)
Mobile side:
Last: Doe
*First: John
Age: 58
Timestamp: 2020-10-10 20:00:00

Server side:
Last: Doe
*First: Henry
Age: 58
Timestamp: 2020-10-10 20:00:00

This info is colliding. If your app is interactive, you could offer a way for the user to actually pick which value, John or Henry, wins and becomes the reference.

But now if you have hundreds of collisions, your user will give up. You can offer Left always wins, or Right always wins. At least the user decides and is responsible for data integrity.

If your app automatically processes the data, then you have to implement a mechanism to make either side win over the other one. This can be a nightmare.

Interbase Databases have change views that are really nice. Other DB also have mechanisms. But they all come with a cost, they're not free like SQLite.

Keep in mind that most models or development tools handling changes usually assume that the record with the newest timestamp wins over the older one. Fits probably 95% of the needs.