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. Examples
  3. Project history
  4. Features
  5. 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

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:

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

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:

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

0.1.1 (2010-01-02):

0.1 (2010-02-23): Initial release

Features

The code currently provides two main features:

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

You can leave a comment, or trackback from your own site.

6 Comments so far

  1. 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?

  2. 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]

  3. 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]

  4. 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]

  5. May 14th, 2010 at 4:37 am
    Adobe Flash Platform * Application design for database performance is reported to have said:

    Kramer auto Pingback[…] execute multiple statements simultaneously. For more information and to download the library, visit http://probertson.com/projects/air-sqlite/. […]

  6. July 30th, 2010 at 12:58 pm
    AIR for Android resource roundup | simon-townsend.com is reported to have said:

    […] AIR SQLite Utility […]

Add your comment





Comment notes

Please keep comments on topic. Comments that are inappropriate or offensive will be edited or removed.

Paragraphs and line breaks are automatically converted to HTML, and quotation marks are converted to “smart” quotes.

The following XHTML tags can be used: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> . All others will be removed.

Articles by Type

Articles by Topic

Random Reading

Currently...

Subscribe