Archive for December, 2008

Drag and Droop

The database has been created and an interface class (DAO) has been created.  Now I need to get some data into the system. To do that I need some kind of data input screen that will take in text and multimedia files (images, videos, etc.)  In the interests of making things easy on the user I didn’t want to use an open file dialog.  I find that they are unpleasant to use, especially for non-technical users.  Drag and drop is much more intuitive.

According to the hype, it’s very easy to drag and drop files from a user’s desktop into an AIR application. I figured a five minute Google would give me a dozen or so examples of how to do it.  Well, yes and no.  There were many examples (875,000 returns on AIR drag and drop), but most were written for the Beta versions of AIR.  It seemed like every sample I found used the DragManager object which (I found out later) was transformed into the NativeDragManager object when AIR was released.  Other samples were so complicated that peeling back the layers to a simple sample took more time that it was worth.

What I need to do is really quite simple.  Have an image field onto which a user can drag and drop an image file.  Once the file was dropped onto the field I want the image to change to the new one and the file contents to be available in binary form.  The last requirement is so the file can be uploaded to the database.

Step 1 – Create a target for the operation – an image field:

<mx:Image x="10" y="10" id="displayImage" source="DropHere.jpg"/>

Step 2 – Add a listener for both the drag and the drop operations. This was done by adding a simple function that is called on the createComplete event of the canvas:

public function initDrop():void{
//Listeners to manage the drop of files on the first panel
 displayImage.addEventListener(NativeDragEvent.NATIVE_DRAG_ENTER,acceptDrag);
 displayImage.addEventListener(NativeDragEvent.NATIVE_DRAG_DROP,dragDrop);
}

Step 3 – Add some code to allow the drag operation. Nothing too complicated here; its just a function that allows the drag operation into the image field:

//Accept drag and drop on the first panel
public function acceptDrag(event:NativeDragEvent):void{
        NativeDragManager.acceptDragDrop(displayImage);
}

Step 4 – Add some code to handle the drop operation.  Here I want to change the image to the new file and to put the contents of the file into a byte array.  To prove to myself that it was working, I echoed the file size to the screen.  This will be replaced later with a call to the DAO for a database update:

import flash.filesystem.File;
private var imageBytes:ByteArray = new ByteArray();  //the byte array can not be null!!!

…..

//On drop collect the files
public function dragDrop(event:NativeDragEvent):void{
        var imageList:Array = event.clipboard.getData(ClipboardFormats.FILE_LIST_FORMAT) as Array;
        var droppedFile:File = imageList[0];

         //load the file into the image
        displayImage.source= droppedFile.url;                             

        //get the byte array for the image
        var fileStream:FileStream = new FileStream();
        fileStream.open(droppedFile, FileMode.READ);                         

        fileStream.readBytes(imageBytes);
        fileSize.text = imageBytes.length.toString();             

   }

There are a couple of things to note about the dragDrop function.  First the result of the drag and drop is always an array of the files.  This is because the system allows you to drag more than one file at a time (very useful in some circumstances).  I took the cheap and cheesy route of just using the first element in the array.

Second I’m not using a Loader object to change the image contents as is suggested on many web pages.  I tried this at first and the image was not replaced, but instead the second image was overlaid on top.  I fiddled around a bit with the image layers but I couldn’t quite get it to work properly.  Simply re-setting the image source was a simple solution to the problem.

There you go.  Not much to it, but it works well and functions properly with the 1.0 release of AIR.

I split this bit of the application into a separate app so you can download it without all of the rest of the overhead.  You can find it here.

Feed the Beast

Working with the local database can be a bit of a challenge.  If the db is not well organized and if your application structures are not well thought out then you can end up with a real mess.  As I said before, sitting down and thinking the data layout all the way through and performing a few simple normalization exercises goes a long way to making the db construction a lot easier.

Knowing how AIR interacts with the local SQLite database helps as well. Essentially there are three parts to an AIR database setup:

  • The SQLite database
  • A class to interact with the database
  • Classes to hold the results of a database query

The SQLite database

AIR uses SQLite for a local/offline database.  SQLite is a very easy db to work with, but its pretty Mickey Mouse in a lot of respects.  For example there is no true auto-increment feature so the developer has to handle record id generation him/herself.  Also there are no foreign keys which means that most relationships between tables must be handled by the calling application.  How can you call yourself a relational database when the major function for controlling relationships does not exist?!

One tool that anyone working with SQLite databases must have is a database management tool. Sometimes you just need to have a look at what is going on inside the database itself.   There are a few out there for SQLite, but one of the most useful is a simple plugin for Firefox.  SQLite Manager has many useful features including a simple SQL execution tool, that will make your SQLite development much easier.  Go get it!

The nice thing about using the built in SQLite database is that you don’t need to mess around with a lot of connection properties.  You just point to a .db file (or create one) and away you go. You can concentrate on working with your data instead of trying to find the database.

Database Interaction Class

To make life easier I re-used a standard J2EE pattern for database connectivity – the Data Access Object (DAO).  A DAO is nothing more complicated than a class that acts as an interface between the AIR app and the database.  This way all db interactions (connection, queries, inserts, etc.) can be centralized and the rest of the AIR application doesn’t need to deal with the db trivialities.  The db structure can also be changed without having to rewrite the entire application.

The first thing that I needed to do was to connect to the database – actually before that I needed to check if the database exists and if not then create it.   I did this in the constructor for the DAO class as it only needs to be done once.

//private vars
private var _dbExists:Boolean;
private var _dbConnection:SQLConnection;
private var _createFlyTypeTableStmt:SQLStatement = new SQLStatement();

……..

public function LocalDAO(){
   _dbConnection = new SQLConnection();

  
// Add event listeners to react when database connection is opened.

  
_dbConnection.addEventListener(SQLEvent.OPEN, openSuccessHandler);
  
//_dbConnection.addEventListener(SQLErrorEvent.ERROR, openErrorHandler);

  // The database file is to be stored in the user’s application (UserData) folder.
  
var dbFile:File = File.applicationStorageDirectory.resolvePath("FlyData.db");
  
trace("Database file location is: " + dbFile.nativePath);

  
//Check if the database has already been created.

  
_dbExists = dbFile.exists;

  //Open the connection.
  
_dbConnection.open(dbFile);
}

The nice thing is that if the FlyData.db database file does not exist then one will be created for me. 

The openSuccessHandler listener will wait until the db is opened (or created and opened).  If it is newly created then I want to create the tables in the database as well.  The listener will then call a method called initDatabase that will fire a bunch of CREATE SQL statements. Most interactions with the database happen via a SQLStatement, through which you pass standard SQL queries as strings.  The SQLStatement is pointed to the correct database using a SQLConnection object.  

/**
* Success handler for _dbConnection
**/

private function openSuccessHandler(event:SQLEvent):void
{
    //We have an open handle to the database file.
    //Next, we need to create the tables if this is a new database

    if(_dbExists){
          trace("Application Database already exists.");
    }else{
         trace("New Application Database, must generate tables");
         initDatabase();
    }
}

private function initDatabase():void{
    _createFlyTypeTableStmt.sqlConnection = _dbConnection;

   
//Create the database tables

   
var sql:String = "CREATE TABLE `Material` (`MaterialID` INTEGER UNSIGNED NOT NULL, `MaterialName` VARCHAR(45) NOT NULL,`Color` VARCHAR(45) NOT NULL, `Size` VARCHAR(45) NOT NULL, PRIMARY KEY (`MaterialID`))";
   
_createFlyTypeTableStmt.text = sql;
   
_createFlyTypeTableStmt.execute();

//more create statements follow …..

Great, now the database exists, has been connected and I know for sure that I have a table structure.  Next I need to be able to select and insert records.

Query Results Classes

You need to have a place to put the results of a search.  In Java you have a result set that you can parse for specific db columns.  In AIR the execution of an SQLStatement returns an array of objects.  The objects must match the structure of the query exactly (and I do mean exactly).  Therefore, for all practical purposes, you need to have a class for each database search – one for each table (or one that matches multiple tables in the case of a join).

The next thing to do then is to create classes for each of the possible searches.  In my case this means that each table will need a corresponding class.  For example I have a table called Material that has a MaterialID, MaterialName, Color and Size.  I therefore need a class that has all of those elements:

package components.dataStructures
{
   public class Material    {
      public var MaterialID:int;
      public var MaterialName:String;
      public var Color:String;
      public var Size:String;

   public function Material(){
   }

  }
}

When I perform a query against the Material database the result will be mapped into an array of the class Material:

public function getMaterials():void{

    _dbMaterialStatement.sqlConnection = _dbConnection;
    var sqlQuery:String = "SELECT * FROM Material";
    _dbMaterialStatement.itemClass = Material;
    _dbMaterialStatement.text = sqlQuery;
   //SQL calls are asynchronous so a listener for the results is required
    _dbMaterialStatement.removeEventListener(SQLEvent.RESULT,onDBMaterialSelectResult);
    _dbMaterialStatement.addEventListener(SQLEvent.RESULT, onDBMaterialSelectResult);
    _dbMaterialStatement.execute();

}

In AIR, database calls are asynchronous.  This means most calls to the db will involve the setup of a listener.  This can be a bit of a pain because its difficult to just setup a method to make a call and return a value, you have to setup a listener to wait for the call to be executed.  This leads to many, many listeners floating around and , take my advice, you need to make sure you remove the listeners when they are not needed.

private function onDBMaterialSelectResult(event:SQLEvent):void{
   var result:SQLResult = _dbMaterialStatement.getResult();
   if (result != null){
       MaterialData = result.data;
   }
}

Guess What Class I Am

As I said earlier, because SQLite does not implement a proper auto-increment feature, you must do it yourself.  Shouldn’t be too hard – get the MAX of a column, add one and Bob’s your Uncle.   Something like:
"SELECT MAX(MaterialID) FROM Material"
should work fine. That works, but there is an important little trick.  AIR returns the result of all db queries as an array of objects, you must specify what those objects are, usually by setting the SQLStatement’s itemClass property.  Unfortunately this doesn’t seem to work too well for the MAX function.  I thought that MAX should return an integer, or at best a long – but it doesn’t. Setting the code to:
_dbMaxStatement.itemClass = int;
didn’t work.  With much use of the Flex Builder’s debugging tool, I determined that the MAX statement was returning an "id" class.  Okay, what the heck is an id class?  I can’t just set
_dbMaxStatement.itemClass = id;
because I don’t have the class "id" anywhere in my code – and I couldn’t figure out its package designation. I tried recasting the result to an integer and it didn’t work either. This all got very frustrating until a colleague pointed out that you can set the returned class type in the SQLStatement text itself. This lead to the following code:

_dbMaxStatement.sqlConnection = _dbConnection;
var sqlQuery:String = "SELECT MAX(MaterialID) AS id FROM Material";
_dbMaxStatement.text = sqlQuery;
_dbMaxStatement.execute();

var result:SQLResult = _dbMaxStatement.getResult();
var myint:int = result.data[0].id;
MaterialMax = myint;
trace ("***** MaterialMax: " + MaterialMax);

That little "AS id" makes all the difference in the world.

Prepared Statement

The one other thing that I wanted to mention is the use of variables inside a SQLStatement.  Since the statement is essentially a string, you could write it like:
var sqlQuery:String = "INSERT into Material (MaterialID, MaterialName, Color, Size) VALUES(‘" + material.MaterialID + "‘,’" + material.MaterialName + "‘,’" + material.Color + "‘,’" + material.Size + "‘)"; The problem is that keeping track of the quotes and commas quickly becomes a nightmare.  Fortunately there is an easier way.

In Java there is the concept of a "prepared statement" in which a question mark is substituted for a variable entry in an SQL statement.  ActionScript has the same concept although it uses an @ symbol and a variable name instead of a question mark.  Using this method the same SQL command can be written as:

var sqlQuery:String = "INSERT into Material (MaterialID, MaterialName, Color, Size) VALUES(@MatID, @MatName, @MatColor, @MatSize)";
_dbMaterialStatement.text = sqlQuery;
_dbMaterialStatement.parameters["@MatID"] = material.MaterialID;
_dbMaterialStatement.parameters["@MatName"] = material.MaterialName;
_dbMaterialStatement.parameters["@MatColor"] = material.Color;
_dbMaterialStatement.parameters["@MatSize"] = material.Size;

This makes the code much easier to read and maintain.

Conclusion

Using the above methods I created the other tables, classes for holding results and DAO methods for querying and updating the database.  The next step will be to wire the database stuff back into the display objects that I created earlier.  As you may recall I had setup some temporary data sources that were hard coded.  I now want to use the real objects as returned from the database.