7

We have an Android app written as a Cordova AngularJS SPA which has now grown so that we need to add some work to it meaning we have to improve the synchronization part of the app. The app is mainly used offline. I can't give details of the business use but data structure and constraints can be illustrated with an over simplified car mechanic's business!

Imagine the data entities are similar to this : entity diagram

Claims are at Company, Garages and Car level, so in other words Jack may be able to see all garages and all cars in company A, Jill may be able to see all garages in Company A and only some cars in each garage, Jo may be able to see only one garage and only some of the cars in that garage. - you get the idea.

  • The app downloads information that the mechanic can see about their jobs.  They are able to see all jobs assigned to them and any other jobs that the claims they have allow them to see, so that they can pick those jobs up.  A job is a grouping of Checkpoints that need to be completed on a car.
  • The garage workshop has no data connection, the mechanic can only sync when they go back to the office.  This may need to be user initiated, not entirely sure yet though.
  • More than one mechanic can work on a job, if the same data item gets updated by both mechanics the last item synchronised wins.
  • If a data item has been updated on the server since the mechanic downloaded it to the tablet app, they then updated it and uploaded it, the mechanic's change wins.
  • Jobs may only be partially complete at time of synchronisation.
  • When the app synchronises it needs to apply the changes it holds and update what data is held locally with the most up to date information from the SQL database.  It may need to remove data from the app that is no longer relevant to the jobs being worked on currently or in the future.

The backend is hosted on Azure, data stored in an Azure SQL database.  There is an OData API we're phasing out and a Web API that can be used / added to.  The complex authorisation happens within a shared module in the APIs, the only data taken down to an app would be data the user is allowed to see.

The data will get fairly large over time.

This needs to be transactional – ie. If there is a problem with the sync we want a whole job to have been synchronized at a time, not entity type at a time.

The app has been written as a SPA Cordova application so, unless there is a compelling reason not to, it has to remain this way.

Ideas we've considered so far :

BreezeJS has been used to track changes. We could send just those changes up to the server, wait for a process on the server to apply those changes to the database and then start the download of any changes and new data required. However this could mean that the sync will take a long time to complete if lots of people are syncing at the same time and things get clogged up in a queue of some sort.

Using CouchDb/PouchDB - I know very little about this, would this work with the authorisation model we have?  And how would it work getting the changes to/from the master SQL database?

Azure offline data sync (https://docs.microsoft.com/en-us/azure/app-service-mobile/app-service-mobile-offline-data-sync) - As far as I can see this is pretty similar to using CouchDb, or have I misunderstood?

The required authorisation model is the part that seems to be the blocker to several solutions being viable, we need different users to have different sets of common data at a given time, this is imperative.

2

If you're going to sync against your master SQL database you will always have the same problem: how do you resolve conflicting changes?

CouchDB doesn't help you there. It explicitly does not handle conflicts and leaves it up the user to handle them (by picking sides). Other solutions also can't really help you, because to resolve a conflict you must understand the meaning of the data that is in a conflicting state. In other words, there is no generic "perfect" solution to this problem.

My advice would be to develop some solution yourself using that upload changes to a queue strategy you described. Performance likely won't be your biggest issue, but conflict resolution will be. Choose one of several strategies for handling that queue:

  1. Optimistic concurrency control

Keep track of the timestamp when a client took data out of the system, and when uploading a new version of that data check that no changes occurred. If changes did occur, either use strategy 3 or allow the user to force or discard.

  1. Locking

Allow the user to explicitly do a "check out" and "check in" operation. Some basic things to take into account: other users need to be able to see who has a lock, and take that lock away from them. Only the user with a lock may check in changes performed on data held under that lock. Look at how subversion (and clients like tortoisesvn) handles locks, it's sane, as far as such things go.

  1. Merging

This is a variation on strategy 1, except that when you detect conflicts you perform a merge. You can implement this using multiple strategies. Last one wins, first one wins, explicit user choice (possibly showing them the delta). You can reduce the risk of conflicts by tracking the data at a finer-grained level (per-field instead of per-form).

0

I don't have all the details in front of me, but...

With Azure, you can combine their Mobile App service as a backend to handle the multi-user nature of it. And if I remember correctly, you can use a SQL data instance connected to it for the actual database.

Whichever way you go, I think you're going to need a web service backend of some kind to coordinate the multi-user nature of your app. I don't think there's any good way around that requirement.

As stated by @Joeri Sebrechts, the other method basically requires your users to check in and out the data and be responsible. Some days I barely trust developers to do that.

protected by gnat Aug 3 '17 at 16:57

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.