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:

Please upgrade your Flash Player This is the content that would be shown if the user does not have Flash Player 9.0.115 or higher installed.


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.

  • Digg
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • DZone
  • LinkedIn
  • StumbleUpon
  • Twitter
This entry was posted in BlazeDS, Flex. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

20 Comments

  1. Posted December 1, 2008 at 8:57 pm | Permalink

    Big thanks for sharing this code. I’m wrestling with an AIR app that needs to work extensively with a DB, so your blog posts appeared at just the right time.

    Do you have any thoughts on a metadata convention for properties that should not considered as columns and therefore skipped in your EntityManager loadMetadata() function?

    Thanks.

  2. Posted December 2, 2008 at 1:18 am | Permalink

    Daniel,
    Thanks for the feedback. I would define a Transient annotation, and modify EntityManager to ignore properties annotated with [Transient].
    Christophe

  3. Posted January 9, 2009 at 8:34 am | Permalink

    AIR good program language i love AIR:)

  4. stevie
    Posted January 26, 2009 at 2:15 pm | Permalink

    Hey Chris,
    great work. This makes me very happy. I hope I can use this approch extensivly. Its very smart. I have a little problem. I m using only the SDK (no builder). When I try to add a column to the datagrid (mxml) it throws an error “multiple initializers for property dataprovider”. I want to use ORM with a custom datagrid fited with buttons for delete actions. Till now I got inline editing working by adding editable=”true” itemEditEnd=”form.saveItem();” to the datagrid, but I still cant add columns for other actions/trigger buttons.
    May even it is possible not to use a seperate Form. But I m new to this aproach.

    Do you have a solution to do that?

  5. stevie
    Posted January 30, 2009 at 12:45 pm | Permalink

    Never mind…got it…forgotten

  6. stevie
    Posted January 30, 2009 at 12:45 pm | Permalink

    …columns-tag

  7. stevie
    Posted January 30, 2009 at 12:48 pm | Permalink

    To implement a bit convinience a added the following to the code:

    import mx.events.DataGridEvent;
    private var em:EntityManager = EntityManager.getInstance();

    public function saveItem():void
    {
    em.save(contact);
    parentApplication.loadContacts();
    }//function

    public function saveItemInline(event:DataGridEvent):void
    {
    var field:String = event.dataField;
    contact[field] = event.currentTarget.itemEditorInstance.text;
    em.save(contact);
    }//function

    public function deleteItem():void
    {
    em.remove(contact);
    parentApplication.loadContacts();
    }

    public function newItem():void
    {
    contact = new Contact();

    }

  8. Arnold
    Posted January 31, 2009 at 7:33 am | Permalink

    Rather off topic, but I hope you have an answer. I try to load the data from two sqlite dbs in the class, using sqlconnection.attach. I can read from either bd, but when I try to read from both (or copy from db1 to db2), the show stops.

    Any example of code that actually manages to manage two dbs?

  9. stevie
    Posted February 1, 2009 at 9:10 pm | Permalink

    Would be interesting to get encryption into the ORM. Any ideas?

  10. Odo
    Posted February 3, 2009 at 11:37 am | Permalink

    I was wondering that no one started a Google Code Project with this sources. I have extended the code to have Pre/Post Hooks for all DB Operations and started to work on the mapping for relations to other Entities. Btw. [Transient] Extension is already working. How about an official Project for this ?

  11. stevie
    Posted February 4, 2009 at 8:00 pm | Permalink

    would be interesting!

  12. stevie
    Posted February 4, 2009 at 8:06 pm | Permalink

    Odo, meanwhile establishing a project site…could you please provide some code-samples
    for relations/transient extension.

  13. Odo
    Posted February 6, 2009 at 4:52 am | Permalink

    Stevie,

    i will. Give me sometime. I builded a business unit in our company that works on Flex and innovative interfaces and thats eating my time up at the moment. So i need a week or two. The relations are still very early. But i hope i can start on it the next week.

  14. Posted February 10, 2009 at 3:51 pm | Permalink

    OK Odo, great
    I began a small summery of my own modifications
    under my linked nick.

  15. Posted July 21, 2009 at 9:02 pm | Permalink

    Christophe,
    I loved this article and i’m adoption the ORM Framework to my project, but i have an issue, my database will need update and my tables will problably be changed, how do i manage that without losing client data when i update my application?

    Thank you
    Tiago Lopes

  16. Posted July 28, 2009 at 1:33 am | Permalink

    Интересный пост, спасибо. Меня интересует только вопрос – будет ли продолжение? :)

  17. Posted July 31, 2009 at 10:24 am | Permalink

    Hello,
    I am trying to convert your AIR application into a MXML application. However, after creating a new directory and copy/paset all of the files over (except for the Application.MXML file which I copied only the middle section) I pasted them each into a directoyr project which manages the files for MLEX in Salesbuilder 3.0 I am getting an error in my SQUIL.as file as such:1046 “Type was not found or was not a compile-time constant: File.” See the code below…

    package com.salesbuilder.control
    {
    import flash.data.SQLConnection;
    import flash.data.SQLStatement;
    import flash.data.SQLTransactionLockType;
    import flash.filesystem.File;
    import flash.filesystem.FileMode;
    import flash.filesystem.FileStream;

    public class SQLUtil
    {
    public function executeBatch(file:File, connection:SQLConnection):void
    {
    var stream:FileStream = new FileStream();
    stream.open(file, FileMode.READ);
    var xml:XML = XML(stream.readUTFBytes(stream.bytesAvailable));
    stream.close();
    connection.begin(SQLTransactionLockType.IMMEDIATE);
    for each (var statement:XML in xml.statement)
    {
    var stmt:SQLStatement = new SQLStatement();
    stmt.sqlConnection = connection;
    stmt.text = statement;
    stmt.execute();
    }
    connection.commit();
    }
    }
    }

  18. Posted September 5, 2009 at 10:17 pm | Permalink

    AIR good program language i love AIR :)

  19. Posted October 9, 2009 at 11:46 pm | Permalink

    Christophe,

    Thanks for this sample. Do you know of any production ready ORM solution for AIR?

    Also are there solutions, which handle DB with object relationships?

  20. Mitesh Dave
    Posted January 25, 2010 at 2:44 am | Permalink

    Hi christopher,
    Wonderful work.We all are really thankful to you for sharing such a nice piece of work.But i have a concern can we use complex and nested queries and sub queries with this approach.Since the example posted by you supported more for simple query implementation.can this approach be opted for the project having different type of queries ?.Again thank you for all your work.

4 Trackbacks

  1. [...] If you’re building your own database access layer, Christopher Coenraets just posted a series of articles looking at some basics patterns for db interaction for AIR developers. The third article discusses a basic ORM. [...]

  2. [...] been experimenting with Christopher Coenraet’s example code for a simple ORM. His code provides a simple way to load data from a table and return an [...]

  3. [...] Using the SQLite Database Access API in AIR… Part 3: Annotation-Based ORM Framework : Christo… – 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 [...]

  4. By Flex for PHP developers | Experts Developers on March 1, 2010 at 8:48 am

    [...] will put the tags in the bytecode. You can then use these tags at runtime. For example, in this post, you can read how you use custom metadata tags to provide a way to persist a given data model in an [...]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>