Sync a database (read-only on device)

[nickk...@gmail.com]

I'm just putting together a strategy for syncing a SQLite database on the device with an online database (MySQL but that doesn't really matter I don't think).

Obviously this is for a Codename One app so I'm looking for solutions that work well with the CN1 supported libraries as much as possible.

Its to replace a system I'm currently using in a couple of apps which isn't going to be scalable beyond a few hundred or maybe a thousand entries and I want to have something that could work beyond that into 10s of 1000s of 'rows' of data.

Its only read-only on the device at this stage, I'm not trying to sync thousands of users' activities into a single online database.  But changes to the online database should be made available to the devices as soon as practical.

Here are the challenges and my thoughts.

1.  I'm trying to reduce traffic from the phone to the server to be as small as practical, so I'm thinking I seed the app with a database snapshot of the live online DB, this will be built in to the app and provide the initial data, removing the need for a long download to install that data.  I believe this is possible with CN1 and SQLite.  The app will request new data periodically (once a day or less) and only if its online, there is no harm in data getting out of date if the user isn't online and the app will continue to work fine with the data is has until it can update it.  In other words offline mode is important.

2.  It'll then store a time stamp and request new data and modifications only from that time stamp and the server will send through those items (JSON or something).  Clearly I don't want the app to be accepting and running SQL queries that are sent over the air like this so some protocol will be needed to manage this in a safer way.  Also does CN1 support https ConnectionRequests?  I haven't tried this in the past.

3. I don't have a solution for identifying deleted rows as properly deleted rows aren't available to my backend framework (i.e., past versions) without a lot of otherwise unnecessary overhead.  I was thinking one solution would be just to send a list of live row IDs from the server to the app and the app can delete any that don't match but it seems like there might be a better way...

I'm thinking of making some of this data available via in app purchase but I believe that I can handle this on the server and just ensure that the correct data is being sent to the current user based on their purchases.

So questions:

1.  Is there a standard approach for this kind of synchronization.

2.  Is there a streamlined solution for synchronizing deleted rows?

3.  Is there a better format than JSON for sending this synchronization data or is JSON adequate.  I'm wondering if a more compressed binary format might be possible.  Again I'm a bit wary of sending raw SQL or even SQLite database files but that is a possibility I thought of.  

If anyone has any advice that would be great, I'm posting it here because I need CN1 compatible solutions, I did some stack overflow searching on this type of thing but it seemed unlikely that the java solutions there would work in Codename One.

Cheers,

Nick

[Shai Almog]

Personally I prefer binary data but maybe that's just my age showing.

You are describing database replication for which there are quite a few strategies although usually they have a DB proprietary solution for that. We don't have such a strategy in place right now.

Generally replication works via timestamps and operation logs so you can repeat operations done on the main DB by querying the operation logs and following them in sequence. The main issue is that you do get collision issues and resolving those is a difficult task. As far as I know there is no standard for dealing with this between SQlite and MySQL but DB's aren't my field so who knows...