Sunday, January 25, 2015

KirSQLite Flex AIR Database Manager Part 4

In this part well add the ability to open databases that are stored on the hard drive.

Firstly go to the windowMenu XML and set the id for the "Open" XML menuitem to "newdb":

<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>

Now in the menuSelect() function add a new line that responds to the users selection of the "Open" option in the menu:

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

As you can see, I call an openDatabase() function.

Inside of openDatabase(), I first create a new File object and call its browseForOpen() method. I create a FileFilter object to filter database (.db) files. Then I add an Event.SELECT listener to the file:

var file:File = new File();
file.browseForOpen("Open database", [new FileFilter("Databases", "*.db")]);
file.addEventListener(Event.SELECT, openSelect);

The openSelect() function checks if the file exists. If it doesnt, throw out an Alert, saying that the file was not found.

Then check if the file is already open using notAlreadyOpen() function that Ill create later. If the file is already open, throw an alert saying "Database already opened".

If the file exists and not already opened, check if dbDatas length equals 0. If it does, open the database using the connections open() method. Add the XML node to dbData as usual. If dbDatas length is more than 0, use the connections attach() method. Everything here is similar to what happens in newDatabase()...

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

function openSelect(evt:Event):void {
if (file.exists) {
if(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);
}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);
}}else {
Alert.show("Database already opened.", "Error");
}
}else {
Alert.show("File not found.", "Error");
}
}
}

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

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.SQLStatement;
import flash.events.Event;
import flash.filesystem.File;
import flash.net.FileFilter;
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";
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;
newDB.@path = file.nativePath;
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;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
}
}
}

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

function openSelect(evt:Event):void {
if (file.exists) {
if(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);
}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);
}}else {
Alert.show("Database already opened.", "Error");
}
}else {
Alert.show("File not found.", "Error");
}
}
}

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!
Posted by at
Labels: , , , , , ,

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)