Prerequisite knowledge
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.
User level
Required products
Adobe ColdFusion Enterprise Edition (2016 release) (Download trial)
Flash Builder 4 (Download trial)
Sample files
The Adobe AIR platform has enabled users to write offline SQLite applications that continue to work even when there is no connectivity to a network or server—giving users a seamless experience. However dealing with SQL queries on the client can be messy, as your application has to create replicas of server-side database tables for your AIR application. Also in order to sync client side data changes to your server, you have to design your application to track offline changes. In this case, the client-side ActionScript ORM in ColdFusion 9.0.1 comes in quite handy for AIR application developers. To avoid the problems in this SQL approach for AIR applications, ColdFusion 9.0.1 has introduced an ActionScript ORM framework library for your offline AIR applications.
In this article, AIR developers can learn how to use the ActionScript ORM library (cfair.swc) to develop offline AIR applications without writing a single SQL statement. In this article, you will learn how to fetch server data and store it offline, keep the AIR client in sync with server, manage conflicts between the client and server data while syncing with each other through a customer order sample application.

The Customer Order Sample Application

Throughout this article, I use code snippets from the customer order product sample application to explain various concepts. This application has three screens, as shown in Figure 1.
The three screens in the customer order product sample application.
Figure 1. The three screens in the customer order product sample application.
The Customer Information screen is a complex one. This screen demonstrates various self joins associated with 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. 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. The Place an Order screen lets you place an 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 that indicates that the application is in online mode. If it is red, it means application is running in offline mode. In the event that the application is running in offline mode and there are offline changes present, you would notice a green round tick mark image indicating that offline changes are not synced with server.
Flow of the application
  • When the application starts, 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 yet. The next time the application starts, if there is already an offline database, your application will not attempt to recreate it.
  • Now your application has established a connection session (which is again stored as static session reference), such that you can access this connection 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 sessions would lock 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 commited on the server database. If the application is offline, commit will be attempted when the server becomes reachable.
  • During commit operation on server, there might be conflicts between the client data and server data, which the server will send back to the client to handle with the 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 of these metadata tags, have been introduced to let the user class define relationships with other ActionScript classes in the application. In the above example, the Customer class shares a different relationship with other classes such as Address, Order, and 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); it also indicates the strategy for auto-incrementing 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.
Entity relationship diagram of an offline database that was created based on a CRUD operation
Figure 2. Entity relationship diagram of an offline database that was created based on a CRUD operation
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 an operation modifies Customer, the application also modifies Address 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
This 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 the Orders and Product table and it stores various products associated with an Order.
To learn more about relationships, cascadeType and FetchType, refer to the Offline AIR Application Support chapter in 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 the ColdFusion server. It lets users specify the server credentials to connect to ColdFusion server. Below is 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 occurs while writing 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 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, your application does not recreate 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 if they would like to encrypt the offline database. You should know, however, that it is not mandatory to encrypt the offline database. Yet be aware that there could be multiple users to a machine and other people may get a hold of the offline database and get access to sensitive data from the user. It is always a good practice to encrypt the offline database. Once you have generated the encryptionKey you can pass it to the openSession() API as a parameter in case you want to encrypt the offline database. This is 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 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.
Fetching data records from ColdFusion Server
The following code snippet explains how to use the syncmanager instance that we had created earlier 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 given by the user; 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.
// Fetch Server side DB data onto Client SQLite DB while starting the application 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)); }
Storing Fetched Data in an Offline Database
Once your application receives data from the server, the next logical step is to store it in the offline database so that even when the application is not connected to the 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 method, we use the session created earlier. In my application, I have created this static session in a class called GlobalVars. Here, you can see that I prefix the 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.
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(); } }

Performing CRUD Operations

The following code snippet is a simple example of how to perform various product CRUD operations.
// 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 key point 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 the ColdFusion Server-side Code

We have discussed client-side ColdFusion ActionScript ORM APIs. In these APIs there are two APIs that make calls to the server. Those are, syncmanager.fetch() and session.commit(). On the ColdFusion server-side, we have two methods corresponding to these client APIs, called fetch and sync, which are defined in the AIRIntegration.cusManger CFC. There is one more rule for the cusManager.cfc file in that it has to implement the CFIDE.AIR.ISyncManager interface. This interface calls for a Sync method.
Let us have a look each method.
The Fetch method
The following is the fetch method that I have used to fetch all customers or an individual customer using the ColdFusion server-side ORM EntityLoad() method. Because the ColdFusion server ORM is a huge topic, I will refrain from explaining it in full detail and will only highlight what is required for you to work with this tutorial. In the code below, the ORM CFC calls 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 record; your application also returns the database records as an array to the AIR client. Your AIR client uses the fetchSuccess() handler to receive the data.
<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 (operations, clientobjects, and the originalobjects 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 the server ORM CFC. In my example application, the client object could be any one of the 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 conflicts = 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 in AIR. OrigionalObject is nothing but the initial copy of the object when it was loaded from server. When you receive copies of origionalobject on the server for the sync method call, as this helps you detect conflicts. Later, you will see how to detect and handle conflicts.
If the operation is INSERT, OrigionalObject is NULL as it is a new copy which is not yet saved on the server. For an UPDATE operation, both the clientObject and OrigionalObject are present. In the case of a DELETE operation, the ClientObject is NULL, as it has already been deleted from the AIR client-side database; as well, it will be deleted on the server using the OrigionalObect copy, after checking for conflicts. In the above method, depending on the type of object you send them to different handler CFML files. In these CFML files, you 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 the Sync method.
  • If there is no conflict, the application performs the desired operation comparing the client/original object against server database.
In my example application, I use the server ORM method EntitySave() for insert and update operations and the EntityDelete() method for delete operations.

Handling Conflict Management

Conflict management is a very important aspect for AIR applications that work in offline mode. Due to non-connectivity, there is a possibility that the AIR client might work with stale data, as another 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 tries 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 the client PK and send back a newly-generated PK on the server database to AIR client to update the offline database with a server PK. This is required, otherwise on subsequent operations, you could see the same record present on both offline and server databases, with each having a different PK.
Detecting conflicts
You can detect conflict by comparing OriginalObject and ServerObject using the ObjectEquals() method. I they do no match, prepare a conflict object with latest serverObject, put the conflict in Conflicts array, and return it back to client. If there is no conflict, 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 the ColdFusion server
If your conflicts array has conflicts in it, your application sends them back to the AIR client; the AIR client has to work on these conflicts. The AIR client has two choices: It can override its offline copy of data with a server copy by accept the server data or ignore the server data and keep the client data as 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 work with conflicts individually by iterating over the Conflicts array collection, you can use other APIs such as the keepClientObject and keepServerObject APIs.

Where to go from here

If you had sales staff that with little or no connectivity to network or a 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 simply need to connect to the network and you could automatically sync all the offline sales data in a 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.