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
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
|
Corresponding SQL statement (LoadEmployee.sql):
1 2 3 4 5 6 |
|
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 |
|
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 theempId
method parameter by creating a generic object:{ employeeId:empId }
that’s passed as theparameters
argument of theexecute()
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 |
|
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 |
|
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 |
|
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 |
|
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.
- For connection errors, the
- 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 theSQLRunner.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.)