Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED Express NYC (Apr. 2009) with: Nafisa Sabu

Experimenting With Offline Data Synchronization Using jQuery And ColdFusion

By Ben Nadel on

Modern browsers are making it much easier to build web applications with offline capabilities. Between the application cache manifest and the SQLite databases, the web as a platform can now be used to deploy "native feeling" applications for mobile devices. As I've started to go down this road, the one area that has really remained a complete mystery to me is that of offline data synchronization (ie. keeping multiple local databases in sync with each other as well as with the live database). As such, I sat down this weekend to start playing around with the concept. What I discovered, not surprisingly, is that this offline data synchronization is super complicated.

NOTE: This is the first time that I have ever even attempted offline data synchronization. As such, please read the following exploration with extreme skepticism - I really have no idea what I was doing.

 
 
 
 
 
 
 
 
 
 

When it comes to offline data synchronization, it's not just about pushing local changes to the live server; since you can create offline storage instances on multiple devices, the live server becomes a central point for syncing data up (local to live), down (live to local), and across (device to device).

 
 
 
 
 
 
Experimenting With Offline Data Synchronization Between Client Database And Live Databases. 
 
 
 

To make matters even more complicated, each device needs to keep its own unique IDs as it will need to model complex record relationships (ie. joins) in a local context before it is synced to the live server. But, at the same time, each local client needs to know if a shared record has been updated. As such, each record will also need to have a globally unique ID that stays with a record no matter where it is synced.

NOTE: I am sure that someone is going to read the above description and suggest using a globally unique ID everywhere instead of mixing local IDs and GUIDs. Yes, you can go this route; but, I find GUIDs to be very off-putting as database indexes. This is a purely emotional constraint - but, it is one that I am not yet ready to break through. Perhaps after I learn more about database synchronization I will be more willing to bend on this point.

When I first started to play with data synchronization, I added an isSynced flag to my offline records. The idea behind this was that I would flag a record as not-synced whenever I made changes to it; however, I quickly realized that this would be worthless the second I deleted a local record - after all, you can't flag a record that doesn't exist.

To deal with the delete issue, I created an "audit" on my offline database table (this was really just an Array as you'll see in the following demo). Every time that I added, updated, or deleted a record, an audit entry was appended to the table to signal that some local mutation had taken place. Then, when I went to sync the local database to the live server, I pushed the audit records up along with the sync request.

The live server also had an audit log for its database table. This was necessary for the server to be able to "push" mutations down to the client as part of the syncing process. Remember, since multiple offline devices might make changes to shared data, changes on one device need to be pushed up to the server and then, subsequently, down to each device that needs to know about them.

 
 
 
 
 
 
Experimenting With Offline Data Synchronization Between Client Database And Live Databases. 
 
 
 

While the local audit records get flushed after each sync request, the live audit records have to be persistent. Since each device will sync independently, it is up to the individual devices to keep track of the last live-audit that they pulled down. This way, the server will know to only push down changes made after a certain point.

Now that we see the basic concepts, let's take a look at the code. I won't go into too much detailed explanation because there is a good amount of code. I tried to put a lot of comments in the code to explain what was going on; but, this was just an exploration, so it might not be the most well thought-out.

Let's start out with the client (browser). For ease of demo, I am modeling an offline database with a single array, girls. As the girls "table" is mutated, I am storing the updates in an audits property off of the girls array (girls.audits).

  • <!DOCTYPE html>
  • <html>
  • <head>
  • <title>Offline Data Sync Exploration</title>
  • <script type="text/javascript" src="./jquery-1.4.2.js"></script>
  • <script type="text/javascript">
  •  
  • // I am the collection of girls (this is meant to represent
  • // the local database store - typically we would use a
  • // SQLite database, but just trying to keep it simple).
  • var girls = [];
  •  
  • // DOM references (to be set when DOM is ready).
  • var dom = {
  • table: null,
  • sync: null,
  • form: null
  • };
  •  
  •  
  • // -------------------------------------------------- //
  • // -------------------------------------------------- //
  •  
  •  
  • // Create a girls auto-incrementing id.
  • girls.autoID = 0;
  •  
  • // Create a key for the last audit sync. This key will be
  • // used to track what changes we have taken from the live
  • // server.
  • girls.lastAuditID = 0;
  •  
  • // Create an array to hold the local audits that need to
  • // be pushed to the live server. We need this in order to
  • // be able to track DELETE actions that are no longer
  • // present in the girls collection.
  • girls.audit = [];
  •  
  •  
  • // Create helper function to lookup girls by local ID.
  • girls.getByID = function( id ){
  • // Loop over the girls to find the record with the
  • // given ID value.
  • for (var i = 0; i < this.length; i++){
  •  
  • // Return the girl if it has the matching ID.
  • if (this[ i ].id == id){
  •  
  • return( this[ i ] );
  •  
  • }
  •  
  • }
  •  
  • // If we made it this far, then the girl could not
  • // be found - just return NULL.
  • return( null );
  • };
  •  
  •  
  • // Create helper function to lookup girls by GUID.
  • girls.getByGUID = function( guid ){
  • // Loop over the girls to find the record with the
  • // given GUID value.
  • for (var i = 0; i < this.length; i++){
  •  
  • // Return the girl if it has the matching GUID.
  • if (this[ i ].guid == guid){
  •  
  • return( this[ i ] );
  •  
  • }
  •  
  • }
  •  
  • // If we made it this far, then the girl could not
  • // be found - just return NULL.
  • return( null );
  • };
  •  
  •  
  • // Create a helper function to locate a girl by ID. Unlike
  • // the findByID() method, this one returns the location,
  • // not the actual girl object.
  • girls.findByID = function( id ){
  • // Loop over the girls to find the record with the
  • // given ID value.
  • for (var i = 0; i < this.length; i++){
  •  
  • // Return the current location if the girl has a
  • // matching ID.
  • if (this[ i ].id == id){
  •  
  • return( i );
  •  
  • }
  •  
  • }
  •  
  • // If we made it this far, then the girl could not
  • // be found - just return -1 for location.
  • return( -1 );
  • };
  •  
  •  
  • // I delete the girl with the given ID.
  • girls.deleteByID = function( id ){
  • // Find the girl's location.
  • var index = this.findByID( id );
  •  
  • // Check for a valid location.
  • if (index >= 0){
  •  
  • // Remove girl from the collection.
  • var removedGirls = this.splice( index, 1 );
  •  
  • // Track the girl audit.
  • this.audit.push({
  • guid: removedGirls[ 0 ].guid,
  • action: "delete"
  • });
  •  
  • // Return true - delete successful.
  • return( true );
  • }
  •  
  • // Return false - delete was not successful.
  • return( false );
  • };
  •  
  •  
  • // I save teh given girl.
  • girls.save = function( girlData ){
  • // Check to see if we are dealing with an existing girl
  • // record or creating a new one.
  • if (girlData.id != ""){
  •  
  • // Get the existing girl that we need to update.
  • var girl = this.getByID( girlData.id );
  •  
  • // Update the properties.
  • girl.name = girlData.name;
  • girl.age = girlData.age;
  •  
  • } else {
  •  
  • // Create a new girl object. Notice that we are
  • // including boolean for sync status.
  • var girl = {
  • id: ++this.autoID,
  • name: girlData.name,
  • age: girlData.age,
  • guid: (girlData.guid || ("guid" + (new Date().getTime())))
  • };
  •  
  • // Add the girl to the local (offline) collection.
  • this.push( girl );
  •  
  • }
  •  
  • // Track the girl audit; but, only do this is there is
  • // no GUID value. The reasoning here is that the only
  • // time a GUID will be passed-in is if the update is
  • // coming from the **server** and not from the local UI.
  • // Not the best approach, but I am still learning.
  • if (!girlData.guid) {
  •  
  • this.audit.push({
  • guid: girl.guid,
  • name: girl.name,
  • age: girl.age,
  • action: "update"
  • });
  •  
  • }
  •  
  • // Return the girl object.
  • return( girl );
  • };
  •  
  •  
  • // -------------------------------------------------- //
  • // -------------------------------------------------- //
  •  
  •  
  • // I render the table based on the girls collection.
  • function renderGirls(){
  • var tbody = dom.table.find( "tbody" );
  •  
  • // Clear out any current girls.
  • tbody.empty();
  •  
  • // Loop over the girls to create a row for each record.
  • for ( var i = 0 ; i < girls.length ; i++ ){
  •  
  • // Get a reference to the current girl object.
  • var girl = girls[ i ];
  •  
  • // Append the record as HTML.
  • tbody.append(
  • "<tr rel='" + girl.id + "'>" +
  • "<td>" + girl.id + "</td>" +
  • "<td>" + girl.name + "</td>" +
  • "<td>" + girl.age + "</td>" +
  • "<td>" +
  • "<a href='#' class='edit'>Edit</a> " +
  • "<a href='#' class='delete'>Delete</a>" +
  • "</td>" +
  • "</tr>"
  • );
  •  
  • }
  • }
  •  
  •  
  • // I edit the girl with the given ID.
  • function editGirl( girlID ){
  • // Get references to the inputs.
  • var id = dom.form.find( "input[ name = 'id' ]" );
  • var name = dom.form.find( "input[ name = 'name' ]" );
  • var age = dom.form.find( "input[ name = 'age' ]" );
  •  
  • // Get the girl reference.
  • var girl = girls.getByID( girlID );
  •  
  • // Move girl data into form.
  • id.val( girl.id );
  • name.val( girl.name );
  • age.val( girl.age );
  •  
  • // Focus the first field.
  • name.focus();
  • }
  •  
  •  
  • // I delete the girl with the given ID.
  • function deleteGirl( girlID ){
  • // Delete the girl.
  • girls.deleteByID( girlID );
  •  
  • // Re-render the data table.
  • renderGirls();
  • }
  •  
  •  
  • // I save the girl form.
  • function saveForm(){
  • // Get references to the inputs.
  • var id = dom.form.find( "input[ name = 'id' ]" );
  • var name = dom.form.find( "input[ name = 'name' ]" );
  • var age = dom.form.find( "input[ name = 'age' ]" );
  •  
  • // Make sure the form is valid.
  • if ((name.val() == "") || (age.val() == "")){
  •  
  • // The most basic of error handling for the demo.
  • alert( "Please complete the form!" )
  •  
  • // Return out since we don't want to process this
  • // form data until it is valid.
  • return;
  •  
  • }
  •  
  • // Save the girl object.
  • var girl = girls.save({
  • id: id.val(),
  • name: name.val(),
  • age: age.val()
  • });
  •  
  • // Clear the form inputs.
  • dom.form.find( "input" ).val( "" );
  •  
  • // Refocus the first input.
  • name.focus();
  •  
  • // Re-render the data table.
  • renderGirls();
  • }
  •  
  •  
  • // -------------------------------------------------- //
  • // -------------------------------------------------- //
  •  
  •  
  • // I sync the current data. This both PUSHES and PULLS data
  • // to and from the server to sync in both directions.
  • function syncGirls(){
  • // Push updates to the server and get update responses.
  • //
  • // NOTE: We are using the JSON object to convert the
  • // array of audits into a JSON string for posting. This
  • // JSON object is not available in all browsers.
  • $.ajax({
  • type: "post",
  • url: "./sync.cfm",
  • data: {
  • lastAuditID: girls.lastAuditID,
  • updates: JSON.stringify( girls.audit )
  • },
  • dataType: "json",
  • success: function( response ){
  • // Clear out the local audits.
  • girls.audit = [];
  •  
  • // Save the last audit ID.
  • girls.lastAuditID = response.lastAuditID;
  •  
  • // Commit the sync locally.
  • mergeUpdates( response.updates );
  • }
  • });
  • }
  •  
  •  
  • // I merge the live updates into the local collection.
  • function mergeUpdates( updates ){
  • // Loop over the updates to merge them.
  • for (var i = 0 ; i < updates.length ; i++){
  •  
  • // Get the current update.
  • var update = updates[ i ];
  •  
  • // Get the girl at the given GUID.
  • var girl = girls.getByGUID( update.guid );
  •  
  • // Check if this update is an update or a delete.
  • if (update.action == "update"){
  •  
  • // Update.
  • girls.save({
  • id: (girl ? girl.id : ""),
  • name: update.name,
  • age: update.age,
  • guid: update.guid
  • });
  •  
  • // This is a delete. Check to make sure we have the
  • // girl locally before we try to delete her.
  • } else if (girl){
  •  
  • // Delete.
  • girls.deleteByID( girl.id );
  •  
  • }
  • }
  •  
  • // Now that we have merged the udpates, re-render the
  • // the girls table.
  • renderGirls();
  • }
  •  
  •  
  • // -------------------------------------------------- //
  • // -------------------------------------------------- //
  •  
  •  
  • // When the DOM is ready, initialize the scripts.
  • $(function(){
  •  
  • // Get the DOM references.
  • dom.table = $( "#girls");
  • dom.sync = $( "#sync" );
  • dom.form = $( "#form" );
  •  
  • // Bind to the form to make sure that submits gets
  • // handled locally.
  • dom.form.submit(
  • function( event ){
  • // Prevent the default event since we are going
  • // to process it locally.
  • event.preventDefault();
  •  
  • // Save the form.
  • saveForm();
  • }
  • );
  •  
  •  
  • // Bind to the table to listen form action events.
  • dom.table.click(
  • function( event ){
  • var target = $( event.target );
  •  
  • // In any case, we want to prevent the default
  • // event since the table contains no valid links.
  • event.preventDefault();
  •  
  • // Check for edit action.
  • if (target.is( ".edit" )){
  •  
  • // Edit the girl with the given ID.
  • editGirl(
  • target.closest( "tr" ).attr( "rel" )
  • );
  •  
  • // Check for delete action.
  • } else if (target.is( ".delete" )){
  •  
  • // Delete the girl with the given ID.
  • if (confirm( "Delete girl?" )){
  •  
  • deleteGirl(
  • target.closest("tr").attr("rel")
  • );
  •  
  • }
  • }
  • }
  • );
  •  
  •  
  • // Bind the sync button.
  • dom.sync.click(
  • function( event ){
  • // Prevent the default link event.
  • event.preventDefault();
  •  
  • // Sync data with the server.
  • syncGirls();
  • }
  • );
  •  
  •  
  • });
  •  
  • </script>
  • </head>
  • <body>
  •  
  • <h1>
  • Offline Data Sync Exploration
  • </h1>
  •  
  •  
  • <!-- BEGIN: Data Table. -->
  • <table id="girls" border="1" cellspacing="2" cellpadding="5">
  • <thead>
  • <tr>
  • <th>
  • ID
  • </th>
  • <th>
  • Name
  • </th>
  • <th>
  • Age
  • </th>
  • <th>
  • <br />
  • </th>
  • </tr>
  • </thead>
  •  
  • <!-- This will be populated on Sync. -->
  • <tbody />
  • </table>
  • <!-- END: Data Table. -->
  •  
  •  
  • <p>
  • <a id="sync" href="##">Sync Offline Data</a>
  • </p>
  •  
  •  
  • <!-- BEGIN: Add/Edit Form. -->
  • <form id="form">
  •  
  • <!-- I am the *local* ID of the record. --->
  • <input type="hidden" name="id" value="" />
  •  
  • <p>
  • <strong>Name:</strong>
  • <input type="text" name="name" value="" size="30" />
  • </p>
  •  
  • <p>
  • <strong>Age:</strong>
  • <input type="text" name="age" value="" size="7" />
  • </p>
  •  
  • <p>
  • <button type="submit">Save</button>
  • </p>
  •  
  • </form>
  • <!-- END: Add/Edit Form. -->
  •  
  • </body>
  • </html>

When I post the audits to the server, I am using the JSON.stringify() method in order to serialize the audits collection. I am not sure how many browsers support the JSON object but, I know it is not completely cross-browser compatible (this was just an exploration).

To mimic GUID creation on the local device, I am simply using the current timestamp. While I know that this is in no way a valid GUID, for this exploration, this approach was enough to create unique IDs between the devices. I assume that a true SQLite database would offer some GUID creation functionality.

Now, let's take a look at the ColdFusion code which is where are centralized database is going to reside. Here is the Application.cfc; as you can see, I am modeling our live database with a ColdFusion query object.

Application.cfc

  • <cfcomponent
  • output="false"
  • hint="I define the application settings and event handlers.">
  •  
  • <!--- Define the application settings. --->
  • <cfset this.name = hash( getCurrentTemplatePath() ) />
  • <cfset this.applicationTimeout = createTimeSpan( 0, 0, 30, 0 ) />
  •  
  • <!--- Define the request settings. --->
  • <cfsetting
  • requesttimeout="10"
  • showdebugoutput="false"
  • />
  •  
  •  
  • <cffunction
  • name="onApplicationStart"
  • access="public"
  • returntype="boolean"
  • output="false"
  • hint="I initialize the application.">
  •  
  • <!--- Define the primary "data table" for this demo. --->
  • <cfset application.girls = queryNew(
  • "id, name, age, guid",
  • "cf_sql_integer, cf_sql_varchar, cf_sql_integer, cf_sql_varchar"
  • ) />
  •  
  • <!---
  • Define a auto-increment value for girls. This helps us
  • to create new record IDs without a true datatable key.
  • --->
  • <cfset application.girlsAutoID = 0 />
  •  
  • <!---
  • Define the audit table for our primary data table. This
  • contains meta information about when the data table was
  • updated and what records were updated.
  • --->
  • <cfset application.girlsRemoteAudit = queryNew(
  • "id, guid",
  • "cf_sql_integer, cf_sql_varchar"
  • ) />
  •  
  • <!---
  • Define a auto-increment value for girls audit. This
  • helps us to create new record IDs without a true
  • datatable key.
  • --->
  • <cfset application.girlsRemoteAuditAutoID = 0 />
  •  
  • <!--- Return true so the page can load. --->
  • <cfreturn true />
  • </cffunction>
  •  
  •  
  • <cffunction
  • name="onRequestStart"
  • access="public"
  • returntype="boolean"
  • output="false"
  • hint="I initialize the request.">
  •  
  • <!--- Check to see if we need to manually reset the app. --->
  • <cfif structKeyExists( url, "reset" )>
  •  
  • <!--- Reset the application. --->
  • <cfset this.onApplicationStart() />
  •  
  • </cfif>
  •  
  • <!--- Return true so the page can load. --->
  • <cfreturn true />
  • </cffunction>
  •  
  • </cfcomponent>

As you can see, the live audit table for the girls contains only its own auto-incrementing ID value and the GUID or the corresponding girls record. When the client makes a sync request, the server will use the audit table's ID column to figure out which of the mutations needs to be pushed back to the particular client making the given request.

The synchronization logic uses a last-come, last-served approach to conflict resolution. In other words, the last device to sync data wins.

Sync.cfm [Offline Synchronization Request Handler]

  • <!---
  • NOTE: We would typically single-thread this entire process since
  • it is, by definition, meant to sync with multiple clients. There
  • are obvious race conditions in this code; however, for the sake
  • of the demo, I am just going to leave out locking and assume that
  • only one request will be made at a time.
  • --->
  •  
  •  
  • <!--- Param the FORM variables. --->
  • <cfparam name="form.lastAuditID" type="numeric" />
  • <cfparam name="form.updates" type="string" />
  •  
  •  
  • <!---
  • The updates data has been posted as JSON data (representing
  • an array of update objects). Therefore, we need to deserialize
  • it into a native ColdFusion array.
  • --->
  • <cfset form.updates = deserializeJSON( form.updates ) />
  •  
  •  
  • <!---
  • Get the current Auto ID for the audits table so we can echo back
  • changes before the ones we are about to merge.
  • --->
  • <cfset currentMaxAuditID = application.girlsRemoteAuditAutoID />
  •  
  •  
  • <!---
  • The first thing we are going to do is deal with the updates made
  • by this client. If the updates conflict with a previous update,
  • they will be overridden - my conflict resolution is a first-come,
  • first-serve basis.
  • --->
  • <cfloop
  • index="update"
  • array="#form.updates#">
  •  
  •  
  • <!---
  • Check to see if this is a delete - if so, we can just remove
  • the record from the target table.
  • --->
  • <cfif (update.action eq "delete")>
  •  
  •  
  • <!---
  • Delete the record and store the result back into the
  • same variable (the easiest way to delete using ColdFusion
  • query of queries).
  • --->
  • <cfquery name="application.girls" dbtype="query">
  • SELECT
  • *
  • FROM
  • application.girls
  • WHERE
  • guid != <cfqueryparam value="#update.guid#" cfsqltype="cf_sql_varchar" />
  • </cfquery>
  •  
  • <!--- Add the audit. --->
  • <cfset queryAddRow( application.girlsRemoteAudit ) />
  •  
  • <!--- Set audit values. --->
  • <cfset application.girlsRemoteAudit[ "id" ][ application.girlsRemoteAudit.recordCount ] = javaCast( "int", ++application.girlsRemoteAuditAutoID ) />
  • <cfset application.girlsRemoteAudit[ "guid" ][ application.girlsRemoteAudit.recordCount ] = javaCast( "string", update.guid ) />
  •  
  •  
  • <!--- This is an update, not a delete. --->
  • <cfelse>
  •  
  •  
  • <!--- Keep track of if a record was found. --->
  • <cfset recordFound = false />
  •  
  • <!---
  • Let's loop over the existing girls to see if any of the
  • GUID values match. If so, we will update the record; if not,
  • we'll insert a new record. We are using this looping
  • technique simply cause Query of Queries does not have an easy
  • UPDATE feature.
  •  
  • NOTE: The GUID values are created on the client.
  • --->
  • <cfloop query="application.girls">
  •  
  • <!--- Check to see if this girl matches the update. --->
  • <cfif (application.girls.guid eq update.guid)>
  •  
  • <!--- Update the current record. --->
  • <cfset application.girls[ "name" ][ application.girls.currentRow ] = javaCast( "string", update.name ) />
  • <cfset application.girls[ "age" ][ application.girls.currentRow ] = javaCast( "int", update.age ) />
  •  
  • <!--- Flag that record was found. --->
  • <cfset recordFound = true />
  •  
  • <!---
  • Break out of this loop since we found a girl to
  • update (we will not need to update another record or
  • add a record for this girl).
  • --->
  • <cfbreak />
  •  
  • </cfif>
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • Check to see the matching record was found. If not, then
  • we need to add a new Girl record.
  • --->
  • <cfif !recordFound>
  •  
  • <!--- Add a new row. --->
  • <cfset queryAddRow( application.girls ) />
  •  
  • <!--- Set values. --->
  • <cfset application.girls[ "id" ][ application.girls.recordCount ] = javaCast( "int", ++application.girlsAutoID ) />
  • <cfset application.girls[ "name" ][ application.girls.recordCount ] = javaCast( "string", update.name ) />
  • <cfset application.girls[ "age" ][ application.girls.recordCount ] = javaCast( "int", update.age ) />
  • <cfset application.girls[ "guid" ][ application.girls.recordCount ] = javaCast( "string", update.guid ) />
  •  
  • </cfif>
  •  
  •  
  • <!---
  • At this point, we either updated an existing girl record,
  • or we added a new girl record. In either case, let's add
  • a record to the audit table. The audit record does not
  • indicate whether it as an UPDATE/INSERT - it merely flags
  • that *something* was done to the given girl record.
  • --->
  • <cfset queryAddRow( application.girlsRemoteAudit ) />
  •  
  • <!--- Set audit values. --->
  • <cfset application.girlsRemoteAudit[ "id" ][ application.girlsRemoteAudit.recordCount ] = javaCast( "int", ++application.girlsRemoteAuditAutoID ) />
  • <cfset application.girlsRemoteAudit[ "guid" ][ application.girlsRemoteAudit.recordCount ] = javaCast( "string", update.guid ) />
  •  
  •  
  • </cfif>
  •  
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • Now that we have merged in the incoming changes, we have to send
  • back any changes that have not been synced to the given client.
  • Let's get all the changes since the last audit ID (not including
  • the audits we just committed).
  • --->
  • <cfquery name="audits" dbtype="query">
  • SELECT
  • id,
  • guid
  • FROM
  • application.girlsRemoteAudit
  • WHERE
  • id > <cfqueryparam value="#form.lastAuditID#" cfsqltype="cf_sql_integer" />
  • AND
  • id <= <cfqueryparam value="#currentMaxAuditID#" cfsqltype="cf_sql_integer" />
  • ORDER BY
  • id ASC
  • </cfquery>
  •  
  •  
  • <!--- Create an array to hold the updates. --->
  • <cfset updates = [] />
  •  
  • <!--- Loop over the audits to build an array of return data. --->
  • <cfloop query="audits">
  •  
  • <!---
  • Query to see if the corresponding girl record still exists
  • for this audit record. Its existence will indicate the type
  • of audit we need to pass back.
  • --->
  • <cfquery name="girl" dbtype="query">
  • SELECT
  • *
  • FROM
  • application.girls
  • WHERE
  • guid = <cfqueryparam value="#audits.guid#" cfsqltype="cf_sql_varchar" />
  • </cfquery>
  •  
  • <!---
  • Check to see if the girl was found. If it was, we'll pass
  • the data back as an update; if not, we'll pass it back as
  • a delete.
  • --->
  • <cfif girl.recordCount>
  •  
  • <!--- Pass back an update action. --->
  • <cfset audit = {} />
  • <cfset audit[ "action" ] = "update" />
  • <cfset audit[ "name" ] = girl.name />
  • <cfset audit[ "age" ] = girl.age />
  • <cfset audit[ "guid" ] = girl.guid />
  •  
  • <cfelse>
  •  
  • <!--- Pass back a delete action. --->
  • <cfset audit = {} />
  • <cfset audit[ "action" ] = "delete" />
  • <cfset audit[ "guid" ] = audits.guid />
  •  
  • </cfif>
  •  
  • <!--- Append the audit object. --->
  • <cfset arrayAppend( updates, audit ) />
  •  
  • </cfloop>
  •  
  •  
  • <!--- Build the response object. --->
  • <cfset response = {} />
  • <cfset response[ "lastAuditID" ] = application.girlsRemoteAuditAutoID />
  • <cfset response[ "updates" ] = updates />
  •  
  •  
  • <!--- Stream the response back as JSON. --->
  • <cfcontent
  • type="application/json"
  • variable="#toBinary( toBase64( serializeJSON( response ) ) )#"
  • />

When a client makes a request to the server, all of its local changes get merged into the live database. There is no complex conflict resolution taking place - all updates completely override the previous records. This merger results in new audit records being created on the server (which will be flushed to other clients as they make sync requests).

After the local-to-live changes have taken place, the server queries the live audit table for changes that have been made since the client's last sync request. The server then creates an audit response to send down the client, at which point, the client has to merge the incoming changes back into its own local data store.

This was my first exploration of this concept and all in all, I found this whole process to be very complicated. But, at the same time, I cannot really think of ways to make offline data synchronization all that much easier. I know that FLEX uses LCDS for this kind of stuff, but is that what people are using for Javascript-based synchronization?




Reader Comments

Another reason the audit (transaction) log is good is because as you start working with complex APIs you can't be sure that individual queries are side-effect free. What if you tried to push just the summary or last query, but one of the middle queries would have caused a problem? Can you then prove you've ended in a reasonable, reproducible state? Whereas if you replay a log, you can prove exactly what is right or wrong.

Offline sync is a topic I'd love to cover in my databases course, but I think it may be too ephemeral for most students, and definitely a brain melter.

Reply to this Comment

@Rick,

I'd definitely love to see you talk about data synchronization - I know you lived and breathed data warehousing for while and seem to have a wonderful mental model for it.

Reply to this Comment

It looks like I might make it to SOTR2011 with you. (Though, I'd be just a humble peon/attendee.) I still owe you a dinner for crashing the last party.

Reply to this Comment

Shared environments are always complicated and hard to synch.

I thought that the offline device would only add, edit and remove its own data associated with this user that is doing transactions or subscribe data to... that's at least how I see majority of the apps work, unless you're allowing multiple users to share and doing asynchronous updates to one table at the server side where it will be difficult do manage and make sure that everything is in good order.

Is the intention here only sending added/updated/deleted records to the client or everything that's on the server girls table?

I think doing this through Audit table might add a little bit of an overhead and performance bottlenecks.

Why don't you keep track of (server's) girls.id on the local database table in addition to the (local) girl.id? That will make sure that client and server are talking to each other in same terms and you'll know which record on the server got updated/deleted.

How are you making sure that the one record is not being updated at the same time, for example:
I changed 'Nanci' to 'Amy' and you changed 'Nanci' to 'Mary', what should I see 'Amy' or 'Mary'? If my transaction got to the server before yours I would see Mary as the end result but if your transaction got first then I would see Amy since it would overwrite Mary ;) Look into database locking, but that's another subject.

I have to think about this more, I will definitely play around with this tonight :)

Reply to this Comment

@Ben:

Excellent article. I'm not normally a video watcher if there's code to read. This was the first time I watched the screencast. Very impressive the way the 2 browsers picked up each others' updates.

Reply to this Comment

@Rick,

Awesome! Hopefully I'll see you there then.

@Marcin,

The offline app would be updating its own data; but, it has to keep a full copy of everything on the live server in case the local database gets wiped. Remember, we're not talking about "installed" apps - we're talking about web applications with local storage capabilities (ex. GMail).

Of course, you wouldn't be able to mutate someone else's data - I am just trying to keep it simple for experimentation. I assume in the long run, you'll be passing credentials back and forth and ensuring that only the accessible records are being changed.

As far as conflict resolution, who ever saves last wins in my current approach. So, if you and I change the same record, but I sync last, my change will win. Just as if I delete a record, it will delete it from your device as well.

Of course, ultimately, I wasn't really thinking of two people on two different devices; mostly, I was thinking of one person on two different devices. As a trite example, let's say I had a lot of money to spend and I had an iPhone for my normal life and an iPod Touch for my Gym workouts. I might have a workout web app that can be used by *me* on both devices - changes that I make on one *have* to be reflected on the other.

... hold on, my mind is starting to melt :)

@Steve,

Thank my man - glad you liked the video. I've been trying to do those as I think they add a little bit more clarity on what is going on (especially in an example like this where this is so much code).

@Johans,

Awesome - I'll have to check it out. I've been Googling for articles on this topic and have not come across anything too good.

Reply to this Comment

@Johans,

That's so funny you mention that - I *just* opened that up in a different tab. I think I saw it come across on a TechTweet on Twitter. Looks awesome. The whole HTML5 Rocks web site just looks like it has a lot of value.

Reply to this Comment

Knowing that you've already set the GUID bar ("I find GUIDs to be very off-putting as database indexes. This is a purely emotional constraint - but, it is one that I am not yet ready to break through."), I still have got to point out that this is one of THE reasons to consider GUIDs or UUIDs for your DB keys, in my opinion. They are gnarly looking, I agree, but data transfer headaches simply Go Away with managed UIDs.

Even apart from remote device and local-DB scenarios, I've run across this situation many times over the years:

* complex app already exists in production
* client wants some changes, which include splitting up some tables, changing the way some indexes work (say, going from 1 customer = 1 address to 1 customer may have 3 addresses)
* changes are made to the staging DB and code, and the client spends time testing and verifying that the new data stuff flows properly
* in the meantime, all sorts of changes have continued to be made in production
* we can't really sync the staging and production DBs directly, since production doesn't have the new schema and staging doesn't have new production data, but now maybe we have new child records that need to carry over from staging, based on data that may have been updated during testing
* with UIDs, I can literally copy unmatched records without worrying about both sources already having ID = 316, for example

INSERT INTO target.dbo.myTable
SELECT *
FROM source.dbo.myTable
WHERE ID NOT IN (
SELECT ID
FROM target.dbo.myTable
)

that sort of thing just simply works :)

Reply to this Comment

Please how can have a version of this tutorial for php developers. I am not conversant with cold fusion. Thank you

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.