iPhone SQLite DB and Web-based DB synchronization and interaction recommendations

Question

I'm in the process of developing my second iPhone application, and am looking for architectural recommendations on DB handling.

The idea is: 1. A database of information is stored on a server (LAMP stack), and information is delivered to the device via JSON. This part has been implemented.

1. The user is able to "favorite" an item in the database, which stores it in SQLite on their local device.

2. The user can also submit new items to the remote server that don't already exist, making them available for other users to favorite.

3. The user can search both databases, via a single search interface to find items.

I'm trying to decide the data structure for this, and how to deal with the resulting objects from the database. I think I have two options for the objects:

1. The remote DB and the local DB have the same object type, and the local DB stores the id of the remote item to link the two

2. Separate objects for the remote item and the local item

Any ideas, thoughts, etc are greatly appreciated!

Answer 1

If you want to have synchronized objects in the databases, you could use a combination of a modification timestamps comparison and a hash column to determine which records are stale and need to be updated.

Answer 2

Is SQLite provided API for synchronization?

Answer 3

You'll need to write your own synchronization engine. I posted a lengthy set of notes on a record-level, history-driven synchronization engine I wrote from a wine journal application, which you can find here…

http://blog.deeje.tv/musings/2009/06/notes-on-writing-a-history-driven-client-server-synchronization-engine.html

Answer 4

If you are looking for an existing API the QuickConnectFamily framework has one that works within the UIWebView using JavaScript for both in browser and installed SQLite databases. I'm also close to having a pure Objective-C implementation for those that aren't writing hybrid apps.

The javascript version also works for Android.

http://www.quickconnectfamily.org