Monday, May 7, 2012

KirSQLite - Flex AIR Database Manager: Part 6

In this part we'll improve our SQL Schema reading function.

In the last tutorial, we've created a function called loadDataSchema(). Here, we call the loadSchema() method of our SQLConnection object twice - the first time to see if the schema exists, the second time to read the data.

When we call it the first time, we set a responder for success and error events. If the operation has succeeded, only then we proceed to load the schema for the second time. We did this, because we couldn't read the schema using getSchemaResult() method. But there's a more efficient and quicker way to read the schema data, without loading it twice.

We simply assign the results variable to the event object that was received:

function schemaSuccess(evt:SQLSchemaResult):void {
// Schema found! Now parsing:
var result:SQLSchemaResult = evt;

Now, after the schema is received, we can read its data to add tables as children for the approporiate database nodes in the dbData XMLList. To find out which node we're looking for, let's break the "name" value we have into a number, which will represent the "numid" attribute of the database in the XMLList.

Create a variable and set its value depending on what the "name" is. If the name is set to "main", then the number will equal 1. Otherwise (if the name is "db2", "db3", etc), remove "db" from the name to get the needed number (2, 3, etc).

var nid:Number = (name=="main")?(1):(Number(name.replace("db", "")));

Then we look for that node in the XMLList by checking whether the "numid" attribute's value equals "nid":

var dataNode:XMLList = dbData.db.(@numid == nid);

We need to make sure the node has no children before we add anything to it. To do that, simply set the children of this node to a single placeholder item, and then delete that item:

dataNode.setChildren(<placeholder/>);
delete dataNode.placeholder;

After that, we can create a loop that adds XML children to the dataNode object. Set the label values of the children to the "name" value of its respective SQLTableSchema object in the result.tables array.

for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
dataNode.appendChild(newTable);
}

Full loadDataSchema() function:

private function loadDataSchema(name:String):void {
if (name != "") {
connection.loadSchema(null, null, name, true, new Responder(schemaSuccess, schemaError));
function schemaSuccess(evt:SQLSchemaResult):void {
// Schema found! Now parsing:
var result:SQLSchemaResult = evt;
// Adding tables:
var nid:Number = (name=="main")?(1):(Number(name.replace("db", "")));
var dataNode:XMLList = dbData.db.(@numid == nid);
dataNode.setChildren(<placeholder/>);
delete dataNode.placeholder;
for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
dataNode.appendChild(newTable);
}
}
function schemaError(evt:SQLError):void {
// Alert.show("Database is empty");
}
}
}

The application now displays all tables in an SQLite database.

Full code:

<?xml version="1.0" encoding="utf-8"?>
<s:WindowedApplication xmlns:fx="http://ns.adobe.com/mxml/2009"
                       xmlns:s="library://ns.adobe.com/flex/spark"
                       xmlns:mx="library://ns.adobe.com/flex/mx" showStatusBar="false">
   
<s:menu>
<mx:FlexNativeMenu dataProvider="{windowMenu}" showRoot="false" labelField="@label" keyEquivalentField="@key" itemClick="menuSelect(event);" />
</s:menu>

<fx:Declarations>
<fx:XML id="windowMenu">
<root>
<menuitem label="Database">
<menuitem id="newdb" label="New" key="n" controlKey="true" />
<menuitem id="opendb" label="Open" key="o" controlKey="true" />
<menuitem label="Save" key="s" controlKey="true" />
<menuitem label="Save As.." key="s" controlKey="true" shiftKey="true" />
</menuitem>
<menuitem label="Table">
<menuitem label="Add" key="t" controlKey="true" />
<menuitem label="Edit" key="e" controlKey="true" />
<menuitem label="Delete"/>
</menuitem>
</root>
</fx:XML>
<fx:XMLList id="dbData">
</fx:XMLList>
<mx:ArrayCollection id="tableData">
</mx:ArrayCollection>
</fx:Declarations>

<fx:Script>
<![CDATA[
import flash.data.SQLConnection;
import flash.data.SQLResult;
import flash.data.SQLSchema;
import flash.data.SQLSchemaResult;
import flash.data.SQLStatement;
import flash.errors.SQLError;
import flash.events.Event;
import flash.events.SQLEvent;
import flash.filesystem.File;
import flash.net.FileFilter;
import flash.net.Responder;
import mx.controls.Alert;
import mx.events.FlexNativeMenuEvent;

private var connection:SQLConnection = new SQLConnection();

private function selectAllChange(evt:Event):void {
var i:int;
if (evt.currentTarget.selected) {
for (i = 0; i < tableData.length; i++) {
tableData[i].sel = true;
}
} else
if (!evt.currentTarget.selected) {
for (i = 0; i < tableData.length; i++) {
tableData[i].sel = false;
}
}
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();
}

private function menuSelect(evt:FlexNativeMenuEvent):void {
(evt.item.@id == "newdb")?(newDatabase()):(void);
(evt.item.@id == "opendb")?(openDatabase()):(void);
}

private function newDatabase():void {
var file:File = File.desktopDirectory.resolvePath("Untitled");
file.addEventListener(Event.SELECT, newSelect);
file.browseForSave("Choose where to save the database");
var newDB:XML;
var statement:SQLStatement = new SQLStatement();
function newSelect(evt:Event):void {
file.nativePath += ".db";
var n:String = parseDatabase(file);
loadDataSchema(n);
}
}

private function openDatabase():void {
var file:File = new File();
file.browseForOpen("Open database", [new FileFilter("Databases", "*.db"), new FileFilter("All files", "*")]);
file.addEventListener(Event.SELECT, openSelect);

function openSelect(evt:Event):void {
var n:String = parseDatabase(file, true);
loadDataSchema(n);
}
}

private function loadDataSchema(name:String):void {
if (name != "") {
connection.loadSchema(null, null, name, true, new Responder(schemaSuccess, schemaError));
function schemaSuccess(evt:SQLSchemaResult):void {
// Schema found! Now parsing:
var result:SQLSchemaResult = evt;
// Adding tables:
var nid:Number = (name=="main")?(1):(Number(name.replace("db", "")));
var dataNode:XMLList = dbData.db.(@numid == nid);
dataNode.setChildren(<placeholder/>);
delete dataNode.placeholder;
for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
dataNode.appendChild(newTable);
}
}
function schemaError(evt:SQLError):void {
// Alert.show("Database is empty");
}
}
}

private function parseDatabase(file:File, needCheck:Boolean = false):String {
var ret:String = "";
if (!needCheck || file.exists) {
if(!needCheck || notAlreadyOpen(file)){
var newDB:XML;
if (dbData.db.length() == 0) {
connection.open(file);
dbData = new XMLList(<root></root>);
newDB = <db/>
newDB.@label = file.name;
newDB.@numid = 1;
newDB.@isBranch = true;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
ret = "main";
}else
if (dbData.db.length() > 0) {
var newnum:int = dbData.db.length() + 1;
connection.attach("db"+newnum.toString(), file);
newDB = <db/>
newDB.@label = file.name;
newDB.@numid = newnum.toString();
newDB.@isBranch = true;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
ret = "db" + newnum.toString();
}}else {
Alert.show("Database already opened.", "Error");
}
}else {
Alert.show("File not found.", "Error");
}
return ret;
}

private function notAlreadyOpen(file:File):Boolean{
var r:Boolean = true;
for (var i:int = 0; i < dbData.db.length(); i++) {
if (file.nativePath == dbData.db[i].@path) {
r = false;
}
}
return r;
}
]]>
</fx:Script>

<s:HGroup gap="0" width="100%" height="100%">
<s:VGroup width="200" height="100%" gap="0">
<mx:Tree width="100%" height="100%" dataProvider="{dbData}" showRoot="false" labelField="@label"/>
</s:VGroup>
<s:VGroup width="100%" height="100%" gap="0">
<mx:TabNavigator width="100%" height="100%" paddingTop="0">
<s:NavigatorContent label="Table contents">
<s:VGroup width="100%" height="100%" gap="0">
<mx:HBox width="100%" height="30" paddingLeft="8" paddingTop="6">
<mx:CheckBox label="Select all" change="selectAllChange(event);" />
<s:Button label="Delete selected"/>
</mx:HBox>
<mx:AdvancedDataGrid id="tableGrid" width="100%" height="100%" dataProvider="{tableData}" editable="true">
<mx:columns>
<mx:AdvancedDataGridColumn headerText=" " width="30" sortable="false" draggable="false" resizable="false" editable="false">
<mx:itemRenderer>
<fx:Component>
<mx:Box width="30" horizontalAlign="center">
<mx:CheckBox selected="@{data.sel}" />
</mx:Box>
</fx:Component>
</mx:itemRenderer>
</mx:AdvancedDataGridColumn>
<mx:AdvancedDataGridColumn dataField="idnum" headerText="ID" editable="false" />
<mx:AdvancedDataGridColumn dataField="firstname" headerText="First name" />
<mx:AdvancedDataGridColumn dataField="lastname" headerText="Last name" />
<mx:AdvancedDataGridColumn dataField="age" headerText="Age"/>
</mx:columns>
</mx:AdvancedDataGrid>
</s:VGroup>
</s:NavigatorContent>
<s:NavigatorContent label="Edit columns">

</s:NavigatorContent>
<s:NavigatorContent label="Query">

</s:NavigatorContent>
</mx:TabNavigator>
</s:VGroup>
</s:HGroup>

</s:WindowedApplication>

Thanks for reading!

No comments:

Post a Comment