Mind the Flex

Updating the existing Air database

The article offers one possible solution what to do with an existing local databases when you have to change the table structure? Sooner or later you happen to be in an Air project where you have programmed an excellent application that stores all the user related data in a local database. Suddenly, a new excellent idea how to extend the application comes to your mind but the bad thing is that it does not fit into the existing database structure. In case you are in the development phase, you can of course drop the tables and start happily from the beginning but what if a lot of people are already using your application and you don't want to frustrate them with a message that they have to delete the locale database or run manually a database update.

We will create an application where we use a user profile that is stored in a local database. The user profile has at the moment of creation two attributes, gender and age. At some point your boss decides that it is necessary to add also the name attribute to the user profile. As the application is already distributed to thousands of users, you cannot just drop the database. There are multiple classes like SQLSchemaResult that can help you to solve this problem.

The first and simplest one is to just execute an alter table command. Let's suppose that the version 1 of the application looked like this:

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml"
                  layout="absolute"
                  creationComplete="creationCompleteHandler()">
   <mx:Script>
  <![CDATA[
     private const DATABASE_NAME:String="updateExample";
     private var createProfileTableTxt:String="create table if not exists
                             userprofile(id integer primary key autoincrement, gender text, age integer);";
     private var sqlconnection:SQLConnection;
 
     private function creationCompleteHandler():void {
        createDatabase();
     }
 
     private function createDatabase():void {
        var file:File=File.userDirectory.resolvePath(DATABASE_NAME);
        sqlconnection=new SQLConnection();
        sqlconnection.addEventListener(SQLEvent.OPEN, buildDatabaseTables);
        sqlconnection.open(file, SQLMode.CREATE);
        sqlconnection.loadSchema();
     }
 
     private function buildDatabaseTables(event:Event):void {
        createUserProfileTable();
 
        function createUserProfileTable():void {
              var stmtCreateUserProfileTable:SQLStatement;
              stmtCreateUserProfileTable=new SQLStatement();
              stmtCreateUserProfileTable.sqlConnection=sqlconnection;
              stmtCreateUserProfileTable.text=createProfileTableTxt;
              stmtCreateUserProfileTable.execute();
           }
     }
  ]]>
   </mx:Script>
</mx:WindowedApplication>

According to the new business requirement you have to add the name attribute to the existing table. What you will do next is to add the method call alterTableForVersion2 to the end of method createUserProfileTable. All the method alterTableForVersion2 does is querying the database structure from database. By traversing the tables we will find the one we are intersted in and currently is is "userprofile" and by traversing its columns we find that there is no column "name". The last step is the execution of the alter statement which modifies the table so you could add the "name" attribute also to your actionscript objects.

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml"
                  layout="absolute"
                  creationComplete="creationCompleteHandler()">
   <mx:Script>
  <![CDATA[
     private const DATABASE_NAME:String="updateExample";
     private var createProfileTableTxt:String="create table if not exists
                     userprofile(id integer primary key autoincrement, gender text, age integer);";
     private var alterProfileTableTxt:String="alter table userprofile add column name text";
     private var sqlconnection:SQLConnection;
 
     private function creationCompleteHandler():void {
        createDatabase();
     }
 
     private function createDatabase():void {
        var file:File=File.userDirectory.resolvePath(DATABASE_NAME);
        sqlconnection=new SQLConnection();
        sqlconnection.addEventListener(SQLEvent.OPEN, buildDatabaseTables);
        sqlconnection.open(file, SQLMode.CREATE);
     }
 
     private function buildDatabaseTables(event:Event):void {
        createUserProfileTable();
 
        function createUserProfileTable():void {
              var stmtCreateUserProfileTable:SQLStatement;
              stmtCreateUserProfileTable=new SQLStatement();
              stmtCreateUserProfileTable.sqlConnection=sqlconnection;
              stmtCreateUserProfileTable.text=createProfileTableTxt;
              stmtCreateUserProfileTable.execute();
              alterTableForVersion2();
           }
 
        function alterTableForVersion2():void {
        	 sqlconnection.loadSchema();
              var schema:SQLSchemaResult=sqlconnection.getSchemaResult();
              var nameFieldExists:Boolean=false;
 
              for each(var currentTable:SQLTableSchema in schema.tables) {
                 if (currentTable.name == "userprofile") {
                    for each(var currentColumn:SQLColumnSchema in currentTable.columns) {
                       if (currentColumn.name == "name") {
                          nameFieldExists = true;
                       }
                    }
                 }
              }
 
              if (!nameFieldExists) {
                 alterUserProfileTable();
              }
           }
 
        function alterUserProfileTable():void {
              var stmtAlterUserProfileTable:SQLStatement;
              stmtAlterUserProfileTable=new SQLStatement();
              stmtAlterUserProfileTable.sqlConnection=sqlconnection;
              stmtAlterUserProfileTable.text=alterProfileTableTxt;
              stmtAlterUserProfileTable.execute();
           }
     }
  ]]>
   </mx:Script>
</mx:WindowedApplication>