Using the SQLite Database Access API in AIR… Part 3: Annotation-Based ORM Framework

In the second version of our contact management application, we encapsulated the data access logic for the Contact entity in a Data Access Object (ContactDAO). This separation of concerns represented a major improvement compared to our first approach. The remaining limitation we identified was the amount of SQL we had to write “manually”.

In this third version, we use a mini Object Relational Mapping (ORM) framework that leverages the Flex support for class annotations to entirely eliminate manually-written SQL statements.

This is an approach I first explored at MAX 2007 (see original blog post here).

The idea is that we need to add a few hints to a model class definition for an automated system to be able to generate all the SQL statements required to persist instances of that class. For example, we need to specify which field is the entity identifier (primary key), as well as any discrepancy between a class field name and the corresponding table column name (firstName and lastName in this example), etc.

The annotated Contact class used in this example looks like this:

package
{
	import flash.utils.ByteArray;

	[Bindable]
	[Table(name="contact")]
	public class Contact
	{
		[Id]
		[Column(name="contact_id")]
		public var contactId:int;

		[Column(name="first_name")]
		public var firstName:String;

		[Column(name="last_name")]
		public var lastName:String;
		public var address:String;
		public var city:String;
		public var state:String;
		public var zip:String;
		public var phone:String;
		public var email:String;
		public var pic:ByteArray;
	}
}

[Bindable] is the standard Flex metadata annotation while Table, Id and Column are custom. Custom annotations are defined in the application config file (inSyncLocalORM-config.xml) as follows:

<flex-config>
   <compiler>
      <keep-as3-metadata>
          <name>Table</name>
          <name>Column</name>
          <name>Id</name>
      </keep-as3-metadata>
   </compiler>
</flex-config>

This instructs the compiler to keep your metadata in the generated SWF so that you can get to this information at runtime using the reflection API (describeType). Click the Describe button (Debug icon) in this version of inSync to see the describeType result that includes the metadata information.

That’s all you have to do to provide your AIR applications with automatic persistence to the embedded SQLite database. No SQL to write! The framework will even generate the table if it doesn’t already exists.

For example to add a new contact to your database, you’d simply do something like this:

var contact:Contact = new Contact();
contact.firstName = "Christophe";
contact.lastName = "Coenraets";
contact.email = "ccoenrae@adob.com";
entityManager.save(contact);

to modify the contact:

contact.firstName = “Chris”;
entityManager.save(contact);

to remove the contact:

entityManager.remove(contact);

You can provide the entityManager with instances of any annotated class and it will figure out how to persist the object (how to generate the appropriate SQL statements) based on your metadata annotations.

Install inSync Local ORM Edition:

Click here to download the source code. You can also right-click the app and select View Source to view the source code and download the application.

Disclaimer: This is still a simplistic proof of concept and is by no means a production ready ORM solution. Some basic assumptions are made for simplicity. For example, I assume that all primary key are autoincremented integers.