Offline/Online Database Authentication/Synchronization from jQuery Mobile

Question

The Situation: I have a database and a web app that interact to display and update tables of scientific data. My task is to construct a mobile application that will allow users to

1. Authenticate and Identify Themselves Offline for Tracking Purposes (using predefined but dynamic user-accounts and permissions stored in the cache)
2. Download Identification Data for Work in the Field (with internet access)
3. Make and Store Observations in the Field (without internet access)
4. Synchronize Observations into an Online Database at a later time

The Question: What is the best way to go about authenticating/downloading/storing/uploading the necessary data to administer such an application independent of consistent internet access? If two people adjust the same reading, and one update happens in between syncs, how do I track which tables and values have been altered, and prevent the changes from being lost?

The Current Train of Thought: My current tentative strategy on collision control to maintain a "Sync" table that stores JSON objects representing every online database transaction stored in sessionStorage and have the app continuously verifying its sync prior to uploading data, and checking this object for potential collisions on updates to specific primary keys. I would like to be able to lock tuples when they are in an editing stage, but that seems impossible with inconsistent internet access.

Sorry if this is a bit of a newb question, I'm new to the whole mobile app development thing...

tl;dr How do I prevent asynchronous changes to the database from mobile apps that go on and offline from overwriting/colliding with another individual's changes to the same database? And how do I authenticate users w/o a network connection?

Answer 1

As to synchronization you could have a look at jIO - an open-source library we are working on that can synchronize JSON documents across different types of storages and has a super-simple API.

There are a bunch of connectors available and being worked on (webDav, S3, xwiki, etc) and you can also write your own connector to hook up JIO to whereever you are supplying your JSON data from.

Then on the client you can for example set up a replicate-revision storage using the client localhost and your remote storage as storages:

 var jio_instance = jIO.newJio({
   "type": "replicaterevision",
   "storage_list": [{
     "type": "revision",
     "sub_storage": {<storage spec for your localhost storage>}
    }, {
     "type": "revision",
     "sub_storage": {<storage spec for your remote storage>}
    }]
 });

All storages in the storage_list will automatically be versioned and synchronized. So if a user is offline, retrieves a document, edits and saves it (only to localstorage, as user is offline), jIO will throw a conflict the next time the user tries to access the file when being online, because the current version on remote-storageand localstorage differ.

Then it's only a matter of writing a routine to solve any conflicts arising from multiple users editing documents while being online/offline = which version to keep/merge/etc.

Access to documents is fairly straightforward. JSON documents have meta data and attachements and you can use the following commands to modify your JSON documents:

  POST  > generate a new document
  PUT > update existing document
  GET > retrieve a document
  REMOVE > delete a document
  ALLDOCS > retrieve all documents
  PUTATTACHEMENT > add an attachment to a document
  GETATTACHEMENT > retrieve an attachment from a document
  REMOVEATTACHEMENT > delete an attachement from a document

callable like this:

  jio_instance.get({"_id":"your_doc_id"}, function (err, response) {
    // do something
  });

JIO also has a complex-queries module, which allows to run database like queries on your storages. So you could do something like this:

options = {
 query: '(author:"% Doe") AND (format:"pdf")',
 limit: [0, 100],
 sort_on: [['last_modified', 'descending'], ['creation_date', 'descending']],
 select_list: ['title'],
 wildcard_character: '%'
};

// run query
jio_instance.allDocs(options, function (error, response) {
  // do sth
});

Let me know if you have any questions.