RSS Link

Tweet

Code-Monkeys Blog

Send Private Message Posted January 17, 2011 1:08 pm

Adobe AIR can natively interface with a SQLite database. Natively! This is particularly useful for applications which have infrequent data changes, for instance, the AllStays Camp&RV application in the Intel AppUp center.

Using AllStays as an example, this article will walk through the processes of

1. hooking up a SQLite database in AIR
2. making queries
3. updating the local database from the server-side database.

Hooking up a SQLite database in AIR

Consider a SQLite database named places.db. SQLite databases are a single file, and in our AIR project, places.db resides in the src folder.

Here's the code to make a connection:

private function makeConnection():void
{
	//hook up the database
	var databaseFile:File;
	databaseFile = File.applicationDirectory.resolvePath("places.db");
	sqlConnection = new SQLConnection();
	sqlConnection.addEventListener(SQLEvent.OPEN, connectionOpened);
	sqlConnection.addEventListener(SQLErrorEvent.ERROR, connectionError);
	sqlConnection.openAsync(databaseFile);
}
	

When a build is released, places.db is packaged with it. Using File.applicationDirectory works for both the release build, and launching it from Flex Builder. It's the same principal as the relative file paths used for Flex apps. It's just nice to see it in action as part of the File class in AIR, too.

In makeConnection(), above, event listeners are added to sqlConnection to handle success/failure. By the way, sqlConnection is declared at the application level so the listeners can be removed later on:

private var sqlConnection:SQLConnection;
	

Lastly, the connection is executed by sqlConnection.openAsync(databaseFile). If an error occurs while attempting the connect, it can be handled by something very simple:

private function connectionError(event:SQLEvent):void
{
	Alert.show("Database failed to open", "Doh!");
}
	

Another nifty AIR feature is that there are no import statements for any SQLite classes. They're all built-in.

For now, call makeConnection() when the creationComplete event is dispatched:

<mx:WindowedApplication
	creationComplete="makeConnection()"
	xmlns:mx="http://www.adobe.com/2006/mxml">
	

But this will change when we synchronize the local database with the server database. When the connection is successful, it launches an event. Our listener captures the event and redirects control to connectionOpened():

private function connectionOpened(event:SQLEvent):void{	
	//initialize sql statement objects
	statement.sqlConnection = sqlConnection;
	statement.addEventListener(SQLErrorEvent.ERROR, statementError);
	statement.addEventListener(SQLEvent.RESULT, statementResult);

	sqlConnection.removeEventListener(SQLEvent.OPEN, connectionOpened);
	sqlConnection.removeEventListener(SQLErrorEvent.ERROR, connectionError);
}
	

Here, the SQL statement objects are set up. The SQLStatement class has a few methods, like accepting query strings, sending them to the database, and producing results.

Only one statement – statement – is shown, but AllStays actually uses several statements depending on how the resultant data is to be used (say, as a dataprovider, or to put markers on a map, etc.) It's easier to use several statements with event listeners that stay put than to use one statement and constantly needing to add/remove event listeners to re-direct the results to different result handlers.

In the function above, statement has listeners for error and result. Note that at the end of connectionOpened(), the listeners for sqlConnection are removed. From here out, all interaction with the database is done through statement. sqlConnection is left to run behind the scenes and needn't be bothered with anymore (but don't close() it).

The variable statement is declared at the application level:

private var sqlConnection:SQLConnection;
private var statement:SQLStatement;
	

Statement's two listener functions, statementError() and statementResult(), might look like this:

private function statementError(event:SQLErrorEvent):void{
	Alert.show( String(event.error) );
}

private function statementResult(event:SQLEvent):void{
	var sqlResult:SQLResult = SQLStatement(event.currentTarget).getResult();
	dataGrid.dataProvider = sqlResult.data;
}
	

...where dataGrid is the id for a data grid component.

Making Queries

Now let's make it do something. Consider that places.db has a table called campsites, and we want to see all it's rows displayed in dataGrid. We'll query places.db by setting statement.text and calling execute(), like so:

private function stateQuery():void
{
	statement.text = "SELECT * FROM campsites"
	statement.execute(); //result is captured by statementResult()
}
	

And this function can be called at the end of connetionOpened(), like so:

...
sqlConnection.removeEventListener(SQLEvent.OPEN, connectionOpened);
sqlConnection.removeEventListener(SQLErrorEvent.ERROR, connectionError);
stateQuery();
}
	

When run, the database connection is made, the statements set up, the query executed, and the result displayed in a data grid.

As an aside, the SQLite syntax is identical in many ways to SQL. For a complete rundown, go here.

Updating the local database from the server-side database.

Why do it this way? The AllStays SQLite database has some 30,000 points of interest in North America. Usually, such a database is stored on a server. But this being a travel application, internet access is not always available for the end user, so the data needs to be accessible locally.

A contrasting issue is that the 30,000 entries are in a state of seasonal flux. Although the data changes infrequently, it does change. Camping sites close for severe seasonal weather. Business hours change. Establishments start up, and close their doors. So, users need to have the latest information before venturing out on their travels. All these changes need to be updated in a central database, and the AIR applications need to "check in" and update themselves.

Here is the general concept behind synchronizing a local database to a server-side database.

Firstly, every table in the server database will have three extra columns:

• CreatedDate (when the row was created)
• LastChangeDate (the last time this row was added/changed/de-activated)
• Active (a boolean used to delete a row without actually removing it from the database)

When a row is added, its CreatedDate and LastChangeDate is populated with the current date.
When a row is changed, http://www.sqlite.org/lang.html is populated with the current date. (even when http://www.sqlite.org/lang.html is set to false).
When a row is deleted, it's not actually deleted from the database. Rather Active is set to false, and LastChangeDate is populated with the current date.

When the AllStays starts up, it detects if an internet connection can be made to the server. If so, it gets a lastChangeDate from a text file stored locally, asks for all the rows whose LastChangeDate is greater than its lastChangeDate, iterates through them, and either creates new rows, updates rows, or deletes rows, accordingly.

This example uses a separate MXML component to do the updating, called DatabaseUpdater.mxml. It has an id of databaseUpdater.

<mx:WindowedApplication
	creationComplete="databaseUpdater.update()"
	xmlns:mx="http://www.adobe.com/2006/mxml">
	

The first half of update() should look familiar:

public function update():void
{
	var databaseFile:File;
	databaseFile = File.applicationDirectory.resolvePath("places.db");
	
	sqlConnection = new SQLConnection();
	sqlConnection.addEventListener(SQLEvent.OPEN, connectionOpened);
	sqlConnection.addEventListener(SQLErrorEvent.ERROR, connectionError);
	sqlConnection.openAsync(databaseFile);
	
	statement.addEventListener(SQLEvent.RESULT, updateResult);
	statement.addEventListener(SQLErrorEvent.ERROR, updateError); 
	
	lastUpdateFile = File.documentsDirectory.resolvePath("AllStays/lastUpdate.txt");
	var fileStream:FileStream = new FileStream();
	if (lastUpdateFile.exists)
	{
		fileStream.open(lastUpdateFile, FileMode.READ);
		lastUpdate = fileStream.readUTFBytes(fileStream.bytesAvailable);
		fileStream.close();
	}
	else
	{
		fileStream.open(lastUpdateFile, FileMode.WRITE);
		fileStream.writeUTFBytes(lastUpdate);
		fileStream.close();
	}

	getUpdatedRows.send();
}
	

Most of the variables are scoped to DatabaseUpdater:

private var sqlConnection:SQLConnection;
private var updateStatement:SQLStatement;
private var lastUpdateFile:File;
private var lastChangeDate:String = "2010-11-01";
	

The variable lastUpdateFile is resolved to point to a text file. If the file exists, then it is opened, read, and the text from it held in lastChangeDate. If there is no such file, then it is created with the text "2010-11-01", sans quotes.

The connectionOpened() and connectionError() listeners are the same as previous. At the end of the method is getUpdatedRows.send(), referring to an HTTPService component:

<mx:HTTPService id="getUpdatedRows"
	result="handleResult(event)"
	url="http://domain.com/phpfile.php"/>
	

Probably, the updated results could be retrieved using a statement like "SELECT * from campsites WHERE LastChangeDate > lastChangeDate". AllStays uses a webservice to a php file. In either case, you'll end up with some results – an array of row objects – which need to be iterated, and made into DELETE, INSERT, and UPDATE statements.

This example has been simplified from what AllStays actually does. Firstly, there is only one table in this example. Most databases have more than one table, and would require an additional level of iteration. Secondly, AllStays does not execute the statements as they are created, but stores them in an arrayCollection and executes them afterward to prevent congestion.

The example supposes that the php file returns the results in XML.

private function handleResult(event:ResultEvent):void
{
   var results:xml = XML(event.message.body);
   if (results.row.length() == 0) shutDown(); return;
   
   var j:uint = 0;
   var r:XML;
   var values:Array = [];
   delete$ = "";
   insert$ = "";
   update$ = "";

   //iterate through the results
   for (var i:uint; i < results.row.length(); i++)
   {
      r = results.row[i];

      //DELETE
      if (r.Active == "false")
         delete$ += " OR ID='" + String(r.ID) + "'";
      else{
         //INSERT
         if (sql2As3(r.CreatedDate).time > sql2As3(lastChangeDate).time)
         {
            values = new Array();
            for (j = 0; j < colNames.length; j++)
            {
               values.push("'" + r.child(colNames[j]) + "'");
            }
            insert$ += " UNION SELECT " + String(values);
         }
         //UPDATE
         else if (sql2As3(String(r.LastChangeDate)).time >
            sql2As3(lastChangeDate).time)
         {
            update$ = "UPDATE campsites SET ";
            values = new Array();
            for (j = 0; j < colNames.length; j++)
            {
               values.push(colNames[j] + "='" + r.child(colNames[j]) + "'");
            }
            update$ += String(values) + " WHERE ID='" + String(r.ID) + "'";
            updateStatement.text = update$;
            updateStatement.execute();
         }
      }
   }
   if (delete$ != "")
   {
      delete$ = "DELETE FROM campsites WHERE" + 
		delete$.substring(3, delete$.length);
      updateStatement.text = delete$;
      updateStatement.execute();
   }
   if (insert$ != "")
   {
      insert$ = "INSERT INTO campsites (" + String(colNames) + ")" +
		insert$.substring(6, insert$.length);
      updateStatement.text = insert$;
      updateStatement.execute();
   }
   //the database is updated.  save the new lastChangeDate.
   lastChangeDate = as32Sql(new Date());
   var fileStream:FileStream = new FileStream();
   fileStream.open(lastUpdateFile, FileMode.WRITE);
   fileStream.writeUTFBytes(lastChangeDate);
   fileStream.close();
}
	

private function sql2As3(dateString:String):Date{
	return new Date(String(dateString).replace(new RegExp("-", "gim"), "/"));
}

private function as32Sql(date:Date):String{
	var dateFormatter:DateFormatter = new DateFormatter;
	dateFormatter.formatString = "YYYY-MM-DD JJ:NN:SS";
	return dateFormatter.format(date);
}
	

The variable colNames is an array of the names of the columns for campsites. It is used both to build the SQL statements, and to access nodes in the XML by using .child(colNames[j]). It is scoped to DatabaseUpdater:

private var colNames:Array = ["ID", "Name"];
	

There are 3 main parts to handleResult().

1. If there are any results, they are iterated through, and the delete/insert/update statements are created.
2. After the iteration, the delete and insert statements are executed. The update statements are executed inside the loop.
3. The current date is saved in lastUpdate.txt.

Examining the building of the SQL statements, consider that the table campsites has 2 columns, ID and Name, in addition to LastChangeDate, CreatedDate, and Active. The statements would then look something like:

• DELETE FROM campsites WHERE ID='42' OR ID='56' OR ID='102' (deletes 3 rows)
• INSERT INTO campsites (ID, Name) SELECT '103', 'Lewis and Clark Caverns' UNION SELECT '104', 'Smokey Bear Historical Preserve' (creates 2 new rows)
• UPDATE campsites SET ID='67' Name='Yellowstone Park' WHERE ID='67' (updates 2 existing rows)

To build the delete statement, delete$ keeps appending "OR ID=" for each row to delete, and then removes the preceding "OR" before tacking it on to the end of "DELETE FROM campsites WHERE". insert$ follows the same idea, concatenating the values to select, removing the preceding "UNION", and adding it to "INSERT INTO campsites (ID, Name)" By building the statements this way, all the SQL statements are executed in one swoop (called a transaction). It's faster and easier on the SQLite database.

When updating, there is also a way to build one statement using conditional blocks (similar to switch...case) but it's rather complex, so AllStays opts to update each row individually. Worst case scenario in this situation would be that the user must wait an extra 15 seconds at some point during the year. On average, the typical wait time would be an extra second or two every couple of months.

At the end of updating, control is returned to the application layer:

private function shutDown():void
{
	sqlConnection.close();
	mx.core.Application.application.makeConnection();
}
	

Databases Make Apps Go 'Round

As you can see, the native support for SQLite makes integration pretty simple, and in many cases even a simple database can dress up an app to include all sorts of great new functionality. Most apps out there can or do benefit from the kinds of power even the simplest database provides.