TOKI WOKI. 

I was talk­ing about it, here it is!

This class will come in handy every time you work with syn­chro­nous SQLite data­bases in an AIR project. It’s a real-world imple­men­ta­tion of the Dynam.ize util­ity I pre­sented ear­lier… I will show you an exam­ple of how to work with it, with sim­ple sub-classes. Let’s go!

First things first.

I need to open my data­base and pro­vide that con­nec­tion to BaseObect:

var sqlCon:SQLConnection=new SQLConnection();
sqlCon.open(File.applicationDirectory.resolvePath('data.sqlite'));
BaseObject.defaultConnection=sqlCon;

We need clients!

Now, let’s con­sider a data­base with a “client” table (pretty cre­ative, right?), those clients have a name, a URL and a gen­der (male/female), and of course, an ID. So we have a “client” table with 4 fields: id, name, url, male (which is a Boolean: true=male, false=female).

I have mapped a BaseOb­ject sub-class to this table (I will show you how in a few moments), so I can now cre­ate a new client in a pretty sim­ple fashion:

var myClient:Client=Client.create({name:'Joe', url:'http://www.yep.com', male:true});

Cool, I now have a Client instance I can work with and a line has been added in my “client” table. What if I wanted to change its name? Easy.

myClient.setName('Mark');

The “name” field has been updated. Let’s check that.

trace(myClient.getName());

Traces “Mark”? Good. OK, what about updat­ing the URL and name at the same time? Two queries? Nope!

myClient.update({name:'Jack', url:'http://www.yo.net'});

Now let’s grab a client via his ID.

var c1:Client=Client.getFromID(1);

Sim­ple. But what if I wanted to grab more than one client, fox exam­ple what if I wanted all male clients?

var males:Array=Client.getFromQuery('SELECT * FROM client WHERE male=@male', {'@male':true});

You can now loop through this array and inter­act with the Client instances it contains…

What’s the trick?

Noth­ing too hard to under­stand. Client is a sub-class of BaseOb­ject that sim­ply exposes some of its sta­tic and instance meth­ods (get­Fro­mID, get­From­Query, cre­ate, update and so on). It also casts BaseOb­ject return val­ues to the right type: Client. For exam­ple, this is how get­Fro­mID works:

public static function getFromID(id:uint):Client {
	return BaseObject._getFromID(getTableData(), id) as Client;
}

Now you might won­der what this get­Table­Data method is. Well it returns data about the table to which Client is bound to, like that:

public static function getTableData():TableData {
	if (!_tableData) _tableData=new TableData('client', Client, ['name', 'url', 'male']);
	return _tableData;
}

See? We define the table name (client), the cur­rent class (Client) and the fields we need to han­dle (name, url and male)… That’s it.

Our clients bought cars…

That’s right, so we should have a “car” table, right? With id, name, brand, cli­en­tID and pur­chase­Date we should be good. Its Table­Data would look like that:

 new TableData('car', Car, ['name', 'brand', 'clientID', 'purchaseDate']);

Note: I didn’t list id as BaseOb­ject sup­poses there’s always an id field.

OK, so our clients bought cars, and we’d like to know who bought what. Let’s write a method in Client to do that:

public function getCars():Array {
	return Car.getFromQuery('SELECT * FROM car WHERE clientID=@id', {'@id':id}));
}

That works, cool. But we can do bet­ter, we could cache this Array so we don’t query every time we need it ; there’s a tech­nique for that:

public function getCars(force:Boolean=false):Array {
	return (hasCache('cars') && !force) ?
		getCache('cars') :
		setCache('cars', Car.getFromQuery('SELECT * FROM car WHERE clientID=@id', {'@id':id}));
}

Done. Opti­mized.

We can now see which cars our first client bought:

var c1cars:Array=c1.getCars();

We could also imple­ment the oppo­site: retriev­ing a Client form a Car instance:

public function getClient():Client {
	return Client.getFromID(this.getClientID());
}

Notice how I used get­Cli­en­tID? This refers to the table’s cli­en­tID field and returns it.

That’s a wrap.

Well that’s pretty much it, I think I cov­ered the basics!

If you want to play with it, get the class/demo project and tell me what you think. I’ve bun­dled Client, Car and some exam­ple uses…