Force.com Flex Toolkit AIRConnection updates

We’ve been working more and more with Adobe AIR and Salesforce.com recently, and as such we’ve been putting the AIRConnection class through its paces, fixing bugs and adding functionality along the way. Here are a few of the modifications we’ve made, and why:

SQL Reserved Words

One of the first things that we noticed was that we needed to amend the the SOQL_To_SQL() method in order to make sure SQLite queries didn’t bomb when querying Case objects. “Case”, unfortunately, is a reserved word in SQL, but not in SOQL, so the query doesn’t translate perfectly. An easy fix for this is to always add back-ticks around the table name in the SQL statement, which is accomplished easily enough with a regular expression:

        private function SOQL_To_SQL(soql:String):String {
//TG: We need to put back-ticks around the table names because
            //Case is an SQL reserved word, which causes bad things.
//We should probably eventually back-tick column names as well,
//but this is good enough until somebody names a
//column with a reserved word, I guess.
var sqlRegEx:RegExp = /FROM\s+(\S*)/gi;
var sql:String = soql.replace( sqlRegEx, “FROM `$1`”);

As noted, it would be a good idea to put back-ticks around the column names too, but we haven’t run into any reserved words in column names, and well, “if it ain’t broke…”

Database Encryption

This is a pretty easy modification since the SQLConnection class allows you to encrypt the database by simply providing an encryption key to the open() method. We basically just added an encryptionKey:ByteArray parameter to the AIRConnection login() method, since this is where the SQLite database is first created or opened (if it already exists):

        public override function login(lreq:LoginRequest,encryptionKey:ByteArray=null):void {
This allows the parent application to create and provide an optional encryption key using whatever method is preferred by the developer, and the default of null ensures that we don’t break any existing applications using this library. Since this is an override function (AIRConnection extends Connection), you’ll want to add the same parameter to the login() method of the Connection class as well. That goes for any modification to an override method in the AIRConnection class. From there, we just have to pass the encryptionKey along to the openSQLiteDatabase method:

 

              if (openDatabases[lreq.username] == undefined)
{
if(encryptionKey==null)
{
openSQLiteDatabase(lreq.username);
}
else
{
//encrypt the db (or, if it exists, open it with the provided key)
openSQLiteDatabase(lreq.username,false,encryptionKey);
}
}

Then, in the openSQLiteDatabase() method, we have to include the encryptionKey in the SQLConnection open() method:

                syncSQL.open(file, SQLMode.CREATE, false, 1024, encryptionKey);
From this point on, to open the SQLite database, you’ll need to use the encryption key, so it’s probably a good idea to either save it somewhere, or make sure you can reliably regenerate it, based on some user-provided information, like a username and password. If you’re looking for an SQLite DB admin tool that will allow you to provide an encryption key, I recommend Lita. It’s an AIR app, and all of the encryption stuff works.

openSQLiteConnection Mods

The openSQLiteConnection method has a few random bugs that cause things not to work properly with very large data sets. For instance, the answer to this question in the stock code…

            /* do we need this?
if (openDatabases[dbName])
{
sql.close();
openDatabases[dbName] = false;
}
*/

…is yes:

            /* do we need this?*/
//TG: Yes we do. We want to close and re-open the database connection after we create the schema
//    because the schema is created with the async connection, and after it is created, the sync
//    connection won’t work anymore because the schema will have changed.  RefreshDatabase is the
//    public method that does this.
if (openDatabases[dbName])
{
sql.close();
openDatabases[dbName] = false;
}
Additionally, in the stock code opens up the asynchronous connection to the database before the synchronous connection, which can cause a crash because we can’t guarantee that the async open will be completed by the time the sync open is called, so the database may not exist yet. Reordering the two open statements is a simple fix:

            //TG: Note, we need to do the sync open before the async open because we can’t guarantee
//    that the async will be completed by the time the sync open is called (i.e. the database
//    won’t exist. This seems to mainly be a problem when using encryption. Alterately, we could
//    use the openAsync responder, but re-ordering the two statements works fine.

syncSQL = new SQLConnection();
if(encryptionKey != null)
syncSQL.open(file, SQLMode.CREATE, false, 1024, encryptionKey);
else
syncSQL.open(file);

//open a second connection for asynchronous calls
if(encryptionKey != null)
sql.openAsync(file, SQLMode.CREATE, null, false, 1024, encryptionKey);
else
sql.openAsync(file);

 

SQLite Serialization on OSX

One major problem we ran into recently is that there is a bug with AIR 1.5 on OSX that causes an application to crash when you try to deserialize an object (as opposed to a primitive data type) from an SQLite database. Serializing the object works fine, but trying to run a SELECT statement on a row that has a serialized object in it will cause SQLite to bomb. This means that  some of the fields in the _describe_sobject_cache table will crash an application, namely “childRelationships”, “fields”, and “recordTypeInfos”. Until the AIR bug is fixed, a workaround is to do the serialization yourself, and store the serialized object as a blob. First, you’ll want to modify the CREATE statement in openSQLiteDatabase as follows:

 

            createDescribeSObjectCacheTable.text = “create table if not exists ” + DESCRIBE_SOBJECT_TABLE +
” (id integer primary key autoincrement, activateable boolean, childRelationships text, childRelationshipsBytes blob, createable boolean, custom boolean, ” +
” deletable boolean, fields text, fieldsBytes blob, keyPrefix text, layoutable boolean, label text, labelPlural text, mergeable boolean, name text, queryable boolean, ” +
” recordTypeInfos text, recordTypeInfosBytes blob, replicateable boolean, retrieveable boolean, searchable boolean, undeletable boolean, updateable boolean, urlDetail text, ” +
” urlEdit text, urlNew text)”;

 

Notice the addition of these fields: “childRelationshipsBytes”, “fieldsBytes”, and “recordTypeInfosBytes”. We’ll then want to add a method to serialize any given object:

        //serialize an object…
//this is necessary because with the OSX version of AIR 1.5, apps will crash when you try to deserialize an object
//from the SQLite database.
public function serializeObject(someObject:Object):ByteArray
{
var objectBytes:ByteArray = new ByteArray();
objectBytes.writeObject(someObject);
return objectBytes;
}

And then we need to use our new serializeObject() method in cacheDescribeSObjectResult(). First we need to add those fields to our INSERT and UPDATE statements:

              if (getCachedDescribeSObjectResultByType(d.name) != null) {
// update
dbStatement.text = “update ” + DESCRIBE_SOBJECT_TABLE + ” set activateable = :activateable, childRelationshipsBytes = :childRelationshipsBytes, ” +
“createable = :createable, custom = :custom, deletable = :deletable, fieldsBytes = :fieldsBytes, keyPrefix = :keyPrefix, layoutable = :layoutable, ” +
“label = :label, labelPlural = :labelPlural, mergeable = :mergeable, queryable = :queryable, recordTypeInfosBytes = :recordTypeInfosBytes, ” +
“replicateable = :replicateable, retrieveable = :retrieveable, searchable = :searchable, undeletable = :undeletable, ” +
“updateable = :updateable, urlDetail = :urlDetail, urlEdit = :urlEdit, urlNew = :urlNew  where name = :name”;
} else {
// insert
dbStatement.text = “insert into ” + DESCRIBE_SOBJECT_TABLE + ” (activateable, childRelationshipsBytes, createable, custom, deletable, fieldsBytes, ” +
“keyPrefix, layoutable, label, labelPlural, mergeable, name, queryable, recordTypeInfosBytes, replicateable, retrieveable, ” +
“searchable, undeletable, updateable, urlDetail, urlEdit, urlNew) values (:activateable, :childRelationshipsBytes, :createable, :custom, :deletable, :fieldsBytes, ” +
“:keyPrefix, :layoutable, :label, :labelPlural, :mergeable, :name, :queryable, :recordTypeInfosBytes, :replicateable, :retrieveable, ” +
“:searchable, :undeletable, :updateable, :urlDetail, :urlEdit, :urlNew)”;
}

 

And then instead of this line:

              dbStatement.parameters[“:childRelationships”] = d.childRelationships;
We’ll want to do this:

              dbStatement.parameters[“:childRelationshipsBytes”] = serializeObject(d.childRelationships);            
Same goes for “fields” and “recordTypeInfos”. Ultimately, then, we’ll have to deserialize these objects ourselves too, so in getCachedDescribeSObjectResultByType(), we need to add a few lines to do that:

        private function getCachedDescribeSObjectResultByType(type:String):DescribeSObjectResult {
var dbStatement:SQLStatementExt = new SQLStatementExt(syncSQL);

dbStatement.text = “select * from ” + DESCRIBE_SOBJECT_TABLE + ” where name = :name”;
logger.debug(dbStatement.text);
dbStatement.parameters[“:name”] = type;
dbStatement.itemClass = DescribeSObjectResult;
dbStatement.execute();

var result:SQLResult = dbStatement.getResult();

if ((result != null) && (result.data != null) && (result.data.length > 0)) {
var dsr:DescribeSObjectResult = result.data[0];

                  //TG deserialize:
dsr.fields = dsr.fieldsBytes.readObject();
dsr.childRelationships = dsr.childRelationshipsBytes.readObject();
dsr.recordTypeInfos = dsr.recordTypeInfosBytes.readObject();

if (dsr.fieldMap == null) {
dsr.fieldMap = {};
for each (var f:Object in dsr.fields)
{
dsr.fieldMap[f.name] = f;
}
}
return dsr;
}

return null;
}

 

And then, since the dbStatement result is cast to a type of DescribeSObjectResult, we need to add these three new fields to that class:

     public dynamic class DescribeSObjectResult
{
public var activateable:Boolean;
public var childRelationships:Array;
        public var childRelationshipsBytes:ByteArray;
public var createable:Boolean;
public var custom:Boolean;
public var deletable:Boolean;
public var fields:Array;
        public var fieldsBytes:ByteArray;
public var keyPrefix:String;
public var layoutable:Boolean;
public var label:String;
public var labelPlural:String;
public var mergeable:Boolean;
public var name:String;
public var queryable:Boolean;
public var recordTypeInfos:Array;
        public var recordTypeInfosBytes:ByteArray;

Online Caching

One of the biggest and most useful changes we made to AIRConnection was to make sure queries are reflected in the local database when remote SOQL insert and update statements are made ONline. In the stock AIRConnection class, local inserts and updates are only made when made offline, so if you are querying the local database directly using the public asynchronous or synchronous connection accessor methods, your queries won’t reflect the previous updates you’ve made remotely. This is solved easily for updates by simply doing the update both to SFDC and to the local SQLite database:

       override public function update(sobjects:Array, callback:IResponder):void
{
logger.debug(‘updating ‘ + ObjectUtil.toString(sobjects));
var apex:Connection=super;callback

if (super.loginResult && connected)
{
//online
super.update(sobjects,callback);

                //TG: cache the data locally too
//I thought of adding a switch to update so data isn’t always cached locally, but I can’t think of any reason to do so…
//may as well keep the local data in sync with the remote data.
if ( !syncSQL) { logger.debug(“no open database to store into”); return }
if (sobjects.length > 0)
{
for( var i:int=0; i<sobjects.length; i++ )
{
syncSQL.begin();
var dbCacheStatement:SQLStatementExt = new SQLStatementExt(syncSQL, sobjects[i]);
// create the update statement
dbCacheStatement.text = buildUpdateTableStatement(sobjects[i]);
// write record to table ( assumes table exists)
logger.debug(dbCacheStatement.text);
dbCacheStatement.execute();

syncSQL.commit();

}
}
}
else
{
//offline
// we are offline or not yet logged in
if (!connected) { logger.debug( ‘create made while offline’); }
else if ( ! super.loginResult ) { logger.debug(‘user not yet logged in’); }

if ( !syncSQL) { logger.debug(“no open database to store into”); return }
if (sobjects.length > 0) {
for( var j:int=0; j<sobjects.length; j++ ) {
syncSQL.begin();
var dbStatement:SQLStatementExt = new SQLStatementExt(syncSQL, sobjects[j]);
// create the update statement
dbStatement.text = buildUpdateTableStatement(sobjects[j]);
// write record to table ( assumes table exists)
logger.debug(dbStatement.text);
dbStatement.execute();

// do not add this to the OFFLINE_UPDATES_TABLE if this is a new record
if ((sobjects[j].Id as String).indexOf(NEW_RECORD_TEMP_ID_PREFIX) != 0) {
var insertOfflineUpdate:SQLStatementExt = new SQLStatementExt(syncSQL);
insertOfflineUpdate.text = “insert into ” + OFFLINE_UPDATES_TABLE + ” (table_name, record_id) values (:table_name, :record_id)”;
insertOfflineUpdate.parameters[“:table_name”] = sobjects[j].type;
insertOfflineUpdate.parameters[“:record_id”] = sobjects[j].Id;
logger.debug(insertOfflineUpdate.text);
insertOfflineUpdate.execute();
}

syncSQL.commit();

dispatchEvent(new Event(“updateSyncingOfflineUpdates”));
}
}

// todo: return something more meaningful, but what?
(callback as AsyncResponder).resultHandler({message: OFFLINE_UPDATE_SUCCEEDED });
}
}

Caching of inserts takes a bit more effort because if we’re online, an Id is automatically generated by SFDC, along with any auto-populated fields. So, an API call of create has to be followed by a query in order to make sure the local data is updated properly. This involves hijacking the callback passed in by the parent application:

   //TG: queryAfterCreate is used to automatically query the result from SFDC after the Create operation has
//    completed. This is useful because not only do we pull down the Id of the newly created record, we also
//    pull down any auto-filled fields.
public override function create( sobjects:Array, callback:IResponder, queryAfterCreate:Boolean=false):void {
var apex:Connection=super;

if (super.loginResult && connected)
{
//TG: do we want to pull down the full record from SFDC after we create it? Default is false because that’s how it comes from SFDC
if(!queryAfterCreate)
{
//just do what the Flex lib normally does…
super.create(sobjects,callback);
}
else
{
//TG: hijack the callback to grab the ID from SFDC, so we cache this record locally
super.create(sobjects, new AsyncResponder(
function(result:Object):void
{
for(var i:Number=0;i<result.length;i++)
{
if(result[i].success)
{
//add the object type to the result object so the calling application knows what type of object to refresh
result[i][‘type’]=sobjects[i].type;

trace(“cache this create result”);
//get the ID returned by SFDC, and create the local record
sobjects[i].Id=result[i].id;

//first, we need to get a list of fields to query from the _describe_sobject_cache table
var queryStatement:SQLStatement = new SQLStatement();
queryStatement.sqlConnection = syncSQL;
queryStatement.text = “SELECT fieldsBytes FROM _describe_sobject_cache WHERE name = ‘”+sobjects[i].type+”‘”;
queryStatement.execute();

var fieldsResult:Array = queryStatement.getResult().data;
if(fieldsResult.length > 0)
{
var fields:Object = fieldsResult[0].fieldsBytes.readObject();

//build a list of fields to select from SFDC for this object
var soqlFieldList:String = “”;
var soqlFieldList2:String = “”;
for(var aField:String in fields)
{
//is query length going to be more than 10000?
if((“SELECT Id,”+soqlFieldList+aField+”,”+” FROM “+sobjects[i].type+” WHERE Id = ‘”+result[i].id+”‘”).length<10000)
{
if(aField.toLowerCase() != “id”)
soqlFieldList+=aField + “,”;
}
else
{
//query the rest…
if((“SELECT Id,”+soqlFieldList2+aField+”,”+” FROM “+sobjects[i].type+” WHERE Id = ‘”+result[i].id+”‘”).length<10000)
{
if(aField.toLowerCase() != “id”)
soqlFieldList2+=aField + “,”;
}
else
{
//TODO: make this recursive, so it’s not limited to two queries…
break;
}
}
}
//remove the last comma
soqlFieldList = soqlFieldList.substr(0,soqlFieldList.length-1);

//assemble the soql query string
var soqlQueryStatement:String = “SELECT Id, “+soqlFieldList+” FROM “+sobjects[i].type+” WHERE Id = ‘”+result[i].id+”‘”;

var soqlQueryStatement2:String;
if(soqlFieldList2.length > 0)
{
soqlFieldList2 = soqlFieldList2.substr(0,soqlFieldList2.length-1);
soqlQueryStatement2 = “SELECT Id, “+soqlFieldList2+” FROM “+sobjects[i].type+” WHERE Id = ‘”+result[i].id+”‘”;
}

//query SFDC for the complete record and then trigger the callback the user passed in
query(soqlQueryStatement,new AsyncResponder(
function(queryResult:Object):void
{
//any more to query?
if(soqlFieldList2.length == 0)
{
// call back the original method passed in
callback.result(result);
}
else
{
//more to do…
query(soqlQueryStatement2,new AsyncResponder(
function(queryResult2:Object):void
{
callback.result(result);
},
function(queryResult2:Object):void
{
//the create worked okay, but the query failed, so the local data will be out of sync…
//maybe not the biggest deal in the world, so just return the create result
trace(“Create Query Error: “+queryResult2.faultstring);
callback.result(result);
}
),false);
}
},
function(queryResult:Object):void
{
//the create worked okay, but the query failed, so the local data will be out of sync…
//maybe not the biggest deal in the world, so just return the create result
trace(“Create Query Error: “+queryResult.faultstring);
callback.result(result);
}
),false);

//working insert code, but it’s being replaced with the query above
/*syncSQL.begin();

var dbStatement:SQLStatementExt = new SQLStatementExt(syncSQL, sobjects[i]);

// create the insert statement
dbStatement.text = buildInsertTableStatement(sobjects[i]);

// write record to table ( assumes table exists)
logger.debug(dbStatement.text);

dbStatement.execute();
syncSQL.commit();*/
}
else
{
//no fields to query from SFDC…dunno what happened, so just finish up and act like nothing happened.
trace(“no fields to query from SFDC”);
callback.result(result);
}
}
else
{
callback.fault(result);
}
}
//callback.result(result);         // call back the original method passed in
},
function (info:Object) :void {
callback.fault(info);     // incase this was passed also
}
));
}
}
else
{
// we are offline or not yet logged in
if (!connected) { logger.debug( ‘create made while offline’); }
else if ( ! super.loginResult ) { logger.debug(‘user not yet logged in’); }

if ( !syncSQL) { logger.debug(“no open database to store into”); return }
if (sobjects.length > 0) {
if (tableExists(sobjects[0].type)) {

for( var j:int=0; j<sobjects.length; j++ ) {
// toss objects in
sobjects[j].Id = NEW_RECORD_TEMP_ID_PREFIX + UIDUtil.createUID();   // must be unique to the db

syncSQL.begin();
var dbStatement:SQLStatementExt = new SQLStatementExt(syncSQL, sobjects[j]);
// create the insert statement
dbStatement.text = buildInsertTableStatement(sobjects[j]);
// write record to table ( assumes table exists)
logger.debug(dbStatement.text);
//trace(dbStatement.text);
dbStatement.execute();

var insertOfflineCreate:SQLStatementExt = new SQLStatementExt(syncSQL);
insertOfflineCreate.text = “insert into ” + OFFLINE_CREATES_TABLE + ” (table_name, record_id) values (:table_name, :record_id)”;
insertOfflineCreate.parameters[“:table_name”] = sobjects[j].type;
insertOfflineCreate.parameters[“:record_id”] = sobjects[j].Id;

logger.debug(insertOfflineCreate.text);
insertOfflineCreate.execute();
syncSQL.commit();

dispatchEvent(new Event(“updateSyncingOfflineCreates”));
}
}
}

// todo: return something more meaningful, but what?
(callback as AsyncResponder).resultHandler({message: OFFLINE_CREATE_SUCCEEDED});
}
}      

Anyway, these are just some handy updates that we’ve made recently. Many of them have been made in a bit of a hurry, so they could use some massaging, but hopefully they help somebody out.

Read More