goeko/websqlsync

Synchronize a local WebSQL DB to a server

WebSqlSync

Automatically synchronize a local WebSql database (SQLite in the navigator) to a server :

• 2 way sync : client <-> server
• Incremental synchronization (send only the necessary data)
• Works offline. All data changes are tracked and synchronized with the server once the connection returns
• Support for replicating changes to multiple devices
• works with webapp and phonegap app (iOS, Android, etc.), without changing your code

Very easy to integrate and to use (2 functions to call : initSync and syncNow), non intrusive with your existing code.

Installing

• just copy the webSqlSync.js file in your project and include it in your html :

   script src="lib/webSqlSync.js" type="application/x-javascript" charset="utf-8">/script>"
  

Usage

Initialize

You need to initialize the lib (at each startup for example).

It will automatically create 2 tables (if they don't already exists). The table new_elem is used to store all the new or modified elements and the table sync_info is used to store the date of the last sync. It will also create SQLite triggers in order to watch the INSERT or UPDATE on the tables you want to synchronize (to automatically insert the modified elements in the new_elem table):

DBSYNC.initSync(TABLES_TO_SYNC, webSqlDb, sync_info, 'http://www.myserver.com', callBackEndInit);

Where TABLES_TO_SYNC is the list of DB table that you want to sync with the server, ex :

TABLES_TO_SYNC = [
    {tableName : 'table1', idName : 'the_id'},
    {tableName : 'table2'} //if idName not specified, it will assume that it's "id"
];

And sync_info can be everything you want. It's useful to identify the client, because it will be sent to the server (it can be a user email or login, a device UUID, etc..)

Synchronize

To start the synchronization, you need to call the syncNow function. You can call it every X seconds, or after some changes for example :

DBSYNC.syncNow(callBackSyncProgress, function(result) {
     if (result.syncOK === true) {
         //Synchronized successfully
     }
});

Where callBackSyncProgress is a function called at every step of the synchronization (useful to show a progress bar with status if there is a lot of data to synchronize) :

callBackSyncProgress: function(message, percent, msgKey) {
     $('#uiProgress').html(message+' ('+percent+'%)');
},

Limitations:

• DELETE are not handled. But an easy workaround is to do a logic delete with an update (ex. UPDATE elm SET flag='DELETED')
• There are no example of generic server side sync for now. But there is our server code as an example in Java with #playframework (but it's not a generic code). Check the test/ directory
• Need to add even more JQunit test cases. But this code is working on more than 13 apps (iOS/Android) in production, with millions of data synchronized
• There is one dependency to JQuery (used only to send data with AJAX, look for jQuery.ajax). I welcome any pull request to remove this dependency