I’ve 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 ArrayCollection of typed objects. (This post won’t make much sense if you haven’t looked through his example.)

However, I found myself needing to return an ArrayCollection of objects related to another object by a “has many” relationship.  I’ve set my tables up using ruby on rails — which is a great tool for helping you get your AIR application sqlite db populated and running — so in my application the columns follow the RoR convention: since a “project” has many “scenarios” the scenarios table has a “project_id” column to represent the foreign key relationship.

So how do I get all scenarios for one project id?

Here’s my first attempt at within Coenraet’s EntityManager class to give me all the related tables:

public function findByForeignKey(c:Class, foreignKeyColumnName:String, foreignKeyID:uint):ArrayCollection
{
    if (foreignKeyColumnName=="") throw new Error("foreignKeyColumnName cannot be empty")
    if (foreignKeyID<1) throw new Error("foreignKey must be greater than 0")

    if (!_map[c]) loadMetadata(c);
    var stmt:SQLStatement = _map[c].findByForeignKeyStmt
    var identity:Object = _map[c].identity;

    stmt.parameters[":foreignKeyColumnName"] = foreignKeyColumnName
    stmt.parameters[":foreignKeyID"] = foreignKeyID
    stmt.execute()
    var result:Array = stmt.getResult().data;
    if (result==null) return null
    return typeArray(result, c);
}

Farther down in the EntityManager class, here’s the code that adds the sql statement along with the others…

var findByForeignKeySQL:String = "SELECT * FROM " + table + " WHERE :foreign_key_column_name=:foreign_key_id"

stmt = new SQLStatement()
stmt.sqlConnection = sqlConnection
stmt.text = findByForeignKeySQL
_map[c].findByForeignKeyStmt = stmt

Then, I use this method where I want to load all scenarios for certain project…

     var em:EntityManager.getInstance()
     scenariosAC = db.findByForeignKey(ScenarioVO, "project_id", project.id)

Ok. So this should all work. But…it doesn’t. It turns out that I can’t use parameter replacement within a SQLStatement for anything other than SQL values! So in my statement

var findByForeignKeySQL:String = "SELECT * FROM " + table + " WHERE :foreignKeyColumnName=:foreignKeyID"

that foreignKeyColumName doesn’t get replaced. So, I switched up the method to create a new SQLStatement each time it’s called…which is ugly, but in my application I won’t be using this too much so it’s not a big deal.

public function findByForeignKey(c:Class, foreignKeyColumnName:String, foreignKeyID:uint):ArrayCollection
{
    if (foreignKeyColumnName=="") throw new Error("foreignKeyColumnName cannot be empty")
    if (foreignKeyID<1) throw new Error("foreignKey must be greater than 0")

    if (!_map[c]) loadMetadata(c);
    var stmt:SQLStatement = new SQLStatement()
    stmt.text = "SELECT * FROM " + _map[c].table + " WHERE " + foreignKeyColumnName + "=" + foreignKeyID
    stmt.sqlConnection = sqlConnection
    stmt.execute()

    stmt.parameters[":foreignKeyColumnName"] = foreignKeyColumnName
    stmt.parameters[":foreignKeyID"] = foreignKeyID
    stmt.execute()
    var result:Array = stmt.getResult().data;
    if (result==null) return null
    return typeArray(result, c);
}

And there you go. A simple method to get your “has many” relationships.

I’m working on a database-intensive AIR application, and I’m thinking wistfully of earlier days spent on a Ruby on Rails project. ActiveRecord magnificence.

Alas, there’s no such ORM magic available yet in AIR. Actually, not true. A few people have recently started an AIR ActiveRecord effort, but the project doesn’t seem to be as far along as the Rails implementation.

So, 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.

Note on Christopher’s blog: I haven’t created -config.xml files for my Flex/AIR apps before. Christopher lists some changes to the application’s -config.xml file in order to keep some class metadata around during compiling. I had to dig for a minute or two to figure out that your project will not be created with this file, you simply have to add it yourself when and if you want to mess with the basic compilation process (e.g. if you’ve name your project MyFlexProject, you’ll be adding a MyFlexProject-config.xml file to the src directory.)

From Adobe’s Flex docs:

You can also use a local configuration file that overrides the compiler options of the flex-config.xml file. You give this local configuration file the same name as the MXML file, plus -config.xml” and store it in the same directory. When you compile your MXML file, the compiler looks for a local configuration file first, then the flex-config.xml file.