How to sync offline HTML5 webdatabase with centralised database

Question

I'd like to be able to do the following in a HTML5 (iPad) web app:

• upload data to an online database (which would be probably <50Mb in size if I was to build the online database in something like SQLite)
• extract either a subset or a full copy of data to an offline webdatabase
• (travel out of 3G network coverage range)
• perform a bunch of analytic-type calculations on the downloaded data
• save parameters for my calculations to the offline webdatabase
• repeat, saving different parameter sets for several different offline analytic-type calculation sessions over an extended period
• (head back into areas with 3G network coverage)
• sync the saved parameters from my offline webdatabase to the central, online database

I'm comfortable with every step up till the last one...

I'm trying to find information on whether it's possible to sync an offline webdatabase with a central database, but can't find anything covering the topic. Is it possible to do this? If so, could you please supply link/s to information on it, OR describe how it would work in enough detail to implement it for my specific app?

Thanks in advance

Gyre

Answer 1

I haven't worked specifically with HTML5 local databases, but I have worked with mobile devices that require offline updates and resyncing to a central data store.

Whether the dataset is created on the server or on the offline client, I make sure its primary key is a UUID. I also make sure to timestamp the record each time its updated.

I also make not of when the last time the offline client was synced.

So, when resyncing to the central database, I first query the offline client for records that have changed since the last sync. I then query the central database to determine if any of those records have changed since the last sync.

If they haven't changed on the central database, I update them with the data from the offline client. If the records on the server have changed since last sync, I update them to the client.

If the UUID does not exist on the central server but does on the offline client, I insert it, and vice versa.

To purge records, I create a "purge" column, and when the sysnc query is run, I delete the record from each database (or mark it as inactive, depending on application requirements).

If both records have changed since last update, I have to either rely on user input to reconcile or a rule that specifies which record "wins".

I usually don't trust built-in database import functions, unless I'm importing into a completely empty database.

Faint

Answer 2

Steps:

1. Keep a list of changes on the local database.
2. When connected to remote database, check for any changes since last sync on remote.
3. If changes on remote side has conflicts with local changes, inform the user on what to do.
4. For all other changes, proceed with sync:
   1. download all online changes which did not change locally.
   2. upload all local changes which did not change remotely.

This method can actually work on any combination of databases, provided there is a data convertor on one side.

Alyosha

Answer 3

It looks to me, from a few sites I visited, that (as long as you are using SQLite for your Server db) it should be possible.

HTML5 webdatabases also use SQLite (although not all browsers support it and W3C seems to have dropped support for it)

so...

If you export the data using the .dump command and then import the data into the webdatabase using the $sqlite mydb.db < mydump.sql syntax you should be able to do this with some fidgeting with a php or java backend?

Then when you want to sync the "offline" data to your server just do the opposite dump from the webdatabase to a dump.sql file and import to the server database

This site explains exporting to and importing from SQLite dumps

SOURCE: dumping and restoring an SQLite DB

Madalene

if you try this and it works or if you found another solution please let me know, I just found out about this HTML5 web database feature through your post and I am extremely interested in trying it out in a project of my own some day – Madalene

Answer 4

HTML5 supports browser db SQLite , I have tried in Mozilla and chrome, and it works fine. I also have a requirement where I have some offline form, user punches the data and click on save, it saves in local browser db, and later when user syncs with the server or comes online, he can click on sync button which actually sync data from browser db any other datasource.

Terti