I was speaking to a friend of mine this week about iPhone development, especially around database development. I posed, what I thought was a straight forward question to him, which opened a whole can of worms, but worms that I've seen before.

My scenario went like this... I want an app that uses SQLite on the device. I would want that device to get DB updates/records, securely over the air,  specific to that user and I want the database to be secure. Those updates/records I want to come from a Server DB, that could be any of the Enterprise Database. I want the user to use the application, which could add, delete or create new records in the device database, then the application should send its changes back securely across the air to the Enterprise DB.

My friend thought on it for a while then said...

Based on what you've written, then I would suggest some possibilities:

1. Wrap any edits to the local database on the iPhone and store the rowId/pk of the changes (for any/all affected changes). Somehow you'll need to store this extra information to facilitate synchronization. This has its own set of issues since you'll probably need to rely on the user's clock being set correctly etc. Then you'll need a way to compare those changes to any incoming data as below.

2. You describe "secure updates over the air" but you don't specify what you mean by that. So, will there be a button that the user presses to refresh the data on the phone?  Or will this be some kind of background thread?  How often will updates be synchronized? Does it only connect via Wi-fi or is cellular reliable enough to sync, etc. Will you use https with an XML payload or are you going to write some kind of custom socket level stuff to handle secure communication?

3. Since there are no native database client libraries for all practical purposes then maybe you'll need to invent a web service for updating modified records and downloading new updates. What do you do when there is a conflict between what the user has chosen and the updates coming in?

4. At which point, depending on the size of these databases, you might have been better off implementing an intermediary web application/middle tier that handles the actual synchronization, since it is complicated...

The conversation paused, with my head spinning. Dude I don't want to do any of that, I thought to myself. You're right, it's hard and I just want to concentrate on my business problem. I know the Enterprise Database I want to synchronize with and I know the subset of tables, that I want on my iPhone.

Luckily for me iAnywhere plan to extend their reach by targeting iPhone support for early summer 2009 with its first beta. The UltraLite database has some similarities with SQLite. Both are in-process databases, they are of similar size, similar performance and have a similar range of SQL. The big differences are that UltraLite has Enterprise-ready features such as a built-in synchronization client, and security. This security can be through obfuscation or strong encryption of the actual database itself as well as the synchronization process.

Phew I don't have to re-invent the wheel... Worms back safely in their can!

Interested? Register your interest here.