mac martine

You may or may not know by now that the AIR runtime includes a version of SQLite engine. Being a smaller implementation of SQL, SQLite supports all your usual database transactions, a lot of the complex queries, and triggers. With it,you can create a database to store all the data for your Flex/AIR desktop applications, store data for offline use of your Flex/AIR desktop/web applications, or for caching of data. It's all done in the same way. I was recently working on an application where I wanted the user to have an option to be logged in automatically when the app was launched, so the first time the user logged in I created a database to cache that login info. From then on when the application is launched it checks to see if the database exists, and if so it grabs the login info, and logs them in automatically.

I've made a simple example which is a super simple desktop application which stores it's data in a SQLite database. The idea and implementation here is very similar to what I just described for the caching example. On launch, we first check to see if the database exists. If it does, that means it ought to contain some data so we grab the data and display it. If the database doesn't exist, we create it and add our one default entry, then load it into the application. The user can then add, remove, and update entries. When each of these transactions sends a result of success, we reload all the data in the database. Obviously in a real-world application this wouldn't be a great idea; that's too much overhead. One option would be to just manipulate the dataProvider ArrayCollection after each successful transaction -- but for this simple example I'm leaving it the way it is for the intent of simply demonstrating using SQLite.

At this point I think I'll let the code speak for itself.


ﾠ Basically you'll notice the basic steps are:

1. Create a connection: connection = new SQLConnection();
2. Define the database file: dbFile = File.applicationStorageDirectory.resolvePath(dbFileString);
3. Open (or create and open ) the database: connection.open(dbFile);
4. Create an empty SQLStatement: var sql : SQLStatement = new SQLStatement();
5. Create a query: var sqlString : String = "CREATE TABLE Users (" +
     " uid INTEGER PRIMARY KEY AUTOINCREMENT, " +
     " name TEXT, " +
     " phonenumber TEXT)";
6. Attach the connection and the query to the SQLStatement:
     sql.sqlConnection = connection;
     sql.text = sqlString;
7. Create event listeners for success and failure:
     sql.addEventListener(SQLEvent.RESULT, onDBCreateTableResult);
     sql.addEventListener(SQLErrorEvent.ERROR, onDBCreateTableError);
8. Execute! : sql.execute()



ﾠ <?xml version="1.0" encoding="utf-8"?> <mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" width="438" height="251" creationComplete="onCreationComplete()"> <mx:Script> <![CDATA[ import mx.controls.Alert; import mx.events.DataGridEvent; import mx.collections.ArrayCollection; [Bindable] public var dp : ArrayCollection = new ArrayCollection(); private var connection : SQLConnection; public var dbStatement : SQLStatement; public var dbFile : File; public var dbFileString : String = "exampledb.db"; public function onCreationComplete():void { connection = new SQLConnection(); dbFile = File.applicationStorageDirectory.resolvePath(dbFileString); // does the database exist? if( dbFile.exists ) { // the database exists, so let's open it and read in the data connection.open(dbFile); loadData(); } else { // the database does not exists, so let's create it createDB(); } } public function createDB() : void { connection.addEventListener(SQLEvent.OPEN, onDBCreateResult); connection.addEventListener(SQLErrorEvent.ERROR, onDBError); // the following line creates the database if it does not exists, but we already know it doesn't connection.open(dbFile); } private function onDBCreateResult(event:SQLEvent):void { // we successfully created the database, so let's create our table createTable(); } /* For this example we can use this error result handler for all our transactions */ private function onDBError(event:SQLErrorEvent):void { Alert.show( event.error.name + " " + event.error.errorID + "\n" + event.error.details + "\n" + event.error.message); } public function createTable() : void { var sql : SQLStatement = new SQLStatement(); sql.sqlConnection = connection; var sqlString : String = "CREATE TABLE Users (" + " uid INTEGER PRIMARY KEY AUTOINCREMENT, " + " name TEXT, " + " phonenumber TEXT)"; sql.text = sqlString; sql.addEventListener(SQLEvent.RESULT, onDBCreateTableResult); sql.addEventListener(SQLErrorEvent.ERROR, onDBCreateTableError); sql.execute(); sqlString = "INSERT INTO Users ( name, phonenumber) " + "VALUES ( 'Mac Martine', '555-1212')"; sql.text = sqlString; //sql.addEventListener(SQLEvent.RESULT, onDBAddResult); sql.addEventListener(SQLErrorEvent.ERROR, onDBError); sql.execute(); } private function onDBCreateTableResult(event:SQLEvent):void { trace('table created'); loadData(); } private function onDBCreateTableError(event:SQLErrorEvent):void { Alert.show( event.error.name + " " + event.error.errorID + "\n" + event.error.details + "\n" + event.error.message); trace( event.error.name + " " + event.error.errorID + "\n" + event.error.details + "\n" + event.error.message); } private function loadData():void { dbStatement = new SQLStatement(); dbStatement.sqlConnection = connection; var sqlQuery:String = "select * from Users"; dbStatement.text = sqlQuery; dbStatement.addEventListener(SQLEvent.RESULT, onRetrieveDataResult); dbStatement.addEventListener(SQLErrorEvent.ERROR, onDBError); dbStatement.execute(); } private function onRetrieveDataResult(event:SQLEvent):void { var result : SQLResult = dbStatement.getResult(); dp = new ArrayCollection(); for each( var el : Object in result.data ) { dp.addItem( { uid: el.uid, name: el.name, phonenumber: el.phonenumber } ); } trace('cache retrieved'); } public function add():void { var sql : SQLStatement = new SQLStatement(); sql.sqlConnection = connection; dbStatement = new SQLStatement(); dbStatement.sqlConnection = connection; var sqlString : String = "INSERT INTO Users ( name, phonenumber) " + "VALUES ( '(edit me)', '555-1212')"; sql.text = sqlString; sql.addEventListener(SQLEvent.RESULT, onDBAddSuccess); sql.addEventListener(SQLErrorEvent.ERROR, onDBError); sql.execute(); } public function onDBAddSuccess( event : SQLEvent ):void { var result : SQLResult = dbStatement.getResult(); var uid : Number = event.currentTarget.sqlConnection.lastInsertRowID; loadData(); } public function remove():void { var selectedItemUid : Number = dg.selectedItem.uid; var selectedIndex : Number = dg.selectedIndex; if( selectedIndex >= 0 ) { var sql : SQLStatement = new SQLStatement(); sql.sqlConnection = connection; dbStatement = new SQLStatement(); dbStatement.sqlConnection = connection; ﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠﾠvar sqlString : String = "DELETE FROM Users WHERE uid=" + selectedItemUid; sql.text = sqlString; sql.addEventListener(SQLEvent.RESULT, onDBRemoveResult); sql.addEventListener(SQLErrorEvent.ERROR, onDBError); sql.execute(); } else { Alert.show("You do not have a row selected to delete."); } } public function onDBRemoveResult( event : SQLEvent ):void { loadData(); } public function update( event : DataGridEvent ):void { var sql : SQLStatement = new SQLStatement(); sql.sqlConnection = connection; dbStatement = new SQLStatement(); dbStatement.sqlConnection = connection; var sqlString : String = "UPDATE Users SET name = '" + event.itemRenderer.data.name + "' WHERE uid = " + event.itemRenderer.data.uid; sql.text = sqlString; //sql.addEventListener(SQLEvent.RESULT, onDBAddResult); sql.addEventListener(SQLErrorEvent.ERROR, onDBError); sql.execute(); } ]]> </mx:Script> <mx:DataGrid id="dg" dataProvider="{dp}" x="10" y="37" width="407" height="202" editable="true" itemFocusOut="update(event)" > <mx:columns> <mx:DataGridColumn headerText="Name" dataField="name" editable="true" /> <mx:DataGridColumn headerText="Phone Number" dataField="phonenumber"/> </mx:columns> </mx:DataGrid> <mx:LinkButton x="300" y="10" label="Add" click="add()"/> <mx:LinkButton x="354" y="10" label="Remove" click="remove()"/> </mx:WindowedApplication>

Posted by Mac Martine at 09:45 AM | Permalink | Comments (0)

Check out InsideRIA.com which was launched today. It's an online community developed by O'Reilly and sponsored by Adobe, which will provide a number of resources for information about RIA's, RIA development, and that which is related, with lots of attention on Flex, AIR, Actionscript and Flash.

Posted by Mac Martine at 11:27 AM | Permalink | Comments (0)

I'm creating my first project using PureMVC after using Cairngorm for a few projects. Once I started understanding the concepts and intent behind each of the architecture components (Proxies and Models, Views and Mediators, Controllers and Commands), it's been great in it's relative simplicity. I did printed out the docs and read over them everyday at lunch for a few days in a row just make it all really sink it, but it didn't take too long to click.
The best part is that I've had a few questions so I decided to check out the PureMVC forums on the PureMVC site. So far everything I've searched has returned a thread with a very detailed, extensive answer from Cliff! The days go so much easier when there's easy to find and good information about whatever your working with.

One of the problems I ran into pretty quickly was that I had a TabNavigator with 3 tabs where the contents aren't to be drawn or loaded until they are clicked on.
I initially tried to register the mediators for those 3 view components at startup, which to no surprise threw runtime errors because the views didn't yet exist. Not being sure what the best solution was here, I searched the forums and found this thread.

I went with Cliff's recommendation (deferred instantiation) and here's what I have now:

My TabNavigator has 3 tabs each containing one custom component named "tags", "groups", "info".
In each of these components I have a creationComplete event which calls an onCreationComplete function, which in turn dispatches a custom Event:

dispatchEvent( new ComponentLoadedEvent( ComponentLoadedEvent.COMPONENT_LOADED, true, false, this ) );

The mediator of the TabNavigator itself listens for this event and calls handleLoad when it hears it:

mainComponent.addEventListener( ComponentLoadedEvent.COMPONENT_LOADED, handleLoad );

The handleLoad() registers the appropriate mediator for the loaded component:

public function handleLoad( e : Event ):void
{
    var component :Object 	= e["component"].name;
			
    switch(component)
    { 
        case "tags":
            facade.registerMediator( new TagsMediator( mainComponent.tags )  );
            break;
        case "groups":
            facade.registerMediator( new GroupsMediator( mainComponent.groups )  );
            break;
        case "info":
            facade.registerMediator( new UploadStatusMediator( mainComponent.info )  );
            break;
    }
}

Posted by Mac Martine at 03:14 PM | Permalink | Comments (0)