Prerequisite knowledge
Required products Sample files
User level
This tutorial is intended for developers who have knowledge of AIR,
ColdFusion (CFML and the Administrator, and ORM concepts), Flex, and
ActionScript 3.0, MySQL, and object-oriented programming.
Adobe ColdFusion Enterprise Edition (2016 release) (Download trial)

Flash Builder (Download trial)
(295 KB)

With the advent of the Adobe AIR platform, it has enabled users to write offline SQLite applications, which continue to work even when there is no connectivity to a network or server—this gives users a seamless experience. But dealing with SQL queries on a client can be messy, as your application has to create replicas of server-side database tables for your AIR application; you must track your using SQL queries programmatically and your application has to keep track of changes done in the offline mode, in order to sync it to your server. In this case, the client-side ActionScript ORM comes in quite handy for AIR application developers. To avoid this SQL approach for AIR applications, ColdFusion 9.0.1 has introduced an ActionScript ORM framework library for AIR.
In this article, AIR developers can learn how to use this library (cfair.swc) to develop AIR offline applications without writing even a single SQL statement. In this article, you will learn how to fetch server data and store it offline, keep AIR client in sync with server, managing conflicts between client and server data while syncing with each other through a customer order sample application.

The customer order sample application

Throughout this article, I discuss code snippets from the customer order product application to explain various concepts. This application has three screens, as shown in Figure 1.
The "Customer Information" screen is a complex one. This screen demonstrates a SelfJoins to the “Customer” table. Also, it deals with other tables like “Address” and “Orders”. This screen implements all the database relationships such as one-to-one, one-to-many, and many-to-many relationships. Using this screen, you can create the customer first and later when enough numbers of entries are available. You can establish various relationships like spouse/manager/children/parents and so forth. The "Product Maintenance" screen allows users to create/edit/delete products. “Place an Order” screen lets you place order containing different products for a particular customer. Also it allows you to edit/delete these orders. On right-hand top corner of the screen, there is a Green round Image, which indicates that the Application is in online mode. If it is red, it means application is running in offline mode. Next to it, you may also see a green round tick mark image while working in offline mode, indicating that there are offline changes present which are not synced with server.
Flow of the application
  • On Application start, the application creates a static instance of SyncManager (this helps in establishing a connection with ColdFusion server and an offline database) by setting the server credentials, which can be accessed from any page in the application.
  • The application creates a SQLite offline database by parsing the ActionScript ORM metadata tags specified in the ActionScript classes. This activity will be done only if the offline database doesn’t exist. The next time the application starts, if there is already an offline database, there wont be any repeated attempt to create it.
  • Due to the previous step, your application has established a connection session which is again stored as static session reference, such that you can access it from anywhere else in the application.
    Note: The rationale for making syncmanger and session instances static is to avoid creating multiple offline database sessions from different parts of application. If done so, different session would put lock on offline databases and hence would conflict with each other, creating a deadlock scenario.
  • Next, the application fetches server data and stores it in the offline database.
  • Users can then perform various CRUD operation using different screens. If the application is online, changes in the offline database are immediately written on server database. If the application is offline, it will attempt the CRUD operations when server becomes reachable.
  • During commit operation on Server, there might be conflicts between the client data and server data, which server will send back to the client to handle. With latest server data. The client has two choices at this point: Either it accepts the server data or ignores it and continues working with its own set of data.

Defining ActionScript classes

Any Flex application that deals with server CFC objects would normally have corresponding ActionScript classes defined on the client that map with the server CFC objects to support data type translations between ColdFusion data types and ActionScript data types.
In the ActionScript ORM framework libraries that ColdFusion 9.0.1 has introduced, there are many Metadata tags that define relationships between the ActionScript classes and which also specify to the framework which ActionScript classes are ORM-enabled ones.
Let us have a look at an ActionScript class called Customer, which has Metadata tags to more easily understand it.
package onetoone { [Bindable] // This class maps with “customer.cfc” CFC. This tags is not part of CF AS ORM Libs, rather it’s part of native ActionScript [RemoteClass(alias="AIRIntegration.customer")] // Create a SQLite Table for this Calls [Entity] public class Customer { [Id] //Define Primary Key // Define AutoIncrement of PK [GeneratedValue(strategy="INCREMENT",initialValue=5,incrementBy=2)] public var cid:int; public var name:String; // Defining One-To-One relationship with Address AS class [OneToOne(cascadeType='ALL',fetchType="EAGER")] [JoinColumn(name="add_id",referencedColumnName="aid")] public var address:Address; // Many-to-One self Join [ManyToOne(targetEntity="onetoone::Customer",fetchType="EAGER")] [JoinColumn(name="managerId",referencedColumnName="cid")] public var manager:Customer; // One-to-one Self Join [OneToOne(targetEntity="onetoone::Customer",fetchType="EAGER")] [JoinColumn(name="spouseId",referencedColumnName="cid",unique="true")] public var spouse:Customer; // Many-to-Many self Join [ManyToMany(targetEntity="onetoone::Customer",fetchType="EAGER")] [JoinTable(name="CUSTOMER_PARENTS_MAPPINGS")] [JoinColumn(name="CUST_ID",referencedColumnName="cid")] [InverseJoinColumn(name="PARENT_ID",referencedColumnName="cid")] public var parents:Array; // Many-to-Many self Join [ManyToMany(targetEntity="onetoone::Customer",fetchType="EAGER")] [JoinTable(name="CUSTOMER_CHILDREN_MAPPINGS")] [JoinColumn(name="CUST_ID",referencedColumnName="cid")] [InverseJoinColumn(name="CHILD_ID",referencedColumnName="cid")] public var children:Array; [OneToMany(targetEntity="onetoone::Order",cascadeType='REMOVE',mappedBy="customer",fetchType="EAGER")] public var orders:Array; } }
As you can see in the above Customer class, a wide range of metadata tags have been introduced such as [Entity], [Id], [GeneratedValue], [OneToOne], [OneToMany], [ManyToOne], [ManyToMany], [JoinTable], [JoinColumn], [InverseJoinColumn], and so forth.

All these metadata tags, have been introduced to let the user class define relationships with other ActionScript classes in the application. In the above example, Customer class shares different relationship with other classes such as Address, Order, Customer (which self-joins). Also, this lets the framework know if a SQLite table has to be created for a class (or entity), which key acts as the primary key ID (PKID), what the strategy is to auto-increment the generated PKID. Many of the tags are self-explanatory; for some, I have tried to explain them with comments in the ActionScript class definition. Note: For detailed information on these tags and their possible parameters and their values or default values, refer to the ColdFusion Reference for this topic.
Likewise, all the other ActionScript classes such as Order, Product, Address are defined. Figure 2 illustrates the database tables that are generated automatically by the ActionScript ORM framework.
As you can see, this is a complex database environment. In fact, this is the idea behind making a tutorial that demonstrates all of the relationships for offline database applications.
Customer table
This table stores customer information, including spouse, manager and so forth. Notice that the Manager field demonstrates a one-to-many self-join; and the spouse field demonstrates a one-to-one self-join. Parents and children fields demonstrate many-to-many self-joins. This example also demonstrates how to define self-joins. Self-joins can affect the performance of loading an object, as they can have many nested circular references to other objects. In such cases, if the fetchType for a nested object is EAGER then it can take a time to load the entire graph. Another option for fetchType could be LAZY, meaning it won’t load the nested object when you load the parent object.
Additionally, notice the cascadeType attribute in the relationship tags. In my example, for the Address class relationship with Customer, I have used the value ALL. This suggests that when the Customer is modified by any operation, also Address is also modified if there are any changes. Other possible values for cascadeType could be PERSIST/REMOVE.
Customer_children_mappings table
This table stores children information for customers.
Customer_parents_mappings table
à his table stores the parent information for customers.
Product table
This table stores available products.
This tables stores orders placed by customers.
This table is an intermediate table for orders and product tables and stores, and for various products associated with an order.
To learn more about relationships, cascadeType and FetchType, refer to the Offline AIR Application Support chapter of the ColdFusion Developer’s Guide.

Connecting to ColdFusion Server using SyncManger

ColdFusion ActionScript ORM libraries introduce a class called SyncManager, which facilitates users to connect with ColdFusion server. It lets users specify the server credentials to connect to ColdFusion server. Below, the code snippet with comments for the init() method explains it how to specify these attributes on a syncmanger object instance.
public static function init():void { // Provide Credentials for Server side Connection and CFC /* Create an Instance(syncmanager) of SyncManger, Here syncmanager is decalred as global static variable so that it could be accessed from anywhere in the application */ syncmanager = new SyncManager(); syncmanager.cfPort = 8500; syncmanager.cfServer = “”; // Path of Server side CFC having Sync/Fetch method, relative from CF webroot syncmanager.syncCFC = “AIRIntegration.cusManager”; /* this attribute automatically commits any changes in offline database to Server DB if Connected otherwise it would result in faultHandler where it should be handled appropriately */ syncmanager.autoCommit = true; /* THis handler will be called when any COnflict occures while writing back changes on serverside */ syncmanager.addEventListener(ConflictEvent.CONFLICT, conflictHandler); //Call a method to Open a connection with offline database OpenLocalDBSession(); } private static function conflictHandler(event:ConflictEvent):void { // Conflict Management we will see later in the article }

Opening a connection with AIR SQLite database using SyncManager

The SyncManger class also provides various APIs to help users to open a connection with the offline database or to fetch server data. The following code snippet with comments explains as how to create an offline database, if it has already been created. On subsequent starts of the application when this method is called, your application internally it checks whether the "customermanger.db" file has already been created—if it has, then your application won’t re-create the database.
This code also demonstrates how to secure the offline database by encrypting it. Here, I have used a custom class called EncryptionKeyGenerator, which is not part of the ColdFusion ActionScript ORM libraries, nor part of the AIR runtime. It is available in the AIR reference guide to teach users how to generate EncryptionKeys. You will find this custom class as part of the example application. User are free to use their own encryptionKey generator class, in order to encrypt the offline database. One should know that though it is not mandatory to encrypt the offline database, there could be multiple users to a machine and other people may get a hold of this offline database and get access to sensitive data from the user. It is always a good practice to encrypt the offline database. Once encryptionKey is generated, it could be passed to the openSession() API as a parameter in case you want to encrypt the offline database, which is otherwise an optional parameter. Since this article is for developers, note that there are quite a few free SQLite clients available. One of them that you can download is SQLiteManger, which you can install as part as a Firefox plug-in.
Also note in the openSesssion() API, that other than the dbFile path reference, we are also passing an integer value. This integer value is required to uniquely separate this offline application from other offline applications, which might otherwise confuse the internal temporary data of all offline applications.
// Open a Session for the client side SQLite DB public static function OpenLocalDBSession(): void { // Under User Directory “customerManger.db” offline database will be created dbFile = File.userDirectory.resolvePath("customerManger.db"); var keyGenerator:EncryptionKeyGenerator = new EncryptionKeyGenerator(); var encryptionKey:ByteArray = keyGenerator.getEncryptionKey("UserPasswrd"); // Create a offline database session using openSession API of syncmanger instance var sessiontoken:SessionToken =syncmanager.openSession(dbFile,179176, encryptionKey); sessiontoken.addResponder(new mx.rpc.Responder(connectSuccess,connectFault)); } private static function connectSuccess(event:SessionResultEvent):void { /*In the ConnectSuccess handler Event, we get the “session” reference, which can be used to perform any CRUD Operation with the Offline DB. Here we have declared a Global static “session” variable, which could be accessed from anywhere in the application */ session = event.sessionToken.session; } private static function connectFault(event:SessionFaultEvent):void {"connect failure" + event.toString()); }

Fetching data from ColdFusion Server and storing it in an offline database

So far we have seen how to connect to ColdFusion server and how to open an offline database session using the SyncManager class. The next logical step in the flow would be to fetch the server data and store it in the offline application. Again, the the SyncManager class provides a fetch() API to achieve the information. Let us see this in the next section.
Fetching data records from ColdFusion Server
The following code snippet explains how to use the syncmanger instance that we had created earlier in order to fetch server data by using fetch() API. This API can take multiple parameters. The first parameter is a mandatory one, which is nothing but the method name “fetch” that is defined in the server CFC AIRIntegration.cusManager. The server-side data fetch method name could be user given; thus, users are provided the flexibility to pass the method name as well as parameter to the syncmanger.fetch() API. Other parameters in this API are optional, which are nothing but the parameters defined in the server-side CFC fetch method. We also register the fetchSuccess and fetchFault handlers. If the application receives data from the server, the application calls the fetchSuccess method along with the SyncResultEvent method.
Storing fetched data in an offline database
Once data is received from server, the next logical step should be to store it in the offline database, so that even when the application is not connected to network or server, all the data will be available for the user to work on. The following code snippet explains how to store the data in an offline database. In this fetchSuccess START HERE method, we use the session that we had created earlier. In my application, I have created this static session in a class called GlobalVars. Here, you see that I am prefixing this class name before the session variable. Now, to store data offline, this session instance provides us access to many of the APIs to deal with the offline database. The saveUpdateCache() API takes the ArrayCollection data type as input. Next, you receive the result set in the form of an array from server. We typecast it first as an ArrayCollection and then assign the cusColl array collection in the API.
// Fetch Server side DB data onto Client SQLite DB while starting the App itself private function fetchServerRecords(arg:String):void { var token:AsyncToken; if(arg==) token = syncmanager.fetch("fetch"); else token = syncmanager.fetch("fetch",arg); token.addResponder(new mx.rpc.Responder(fetchSuccess, fetchFault)); }

Performing CRUD operations

The following is a very simple example of how to perform various product CRUD operations through following code snippets with comments from application.
private function fetchSuccess(event:SyncResultEvent):void { var cus:Array = event.result as Array; cusColl = new ArrayCollection(cus); if(cusColl.length > 0) { // This operation will save/update it to AIR SQLite DB var savetoken:SessionToken = GlobalVars.session.saveUpdateCache(cusColl); savetoken.addResponder(new mx.rpc.Responder(saveCacheSuccess, saveCachefault)); } else {"No data available from Server to save on local DB, DataGrid will be attempted to load with local DB Data if any available"); updateGrid(); } }
// Insert or Update the Product private function saveProduct():void { // Create an instance of the Product class var prd:Product = new Product(); // Check if It is new product or editing a product if(int(prodID.text) != 0) {// not a new product as prodID is not 0, set it to prd = int(prodID.text); } // set the prouct name from the Flex UI textbox =prodName.text.toString(); // now, once entire Product Instance is constructed from Flex UI, save it in offline database var savetoken:SessionToken = GlobalVars.session.saveUpdate(prd); savetoken.addResponder(new mx.rpc.Responder(savesuccess, savefault)); } // Delete an existing Product private function DeleteProduct():void { var prd:Product = new Product(); // Just setting the prodID is enough to delete a product = int(prodID.text); var savetoken:SessionToken = GlobalVars.session.remove(prd); savetoken.addResponder(new mx.rpc.Responder(removeSuccess, removeFault)); }
As you can see in the above methods, we have used two new session APIs to save/update (session.saveUpdate()) and delete (session.remove()) to perform CRUD on a product object. The main things here is to construct an object from the user inputs from a Flex user interface and then use various session APIs to update them in offline databases. The next session explains how this CRUD operations would be committed on server.

Committing offline changes to the ColdFusion Server

CRUD operations that an application user performs could be performed while the application is online or offline. If the application is running in online mode and if the syncmanger.autocommit attribute is set to true, as soon as the changes are done in offline database, the application will also attempt to store the changes in the server database. If the application is running online mode, it is recommended to sync offline data as soon as possible. Otherwise, delays could result data conflicts if other clients are also updating the same database—not a nice scenario to handle.
In the case of offline mode, the data changes done in this application by user are stored in offline database only, and since syncmanger.autocommit = true, it will try to commit data on server but it would fail and eventually, a saveFault handler will be called to catch and handle the exception as shown in the below code snippet.
private function savefault(event:SessionFaultEvent):void { // Checking if exception is due to, Server is Unreachable if(event.error.message == "Send failed") {"Data Changes done Offline only, as Server is Unreachable","Confirmation"); updateGrid(); } else {"Product Save Fault"+event.error,"Error"); } }
Now, when application mode changes from offline to online mode, user either can manually call the GlobaVars.session.commit() API by a button click or you could write some custom logic to automatically call this API, which I have done in this application. A sample code snippet to call this commit() APIs follows:
private static function commit():void { var committoken:SessionToken = GlobalVars.session.commit(); committoken.addResponder(new mx.rpc.Responder(commitSuccess, commitFault)); } private static function commitSuccess(event:SessionResultEvent):void {"Server has been updated with local changes"); } private static function commitFault(event:SessionFaultEvent):void { if(event.error.message == "Send failed") {"Data Saved Offline only as Server unreachable"); } else {"Commit Failed::"+event.error); } }

Having a look at ColdFusion server-side code

We have discussed client-side ColdFusion ActionScript ORM APIs. In these APIs there are two APIs that make calls to Server. Those are, syncmanager.fetch() and session.commit() and on ColdFusion server-side, we have two methods corresponding to these client APIs, called "fetch" and "sync" respectively defined in the AIRIntegration.cusManger CFC. There is one more rule for this cusManager.cfc file. It has to implement the CFIDE.AIR.ISyncManager interface. This interface calls for a Sync method.
Let us have a look each method.
Fetch Method
The following is the fetch method, that I have used to fetch all or a particular customer using the ColdFusion server-side ORM EntityLoad() method. The ColdFusion server ORM is a huge topic in itself and so I will refrain from explaining it here at depth and only as much as required. What is in the code below is an ORM CFC called customer.cfc. I pass the CFC name in the EntityLoad() method and then the server-side ORM takes care of returning the server’s database records, which are also returned by this method to the AIR client as an Array, which is received by the fetchSuccess() handler.
<cffunction name="fetch" returnType="Array" access="remote"> <cfargument name="custId" type="any" required="false"> <cfset cus = ArrayNew(1)> <cfif not isdefined('custId')> <cfset cus = EntityLoad("customer")> <cfelse> <cfset cus = EntityLoad("customer",custId)> </cfif> <cfreturn cus> </cffunction>
Sync Method
Another very important method, is the Sync method, which receives three arrays for operations, clientobjects, and the origionalobjects array from the AIR client from the session.commit() call. Values in the operations array could be any one of the following CRUD operations: INSERT, UPDATE, or DELETE. Values for clientobjects or origionalobjects arrays could be any client object that maps with server ORM CFC. In my example application, it could be Customer/Product/Order objects.
<cffunction name="sync" returntype="any"> <cfargument name="operations" type="array" required="true"> <cfargument name="clientobjects" type="array" required="true"> <cfargument name="originalobjects" type="array" required="false"> <!---Create a Conflict Array to keep track of Conflicts---> <cfset conclits = ArrayNew(1)> <cfset conflictcount = 1> <!---Loop over the Array of Objects with changes, that we have received from AIR Client and perform the required OPERATION against server DB---> <cfloop index="i" from="1" to="#ArrayLen( operations )#"> <cfset operation = operations[i]> <cfset clientobject = Duplicate(clientobjects[i])> <cfset originalobject = originalobjects[i]> <cfif isinstanceOf(clientobject,"customer") OR isinstanceOf(originalobject,"customer")> <cfinclude template="customerHandler.cfm"> <cfelseif isinstanceOf(clientobject,"order") OR isinstanceOf(originalobject,"order")> <cfinclude template="orderHandler.cfm"> <cfelseif isinstanceOf(clientobject,"product") OR isinstanceOf(originalobject,"product")> <cfinclude template="productHandler.cfm"> </cfif> </cfloop> <!---return the Conflict array, if there are any----> <cfif conflictcount gt 1> <cfreturn conflicts> </cfif> </cffunction>
ClientObject is a copy of the object with changes done on AIR side. OrigionalObject is nothing but the initial copy of the object when it was loaded from server. We receive copies of origionalobject on the server for sync method call, as this helps you detect conflicts. Later, you will see how conflicts are detected and handled.

If the operation is INSERT, then OrigionalObject would be NULL as it is a new copy which is yet to be saved on server. For UPDATE operation both the clientObject and OrigionalObject would be present. In case of DELETE, ClientObject would be NULL, as it has already been deleted from AIR side database, and it will be deleted on server using OrigionalObect copy, after checking for conflicts. Here in the above method, depending upon the type of Object, we are sending them to different handler CFM files. In these CFM files, we mainly do two things, check for conflict. If there is a conflict, we prepare the conflict object and put it in the Conflicts array, which will be returned from Sync method. If there is no conflict, the application performs the desired Operation using client/original object against server database. In my example application, I use Server ORM methods EntitySave() for insert/update operations and the EntityDelete() method for delete operations.

Handling conflict management

Conflict management is a very important aspect for AIR applications, which work in offline mode. Due to non-connectivity, it is a possibility that AIR client might work with stale data, as other client might have modified the same set of data on server database. Conflicts can happen in quite a few situations. For instance:
  • An application is trying to update a server record, with changes on stale data that it possesses
  • An application tries to update a server record that is already deleted.
  • An application tries to delete a server record that is already deleted.
  • An application itries to insert a record on the server with a primary key (PK) generated by client, while server database also generates its own PK. In this case, server should reject client PK and would send back newly generated PK on server dtabase to AIR client to update offline database with server PK. This is required as otherwise on subsequent operations, you would see same record present on both, Offline as well as Server DB, with Different PKs.
Detecting conflicts
Detection of conflcit could be done by comparing OrigionalObject and ServerObject using the ObjectEquals() method, and if they do no match then we prepare a conflict object with latest serverObject and put this conflict in Conflicts array and return it back to client. If there is no conflict, we go ahead and perform the required operations on server database.
<cfset serverobject = EntityLoadByPK("order",originalobject.getoid())> <cfset isNotConflict = ObjectEquals(originalobject, serverobject)> <cfif isNotConflict> <cfif operation eq "UPDATE"> <cfset obj = ORMGetSession().merge(clientobject)> <cfset EntitySave(obj)> <cfelseif operation eq "DELETE"> <cfset obj = ORMGetSession().merge(originalobject)> <cfset EntityDelete(obj)> </cfif> <cfelse><!----Conflict---> <cflog text = "is a conflict"> <cfset conflict = CreateObject("component","CFIDE.AIR.conflict")> <cfset conflict.serverobject = serverobject> <cfset conflict.clientobject = clientobject> <cfset conflict.originalobject = originalobject> <cfset conflict.operation = operation> <cfset conflicts[conflictcount++] = conflict> </cfif>
Handling data conflicts returned by ColdFusion server
If your conflicts array has conflicts in it, it is sent back to AIR client, and AIR client has to work on these conflicts. The AIR client has two choices here: It can override its offline copy of data with a server copy by accepting server data or ignore the server data and keep the client data as it is. The AIR application’s conflict handler is shown in the following code snippet.
private static function conflictHandler(event:ConflictEvent):void { //"Data conflict!"); var conflicts:ArrayCollection = event.result as ArrayCollection; // Ignore Server data and continue working with current client data // var token:SessionToken = session.keepAllClientObjects(conflicts); // Accept Server data and write it to client side SQLite DB var token:SessionToken = session.keepAllServerObjects(conflicts); token.addResponder(new mx.rpc.Responder(conflictSuccess, conflictFault)); }
If you want to deal with conflicts on individual basis by iterating over the Conflicts array collection, you can use other APIs such as keepClientObject and keepServerObject.

Where to go from here

If you have a use case of a sales staff that does not have connectivity to network or server while doing their field work and attending to customers, an offline application can help them collect customer information and place orders. When they get back to office, they just connect to the network and you could automatically sync all the offline sales data in the centralized server database.
Related resources
You can find the ActionScript ORM library, named cfair.swc under following installation folder as default location, ColdFusion901\wwwroot\CFIDE\scripts\AIR. Include cfair.swc file under your Flash Builder project librarypath or directly place it under the libs folder.