SQLite and AIR apps with Cairngorm
Having never done any SQLite stuff prior to the session survey, building it was a definite learning experience.
The hardest part of building with SQLite I think, is working with existing data. Writing data for later retrieval, cake. retrieving "offline" data, that was a bit more labor.
To start, populating the SQLite DB, isn't fun. I bought an off the shelve tool, SQLite Manager, that I like a whole lot. It supports SQLlite 2 and 3.
I'm not sure, but I'm guessing it's the nature of SQLite files, that once you've created the table and started working with it, making changes to structure, ain't possible.
I suppose since it's a flat file of some sort, making structure changes once populated, wouldn't be possible, but still a pisser. LOL.
Make sure when you create your "offline" tables, you know what you want.
For the survey app, we needed users to be able to still pick a track, and still pick a session from that track.
Tom and I ended up making our Cairngorm commands dual prupose. Getting on and off line data. Detecting whether the user is connected as a simple matter of implementing a URLMonitor to ping our site.
The command, looks for our global "am I online" var, and if we're not online, executes the SQLite functionality.
In order to make the commands as loosely coupled (for lack of a better term) to being on or off-line, we made the SQL result handler, call the command's normal result. From there the result() looks to see what type of result it's getting, since delegate sends a different type of result Object, than the SQL connection does. From there, it's a matter of formatting the data and storing it.
Our execute() looks like
if (model.networkConnection)
{
var delegate:SurveyDelegate = new SurveyDelegate(this);
delegate.getTracks();
} else {
var connection:SQLConnection = new SQLConnection();
var dbFileString : String = "360Survey.sqlite";
var dbFile = File.applicationDirectory.resolvePath(dbFileString);
connection.open(dbFile);
var sqlQuery:String = "SELECT trackID, trackTitle, trackShortName FROM tbl_tracks";
dbStatement.sqlConnection = connection;
dbStatement.text = sqlQuery;
dbStatement.addEventListener(SQLEvent.RESULT, onRetrieveDataResult);
dbStatement.addEventListener(SQLErrorEvent.ERROR, onDBError);
dbStatement.execute();
}
Then the result() does the "what did I just get" logic.
public function result(data:Object):void
{
trace('[GetTracksCommand] - Result');
var resultData:ArrayCollection = new ArrayCollection();
var tmpAC:ArrayCollection = new ArrayCollection();
if (data.hasOwnProperty("result")) {
resultData = data.result;
} else {
resultData = new ArrayCollection(data.data);
}
I was a bit worried that it would be harder to build a connection aware application, especially since Tom and I didn't really start writing code, until about 2 weeks out from Atlanta. There's still a few things to re-factor in the code, and get cleaned up, but I think when we put this app out into the Open Source domain, other conferences will be able to easily modify it to their needs, they'll need a CF backend, unless they want to replace the CFCs with .NET, PHP or Java whatever, but hey, it'll be a starting place.
