When we spec'd out the Web2project Desktop Client, the Vision was to build a simple client that would retrieve a user's list of tasks, allow them to log time against any or all of them, and update the server with the time and comments. While the vast majority of the time the user would be online, there are times – either by choice or necessity – when the user may not be able to connect to their server. Therefore, we needed a simple local datastore.
While I could attempt to have mysql installed locally, that's a huge dependency that will scare away non-developers. Alternatively, some file-based XML monstrousity might work but then I'd have the fun of dealing with XML. Without a doubt, SQLite was the only logical choice, especially once I found out Flex has support built in.
If you've only worked with a “normal” database engine like MySQL or SQL Server, then you'll quickly realize that SQLite is whole other beast..
First of all, there's no separate engine to install. You don't download an executable and set it up within the operating system, instead the application that uses the database includes the engine itself. In this case, an AIR application will package the SQLite engine automagically with a simple import:
import flash.data.*
That one line gives you access to all of the SQLite functionality and features immediately. There's nothing else required except to use them.
Next, there's no “real” database to install. Yes, there is definitely a database and you have to connect to it, but the setup step is not separate from running the application itself. Further, we don't have to create users and permissions to go with it. In our case, we have some simple steps:
var dbFile:File = File.applicationStorageDirectory.resolvePath("web2project.db");
var sqlConn:SQLConnection = new SQLConnection();
var statement:SQLStatement = new SQLStatement();
private function init():void
{
sqlConn.open(dbFile);
createDatabase();
}
private function createDatabase():void
{
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = sqlConn;
statement.text = "CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY AUTOINCREMENT, task_id INTEGER, task_module TEXT, task_name TEXT, task_desc TEXT, task_start INTEGER, task_end INTEGER)";
statement.execute();
}
This takes care of both initializing the database and creating the core table of the desktop client. There's really nothing else involved. Of course, if you're concerned about security, you can have Flex encrypt/decrypt the database specifically for your application.
Next, while you can interact with SQLite using string concatenation or prepared statements, Flex favors prepared statements. If you come from the PDO school of thought, this is easy to get a handle on. If not, you should get familiar with them. Prepared statements make the driver handle all the escaping so SQL Injection becomes a thing of the past. In our case, after we download and process a list of tasks from web2project, we want to store or update them as appropriate:
private function storeEvent(uid:String, summary:String, startDate:Date, endDate:Date, description:String):void {
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = sqlConn;
var module:String = uid.substring(0, uid.search('_'));
var id:int = parseInt(uid.substring(uid.search('_') + 1));
statement.text = "SELECT * FROM tasks WHERE task_id = :id AND task_module = :module";
statement.parameters[":id"] = id;
statement.parameters[":module"] = module;
statement.execute();
statement.clearParameters();
var result:Array = statement.getResult().data;
if (result == null) {
statement.text = "INSERT INTO tasks (task_id, task_module, task_name, task_desc, task_start, task_end) VALUES (:id, :module, :task_name, :task_desc, :task_start, :task_end)";
statement.parameters[":id"] = id;
statement.parameters[":module"] = module;
statement.parameters[":task_name"] = summary;
statement.parameters[":task_desc"] = description;
statement.parameters[":task_start"] = startDate.valueOf();
statement.parameters[":task_end"] = endDate.valueOf();
statement.execute();
} else {
//TODO: at some point, we should update
}
}
Finally, we can interact with SQLite data with the standard SQL we know and.. love. For testing purposes, I've added a simple Flush function that should look quite familiar:
protected function button1_clickHandler(event:MouseEvent):void
{
var statement:SQLStatement = new SQLStatement();
statement.sqlConnection = sqlConn;
statement.text = "DELETE FROM tasks";
statement.execute();
gridTasks.dataProvider.removeAll();
}
Not surprisingly, this deletes everything from the tasks table and clears our datagrid. On the display side of things, we have:
sqlStatement.sqlConnection = sqlConn;
sqlStatement.text = "SELECT * FROM tasks";
sqlStatement.execute();
var result:Array = sqlStatement.getResult().data;
//TODO: provide a default sort... by end date?
gridTasks.dataProvider = result;
All this does is retrieve the list of tasks and assigns it to a datagrid but the SQL is key. It's what we already know and write all the time. Even better, if we combine this with my previous research in detecting connectivity, the application can know whether to load the task list from the local SQLite or connect and download the new list.
Overall, the most complicated part of getting started with SQLite and AIR was not getting them to play nice together… but designing a proper database schema. I made a point of spending so much time and thought on that portion in order to get it right the first time. While there are methods and recommendations on how to update an existing SQLite database, detecting versions and upgrading accordingly is a problem that I don't want to deal with yet. Hopefully, I can save that for version 1.1..
In case you're interested in the web2project AIR Desktop Client, you can follow development on GitHub. It is nowhere near production-quality yet.
Disclosure: Through Blue Parabola, we're working with Adobe and Flex in a number of things. Regardless, I choose to explore Flex for the first time long before that.