Project Elbow: Using the Adobe AIR SQLite Database

Much happy face this morning.

Yesterday I worked from like 1:30am ’till 5 on trying to store the media files downloaded from the Elbow service provider in the SQLite database provided by the Adobe AIR platform.  In my mind, this was a better alternative than caching the file on the PC’s file system, but I had one problem and there were two drawbacks:

Problem: I could store the ByteArray in the database, but when I retrieved it, it always had either 5 or 10 extra bytes, and the Loader class refused to load the bytes, saying it didn’t know what the file type was.  I’m sure I could have tracked that problem down, but I got tired, went to work, and at work thought of these drawbacks:

Drawback 1: Saving a bunch of binary data in the SQLite database is slower than just saving them to disk.

Drawback 2: If the database gets corrupted then cache is gone and that just sounds messy.

So, I decided that I would keep a list of files along with their lengths, use counts, and last times accessed so that a process can prune back the least recently used (with weight on infrequently used and size).

If you’re using the file.data.SQLConnection, SQLStatement, et al and interested in what seems to be a pretty good way to put your SQL handling into one central place, then you’ll probably be considering using a static class or singleton implementation.

I’ve decided to just post the source to mine as it is right now.  I hope it helps!

In the invoke process for the application, I call SQLManager.init();

To touch a cache entry, I just call SQLManager.execCacheTouch(id);

It’s all static, so the FileLoader class doesn’t need to know where it is or really what it does.  I also decided not to refer to flash.data.SQLResult or flash.net.Responder in the FileLoader class.  It just passes a function to the select statement.

package elbow.common 
{
  import flash.data.SQLResult;
  import flash.data.SQLStatement;
  import flash.data.SQLConnection;  
  import flash.net.Responder;
  import flash.utils.ByteArray;
  import flash.events.Event;
  import flash.events.SQLErrorEvent;
  import flash.events.SQLEvent;
  import flash.filesystem.File;
  
  /**
   * ...
   * @author Joe Winett
   */
  public class SQLManager 
  {
    protected static var sqlConn:SQLConnection;
    protected static var sqlErrorMessage:String = null;
    
    protected static var stmtCacheInsert:SQLStatement = null;
    protected static var stmtCacheSelect:SQLStatement = null;    
    protected static var stmtCacheTouch:SQLStatement = null;  
    
    
    
    public static function init(): void
    {
      
      sqlConn = new SQLConnection();
      
      sqlConn.addEventListener( SQLEvent.OPEN, onSqlOpen );
      sqlConn.addEventListener( SQLErrorEvent.ERROR, onSqlOpen );
      
      var sqlFile:File = File.applicationStorageDirectory.resolvePath( "KidsElbowRoom.db" );
      sqlConn.openAsync( sqlFile );
      
    }
    
    protected static function onSqlOpen( event:Event ):void
    {
      var sql:SQLConnection = event.target as SQLConnection;
      
      if ( event.type == SQLErrorEvent.ERROR )
      {
        sqlErrorMessage = "Unable to open SQL Database: " + event.toString();
        trace(  sqlErrorMessage );
      }
      else if ( event.type == SQLEvent.OPEN )
      {                
        trace( "SQL Open: CacheSize " + sql.cacheSize + " PageSize " + sql.pageSize + " AutoCompact " + sql.autoCompact );
        
        var createStatement:SQLStatement = new SQLStatement();
        createStatement.sqlConnection = sql;
        
        createStatement.text = "CREATE TABLE IF NOT EXISTS cache ( id STRING PRIMARY KEY, useLast DATE, useCount INT, md5 STRING, mime STRING, length UINT )";
        
        createStatement.addEventListener(SQLErrorEvent.ERROR, onSqlError );
        
        createStatement.execute();
        
      }
    }
    
    protected static function onSqlError( event:Event ):void
    {
      sqlErrorMessage = ( event as SQLErrorEvent ).error.toString()  + " " + event.target.text;
      trace( sqlErrorMessage );
    }
    
    public static function makeStatement( text:String ) : SQLStatement
    {
      var stmt:SQLStatement = new SQLStatement();
      stmt.sqlConnection = sqlConn;
      stmt.text = text;
      stmt.addEventListener( SQLErrorEvent.ERROR, onSqlError );
      return stmt;
    }
    
    
    public static function execCacheInsert( id:FileId, md5:FileMD5, mime:String, length:uint ) : void
    {
      if ( stmtCacheInsert == null )
        stmtCacheInsert = makeStatement( "REPLACE INTO cache( id, useLast, useCount, mime, md5, length ) VALUES ( :id, :now, 1, :mime, :md5, :length )" );
      
      stmtCacheInsert.parameters[":id"] = id.id;
      stmtCacheInsert.parameters[":now"] = new Date();      
      stmtCacheInsert.parameters[":md5"] = md5.md5;      
      stmtCacheInsert.parameters[":mime"] = mime;
      stmtCacheInsert.parameters[":length"] = length;
      
      stmtCacheInsert.execute();
    }
    
    public static function execCacheSelect( id:FileId, funcData:Function ): void
    {
      var funcResult:Function = function( result:SQLResult ): void
      {
        funcData(result.data);
      }
      
      if ( stmtCacheSelect == null )
        stmtCacheSelect = makeStatement( "SELECT * FROM cache WHERE id = :id" );
        
      stmtCacheSelect.parameters[":id"] = id.id;
      stmtCacheSelect.execute( -1, new Responder( funcResult ) );
    }
    
    public static function execCacheTouch( id:FileId ): void 
    {
      if ( stmtCacheTouch == null )
        stmtCacheTouch = makeStatement( "UPDATE cache SET useLast = :now, useCount = useCount + 1 WHERE id = :id" );
        
      stmtCacheTouch.parameters[":id"] = id.id;
      stmtCacheTouch.parameters[":now"] = new Date();
      
      stmtCacheTouch.execute();
    }
    
  }

}