Converting the schema of a SQLite AIR database to XML A new AIR admin tool for SQLite databases is coming
Jan 26

I don’t know why but the SQLColumnSchema class of the SQLite API does not return the DEFAULT value of a field, neither its UNIQUE attribute. This kind of information is needed when you have to explore the schema of your AIR database. Here is my solution.

There are probably lots of solutions to solve this issue; the one I chose is quite simple but may be dependant on the SQLite syntax implemented by the tool you used to create the .db file, if any.

For me, to get the DEFAULT value and the UNIQUE attribute of a field is the same as to search a string in the SQL statement of its table. The best way is to use Regular Expressions, RegExp. I didn’t know RegExp before this work and I really think now it is a wonderful and very powerful tool. I mainly used the Ryan Swanson’s RegExpr Explorer, the RegExr tool by GSkinner to help me define a most accurate Regular Expressions. Some googling activities returned of course lots of samples as Regular Expressions seem to be shared by almost all programming languages.

The steps of the extraction:

  1. "Clean" the SQL statement to keep only the main body
  2. /**
    * @private
    * Gets the definition of any kind of structure such as TABLE, VIEW, TRIGGER...
    * The function removes the CREATE TABLE|VIEW|TRIGGER|INDEX name (AS) from the SQL statement and return the remaining string
    * that is considered as the definition of the structure
    * 
    * @param	sql
    * @return
    */
    private function getStructureDefinition( sql:String ):String
    {
       var def:String = sql.replace( /^CREATE\s+\w+\s+(("\w+"|\w+)|\[(.+)\])\s+(\(|AS|)/im , '' );
     
      return def;
    }

    (updated 2009-04-08 with code from the v1.0 of the SQLite Sorcerer)

  3. Isolate and extract each field definition
  4. /**
     * @private
     * Isolate the definition of a field in the table
     * To ease the extraction, the name of the field is passed to the function.
     * This also makes the function more accurate and independent of the structure of the sql
     * 
     * @param	sql		String. The sql statement of the table
     * @param	fieldName	String. The name of the field to be retrieved
     * @return
     */
     private function getField( sql:String, fieldName:String ):String
     {
        var def:String;
     
        sql = getStructureDefinition( sql );
        try 
        {
    	var fieldPattern:RegExp
    	fieldPattern = new RegExp( fieldName + "(.*?)(,|\r|$)", "m" );
     
    	var fields:Array = sql.match( fieldPattern );
     
    	def = fields[0];
        }
        catch (err:TypeError)
        {
    	def = '';
        }
     
        return def;
     }

     (updated 2009-04-08 with code from the v1.0 of the SQLite Sorcerer)

  5. Finally search the keywords using another RegExp:
  • For UNIQUE
private function getUnique( item:String ) :Boolean
{
   var isUnic:Boolean;
   try
   {
      ( item.search( new RegExp(/ UNIQUE/i ))  >= 0 ) ? isUnic = true : isUnic = false;
   }
   catch (err:TypeError)
   {}
 
   return isUnic;
}
  • For DEFAULT
private function getDefault( item:String ) :String
{
   try
   {
      // Finds the default value
       var def:String = item.match( /(DEFAULT (\b\w*)|(\'([^"]*)\')|(\"([^"]*)\"))/ )[0];
 
      // Cuts the DEFAULT keyword
      def = def.replace( /DEFAULT / , '');
   }
   catch (err:TypeError)
   {
      def = '';
   }
 
   return def;
}

 (updated 2009-04-08 with code from the v1.0 of the SQLite Sorcerer)

 

How to implement this ?

First, have a look here, in the function addSchemaItem.

The function loops on all tables, for instance, and then all fields in a table. A XML is then built using all fields attributes provided by the SQLColumnSchema class.

We can now modify the function to build a more complete XML representation of the schema of the database.

BEFORE:

204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
// Builds the node of a table or a view
if ( nodeType=='table' || nodeType=='view' )
{
   // Builds the node of a table or a view
   target.appendChild( <{nodeType} name={schm.name} sql={schm.sql} />);
 
   // Adds children for each field of the table
   var index:Number = 0;
   // so gets the properties of field in the 'columns' property of the SQLXXXSchema
   for (var j:String in schm.columns)
   {
      // Adds a child corresponding to the field
      // For this, the right table has to be found
      // => use of the filter on @name
      target[nodeType].(@name == schm.name).appendChild(
         <field allownull="{schm.columns[j].allowNull}" datatype="{schm.columns[j].dataType}" autoincrement="{schm.columns[j].autoIncrement}" primarykey="{schm.columns[j].primaryKey}" name="{schm.columns[j].name}"></field>
          );
 
      // not added : defaultCollationType={schm.columns[j].defaultCollationType}						      
 
      index++
   }
}

AFTER:

if ( nodeType=='table' || nodeType=='view' )
{
   // Builds the node of a table or a view
   target.appendChild( <{ nodeType } type={nodeType} name={schm.name} sql={schm.sql} /> );
 
   // Adds children for each field of the table
   // so gets the properties of field in  the 'columns' property of the SQLXXXSchema
   for (var j:String in schm.columns)
   {
	// Gets the current field from the SQL statement of the processed table	
        var currentField:String = getField( schm.sql, schm.columns[j].name );
 
	// Builds the nodes of the field.
	// For this, the nodeType (table or view) that has the current name has to be found.
	// => use of the filter on @name
	target[nodeType].(@name == schm.name).appendChild(
         <field name={schm.columns[j].name} 
            primaryKey={schm.columns[j].primaryKey} 
	    autoIncrement={schm.columns[j].autoIncrement}
	    dataType={schm.columns[j].dataType}
	    affinity={getAffinity(schm.columns[j].dataType)}
	    allowNull={schm.columns[j].allowNull}
	    default={getDefault(currentField)}
	    unique={getUnique(currentField)}></field> );
 
	// not added : defaultCollationType = { schm.columns[j].defaultCollationType }
   }
}

At the end, the XML of the field adds a default and a unique attribute. They are built by calling the functions getDefault and getUnique and passing the currentField which is actually the extracted part of the SQL stattement for the table being processed.

RESULT:

  <table type="table" name="tblComments" sql="CREATE TABLE tblComments (
idcomment integer  UNIQUE NOT NULL,
date nvarchar DEFAULT CURRENT_DATE NOT NULL,
comment nvarchar(12) DEFAULT 'Cliquez ici' NULL,
idclient integer  NOT NULL
)">
   <field name="idcomment" primarykey="false" autoincrement="false" datatype="integer" allownull="false" default="" unique="true"></field>
   <field name="date" primarykey="false" autoincrement="false" datatype="nvarchar" allownull="false" default="CURRENT_DATE" unique="false"></field>
   <field name="comment" unique="false" primarykey="false" autoincrement="false" datatype="nvarchar(12)" allownull="true" default="Cliquez ici"></field>
   <field name="idclient" primaryKey="false" autoIncrement="false" dataType="integer" allowNull="false" default="" unique="false"></field>
</table>

 

Now you can complete your own SQLite explorer tool… mine is coming soon

Related posts

Written by Arnaud
Creative Commons License
Tags: , ,

Share/Save/Bookmark

Help me improve my blog by rating this post or sending a comment.

not goodquite goodgoodvery goodexcellent (No Ratings Yet)
Loading ... Loading ...


Comments are closed.