Syncing a local sqlite file to iCloud

Question

I store some data in my iOS app directly in a local .sqlite file.  I chose to do this instead of CoreData because the data will need to be compatible with non-Apple platforms.

Now, I'm trying to come up with the best way to sync this file over iCloud.  I know you can't sync it directly, for many reasons.  I know CoreData is able to sync its DBs, but even ignoring that using CD would essentially lock this file into Apple platforms (I think? I've only looked into CD a bit), I need the iCloud syncing of this file to work across ALL of iCloud's supported platforms - which is supposed to include Windows.  I have to assume that there won't be any compatibility for the CoreData files in the Windows API.  Planning out the best way to accomplish this would be a lot easier if Apple would tell us any more than "There will be a Windows API [eventually?]"

In addition, I'll eventually need to implement at least one more sync service to support platforms that iCloud does not.  It would be helpful, though not required, if the method I use for iCloud can be mostly reused for future services.

For these reasons, I don't think CoreData can help me with this.  Am I correct in thinking this?

Moving on from there, I need to devise an algorithm for this, or find an existing one or an existing 3rd party solution.  I haven't stumbled across anything yet. However, I have been mulling over a couple possible methods I could implement:

Method 1:

Do something similar to how CoreData syncs sqlite DBs: send "transaction logs" to iCloud instead and build each local sqlite file off of those.

I'm thinking each device would send a (uniquely named) text file listing all the sql commands that that device executed, with timestamps.  The device would store how far along in each list of commands it has executed, and continue from that point each time the file is updated. If it received updates to multiple log files at once, it would execute each command in timestamp order.

Things could get 'interesting' efficiency-wise once these files get large, but it seems like a solvable problem.  

Method 2:

Periodically sync a copy of the working database to iCloud.  Have a modification timestamp field in every record.  When an updated copy of the DB comes through, query all the records with newer timestamps than some reference time and update the record in the local DB from the new data.

I see many potential problems with this method:

-Have to implement something further to recognize record deletion.

-The DB file could get conflicts. It might be possible to deal with them by handling each conflict version in timestamp order.

-Determining the date to check each update from could be tricky, as it depends on which device the update is coming from.

There are a lot of potential problems with method 2, but method 1 seems doable to me...

Does anyone have any suggestions as to what might be the best course of action? Any better ideas than my "Method 1" (or reasons why it wouldn't work)?

Answer 1

I've considered the possibility of transferring a database file through iCloud but I think that I would run into classic problems of timing - slow start for the user - and corrupted databases if the app is run on multiple devices simultaneously. (iPad/iPhone for example).

Sooo. I've had to use the transaction logs method. It really is difficult to implement, but once in place, seems ok.

I am using Apple's SharedCoreData sample as the base for this work. This link requires an Apple Developer Account.

I did find a much much better solution from Tim Roadley however this only works for IOS and I needed both IOS and MacOS.

rant> iCloud development really has to get easier and more stable! /rant

Answer 2

Both method 1 and method 2 seem doable. Perhaps a combination of the two in fact - use iCloud to send a separate database file that is a subset of data - i.e. just changed items. Or maybe another file format instead of sqlite db - XML/JSON/CSV etc.

Another alternative is to do it outside of iCloud - i.e. a simple custom web service for syncing. So each change gets submitted to a central server via JSON/XML over HTTP, and then other devices pull updates from that.

Obviously it depends how much data and how many devices you want to sync across, and whether you have access to an appropriate server and/or budget to cover running such a server. iCloud will do that for "free" but all it really does is transfer files. A custom solution allows you to define your syncing model as you wish, but you have to develop and manage it and pay for it.

Answer 3

Try those two solutions from Ray Wenderlich:

Exporting/Importing data through mail: http://www.raywenderlich.com/1980/how-to-import-and-export-app-data-via-email-in-your-ios-app

File Sharing with iTunes: http://www.raywenderlich.com/1948/how-integrate-itunes-file-sharing-with-your-ios-app

I found it quite complex but helped me a lot.