I was talking about it, here it is!
This class will come in handy every time you work with synchronous SQLite databases in an AIR project. It’s a real-world implementation of the Dynam.ize utility I presented earlier… I will show you an example of how to work with it, with simple sub-classes. Let’s go!
First things first.
I need to open my database and provide that connection to BaseObect:
var sqlCon:SQLConnection=new SQLConnection();
sqlCon.open(File.applicationDirectory.resolvePath('data.sqlite'));
BaseObject.defaultConnection=sqlCon;
We need clients!
Now, let’s consider a database with a “client” table (pretty creative, right?), those clients have a name, a URL and a gender (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 BaseObject sub-class to this table (I will show you how in a few moments), so I can now create a new client in a pretty simple 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 updating 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);
Simple. But what if I wanted to grab more than one client, fox example 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 interact with the Client instances it contains…
What’s the trick?
Nothing too hard to understand. Client is a sub-class of BaseObject that simply exposes some of its static and instance methods (getFromID, getFromQuery, create, update and so on). It also casts BaseObject return values to the right type: Client. For example, this is how getFromID works:
public static function getFromID(id:uint):Client {
return BaseObject._getFromID(getTableData(), id) as Client;
}
Now you might wonder what this getTableData 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 current class (Client) and the fields we need to handle (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, clientID and purchaseDate we should be good. Its TableData would look like that:
new TableData('car', Car, ['name', 'brand', 'clientID', 'purchaseDate']);
Note: I didn’t list id as BaseObject supposes 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 better, we could cache this Array so we don’t query every time we need it ; there’s a technique 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. Optimized.
We can now see which cars our first client bought:
var c1cars:Array=c1.getCars();
We could also implement the opposite: retrieving a Client form a Car instance:
public function getClient():Client {
return Client.getFromID(this.getClientID());
}
Notice how I used getClientID? This refers to the table’s clientID field and returns it.
That’s a wrap.
Well that’s pretty much it, I think I covered the basics!
If you want to play with it, get the class/demo project and tell me what you think. I’ve bundled Client, Car and some example uses…
