User experience considerations with SQLite operations

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.

Page 4 of 8

Previous Page

Next Page