12

I have an iPhone (iOS) app that keeps data in a local SQLite database on each device. The app is used to manage a virtual bank account for kids to track their allowance, spending, savings, etc. (KidsBank and KidsBank Free). I am getting a lot of requests from parents to provide a sync capability between parents and possibly even their children's iOS devices.

I have considered several options, but all are tedious and non-trivial since this basically requires database replication or a new architecture. Any transaction on any device ideally should appear (sync) to all devices in the family (as immediately as possible).

Ideally, I would like the sync to be automatic & hands off

Options include (1) Use of iCloud (2) Use a direct network connection between devices (wifi) (3) Use of a server side database and web service (JSON/RESTFul)

(1) iCloud PRO: iCloud provides distributed file sync CON: iOS 5 required, SQLite database files can not be synced via iCloud, classic database replication (and non-trivial)

Using iCloud is a strong consideration. Devices can write a custom transaction log to an iCloud file where there is one file for each device identified by a unique device ID. Global unique ids (GIDs) and last change timestamps are added to each table. All participating devices will write a unique device ID to a separate file in iCloud. Upon app launch or upon log file change, the app running on a specific device will load all transactions but not those generated on their own device from the files via iCloud. The last participating device to load the transaction will remove the transaction from the file. If the device is not the last participating device, it simply signs off on the transaction and allows the file to sync via iCloud. There may be better algorithms, but the basic idea is the same - using iCloud to push around change logs.

(2) A direct wifi connection will allow two devices to manually sych. PRO: Not as complicated to manage the sync process CON: Users must both select to sync from their apps while connected on wifi

(3) Move the entire database or manage transactions on a server. PRO: Sync is no longer required CON: Typical issues for a web-driven app. Would need to rewrite the database service layer (currently in SQL) to use a remote web service. Cost of running a server (I would use AWS).

Can anyone offer some experience in syncing SQLite between multiple devices? I'm leaning in the direction of using iCloud to push around transaction logs. I'm trying to minimize cost and complexity.

Bill Bunting
  • 521
  • 6
  • 28

2 Answers2

3

Moving to iCloud is probably the best solution, as it is proven and made by Apple. You don't need to worry to much about the iOS 5 requirement, as according to most stats over 90% use it. iOS 5 is free to upgrade to. You could then rename your old version as Lite, and continue without syncing.

Syncing is probably one of the hardest things you do.

One solution I made is that all changes to a database leave a log, with timestamp, uniqueid and couple of other things to make sure the transaction is totally anonymous and totally unique. I have made an extremely simple web service that has two operations, you can add transaction to it, so I sync whenever the user is on wifi, so I push all changes, receive a result from the server, then delete the transaction records as they are synced.

The other action is to fetch the records, send the timestamp of last sync, userid and other.

All data is sent using JSON and received as such. It can easily handle tens of thousands of users, running on a small Amazon EC2 server.

This is pretty much how iCloud works, but I made this solution before iCloud. Now I am going to iCloud, but probably need to keep the server running for 1 more year or so, depends on usage.

Hope this helps you.

Trausti Thor
  • 3,722
  • 31
  • 41
  • Might be possible to replace the "small EC2 server" with a light Heroku dyno instance, which would be free. Depends on actual storage requirement. – Romain May 08 '12 at 12:55
  • Of course, anywhere you can put a simple web service on. ALl you need is to run the service and a database – Trausti Thor May 08 '12 at 12:56
  • Indeed. I mostly intended this as a note to readers - if the actual storage need is ~0, then Heroku will be cheaper (since free). – Romain May 08 '12 at 13:00
  • 1
    I realize it has been almost a year since I made the initial post. Here is an update. I did decide to implement using iCloud, first converting the SQLLite to Core Data then on to iCloud. – Bill Bunting Apr 28 '13 at 03:52
2

After finding time to get back to working on the app and also with time passing and Core Data iCloud replication maturing, I converted my app to Core Data (NSSQLiteStoreType) and monitor notifications such as persistentStoreDidImportUbiquitousContentChanges. Using lightweight migrations too. Working well.

Bill Bunting
  • 521
  • 6
  • 28
  • Hi Bill, why didn't you just use iCloud to sync the SQLite file ? Was there a constraint? Also, how long did it take to convert your app to Core Data? – pAkY88 Oct 14 '15 at 21:42
  • I had a data abstraction layer so the conversion involved only rewriting the data layer for the most part. It is not possible to simply move a SQLLite file to iCloud since there would be database concurrency issues. I converted to Core Data successfully in about 30 days start to release (part time, just me). Time will depend on the size of the data and complexity of the app. I did get bit by an issue with Core Data / iCloud where some users have lost their data, which was out of my control. I may not use Core Data over iCloud in the future. – Bill Bunting Oct 15 '15 at 01:33
  • Thanks for the info! By "database concurrency issues" you mean the fact that there may be modifications on the database at the same time? If so, if iCloud is used in order to sync a file across multiple devices belonging to the same user, I don't really think it's an issue. – pAkY88 Oct 15 '15 at 08:28
  • Hi Guy especially @Bill Bunting, I know it is an old thread, but I just got in to this mobile thingy, created a basic app using Sqlite for backend. Now it is time for me to make app supports iCloud so that user can see same data on different devices of theirs. Could someone point me to the right direction. I read many posts and still not seeing a clear direction how to tackle this the right way. thanks in advance. – HaiNguyen Dec 12 '17 at 21:09
  • @HaiNguyen, First, if you are using SQL to access a SQLite database, you need to migrate to CoreData. Then, you can sync with iCloud. https://developer.apple.com/library/content/documentation/Cocoa/Conceptual/CoreData/index.html and https://developer.apple.com/library/content/documentation/General/Conceptual/iCloudDesignGuide/Chapters/DesignForCoreDataIniCloud.html Apple provides plenty of resources and sample code. Good luck. – Bill Bunting Dec 13 '17 at 01:29