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:
- "Clean" the SQL statement to keep only the main body
- Isolate and extract each field definition
- Finally search the keywords using another RegExp:
/** * @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)
/** * @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)
- 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
Comments are closed.