Friday, May 4, 2012

KirSQLite - Flex AIR Database Manager: Part 3

In this tutorial we will add some very basic functionality to our program. We will add the ability to create new databases and save them.

First of all we'll nede to get rid of the 2 placeholder data holders in the Declarations tags - testData and testTree. Create new XMLList and ArrayCollection in their place, name them dbData and tableData. Also, Set the first menu item's "New" id to "newdb" in the windowMenu XML:

<fx:Declarations>
<fx:XML id="windowMenu">
<root>
<menuitem label="Database">
<menuitem id="newdb" label="New" key="n" controlKey="true" />
<menuitem 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>

Now find the FlexNativeMenu declaration line and set its itemClick event handler to menuSelect(event):

<s:menu>
<mx:FlexNativeMenu dataProvider="{windowMenu}" showRoot="false" labelField="@label" keyEquivalentField="@key" itemClick="menuSelect(event);" />
</s:menu>

Before we continue, let's get rid of the temporary data objects' traces. Change the Tree object's dataProvider to dbData:

<mx:Tree width="100%" height="100%" dataProvider="{dbData}" showRoot="false" labelField="@label"/>

And AdvancedDataGrid's to tableData:

<mx:AdvancedDataGrid id="tableGrid" width="100%" height="100%" dataProvider="{tableData}" editable="true">

Now go to the script tags and in the selectAllChange() function change all "testData" to "tableData":

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();
}

Now declare a connection variable:

private var connection:SQLConnection = new SQLConnection();

Create a new function menuSelect(), which checks the selected item's id. If it is "newdb", call newDatabase():

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

In the newDatabase() function first of all we create a new File object and call its browseForSave() method. We set a listener for its Event.SELECT event and declare 2 variables - newDB (an XML) and statement (an SQLStatement):

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();

Now inside the function, we add another function - newSelect(). This one is called when the user has selected the save location.

Inside of it, we add ".db" to the end of the file's native path.

Then we check if this is the first database that is opened. If it is, we use the connection's open() method and pass the file object as the parameter. We create a temporary SQL statement to create a new blank table - just for the test. You won' be able to see the table in this tutorial, but you'll see that the size of the database on hard drive increased, which means that the table was added.

After the statement is executed, we set dbData to a new XMLList() object, which contains just root tags. After the root tags are added, we use the "newDB" variable to create a new XML element and add it to the XMLList. We specify the following properties for the object: label, numid and isBranch. Set label to file.name, numid to 1 and isBranch to true. Then append the XML child to the dbData object.

Now, this all hapepns when there were no databases previously opened. If the dbData object is not empty, then we execute a little bit different code.

First of all we declare a "newnum" variable which holds the new "numid" for the object (this property will be needed later to tell apart the databases). Newnum's value is set to the current number of databases opened +1.

Instead of calling connection's open() method, we call an attach() method, specify a name ("db" + newnum) and the file object as parameters.

Then we execute an SQL statement. This statement is the same as the previous one, except that the database which we create the table in is different. We can refer to the first database using main. prefix, and to all other databases using its name with a dot (for example, "db2."). After the dot we specify the table name, for example, "main.test" will create a "test" table in the main (first) database.

After the statement, we create a new XML object to append to the dbData object.

The full function looks like this:

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";
if (dbData.db.length() == 0) {
connection.open(file);
statement.sqlConnection = connection;
statement.text = "CREATE TABLE IF NOT EXISTS main.test (id INTEGER PRIMARY KEY AUTOINCREMENT)";
statement.execute();
dbData = new XMLList(<root></root>);
newDB = <db/>
newDB.@label = file.name;
newDB.@numid = 1;
newDB.@isBranch = true;
dbData[0].appendChild(newDB);
}else
if (dbData.db.length() > 0) {
var newnum:int = dbData.db.length() + 1;
connection.attach("db"+newnum.toString(), file);
statement.sqlConnection = connection;
statement.text = "CREATE TABLE IF NOT EXISTS db" + newnum.toString() + ".test (id INTEGER PRIMARY KEY AUTOINCREMENT)";
statement.execute();
newDB = <db/>
newDB.@label = file.name;
newDB.@numid = newnum.toString();
newDB.@isBranch = true;
dbData[0].appendChild(newDB);
}
}
}

You can see that the database files are saved automatically. You can also see that their size changes (to around 3kb) after we insert a table there.

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 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.SQLStatement;
import flash.events.Event;
import flash.filesystem.File;
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);
}

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";
if (dbData.db.length() == 0) {
connection.open(file);
statement.sqlConnection = connection;
statement.text = "CREATE TABLE IF NOT EXISTS main.test (id INTEGER PRIMARY KEY AUTOINCREMENT)";
statement.execute();
dbData = new XMLList(<root></root>);
newDB = <db/>
newDB.@label = file.name;
newDB.@numid = 1;
newDB.@isBranch = true;
dbData[0].appendChild(newDB);
}else
if (dbData.db.length() > 0) {
var newnum:int = dbData.db.length() + 1;
connection.attach("db"+newnum.toString(), file);
statement.sqlConnection = connection;
statement.text = "CREATE TABLE IF NOT EXISTS db" + newnum.toString() + ".test (id INTEGER PRIMARY KEY AUTOINCREMENT)";
statement.execute();
newDB = <db/>
newDB.@label = file.name;
newDB.@numid = newnum.toString();
newDB.@isBranch = true;
dbData[0].appendChild(newDB);
}
}
}
]]>
</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