Jacob Mumm

Over the last year, I developed a web application with a mobile client. I used the same PHP MVC framework I’ve built several other applications with, but the real learning came from the extensive use of JavaScript required for a cross-platform mobile application capable of being packaged for native distribution with PhoneGap.

In a nutshell, my application is about evaluation forms. You need to define your organization, your evaluators and your staff(evaluees). You then create custom forms with custom answer types and associate them with parts or all of your organization. The mobile app, upon login, pulls down all the records needed to be able to construct the forms on the fly in online or offline mode.

Given that I wanted to support both Android and iOS platforms with minimum development time and without having to learn a new language, I decided to rely on HTML5 and the WebSQL. jQuery Mobile was just coming out when we began our planning, so I knew I could use that for my interface. With WebSQL, I could save a large amount of information on the local device and enable the app to function offline. The challenge was going to be synchronizing my data with the server.

Data Storage

For the foundation of the app, the data storage component consists of a product called persistence.js, which abstracts the actual WebSQL interaction into a nice, clean object model.

// simple method for defining models
var Task = persistence.define('Task', {
    name : "TEXT",
    description : "TEXT",
    done : "BOOL"
});

// create new objects
var newTask = new Task();
newTask.name = "Task Name";
newTask.description = "Task Description";
newTask.done = false;
persistence.add(newTask);

// retrieval
Task.all().filter('done','=',true).list(function(tasks){ /***/ });

Synchronization

persistence.js works on the server side with node.js. I used it on top of a MySQL database. The model declarations are nearly identical to those on the client. A simple node app is required to handle the sync requests. This was accomplished using Connect and Express to provide a set of URIs for each model to sync with. The sync process works like this: first the client GETs updates, then it POSTs its own new records.

// client sync configuration
Task.enableSync('http://m.mydomain.com/taskupdates');

//server side URIs
app.get('/taskupdates', function(req, res) {
  persistenceSync.pushUpdates(req.conn, req.tx, Task,
      req.query.since, function(updates){
    res.send(updates);
  });
});

app.post('/taskupdates', function(req, res) {
  persistenceSync.receiveUpdates(req.conn, req.tx, Task,
      req.body, function(result){
    res.send(result);
  });
});

Multi-Customer Database

With persistence.js, it is simple to synchronize a WebSQL table with a MySQL table. It is powerful, but there was a huge problem when I started using it. My app is a service provided to many customers. The MySQL database contains sensitive information. I didn’t want a mobile device to hold records that belonged to a different customer. Also, in some cases, not all records needed to be pushed down to the client and only increased bandwidth usage. But there was no way to filter in persistence.js — it was all or nothing.

In the GET stage of the sync process, we call pushUpdates from the persistence.sync.server.js plugin with parameters for the Entity model and the client-passed “last-synced” variable. I worked with Zef Hemel, the creator of persistence.js, to add a subtle little change to this pushUpdates function that allows for custom filtering.

Here’s what pushUpdates looked like originally.

pushUpdates = function(session, tx, Entity, since, callback) {
  Entity.all(session).filter("_lastChange", ">", since).list(tx,
      function(items) {/***/});
};

After the change, you could pass the Entity model, which comes across as a “function” variable type. If, in your server code, you wanted to apply a number of filters to the model, you would have a Query Collection object.

pushUpdates = function(session, tx, Entity, since, callback) {
  var queryCollection;
  if(typeof(Entity) == "function"){
    queryCollection = Entity.all(session);
  }else if(typeof(Entity) == "object"){
    queryCollection = Entity;
  }
  queryCollection.filter("_lastChange", ">", since).list(tx,
      function(items) {/***/});

In the GET request on the server side, you can generate a query collection before you move call pushUpdates. If you know the client’s customer id, you can use that to filter.

For example, if our Task entity had a customer_id field.

Task = persistence.define('Task', {
  name : "TEXT",
  description : "TEXT",
  done : "BOOL",
  customer_id : "INT"
});

If you had the customer_id stored in the session, when the client requested updates, you could filter a query collection before calling pushUpdates.

app.get('/taskupdates', function(req, res) {
  var queryCollection = Task.all(req.conn)
      .filter('customer_id','=', req.session.customer_id);
  persistenceSync.pushUpdates(req.conn, req.tx, queryCollection,
      req.query.since, function(updates) {
    res.send(updates);
  });
});

TL;DR

Discrete mobile synchronization of database tables between a client and a multi-customer database is now made easy with the persistence.js library.