SQlite synchronization scheme

Question

Hi, I know it is xmas eve, so it is a perfect time to find hardcore programers online :).

I have a sqlite db fiel that contains over 10 K record, I generate the db from a mysql database, I have built the sqlite db within my iphone application the usual way.

The records contains information about products and their prices, shops and the like, this info of course is not static, I use an automatic scheme to populate and keep updating my mysql db.

Now, how can I update the iphoen app sqlite database with the new information available in the mysql db, the db structure is still the same, but the records contains new information.

Thanks. Ahed

info: libsqlite3.0, iphone OS 3.1, mysql 2005, Mac OS X 10.6.2

Answer 1

There is a question you need to answer first; How do you determine the set of changed records in your MySQL database?

Or, more specifically, given that the MySQL database is in state A, some transactions occur and now it is in state B, how do you know what changed between A and B?

Bottom line; you need a schema in MySQL that enables this. Once you have answered that question, then you can answer the "how do I sync problem?".

Answer 2

You can find many other similar questions by searching for "iphone synchronization":

http://stackoverflow.com/search?q=iphone+synchronization

I'm going to assume that the data is going only from mysql to sqlite, and not the reverse direction.

Three are a few ways that I could imagine doing this. The first is to just redownload the entire database during every update. Another way, which I'm describing below, would be to create a "log" table to record the modifications to your master table, and then download just the new logs when doing the update.

I would creat a new "log" table in your SQL database to log changes to the table needing synchronization. The log could contain a "revision" column to track in what order changes were made, a "type" column to specify if it was a insert, update, or or delete, a the row-id if your affected row, and finally have the entire set of columns from your master table.

You could automate the creation of log entries by using stored procedures as wrappers to modify your master table.

With only 10k records, I wouldn't expect this log table to grow to be that huge.

You would then in sqlite keep track of the latest revision downloaded from mysql. To update the table, you would download all log entries after the latest update, and then apply them to your sqlite table.

Answer 3

I have a similar application.

I am using Push Notification to let my users know there is new or updated data available.

Each time a record on the server is updated, I store a sequential record-number alongside the record.

Each UDID that's registered has a "last updated" number associated with it that contains the highest record-number it has ever downloaded.

When any given device comes to get it's updates, all database records greater than the UDID's last updated record-number as stored on the server are sent to the device. If everything goes OK, the last updated record-number for the UDID is set to the last record number sent.

The user has the option to fetch all records and refresh his database if he feels any need to sync his device to the entire database.

Seems to be working well.

-t