Happy New Year 2009 ! How to get the DEFAULT value and UNIQUE attribute of a field in a SQLite database using AIR
Jan 03

Converting the schema of a SQLite AIR database to XML

Adobe AIRAdd comments

The SQLite API that is provided by AIR has a very interesting and powerful feature: the loadSchema() method that returns the whole structure of a SQLite database as a SQLSchemaResult class.

The structure of the SQLSchemaResult can be illustrated by the following schema.Structure of the SQLSchemaResult class

This class is composed by 4 arrays: tables containing SQLTableSchema classes, views containing SQLViewSchemas, triggers for SQTriggerSchema and indices for SQLIndicesSchema.

SQLTableSchema and SQLViewSchema contain an array of SQLColumnSchema classes. Each SQLColumnSchema represents the fields in the tables (or views) of the database. This last class has properties as names, allowNull, primaryKey, dataType… that describes the field in the SQLite database.

As I am working on an AIR application that needs to analyze the structure of SQLite databases, I decided to convert the SQLSchemaResult to XML and then play with it. I think that it is really easier to perform the task that the application has to execute (the easiest way being the use of the HierarchicalData class but…).

The principle is to parse each level and each array of the structure above. And as the generated XML has to be presented in a DataGrid, the required properties of the differents classes have to be turned into attributes of XMLLists.

I also wanted to get a big XML, embedding the whole structure and also the XML for only one type of element (only the tables Array for instance). And this, using only 1 function for the conversion.

The conversion process follows the schema above, in a bottom-up way. But to explain it, I will keep the top-down approach.

1- A schemaToXML function is called to convert the whole structure

  • the root XML is initialized,
  • the functions to convert each kind of elements (tables, views…) are called. These functions return a XMLList that is directly added the the root XML.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

// Result of the loadSchema() method,
private var _schema:SQLSchemaResult;
 
public function schemaToXML():XML {
  //Build the initial XML
  var xmlSchm:XML = new XML(<main name="main"></main>);
 
  // Adds the structure of all tables
  xmlSchm.appendChild( tablesToXML( _schema.tables ) );
 
  // Adds the structure of all views
  xmlSchm.appendChild( viewsToXML( _schema.views ) );
 
  // Adds the structure of all triggers
  xmlSchm.appendChild( triggersToXML( _schema.triggers ) );
 
  // Adds the structure of all indices
  xmlSchm.appendChild( indicesToXML( _schema.indices ) );
 
  return xmlSchm;
}

2- The function (tablesToXML, viewsToXML…) that converts only one kind of item has the same structure:

  • the head is initialized
  • the function that parses the schema is called. This function adds nodes to the passed xml.
1
2
3
4
5
6
7

public function tablesToXML( arr:Array ) :XMLList
{
   var xml:XMLList = new XMLList(<tables name="tables"></tables>);
   addSchemaItem( xml, arr );
 
   return xml;
}

3- The function addSchemaItem checks and parses the different kinds of schema to add all fields and their attributes as the XMLList

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

/*
* Takes each kind of SQLSchema (SQLTableSchema, SQLViewSchema, SQLTriggerSchema, SQLIndexSchema)
* and converts it to a formatted XML:
*  - 1 node for each element (ie, 1 table = 1 node, 1 trigger = 1 node)
*  - 1 child node for each field in a table
*  - each parameter of a field (data type, primary key) is turned to an attribute
*/
private function addSchemaItem( target:XMLList, items: Array ):void
{
   var nodeType:String;
 
   for (var i:String in items)
   {
      var schm:*;
 
      // Checks the type of the passed array and assigns the corresponding nodeType
      if (items[i] is SQLTableSchema) 
      {
	nodeType = 'table';
	schm= items[i] as SQLTableSchema;
      }
      if (items[i] is SQLTriggerSchema)
      {
	nodeType = 'trigger';
	schm= items[i] as SQLTriggerSchema;
      }
      if (items[i] is SQLViewSchema)
      {
	nodeType = 'view';
	schm= items[i] as SQLViewSchema;
      }
      if (items[i] is SQLIndexSchema)
      {
	nodeType = 'index';
	schm= items[i] as SQLIndexSchema;
      }
 
      // Builds the node of a trigger or a index, with the additional 'table' attribute
      if ( nodeType=='trigger' || nodeType=='index' )
      {
	target.appendChild( <{nodeType} name={schm.name} sql={schm.sql} table={schm.table} />);
      }
 
      // 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 name="{schm.columns[j].name}" primarykey="{schm.columns[j].primaryKey}" autoincrement="{schm.columns[j].autoIncrement}" datatype="{schm.columns[j].dataType}" allownull="{schm.columns[j].allowNull}"/>
            );
	   // not added : defaultCollationType={schm.columns[j].defaultCollationType}
 
	   index++
	}
      }
   }
}

Here is the beginning of the generated XML, including the system table xp_proc:

<main name="main">
  <tables name="tables">
    <table name="xp_proc" sql="CREATE TABLE xp_proc 
(
      view_name VARCHAR(20) PRIMARY KEY,
      param_list VARCHAR(255),
      xSQL TEXT,
      def_param VARCHAR(255),
      opt_param VARCHAR(255),
      comment TEXT
)">
      <field name="view_name" primaryKey="true" autoIncrement="false" dataType="VARCHAR(20)" allowNull="false" default="" unique="false"/>
      <field name="param_list" primaryKey="false" autoIncrement="false" dataType="VARCHAR(255)" allowNull="true" default="" unique="false"/>
      <field name="xSQL" primaryKey="false" autoIncrement="false" dataType="TEXT" allowNull="true" default="" unique="false"/>
      <field name="def_param" primaryKey="false" autoIncrement="false" dataType="VARCHAR(255)" allowNull="true" default="" unique="false"/>
      <field name="opt_param" primaryKey="false" autoIncrement="false" dataType="VARCHAR(255)" allowNull="true" default="" unique="false"/>
      <field name="comment" primaryKey="false" autoIncrement="false" dataType="TEXT" allowNull="true" default="" unique="false"/>
    </table>
...
  </tables>
</main>

I hope this is clear. Don’t hesitate to ask for more information.

I also found a way to add the DEFAULT value of fields and their UNIQUE attributes (these 2 essential attributes are not provided by the SQLColumnSchema class of the AIR framework). But I keep this for an other post, 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 (1 votes, average: 5 out of 5)
Loading ... Loading ...


Comments are closed.