Using the SQLite database access API in Adobe AIR

In my MAX 2008 session, Liberate Your Data with AIR, I presented three different approaches to access local data using the SQLite database access API in AIR:

• "SQL in View"
• DAO pattern
• Annotation-based ORM framework

I demonstrated a version of inSync (a simple contact management application) built with each of these approaches. In this article, I summarize the benefits and limiations of each approach.

Note: This application also demonstrates how to take a picture of a contact using your webcam and store it in the SQLite database as a blob. Note also that the application uses the same skin and overall context as a sample I posted previously to illustrate offline data synchronization using LCDS, but the implementation is entirely different: No LCDS here… just local data access.

Using the "SQL in View" approach

In the "SQL in View" approach, you embed SQL statements as needed in View components. In this example the ContactForm component has create, update, and delete methods with the appropriate embedded SQL statements to insert, update, and delete a contact in the database. This approach works for quick prototyping, but is generally a bad practice. When you mix view logic and data access logic in the same component, neither the view logic nor the data access logic is reusable: you can't reuse the view with a different way to access your data, and you can't reuse your data access logic with a different view.

Download the inSync Local DAO Edition source code.

I provide this approach here as a quick way to get acquainted with the database access API in AIR. For real-world implementations, a more partitioned approach such as the DAO pattern or an ORM framework is highly recommended.

Using the DAO pattern

In this second version, I use the Data Access Object (DAO) pattern to improve the overall architecture of the application. A Data Access Object typically encapsulates the data access logic for one entity (in this case: Contact).

An interface (IContactDAO) defines the contract:

The ContactDAO class implements that interface and provides one specific implementation of the contract (persisting data to the embedded SQLite database).

Download the inSync Local DAO Edition source code.

This approach has the following benefits:

• The View doesn't know anything about your data access logic: You can reuse the same view (ContactForm) with a different way to access your data. You would just create another class implementing IContactDAO and pass an instance of that class to ContactForm. Note that the dao property of ContactForm is of the IContactDAO data type (the interface). This allows us to pass an instance of any class implementing the IContactDAO interface to ContactForm.
• The DAO doesn't know anything about the view: You can reuse the same data access logic from within different views.

The limitation of this approach is that you still have a lot of SQL code to write. You could use one of the following approaches to overcome this limitation:

• You could create a mini DAO framework where a base DAO class would take care of all the boilerplate code to set up and execute SQL statements. (See the BaseDAO class in Salesbuilder for an example).
• You could use an ORM framework where SQL statements are automatically generated.

Annotation-based ORM framework

In this approach, I use a mini object-relational mapping (ORM) framework that leverages the Flex support for class annotations to entirely eliminate manually written SQL statements. I first explored this approach at MAX 2007.

The idea is that you 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, you 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), and so on.

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

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

This instructs the compiler to keep your metadata in the generated SWF file 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 meta data 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:

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.