NAV Navbar
Objective-C Java .NET C# JavaScript XML

Introduction

Welcome to the SQLite-sync.com documentation!

We have language bindings in JavaScript, .NET C#, Java and Objective-C! You can view code examples in the dark area to the right, and you can switch the programming language of the examples with the tabs in the top right.

Samples

You can find samples implementations on various platform (iOS ObjectiveC, Android Java, Cordova, HTML5, Xamarin, .NET, Uniwersal Windows Platform) on our GitHub page here

Demo is based on MySQL sample database SAKILA. You can download it here here. You can also use our demo webservice under http://demo.sqlite-sync.com:8081/SqliteSync/API3 (Under service maintenance at the moment)

Installation

Prerequisites

To make server work you need Apache Tomcat 8. You can find documentation on how to install Tomcat server in various platforms here Apache Tomcat 8.

After installing Apache Tomcat go to the Tomcat Web Application Manager. Do the following steps:

  1. Go to site and fill in the form on the bottom of the page to get SqliteSync_315.zip file
  2. Go to ‘WAR file to deploy’ section on your Tomcat Web Application Manager
  3. Select SqliteSync.war from previously downloaded package
  4. Deploy

After succesfully deploy you should see a new application: /SqliteSync_315 on your Tomcat application list. To check if everything is working as expected:

  1. Start your browser
  2. Enter http://your_server_address/SqliteSync_315/API3/
  3. You should see message: 'API[3.1.5] SQLite-Sync.COM is working correctly!’

Configuration steps

First you need to adjust website configuration file (web.xml), then you need to change main configuration file (sync.properties).

Website configuration (web.xml)

Go to your_webapps_folder/SqliteSync_315/WEB-INF/web.xml and open for edit. Navigate to section:

<env-entry>
<env-entry-name>working-dir</env-entry-name>
<env-entry-type>java.lang.String</env-entry-type>
<env-entry-value>/your/working/dir/sqlite-sync/</env-entry-value>
</env-entry>

change env-entry-value key and point to working dir where SQLite-sync.com server will store log files, temporary files and configuration. Create manually a subfolder named config. Create atext file sync.properties in folder config. The path should look like this:

\working_dir\config\sync.properties

IMPORTANT Restart service after changing web.xml. Make sure that Tomcat has read/write access to working dir.

WebService main configuration file

sync.properties - webservice main configuration file

DB_ENGINE = mssql
DBURL = jdbc:sqlserver://server\instance:1433;DatabaseName=dbname
DBUSER = sa
DBPASS = pass
DBDRIVER = com.microsoft.sqlserver.jdbc.SQLServerDriver
DATE_FORMAT = yyyy-MM-dd HH:mm:ss
HISTORY_DAYS = 7
LOG_LEVEL = 4

DB_ENGINE = mysql
DBURL = jdbc:mysql://server:3306/dbname?rewriteBatchedStatements=true
DBUSER = user
DBPASS = pass
DBDRIVER = com.mysql.cj.jdbc.Driver
DATE_FORMAT = yyyy-MM-dd HH:mm:ss
HISTORY_DAYS = 7
LOG_LEVEL = 4

LOG_LEVEL - defain details level for log
4: TRACE, DEBUG, INFO, WARN; (default)
3: DEBUG, INFO, WARN;
2: INFO,WARN;
1: WARN;
0 - disable logs

DATE_FORMAT - set format of date
default format: yyyy-MM-dd HH:mm:ss

HISTORY_DAYS - How long files with sync data will be kept
default value: 7

When you use MySQL database DO NOT remove
?rewriteBatchedStatements=true
from the end of the connection string

Supported databases

SQLite-sync.com support those databases:

Oracle and PostgreSQL are not supported in free version.

Supported columns data types

SQLite-sync.com uses own conversion table to match column data types when schema from master database is converted to sqlite database.

Types:

are converted to sqlite type “BLOB”

Types:

are converted to sqlite type “TEXT”

Types:

are converted to sqlite type “INTEGER”

Types:

are converted to sqlite type “REAL”

Other types are converted to “TEXT”.

Primary Key requirements

Single and mupltiple columns are supported as primary key.

When column is AUTO_INCREMENT, identity pool managment is handled by SQLite-sync.com. It means when you insert a new record onto the device, the PK will be automatically changed for the first value available for device. For more details see Primary Key Pool Managment

Primary Key Pool Managment

When Primary Key is INTEGER with AUTO_INCREMENT set to ON pool managment is used for managing PK. Each client (when reinitializing) get it’s own ids pool. For example: client 1 = 1-5000 client 2 = 5001 - 10000

Size of this pool is defined in MergeTablesToSync->[IdentityRange]. Default value is 5000. When ids pool starting (percentage value set in IdentityTrashold) device will get a new one.

Web service

Available methods

API3

Method: GET
Path: “/API3”
Produces: TEXT_HTML
Description: control method. Returns “API[v3] SQLite-Sync.COM is working correctly!” if web service is correctly configured.

InitializeSubscriber

$.ajax({
    url: sqlitesync_SyncServerURL + "InitializeSubscriber/" + sqlitesync_syncPdaIdent,
    method: 'GET',
    scope: this,
    cache: false,
    timeout: 10 * 60 * 1000, //4min
    success: function (response, status) { //Success Callback
        var responseReturn = JSON.parse(response);

        sqlitesync_AddLog('<p>Connected to server...</p>');
        sqlitesync_DB.transaction(function (tx) {

            Object.keys(responseReturn)
                  .sort()
                  .forEach(function(v, i) {
                        tx.executeSql(responseReturn[v],
                            null,
                            function (transaction, result) {
                                sqlitesync_AddLog('Creating object <b>' + v + '</b>...');
                            },
                            function (transaction, error) {
                                console.log('Object ' + v  + '; ' + error.message + ' (Code ' + error.code + ')');
                            });
                   });

        }, function (error) {//error
            sqlitesync_AddLog('<p>Error while syncing with the server ' + error + '</p>');
        }, function () {
            sqlitesync_AddLog('<p style=\"font-weight:bold; color:green;\">Synchronization completed</p>');
        });

    },
    failure: function (result, request) {
        var statusCode = result.status;
        var responseText = result.responseText;
        sqlitesync_AddLog('<p>Error while syncing with the server ' + responseText + '</p>');
    }
});
-(void)initializeSubscriber:(nonnull NSString*)subscriberId error:(NSError * _Nullable * _Nullable)error{
    NSString *requestUrlString = [NSString stringWithFormat:@"%@/InitializeSubscriber/%@", _serverURL, subscriberId];
    NSURL *requestURL = [NSURL URLWithString:requestUrlString];
    NSMutableURLRequest *request = [[NSMutableURLRequest alloc] init];

    [request setURL:requestURL];
    [request setHTTPMethod:@"GET"];

    NSHTTPURLResponse *response;

    NSData *data = [NSURLConnection sendSynchronousRequest:request returningResponse:&response error:error];

    if(!*error){
        switch (response.statusCode) {
            case 200:
                break;
            default:
                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding] forKey:NSLocalizedDescriptionKey]];
                break;
        }
    }

    if(*error) return;

    NSDictionary* schema = [NSJSONSerialization JSONObjectWithData:data options:kNilOptions error:error];

    if(*error) return;

    NSArray *keys = [[schema allKeys] sortedArrayUsingComparator:^NSComparisonResult(id a, id b) { return [a compare:b]; }];

    sqlite3 *db;
    sqlite3_stmt *stmt = NULL;

    if(sqlite3_open([_databasePath UTF8String], &db) == SQLITE_OK){
        sqlite3_exec(db, "BEGIN TRANSACTION", 0, 0, 0);

        for (NSString* key in keys) {
            if(![key containsString:@"00000"]){
                if(sqlite3_prepare_v2(db, [[schema objectForKey:key] UTF8String], -1, &stmt, NULL) != SQLITE_OK
                   || sqlite3_step(stmt) != SQLITE_DONE){
                    *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                    break;
                }
                sqlite3_reset(stmt);
            }
        }

        if(*error){
            sqlite3_exec(db, "ROLLBACK TRANSACTION", 0, 0, 0);
        }
        else{
            sqlite3_exec(db, "COMMIT", 0, 0, 0);
        }

        if(stmt){
            sqlite3_finalize(stmt);
        }

        sqlite3_close(db);
    }
    else{
        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:@"Failed to open database" forKey:NSLocalizedDescriptionKey]];
    }
}
/**
 * Recreate database schema from remote server for specific subscriber
 * @param subscriberId id of subscriber
 * @throws Exception
 */
public void initializeSubscriber(String subscriberId) throws Exception {
    HttpURLConnection connection = null;
    InputStream resultStream = null;
    String resultString = null;
    Map<String, String> schema = null;

    String requestUrl = String.format("%s/InitializeSubscriber/%s", _serverURL, subscriberId);

    try {
        connection = (HttpURLConnection) new URL(requestUrl).openConnection();

        int status = connection.getResponseCode();

        switch (status){
            case HttpURLConnection.HTTP_OK:
                resultStream = connection.getInputStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                schema = new Gson().fromJson(resultString, new TypeToken<Map<String, String>>(){}.getType());
                break;
            default:
                resultStream = connection.getErrorStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                throw new Exception(resultString);
        }
    }
    finally {
        if (resultStream != null) {
            try {
                resultStream.close();
            } catch (IOException e) {
            }
        }
        if (connection != null) {
            connection.disconnect();
        }
    }

    List<String> keys = new ArrayList<String>();
    Set<String> keySet = schema.keySet();
    for(String key : keySet){
        keys.add(key);
    }
    Collections.sort(keys);

    SQLiteDatabase db = openOrCreateDatabase(_dbFileName, null);
    try{
        db.beginTransaction();
        for (String key : keys) {
            if(!key.startsWith("00000")){
                db.execSQL(schema.get(key));
            }
        }
        db.setTransactionSuccessful();
    }
    finally {
        db.endTransaction();
        db.close();
    }
}
/// <summary>
/// Reinitialize subscriber
/// </summary>
/// <param name="subscriberId">identifier of subscriber</param>
public void ReinitializeDatabase(string subscriberId)
{
    //getting data from server
    var request = new RestRequest("InitializeSubscriber/{subscriberUUID}", Method.GET);
    request.AddUrlSegment("subscriberUUID", subscriberId);
    request.AddHeader("Accept", "*/*");
    IRestResponse response = wsClient.Execute(request);
    Dictionary<string, string> dbSchema = JsonConvert.DeserializeObject<Dictionary<string, string>>(response.Content);

    //we need to sort by key
    var tmp = dbSchema.OrderBy(key => key.Key);
    var dbSchemaSorted = tmp.ToDictionary((keyItem) => keyItem.Key, (valueItem) => valueItem.Value);

    using (SQLiteConnection conn = new SQLiteConnection(this.connString))
    {
        using (SQLiteCommand cmd = new SQLiteCommand())
        {
            cmd.Connection = conn;
            conn.Open();

            SQLiteHelper sh = new SQLiteHelper(cmd);

            sh.BeginTransaction();

            try
            {
                foreach (KeyValuePair<string, string> entry in dbSchemaSorted)
                    if (!entry.Key.StartsWith("00000"))
                    {
                        sh.Execute(entry.Value);
                    }
                sh.Commit();
            }
            catch (Exception ex)
            {
                sh.Rollback();
                throw ex;
            }

            conn.Close();
        }
    }
}

Method: GET
Path: “/InitializeSubscriber/{subscriberUUID}”
Produces: TEXT_PLAIN
Description: Reinitialize subscriber, create empty schema on device, prepare master database for new subscriber.

Sync

//get changes for table (complete method)
$.ajax({
    url:  sqlitesync_SyncServerURL + "Sync/1/users", //Url of the Service
    method: 'GET',
    cache : false,
    scope:this,
    timeout: 5 * 60 * 1000,//10min
    success: function (response, status) { //Success Callback
        if(response.trim().length != 0)
        {
            var responseReturn = JSON.parse(response);
            var tableNameSync = '';
            var syncId = null;
            sqlitesync_DB.transaction(function (tx) {

                var queryInsert = null;
                var queryUpdate = null;
                var queryDelete = null;

                if (responseReturn[0].SyncId > 0) {
                    tableNameSync = responseReturn[0].TableName;
                    sqlitesync_AddLog('<p>Preparing changes for table <b>' + responseReturn[0].TableName + '</b></p>');
                    syncId = responseReturn[0].SyncId;

                    if (window.DOMParser) {
                        parser = new DOMParser();
                        xmlDoc = parser.parseFromString(responseReturn[0].Records, "text/xml");
                    }
                    else // Internet Explorer
                    {
                        xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
                        xmlDoc.async = "false";
                        xmlDoc.loadXML(responseReturn[0].Records);
                    }

                    queryInsert = responseReturn[0].QueryInsert;
                    queryUpdate = responseReturn[0].QueryUpdate;
                    queryDelete = responseReturn[0].QueryDelete;

                    /****************************/
                    tx.executeSql(responseReturn[0].TriggerInsertDrop, null, null,
                        function (transaction, error) {
                        });
                    tx.executeSql(responseReturn[0].TriggerUpdateDrop, null, null,
                        function (transaction, error) {
                        });
                    tx.executeSql(responseReturn[0].TriggerDeleteDrop, null, null,
                        function (transaction, error) {
                        });
                    /****************************/

                    for (var i = 0; i < xmlDoc.childNodes[0].childElementCount; i++) {

                        var rowValues = new Array();
                        var query = null;
                        var colCount = 0;

                        for (var ii = 0; ii < xmlDoc.childNodes[0].childNodes[i].childElementCount; ii++) {
                            rowValues[ii] = xmlDoc.childNodes[0].childNodes[i].childNodes[ii].textContent;
                            colCount++;
                        }

                        var rowId = rowValues[colCount - 1];
                        var identityCol = rowValues[0];

                        switch (xmlDoc.childNodes[0].childNodes[i].getAttribute("a")) {
                            case "1":
                                tx.executeSql(queryInsert,
                                    rowValues,
                                    function (transaction, result) {

                                    },
                                    function (transaction, error) {
                                        console.log(error);
                                    });
                                break;
                            case "2":
                                tx.executeSql(queryUpdate,
                                    rowValues,
                                    function (transaction, result) {

                                    },
                                    function (transaction, error) {
                                        console.log(error);
                                    });
                                break;
                            case "3":
                                tx.executeSql(queryDelete + "'" + rowId + "'",
                                    null,
                                    null,
                                    function (transaction, error) {
                                        console.log(error);
                                    });
                                break;
                        }
                    }

                    /****************************/
                    tx.executeSql(responseReturn[0].TriggerInsert, null, null,
                        function (transaction, error) {
                        });
                    tx.executeSql(responseReturn[0].TriggerUpdate, null, null,
                        function (transaction, error) {
                        });
                    tx.executeSql(responseReturn[0].TriggerDelete, null, null,
                        function (transaction, error) {
                        });
                    /****************************/

                }

            }, function(error){//error
                sqlitesync_AddLog('<p>Error while syncing with the server ' + error + '</p>');
                sqlitesync_SyncTableCurrentIndex++
                if(sqlitesync_SyncTableCurrentIndex < sqlitesync_SyncTableList.length)
                    sqlitesync_SyncTables();
                else
                    sqlitesync_SyncEnd();

            }, function(){//success
                if(syncId > 0){
                    $.ajax({
                        url: sqlitesync_SyncServerURL + "CommitSync/"+syncId,
                        method: 'GET',
                        scope:this,
                        cache : false,
                        timeout: 5 * 60 * 1000,//10min
                        success: function(){
                            sqlitesync_AddLog('<p style=\"font-weight:bold; color:blue;\">Received the table '+tableNameSync+'.</p>');
                            sqlitesync_SyncTableCurrentIndex++;

                            if(sqlitesync_SyncTableCurrentIndex < sqlitesync_SyncTableList.length)
                                sqlitesync_SyncTables();
                            else
                                sqlitesync_SyncEnd();
                        },
                        failure: function (result, request) {
                            var statusCode = result.status;
                            var responseText = result.responseText;
                            sqlitesync_AddLog('<p>Error while syncing with the server ' + error + '</p>');
                            sqlitesync_SyncTableCurrentIndex++

                            if(sqlitesync_SyncTableCurrentIndex < sqlitesync_SyncTableList.length)
                                sqlitesync_SyncTables();
                            else
                                sqlitesync_SyncEnd();
                        }
                    });
                } else{
                    sqlitesync_SyncTableCurrentIndex++;

                    if(sqlitesync_SyncTableCurrentIndex < sqlitesync_SyncTableList.length)
                        sqlitesync_SyncTables();
                    else
                        sqlitesync_SyncEnd();
                }
            });
        } else {
            sqlitesync_SyncTableCurrentIndex++
            if(sqlitesync_SyncTableCurrentIndex < sqlitesync_SyncTableList.length)
                sqlitesync_SyncTables();
            else
                sqlitesync_SyncEnd();
        }
    },
    failure: function (result, request) {
        var statusCode = result.status;
        var responseText = result.responseText;
        sqlitesync_AddLog('<p>Error while syncing with the server ' + responseText + '</p>');
    }
});
}
/**
 * Get changes for table from remote server for specific subscriber
 * @param subscriberId id of subscriber
 * @param tableName table name
 * @throws Exception
 */
private void getRemoteChangesForTable(String subscriberId, String tableName) throws Exception {
    HttpURLConnection connection = null;
    InputStream resultStream = null;
    String resultString = null;

    String requestUrl = String.format("%s/Sync/%s/%s", _serverURL, subscriberId, tableName);

    try {
        connection = (HttpURLConnection) new URL(requestUrl).openConnection();

        int status = connection.getResponseCode();

        switch (status){
            case HttpURLConnection.HTTP_OK:
                resultStream = connection.getInputStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                break;
            default:
                resultStream = connection.getErrorStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                throw new Exception(resultString);
        }
    }
    finally {
        if (resultStream != null) {
            try {
                resultStream.close();
            } catch (IOException e) {
            }
        }
        if (connection != null) {
            connection.disconnect();
        }
    }

    SQLiteSyncData[] syncDatas = new Gson().fromJson(resultString, SQLiteSyncData[].class);

    for(SQLiteSyncData syncData : syncDatas){
        if(syncData.SyncId > 0) {
            SQLiteDatabase db = null;

            try{
                db = openOrCreateDatabase(_dbFileName, null);
                db.beginTransaction();

                if(syncData.TriggerInsertDrop.length() > 0){
                    db.execSQL(syncData.TriggerInsertDrop);
                }
                if(syncData.TriggerUpdateDrop.length() > 0){
                    db.execSQL(syncData.TriggerUpdateDrop);
                }
                if(syncData.TriggerDeleteDrop.length() > 0){
                    db.execSQL(syncData.TriggerDeleteDrop);
                }

                SQLiteSyncDataRecord[] records = syncData.getSQLiteSyncDataRecords();

                for(SQLiteSyncDataRecord record : records){
                    switch (record.Action){
                        case 1:
                            db.execSQL(syncData.QueryInsert, record.Columns);
                            break;
                        case 2:
                            db.execSQL(syncData.QueryUpdate, record.Columns);
                            break;
                        case 3:
                            db.execSQL(syncData.QueryDelete + "?", record.Columns);
                            break;
                    }
                }

                if(syncData.TriggerInsert.length() > 0){
                    db.execSQL(syncData.TriggerInsert);
                }
                if(syncData.TriggerUpdate.length() > 0){
                    db.execSQL(syncData.TriggerUpdate);
                }
                if(syncData.TriggerDelete.length() > 0){
                    db.execSQL(syncData.TriggerDelete);
                }

                db.setTransactionSuccessful();
            }
            finally {
                if(db != null && db.isOpen()){
                    if(db.inTransaction()){
                        db.endTransaction();
                    }
                    db.close();
                }
            }

            commitSynchronization(syncData.SyncId);
        }
    }
}
-(void)getRemoteChangesForTable:(NSString*)subscriberId tableName:(NSString*)tableName error:(NSError **)error{
    NSString *requestUrlString = [NSString stringWithFormat:@"%@/Sync/%@/%@", _serverURL, subscriberId, tableName];
    NSURL *requestURL = [NSURL URLWithString:requestUrlString];
    NSMutableURLRequest *request = [[NSMutableURLRequest alloc] init];

    [request setURL:requestURL];
    [request setHTTPMethod:@"GET"];

    NSHTTPURLResponse *response;

    NSData *data = [NSURLConnection sendSynchronousRequest:request returningResponse:&response error:error];

    if(!*error){
        switch (response.statusCode) {
            case 200:
                break;
            default:
                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding] forKey:NSLocalizedDescriptionKey]];
                break;
        }
    }

    if(*error) return;

    NSArray<SQLiteSyncData*> *syncDatas = [SQLiteSyncData arrayOfModelsFromData:data error:error];

    if(*error) return;

    for(SQLiteSyncData *syncData in syncDatas){        
        if([syncData.SyncId intValue] > 0){
            sqlite3 *db;
            sqlite3_stmt *stmt = nil;

            if(sqlite3_open([_databasePath UTF8String], &db) == SQLITE_OK){
                sqlite3_exec(db, "BEGIN TRANSACTION", 0, 0, 0);

                if(!*error && [syncData.TriggerInsertDrop length] > 0){
                    if(sqlite3_prepare_v2(db, [syncData.TriggerInsertDrop UTF8String], -1, &stmt, NULL) != SQLITE_OK
                       || sqlite3_step(stmt) != SQLITE_DONE){
                        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                    }
                }
                if(!*error && [syncData.TriggerUpdateDrop length] > 0){
                    if(sqlite3_prepare_v2(db, [syncData.TriggerUpdateDrop UTF8String], -1, &stmt, NULL) != SQLITE_OK
                       || sqlite3_step(stmt) != SQLITE_DONE){
                        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                    }
                }
                if(!*error && [syncData.TriggerDeleteDrop length] > 0){
                    if(sqlite3_prepare_v2(db, [syncData.TriggerDeleteDrop UTF8String], -1, &stmt, NULL) != SQLITE_OK
                       || sqlite3_step(stmt) != SQLITE_DONE){
                        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                    }
                    sqlite3_reset(stmt);
                }

                NSArray<SQLiteSyncDataRecord*> *records;

                if(!*error){
                    records = [syncData SQLiteSyncDataRecordsWithError:error];
                }

                if(!*error){
                    for(SQLiteSyncDataRecord *record in records){
                        if([record.action intValue] == 1 || [record.action intValue] == 2 || [record.action intValue] == 3){
                            NSString *query;

                            switch ([record.action intValue]) {
                                case 1:
                                    query = syncData.QueryInsert;
                                    break;
                                case 2:
                                    query = syncData.QueryUpdate;
                                    break;
                                case 3:
                                    query = [syncData.QueryDelete stringByAppendingString:@"?"];
                                    break;
                            }

                            if(sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, NULL) == SQLITE_OK){
                                for(int i = 0; i < [record.columns count]; i++){
                                    sqlite3_bind_text(stmt, i + 1, [[record.columns objectAtIndex:i] UTF8String], -1, SQLITE_TRANSIENT);
                                }

                                if(sqlite3_step(stmt) != SQLITE_DONE){
                                    *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                                }

                                sqlite3_reset(stmt);
                            }
                            else{
                                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                            }

                            if(*error) break;
                        }
                    }
                }

                if(!*error && [syncData.TriggerInsert length] > 0){
                    if(sqlite3_prepare_v2(db, [syncData.TriggerInsert UTF8String], -1, &stmt, NULL) != SQLITE_OK
                       || sqlite3_step(stmt) != SQLITE_DONE){
                        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                    }
                    sqlite3_reset(stmt);
                }
                if(!*error && [syncData.TriggerUpdate length] > 0){
                    if(sqlite3_prepare_v2(db, [syncData.TriggerUpdate UTF8String], -1, &stmt, NULL) != SQLITE_OK
                       || sqlite3_step(stmt) != SQLITE_DONE){
                        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                    }
                    sqlite3_reset(stmt);
                }
                if(!*error && [syncData.TriggerDelete length] > 0){
                    if(sqlite3_prepare_v2(db, [syncData.TriggerDelete UTF8String], -1, &stmt, NULL) != SQLITE_OK
                       || sqlite3_step(stmt) != SQLITE_DONE){
                        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                    }
                    sqlite3_reset(stmt);
                }

                if(*error){
                    sqlite3_exec(db, "ROLLBACK TRANSACTION", 0, 0, 0);
                }
                else{
                    sqlite3_exec(db, "COMMIT", 0, 0, 0);
                }

                if(stmt){
                    sqlite3_finalize(stmt);
                }

                sqlite3_close(db);
            }
            else{
                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:@"Failed to open database" forKey:NSLocalizedDescriptionKey]];
            }

            if(*error) return;

            [self commitSynchronization:syncData.SyncId error:error];

            if(*error) return;
        }
    }
}
/// <summary>
/// Get changes from server.
/// </summary>
/// <param name="subscriberId">identifier of subscriber</param>
private void GetChangesFromServer(string subscriberId)
{
    using (SQLiteConnection conn = new SQLiteConnection(this.connString))
    {
        using (SQLiteCommand cmd = new SQLiteCommand())
        {
            cmd.Connection = conn;
            conn.Open();

            SQLiteHelper sh = new SQLiteHelper(cmd);

            //get list of tables in local database
            DataTable tables = sh.Select("select tbl_Name from sqlite_master where type='table';");

            foreach (DataRow table in tables.Rows)
            {
                try
                {
                    sh.BeginTransaction();

                    //get changes from server for current table
                    var request = new RestRequest("Sync/{subscriberUUID}/{tableName}", Method.GET);
                    request.AddUrlSegment("subscriberUUID", subscriberId);
                    request.AddUrlSegment("tableName", table["tbl_Name"].ToString());
                    request.AddHeader("Accept", "*/*");
                    IRestResponse response = wsClient.Execute(request);
                    List<DataObject> tablesData = JsonConvert.DeserializeObject<List<DataObject>>(response.Content);

                    //parse resposonse
                    foreach (DataObject tableData in tablesData)
                        if (tableData.SyncId > 0)
                        {
                            sh.Execute(tableData.TriggerDeleteDrop);
                            sh.Execute(tableData.TriggerInsertDrop);
                            sh.Execute(tableData.TriggerUpdateDrop);

                            XmlDocument xmlRecords = new XmlDocument();
                            xmlRecords.LoadXml(tableData.Records);
                            XPathNavigator oRecordsPathNavigator = xmlRecords.CreateNavigator();
                            XPathNodeIterator oRecordsNodesIterator = oRecordsPathNavigator.Select("/records/r");

                            foreach (XPathNavigator oCurrentRecord in oRecordsNodesIterator)
                            {
                                string action = oCurrentRecord.GetAttribute("a", "");
                                XmlDocument xmlRecord = new XmlDocument();
                                xmlRecord.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\"?><columns>" + oCurrentRecord.InnerXml + "</columns>");
                                XPathNavigator oColumnsPathNavigator = xmlRecord.CreateNavigator();
                                XPathNodeIterator oColumnsNodesIterator = oColumnsPathNavigator.Select("/columns/c");
                                int coumnsCount = oColumnsNodesIterator.Count;

                                SQLiteParameter[] parameters = new SQLiteParameter[coumnsCount];
                                int idx = 0;
                                foreach (XPathNavigator oCurrentColumn in oColumnsNodesIterator)
                                {
                                    SQLiteParameter parameter = new SQLiteParameter();
                                    parameter.Value = oCurrentColumn.InnerXml;
                                    parameters[idx] = parameter;
                                    idx++;
                                }

                                switch (action)
                                {
                                    case "1"://insert
                                        sh.Execute(tableData.QueryInsert, parameters);
                                        break;
                                    case "2"://update
                                        sh.Execute(tableData.QueryUpdate, parameters);
                                        break;
                                    case "3"://delete
                                        sh.Execute(tableData.QueryDelete, parameters);
                                        break;
                                }
                            }

                            sh.Execute(tableData.TriggerDelete);
                            sh.Execute(tableData.TriggerInsert);
                            sh.Execute(tableData.TriggerUpdate);

                            //now we need to tell server that we successfully received changes
                            request = new RestRequest("CommitSync/{syncId}", Method.GET);
                            request.AddUrlSegment("syncId", tableData.SyncId.ToString());
                            request.AddHeader("Accept", "*/*");
                            IRestResponse responseCommit = wsClient.Execute(request);
                        }

                    sh.Commit();

                }
                catch (Exception ex)
                {
                    sh.Rollback();
                    throw ex;
                }
            }

            conn.Close();
        }
    }
}
<?xml version="1.0" encoding="utf-8"?>
<records>
  <r a="1">
    <c>2</c>
    <c>Document</c>
    <c>75541</c>
    <c>2014-02-13 00:00:00</c>
    <c>665.000</c>
    <c>2c93d64e-cc72-11e3-87e0-f82fa8e587f9</c>
  </r>
  <r a="2">
    <c>4</c>
    <c>Document 4</c>
    <c>4879</c>
    <c>2014-04-23 13:44:48</c>
    <c>4875.000</c>
    <c>2c93d765-cc72-11e3-87e0-f82fa8e587f9</c>
  </r>
</records>

Method: GET
Path: /Sync/{subscriberUUID}/{tableName}“
Produces: TEXT_PLAIN
Description: Get changed data. Params:

  1. subscriberUUID - identifier of subscriber
  2. tableName - name of table from database (without schema)

For sample response see XML tab.

CommitSync

$.ajax({
    url: sqlitesync_SyncServerURL + "CommitSync/"+syncId,
    method: 'GET',
    scope:this,
    cache : false,
    timeout: 5 * 60 * 1000,//10min
    success: function(){

    },
    failure: function (result, request) {

    }
});
/**
 * Send info to remote server about successful single table synchronization
 * @param syncId id of synchronization
 * @throws Exception
 */
private void commitSynchronization(@NonNull int syncId) throws Exception {
    HttpURLConnection connection = null;
    InputStream resultStream = null;
    String resultString = null;

    String requestUrl = String.format("%s/CommitSync/%s", _serverURL, syncId);

    try {
        connection = (HttpURLConnection) new URL(requestUrl).openConnection();

        int status = connection.getResponseCode();

        switch (status){
            case HttpURLConnection.HTTP_OK:
                resultStream = connection.getInputStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                break;
            default:
                resultStream = connection.getErrorStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                throw new Exception(resultString);
        }
    }
    finally {
        if (resultStream != null) {
            try {
                resultStream.close();
            } catch (IOException e) {
            }
        }
        if (connection != null) {
            connection.disconnect();
        }
    }
}
-(void)commitSynchronization:(NSNumber*)syncId error:(NSError **)error{
    NSString *requestUrlString = [NSString stringWithFormat:@"%@/CommitSync/%@", _serverURL, syncId];
    NSURL *requestURL = [NSURL URLWithString:requestUrlString];
    NSMutableURLRequest *request = [[NSMutableURLRequest alloc] init];

    [request setURL:requestURL];
    [request setHTTPMethod:@"GET"];

    NSHTTPURLResponse *response;

    NSData *data = [NSURLConnection sendSynchronousRequest:request returningResponse:&response error:error];

    if(!*error){
        switch (response.statusCode) {
            case 200:
                break;
            default:
                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding] forKey:NSLocalizedDescriptionKey]];
                break;
        }
    }
}

    var request = new RestRequest("CommitSync/{syncId}", Method.GET);
    request.AddUrlSegment("syncId", tableData.SyncId.ToString());
    request.AddHeader("Accept", "*/*");
    IRestResponse responseCommit = wsClient.Execute(request);

Method: GET
Path: ”/CommitSync/{syncId}“
Produces: TEXT_PLAIN
Description: If device recieved all changes without error this method sould be call to tell server that there was no errors during reciving package. Params:

  1. syncId - id of data package

Send

$.ajax({
    url: sqlitesync_SyncServerURL + "Send",
    method: 'POST',
    headers: {
            'Content-Type':'application/json'
    },
    cache : false,
    scope:this,
    data: JSON.stringify({ "subscriber" : sqlitesync_syncPdaIdent, "content": sqlitesync_SyncDataToSend , "version" : "3" }),
    dataType: 'json',
    timeout: 5 * 60 * 1000,//10min
    success: function (response, status) { //Success Callback

    },
    failure: function (result, request) {

    }
});
/**
 * Send local changes to webservice
 * @param subscriberId id of subscriber
 * @throws Exception
 */
private void sendLocalChanges(String subscriberId) throws Exception {
    String query;
    Cursor cursor = null;
    SQLiteDatabase db = null;

    StringBuilder builder = new StringBuilder();

    try {
        db = openOrCreateDatabase(_dbFileName, null);

        List<String> tables = new ArrayList<String>();
        query = "select tbl_Name from sqlite_master where type='table' and sql like '%RowId%'  and tbl_Name != 'android_metadata';";
        cursor = db.rawQuery(query, null);
        while (cursor.moveToNext()) {
            tables.add(cursor.getString(0));
        }
        cursor.close();

        builder.append("<?xml version=\"1.0\" encoding=\"utf-8\"?><SyncData xmlns=\"urn:sync-schema\">");

        for (String tableName : tables) {
            if (!tableName.equalsIgnoreCase("MergeDelete")) {
                builder.append(String.format("<tab n=\"%1$s\">", tableName));

                builder.append("<ins>");
                query = String.format("select * from %1$s where RowId is null;", tableName);
                cursor = db.rawQuery(query, null);
                while (cursor.moveToNext()) {
                    builder.append("<r>");
                    for (int i = 0; i < cursor.getColumnCount(); i++) {
                        if (!cursor.getColumnName(i).equalsIgnoreCase("MergeUpdate")) {
                            builder.append(
                                    String.format("<%1$s><![CDATA[%2$s]]></%1$s>",
                                            cursor.getColumnName(i),
                                            cursor.getString(i)));
                        }
                    }
                    builder.append("</r>");
                }
                cursor.close();
                builder.append("</ins>");

                builder.append("<upd>");
                query = String.format("select * from %1$s where MergeUpdate > 0 and RowId is not null;", tableName);
                cursor = db.rawQuery(query, null);
                while (cursor.moveToNext()) {
                    builder.append("<r>");
                    for (int i = 0; i < cursor.getColumnCount(); i++) {
                        if (!cursor.getColumnName(i).equalsIgnoreCase("MergeUpdate")) {
                            builder.append(
                                    String.format("<%1$s><![CDATA[%2$s]]></%1$s>",
                                            cursor.getColumnName(i),
                                            cursor.getString(i)));
                        }
                    }
                    builder.append("</r>");
                }
                cursor.close();
                builder.append("</upd>");

                builder.append("</tab>");
            }
        }

        builder.append("<delete>");
        query = String.format("select TableId,RowId from MergeDelete;");
        cursor = db.rawQuery(query, null);
        while (cursor.moveToNext()) {
            builder.append(
                    String.format("<r><tb>%1$s</tb><id>%2$s</id></r>",
                            cursor.getString(0),
                            cursor.getString(1)));
        }
        cursor.close();
        builder.append("</delete>");

        builder.append("</SyncData>");
    }
    finally {
        if(cursor != null && !cursor.isClosed()){
            cursor.close();
        }
        if(db != null && db.isOpen()){
            db.close();
        }
    }

    HttpURLConnection connection = null;
    InputStream resultStream = null;
    String resultString = null;

    String requestUrl = String.format("%s/Send", _serverURL);
    JSONObject inputObject = new JSONObject();

    try {
        inputObject.put("subscriber", subscriberId);
        inputObject.put("content", builder.toString());
        inputObject.put("version", "3");
        byte[] requestBytes = inputObject.toString().getBytes("UTF-8");

        connection = (HttpURLConnection) new URL(requestUrl).openConnection();
        connection.setRequestMethod("POST");
        connection.setRequestProperty("Content-Type", "application/json");
        connection.setRequestProperty("charset", "utf-8");
        connection.setRequestProperty("Content-Length", Integer.toString(requestBytes.length));

        DataOutputStream wr = new DataOutputStream(connection.getOutputStream());
        wr.write(requestBytes);
        wr.flush();
        wr.close();

        int status = connection.getResponseCode();

        switch (status){
            case HttpURLConnection.HTTP_OK:
                resultStream = connection.getInputStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                break;
            case HttpURLConnection.HTTP_NO_CONTENT:
                break;
            default:
                resultStream = connection.getErrorStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                throw new Exception(resultString);
        }
    }
    finally {
        if (resultStream != null) {
            try {
                resultStream.close();
            } catch (IOException e) { }
        }
        if (connection != null) {
            connection.disconnect();
        }
    }
}
-(void)sendLocalChanges:(NSString*)subscriberId error:(NSError **)error{
    sqlite3 *db;
    sqlite3_stmt *stmt;
    NSString *query;

    NSMutableString *builder = [NSMutableString string];

    [builder appendString:@"<?xml version=\"1.0\" encoding=\"utf-8\"?><SyncData xmlns=\"urn:sync-schema\">"];

    if(sqlite3_open([_databasePath UTF8String], &db) == SQLITE_OK){
        NSMutableArray<NSString*> *tables = [NSMutableArray array];

        query = @"select tbl_Name from sqlite_master where type='table' and sql like '%RowId%';";
        if(sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, NULL) == SQLITE_OK){
            while(sqlite3_step(stmt) == SQLITE_ROW) {
                [tables addObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 0)]];
            }
        }
        else{
            *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
        }

        if(!*error){
            for(NSString *tableName in tables){
                if([tableName caseInsensitiveCompare:@"MergeDelete"] != NSOrderedSame){
                    [builder appendFormat:@"<tab n=\"%@\">", tableName];

                    [builder appendString:@"<ins>"];
                    query = [NSString stringWithFormat:@"select * from %@ where RowId is null;", tableName];
                    if(sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, NULL) == SQLITE_OK){
                        while(sqlite3_step(stmt) == SQLITE_ROW) {
                            [builder appendString:@"<r>"];
                            for(int i = 0; i < sqlite3_column_count(stmt); i++){
                                NSString *columnName = [NSString stringWithUTF8String:sqlite3_column_name(stmt, i)];
                                if([columnName caseInsensitiveCompare:@"MergeUpdate"] != NSOrderedSame){
                                    [builder appendFormat:@"<%1$@><![CDATA[%2$s]]></%1$@>", columnName, sqlite3_column_text(stmt, i)];
                                }
                            }
                            [builder appendString:@"</r>"];
                        }
                    }
                    else{
                        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                        break;
                    }
                    [builder appendString:@"</ins>"];

                    [builder appendString:@"<upd>"];
                    query = [NSString stringWithFormat:@"select * from %@ where MergeUpdate > 0 and RowId is not null;", tableName];
                    if(sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, NULL) == SQLITE_OK){
                        while(sqlite3_step(stmt) == SQLITE_ROW) {
                            [builder appendString:@"<r>"];
                            for(int i = 0; i < sqlite3_column_count(stmt); i++){
                                NSString *columnName = [NSString stringWithUTF8String:sqlite3_column_name(stmt, i)];
                                if([columnName caseInsensitiveCompare:@"MergeUpdate"] != NSOrderedSame){
                                    [builder appendFormat:@"<%1$@><![CDATA[%2$s]]></%1$@>", columnName, sqlite3_column_text(stmt, i)];
                                }
                            }
                            [builder appendString:@"</r>"];
                        }
                    }
                    else{
                        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
                        break;
                    }
                    [builder appendString:@"</upd>"];

                    [builder appendString:@"</tab>"];
                }
            }
        }

        if(!*error){
            [builder appendString:@"<delete>"];
            query = @"select TableId,RowId from MergeDelete;";
            if(sqlite3_prepare_v2(db, [query UTF8String], -1, &stmt, NULL) == SQLITE_OK){
                while(sqlite3_step(stmt) == SQLITE_ROW) {
                    [builder appendFormat:@"<r><tb>%1$s</tb><id>%2$s</id></r>", sqlite3_column_text(stmt, 0), sqlite3_column_text(stmt, 1)];
                }
            }
            else{
                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[NSString stringWithFormat:@"%s", sqlite3_errmsg(db)] forKey:NSLocalizedDescriptionKey]];
            }
            [builder appendString:@"</delete>"];
        }

        if(stmt){
            sqlite3_finalize(stmt);
        }
        sqlite3_close(db);
    }
    else{
        *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:@"Failed to open database" forKey:NSLocalizedDescriptionKey]];
    }

    [builder appendString:@"</SyncData>"];

    if(*error) return;

    NSDictionary *inputObject = @{@"subscriber": subscriberId, @"content": builder, @"version": @"3"};
    NSData *inputData = [NSJSONSerialization dataWithJSONObject:inputObject options:NSJSONWritingPrettyPrinted error:error];

    if(*error) return;

    NSString *requestUrlString = [NSString stringWithFormat:@"%@/Send", _serverURL];
    NSURL *requestURL = [NSURL URLWithString:requestUrlString];
    NSMutableURLRequest *request = [[NSMutableURLRequest alloc] init];

    [request setURL:requestURL];
    [request setHTTPMethod:@"POST"];
    [request setHTTPBody:inputData];
    [request setValue:@"application/json; charset=utf-8" forHTTPHeaderField:@"Content-Type"];
    [request setValue:[NSString stringWithFormat:@"%lu", (unsigned long)inputData.length] forHTTPHeaderField:@"Content-Length"];

    NSHTTPURLResponse *response;

    NSData *data = [NSURLConnection sendSynchronousRequest:request returningResponse:&response error:error];

    if(!*error){
        switch (response.statusCode) {
            case 200:
                break;
            case 204:
                break;
            default:
                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding] forKey:NSLocalizedDescriptionKey]];
                break;
        }
    }
}

/// <summary>
/// Send changes (updates, insert, deletes) from local database to server
/// </summary>
/// <param name="subscriberId"></param>
private void SendChanges(string subscriberId)
{
    using (SQLiteConnection conn = new SQLiteConnection(this.connString))
    {
        using (SQLiteCommand cmd = new SQLiteCommand())
        {
            cmd.Connection = conn;
            conn.Open();

            SQLiteHelper sh = new SQLiteHelper(cmd);

            DataTable tables = sh.Select("select tbl_Name from sqlite_master where type='table' and sql like '%RowId%';");

            StringBuilder sqlitesync_SyncDataToSend = new StringBuilder();
            sqlitesync_SyncDataToSend.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?><SyncData xmlns=\"urn:sync-schema\">");

            foreach (DataRow table in tables.Rows)
            {
                string tableName = table["tbl_Name"].ToString();
                if (tableName.ToLower() != "MergeDelete".ToLower())
                {
                    try
                    {
                        sqlitesync_SyncDataToSend.Append("<tab n=\"" + tableName + "\">");

                        #region new records
                        DataTable newRecords = sh.Select("select * from " + tableName + " where RowId is null;");
                        sqlitesync_SyncDataToSend.Append("<ins>");
                        foreach (DataRow record in newRecords.Rows)
                        {
                            sqlitesync_SyncDataToSend.Append("<r>");
                            foreach (DataColumn column in newRecords.Columns)
                            {
                                if (column.ColumnName != "MergeUpdate")
                                {
                                    sqlitesync_SyncDataToSend.Append("<" + column.ColumnName + ">");
                                    sqlitesync_SyncDataToSend.Append("<![CDATA[" + record[column.ColumnName].ToString() + "]]>");
                                    sqlitesync_SyncDataToSend.Append("</" + column.ColumnName + ">");
                                }
                            }
                            sqlitesync_SyncDataToSend.Append("</r>");
                        }
                        sqlitesync_SyncDataToSend.Append("</ins>");
                        #endregion

                        #region updated records
                        DataTable updRecords = sh.Select("select * from " + tableName + " where MergeUpdate > 0 and RowId is not null;");
                        sqlitesync_SyncDataToSend.Append("<upd>");
                        foreach (DataRow record in updRecords.Rows)
                        {
                            sqlitesync_SyncDataToSend.Append("<r>");
                            foreach (DataColumn column in updRecords.Columns)
                            {
                                if (column.ColumnName != "MergeUpdate")
                                {
                                    sqlitesync_SyncDataToSend.Append("<" + column.ColumnName + ">");
                                    if (record[column.ColumnName].GetType().Name == "Byte[]")
                                        sqlitesync_SyncDataToSend.Append("<![CDATA[" + Convert.ToBase64String((byte[])record[column.ColumnName]) + "]]>");
                                    else
                                        sqlitesync_SyncDataToSend.Append("<![CDATA[" + record[column.ColumnName].ToString() + "]]>");
                                    sqlitesync_SyncDataToSend.Append("</" + column.ColumnName + ">");
                                }
                            }
                            sqlitesync_SyncDataToSend.Append("</r>");
                        }
                        sqlitesync_SyncDataToSend.Append("</upd>");
                        #endregion

                        sqlitesync_SyncDataToSend.Append("</tab>");
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }

            #region deleted records
            DataTable delRecords = sh.Select("select * from MergeDelete;");
            sqlitesync_SyncDataToSend.Append("<delete>");
            foreach (DataRow record in delRecords.Rows)
            {
                sqlitesync_SyncDataToSend.Append("<r>");
                sqlitesync_SyncDataToSend.Append("<tb>" + record["TableId"].ToString() + "</tb>");
                sqlitesync_SyncDataToSend.Append("<id>" + record["RowId"].ToString() + "</id>");
                sqlitesync_SyncDataToSend.Append("</r>");
            }
            sqlitesync_SyncDataToSend.Append("</delete>");
            #endregion

            sqlitesync_SyncDataToSend.Append("</SyncData>");

            #region send changes to server
            JsonObject inputObject = new JsonObject();
            inputObject.Add("subscriber", subscriberId);
            inputObject.Add("content", sqlitesync_SyncDataToSend.ToString());
            inputObject.Add("version", "3");

            System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
            byte[] bytes = encoding.GetBytes(inputObject.ToString());

            var request = new RestRequest("Send", Method.POST);
            request.AddHeader("Content-Type", "application/json");
            request.AddHeader("Accept", "*/*");
            request.AddHeader("charset", "utf-8");
            request.AddHeader("Content-Length", bytes.Length.ToString());

            request.AddParameter("application/json; charset=utf-8", inputObject.ToString(), ParameterType.RequestBody);
            request.RequestFormat = DataFormat.Json;

            IRestResponse response = wsClient.Execute(request);
            #endregion

            #region clear update marker
            foreach (DataRow table in tables.Rows)
            {
                string tableName = table["tbl_Name"].ToString().ToLower();
                if (tableName != "MergeDelete".ToLower() && tableName != "MergeIdentity".ToLower())
                {
                    string updTriggerSQL = (string)sh.ExecuteScalar("select sql from sqlite_master where type='trigger' and name like 'trMergeUpdate_" + tableName + "'");
                    sh.Execute("drop trigger trMergeUpdate_" + tableName + ";");
                    sh.Execute("update " + tableName + " set MergeUpdate=0 where MergeUpdate > 0;");
                    sh.Execute(updTriggerSQL);
                }

                if (tableName == "MergeIdentity".ToLower())
                    sh.Execute("update MergeIdentity set MergeUpdate=0 where MergeUpdate > 0;");
            }
            #endregion

            #region clear delete marker
            sh.Execute("delete from MergeDelete");
            #endregion

            conn.Close();
        }
    }
}

<?xml version="1.0" encoding="utf-8"?>
<SyncData xmlns="urn:sync-schema">
  <tab n="documents">
    <ins></ins>
    <upd>
      <r>
        <docId><![CDATA[2]]></docId>
        <docName><![CDATA[Document 2]]></docName>
        <docSize><![CDATA[75541]]></docSize>
        <docDate><![CDATA[2014-02-13 00:00:00]]></docDate>
        <docValue1><![CDATA[665]]></docValue1>
        <RowId><![CDATA[2c93d64e-cc72-11e3-87e0-f82fa8e587f9]]></RowId>
      </r>
    </upd>
  </tab>
  <tab n="entities">
    <ins>
      <r>
        <entId><![CDATA[25010]]></entId>
        <entName><![CDATA[one]]></entName>
        <entAddress><![CDATA[street]]></entAddress>
        <entEnabled><![CDATA[1]]></entEnabled>
        <RowId><![CDATA[]]></RowId>
      </r>
    </ins>
    <upd></upd>
  </tab>
  <tab n="users">
    <ins></ins>
    <upd></upd>
  </tab>
  <delete></delete>
</SyncData>

Method: POST
Path: ”/Send"
Consumes: JSON
Produces: TEXT_PLAIN
Description: Send changes from device to master database.

For sample data format with changes see XML tab.

AddTable

$.ajax({
    url: sqlitesync_SyncServerURL + "AddTable/users",
    method: 'GET',
    scope:this,
    cache : false,
    timeout: 5 * 60 * 1000,//10min
    success: function(){

    },
    failure: function (result, request) {

    }
});
/**
 * Add table to synchronization
 * @param tableName table name
 * @throws Exception
 */
public void addSynchrnizedTable(String tableName) throws Exception {
    HttpURLConnection connection = null;
    InputStream resultStream = null;
    String resultString = null;

    String requestUrl = String.format("%s/AddTable/%s", _serverURL, tableName);

    try {
        connection = (HttpURLConnection) new URL(requestUrl).openConnection();

        int status = connection.getResponseCode();

        switch (status){
            case HttpURLConnection.HTTP_OK:
                resultStream = connection.getInputStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                break;
            default:
                resultStream = connection.getErrorStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                throw new Exception(resultString);
        }
    }
    finally {
        if (resultStream != null) {
            try {
                resultStream.close();
            } catch (IOException e) {
            }
        }
        if (connection != null) {
            connection.disconnect();
        }
    }
}
-(void)addSynchrnizedTable:(nonnull NSString*)tableName error:(NSError * _Nullable * _Nullable)error{
    NSString *requestUrlString = [NSString stringWithFormat:@"%@/AddTable/%@", _serverURL, tableName];
    NSURL *requestURL = [NSURL URLWithString:requestUrlString];
    NSMutableURLRequest *request = [[NSMutableURLRequest alloc] init];

    [request setURL:requestURL];
    [request setHTTPMethod:@"GET"];

    NSHTTPURLResponse *response;

    NSData *data = [NSURLConnection sendSynchronousRequest:request returningResponse:&response error:error];

    if(!*error){
        switch (response.statusCode) {
            case 200:
                break;
            default:
                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding] forKey:NSLocalizedDescriptionKey]];
                break;
        }
    }
}

        request = new RestRequest("AddTable/users", Method.GET);
        request.AddUrlSegment("syncId", tableData.SyncId.ToString());
        request.AddHeader("Accept", "*/*");
        IRestResponse responseCommit = wsClient.Execute(request);

Method: GET
Path: “/AddTable/{tableName}”
Produces: TEXT_PLAIN
Description: Add table to synchronization.

RemoveTable

$.ajax({
    url: sqlitesync_SyncServerURL + "RemoveTable/users",
    method: 'GET',
    scope:this,
    cache : false,
    timeout: 5 * 60 * 1000,//10min
    success: function(){

    },
    failure: function (result, request) {

    }
});
/**
 * Remove table from synchronization
 * @param tableName table name
 * @throws Exception
 */
public void removeSynchrnizedTable(String tableName) throws Exception {
    HttpURLConnection connection = null;
    InputStream resultStream = null;
    String resultString = null;

    String requestUrl = String.format("%s/RemoveTable/%s", _serverURL, tableName);

    try {
        connection = (HttpURLConnection) new URL(requestUrl).openConnection();

        int status = connection.getResponseCode();

        switch (status){
            case HttpURLConnection.HTTP_OK:
                resultStream = connection.getInputStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                break;
            default:
                resultStream = connection.getErrorStream();
                resultString = IOUtils.toString(resultStream, "UTF-8");
                throw new Exception(resultString);
        }
    }
    finally {
        if (resultStream != null) {
            try {
                resultStream.close();
            } catch (IOException e) {
            }
        }
        if (connection != null) {
            connection.disconnect();
        }
    }
}
-(void)removeSynchrnizedTable:(nonnull NSString*)tableName error:(NSError * _Nullable * _Nullable)error{
    NSString *requestUrlString = [NSString stringWithFormat:@"%@/RemoveTable/%@", _serverURL, tableName];
    NSURL *requestURL = [NSURL URLWithString:requestUrlString];
    NSMutableURLRequest *request = [[NSMutableURLRequest alloc] init];

    [request setURL:requestURL];
    [request setHTTPMethod:@"GET"];

    NSHTTPURLResponse *response;

    NSData *data = [NSURLConnection sendSynchronousRequest:request returningResponse:&response error:error];

    if(!*error){
        switch (response.statusCode) {
            case 200:
                break;
            default:
                *error = [NSError errorWithDomain:@"com.sqlite-sync" code:0 userInfo:[NSDictionary dictionaryWithObject:[[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding] forKey:NSLocalizedDescriptionKey]];
                break;
        }
    }
}

        request = new RestRequest("RemoveTable/users", Method.GET);
        request.AddUrlSegment("syncId", tableData.SyncId.ToString());
        request.AddHeader("Accept", "*/*");
        IRestResponse responseCommit = wsClient.Execute(request);

Method: GET
Path: “/RemoveTable/{tableName}”
Produces: TEXT_PLAIN
Description: Remove table from synchronization.

Filters

Filtering data is a very useful feature. When you want limit the amount of data sent to a device - SQLite-sync filter will do the job. You won’t need to do extra coding in your app - all you will need to do is create a new data view.

Queries in sample below were written for MS SQL server.

Creating new filter

Tutorial is based on sample database. You can download it here.

Goal: send to device only documents that are my.

I’m using the device with subscriber id 1.

In the database I have a table where i’m storing documents: [dbo].[Documents], user data: [dbo].[Users] and table with relations between documents and users: [dbo].[UserDocuments].

[dbo].[User Documents] - tells me which document belongs to which user.

[dbo].[Documents] structure:

[dbo].[Documents] structure

[dbo].[Users] structure

[dbo].[Users] structure

[dbo].[UserDocuments] structure

[dbo].[UserDocuments] structure

Please notice, that in table dbo.User I have a column usrSubscriberId - it tells me exactly which user uses particular subscriber id. Based on this column I’m able to select which user id is used while the device is undergoing the synchronization process.

The next step is to prepare view (click to enlarge) :

filter

SQL code:

SELECT dbo.Documents.RowId, dbo.MergeSubscribers.SubscriberId AS pdaIdent FROM dbo.Documents INNER JOIN dbo.UserDocuments ON dbo.Documents.docId = dbo.UserDocuments.usdDocId INNER JOIN dbo.Users ON dbo.UserDocuments.usdUsrId = dbo.Users.usrId INNER JOIN dbo.MergeSubscribers ON dbo.Users.usrSubscriberId = dbo.MergeSubscribers.SubscriberId

The view needs to return two things:

  1. RowId of filtered table (dbo.Documents is this scenario)
  2. subscriberId aliased as 'pdaIdent’

You can add extra condition in this view - it’s up to you.

Save your view with with any name. I’ve named it: vw_Merge_Documents.

Setting filter for table

Last step you need to do is to tell SQLite-sync to use the created filter. You can do this by running this script:

update [dbo].[MergeTablesToSync] set [TableFilter]='[dbo].[vw_Merge_Documents]' where TableName='Documents'