Register now or log in to join your professional community.
The IOS app has an SQLITE db and uses Core data framework, and the MYSQL db is on a webserver.
The best practice to synchronise mobile SQLite database and MySql database is to store last updated timestamp in both the databases i.e server and mobile database.
The phone searches for everything that has changed since the last sync and sends it up to the server along with a timestamp of the last sync, and the server responds with everything that has changed on it's end since the provided sync timestamp.
You first save data on server and then get response from server using NSUrlConnection,AFNetworking etc in the form of json/xml,then parse the web service and store it to your core data.
I would use JSON strings between the app and the webserver.
You can build JSON strings from SQLITE data, and on the webserver, reverse the process by building SQL statements from JSON strings.
Check this SQLITE to MySQL and this MySQL to SQLITE
While those links are for synching Android's SQLITE with MySQL on a webserver (using PHP), it should be the same concept for an IOS app with PHP/JSP/ASP...
But you should take into consideration conflicts between records. Maybe decide earlier (or provide an option for the user to choose) which source to prefer if a conflict occurs. Or even let the user know about conflicting records so they can decide what to do with conflicting records.
EDIT:
If the database is big enough to make it cumbersome to do it manually for each table, you might want to make the process generic for all table.
This can be done by querying the tablenames, and then for each table querying the column names, and build the JSON string from that.
Check this answer on StackOverflow: SQLite Schema Information Metadata. and this link SQLITE PRAGMA documentation
You will end up in more loops, and nested loops, but you'll relieve yourself from hardcoding table names and column names and entering them manually for each table.
