Demo: Creating and Using the SQLite DB in AIR · May 28, 08:37 PM
I have been dabbling more and more with AIR these days, and I am getting pretty excited about building desktop applications that can maintain their own databases and sync up with their online counterparts when a user is connected to the web. I spent quite a bit of time looking into examples of using the built in databases in AIR applications the other day, and couldn’t really find an easy to follow tutorial with source files that would compile, so hopefully I will be able to fill that niche!
I have put together a demo that will compile with Flash CS3’s built in AIR support. Its is simple little app that will log SQLite queries to an output window. Here is a screenshot:
You can download the .air app file here: SQLiteDemo.air. (The source is at the bottom of the post).
I have been trying to create a FlexBuilder Actionscript project that will compile to AIR correctly, but have had troubles with that. I can import the air swcs, getting me code hinting and everything, but anytime I envoke an AIR class at runtime i get the dreaded VerifyError: Error #1014 error… anyway, I have settled for writing the code in FlexBuilder and then just compiling with Flash CS3.
Back on subject… since AIR applications are self contained, and its native database type (SQLite) is meant to be a self-contained flat file database, we will need to actually create the database with our AS3 code. It’s a slight paradigm shift from my typical development process, where I create my DB before even opening Flash/Flex. In AIR you have to take care of creating your DB at runtime (since we are making a standalone desktop application afterall). Since we do not want to overwrite our database every-time our AIR application runs, we will do a simple check to see if our database exists before we execute any SQLStatements. If our database does not exist, we will create it, and if it already exists we will just begin interacting with it.
Here is a snippet of code from my DBConnection class:
{
addEventListener(SQLEvent.SCHEMA, _handleDBSchemaInit);
_dbFile = File.applicationStorageDirectory.resolvePath("sqlitedemo.db");
open( _dbFile );
// test to see if db exists... if not create it
try { loadSchema() } catch( err:SQLError ) { _createDB(); }
}
My DBConnection class is a static class that extends SQLConnection (the AIR class used to establish communication from your project to its database). I like extending the SQLConnection class with a static singleton class because it lets me use the same connnection to my DB from any class within my project. This really can help to maintain the accurate state of your DB and is just a lot easier to use. As an example, i just have to import my DBConnection class into any class like so:
// import the net package that contains my DBConnection Class
import com.sqlitedemo.net.*;
and then i can use it:
// call any public static method of the DBConnection class like so:
DBConnection.insertRecord( theRecord );
Ok, enough about the joy of Singletons :) … back to SQLite in AIR
Before playing around with AIR I had never used SQLite before, but I have had a lot of experience with MySQL. As you might be able to imagine, SQLite is very similar to MySQL (or any other SQL-based language). For example, in my little demo I needed to create a table called users. I did it like so:
// structure the query
var sqlString:String = "CREATE TABLE `users` (id INTEGER PRIMARY KEY NOT NULL, created DATE NOT NULL, modified DATE NOT NULL, active BOOLEAN NOT NULL, username CHAR(100) UNIQUE NOT NULL, name CHAR(256), email CHAR(256) UNIQUE NOT NULL, password CHAR(256) NOT NULL);";
// create a new SQLStatement object and execute the query
var query:SQLStatement = new SQLStatement
query.sqlConnection = instance;
query.text = sqlString;
query.execute();
If we just focus on the query string itself, you may noticed it looks like a barebone version of regular SQL. The query to creating the same table in MySQL would look like this:
(
`id` int(4) unsigned NOT NULL auto_increment,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`active` binary(1) NOT NULL,
`username` varchar(100) collate utf8_bin NOT NULL,
`name` varchar(255) collate utf8_bin NOT NULL,
`email` varchar(255) collate utf8_bin NOT NULL,
`password` varchar(255) collate utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin AUTO_INCREMENT = 0 ;
Not so different afterall… SQLite supports a leaner slew of data and column affinities ( think data types ) than MySQL, but they do support some additional additional AIR/Flash specific ones: XML, XMList, Object, and Date ( Date not new to SQLite, but SQLite for AIR uses Flash Date object, not SQL DATETIMES).
Ok, so now we have taken care of creating the DB if it does not exist. Nice job! Next up is interacting with the DB. I am only going to cover the SQLite specific parts of my demo. I’ll will leave the business logic and interaction of my demo app out of the discussion, but feel free to grab the source code (see bottom of post) and check out all the code if you are so inclined.
Inserting Records
Since all interactions with the DB will be passed through an SQLStatement we will create our INSERT statement much like we created the CREATE statement. Take a look at the insertion code block:
// create a new date to be used in our statement
var now:Date = new Date()
// create our statement... notice the @s... dont execute quite yet...
var ins_query:SQLStatement = new SQLStatement();
ins_query.sqlConnection = instance;
ins_query.text = 'INSERT INTO users (created, modified, active, username, name, email, password) VALUES (@bdate, @bdate, 1, @username, @name, @email, @password)';
ins_query.parameters['@bdate'] = new Date();
ins_query.parameters['@username'] = recordData.username;
ins_query.parameters['@name'] = recordData.name;
ins_query.parameters['@email'] = recordData.email;
ins_query.parameters['@password'] = recordData.password;
So we create a new SQLStatement object and pass it a reference to our DBConnection instance (remember our DBConnection class just subclasses SQLConnection). Next, we write our query string, and assign it to our statement’s .text property. The structure of the query string is very similar to MySQL. We start with the INSERT INTO keywords then supply the table name we are going to be inserting into. Then we supply a comma delineated list of the column names we want to edit wrapped in parenthesis followed by the VALUES keyword and another comma delineated list of the corresponding values to our column list. Its in the VALUES list we see something unique to AIR’s SQLite support: inline parameters.
Notice how each '@varname' in the query string corresponds to ins_query.parameters['@varname'] statement. Its pretty simple… we are just telling Flash that our query needs to reference a variable, not a hardcoded value.
Now, since we are dealing with a UNIQUE fields in our table, and we are also letting our user delete our database, there are a couple different senarios where out insertion could fail: 1) either a user tries to insert a record with a username or email address that is already in the DB or 2) the DB was dropped and does not exist when the insertion is being made. Here comes our error checking:
// create an empty string to hold our potential error details
var errorMessage:String = "";
// try to execute the query, if it fails we will catch it
try
{
// attempt initial execution
ins_query.execute()
}
catch (err:SQLError)
{
switch (err.details)
{
// if the email is already used
case 'column email is not unique':
errorMessage = 'the supplied email address is already being used'
break;
// if the username is already used
case 'column username is not unique':
errorMessage = 'the supplied username is already being used'
break;
// if the DB does not exist
case 'no such table: users':
_createDB();
ins_query.execute();
break;
// uhhhh... damn it!
default:
trace('an uncaught insertion error occured: ' + err.details);
}
}
Now, recording the error message is not necessary, but I am using it for the Output window of this demo app, but keeping track of the whether or not the query executing is a good idea. In this case, if the query failed I will send a custom DBErrorEvent, and if it succeeded, I will use the lastInsertRowID property of our DBConnection ( remember its a SQLConnection subclass ) to pull the entire dataset for the record we just inserted:
// if the insert did not fail...
if( errorMessage == "" )
{
// grab the record we just inserted
var sel_query:SQLStatement = new SQLStatement();
sel_query.sqlConnection = instance;
sel_query.text = "SELECT * FROM users WHERE id = " + instance.lastInsertRowID;
sel_query.execute();
// broadcast our custom INSERTED event
var sdbe:DBEvent = new DBEvent( DBEvent.INSERTED, sel_query.getResult(), sel_query.text );
StaticEventDispatcher.dispatchEvent(sdbe)
}
else
{
// if the insert failed... create and dispatch a custom DBErrorEvent
var dbee:DBErrorEvent = new DBErrorEvent( DBErrorEvent.INSERT_FAILED, errorMessage);
StaticEventDispatcher.dispatchEvent( dbee );
}
Dropping The Database
Now that we are familiar with the basic SQLStatement syntax dropping the DB is a snap:
{
// drop the DB
var query:SQLStatement = new SQLStatement()
query.sqlConnection = instance;
query.text = 'DROP TABLE users';
query.execute();
// broadcast a custom DBEvent
var dbe:DBEvent = new DBEvent( DBEvent.DROPPED, query.getResult(), query.text );
StaticEventDispatcher.dispatchEvent( dbe );
}
Ok, my hands hurt from typing and the Piston’s game is about to come on, errrrrrrr, just started on, errrrrr, is at halftime, so I will end things here for now. We haven’t gotten into DELETE or UPDATE but since we have explored the basic syntax of using SQLite in AIR, those topics should be less intimidating! Please have a look at Adobe’s official docs on SQL support in AIR
Here are the SQLite Demo source files
☆ Jonathan Greene