Using an SQLite database in an AIR app

Adobe AIR bundles the popular SQLite database to serve your application’s relations data storage needs. This makes it simple for applications to persist complex data across sessions.

If you are creating an application for your website, this will make it simple to synchronize data form your website’s SQL database (assuming you are using a relational database) to your app for offline operation. Even otherwise storing data in a structured manner in a relational database has many benefits as you may be aware.

We will build a simple application that implements a TODO list storing the data in an SQL database. The application will have an uncomplicated interface. The image of interface below should be self-explanatory:

All we have is a simple list of to-do items. A small textbox and add button allow one to add items to the list. Items in the list can be removed by selecting them and pressing the delete key. Simple!

Before we begin though, let us take a look at the SQL database API in AIR. We will only take a look at the relevant portions, Adobe AIR provides numerous other features such as encryption, indices etc. Note that we will not be covering the semantics of SQL itself, so we will assume you have at least a cursory knowledge of the same. A good resource for learning the SQL syntax is here.

The SQL database API in AIR supports both synchronous and asynchronous modes of operation. While the synchronous mode is simpler to understand and implement, a long synchronous operation means that your UI is blocked and non-responsive while the data is being fetched. In synchronous mode, as expected, you get a response immediately after an SQL statement has been executed.

In asynchronous mode, you need a call-back function to respond to the data once it has been retrieved from the database. This way your application can continue updating and working while data is being retrieved, added or updated in the database.

For further gains in speed, you can create a database that is stored only in memory, instead of a file. This can be useful if you don’t need the data to persist across multiple sessions with the app.

Initializing and opening the database

To work with the database, you must initialize a new SQLConnection object. The SQLConnection object will allow us to open a connection to a database using the openAsync (for asynchronous operation) or open functions (for synchronous operation). This function needs to be provided a File instance in which it will store the data – this file will be created if it doesn’t already exist.

var dbConnection:SQLConnection = new SQLConnection();var dbFile:File = File.applicationDirectory.resolvePath("dbfile.db");dbConnection.open(dbFile);

If we had provided null instead of dbFile in the dbConnection.open function, the database would be stored in memory instead.

If we use the openAsync function instead of open, the database will be opened in the Asynchronous mode, and you will have to add event listeners to dbConnection to know when it is safe to operate on the database.

Creating and executing statements

Once you have your database set and ready, you can begin creating new statements to execute on your database. When you create a statement once, it is compiled, and as long as you don’t change the statement text, you stand to get better performance at each subsequent execution.

SQL statements need to be associated with a SQLConnection before they are executed. The following is an example of a simple SQL statement being executed:

var createTableStmt:SQLStatement = new SQLStatement();createTableStmt.sqlConnection = dbConnection;createTableStmt.text = "CREATE TABLE data (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT)";createTableStmt.execute();

If you execute a statement that is supposed to return data, then you can access that data using the getResult() function of the SQLStatement. If you opened the database Asynchronously, then this value will only be available after the SQLEvent.RESULT event has occurred, and you will need to add an event listener for the event.

Making our app

Our todo app requires a single table with a single column to store our todo entries, and column for the id. We create a class to perform all the data access and manipulation operation.

Since we have already covered the basics of the SQL database API, let’s dive into our TodoData class that we will use to manipulate the database:

 

package{ import flash.data.SQLConnection; import flash.data.SQLStatement; import flash.filesystem.File;  import mx.collections.ArrayCollection; public class TodoData { private var dbConnection:SQLConnection; private var getTodosStmt:SQLStatement; private var addTodoStmt:SQLStatement; private var delTodoStmt:SQLStatement;  public function TodoData(databaseFile:File) { dbConnection = new SQLConnection(); dbConnection.open(databaseFile);  initializeDB(); getTodosStmt = new SQLStatement(); getTodosStmt.sqlConnection = dbConnection; getTodosStmt.text = "SELECT * FROM todos ";  addTodoStmt = new SQLStatement(); addTodoStmt.sqlConnection = dbConnection; addTodoStmt.text = "INSERT INTO todos (todo) VALUES (?) ";  delTodoStmt = new SQLStatement(); delTodoStmt.sqlConnection = dbConnection; delTodoStmt.text = "DELETE FROM todos WHERE id = ? ";  }  private function initializeDB():void { var createStmt:SQLStatement = new SQLStatement(); createStmt.sqlConnection = dbConnection; createStmt.text = "CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY AUTOINCREMENT, todo TEXT) "; createStmt.execute(); }  public function getAll():ArrayCollection { getTodosStmt.execute(); return new ArrayCollection(getTodosStmt.getResult().data); }  public function add(todo:String):void { addTodoStmt.clearParameters(); addTodoStmt.parameters[0] = todo; addTodoStmt.execute(); }  public function remove(id:int):void { delTodoStmt.clearParameters(); delTodoStmt.parameters[0] = id; delTodoStmt.execute(); } }}

A quick overview of what we are doing here:

• The class constructor initializes the database connection to the specified file
• It runs the initializeDB function that creates  a new table if not already present
• The class constructor also initializes the different statements that will be used in the application. By doing so we improve performance as described before
• The getAll function retrieves all the todo items in the database, wraps them in an ArrayCollection, and returns them
• The add function adds the specified string to the database
• The remove function removes the todo having the specified id from the database
• An SQL statement can have a parameter, which is specified by a “?”, or a named parameter as “:paramname”
• The parameter for a statement can be specified using the parameters property

Now for the rest of our applications code in the mxml file.

 

<?xml version="1.0" encoding="utf-8"?><s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"  xmlns:s="library://ns.adobe.com/flex/spark"  xmlns:mx="library://ns.adobe.com/flex/mx" showStatusBar="false" width="250" height="430" creationComplete="windowedapplication1_creationCompleteHandler(event)" > <s:layout> <s:VerticalLayout /> </s:layout> <fx:Script> <![CDATA[ import mx.collections.ArrayCollection; import mx.events.FlexEvent; private var dbFile:File = File.applicationDirectory.resolvePath("dbfile.db"); private var todos:TodoData = new TodoData(dbFile);  protected function addTodo_clickHandler(event:MouseEvent):void { todos.add(newTodo.text); newTodo.text = ""; refreshTodos(); }  protected function todoItems_keyDownHandler(event:KeyboardEvent):void { if (event.charCode == 127){ if (todoItems.selectedItem != null){ todos.remove(todoItems.selectedItem.id); refreshTodos(); } }  }  private function refreshTodos():void { todoItems.dataProvider = todos.getAll(); } protected function windowedapplication1_creationCompleteHandler(event:FlexEvent):void { refreshTodos(); } ]]> </fx:Script> <s:List id="todoItems" width="250" height="400" keyDown="todoItems_keyDownHandler(event)" dataProvider="{new ArrayCollection()}" labelField="todo" /> <s:HGroup width="100%"> <s:TextInput id="newTodo" width="100%" /> <s:Button id="addTodo" label=" " click="addTodo_clickHandler(event)" /> </s:HGroup></s:WindowedApplication>

 

Here is a brief description of what is going on:

 

<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"  xmlns:s="library://ns.adobe.com/flex/spark"  xmlns:mx="library://ns.adobe.com/flex/mx" showStatusBar="false" width="250" height="430" creationComplete="windowedapplication1_creationCompleteHandler(event)" >

• We are hiding the status bar of the application
• We are giving our app a width of 250 and height of 430

<s:List id="todoItems" width="250" height="400" keyDown="todoItems_keyDownHandler(event)" dataProvider="{new ArrayCollection()}" labelField="todo" />

• Our list is given a width of 250, just like the app itself, and a height of 400
• We are handling the keyDown event to check if the user pressed the Delete key
• We are initializing the dataprovider with a new ArrayCollection
• The labelField is set to “todo”, as this is the field of containing the label name in the arraycollection items

<s:HGroup width="100%"> <s:TextInput id="newTodo" width="100%" /> <s:Button id="addTodo" label=" " click="addTodo_clickHandler(event)" /></s:HGroup>

• A horizontal group spanning the width of the app contains the text entry box and the button to add the todo
• The button is labelled simply “ ” and on click it will call the addTodo_clickHandler function

private var dbFile:File = File.applicationDirectory.resolvePath("dbfile.db");private var todos:TodoData = new TodoData(dbFile);

• We are pointing dbFile to our database file and
• We are initializing the todos database using the class above

private function refreshTodos():void{ todoItems.dataProvider = todos.getAll();}protected function windowedapplication1_creationCompleteHandler(event:FlexEvent):void{ refreshTodos();}

• Here will simply get all the todos from the database set them as the todoItems List’s dataProvider
• We are handling the creation complete event of the application window to refresh our todo list

protected function addTodo_clickHandler(event:MouseEvent):void{ todos.add(newTodo.text); newTodo.text = ""; refreshTodos();}

• When a user clicks on the “ ” add button to add a todo, we simply use the corresponding function from our class
• We then clear the todo entry box
• We also refresh the todos so the newly added entry is displayed

protected function todoItems_keyDownHandler(event:KeyboardEvent):void{ if (event.charCode == 127){ if (todoItems.selectedItem != null){ todos.remove(todoItems.selectedItem.id); refreshTodos(); } } }

• This function handles deleting a todo when a user selects it and presses the delete key
• First we check if the delete key was pressed by checking for its keycode (127)
• Second we check if a todo item is even selected from the list
• We simply call our previously defined TodoData class function to remove the selected item
• We refresh the todo list to show the change

This is all we need to do for this app! There are many ways to improve it; a starter would be giving the ability to edit todos. However as it is it will function as a todo app, allowing one to enter a list of todos and delete them from the list when done. The todo items will persist across sessions thanks to the database.

This is just a small taste of what you can do with a database. Using third party libraries and ORM frameworks you can take advantage of this great facility allowed by AIR.

 

 

You can download a free trial of Adobe Flash Builder 4 from the Adobe website.