How to synchronize a SQLite Core Data database with a remote MySQL database?

Question

I recently completed work on an iOS app, and everything for the most part is working the way it should with the app. I have managed to create a multi-user app that uses Core Data to persist to a SQLite DB. However, the time has come where the users would like to manage their account from a remote device, i.e. their own phone, or whatever web enabled device their using. With that said, I have done a little google searching and have discovered that I am going to need to create a "web service". Now the caveat is that I already accumulated data in the SQLite DB on the iDevice that is running the app. I would like to push the existing data to a MySQL DB or a remote machine, and have it synchronized. For example when a user updates their account on the iOS device, the change gets pushed to the MySQL DB, and if the user connects to a web service using a standard browser that is updates the SQLite DB on the iOS device.

I started learning rails because I figured it would be a good solution to create a simple web front-end for the user to manage their account with, and it exposes an API for a developer to manipulate data in the database. Basically, I would like to hear some suggestions from the community, or links that could provide a good starting point for what I'm trying to accomplish.

LyricalPanda · Accepted Answer · 2014-07-01 18:46:12Z

2

If you're looking at rails, try taking a peek at ActiveAdmin gem. It's what I used on my first iOS and rails project for a client. It gives you an administrative dashboard that'll handle a lot of what you'll be wanting if you can get it set up. It's very confusing at first, but a few weeks will give you a pretty good web solution. In addition, depending on your experience in creating servers, you might want to look at heroku for a low cost host that does all the work for you (if you start needing more processors though, Heroku gets pricy very quickly). From a github project, you can have heroku up and running your rails code in about 5 minutes flat.

As far as synchronizing your database from Server To Phone: You'll want to institute a type of last_updated_at timestamp for your models on the server DB. Now when anything is updated, you'll update the timestamp. Now the iOS app can pass a ?last_updated_at parameter to your server. This will allow your server to figure out everything that has changed since the last time they pinged the server. Then gnab it into your core data db on the phone.

For Syncing Phone to Server:

First make sure the phone is up to date before syncing (using last_updated_at param). If it's clear, then this is where it's hard. You'll need to translate the objects you want to sync from the CoreData db (since it adds it's own columns/tables automatically) and pass them up. Otherwise you can pass up your coreData db and do some kind of conversion on the server.

OR

Do a conversion in your iPhone app with an update to migrate off of CoreData. This will be a pain but it'll help in the long run if you deploy to other OS's. You'll need to create sqlite3 queries to convert the CoreData db into a new SQL DB (we were able to copy 2-3MB of data within 1.2 seconds on an iPhone 5 so it's pretty quick). Then the app will only use SQLite3 so that it can sync up the full DB to the server. Then this will make syncing with the phone easier, where it can just grab the full DB from the server and plug it in.

edited Jul 1, 2014 at 18:46

answered Jul 1, 2014 at 18:29

LyricalPanda

1,41414 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ipatch Over a year ago

Thanks for the constructive answer.

ipatch Over a year ago

yeah it was helpful, still in the process of building out the rails app :/

Shourob Datta · Accepted Answer · 2019-10-01 19:10:48Z

typedef NS_ENUM(NSInteger, RecordStatus){

RecordStatusUnchanged = 0,
RecordStatusUpdated   = 1,
RecordStatusAdded     = 2,
RecordStatusRemoved   = 3
};

Create a new SQL table named change_record. column like ( item_id, status, category_name)

For Update

change_record table: from the local database, you will get item_id, keep status RecordStatusUpdated, and set the category_name

For Add

change_record table: item_id will empty, keep status RecordStatusAdded , set the category_name

For Deletion

change_record table: from the local database, you will get item_id, keep status RecordStatusRemoved , set the category_name empty

Use a background service to check internet connection is available for each n time interval.

if change_record count > 0. then send all items(using loop) to the server

Collectives™ on Stack Overflow

How to synchronize a SQLite Core Data database with a remote MySQL database?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related