Using QuickConnectFamily Enterprise Synchronization

QCFamily 1.6 beta 9 and later

 

Purpose:  Having local SQLite databases in your application is great.  But what do you do when you need the data synchronized to a central database?  You could write a lot of your own code or you could use the synchronization tools inside of QuickConnect.  These tools were designed with the idea that you as a developer should have to write as little code as possible.  It should also be dead simple to use.

 

Another nice feature is that the central database can be any DBMS of your choosing.  These databases include but are not limited to MySQL, Oracle, Sybase, and Microsoft SQL Server.  The database and its tables do not even need to match the structure of the SQLite database on the device.

 

Assumptions:  This explanation assumes that you understand, can use, and can create Business, View, and Validation Control Functions used in all QuickConnectFamily applications.  If you need help with control functions see the Control Function Tutorial.

 

Overview:  The QCFamily synchronization tools create a connection to a remote web service that you create.  JSON is used to communicate synchronization requests.  Therefore your service should be able to accept and respond with JSON strings.  The 1.6 beta 9 and later download includes a simple PHP file, ServerSideTest.php, that receives and responds with JSON.  It can show you where to start even if your service is create in some other language.

 

Warning:  Database synchronization works on all of the devices and in the simulator/emulators from within Xcode.  It does not work from within Dashcode.  Dashcode imposes the same source restriction for AJAX and thus makes the sync tools fail.  Xcode does not impose this restriction.

 

Synchronization uses the standard Control Function behavior of the QCFamily framework.  Because of that, if you want to trigger a synchronization of the databases all you need to do is call "handleRequest('QCSync')" and the tools take over.  The tools consist of several Business Control Functions (BCFs) that gather the data from the local database and then make an AJAX call to your service.  When these calls are complete a View Control Function (VCF) is called by the framework to display the results of your sync request.

 

By using the existing DataAccessObject and ServerAccessObject elements of the framework and the handleRequest functionality the you can easily modify and add to the sync process. (See 'Customization' below.) 

 

So how do I get it to work?:  When you create a QCFamily application for iPhone, Android, Blackberry, or WebOS using the Xcode template all of the support files are already included.  The first step is to create a DataAccessObject that is attached to your database.  This database can be either an  in-browser HTML 5 database or a native database shipped with your application.  The following is the code, found in the databaseDefinition.js file for an in-browser database.

 

 var database = new DataAccessObject("WelcomeExample", "1.0", "Welcome example", 20);

 

Once the database has been created it must be told that it needs to synchronize with a remote service.  The next line of code shows how this is done.

 

database.setupSyncing(DataAccessObject.SYNC_ON_DEMAND, "http://<your URL>", "bob", "fred", 5);

 

The parameters for the setupSyncing function are:

1. Sync type
   1. SYNC_ON_DEMAND - synchronization happens when you call handleRequest('QCSync')
   2. SYNC_PERIODIC - synchronization happens every so many seconds.  The seconds are defined in an optional sixth parameter
   3. SYNC_CONTINUOUS - synchronization happens each time a change is made in either database.  This feature is not available yet.
2. URL - the URL of your synchronization service that you create
3. User Name - a user name that has privileges to communicate with the service
4. Password - the password associated with the privileged user
5. Timeout Seconds - the number of seconds at which the sync call to the server times out
6. Optional Sync Period - the number of seconds between sync calls.  If you define periodic syncing you do not call handleRequest('QCSync').  The framework does it for you every n seconds that you define with this parameter.

    

In order to separate the table structures on the device from those in the central database the SQL on the device is associated with a key.  This key is used each time you want to execute the statement and is also sent to the central database at sync time.  The following line of code from databaseDefinition.js shows how a piece of SQL is made into a synchronized call.

 

database.storeSyncStatement("anyName", "INSERT INTO names VALUES(?,?)");

 

The parameters for the storeSyncStatement function are:

1. Key - any alphanumeric set of characters.  Keys can be strings, numbers, or a mix of both character and number.  The keys are case sensitive.
2. SQL - the SQL associated with the key that is to be treated as synchronize-able.

 

Since the statement in this example is a prepared statement, each time the SQL is executed the parameters passed are inserted into the local database table and stored with the key in a temporary sync table that is created and maintained by the sync tools.  

 

Store any statements that you want to be synchronized here with your database definition.  Not all of your SQL statements have to be synchronized.  Only those for which you want the data synced with the central database.

 

Note:  there are several other lines of code in the example file.  Those are specific to the example.

 

Having now informed the database and the sync tools which SQL statements are to be synced it is now time to make a call and execute some synced SQL.

 

The index.html file has the following button tag defined in it.  The onclick listener is defined as a call to the QCFamily handleRequest function.  The command passed to handleRequest is 'insertNames'.  This command is associated with a stack of Control Functions in the mappings.js file.  Take a look in that file to see the functions and their order that the framework will execute when handleRequest is called.

 

<input id='insertNamesButton' type="button" onclick="handleRequest('insertNames')" value="Insert Pre-defined Names">

 

 

The purpose of this call is to insert several predefined names and numbers into the names table.  Because several inserts are to be done at the same time and as part of the same transaction I have chosen to use the DBScript framework object.  For the purpose of this example, and my own testing, I made the code only insert one statement as a synchronized statement.  Notice that only the third statement is synchronized since it is the only addStatement call that uses the key defined earlier instead of normal SQL.  Also notice that the only difference between a normal call and a synchronized call is that the synchronization key is used.  You don't need to learn any new functions to make a synchronized call.

 

 

 function insertNamesBCF(parameters){

 

     var bulkInsertScript = new DBScript(database);

     

     bulkInsertScript.addStatement("INSERT INTO names VALUES(1,'Bob')");

     bulkInsertScript.addStatement("INSERT INTO names VALUES(2,'Sue')");

     //and example of using a synced statement

     bulkInsertScript.addStatement("anyName",[3,"Jose"]);

     bulkInsertScript.addStatement("INSERT INTO names VALUES(4,'Bjorn')");

     bulkInsertScript.addStatement("INSERT INTO names VALUES(5,'Jean')");

     bulkInsertScript.addStatement("INSERT INTO names VALUES(6,'Gustav')");

     

     bulkInsertScript.executeSetDataScript();

 }

 

 

 

The index.html file in the example also includes this tag.  It sets up a button that has JavaScript triggered when the button is touched.  The purpose of this button is to insert a name and number defined in the input tags in the user interface.  Each insertion into the local database of names and numbers is to be done in a way that the data is synchronized to the remote database.

 

<input id="insertNameButton" type="button" onclick="handleRequest('insertName')" value="Insert Single Name">

 

The insertName command stack includes a mapping to the following function.  When handleRequest('insertName') is called as a result of the user touching the 'Insert Single Name' button the insertNameBCF is called by the framework.  Notice again that the key, 'anyName', is used in the setData call of the DataAccessObject named database instead of the SQL that would normally be expected.  Just as with the previous function this causes the index number and name to be stored for synchronization with the central database.

 

function insertNameBCF(parameters){

var indexNum = parameters[0];

var name = parameters[1];

database.setData("anyName",[indexNum,name]);

}

 

 

Once again no new functions beyond the standard DataAccessObject function, setData, need to be learned or used.

 

Now that data has been stored for synchronizing with the central database, assuming that the user has selected the button or buttons, it is now time to trigger a synchronization.

 

The index.html file in the example includes the following tag.  It sets up a button that has JavaScript triggered when the button is touched.

 

<input id='syncButton' type="button" onclick="syncDatabase()" value="Sync Data With Server">

 

The onclick listener function, syncDatabase, is found in the main.js file and can be seen below.  I created it since the handleRequest('QCSync') call requires that a DataAccessObject be passed as one of the elements of the parameter array.  By requiring this parameter you are allowed to have more than one database on the device that you are syncing if you wish.  

 

Remember that the DataAccessObject created in the databaseDefinition.js file was stored in the global variable called 'database'.  That variable is being passed to handleRequest.

 

function syncDatabase(){

    //pass in the database you want to sync with the remote database

handleRequest('QCsync',[database]);

}

 

When the user selects the sync button the framework will disable further user interaction with the UI and begin giving them feedback about synchronization progress.  This feedback consists of a Synchronizing string that continues to change color during the sync process.  Messages are also posted such as 'Gathering Data', 'Sending Data to Service', 'Synchronization Complete', and error messages should an error occur.  This is done by overlaying a div above the normal UI.  When synchronization completes successfully or an error happens the overlay informs the user and then disappears.  By not allowing the user to enter data during synchronization many possibilities for sync errors were avoided.

 

The sync tools that do this user notification, etc. consist of several Business and View Control functions that are triggered by the call to handleRequest.  You do not have to understand the code for these tool functions and can consider them part of the framework.  If you want to look at them and see how they work you can find them in the EnterpriseSyncFunctions.js file in the QCJSLib directory of the framework.

 

Customization:  If you want you can add more Control Functions to the 'QCSync' command stack.  These can be more Business or View Control functions or they could also be a Validation Control Function.  If you add Business Control Functions they must be executed after the sync tool Business Control Functions.  This is also true for View Control Functions.  If you add a VCF it must be executed after the standard tool VCF.  Any Validation Control Functions you add will be executed before any BCFs or VCFs.

 

To add more control functions insert the appropriate mapping in the mappings.js file.  Make sure that your BCF mappings are added AFTER the standard tool BCF mappings.  Also make sure that mappings for any VCFs that you add are AFTER the standard tool VCF mappings.  You can add Validation Control Function, ValCF, Mappings anywhere without worry since the standard tools do not include any ValCF mappings.

 

If you want to change the progress, success, error notifications displayed to the user you will find them in the  QCdoneSyncingVCF function.  This function is seen below.  Feel free to modify the messages but understand that they will be modified for all of your applications since the EnterpriseSyncFunctions.js file is shared between all of the applications created by the QuickConnectFamily template.  This is shared file state is modifiable but the description of how to do that is beyond the scope of this document.

 

 function QCdoneSyncingVCF(data, parameters){

//do not comment out the setting of currentlySyncing.  It is needed

    parameters[0].currentlySyncing = false;

/*

  *  If you commented out the call to showSyncView() in QCgetLastSyncDateBCF

  *  you must comment out or modify the messaging code below.

  */

var message = data[3];

if(message != 'server data format error'){

if(message[0].errorMessage){

message = 'DATA INSERTION ERROR: '+message[0].errorMessage+'.  Please contact your application provider';

}

else{

message = 'Data sucessfully synchronized';

}

}

else{

message = 'Error: The server is not responding with correctly formatted data.\nPlease contact you application provider.';

}

var syncView = document.getElementById('syncUpdateText');

if(syncView){

document.getElementById('syncUpdateText').innerText = message;

setTimeout(function(){

var body = document.getElementsByTagName('body')[0];

var syncDisplay = document.getElementById('syncView');

body.removeChild(syncDisplay);

turnOnScrolling();

},3500);

}

else{

turnOnScrolling();

}

 }

 

Conclusion:  With only a few more lines of code in your iPhone, Android, Blackberry, or Palm WebOS application, you can implement enterprise quality data synchronization for your application's users.  You do need to create a JSON enabled web service to interact with your application.  While the creation of such a service is beyond the scope of this document a simple hardcoded PHP service is included in the synchronization example included in the download.  

 

Currently the in-browser databases only work for iPhone, Blackberry, and Palm WebOS.  Google doesn't have a working in-browser database solution.  Also, only in-browser databases work for Palm WebOS since we are strictly limited from working with the underlying system.