ColdFusion 9 - AIR SQLite Offline Support

In this Article, I will be explaining about the new feature, AIR Offline Support, introduced in ColdFusion 9. This Article will try to cover, most of the aspects of the feature with Code snippets to simplify the subject.

Now, before explaining this feature I would like to cover a few basic questions which user may have in mind.

Is this a client side feature or server side feature?

This is mainly a client side feature which could be used for Flex based AIR applications. Browser based Flex applications can not take advantage of this feature as it needs client side SQLite DB which is offered only by AIR platform.

Also, There are a few ColdFusion Interfaces defined on server side, which user need to comply with while writing their CFC having “Fecth” and “Sync” methods.

Why Offline Support in ColdFusion 9 where as AIR also provides it?

One may argue that AIR provides Offline SQLite DB support, then why ColdFusion 9 is redoing it? To clarify on this, ColdFusion 9 is not redoing it, but rather making it more simplified by introducing an ActionScript persistence framework which could help in taking all the complexities and pain away from user to deal with Client side DB/Tables/SQL Queries etc...

Why should I use Offline feature of ColdFusion 9 ?

By using the ActionScript persistence framework introduced in ColdFusion 9, user doesnt need to create the client side DB Schema, Tables and also user doesn’t need to write even a Single line of SQLite Query to store/update/retrieve data from Offline DB. Isn’t it simplified ?

How can ColdFusion 9 Offline feature do it without writing Queries?

This ActionScript Persistence Framework, comes with many Metadata tags to assist user in defining their client side classes which should be mapped with Client side DB. See the few basic Metadata tags information in the following table.

MetaData Tags	Purpose
[Entity]	Specifies that instances of this class can be persisted to the SQLite database. This element is required.
[Table( name = "tableName")]	The name of the SQLite table in which the object is to be stored. Defaults to the name of the class.
[Id]	Precedes a field definition. Indicates that the field is a primary key in the table. For composite keys, use Id tags on all the primary key fields.
[Column(name="name", columnDefinition="TEXT INTEGER FLOAT BOOLEAN VARCHACHAR", nullable = true false, unique = true false ) ] ),	Specifies the SQLite database column that contains data for this field. name Column name. If not specified, defaults to the property name. columnDefinition The SQL Datatype to use for the column. nullable Specifies whether a field can have a null value. unique Specifies whether the value for each field must be unique within the column.

This framework also offers Relationship Metadata tags, like [OneToOne],[OneToMany],[ManyToMany],[JoinTable],[JoinColumn],[InverseJoinColumn] which are used to define relationships between the AS classes, which is also being followed while creating Offline tables for these classes. Refer ColdFusion9 AIR Offline Documentation for more information on these Relationship metadata tags.

Let’s have a look at a Simple AIR Offline Application Example ….

Given is the Screenshot of the Application. Application has mainly 2 sections, top section is used for performing the CRUD operations and Bottom datagrid panel provides user to view the local SQLite data for Customer and Address tables. Also it provides 2 buttons for Fetching data from server and Syncing local data changes back to server.

This Application demonstrates working of two Objects, Customer and Address, having One-To-One relationship.

It also demonstrates concepts of CASCADING, LAZY Loading, FETCHing and PERSISTing Server data on client side SQLite DB.

CONFLICT management is also one of the important concept for Offline applications, as user may be working in offline mode for a long time and when user tries to sync the data back to server it may happen that those datasets are already updated or deleted by other clients.

Also on server side it uses, newly introduced feature of ColdFusion 9, ORM, for Fetch() and Sync() methods. To get to know more on ORM feature of ColdFusion 9 refer the documentation.

How to setup this example as a project ?

Download this application from below link.

-->After extracting the ZIP file, put “AIRIntegration” folder directly under your ColdFusion 9 wwwroot

-->Create a DataSource called ‘AIROffline’ in CF Admin on “AIRIntegration\

ServerSideDB_AIROfflineExample.mdb” using MS Access with Unicode as driver.

--> Import ‘CFAIROfflineCustomerManagerApp.zip’ as Flex Project into your Flex Builder.

--> Once Imported you may need to change ‘cfair.swc’ path from Project->Properties-> Flex build Path -> Library path. Adding this SWC file into your library path is very important in order to use AIR Offline feature of Coldfusion 9. You may also want to change your compiler arguments to set the right path for services-config.xml file.

--> Change the SyncManager CF Server credentials in application, as per your CF Server IP/Port/ContextRoot.

Following above steps should be good to go for launching this Application.

Let’s have a look at Client Side ActionScript Classes…

I have explained the constructs of the class by providing inline comments, so read through it.

Customer.as

package onetoone

{

[Bindable]

// corresponding Server side class

[RemoteClass(alias="AIRIntegration.customer")]

// This tag is required to create a client side Table for Class

[Entity]

// Optional. If table name is not provided, class name will be

//considered as default name for creating table

[Table(name=”Customer”)]

public class Customer

{

//Define PK for Offline “Customer” table, “cid” will be PK

[Id]

public var cid:int;

public var name:String;

// Defines OneToOne relationship with Address class

[OneToOne(cascadeType='ALL',fetchType="EAGER")]

// Foreign Key Column which will be referring to Address table

// name="add_id" --> FK Column Name

// referencedColumnName="aid" --> Refers to the Address Table PK

[JoinColumn(name="add_id",referencedColumnName="aid")]

public var address:Address;

}

}

Address.as

package onetoone

{

[Bindable]

[RemoteClass(alias="AIRIntegration.address")]

[Entity]

public class Address

{

[Id]

public var aid:int;

public var street:String;

}

}

Corresponding Server side ORM CFCs ….

customer.cfc : This ORM Cutomer CFC has one-to-one relationship with Address CFC, as it can be observed in the 3^rd cfproperty defined.

<cfcomponent persistent="true">

<cfproperty name="cid" fieldtype="id" >

<cfproperty name="name" >

<cfproperty name="address" fieldType='one-to-one' CFC="address" fkcolumn='aid' cascade='all' lazy="false">

</cfcomponent>

address.cfc: This ORM Address CFC has no references back to Cutomer CFC it being a one directional relationship. Although it’s quite possible to have bidirectional relationship.

<cfcomponent persistent="true">

<cfproperty name="aid" fieldtype="id" >

<cfproperty name="street" >

</cfcomponent>

cusManger.cfc: This is the Manger CFC which AIR Offline app client interacts with. This Manger CFC will have to implement “CFIDE.AIR.ISyncManger”. This interface has declaration for SYNC method. The SYNC Method in the following code handles CONFLICT management as well part from persisting client data changes to server. It Uses ORM Entity methods to perform the CRUD operations.

These CRUD operations could also be done using normal but that would be not so clean way of writing SYNC method. Because Query approach will require query records sets to get translated into Objects before sending data to AIR Offline app client. And also vice versa, each and every property of AIR Client Objects needs to be accessed for performing CRUD operations.

Also Notice the ORMGetSession().merge() methods in SYNC method usage before calling EntitySave() / EntityDelete() methods. You may see following kind of error message if you are using Old Style (cf8) Remoting with AIR Offline applications having server side "Sync" method using ORM EntitySave()/EntityDelete() methods.

Error handling message: flex.messaging.MessageException: Unable to invoke CFC - a different object with the same identifier value was already associatedwith the session: [address#1]. Root cause :org.hibernate.NonUniqueObjectException: a different object with the same identifier value was already associated withthe session: [address#1]

You may also encounter this error with new style (cf9) Remoting also but only for EntityDelete method.

Same above solution will apply to resolve this issue.

<cfcomponent implements="CFIDE.AIR.ISyncManager">

<!----Fetch method--->

<cffunction name="fetch" returnType="Array" access="remote">

<cfset cus = ArrayNew(1)>

<!----This ORM Method will load all data from Server Customer table and send it to AIR client ---->

<cfset cus = EntityLoad("customer")>

<cfreturn cus>

</cffunction>

<!----SYNC method, Sync Client data with Server and also handles Conflicts--->

<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">

<cfset conclits = ArrayNew(1)>

<cfset conflictcount = 1>

<cfloop index="i" from="1" to="#ArrayLen( operations )#">

<cfset operation = operations[i]>

<cfset clientobject = clientobjects[i]>

<cfset originalobject = originalobjects[i]>

<cfif operation eq "INSERT">

<cfset obj = ORMGetSession().merge(clientobject)>

<cfset EntitySave(obj)>

<cfelseif listfindnocase("UPDATE,DELETE",operation) neq 0>

<cfif isinstanceOf(originalobject,"customer")>

<cfset serverobject = EntityLoadByPK("customer",originalobject.getcid())>

<cfelseif isinstanceOf(originalobject,"address")>

<!---Ignoring Address object, as it is casceded with Customer Object, so while saving Custoemr, Address will also be saved,

If not Ignored, then It will result in Conflict, incase Cutomer and Address CFC relationship has lazy=fasle.

--->

<cfcontinue>

<!---<cfset serverobject = EntityLoadByPK("address",originalobject.getaid())>

<cflog text="AddressID: #originalobject.getaid()#">--->

<cfelse>

<cfthrow message="Invalid Object">

</cfif>

<cfif not isdefined('serverobject') >

<cflog text="CONFLICT::SERVER OBJECT NOT FOUND, RECORD MAY BE DELETED ALREADY">

<cfset conflict = CreateObject("component","CFIDE.AIR.conflict")>

<cfset conflict.clientobject = clientobject>

<cfset conflict.originalobject = originalobject>

<cfset conflict.operation = operation>

<cfset conflicts[conflictcount++] = conflict>

<cfcontinue>

</cfif>

<cfset isNotConflict = ObjectEquals(originalobject, serverobject)>

<cfif isNotConflict>

<cfif operation eq "UPDATE">

<cfdump var="#clientobject#" output="C:\clientobject.txt">

<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>

<cfcontinue>

</cfif>

</cfif>

</cfloop>

<cfif conflictcount gt 1>

<cfreturn conflicts>

</cfif>

</cffunction>

</cfcomponent>

What is the Flow Of the client side AIR Application ?

It is upto developer to decided on the Offline application workflow depending upon the application requirements. But I am trying to present here a general approach which may be suitable for a large number of offline applications.

As soon as this example application is launched, it will first try to connect to server by creating an instance of SyncManger, in order to fetch the server data. See the following code snippets with inline comments provided.

private function init():void

{

// Provide Credentials for Server side Connection and CFC

syncmanager = new SyncManager();

syncmanager.cfPort = 8501;

syncmanager.cfServer = "localhost";

// Path of the Server side CFC having Sync/Fetch method, relative from CF webroot

syncmanager.syncCFC = "AIRIntegration.cusManager";

// THis handler will be called when any COnflict occures while writing back changes on serverside

syncmanager.addEventListener(ConflictEvent.CONFLICT, conflictHandler);

// Fetch Server side DB data onto Client SQLite DB while starting the App itself

var token:AsyncToken= syncmanager.fetch("fetch");

token.addResponder(new mx.rpc.Responder(fetchSuccess, fetchFault));

}

Once Server connection is established successfully it fetches the data on client side and stores it on local clientside SQLite DB by creating a DB, if already not created.

private function fetchSuccess(event:SyncResultEvent):void

{

var cus:Array = event.result as Array;

cusColl = new ArrayCollection(cus);

// Open a Session for the client side SQLite DB, It will create a //DB with Name “onetonesync.db” under user directory

dbFile = File.userDirectory.resolvePath("onetoonesync.db");

/*

Providing a Unique interger number in openSession method is required in order to create a unique database session, in order to avoid conflicts with other Applications.

*/

var sessiontoken:SessionToken =syncmanager.openSession(dbFile,017915);

sessiontoken.addResponder(new mx.rpc.Responder(connectSuccess,connectFault));

}

private function connectSuccess(event:SessionResultEvent):void

{

session = event.sessionToken.session;

if(cusColl.length > 0)

{

// This operation will save/update fetched data into AIR SQLite DB

var savetoken:SessionToken = session.saveUpdateCache(cusColl);

savetoken.addResponder(new mx.rpc.Responder(saveCacheSuccess, savefault));

}

else

{

Alert.show("No data available from Server to save on local DB, Grid will be attempted to load with local DB Data if any available");

updateGrid();

}

}

So now, user has local data available to work with in offline mode. This data will be available even in case of server is unreachable or network outage.

User has all the liberty to perform CRUD operation by Adding new records, by Editing existing Records or by Deleting them.

// This Method will be used for Insert / Update operations

private function SaveLocal():void

{

cus = new Customer();

cus.cid = int(cusId.text);

cus.name = cusName.text;

add = new Address();

add.aid = int(AddId.text);

add.street = AddStreet.text;

cus.address = add;

/*

INSERT the new Records, this will be first saveed in client side SQLite DB, only on Commit operation this will be saved this new records in Server side DB. Notice that we are only saving Customer here, this operation will also save the binded Address Object also, as both the entities are CASCADED inside Customer Class

*/

var savetoken:SessionToken = session.saveUpdate(cus);

savetoken.addResponder(new mx.rpc.Responder(savesuccess, savefault));

}

// This Method will be used for Delete operations

private function DeleteLocal():void

{

cus = new Customer();

cus.cid = int(cusId.text);

cus.name = cusName.text;

add = new Address();

add.aid = int(AddId.text);

add.street = AddStreet.text;

cus.address = add;

var savetoken:SessionToken = session.remove(cus);

savetoken.addResponder(new mx.rpc.Responder(removeSuccess, removeFault));

}

At any point in time if user feels like getting fresh data from Server, he can do so by pressing the “Fetch Data Server” button. This operation will override the Updated/deleted data changes by user. Newly inserted records on local DB table will have no impact by Fetch data operation. So it makes sense to perform this operation while only when you changes are Synced with Server.

Once after performing the required data changes, user needs to write back this data on server. It can be done by performing “Commit” operation by pressing “Commit/Sync local data to Server” button.

private function commit():void

{

/*

So far, we have performed Insert/Update/Delete operation on Customer/Address entities on client side SQLite DB, Now let's send them to Server by performing COMMIT Operation

*/

var committoken:SessionToken = session.commit();

committoken.addResponder(new mx.rpc.Responder(commitSuccess, commitFault));

}

Now, while Syncing back data on Server, it may happen that user has stale data that he was working on OR User is updating a record that is already deleted from Server. In this case, Server CFC will throw back an array of Conflicts back to client, informing client about the latest server data copy. There will be a Conflict Event on client and eventually Conflict Handler will be called. Now, it is upto user to Accept / Ignore this Server changes. Incase, user wants to Accpets these changes he can do so as following.

One more Important aspect of Conflict handler that one need to know is, even incase of conflict, the Commit Success event will be fired. So, you will see that ConflictHandler as well as CommitSuccess Handlers both will be called. This happens because commit success event is fired when data changes from client reaches to server successfully. Now, this data gets written on server DB or not, it doesn’t wait for this. And hence, if there are conflicts here, we fire another Conflict event.

private function conflictHandler(event:ConflictEvent):void

{

Alert.show("conflict man!");

var conflicts:ArrayCollection = event.result as ArrayCollection;

// 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));

}