AIR SQLite Manager API allow using multiple tables, transactions and rollbacks

One of the most significant features of AIR is the ability to work with a SQLite database.  Having a local database allows creating offline capability for applications that are rich with data as well as opening the door for storing large amounts of data locally.

Creating a complex application using SQLite can be challenging, because an application may use many SQL commands and multiple tables and SQLite doesn't support all these features:

                                                                                                                               
    * Different classes in our application may need to use the SQLite database
    * We may want to keep the database connection open, and avoid duplicating of code.

I have created a manager class that wraps many of the SQLite classes such as SQLConnection and SQLStatement.  The manager also simplifies the complex process.   The API is called SQLiteManager, and it allows you to set the database settings and then access the manager from anywhere in your application. It makes the process simpler and integrates very well with micro architecture frameworks.

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

The source code for the utility class can be loaded from here:

http://github.com/EladElrom/eladlib/tree/master/EladLibAIR/src/com/elad/framework/sqlite/

SWC for the EladLibAIR code can be loaded from here.

implementation code:

<?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/halo"
                       height="372" width="504"
                      
creationComplete="creationCompleteHandler()">    
    
    <fx:Script>
        <![CDATA[
           
            import mx.rpc.events.ResultEvent;
            import mx.controls.Alert;
            import com.elad.framework.sqlite.vo.SqliteTableVO;
           
            import
com.elad.framework.sqlite.events.DatabaseSuccessEvent;
            import
com.elad.framework.sqlite.events.StatementCompleteEvent;
            import
com.elad.framework.sqlite.events.DatabaseFailEvent;
            import mx.collections.ArrayCollection;
            import mx.events.FlexEvent;
            import com.elad.framework.sqlite.SQLiteManager;
           
            // 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();

           
//--------------------------------------------------------------------------
            //
            //  Methods
            //
           
//--------------------------------------------------------------------------
   
           
            // 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 );
            }
           
            // 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);
                });                
            }    

            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);                
            }            

           
//--------------------------------------------------------------------------
            //
            //  Handlers
            //
           
//--------------------------------------------------------------------------
   
           
            // 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;
                }
            }

        ]]>
    </fx:Script>

   
    <!-- 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>       
   
    <!-- 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;           
   
                }"
    />
   
    <s:Button id="setSavePointBtn"
              x="20" y="312"
              label="setSavePoint"
              enabled="false"
              click="database.setSavepoint('point1');"/>
   
    <s:Button id="releaseSavePointBtn"
              x="122" y="312"
              label="ReleaseSavePoint"
              enabled="false"
              click="database.releaseSavepoint('point1');"/>
   
 
   <s:Button
id="rollbackToSavePoint"
              x="249" y="312"
              label="RollbackToSavePoint"
              enabled="false"
              click="database.rollbackToSavepoint('point1', new
Responder(function(event:SQLEvent):void
              {
                  Alert.show( "Total number of transactions: " +
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 );
              "/>   
       
</s:WindowedApplicatio