AIR SQLite utility library (SQL runner, database copy)

A set of libraries for optimizing the process of working with SQLite databases in Adobe AIR.

Download/source
Documentation
Examples
Project history
Features
Design philosophy

Download/Source

The AIR SQLite utility library source code is hosted on Github, released under the MIT license.

The current version is 0.1.2 beta. Download|Changes

Documentation

air-sqlite project language reference documentation (including examples)

Examples

Cut to the chase (short examples)
Robotlegs usage example
More details (Longer examples with explanations)

Short examples

The minimum to get going.

Example 1: Standard (pooled) SQLRunner running a SQL SELECT statement

A common use case — running a single SELECT statement to load one row from a table, passing the primary key as a parameter:

ActionScript code:

// setup code:
// define database file location
var dbFile:File = File.applicationStorageDirectory.resolvePath("myDatabase.db");
// create the SQLRunner
var sqlRunner:SQLRunner = new SQLRunner(dbFile);

// ...

// run the statement, passing in one parameter (":employeeId" in the SQL)
// the statement returns an Employee object as defined in the 4th parameter
sqlRunner.execute(LOAD_EMPLOYEE_SQL, {employeeId:102}, resultHandler, Employee);

private function resultHandler(result:SQLResult):void
{
    var employee:Employee = result.data[0];
    // do something with the employee data
}

// constant for actual SQL statement text
[Embed(source="sql/LoadEmployee.sql", mimeType="application/octet-stream")]
private static const LoadEmployeeStatementText:Class;
private static const LOAD_EMPLOYEE_SQL:String = new LoadEmployeeStatementText();

Corresponding SQL statement (LoadEmployee.sql):

SELECT firstName,
    lastName,
    email,
    phone
FROM main.employees
WHERE employeeId = :employeeId

Robotlegs example

If you’re wondering how to use the library in a Robotlegs application, look no further. In the Robotlegs “AddressBook” example that has been used in Robotlegs presentations, this library is used in a Robotlegs service layer class to provide database access. The complete app was built using a dummy XML service, then converted to use the SQL service by changing only a couple of lines of code (plus adding the new service class)—demonstrating the benefit of clean separation that Robotlegs provides.

Longer examples with explanations

These examples are taken from real applications where I really used this library. (In fact, one reason I feel that this design approach works well with my preferred workflow is because I developed and adapted the library as I was building real-world apps. For example, I didn’t add the “batch” functionality until I needed it, and I didn’t build the “unpooled” version of the SQLRunner until I needed it for an app.)

Standard SQLRunner

This is the “normal” version of the SQLRunner. It uses connection pooling and executes multiple SQL statements simultaneously, so it’s designed for use in desktop apps on fairly modern hardware that can handle multiple threads (it doesn’t have to be “multicore”). I developed and used this version on a laptop from mid-2006 with a 2.33 GHz Intel Centrino Duo processor, which is reasonably fast but no faster than today’s consumer-grade systems.

One technique I’ve used is to create a simple app-specific “service” class that wraps the SQLRunner. Application code then calls that service to perform various operations. This code is an example of the service class from one of my apps:

package demo.data
{
    import com.probertson.data.QueuedStatement;
    import com.probertson.data.SQLRunner;
    import flash.filesystem.File;

    public class SQLService
    {
        // ------- Constants -------

        private static const dbFile:File = File.applicationStorageDirectory.resolvePath("MyDatabase.db");

        // ------- Singleton implementation -------

        private static var _instance:SQLService;
        public static function getInstance():SQLService
        {
            if (_instance == null)
            {
                _instance = new SQLService(dbFile);
            }

            return _instance;
        }

        // ------- Constructor -------

        public function SQLService(databaseFile:File)
        {
            _sqlRunner = new SQLRunner(databaseFile);
        }

        // ------- Instance vars -------

        private var _sqlRunner:SQLRunner;

        // ------- Public methods -------

        public function close(resultHandler:Function):void
        {
            _sqlRunner.close(resultHandler);
        }

        public function execute(sql:String, parameters:Object, handler:Function, itemClass:Class=null):void
        {
            _sqlRunner.execute(sql, parameters, handler, itemClass);
        }

        public function executeModify(statementBatch:Vector.<QueuedStatement>, resultHandler:Function, errorHandler:Function, progressHandler:Function):void
        {
            _sqlRunner.executeModify(statementBatch, resultHandler, errorHandler, progressHandler);
        }
    }
}

Here’s an example of some code that called the service for a typical operation (e.g. a SQL SELECT command). A few notes about this code:

I use the [Embed] metadata to compile in the SQL statement text. I prefer that approach because it allows me to write my SQL code as a separate file (authoring and testing using Run!). I can modify the SQL code easily without needing to copy/paste/reformat it as a String in ActionScript code.
In this example, the SQL expects a single statement parameter named :employeeId. That parameter is given the value of the empId method parameter by creating a generic object: { employeeId:empId } that’s passed as the parameters argument of the execute() method.
The SQL returns a single row corresponding to a single employee. Because the EmployeeData class is specified as the final argument of the execute() call, the result data (result.data) is automatically created as an EmployeeData instance.

import flash.data.SQLResult;
import demo.data.SQLService;

[Embed(source="sql/load/EmployeeDataLoader.sql", mimeType="application/octet-stream")]
private static const StatementText:Class;
private static const SQL:String = new StatementText();

private function loadEmployeeData(empId:String):void
{
    SQLService.getInstance().execute(SQL, { employeeId:empId }, loadEmployeeData_result, EmployeeData);
}

private function loadEmployeeData_result(result:SQLResult):void
{
    if (result.data == null)
    {
        _resultData = null;
        dispatchEvent(new ElementLoadEvent(ElementLoadEvent.NO_ROWS_RETURNED));
    }
    else
    {
        _tempResult = result.data[0];

        // load child elements
        loadAvailability();
        loadRelatedLinks();
    }
}

Here’s an example of code that calls the executeModify() method. This supports two more “advanced” use cases:

Executing a “batch” of SQL operations as a transaction
Executing INSERT/UPDATE/DELETE (data change) operations. The SQLRunner waits until all pending statements have run before executing those operations, and runs them linearly using a single connection, so that the order of execution is controlled and there are no race conditions or data integrity problems.

package demo.data
{
    import com.probertson.data.QueuedStatement;
    import flash.data.SQLResult;
    import flash.errors.SQLError;

    public class BuildDatabase
    {
        // ------- Member vars -------

        private var _resultHandler:Function;
        private var _errorHandler:Function;
        private var _progressHandler:Function;

        // ------- Public methods -------

        public function execute(resultHandler:Function, errorHandler:Function, progressHandler:Function):void
        {
            _resultHandler = resultHandler;
            _errorHandler = errorHandler;
            _progressHandler = progressHandler;

            createStructure();
        }

        // ------- Private methods -------

        private function createStructure():void
        {
            var statements:Vector.<QueuedStatement> = new Vector.<QueuedStatement>();

            // CREATE TABLE
            statements[statements.length] = new QueuedStatement(CREATE_TBL_EMPLOYEES_SQL);
            statements[statements.length] = new QueuedStatement(CREATE_TBL_DEPARTMENTS_SQL);
            statements[statements.length] = new QueuedStatement(CREATE_TBL_LOCATIONS_SQL);

            // ...

            // INSERT default data
            statements[statements.length] = new QueuedStatement(INSERT_DATA_SQL);			

            // execute
            SQLService.getInstance().executeModify(statements, executeBatch_complete, executeBatch_error, executeBatch_progress);
        }

        // ------- Event handling -------

        private function executeBatch_progress(completedCount:uint, totalCount:uint):void
        {
            _progressHandler(completedCount, totalCount);
        }

        private function executeBatch_complete(results:Vector.):void
        {
            _resultHandler();
        }

        private function executeBatch_error(error:SQLError):void
        {
            // handle the error as desired or bubble

            _errorHandler(error);
        }

        // ------- SQL statements -------

        // --- CREATE TABLE ---

        [Embed(source="sql/create/CreateTable_employees.sql", mimeType="application/octet-stream")]
        private const CreateEmployeesStatementText:Class;
        private const CREATE_TBL_EMPLOYEES_SQL:String = new CreateEmployeesStatementText();

        [Embed(source="sql/create/CreateTable_departments.sql", mimeType="application/octet-stream")]
        private const CreateDepartmentsStatementText:Class;
        private const CREATE_TBL_DEPARTMENTS_SQL:String = new CreateDepartmentsStatementText();

        [Embed(source="sql/create/CreateTable_locations.sql", mimeType="application/octet-stream")]
        private const CreateLocationsStatementText:Class;
        private const CREATE_TBL_LOCATIONS_SQL:String = new CreateLocationsStatementText();

        // --- INSERT default values ---

        [Embed(source="sql/create/Insert_data.sql", mimeType="application/octet-stream")]
        private const InsertDataStatementText:Class;
        private const INSERT_DATA_SQL:String = new InsertDataStatementText();
    }
}

SQLRunner with only one connection

This is the “lite” version of the SQLRunner. It only uses one SQLConnection object, so there isn’t any connection pooling. (It also avoids the use of Dictionary objects that are used in the standard version.) It still has the benefit of caching statements and ease-of-use, but definitely runs leaner. I developed this version for use in one of the Flash-based iPhone apps that was created for Adobe MAX 2009. It worked great on the iPhone 3G/iPod Touch 2nd generation — which is obviously a lower-end hardware target compared to a desktop computer.

Here is the SQLService class I use with the unpooled SQLRunner (note that it’s nearly identical to the one described above):

package demo.data
{
    import com.probertson.data.Responder;
    import com.probertson.data.SQLRunnerUnpooled;
    import flash.filesystem.File;

    public class SQLService
    {
        // ------- Constants -------

        private static const dbFileName:String = "MyDatabase.db";
        private static const dbFile:File = File.applicationStorageDirectory.resolvePath(dbFileName);

        // ------- Singleton implementation -------

        private static var _instance:SQLService;
        public static function getInstance():SQLService
        {
            if (_instance == null)
            {
                _instance = new SQLService(dbFile);
            }

            return _instance;
        }

        // ------- Constructor -------

        public function SQLService(databaseFile:File)
        {
            _sqlRunner = new SQLRunnerUnpooled(databaseFile);
        }

        // ------- Instance vars -------

        private var _sqlRunner:SQLRunnerUnpooled;

        // ------- Public methods -------

        public function execute(sql:String, parameters:Object, responder:Responder, itemClass:Class=null):void
        {
            _sqlRunner.execute(sql, parameters, responder, itemClass);
        }
    }
}

Here’s an example of some code that calls the SQLService to execute a SQL query. (Again, it’s very similar to the previous example, so see that example’s explanation for more details.):

import com.probertson.data.Responder;

import demo.data.SQLService;
import demo.vo.CategoryData;

// ------- SQL statements -------

// --- Load Categories ---
[Embed(source="sql/CategoryLoader_categories.sql", mimeType="application/octet-stream")]
private const LoadCategoriesStatementText:Class;
private const LOAD_CATEGORIES_SQL:String = new LoadCategoriesStatementText();

// --- Load subcategories ---
[Embed(source="sql/CategoryLoader_subcategories.sql", mimeType="application/octet-stream")]
private const LoadSubcategoriesStatementText:Class;
private const LOAD_SUBCATEGORIES_SQL:String = new LoadSubcategoriesStatementText();

// ------- Constructor -------

public function CategoryLoader()
{
    _sqlService = SQLService.getInstance();
}

// ------- Private vars -------

private var _sqlService:SQLService;

// ------- Public methods -------

public function loadCategories():void
{
    _sqlService.execute(LOAD_CATEGORIES_SQL, null, new Responder(categories_complete, categories_error), CategoryData);
}

public function loadSubcategories(categoryId:int):void
{
    var params:Object = new Object();
    params.parentId = categoryId;

    _sqlService.execute(LOAD_SUBCATEGORIES_SQL, params, new Responder(subcategories_complete, subcategories_error), CategoryData);
}

// ------- Event handling -------

private function categories_complete(result:SQLResult):void
{
    // do something with the result data
}

private function categories_error(error:SQLError):void
{
    // handle the error
}

private function subcategories_complete(result:SQLResult):void
{
    // do something with the result data
}

private function subcategories_error(error:SQLError):void
{
    // handle the error
}

Project history

0.1.2 (2010-08-18):

Credits: I owe a big thank you to Nicholas Shipes of Urban 10 Interactive for his help tracking down some of the subtle bugs that are fixed in this release.
Added new error handling hooks for detecting errors that happen when the library is attempting to create connections, errors when the library is closing (after SQLRunner.close() is called), and errors that occur with SELECT statements run using SQLRunner.execute().

The new apis are:
- For connection errors, the SQLRunner.connectionErrorHandler property: Function(SQLError)
- For closing errors, the new optional 2nd parameter to SQLRunner.close(): Function(SQLError)
- For SQLRunner.execute() errors, the new optional 5th parameter to SQLRunner.execute(): Function(SQLError)
To be completely honest, I’m not perfectly happy with how these apis are structured, and I’m starting to think about other ways to organize things. I wanted to get these in without breaking backwards-compatibility for now, but I’m definitely looking at other ways to structure the api for a future major version release.
Fixed several subtle bugs related to uncaught errors and error handling. Two specific issues include 1) when calling SQLRunner.close() the connections weren’t all closing, and 2) an error sometimes happened (resulting in the library becoming permanently stuck) when calling SQLRunner.execute() while a executeModify() batch is currently running.

0.1.1 (2010-01-02):

Fixed issue #1: Calling SQLRunner.executeModify() with same statement multiple times in a single batch throws error. This bug caused the following error message: “Error #3110: Operation cannot be performed while SQLStatement.executing is true.”
New feature: added ability to get SQLResult objects for each statement in a batch. Note: this is a breaking change!. Going forward, the resultHandler function that’s passed to the SQLRunner.executeModify() method must specify a single parameter so that it has the following signature:
```
private function resultHandler(results:Vector.):void
```
Unfortunately, the problem can’t be caught at compile time—you will get a run time error about argument count mismatch.

0.1 (2010-02-23): Initial release

Features

The code currently provides two main features:

SQLRunner simplifies the process of executing database statements. You provide the SQL to run, any desired parameters, and the event handler. The library does the work of providing a pool of database connections, so you don’t have to wait if one or some of the connections are currently busy and you can take advantage of parallel processing using multiple operating system threads. It also tracks compiled statements so that statements are compiled for better performance when they’re run more than once.
Database copy (coming soon) allows you to create a deep copy of a database, including all database structure (tables, views, triggers, indexes) and optionally table data. All you do is provide the source and destination file locations. You can use this feature to create an encrypted copy of an unencrypted database, or create an unencrypted copy of an encrypted database.

Design philosophy

As the blurb above says, the goal of this project is to “optimize the developer experience.” That’s the underlying philosophy behind the design of this library.

A key part of optimization is to optimize your (the developer’s) workflow. These APIs are designed to make it easy to integrate SQLite functionality into an AIR application with no boilerplate code necessary. You write your SQL code and your event handlers…and that’s all.

Simplicity shouldn’t require you to sacrifice performance. The code is written to follow best practices and guidance for database performance optimization. You don’t have to worry about designing your data access code for performance, because it’s already done for you.

Simplicity also shouldn’t require you to sacrifice control and flexibility. You write your own SQL code so you can load only the data you need, when you need it, and optimize the design of your database and queries based on your knowledge of your application. (This is in contrast to systems such as ORM which provide a nice benefit that you don’t have to write SQL, with the downside that you have to limit yourself to basic object structures and generic data loading — usually loading more data than you really need — rather than being able to tune the database and queries to your application’s needs.)

Comments/trackbacks are closed for this post.

16 Comments so far

December 23rd, 2009 at 8:10 am
Mario Rodrigues is reported to have said:

Hi, thank you for this library. Do you have any sample code or pointers for getting started?
February 24th, 2010 at 7:57 am
Mario Rodrigues is reported to have said:

Hi, I get the following error when trying to use your library for both simple SQL statements and batch statements.

Error: Error #3110: Operation cannot be performed while SQLStatement.executing is true.
at Error$/throwError()
at flash.data::SQLStatement/checkReady()
at flash.data::SQLStatement/execute()
at com.probertson.data.sqlRunnerClasses::PendingBatch/executeStatements()[C:\git\air-sqlite\src\com\probertson\data\sqlRunnerClasses\PendingBatch.as:112]
at com.probertson.data.sqlRunnerClasses::PendingBatch/conn_begin()[C:\git\air-sqlite\src\com\probertson\data\sqlRunnerClasses\PendingBatch.as:95]

Any ideas on what I might be doing wrong?

[This should be fixed in version 0.1.1]
February 26th, 2010 at 9:28 am
Don Mitchinson is reported to have said:

Hi Matt:

I don’t know if you found the solution to your problemm or not yet, but I encountered similar errors when I was opening my db asynchronlously.

I had to ensure SQLStatement.executing was false before executing the next statement.

Don

[The problem was a bug in the library, which is fixed in version 0.1.1]
March 22nd, 2010 at 4:29 pm
Nicholas Shipes is reported to have said:

I’m having the same problem with SQLStatement.executing returning true when running batch statements. Don, you stated you had to ensure .executing was false before executing the next statement, but how did you accomplish this? Did you modify the PendingBatch class for this?

[This should be fixed in version 0.1.1]
May 14th, 2010 at 4:37 am
Adobe Flash Platform * Application design for database performance is reported to have said:

[…] execute multiple statements simultaneously. For more information and to download the library, visit http://probertson.com/projects/air-sqlite/. […]
July 30th, 2010 at 12:58 pm
AIR for Android resource roundup | simon-townsend.com is reported to have said:

[…] AIR SQLite Utility […]
October 19th, 2010 at 4:39 pm
Jeffery Fernandez is reported to have said:

Hi Robert,

Why do I keep getting the following error:

SQLError: ‘Error #3125: Unable to open the database file.’, details:’Connection closed.’, operation:’open’, detailID:’1001’

I have followed the examples you have shown here but I get the above error. I understand what the error means ( that the database path does not exist ) but am I missing something else. Do I have to make an SQLConnection before creating the SQLRunner object?
November 10th, 2010 at 9:44 am
Lukasz 'Severiaan' Grela is reported to have said:

@Jeffrey Fernandez
I could guess that you have used that on android device. I have the same error when I am trying to save to the sdcard so basically any path for file:///mnt/sdcard from the list below.

File.applicationDirectory:app:/
File.applicationStorageDirectory:app-storage:/
File.desktopDirectory:file:///mnt/sdcard
File.documentsDirectory:file:///mnt/sdcard
File.userDirectory:file:///mnt/sdcard
April 6th, 2011 at 7:08 pm
Damon is reported to have said:

I’m confused a bit between execute vs executeModify. I think i get that execute does a single statement and can assign the results as a specific class, and execute modify allows you to batch statements. But what if you’d like to batch statements and have the results be typed as a class? Is that simply not possible because you can’t have an array of specific classes the same way you can have an array of objects?

the reason i’m curious is I have a large statement batch to pull a variety of objects from different tables from a day, and I would like to be able to have them come out as their proper class types so that i can update them right in a datagrid.
April 6th, 2011 at 8:25 pm
Paul Robertson is reported to have said:

Hi Damon,

The difference between execute() and executeModify() really just comes down to the types of SQL commands each one can run — it doesn’t have anything to do with “batch” or “not a batch”.

I. For SELECT statements, use execute()

if you want to run several SELECT statements at the same time, just call execute() several times. It’s okay — in fact, the library is designed specifically around that use case.
The statements will run in parallel, up to the number of available database connections (specified in the SQLRunner constructor, default 5).
If you send more statements than there are connections available, the rest will just patiently wait and will run as soon as a connection becomes available.

II. For INSERT/UPDATE/DELETE statements, use executeModify()

if you want the statement to run in isolation, call executeModify() and just pass one statement (wrapped in a “batch”).
If you want to run several statements and have them linked together in a transaction (so that if one fails they are all reverted), call executeModify() one time with all the statements together in the batch.
There’s no point to having an itemClass for executeModify(), since you (presumably) aren’t going to send a SELECT statement in the middle of a transaction of INSERT/UPDATE/DELETE statements
If you want to for example perform an INSERT or UPDATE then run a SELECT statement immediately after, just call executeModify() then call execute(). The library takes care of keeping them in order so the INSERT/UPDATE will run first, the transaction will finish, and then your SELECT will run.

Sorry if this is not very clear. I’m going to try to rethink the api to figure out a way to make this make more sense (or else to not have the two methods, or something).
May 4th, 2011 at 10:50 am
rudi is reported to have said:

hello,
I’m still trying to create some sort of application “server” that is able to respond to multiple clients through your library on lan.
Now I’m a little panicked at the time that it does not respond well, let me explain: If you start to change simultaneously two modify queries I get from my “server”only the first response to the query made ‚Äã‚Äã… now I’d send you my job so you could see it if you want! unfortunately I do not have good depth of your work, and I’m not a great programmer, it may be that your library may stop when there’s two or more simultaneous requests for changes? or rather, it is possible that in the previous case, your utility takes into account only the first query (perhaps not consider … is lost … or anything else?).
if I understood, your library takes all queries that come in, put them in a queue, and executes them one after the other, keeping the query blockers, without skipping any request.
thanks for your kind reply.
August 10th, 2011 at 3:29 am
Tac is reported to have said:

Thanks, this looks like an interesting approach. I’m trying to integrate it with SurveyApe, but am such a newbie at Flash/Flex that I’m stuck on some of the basics. Can you post (or point me to) a working project, with the associated mxml and Employee.as class files?

Thx,

Tac
August 25th, 2011 at 7:12 pm
SQLiteNoob is reported to have said:

Hey, Amazing stuff! I’ve been learning a lot about SQLite and Flex from the posts you’ve done on this site and from Adobe.

Quick question - I created an SQLite db, and now I want to use your DBCopier.as class to encrypt it - but I am having a lot of difficulty: Flash Builder doesn’t recognize the “EncrypterProgressPhase” properties as well as the start() method! Could a lowly newb like myself humbly request a post of how to use this class?

Thanks for everything you have already contributed to the community!
October 12th, 2011 at 10:07 am
Paul is reported to have said:

Paul.

I am finding your code very helpful while developing an Android App.
At the moment I am using your SQLRunnerUnpooled class but I got to a moment in which I need to do an INSERT to the database. How can I do that, or should I change code to use the SQLRunner class instead.

Thx a lot
October 12th, 2011 at 10:16 am
Paul is reported to have said:

Hmmm. I guess I need to make the docs clearer on that point!

The SQLRunnerUnpooled.execute() method should work for any type of SQL statement, so you should be fine using it with an INSERT statement.

If it doesn’t work, let me know and I’ll fix it!
October 12th, 2011 at 2:42 pm
Paul is reported to have said:

Hey Paul, You are totally right as it is working fine. I was having some permission issue with db.
Your library is great by the way!

Cheers,
Paul