Devin Holloway

ColdFusion 9 provides built-in support for creating offline AIR applications using CF as the backend and full ORM support with SQLite in the AIR client. An interesting aspect of the ORM functionality is that although it integrates well with CF, it can be used independently… even if you’re not using CF on the backend or no server at all. If you happen to be building AIR apps with CF on the server, I highly recommend you review the Offline AIR docs to see what all it can do for you. The rest of this post will be covering the utilization of the AIR ORM piece without using CF.

After installing CF9, you’re provided with a SWC located in [webroot]/CFIDE/scripts/AIR/cfair.swc. This includes all the code needed to enable ORM in your AIR application. As I said before, you don’t actually need CF9 installed to utilize the ORM piece, but you do need to install CF9 (such as the free developer edition) to get access to this SWC file. Once you setup a Flex project, you’ll drop this SWC in your libs folder just like installing any other SWC. I built my demo using the latest Flash Builder 4 beta and its included Flex4 SDK, but this should work just as well with Flex Builder 3 and/or the Flex 3 SDK.

After creating the project and adding the cfair.swc, create a simple master/detail UI consisting of a datagrid and form.

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
					   xmlns:s="library://ns.adobe.com/flex/spark" 
					   xmlns:mx="library://ns.adobe.com/flex/halo">
	<s:layout>
		<s:VerticalLayout horizontalAlign="center"/>
	</s:layout>
	
	<fx:Declarations>
		<!-- Place non-visual elements (e.g., services, value objects) here -->
	</fx:Declarations>
	
	<mx:DataGrid/>
	
	<mx:Form>
		<mx:FormItem label="User ID">
			<s:TextInput/>
		</mx:FormItem>
		<mx:FormItem label="First Name">
			<s:TextInput/>
		</mx:FormItem>
		<mx:FormItem label="Last Name">
			<s:TextInput/>
		</mx:FormItem>
		<mx:FormItem direction="horizontal">
			<s:Button label="Save"/>
			<s:Button label="Delete"/>
		</mx:FormItem>
	</mx:Form>
	
</s:WindowedApplication>

For the next step, create an Object that you want to be mapped to the database. Mine looks like this:

package vo
{
	[Entity]
	[Bindable]
	[RemoteClass(alias="user")]
	public class UserVO
	{
		public function UserVO()
		{
		}
		
		[Id]
		public var userId:int;
		
		public var firstName:String;
		
		public var lastName:String;
		
	}
}

Notice the use of metadata tags. These are required to map the Object to a SQLite table. If you’ve used any of the other 3rd party AIR ORM frameworks out there, the metadata is probably very similar.  The [Entity] tag is required to mark the Object as a persistent entity. The [RemoteClass] tag is used to map to an equivalent Object on the CF server to persist on the server-side. Even though we’re not using CF (or a server-side at all) the RemoteObject is still required (it’s assuming you are utilizing CF) and will throw a runtime error if omitted. Just make sure the alias value is unique for all your entities, and other than that, it won’t be serving any purpose. And finally we have the [Id] used to mark the PK property. There’s a handful of other metadata tags for various other purposes that I’ll go over in a future post.

Back in the main application you’ll start off by creating the database and initializing the ORM session.

	<fx:Script>
		<![CDATA[
			import coldfusion.air.Session;
			import coldfusion.air.SessionToken;
			import coldfusion.air.SyncManager;
			import coldfusion.air.events.SessionFaultEvent;
			import coldfusion.air.events.SessionResultEvent;
			
			import mx.collections.ArrayCollection;
			import mx.rpc.Responder;
			
			import vo.UserVO;
			
			private var dbFile:File;
			private var ormSession:Session;
			private var syncManager:SyncManager;
			
			[Bindable]
			public var userCollection:ArrayCollection;
			
			private function creationCompleteHandler():void
			{
				dbFile = File.applicationStorageDirectory.resolvePath("ormTest.db");
				syncManager = new SyncManager();
				
				var sessionToken:SessionToken = syncManager.openSession(dbFile, 1);
					sessionToken.addResponder(new mx.rpc.Responder(
					function connectSuccess(event:SessionResultEvent):void 
					{ 
						ormSession = event.sessionToken.session; 
						loadAll();
					}, sessionFault));
			}
			
			private function loadAll():void
			{
				var loadSession:SessionToken = ormSession.loadAll(UserVO);
				loadSession.addResponder(new mx.rpc.Responder(
					function (event:SessionResultEvent):void
					{
						userCollection = event.result as ArrayCollection;
					}, sessionFault));					
			}
			
			private function sessionFault(event:SessionFaultEvent):void
			{
				
			}
		]]>
	</fx:Script>

First, I have a few private variables: dbFile, ormSession, and syncManager. The first two should be fairly obvious (references to the database file and the ORM session). The SyncManager is used for syncronizing the SQLite data with the server. Normally, when instantiating the SyncManager, you’d provide it with a few details of your CF server (server name, port, credentials, etc.).  Again, since we’re not utilizing CF in this tutorial, we can omit giving it CF server details and just use it to open a SQLite session. And then I’ve defined a userCollection ArrayCollection which will hold the data we load from the SQLite db (be sure to bind that to your datagrid).

In the creationCompleteHandler (be sure add a creationComplete event in your main WindowedApplication) I’m pointing the dbFile reference to where I want the database stored. The database isn’t created yet, for, AIR will create it for us and the cfair.swc will take care of creating all the tables. Next I’m instantiating the syncManager and having it open a session with the referenced database file. The second is just a session identifier and can be any number you choose. Then I’m adding a responder to the creation of the db session, and in the result of that, creating the ormSession and calling the loadAll() method.

The ormSession variable is what you’ll use from now on to call methods in the ORM API. All of the ORM API methods are asynchronous and require responders if you want to handle the results/faults. The loadAll() method creates a token that calls the ormSession’s loadAll API method which loads all the records of the specified class passed as an argument. The token’s responder result is then just setting the result to the userCollection variable which is bound to the datagrid.

Next add the Save() and Remove() methods, both of which should be self explanatory by now:

private function save(user:UserVO):void
{
	var saveSession:SessionToken = ormSession.saveUpdate(user);
	saveSession.addResponder(new mx.rpc.Responder(
		function (event:SessionResultEvent):void
		{
			loadAll();
		}, sessionFault));
}
private function remove(user:UserVO):void
{
	var removeSession:SessionToken = ormSession.remove(user);
	removeSession.addResponder(new mx.rpc.Responder(
		function (event:SessionResultEvent):void
		{
			loadAll();
		}, sessionFault));
}

Each of these take a user Object to Save/Remove. In the save() method, I’m calling the saveUpdate() method which uses the object’s primary key to decide if it should be inserted or updated. The result of each these methods is calling the loadAll() to refresh the datagrid.

And finally, I’m hooked up the the Form to display the selectedItem from the datagrid, added an MX Model to easily package up the form contents into a User object to be passed to our methods, and gave the form’s Save/Delete buttons appropriate event handlers.  The final code is below.

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009" 
					   xmlns:s="library://ns.adobe.com/flex/spark" 
					   xmlns:mx="library://ns.adobe.com/flex/halo"
					   xmlns:vo="vo.*"
					   creationComplete="creationCompleteHandler()">
	<s:layout>
		<s:VerticalLayout horizontalAlign="center"/>
	</s:layout>
	
	<fx:Declarations>
		<vo:UserVO id="userModel">
			<vo:userId>{Number(userIdTI.text)}</vo:userId>
			<vo:firstName>{firstNameTI.text}</vo:firstName>
			<vo:lastName>{lastNameTI.text}</vo:lastName>
		</vo:UserVO>
	</fx:Declarations>
	
	<fx:Script>
		<![CDATA[
			import coldfusion.air.Session;
			import coldfusion.air.SessionToken;
			import coldfusion.air.SyncManager;
			import coldfusion.air.events.SessionFaultEvent;
			import coldfusion.air.events.SessionResultEvent;
			
			import mx.collections.ArrayCollection;
			import mx.rpc.Responder;
			
			import vo.UserVO;
			
			private var dbFile:File;
			private var ormSession:Session;
			private var syncManager:SyncManager;
			
			[Bindable]
			public var userCollection:ArrayCollection;
			
			private function creationCompleteHandler():void
			{
				dbFile = File.applicationStorageDirectory.resolvePath("ormTest.db");
				syncManager = new SyncManager();
				
				var sessionToken:SessionToken = syncManager.openSession(dbFile, 1);
					sessionToken.addResponder(new mx.rpc.Responder(
					function connectSuccess(event:SessionResultEvent):void 
					{ 
						ormSession = event.sessionToken.session; 
						loadAll();
					}, sessionFault));
			}
			
			private function loadAll():void
			{
				var loadSession:SessionToken = ormSession.loadAll(UserVO);
					loadSession.addResponder(new mx.rpc.Responder(
					function (event:SessionResultEvent):void
					{
						userCollection = event.result as ArrayCollection;
					}, sessionFault));					
			}
			
			private function save(user:UserVO):void
			{
				var saveSession:SessionToken = ormSession.saveUpdate(user);
					saveSession.addResponder(new mx.rpc.Responder(
					function (event:SessionResultEvent):void
					{
						loadAll();
					}, sessionFault));
			}
			
			private function remove(user:UserVO):void
			{
				var removeSession:SessionToken = ormSession.remove(user);
					removeSession.addResponder(new mx.rpc.Responder(
					function (event:SessionResultEvent):void
					{
						loadAll();
					}, sessionFault));
			}
			
			private function sessionFault(event:SessionFaultEvent):void
			{
				
			}
		]]>
	</fx:Script>
	
	<mx:DataGrid id="dataGrid" dataProvider="{userCollection}"/>
	
	<mx:Form>
		<mx:FormItem label="User ID">
			<s:TextInput id="userIdTI" text="{dataGrid.selectedItem.userId}"/>
		</mx:FormItem>
		<mx:FormItem label="First Name">
			<s:TextInput id="firstNameTI" text="{dataGrid.selectedItem.firstName}"/>
		</mx:FormItem>
		<mx:FormItem label="Last Name">
			<s:TextInput id="lastNameTI" text="{dataGrid.selectedItem.lastName}"/>
		</mx:FormItem>
		<mx:FormItem>
			<s:Button label="Save" click="save(userModel)"/>
			<s:Button label="Delete" click="remove(userModel)"/>
		</mx:FormItem>
	</mx:Form>
	
</s:WindowedApplication>

You should now be able to run the application and Create/Read/Update/Delete users without ever creating a database or writing any SQL code. One limitation that I’ve found is that, unless I’ve missed it, there doesn’t seem to be any metadata to specify that the PK is an auto numbered field. This sort of makes sense, for if you were to be using this to sync with a CF server (for which it’s designed for), the workflow would be to pass the newly created objects with the server (from which the IDs get generated) and then sync the result of that back to the client-side SQLite database. In any case, if you’re not planning on managing the IDs in the client code and would rather use an auto number field, it’s easy enough to make that modification directly on the SQLite tables (either right after they get generated or create the database first manually).

In a future post, I’ll discuss managing multiple entities with relationships (one-to-one, one-to-many, many-to-many) as well as the various other entity metadata and ORM API methods available.

posted by Devin  # 11:55 PM 3 Comments