Adobe AIR's SQLite support brings powerful relational database support to the desktop in a standardized cross-platform environment. Manipulating information in a SQLite database is made easy with the rich APIs provided by the AIR runtime.

One of the first steps in using a local database is deciding whether a connection will operate in a synchronous or asynchronous execution mode. An application using a synchronous connection waits for a database operation to complete before continuing, whereas an application using an asynchronous connection is informed when a database operation completes. This article explores what those two execution modes mean, discusses why to choose one over the other, and provides an example of how Allurent Desktop Connection leverages a SQLite database to produce the rich interactive shopping experience demoed at Adobe MAX 2007 in Chicago.

Creating a SQLConnection

In order to work with a local SQLite database, an application needs to establish a connection to it. The AIR runtime provides facilities for creating database connections that operate in either synchronous or asynchronous execution mode. After a connection to a database has been established in one of the two execution modes, all database operations on that connection will operate using that mode. It is impossible to mix the two execution modes on the same connection.

A synchronous connection operates like calling a method that performs some database operation and returns the result to the next line of code. An asynchronous connection uses user-specified functions that are called when the database operation completes and are either registered as event listeners or bundled in a Responder instance. Think of it like a function associated with the click operation on a Button that gets called when the user clicks the Button, but in this case the function gets called when the database operation finishes.

Based on the execution mode, one of the primary differences is the programming model used to interact with the API. As described above, a synchronous connection works like a call to a method while an asynchronous connection requires the use of functions that will be notified when the database operation completes. Before diving into a discussion about the other differences in execution modes, let's look at how to create a connection of each execution type, which will give a feel for the two programming models.

The following examples create an in-memory database. Examples of creating and connecting to a database on the file system are in the sample application described later and in the SQLConnection documentation.

To open a synchronous connection, use the open() method on an instance of the SQLConnection class.

        var sqlConnection:SQLConnection = new SQLConnection();
// This will create a new in-memory database and a synchronous connection.
sqlConnection.open(null, SQLMode.CREATE);
// The connection has been established and sqlConnection can be used for other database operations at this point.

    

To open an asynchronous connection, you must use either event listeners or Responders to receive the notification that the connection has been established. Each database operation specifies an event type that it will dispatch when it completes. In the case of the openAsync() method, used to create an asynchronous mode connection, an event type of SQLEvent.OPEN is dispatched. The API documentation lists the event types that are dispatched for other database operations. The following example uses an event listener to handle the dispatched event:

        var sqlConnection:SQLConnection = new SQLConnection();
// The handleConnectionOpen function will be called when the connection has been established.
sqlConnection.addEventListener(SQLEvent.OPEN, handleConnectionOpen);
// This will create a new in-memory database and an asynchronous connection.
sqlConnection.openAsync(null, SQLMode.CREATE);
// The connection is not established and sqlConnection can not be used for other database operations at this point.

    

Instead of adding an event listener to receive the event, you can specify a Responder instance. The Responder constructor takes two parameters: the function to call if the method succeeds and the function to call if the method fails. The error case is explored in the next section. For now I'll only specify the function to call if the openAsync() method succeeds:

        var sqlConnection:SQLConnection = new SQLConnection();
// This will create a new in-memory database and an asynchronous connection.
// The handleConnectionOpen function will be called when the connection has been established.
sqlConnection.openAsync(null, SQLMode.CREATE, new Responder(handleConnectionOpen));
// The connection is not established and sqlConnection cannot be used for other database operations at this point.

    

For both the event listener and the Responder, the function specified must declare that it takes a single parameter of type SQLEvent. The definition of the handleConnectionOpen() function used above would have the following form:

        function handleConnectionOpen(sqlEvent:SQLEvent) {
    // Handle connection having been established.
}

    

When opening an asynchronous connection, if both an event listener and a Responder are specified, the Responder will take precedence and be called instead of the registered event listener.

Handling errors

Up to this point the code assumes that the SQLConnection gets established without errors. Alas, this isn't always the case. To make sure an application does the right thing we need to look at error handling. The execution mode being used will dictate how to detect that an error occurred. If explicit error handling is not specified, any errors that are generated will be handled silently, unless the application is running in a debug environment.

For a synchronous connection, any database operation that might cause an error should be surrounded with a try..catch block. Multiple database operations can be combined into the same try..catch block if an error generated by any of those operations should be handled the same way. Returning to the example of opening a synchronous connection, the following example introduces the needed error handling.

        try {
    sqlConnection.open(null, SQLMode.CREATE);
} catch(sqlError:SQLError) {
    // Handle error establishing connection here.
}

    

An asynchronous connection opened using event listeners will dispatch an event of type SQLErrorEvent.ERROR to indicate that a problem occurred:

        sqlConnection.addEventListener(SQLEvent.OPEN, handleConnectionOpen);
sqlConnection.addEventListener(SQLErrorEvent.ERROR, handleConnectionError);
sqlConnection.openAsync(null, SQLMode.CREATE);

    

Lastly, in the case of using a Responder, the second argument that is passed into the Responder constructor indicates the function to call if the database operation fails:

        sqlConnection.openAsync(null, SQLMode.CREATE, new Responder(handleConnectionOpen, handleConnectionError));
    

In both the event listener and Responder cases the function used to handle the error condition must declare that it takes a single parameter of type SQLErrorEvent. The definition of the handleConnectionError() function used above would have the following form:

        function handleConnectionError(sqlErrorEvent:SQLErrorEvent) {
    // Handle error establishing connection here.
}

    

As mentioned in the previous section, if an asynchronous connection is given both an event listener and a Responder instance, the Responder functions will take precedence and be called instead of the registered event listener. If a Responder is used but the second constructor argument is not supplied, any errors will be handled silently regardless of whether a debug environment is used.

Using a SQLConnection

A SQLConnection exposes many other methods on itself, primarily geared towards database administration or transaction handling. Each of these methods follows the same programming model described above.

The other common class that is used after a connection has been established is SQLStatement. When a SQLStatement is created, it is associated with a SQLConnection, so the execution mode that the SQLConnection is operating in extends to the SQLStatement. Calling methods on a SQLStatement will require the same programming model as described above for a SQLConnection. One difference of note is that when using Responders, certain database operations primarily execute() and next() will pass a SQLResult object to the success function instead of a SQLEvent.

To help put the two different execution modes into perspective, I've inlcuded a sample application with this article that accesses a local database in both synchronous and asynchronous execution modes. After downloading and unpacking the source, you can compile the application with either Flex Builder 3 or the AIR SDK. Once the application is launched, the first action will be to generate a sample database; click the Generate Sample Database button. Depending on the speed of your system, this could take a few seconds, during which time the application will be unresponsive. This is a glimpse at how the execution mode used can manifest itself as a user experience issue. The next section goes into more detail on why that is, while the remainder of this section provides a walkthrough of the sample application.

The generated database represents a primitive catalog with three tables: a product table with an identifier and two summary fields, a SKU table with an identifier and four attribute fields, and a join table that associates a SKU to a product. The code used for generating a database is found in the SampleDatabaseGenerator class. The code is broken down into methods that handle the database schema creation and data insertion for products and SKUs.

The sample application provides three columns, each with an identical set of controls under the headers Synchronous, Asynchronous, and Pool. The first two use the similarly named execution modes in the manners described above. The Pool represents using utility classes to make working with a database easier. The implementation of the utility classes used by the Pool is described in a later section.

The Debug Output check box toggles logging debug-level information into the TextArea in the middle of the application. This debugging information can give you an idea of what the application is doing behind the scenes as each database operation is executed. The Clear Debug Output button will erase any debugging output already captured. Any data loaded by one of the scenarios is displayed using a DataGrid component at the bottom of the application.

In each column the first text field is used to enter a numeric product identifier that will be loaded from the database when the Load Product button is clicked. The sample database uses product identifiers from 1 to 500 inclusive. This scenario uses a simple database operation that may or may not return a data row and represents basic data loading techniques and error handling. The same text field drives loading the SKUs for a product with the Load Product SKUs button. This scenario demonstrates a more complex database operation that requires additional processing in the database and can return multiple records.

The other text field in each column accepts a number that will be used to load that many products randomly from the database when the Load Products button is clicked. This last scenario is designed to demonstrate the impact the execution mode can have on the UI responsiveness. When loading more than 10 products I recommend deselecting Debug Output; otherwise the logging overhead can drastically slow down the application.

The classes SynchronousExample and AsynchronousExample are used to process the actions present in the user interface under the corresponding column headings. I'd recommend setting break points in the various methods and exploring how the system responds to each type of execution mode. In particular, if you load many products randomly when using the synchronous controls you'll notice the application becomes unresponsive. The reasons for this are addressed in the next section.

Database operation time

Although one of the primary differences in the two execution modes is the programming model used to work with the SQLite database, the primary consideration in deciding which execution mode to use is the total time that operations against the database take to complete. The longer a database operation takes to complete, the bigger impact the choice of execution mode can have on the user experience. Before we dive into the distinctions further it is important to understand a little about the Adobe AIR runtime.

The Adobe AIR runtime uses a single application thread. What this means is that when code that you have written is being executed, other application tasks like screen updates or user gestures like button clicks are ignored. If you've ever written a loop that took a really long time to run, you probably noticed that the rest of the user interface stopped responding until the loop finished. If your application has effects that play having the user interface jitter can make for a degraded user experience.

Although there is only a single application thread, there are multiple system threads. System threads handle low-level tasks like loading an image or accessing a web service. In the case of Adobe AIR, a system thread handles processing operations against a SQLite database. System threads operate in the background, which means that it is possible for a database operation to be processing at the same time the application code is playing effects or responding to user gestures. With that quick primer, take a look at the two execution modes and how they handle the total time a database operation takes to complete.

With a synchronous connection, the application thread pauses while waiting for the system thread to complete the requested database operation. Since that database operation is pausing the application thread, effects will not play and user gestures will be ignored until the operation completes. Thus the longer a database operation takes, the more the impact on application responsiveness increases. If a database operation takes three seconds to complete, the application would be unresponsive for that entire time. No screen updates, no user gestures, and so on.

An asynchronous connection, on the other hand, will cause a system thread to start the database operation and then immediately continue to execute the application code. In this case the application can go back to responding to other user gestures or playing effects. Since the database operation is happening in the background, the length of time it takes to complete will have no impact on the user interface responsiveness. If a database operation takes three seconds to complete, the application may slow down a little as the database operation executes in the background, but it will remain responsive.

The lack of user gesture processing on a synchronous connection also means that the user can't cancel the operation, nor can the user be given any indication of how much time the database operation will take to complete. For this reason, understanding how long database operations will take, and how responsive the user interface should be when performing those actions, should be the primary consideration when deciding what type of execution mode to use.

Processing data

In addition to the time spent executing a database operation, regardless of the execution mode, trying to process too much returned data at once can lead to user experience issues. The application code that iterates over a SQLResult is running in the application thread which means that if it needs to loop over hundreds of records and manipulate each one, the time spent processing is not being used to handle effects or user gestures. The primary means to avoid this situation is to divide the data processing into smaller pieces. Between processing each group of data the application thread is allowed to execute code that handles other UI tasks and thus the application remains responsive. The primary technique for dividing up the work on a connection is to use the SQLStatement.execute() method's prefetch argument and the corresponding next() method. An alternative technique can be to manually divide up processing the results using techniques like an enter frame handler, callLater(), or a Timer.

The first optional parameter to the execute() method on a SQLStatement is a prefetch size. If it is unspecified, all records matching the query will be returned. If a prefetch is specified, no more than the number of records specified will be returned. The SQLResult.complete property indicates whether there are additional records that could be returned. To divide up the data processing, all of the records returned in a single request are processed and then the same SQLStatement is used to fetch the next set of records. The next() method also takes a prefetch argument, letting the application divide up the remaining records in the best possible way. This method of dividing up the processing by using a prefetch limit is best suited for an asynchronous connection because the application can process other events while the system thread is gathering the next set of results. If a synchronous connection is used, the calls to next() should be divided up using one of the methods mentioned below.

To manually divide up the processing of a large set of results an enter frame handler, callLater(), or a Timer can be used. Each time an enter frame or time event fires, the application processes a fixed amount of the remaining data and then stops until the next event fires. This same approach applies to callLater() but instead of waiting for an event, the application supplies a function to continue processing the work. Each time the data processing stops to wait for the next batch other application code activities for updating the user interface or handling user gestures get processed keeping the user interface responsive. Details on using each of these techniques can be found in the Adobe Flex documentation.

Database operation tips

A common situation with database applications is the need to use the result of one database operation to perform another database operation. This is most often seen when inserting new data that uses a database-generated primary key that is then used to insert data into dependent tables that reference the newly created identifier. When a synchronous connection is in use, this scenario is easily handled, as the database operation that generated the primary key will have finished before the next line of code to insert the dependent data executes. With an asynchronous connection, the database operation must be carefully chained so that the function handling the result of the first insert has access to the information needed to insert the dependent data.

Another common situation is the need to execute multiple database operations at once. A SQLConnection supports SQLStatement queueing provided every database operation uses a unique SQLStatement instance. In particular, when looping over a set of identifiers that each needs to be loaded, a single prepared SQLStatement can not be used to queue up all the database operations if using an asynchronous connection. Reusing the same SQLStatement on a synchronous connection is valid since the call to execute() will have finished before the parameters on the SQLStatement are changed.

Lastly, only one database operation can be executing on a SQLConnection at a time. If both long-running and short-running database operations will occur that are independent of each other, it is possible to have multiple connections open to the same database. In order to take advantage of multiple connections, you will need to use asynchronous connections. One connection can handle the long-running database operations while another connection handles the short database operations. However, it is not recommended to use multiple connections if both read and write database operations will be used simultaneously. Attempting to update a database on one connection while reading on another will generate an exception. How the Allurent Desktop Connection used this multiple connection approach is described in the next section.

Allurent Desktop Connection's database pool

The Allurent Desktop Connection bundled a large database with the application to support client-side keyword and color searching. Additionally this database included detailed information on all products, including ones not shown in the primary navigation. A key aspect of the application was to provide as rich and engaging a user experience as possible while providing access to all of the information in the bundled database.

The use of panning and zooming as the primary navigation mechanism meant that using a synchronous connection would have impacted the user experience too much. When a user selects a product on the main strip, the application zooms in on the product before rendering detailed information about it. In the background while the zoom effect is playing, the application issues a database operation to load the full product information from the database. The effect that accomplishes the zooming was tuned to take slightly longer than it took to load the data from the database, so the user is visually occupied while the data is loading.

To minimize the burden of working with the asynchronous APIs we developed two utility classes. The first is the ConnectionPool class. This class handles opening one or more connections to the same database and executing database operations whenever a connection is available. In our application we found that we frequently had a long-running database operation executing at the same time a short operation needed to execute. Instead of queueing all database operations on the same connection, by using multiple connections we enabled the system threads to execute both queries at the same time. The ConnectionPool class handles the bookkeeping of which connections are in use and which are free, and assigns pending database operations to the next available connection.

On top of the ConnectionPool we wanted to leverage the use of prepared statements for the performance gains over needing to parse the SQL query each time. Since a SQLStatement is bound to a particular connection, managing which SQLStatement to use based on the connection returned from the ConnectionPool was cumbersome. The StatementPool was developed to encapsulate the logic of caching and reusing a SQLStatement based on the connection it was associated with.

These two utility classes provided a simplified view to the rest of the application. A typical data load operation was reduced to specifying the query to run, the parameters for the query, and the function the SQLResult should be passed to. This technique is used by the PoolExample class in the sample application. All the performance benefits of multiple connections and cached prepared SQLStatements were hidden from the rest of the application.

Where to go from here

This article discussed the two execution modes (synchronous and asynchronous), the programming model used for each (method calls versus handler functions), the primary factors that drive what execution mode should be used (time to complete database operations and user interface responsiveness when executing them), and some tips about other areas to consider (post processing delays, multiple connections, and database operation queuing). With this knowledge you can now better understand what type of execution mode will be best to use for your application.

Tutorials & Samples

Tutorials

Samples

Using the Salesbuilder Adobe AIR sample application

Adobe AIR Forums

04/11/2012	Surround sound 5.1 with Air 3.2 on desktop app?
12/12/2011	Live Streaming H.264 Video on iOS using AIR
04/17/2012	HTMLLoader - Google Maps?
04/12/2012	Tabindex in forms on mobile?

Adobe AIR Blog

07/09/2012	Protected: Publishing Adobe AIR 3.0 for TV on Reference Devices
07/08/2012	Source Code: Adobe AIR 3.3 Retina Video Application
07/06/2012	Application specific File Storage on Adobe AIR based ios Application
07/04/2012	Recent Work - iPad/Android App: Inside My toyota

User experience considerations with SQLite operations

by Daniel Rinehart

Content

Created

Page tools

Requirements

Prerequisite knowledge

User level

Required products

Sample files

Additional Requirements

Adobe AIR SDK

Adobe Flex Builder 3 (optional)

Creating a SQLConnection

Handling errors

Handling errors

Using a SQLConnection

Database operation time

Processing data

Database operation tips

Allurent Desktop Connection's database pool

Where to go from here

More Like This

Tutorials & Samples

Tutorials

Samples

Adobe AIR Forums

Adobe AIR Blog

Products

Solutions

Industries

Help

Learning

Ways to buy

Downloads

Company