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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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:
1 2 3 4 5 6 7 8 9 | <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:
1 2 3 4 5 | var contact:Contact = new Contact(); contact.firstName = "Christophe"; contact.lastName = "Coenraets"; contact.email = "ccoenrae@adob.com"; entityManager.save(contact); |
to modify the contact:
1 2 | contact.firstName = “Chris”; entityManager.save(contact); |
to remove the contact:
1 | 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.
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.
Daniel,
Thanks for the feedback. I would define a Transient annotation, and modify EntityManager to ignore properties annotated with [Transient].
Christophe
[...] 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. [...]
[...] 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 [...]
[...] 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 [...]
AIR good program language i love AIR:)
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?
Never mind…got it…forgotten
…columns-tag
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();
}
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?
Would be interesting to get encryption into the ORM. Any ideas?
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 ?
would be interesting!
Odo, meanwhile establishing a project site…could you please provide some code-samples
for relations/transient extension.
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.
OK Odo, great
I began a small summery of my own modifications
under my linked nick.