Best way to send information from one web db/app to multiple desktop/mobile apps?

Question

I am planning to build multiple desktop addons ( for Chrome & Firefox ) and mobile apps ( for iPhone and Droid ).

The application would have its own database, and it would be filled by downloading information from a web database (possibly MySQL in Drupal). My plan is to have specific information in the web database transferred to an SQLite DB, and then having the apps download the database and/or sync with the master web database to retrieve updated information.

What is the best database, or the best process, for multiple desktop and mobile applications? The applications are all offline apps that downloads multiple contacts (phone number, address, email ), so it wouldn't be something where 10,000 queries are happening a second. The applications and databases would be updated about once per month.

I see that many people don't recommend reading from MySQL to a mobile app, so is SQLite the way to go? I am new to application development, so any answers will be welcome.

Answer 1

http://ODBCrouter.com/ipad (new) has XCode client-side ODBC libraries for iPhone. Your desktop addons would be compiled to use similar client-side ODBC libraries for Mac, Windows and Linux. ODBC is an industry standard set of APIs for accessing any database that is very easy to learn. On your back-end, you would setup a Windows box (or VM) with the ODBC drivers for the database(s) you want to serve along with an ODBC Router --the database themselves can live on the same Windows box, or on another system like OS X, Linux or iSeries.

From there, if you want your offline database (SQLite is only real choice) to synchronize efficiently, just add a tsLastClientUpdate and/or tsLastServerUpdate column to the tables that is updated (with a TIMESTAMP) each time any of the fields in that row are changed (this can easily be done with a TRIGGER and/or a STORED PROCEDURE or function in your C code). Then keep track of the timestamp when you last synchronized your local database with your server and when it's time to sync, only SELECT rows that have a more recent tsLastClientUpdate and/or tsLastServerUpdate value to figure out your "list" of INSERT, UPDATE and DELETEs. ODBC also allows you to run that "list" in a single "transaction" if your back-end database supports it --so if the network goes down (or app closes) or whatever, your database is not left in a half-updated state..and then only update your internally tracked timestamp of when you last synchronized upon completion of that "list" transaction.