Bi-Directional Offline Sync Logic ( WebSql / IndexedDb / LocalStorage on the client side using JayData frameworks and ASP.NET Web Api2 OData REST and Sql Server on the Server)

Standard

Why

I could not find a good fully baked framework to do this so i decided to write the logic based off of the Microsoft Sync Framework algorithm. Hopefully they bake something like this into EF 7 someday.

Entity Model Setup
These are the Extra Fields Needed on each Entity/Table to make a bi-directional offline sync senerio work:

Guid (Client Generated)
_IsTombstoned = false
_UpdatedAt = UnixTime.Now

js -http://momentjs.com/docs/#/displaying/unix-offset/ and c# http://stackoverflow.com/questions/7983441/unix-time-conversions-in-c-sharp/#answer-7983514

_State = add/updated/delete is only needed in the client model (override jaydata entity and add filed that does not get passed to server)

Client Logic

  1.  Only offline sync those Enities that are needed offline (cachedEntities). We are only going to allow a subset of entities to be available in the offlinedb this will be known as the cachedEntites array there is rarely a need to sync entire database (not a safe solution as a database can grow to TB+, doubt a mobile phone could handle that 🙂
    1.  UI – we need an Offline Sync Settings page that will lists all main entities and asks if user want to store offline: TodoItems => available offine y| N
    2. ???maybe??? Need a user settings table in server database to contain offline subset entity metadata, telling what Entity Items are stored offline for a particular user

2. Use Guid’s
use client generated GUID to avoid having separate Id’s on client and server side. Every entity must have a _State , _UpdatedAt, _IsTomstoned fields to keep data in sync on the server and client side => the following are assume since we are using a bi-directional sync – the newest _UpdatedAt field either from the client or server wins the update. That means this can undo a Tombstoned items as the newer _UpdatedAt date wins

3. Sync with the server in Batches using OData
have an offline and online db instance using jaydata. Use the setSync batch (oData) alogarthm to batch the sync in sets of 200. This is because if a client if offline for a long time, lets say a year you could build up a large number of updates, possibly causing one HTTP request payload to be GB’s in size causing the request to fail or recieve an “413 Request Entity Too Large” status code. Reference here: http://jaydata.org/blog/synchronized-online-offline-data-applications-part-2-syncing-large-tables-and-tables-with-foreign-relations

4. Track Client Side Entity Changes
Everytime something is updated/deleted/added in the client simply update it in the offlinedb and set the appropriate _State = ‘add’ ‘update’ ‘delete’,also set the _UpdatedAt = datetime.UtcNow (we assume the client has the correct UTC date, we are using mobile phones connected to a cell network so this is a given, also azure is connected and has the UTC correct time

5. Client to Server Sync First

  • Every 60 seconds a batch sync will occur in the client using setInterval()
  • First check that a sync is not already in progess (datacontext.isSyncing = false)
    • if no, then check you are online (http://github.hubspot.com/offline/docs/welcome/ ) then loop all items in offlinedb. do a pre-flight check to make sure none of the items are tombstoned on the server side (if they are then a msg needs to be sent to the user), then
      • state = add => use offlinedb.entityItems.add(entity)
      • state = update => use offlinedb.entityItems.attach(entity)
      • state = delete => use offlinedb.entityItems.remove(entity)
      • state = no => do nothing
    • if  yes, datacontext.isSyncing = true then wait another 60 seconds

6. Server to client sync Second

if an item is updated/deleted on the server we need to true-up with the client
does server side trump client (server to client sync) or does client trump server (client to server sync) or does last _updatedat trump all (two way sync) ?

7. SyncInit() Logic at App load if network is available

  • Set global variable isSyncing = true;
  • First sync client to server (server will take care of any conflicts)
  • Second loop cachedEntited array and get latest updates from server to client, this will be done batches.
  • The logic in SyncInit loop will :
    1. Compare the server’s json payload’s _UpdatedAt field with local payload
    a) if datetime is the same then no change
    b) if dates are different then update the local entity with the servers entity data
  • Last set isSyncing = false;

8. ???maybe??? Need conflict resolution built into the app, for instance when a conflict is detected pop a message up to the user stating there has been a conflict between the local and server version and allow the user to choose a) use local version b) use server version might not need this due to bi-directional sync and the fact we are using an _UpdatedAt field on each entity

Server Logic
9. Each Controller Needs custom logic in the REST API Verbs

// GET (Read)
// none

// POST (Add/Create New)
// none

// PUT (Full Update)
// check for conflicts
// is item already tombstoned?
// Y: then compare _UpdatedAt dates to see if client or server wins
// If client wins then untombstone and update data, if server wins then tell send back the http status 404 Not Found (the subsequent server to client sync will clean this up)
// N: then then compare _UpdatedAt dates to see if client or server wins
// If client wins proceed with the update, if server wins then simply pass back the success response with the server data

// PATCH (Partial Update)
// same logic as PUT

// DELETE (Delete/TombStone)
// don’t do a real delete simply tombstone this entity, need a schedule sproc to clean this data up periodically

// TODO think about the logic here, if somebody deletes in the past, then somebody updates in the future what happens? See Test #3 probably send a conflict back
// if the user accepts the delete then simply update the clients _UpdatedAt at on the delete status

Tests

test senerios for bi-directional sync

1. Very late update sync from offline user
// If a user collects data from the offline mobile app and does not get access for a week, the data is marked status = add/updated locally
// Then a few weeks later the data is synced to the server, the server finds a conflict because another user updated the data at a later date
// therefore that remote server trumps the mobile app because the _UpdatedAt date is newer.
// We might want track this merge conflict and maybe let the user know what version to take (take local or take server)

2. An item is deleted on server side but the client has updated it since the last sync due to offline connectivity (Tombstoned Test)
// an item is deleted out of the database by a user using the online web app
// _IsTomstoned = true and _UpdatedAt = Unix.Now
// then an offline user of the mobile app tries to update this entry.
// The user should get a pop up stating the item has been deleted, Do you want to restore?

3. An item is deleted on the client, but due to offline connectivity and the time passed the data has been updated on the server (Very late delete sync from offline user Test)
// If a user deletes data from the offline mobile app and does not get access for a week, the data is marked status = deleted locally
// Then a few weeks later the data is synced to the server, the server finds a conflict because another user updated the data at a later date
// Should the client trump the server because it was a delete? Or should we notify the client of a conflict stating, i know you are
// trying to delete this data but somebody updated it since then…do you really want to delete?
To Read
// read this article to figure out sync alogarthm http://jtabadero.wordpress.com/2013/01/09/synchronizing-winrtsqlite-using-sync-framework-toolkit/
// IsDirty = true when you do an insert(add/post) or update (attach/put)
// that means in each
// IsTombstone = true when you delete an item and it does a soft delete on the server (have a scheduled sproc clean this up periodically).
// that means in each delete method we need to do a tombstone instead
// – sync alogathim http://techbrij.com/sync-offline-html5-indexeddb-aspnet-web-api
// – unidirectional (server to client) http://coenraets.org/blog/2012/05/simple-offline-data-synchronization-for-mobile-web-and-phonegap-applications/
// azure mobile service offline sync – http://azure.microsoft.com/en-us/documentation/articles/mobile-services-windows-store-dotnet-handling-conflicts-offline-data/

// and some borrowed logic from the Microsoft Sync Framework
// Maybe One day this should be baked into EF7 from my speculations from this blog post -http://blogs.msdn.com/b/adonet/archive/2014/12/02/ef7-priorities-focus-and-initial-release.aspx