Feed the Beast

Working with the local database can be a bit of a challenge.  If the db is not well organized and if your application structures are not well thought out then you can end up with a real mess.  As I said before, sitting down and thinking the data layout all the way through and performing a few simple normalization exercises goes a long way to making the db construction a lot easier.

Knowing how AIR interacts with the local SQLite database helps as well. Essentially there are three parts to an AIR database setup:

  • The SQLite database
  • A class to interact with the database
  • Classes to hold the results of a database query

The SQLite database

AIR uses SQLite for a local/offline database.  SQLite is a very easy db to work with, but its pretty Mickey Mouse in a lot of respects.  For example there is no true auto-increment feature so the developer has to handle record id generation him/herself.  Also there are no foreign keys which means that most relationships between tables must be handled by the calling application.  How can you call yourself a relational database when the major function for controlling relationships does not exist?!

One tool that anyone working with SQLite databases must have is a database management tool. Sometimes you just need to have a look at what is going on inside the database itself.   There are a few out there for SQLite, but one of the most useful is a simple plugin for Firefox.  SQLite Manager has many useful features including a simple SQL execution tool, that will make your SQLite development much easier.  Go get it!

The nice thing about using the built in SQLite database is that you don’t need to mess around with a lot of connection properties.  You just point to a .db file (or create one) and away you go. You can concentrate on working with your data instead of trying to find the database.

Database Interaction Class

To make life easier I re-used a standard J2EE pattern for database connectivity – the Data Access Object (DAO).  A DAO is nothing more complicated than a class that acts as an interface between the AIR app and the database.  This way all db interactions (connection, queries, inserts, etc.) can be centralized and the rest of the AIR application doesn’t need to deal with the db trivialities.  The db structure can also be changed without having to rewrite the entire application.

The first thing that I needed to do was to connect to the database – actually before that I needed to check if the database exists and if not then create it.   I did this in the constructor for the DAO class as it only needs to be done once.

//private vars
private var _dbExists:Boolean;
private var _dbConnection:SQLConnection;
private var _createFlyTypeTableStmt:SQLStatement = new SQLStatement();


public function LocalDAO(){
   _dbConnection = new SQLConnection();

// Add event listeners to react when database connection is opened.

_dbConnection.addEventListener(SQLEvent.OPEN, openSuccessHandler);
//_dbConnection.addEventListener(SQLErrorEvent.ERROR, openErrorHandler);

  // The database file is to be stored in the user’s application (UserData) folder.
var dbFile:File = File.applicationStorageDirectory.resolvePath("FlyData.db");
trace("Database file location is: " + dbFile.nativePath);

//Check if the database has already been created.

_dbExists = dbFile.exists;

  //Open the connection.

The nice thing is that if the FlyData.db database file does not exist then one will be created for me. 

The openSuccessHandler listener will wait until the db is opened (or created and opened).  If it is newly created then I want to create the tables in the database as well.  The listener will then call a method called initDatabase that will fire a bunch of CREATE SQL statements. Most interactions with the database happen via a SQLStatement, through which you pass standard SQL queries as strings.  The SQLStatement is pointed to the correct database using a SQLConnection object.  

* Success handler for _dbConnection

private function openSuccessHandler(event:SQLEvent):void
    //We have an open handle to the database file.
    //Next, we need to create the tables if this is a new database

          trace("Application Database already exists.");
         trace("New Application Database, must generate tables");

private function initDatabase():void{
    _createFlyTypeTableStmt.sqlConnection = _dbConnection;

//Create the database tables

var sql:String = "CREATE TABLE `Material` (`MaterialID` INTEGER UNSIGNED NOT NULL, `MaterialName` VARCHAR(45) NOT NULL,`Color` VARCHAR(45) NOT NULL, `Size` VARCHAR(45) NOT NULL, PRIMARY KEY (`MaterialID`))";
_createFlyTypeTableStmt.text = sql;

//more create statements follow …..

Great, now the database exists, has been connected and I know for sure that I have a table structure.  Next I need to be able to select and insert records.

Query Results Classes

You need to have a place to put the results of a search.  In Java you have a result set that you can parse for specific db columns.  In AIR the execution of an SQLStatement returns an array of objects.  The objects must match the structure of the query exactly (and I do mean exactly).  Therefore, for all practical purposes, you need to have a class for each database search – one for each table (or one that matches multiple tables in the case of a join).

The next thing to do then is to create classes for each of the possible searches.  In my case this means that each table will need a corresponding class.  For example I have a table called Material that has a MaterialID, MaterialName, Color and Size.  I therefore need a class that has all of those elements:

package components.dataStructures
   public class Material    {
      public var MaterialID:int;
      public var MaterialName:String;
      public var Color:String;
      public var Size:String;

   public function Material(){


When I perform a query against the Material database the result will be mapped into an array of the class Material:

public function getMaterials():void{

    _dbMaterialStatement.sqlConnection = _dbConnection;
    var sqlQuery:String = "SELECT * FROM Material";
    _dbMaterialStatement.itemClass = Material;
    _dbMaterialStatement.text = sqlQuery;
   //SQL calls are asynchronous so a listener for the results is required
    _dbMaterialStatement.addEventListener(SQLEvent.RESULT, onDBMaterialSelectResult);


In AIR, database calls are asynchronous.  This means most calls to the db will involve the setup of a listener.  This can be a bit of a pain because its difficult to just setup a method to make a call and return a value, you have to setup a listener to wait for the call to be executed.  This leads to many, many listeners floating around and , take my advice, you need to make sure you remove the listeners when they are not needed.

private function onDBMaterialSelectResult(event:SQLEvent):void{
   var result:SQLResult = _dbMaterialStatement.getResult();
   if (result != null){
       MaterialData = result.data;

Guess What Class I Am

As I said earlier, because SQLite does not implement a proper auto-increment feature, you must do it yourself.  Shouldn’t be too hard – get the MAX of a column, add one and Bob’s your Uncle.   Something like:
"SELECT MAX(MaterialID) FROM Material"
should work fine. That works, but there is an important little trick.  AIR returns the result of all db queries as an array of objects, you must specify what those objects are, usually by setting the SQLStatement’s itemClass property.  Unfortunately this doesn’t seem to work too well for the MAX function.  I thought that MAX should return an integer, or at best a long – but it doesn’t. Setting the code to:
_dbMaxStatement.itemClass = int;
didn’t work.  With much use of the Flex Builder’s debugging tool, I determined that the MAX statement was returning an "id" class.  Okay, what the heck is an id class?  I can’t just set
_dbMaxStatement.itemClass = id;
because I don’t have the class "id" anywhere in my code – and I couldn’t figure out its package designation. I tried recasting the result to an integer and it didn’t work either. This all got very frustrating until a colleague pointed out that you can set the returned class type in the SQLStatement text itself. This lead to the following code:

_dbMaxStatement.sqlConnection = _dbConnection;
var sqlQuery:String = "SELECT MAX(MaterialID) AS id FROM Material";
_dbMaxStatement.text = sqlQuery;

var result:SQLResult = _dbMaxStatement.getResult();
var myint:int = result.data[0].id;
MaterialMax = myint;
trace ("***** MaterialMax: " + MaterialMax);

That little "AS id" makes all the difference in the world.

Prepared Statement

The one other thing that I wanted to mention is the use of variables inside a SQLStatement.  Since the statement is essentially a string, you could write it like:
var sqlQuery:String = "INSERT into Material (MaterialID, MaterialName, Color, Size) VALUES(‘" + material.MaterialID + "‘,’" + material.MaterialName + "‘,’" + material.Color + "‘,’" + material.Size + "‘)"; The problem is that keeping track of the quotes and commas quickly becomes a nightmare.  Fortunately there is an easier way.

In Java there is the concept of a "prepared statement" in which a question mark is substituted for a variable entry in an SQL statement.  ActionScript has the same concept although it uses an @ symbol and a variable name instead of a question mark.  Using this method the same SQL command can be written as:

var sqlQuery:String = "INSERT into Material (MaterialID, MaterialName, Color, Size) VALUES(@MatID, @MatName, @MatColor, @MatSize)";
_dbMaterialStatement.text = sqlQuery;
_dbMaterialStatement.parameters["@MatID"] = material.MaterialID;
_dbMaterialStatement.parameters["@MatName"] = material.MaterialName;
_dbMaterialStatement.parameters["@MatColor"] = material.Color;
_dbMaterialStatement.parameters["@MatSize"] = material.Size;

This makes the code much easier to read and maintain.


Using the above methods I created the other tables, classes for holding results and DAO methods for querying and updating the database.  The next step will be to wire the database stuff back into the display objects that I created earlier.  As you may recall I had setup some temporary data sources that were hard coded.  I now want to use the real objects as returned from the database.

Comments are closed.