Back in January ‘09 I posted an API that makes working with an AIR application that works with an SQLite database and has many SQL commands and multiple tables simple. I have received feedback from developers that are using the API and have found it helpful. I kept getting requests to update the API to support multiple tables.

You need this API because working with an application that has many SQL commands and multiple tables can become challenging.

• These commands may be initialized from different classes, and
• we may want to keep the database connection open and avoid duplicating code.

SQLiteManager does just that and allows you to set the database settings and than access the manager from anywhere in your application. It makes the process simpler and integrates very well with micro architecture frameworks.

I finally got around to updating the API, and it now support the following:

• Password encryption
• Multiple tables
• Common SQL commands
• Transactions and rollback option
• Better handling of results
• Improved and optimized code

Let’s look at the implementation code. The application will hold two tables, one for users and one for orders, and we will be able to insert and read information as well as keep track of transactions and rollback in case there are errors or for any other reasons.

AIR SQLite Manager

The first step is to set constants with all the names of all the user gesture SQL we will be running. This way we can track requests and use the same result handler for all of our SQL requests.

			// SQL user gestures
			private const READ_ALL_USERS_INFO:String = "readAllUsersInfo";
			private const INSERT_USER_INFO:String = "insertUserInfo";
			private const INSERT_ORDER_INFO:String = "insertOrderInfo";
			private const READ_ALL_ORDERS_INFO:String = "readAllOrdersInfo";

			// holds the database manager singelton instance
			private var database:SQLiteManager = SQLiteManager.getInstance();

The creation CompleteHandler method will be called once the application has completed initialization. We will be setting the database information and starting the connection. Notice that you need to set the array Vector with all of the tables you will have in your application. The way it is working is in case the application doesn’t have the tables created already. They will be generated automatically for the user so you need to specifiy the SQL command to create these tables and the name of each table. The names can be anything; just use unique names.

			// start database
			protected function creationCompleteHandler():void
			{
				var password:String = null; // leave as null to have the database unsecure or set a password for secure connection. Example: "Pa55word";
				var sqliteTables:Vector.<SqliteTableVO> = new Vector.<SqliteTableVO>;

				sqliteTables[0] = new SqliteTableVO( "Users", "CREATE TABLE Users(UserId INTEGER PRIMARY KEY, UserName VARCHAR(150)); " );
				sqliteTables[1] = new SqliteTableVO( "Orders", "CREATE TABLE Orders(OrderId INTEGER PRIMARY KEY, UserId VARCHAR(150), OrderTotal DOUBLE);" );

				addListeners();

				database.start( "Users.sql3", sqliteTables, password, sqliteTables[0].tableName );
			}

Notice that in the previous method we had a call to set the listeners we will be using. Take a look at the events we will be listening to:

			// Set all the listeners
			private function addListeners():void
			{
				database.addEventListener(DatabaseSuccessEvent.DATABASE_CONNECTED_SUCCESSFULLY, function(event:DatabaseSuccessEvent):void
				{
					event.currentTarget.removeEventListener(event.type, arguments.callee);
					database.executeSelectAllCommand( database.sqliteTables[0].tableName, READ_ALL_USERS_INFO );
				});				

				database.addEventListener(DatabaseSuccessEvent.COMMAND_EXEC_SUCCESSFULLY, onSelectResult);

				database.addEventListener(DatabaseSuccessEvent.DATABASE_READY, function(event:DatabaseSuccessEvent):void {
					event.currentTarget.removeEventListener(event.type, arguments.callee);
					trace("database ready!");
				} );
				database.addEventListener(DatabaseFailEvent.COMMAND_EXEC_FAILED, function(event:DatabaseFailEvent):void {
					trace("SQL execution fail: "+event.errorMessage);
				});
				database.addEventListener(DatabaseFailEvent.DATABASE_FAIL, function(event:DatabaseFailEvent):void {
					var message:String = "Database fail: "+event.errorMessage;

					if (event.isRolledBack)
					{
						message += "\nTransaction was rolled back";
					}

					Alert.show(message);
				});
				database.addEventListener(DatabaseSuccessEvent.CREATING_DATABASE, function(event:DatabaseSuccessEvent):void {
					event.currentTarget.removeEventListener(event.type, arguments.callee);
					trace(event.message);
				});
			}

We need two methods to generate the insert SQL command for the two tables we have and make the request.

			protected function insertDataClickHandler(event:MouseEvent):void
			{
				var SQLStatementText:String = "INSERT INTO Users VALUES('" + userId.text + "','" + userName.text + "');'";
				database.executeCustomCommand(SQLStatementText, INSERT_USER_INFO);
			}

			protected function insertOrderClickHandler(event:MouseEvent):void
			{
				var SQLStatementText:String = "INSERT INTO Orders VALUES('" + ordersDataGrid.dataProvider.length+1 + "','" + IdComboBox.selectedItem.label + "','" + orderTotal.text + "');'";
				database.executeCustomCommand(SQLStatementText, INSERT_ORDER_INFO);
			}

Once SQL commands are requested all the results are processed in this implementation with the same handler called onSelectResult. Notice that each request had a unique name so we are able to match the request to the result and update the view as needed.

			// handles results
			private function onSelectResult(event:StatementCompleteEvent):void
			{
				var result:Array = event.results.data;
				var rowsAffected:int = event.results.rowsAffected;  

				switch (event.userGestureName)
				{
					case null:
						break;
					case READ_ALL_USERS_INFO:

						if (result == null)
							break;

						var len:int = result.length;
						var dp:ArrayCollection = new ArrayCollection();

						for (var i:int; i<len; i++)
						{
							dp.addItem( { label: result[i].UserId, UserName: result[i].UserName } );

						}

						IdComboBox.dataProvider =  usersDataGrid.dataProvider = dp;	

						database.executeSelectAllCommand( this.database.sqliteTables[1].tableName, READ_ALL_ORDERS_INFO );

						break;
					case INSERT_USER_INFO:
						database.executeSelectAllCommand( this.database.sqliteTables[0].tableName, READ_ALL_USERS_INFO );
						break;
					case INSERT_ORDER_INFO:
						database.executeSelectAllCommand( this.database.sqliteTables[1].tableName, READ_ALL_ORDERS_INFO );
						break;
					case READ_ALL_ORDERS_INFO:

						if (result == null)
							break;

						len = result.length;
						dp = new ArrayCollection();

						for (i = 0; i<len; i++)
						{
							dp.addItem( { OrderId: result[i].OrderId, OrderTotal: result[i].OrderTotal, UserId: result[i].UserId } );

						}

						ordersDataGrid.dataProvider = dp;	

						break;
				}
			}

The last part is the view. We have two forms to submit data and data grids to show the results.

	<!-- Users Form -->
	<mx:Form width="221" y="5">
		<mx:FormItem label="User ID:">
			<s:TextInput id="userId" width="85"/>
		</mx:FormItem>
		<mx:FormItem label="User Name:">
			<s:TextInput id="userName" width="85"/>
		</mx:FormItem>
		<mx:FormItem>
			<s:Button label="Insert User"
					  click="insertDataClickHandler(event)"/>
		</mx:FormItem>
	</mx:Form>

	<!-- Orders Form -->
	<mx:Form x="239" y="5"
			 width="221">
		<mx:FormItem label="User Id">
			<mx:ComboBox id="IdComboBox" editable="true" width="85"></mx:ComboBox>
		</mx:FormItem>
		<mx:FormItem label="Order Total:">
			<s:TextInput id="orderTotal" width="85"/>
		</mx:FormItem>
		<mx:FormItem>
			<s:Button label="Insert Order"
					  click="insertOrderClickHandler(event)"/>
		</mx:FormItem>
	</mx:Form>			

	<!-- Results -->
	<mx:DataGrid id="usersDataGrid" x="16" y="123" height="145">
		<mx:columns>
			<mx:DataGridColumn headerText="User Id" dataField="label"/>
			<mx:DataGridColumn headerText="User Name" dataField="UserName"/>
		</mx:columns>
	</mx:DataGrid>
	<mx:DataGrid id="ordersDataGrid" x="231" y="123" width="231" height="145">
		<mx:columns>
			<mx:DataGridColumn headerText="Order Id" dataField="OrderId"/>
			<mx:DataGridColumn headerText="User Id" dataField="UserId"/>
			<mx:DataGridColumn headerText="Order Total" dataField="OrderTotal"/>
		</mx:columns>
	</mx:DataGrid>

To handle the transactions we have a check box and a button to roll back in case we need to roll back. There are a few reasons to roll back. For instance, a commit of a few SQL commands that depend on each other failed. For example, in this demo app we have a user and an order, and in case the user couldn’t be created we may want to roll back and remove the user’s order.

	<!-- Transactions -->
	<s:Button id="rollbackBtn"
			  x="119" y="283"
			  label="Rollback"
			  enabled="false"
			  click="database.rollbackTransaction(new Responder(function(event:SQLEvent):void
			  {
			  Alert.show( 'Total number of changes being rolled back: ' + database.connection.totalChanges );
			  }));
			  database.executeSelectAllCommand( this.database.sqliteTables[0].tableName, READ_ALL_USERS_INFO );
			  database.executeSelectAllCommand( this.database.sqliteTables[1].tableName, READ_ALL_ORDERS_INFO );
			  isTransactionCheckBox.selected=false;"/>

	<s:CheckBox id="isTransactionCheckBox" x="18" y="284"
				label="isTransaction"
				selected="false"
				change="if ( isTransactionCheckBox.selected )
				{
					database.beginTransaction();
					rollbackBtn.enabled = true;
					setSavePointBtn.enabled = true;
					releaseSavePointBtn.enabled = true;
					rollbackToSavePoint.enabled = true;
				}
				else
				{
					database.stopTransactionAndCommit();
					rollbackBtn.enabled = false;
					setSavePointBtn.enabled = false;
					releaseSavePointBtn.enabled = false;
					rollbackToSavePoint.enabled = false;
				}"
				/>	

You can download and use the SWC from here (use eladlibair_1.1.swc 37kb):
http://eladlib.googlecode.com/files/SWCs.zip

Or download the source code of the API from here:
http://code.google.com/p/eladlib/source/browse/#svn/trunk/EladLibAIR/src/com/elad/framework/sqlite

The implementation example is here:
http://code.google.com/p/eladlib/source/browse/trunk/EladLibAIR/src/SqliteManager.mxml