Paul Robertson's words, punctuated

Thoughts on development, user-centered design, code, etc. by Paul Robertson

AIR SQLite utility library (SQL runner, database copy)

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

  1. Download/source
  2. Documentation
  3. Examples
  4. Project history
  5. Features
  6. 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

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

// 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):

1
2
3
4
5
6

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

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.<SQLResult>):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):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

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.):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

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.)