Sunday, June 17, 2012

KirSQLite - Flex AIR Database Manager: Part 47

In this tutorial we will fix a few problems in our application and make it more stable.

The first issue we're going to deal with is related to the "isTreeEmpty" variable.

The problem is that right now this variable is set to false whenever a database is opened. However, all of the buttons that have their enabled properties bound to this value actually require a table to be present, not just a database. So, in loadDataSchema(), put the line that sets isTreeEmpty to false inside the first for..loop:

for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
newTable.@isBranch = false;
newTable.@databaseName = name;
newTable.@icon = "iconTable";
newTable.@type = "table";
dataNode.appendChild(newTable);
isTreeEmpty = false;
}

Set isTreeEmpty to true and call refreshEverything in dropTableSuccess() function of dropTable():

private function dropTable():void {
Alert.show("Are you sure you want to completely delete this table?", "Drop table?", Alert.YES | Alert.NO, null, dropConfirm);
function dropConfirm(evt:CloseEvent):void {
if (evt.detail == Alert.YES) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
lastStatement(stat.text);
stat.execute( -1, new Responder(dropTableSuccess, dropTableError));
}
}
function dropTableSuccess(evt:SQLResult):void {
isTreeEmpty = true;
refreshEverything();
tableData = new ArrayCollection([]);
}
function dropTableError(evt:SQLError):void {
Alert.show("ERROR:" + evt.details, "Error");
}
}

This is fixed. Now return to loadDataSchema(). Right now the code responsible for deleting all children in a node is located in the success responder of the loadSchema() method. This basically means that this action will only be performed if there is something in the schema. When the user deletes the last table in a database, however, the schema will not succeed, since the database is empty. So the table won't be removed from the XML.

To fix this, move that code outside of schemaSuccess() function:

private function loadDataSchema(name:String):void {
if (name != "") {
// Adding tables:
var nid:Number = (name == "main")?(1):(Number(name.replace("db", "")));
// Delete all children
var dataNode:XMLList = dbData.db.(@numid == nid);
dataNode.setChildren(<placeholder/>);
delete dataNode.placeholder;
connection.loadSchema(null, null, name, true, new Responder(schemaSuccess, schemaError));
function schemaSuccess(evt:SQLSchemaResult):void {
// Schema found! Now parsing:
var result:SQLSchemaResult = evt;

for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
newTable.@isBranch = false;
newTable.@databaseName = name;
newTable.@icon = "iconTable";
newTable.@type = "table";
dataNode.appendChild(newTable);
isTreeEmpty = false;
}
// Adding views
for (var v:int = 0; v < result.views.length; v++) {
var newView:XML = new XML(<view/>);
newView.@label = result.views[v].name;
newView.@isBranch = false;
newView.@databaseName = name;
newView.@icon = "iconView";
newView.@type = "view";
dataNode.appendChild(newView);
}
// Adding triggers
for (var t:int = 0; t < result.triggers.length; t++) {
var newTrigger:XML = new XML(<trig/>);
newTrigger.@label = result.triggers[t].name;
newTrigger.@isBranch = false;
newTrigger.@databaseName = name;
newTrigger.@icon = "iconTrigger";
newTrigger.@type = "trigger";
dataNode.appendChild(newTrigger);
}
// Adding indices
for (var u:int = 0; u < result.indices.length; u++) {
var newIndex:XML = new XML(<ind/>);
newIndex.@label = result.indices[u].name;
newIndex.@isBranch = false;
newIndex.@databaseName = name;
newIndex.@icon = "iconIndex";
newIndex.@type = "index";
dataNode.appendChild(newIndex);
}
}
function schemaError(evt:SQLError):void {
// Alert.show("Database is empty");
}
isTableSelected = false;
isViewSelected = false;
isIndexSelected = false;
isTriggerSelected = false;
}
}

You can also see that I set isViewSelected, isIndexSelected and isTriggerSelected to false in the end of the function, just like isTableSelected is.

The next problem is related to attaching new databases. The problem lies in the fact that some databases may only be opened and function properly as main databases. Attaching them will cause an error and break the program. This is not something we can fix, but we can prevent the program from breaking and warn the user of the error.

Go to parseDatabase() and add a Responder for the attach() method. Execute the rest of the function inside the success responder. Display the error incase of an error.

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 + "(main)";
newDB.@name = 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, new Responder(attachSuccess, attachError));
function attachSuccess():void {
newDB = <db/>
newDB.@label = file.name + "(db" + newnum.toString() + ")";
newDB.@name = file.name;
newDB.@numid = newnum.toString();
newDB.@isBranch = true;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
ret = "db" + newnum.toString();
}
function attachError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}}else {
Alert.show("Database already opened.", "Error");
}
}else {
Alert.show("File not found.", "Error");
}
return ret;
}

That's all for today.

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"
                       xmlns:custom="*">
   
<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" enabled="{enableEverything}">
<menuitem id="newdb" label="New" key="n" controlKey="true"/>
<menuitem id="opendb" label="Open" key="o" controlKey="true"/>
<menuitem id="savedb" label="Save a copy" key="s" controlKey="true" enabled="{tableTree.selectedItems.length>0}"/>
</menuitem>
<menuitem label="Table" enabled="{enableEverything}">
<menuitem id="newtable" label="Add table" key="t" controlKey="true" enabled="{tableTree.selectedItems.length>0}"/>
<menuitem id="droptable" label="Drop table" controlKey="true" enabled="{isTableSelected}"/>
<menuitem id="renametable" label="Rename table" controlKey="true" enabled="{isTableSelected}"/>
<menuitem id="copytable" label="Copy table" controlKey="true" enabled="{isTableSelected}"/>
<menuitem id="jointable" label="Join table" controlKey="true" enabled="{isTableSelected}"/>
<menuitem id="addindex" label="Add index" controlKey="true" enabled="{!isTreeEmpty}"/>
<menuitem id="addview" label="Add view" controlKey="true" enabled="{!isTreeEmpty}"/>
<menuitem id="addtrigger" label="Add trigger" controlKey="true" enabled="{!isTreeEmpty}"/>
</menuitem>
</root>
</fx:XML>
<fx:XMLList id="dbData">
</fx:XMLList>
<mx:ArrayCollection id="tableData">
</mx:ArrayCollection>
<mx:ArrayCollection id="columnData">
</mx:ArrayCollection>
<mx:ArrayCollection id="resultData">
</mx:ArrayCollection>
<mx:ArrayCollection id="databaseData">
</mx:ArrayCollection>
<mx:ArrayCollection id="allTables">
</mx:ArrayCollection>
<mx:ArrayCollection id="joinColumns">
</mx:ArrayCollection>
<mx:ArrayCollection id="indexTableColumns">
</mx:ArrayCollection>
<mx:ArrayCollection id="triggerTableColumns">
</mx:ArrayCollection>
<mx:ArrayCollection id="triggerTables">
</mx:ArrayCollection>
<mx:ArrayCollection id="conflictTypes">
<fx:String>---</fx:String>
<fx:String>ABORT</fx:String>
<fx:String>FAIL</fx:String>
<fx:String>IGNORE</fx:String>
<fx:String>ROLLBACK</fx:String>
<fx:String>REPLACE</fx:String>
</mx:ArrayCollection>
<mx:ArrayCollection id="dataTypes">
<fx:String>NONE</fx:String>
<fx:String>INTEGER</fx:String>
<fx:String>TEXT</fx:String>
<fx:String>REAL</fx:String>
<fx:String>NUMERIC</fx:String>
</mx:ArrayCollection>
<mx:ArrayCollection id="triggerKeywordOptions">
<fx:String>BEFORE </fx:String>
<fx:String>AFTER </fx:String>
<fx:String>INSTEAD OF </fx:String>
<fx:String> </fx:String>
</mx:ArrayCollection>
<mx:ArrayCollection id="triggerActionOptions">
<fx:String>DELETE </fx:String>
<fx:String>INSERT </fx:String>
<fx:String>UPDATE </fx:String>
</mx:ArrayCollection>
<mx:AdvancedDataGridColumn id="checkboxColumn" 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:TitleWindow id="newTableWindow" title="Create new table" close="closeNewTableWindow();" showCloseButton="true">
<s:VGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table name: </s:Label>
<s:TextInput id="newTableName" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Key column: </s:Label>
<s:TextInput id="keyName" />
</s:HGroup>
<s:Button click="createNewTable();" label="Create" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="newRecordWindow" title="Add new record" close="closeNewRecordWindow();" showCloseButton="true">
<s:VGroup width="100%" height="100%">
<mx:AdvancedDataGrid id="recordDataGrid" width="100%" height="50" editable="true"/>
<s:Button click="addNewRecord();" label="Add record" width="100%"/>
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="historyWindow" title="SQL History" close="closeHistoryWindow();" showCloseButton="true">
<mx:Box width="100%" height="100%" paddingLeft="10" paddingRight="10" paddingTop="10" paddingBottom="10">
<s:TextArea id="historyText" width="100%" height="100%" editable="false" />
</mx:Box>
</mx:TitleWindow>
<mx:TitleWindow id="renameTableWindow" title="Rename table" close="closeRenameTableWindow();" showCloseButton="true">
<s:VGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table name: </s:Label>
<s:TextInput id="renameTableName" />
</s:HGroup>
<s:Button click="doRenameTable();" label="Rename" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="copyTableWindow" title="Copy table" close="closeCopyTableWindow();" showCloseButton="true">
<s:VGroup paddingTop="8">
<s:Label id="selectedCopyTable" />
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Destination database: </s:Label>
<mx:ComboBox editable="false" id="copyDestinationDatabase" dataProvider="{databaseData}" labelField="name" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>New table name: </s:Label>
<s:TextInput id="copyTableName" />
</s:HGroup>
<s:Button click="doCopyTable();" label="Copy" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="joinTableWindow" title="Join two tables" close="closeJoinTableWindow();" showCloseButton="true">
<s:VGroup paddingTop="4">
<s:Label id="selectedJoinTable" />
<mx:ComboBox editable="false" id="joinTableCombo" dataProvider="{allTables}" labelField="name" width="100%" />
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>New table name: </s:Label>
<s:TextInput id="joinTableName" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Destination database: </s:Label>
<mx:ComboBox editable="false" id="joinDestinationDatabase" dataProvider="{databaseData}" labelField="name" />
</s:HGroup>
<s:Button click="doJoinTable();" label="Join" width="100%" enabled="{joinTableCombo.selectedIndex>-1}" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="joinTableColumnsWindow" title="Choose which columns to leave" close="closeJoinTableColumnsWindow();" showCloseButton="true">
<s:VGroup paddingTop="4">
<mx:AdvancedDataGrid id="joinColumnsGrid" width="500" height="300" dataProvider="{joinColumns}" editable="false">
<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="fullName" headerText="Column" />
<mx:AdvancedDataGridColumn dataField="tableName" headerText="Table" width="90" />
</mx:columns>
</mx:AdvancedDataGrid>
<s:Button click="doJoinColumnsTable();" label="Join" width="100%"/>
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="indexWindow" title="Add a new index" close="closeIndexWindow();" showCloseButton="true">
<s:VGroup paddingTop="4">
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>New index name: </s:Label>
<s:TextInput id="indexName" />
</s:HGroup>
<mx:CheckBox label="Unique" id="indexUnique"/>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table: </s:Label>
<mx:ComboBox editable="false" id="indexTableCombo" dataProvider="{allTables}" labelField="name" width="100%" change="updateIndexTableColumns();"/>
</s:HGroup>
<s:Label>Column(s):</s:Label>
<s:List allowMultipleSelection="true" width="100%" height="160" id="indexColumnList" dataProvider="{indexTableColumns}" />
<s:Button click="doNewIndex();" label="Add index" width="100%" enabled="{indexColumnList.selectedIndex>-1}" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="viewWindow" title="Add a new view" close="closeViewWindow();" showCloseButton="true" width="400">
<s:VGroup paddingTop="4" width="100%">
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>View name: </s:Label>
<s:TextInput id="viewName" width="100%" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Destination database: </s:Label>
<mx:ComboBox editable="false" id="viewDestinationDatabase" dataProvider="{databaseData}" labelField="name" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Select statement: </s:Label>
<s:TextInput id="viewSelect" width="100%" />
</s:HGroup>
<s:Button click="doNewView();" label="Add view" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="triggerWindow" title="Add a new trigger" close="closeTriggerWindow();" showCloseButton="true" width="400">
<s:VGroup paddingTop="4" width="100%">
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Trigger name: </s:Label>
<s:TextInput id="triggerName" width="100%" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Trigger database: </s:Label>
<mx:ComboBox editable="false" id="triggerDatabase" dataProvider="{databaseData}" labelField="name" change="updateTriggerTables();" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table: </s:Label>
<mx:ComboBox editable="false" id="triggerTable" dataProvider="{triggerTables}" labelField="name" change="updateTriggerTableColumns();" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Keyword: </s:Label>
<mx:ComboBox editable="false" id="triggerKeyword" dataProvider="{triggerKeywordOptions}"/>
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Action: </s:Label>
<mx:ComboBox editable="false" id="triggerAction" dataProvider="{triggerActionOptions}"/>
</s:HGroup>
<s:Label>Optional: UPDATE column(s): </s:Label>
<s:List enabled="{triggerAction.selectedIndex==2}" allowMultipleSelection="true" width="100%" height="100" id="triggerColumnList" dataProvider="{triggerTableColumns}" />
<s:CheckBox id="triggerForEachRow" label="FOR EACH ROW" />
<s:Label>Optional: WHEN</s:Label>
<s:TextArea id="triggerWhen" width="100%" height="50" />
<s:Label>BEGIN</s:Label>
<s:TextArea id="triggerBegin" width="100%" height="100" />
<s:Label>END</s:Label>
<s:Button click="doNewTrigger();" label="Add trigger" width="100%" />
</s:VGroup>
</mx:TitleWindow>
</fx:Declarations>

<fx:Script>
<![CDATA[
import flash.data.SQLCollationType;
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.MouseEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;
import flash.net.FileFilter;
import flash.net.FileReference;
import flash.net.Responder;
import flash.ui.ContextMenu;
import flash.ui.ContextMenuItem;
import mx.collections.ArrayCollection;
import mx.controls.advancedDataGridClasses.AdvancedDataGridColumn;
import mx.controls.Alert;
import mx.events.CloseEvent;
import mx.events.FlexNativeMenuEvent;
import mx.events.IndexChangedEvent;
import mx.managers.PopUpManager;

[Bindable]
[Embed("../lib/table.png")]
public var iconTable:Class;

[Bindable]
[Embed("../lib/key.png")]
public var iconIndex:Class;

[Bindable]
[Embed("../lib/eye.png")]
public var iconView:Class;

[Bindable]
[Embed("../lib/flag.png")]
public var iconTrigger:Class;

private var connection:SQLConnection = new SQLConnection();
[Bindable]
private var selectedDatabase:String = "";
[Bindable]
private var isTableSelected:Boolean = false;
[Bindable]
private var isIndexSelected:Boolean = false;
[Bindable]
private var isViewSelected:Boolean = false;
[Bindable]
private var isTriggerSelected:Boolean = false;
private var sqlHistory:Array = [];
private var recordColumnNames:Array = [];
private var recordColumnNamesFull:Array = [];
[Bindable]
private var fullSelectedTable:String = "";
[Bindable]
private var enableEverything:Boolean = true;
[Bindable]
private var isTreeEmpty:Boolean = true;

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 {
if(enableEverything){
(evt.item.@id == "newdb")?(newDatabase()):(void);
(evt.item.@id == "opendb")?(openDatabase()):(void);
(evt.item.@id == "newtable")?(newTable()):(void);
(evt.item.@id == "droptable")?(dropTable()):(void);
(evt.item.@id == "savedb")?(saveCopy()):(void);
(evt.item.@id == "renametable")?(renameTable()):(void);
(evt.item.@id == "copytable")?(copyTable()):(void);
(evt.item.@id == "jointable")?(joinTable()):(void);
(evt.item.@id == "addindex")?(newIndex()):(void);
(evt.item.@id == "addview")?(newView()):(void);
(evt.item.@id == "addtrigger")?(newTrigger()):(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 {
if (file.exists) {
Alert.show("File already exists, cannot overwrite.", "Nope");
return;
}
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 saveCopy():void {
var databasePath:String;
if (selectedDatabase == "main") databasePath = dbData.db[0].@path;
if (selectedDatabase != "main") {
var newNum:int = Number(selectedDatabase.replace("db", ""));
var newInd:int;
for (var i:int = 0; i < dbData.db.length(); i++) {
if (dbData.db[i].@numid == newNum) {
newInd = i;
break;
}
}
databasePath = dbData.db[newInd].@path;
}
var file:File = new File(databasePath);
file.browseForSave("Save copy of database");
file.addEventListener(Event.SELECT, onCopySelect);
function onCopySelect(evt:Event):void {
if(notAlreadyOpen(file)){
var initFile:File = new File(databasePath);
initFile.copyTo(file, true);
}else {
Alert.show("Cannot overwrite a file that is currently open.", "Nope");
}
}
}

private function loadDataSchema(name:String):void {
if (name != "") {
// Adding tables:
var nid:Number = (name == "main")?(1):(Number(name.replace("db", "")));
// Delete all children
var dataNode:XMLList = dbData.db.(@numid == nid);
dataNode.setChildren(<placeholder/>);
delete dataNode.placeholder;
connection.loadSchema(null, null, name, true, new Responder(schemaSuccess, schemaError));
function schemaSuccess(evt:SQLSchemaResult):void {
// Schema found! Now parsing:
var result:SQLSchemaResult = evt;

for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
newTable.@isBranch = false;
newTable.@databaseName = name;
newTable.@icon = "iconTable";
newTable.@type = "table";
dataNode.appendChild(newTable);
isTreeEmpty = false;
}
// Adding views
for (var v:int = 0; v < result.views.length; v++) {
var newView:XML = new XML(<view/>);
newView.@label = result.views[v].name;
newView.@isBranch = false;
newView.@databaseName = name;
newView.@icon = "iconView";
newView.@type = "view";
dataNode.appendChild(newView);
}
// Adding triggers
for (var t:int = 0; t < result.triggers.length; t++) {
var newTrigger:XML = new XML(<trig/>);
newTrigger.@label = result.triggers[t].name;
newTrigger.@isBranch = false;
newTrigger.@databaseName = name;
newTrigger.@icon = "iconTrigger";
newTrigger.@type = "trigger";
dataNode.appendChild(newTrigger);
}
// Adding indices
for (var u:int = 0; u < result.indices.length; u++) {
var newIndex:XML = new XML(<ind/>);
newIndex.@label = result.indices[u].name;
newIndex.@isBranch = false;
newIndex.@databaseName = name;
newIndex.@icon = "iconIndex";
newIndex.@type = "index";
dataNode.appendChild(newIndex);
}
}
function schemaError(evt:SQLError):void {
// Alert.show("Database is empty");
}
isTableSelected = false;
isViewSelected = false;
isIndexSelected = false;
isTriggerSelected = false;
}
}

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 + "(main)";
newDB.@name = 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, new Responder(attachSuccess, attachError));
function attachSuccess():void {
newDB = <db/>
newDB.@label = file.name + "(db" + newnum.toString() + ")";
newDB.@name = file.name;
newDB.@numid = newnum.toString();
newDB.@isBranch = true;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
ret = "db" + newnum.toString();
}
function attachError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}}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;
}

private function tableSelect():void {
isTableSelected = false;
isIndexSelected = false;
isViewSelected = false;
isTriggerSelected = false;
saveTableButton.emphasized = false;
fullSelectedTable = "";
columnData = new ArrayCollection([]);
tableGrid.columns = [new AdvancedDataGridColumn("Data")];
if(col_name!=null){
col_name.text = "";
col_data.selectedIndex = 0;
col_key.selected = false;
col_auto.selected = false;
col_unique.selected = false;
col_null.selected = false;
col_default.text = "";
col_conflict.selectedIndex = 0;
}
// database
if (tableTree.selectedItem.@isBranch) {
var dataname:String;
if (tableTree.selectedItem.@numid == 1) dataname = "main";
if (tableTree.selectedItem.@numid > 1) dataname = "db" + tableTree.selectedItem.@numid;
selectedDatabase = dataname;
}
// table
if (tableTree.selectedItem.@isBranch == false && tableTree.selectedItem.@type == "table") {
isTableSelected = true;
selectedDatabase = tableTree.selectedItem.@databaseName;
fullSelectedTable = "Selected table: " + selectedDatabase + "." + tableTree.selectedItem.@label;
tableData = new ArrayCollection([]);
var newColumns:Array = [checkboxColumn];
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
for (var i:int = 0; i < schema.tables[0].columns.length; i++) {
columnData.addItem({name:schema.tables[0].columns[i].name});
var aColumn:AdvancedDataGridColumn = new AdvancedDataGridColumn();
aColumn.headerText = schema.tables[0].columns[i].name;
aColumn.dataField = "db_" + schema.tables[0].columns[i].name;
if (schema.tables[0].columns[i].autoIncrement) aColumn.editable = false;
if (schema.tables[0].columns[i].primaryKey) tableTree.selectedItem.@primaryKeyColumn = schema.tables[0].columns[i].name;
newColumns.push(aColumn);
}
tableGrid.columns = newColumns;
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT * FROM " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
stat.execute(-1, new Responder(tableSuccess, tableError));
}
// view
if (tableTree.selectedItem.@isBranch == false && tableTree.selectedItem.@type == "view") {
isViewSelected = true;
connection.loadSchema(SQLViewSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var viewschema:SQLSchemaResult = connection.getSchemaResult();
if(tabNavigator.selectedIndex!=2){
tabNavigator.addEventListener(IndexChangedEvent.CHANGE, tabChangeView);
tabNavigator.selectedIndex = 2;
}else {
queryText.text = viewschema.views[0].sql;
displayView();
}
function tabChangeView(evt:IndexChangedEvent):void {
queryText.text = viewschema.views[0].sql;
displayView();
tabNavigator.removeEventListener(IndexChangedEvent.CHANGE, tabChangeView);
}
}
// trigger
if (tableTree.selectedItem.@isBranch == false && tableTree.selectedItem.@type == "trigger") {
isTriggerSelected = true;
connection.loadSchema(SQLTriggerSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var triggerschema:SQLSchemaResult = connection.getSchemaResult();
if(tabNavigator.selectedIndex!=2){
tabNavigator.addEventListener(IndexChangedEvent.CHANGE, tabChangeTrigger);
tabNavigator.selectedIndex = 2;
}else {
queryText.text = triggerschema.triggers[0].sql;
}
function tabChangeTrigger(evt:IndexChangedEvent):void {
queryText.text = triggerschema.triggers[0].sql;
tabNavigator.removeEventListener(IndexChangedEvent.CHANGE, tabChangeTrigger);
}
}
// index
if (tableTree.selectedItem.@isBranch == false && tableTree.selectedItem.@type == "index") {
isIndexSelected = true;
connection.loadSchema(SQLIndexSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var indschema:SQLSchemaResult = connection.getSchemaResult();
if(tabNavigator.selectedIndex!=2){
tabNavigator.addEventListener(IndexChangedEvent.CHANGE, tabChange);
tabNavigator.selectedIndex = 2;
}else {
queryText.text = indschema.indices[0].sql;
}
function tabChange(evt:IndexChangedEvent):void {
queryText.text = indschema.indices[0].sql;
tabNavigator.removeEventListener(IndexChangedEvent.CHANGE, tabChange);
}
}
function tableSuccess(evt:SQLResult):void {
if (evt.data != null) {
for (var item:Object in evt.data) {
var obj:Object = new Object();
for (var value:Object in evt.data[item]) {
obj["db_"+value] = evt.data[item][value];
}
tableData.addItem(obj);
}
}
}
function tableError(evt:SQLError):void {
Alert.show("Unable to read table data.", "Error");
}
}

private function newTable():void {
PopUpManager.addPopUp(newTableWindow, this);
PopUpManager.centerPopUp(newTableWindow);
enableEverything = false;
newTableWindow.title = "Create new table";
focusManager.setFocus(newTableName);
}

private function dropTable():void {
Alert.show("Are you sure you want to completely delete this table?", "Drop table?", Alert.YES | Alert.NO, null, dropConfirm);
function dropConfirm(evt:CloseEvent):void {
if (evt.detail == Alert.YES) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
lastStatement(stat.text);
stat.execute( -1, new Responder(dropTableSuccess, dropTableError));
}
}
function dropTableSuccess(evt:SQLResult):void {
isTreeEmpty = true;
refreshEverything();
tableData = new ArrayCollection([]);
}
function dropTableError(evt:SQLError):void {
Alert.show("ERROR:" + evt.details, "Error");
}
}

private function closeNewTableWindow():void{
PopUpManager.removePopUp(newTableWindow);
enableEverything = true;
}

private function createNewTable():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "CREATE TABLE IF NOT EXISTS " + selectedDatabase + "." + newTableName.text + "(" + keyName.text + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL)";
lastStatement(stat.text);
stat.execute( -1, new Responder(newTableSuccess, newTableError));
function newTableSuccess(evt:SQLResult):void {
closeNewTableWindow();
loadDataSchema(selectedDatabase);
}
function newTableError(evt:SQLError):void {
Alert.show("ERROR:" + evt.details, "Error");
}
}

private function lastStatement(text:String):void {
statementText.text = text;
sqlHistory.push(text);
}

private function openHistory():void {
PopUpManager.addPopUp(historyWindow, this);
enableEverything = false;
historyWindow.width = width - 100;
historyWindow.height = height - 100;
PopUpManager.centerPopUp(historyWindow);
historyText.text = "";
for (var i:int = sqlHistory.length - 1; i >= 0; i--) {
historyText.appendText(sqlHistory[i] + "\n");
}
}

private function closeHistoryWindow():void {
PopUpManager.removePopUp(historyWindow);
enableEverything = true;
}

private function saveTable():void {
connection.begin();
var keyColumnName:String = tableTree.selectedItem.@primaryKeyColumn;
// clear all "sel" and store them in a temp array
var tempSel:Array = [];
for (var s:int = 0; s < tableData.length; s++) {
if (tableData[s].sel) {
tempSel.push(true);
tableData[s].sel = false;
}else
if (!tableData[s].sel) {
tempSel.push(false);
}
}
// update each row
for (var i:int = 0; i < tableData.length; i++) {
var stat:SQLStatement = new SQLStatement();
var sqlStat:String = "UPDATE " + selectedDatabase + "." + tableTree.selectedItem.@label + " SET";
// add each attribute as parameter
for (var attribute:String in tableData[i]) {
// if column is not our CheckBox column or the key column
if (attribute != "mx_internal_uid" && attribute!=keyColumnName && attribute!="sel") {
// add value as parameter
stat.parameters["@" + attribute.substr(3)] = tableData[i][attribute];
sqlStat += " " + attribute.substr(3) + "=@" + attribute.substr(3) + ",";
}
}
// remove the last comma
sqlStat = sqlStat.substr(0, sqlStat.length - 1);
sqlStat += " WHERE " + keyColumnName + "=" + tableData[i]["db_"+keyColumnName];
stat.sqlConnection = connection;
stat.text = sqlStat;
lastStatement(stat.text);
stat.execute( -1, new Responder(saveSuccess, saveError));
}

function saveSuccess(evt:SQLResult):void {
}

function saveError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}

tableSelect();
for (var t:int = 0; t < tableData.length; t++) {
if (tempSel[t]) tableData[t].sel=true;
}
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();
connection.commit();
}

private function deleteSelected():void {
connection.begin();
var keyColumnName:String = tableTree.selectedItem.@primaryKeyColumn;
for (var i:int = 0; i < tableData.length; i++) {
if (tableData[i].sel) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "DELETE FROM " + selectedDatabase + "." + tableTree.selectedItem.@label + " WHERE " + keyColumnName + "=" + tableData[i]["db_" + keyColumnName];
lastStatement(stat.text);
stat.execute( -1, new Responder(deleteSuccess, deleteError));
}
}
tableSelect();
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();

function deleteSuccess(evt:SQLResult):void {
}
function deleteError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
connection.commit();
}

private function newRecord():void {
PopUpManager.addPopUp(newRecordWindow, this);
enableEverything = false;
newRecordWindow.width = width - 100;
newRecordWindow.height = 120;
PopUpManager.centerPopUp(newRecordWindow);

var columns:Array = getSchemaColumns();

var defaultItem:Object = new Object();
recordColumnNames = [];
recordColumnNamesFull = [];

var recordColumns:Array = [];
for (var i:int = columns.length-1; i >= 0; i--) {
if (columns[i].indexOf(" AUTOINCREMENT") == -1) {
recordColumnNames.push(columnData[i].name);
recordColumnNamesFull.push(columns[i]);
var advColumn:AdvancedDataGridColumn = new AdvancedDataGridColumn(columns[i]);
recordColumns.push(advColumn);
// find DEFAULT and extract it
var defaultMatch:String = "";
var defaultPattern:RegExp = /((DEFAULT)\s((".+")|([0-9]+)))/i;
if (columns[i].match(defaultPattern)) {
defaultMatch = columns[i].match(defaultPattern)[0];
// delete "DEFAULT" from the match
defaultMatch = defaultMatch.substr(8);
// if any quotes are found, remove the first and last symbols
if (defaultMatch.indexOf('"') != -1) {
defaultMatch = defaultMatch.substring(1, defaultMatch.length - 1);
}
}
defaultItem[columns[i]] = defaultMatch;
}
if (columns[i].indexOf(" AUTOINCREMENT") != -1) {
columns.splice(i, 1);
}
}

// if no fields to edit, insert blank
if (columns.length == 0) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO " + selectedDatabase + "." + tableTree.selectedItem.@label + " DEFAULT VALUES;";
lastStatement(stat.text);
stat.execute( -1, new Responder(newSuccess, newError));

function newSuccess(evt:SQLResult):void {
closeNewRecordWindow();
tableSelect();
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();
}
function newError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
return;
}

recordDataGrid.columns = recordColumns;
recordDataGrid.dataProvider = new ArrayCollection([defaultItem]);
}

private function closeNewRecordWindow():void {
PopUpManager.removePopUp(newRecordWindow);
enableEverything = true;
}

private function addNewRecord():void {
// placeholder code that inserts empty row:
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "INSERT INTO " + selectedDatabase + "." + tableTree.selectedItem.@label + " (";
stat.text += String(recordColumnNames);
stat.text += ") VALUES (";
var values:Array = [];
for (var i:int = 0; i < recordColumnNames.length; i++) {
var val:String;
var dataValue:String = recordDataGrid.dataProvider[0][recordColumnNamesFull[i]];
if (isNaN(Number(dataValue))) val = '"' + dataValue + '"';
if (!isNaN(Number(dataValue))) val = dataValue;
values.push(val);
}
stat.text += String(values);
stat.text += ");";
lastStatement(stat.text);
stat.execute( -1, new Responder(newSuccess, newError));

function newSuccess(evt:SQLResult):void {
closeNewRecordWindow();
tableSelect();
tableGrid.invalidateDisplayList();
tableGrid.invalidateList();
}
function newError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}

private function columnSelect():void {
var columns:Array = getSchemaColumns();
// get the currently selected column
var currentColumn:String = columns[columnList.selectedIndex];
// delete the name of the column from this text
var currentParameters:String = currentColumn.substr(currentColumn.indexOf(columnList.selectedItem.name) + columnList.selectedItem.name.length + 1);
// find DEFAULT and extract it
var defaultMatch:String = "";
var defaultPattern:RegExp = /((DEFAULT)\s((".+")|([0-9]+)))/i;
if (currentParameters.match(defaultPattern)) {
defaultMatch = currentParameters.match(defaultPattern)[0];
// delete it from currentParameters
currentParameters = currentParameters.replace(defaultMatch, "");
// delete "DEFAULT" from the match
defaultMatch = defaultMatch.substr(8);
// if any quotes are found, remove the first and last symbols
if (defaultMatch.indexOf('"') != -1) {
defaultMatch = defaultMatch.substring(1, defaultMatch.length - 1);
}
}
// find ON CONFLICT and extract it
var conflictMatch:String = "";
var conflictPattern:RegExp = /((ON CONFLICT)\s(ABORT|FAIL|IGNORE|ROLLBACK|REPLACE))/i;
if (currentParameters.match(conflictPattern)) {
conflictMatch = currentParameters.match(conflictPattern)[0];
// delete it from currentParameters
currentParameters = currentParameters.replace(conflictMatch, "");
// delete "ON CONFLICT" from the match
conflictMatch = conflictMatch.substr(12);
}
// apply values
col_name.text = columnList.selectedItem.name;
col_key.selected = (currentParameters.toUpperCase().indexOf("PRIMARY KEY") != -1)?(true):(false);
col_auto.selected = (currentParameters.toUpperCase().indexOf("AUTOINCREMENT") != -1)?(true):(false);
col_unique.selected = (currentParameters.toUpperCase().lastIndexOf("UNIQUE") != -1)?(true):(false);
col_null.selected = (currentParameters.toUpperCase().indexOf("NOT NULL") != -1)?(false):(true);
col_default.text = defaultMatch;
col_conflict.selectedIndex = 0;
if (conflictMatch.toUpperCase() == "ABORT") col_conflict.selectedIndex = 1;
if (conflictMatch.toUpperCase() == "FAIL") col_conflict.selectedIndex = 2;
if (conflictMatch.toUpperCase() == "IGNORE") col_conflict.selectedIndex = 3;
if (conflictMatch.toUpperCase() == "ROLLBACK") col_conflict.selectedIndex = 4;
if (conflictMatch.toUpperCase() == "REPLACE") col_conflict.selectedIndex = 5;

// read data type
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
col_data.textInput.text = schema.tables[0].columns[columnList.selectedIndex].dataType;

// enable or disable ON CONFLICT
checkConflict();
// unhighlight "Update selected"
col_b_update.emphasized = false;
}

private function checkConflict():void {
if (col_key.selected || !col_null.selected || col_unique.selected) {
col_conflict.enabled = true;
}else {
col_conflict.enabled = false;
}
}

private function formChange():void {
checkConflict();
if (columnList.selectedItems.length > 0) {
col_b_update.emphasized = true;
}
}

private function addColumn():void {
col_b_update.emphasized = false;
var prevTableName:String = tableTree.selectedItem.@label;
if (col_name.text != "" && col_data.textInput.text != "" && (col_null.selected || col_default.text != "")) {
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
var fullSQL:String = schema.tables[0].sql;
// extract the text inside the ( )
fullSQL = fullSQL.substring( fullSQL.indexOf("(") + 1 , fullSQL.lastIndexOf(")") );
var sqlText:String = "CREATE TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label + " (";
sqlText += fullSQL + ", ";
// add the new column
sqlText += col_name.text + " " + col_data.textInput.text + " ";
if (col_key.selected) sqlText += "PRIMARY KEY ";
if (col_key.selected && col_conflict.selectedIndex > 0) sqlText += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_key.selected && col_auto.selected) sqlText += "AUTOINCREMENT ";
if (!col_null.selected) sqlText += "NOT NULL ";
if (!col_null.selected && col_conflict.selectedIndex > 0) sqlText += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_unique.selected) sqlText += "UNIQUE ";
if (col_unique.selected && col_conflict.selectedIndex > 0) sqlText += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_default.text != "") {
sqlText += "DEFAULT ";
if (isNaN(Number(col_default.text))) sqlText += '"' + col_default.text + '"';
if (!isNaN(Number(col_default.text))) sqlText += col_default.text;
}
sqlText += ");";
lastStatement(sqlText);

// Create backup
var backupName:String = "backup";
while (!tableIsUnique(backupName)) {
backupName += "0";
}
var bstat:SQLStatement = new SQLStatement();
bstat.sqlConnection = connection;
bstat.text = "CREATE TABLE " + selectedDatabase + "." + backupName + " (" + fullSQL + ");";
bstat.execute();

// Copy data to backup
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "INSERT INTO " + selectedDatabase + "." + backupName + " SELECT * FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
cstat.execute();

// Delete initial table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
dstat.execute();

// Create new table
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = sqlText;
stat.execute( -1, new Responder(newColumnSuccess, newColumnError));
}else {
Alert.show("Please fill all the required fields!", "Error");
}
function newColumnSuccess(evt:SQLResult):void {
// Insert previous values
var istat:SQLStatement = new SQLStatement();
istat.sqlConnection = connection;
istat.text = "INSERT INTO " + selectedDatabase + "." + prevTableName + " (" + columnNames() + ") SELECT " + columnNames() + " FROM " + selectedDatabase + "." + backupName;
istat.execute( -1, new Responder(newColumnInsertSuccess, newColumnInsertError));
function newColumnInsertSuccess(evt:SQLResult):void {
// Delete backup
var bdstat:SQLStatement = new SQLStatement();
bdstat.sqlConnection = connection;
bdstat.text = "DROP TABLE " + selectedDatabase + "." + backupName;
bdstat.execute();
tableSelect();
}
function newColumnInsertError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details + "\n\nRestoring the database using backup...", "Error");
// Delete existing table
var destat:SQLStatement = new SQLStatement();
destat.sqlConnection = connection;
destat.text = "DROP TABLE " + selectedDatabase + "." + prevTableName;
destat.execute();
// Restore table using backup
var rstat:SQLStatement = new SQLStatement();
rstat.sqlConnection = connection;
rstat.text = "ALTER TABLE " + selectedDatabase + "." + backupName + " RENAME TO " + prevTableName;
rstat.execute();
tableSelect();
}
}
function newColumnError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details + "\n\nRestoring the database using backup...", "Error");
// Restore table
var rstat:SQLStatement = new SQLStatement();
rstat.sqlConnection = connection;
rstat.text = "ALTER TABLE " + selectedDatabase + "." + backupName + " RENAME TO " + prevTableName;
rstat.execute();
tableSelect();
}
}

private function deleteColumn():void {
col_b_update.emphasized = false;
var prevTableName:String = tableTree.selectedItem.@label;

var columns:Array = getSchemaColumns();
// get the currently selected index
var currentIndex:int = columnList.selectedIndex;

// Create backup
var backupName:String = "backup";
while (!tableIsUnique(backupName)) {
backupName += "0";
}
var bstat:SQLStatement = new SQLStatement();
bstat.sqlConnection = connection;
var sqlText:String = "CREATE TABLE " + selectedDatabase + "." + backupName + " (";
columns.splice(currentIndex, 1);
if (columns.length == 0) {
Alert.show("Can't make a table completely empty! Leave at least one column.", "Nope");
return;
}
sqlText += String(columns);
sqlText += ");";
bstat.text = sqlText;
lastStatement(sqlText);
bstat.execute();

// Copy data to backup
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "INSERT INTO " + selectedDatabase + "." + backupName + " (" + columnNames(currentIndex) + ") SELECT " + columnNames(currentIndex) + " FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
cstat.execute();

// Drop existing table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
dstat.execute();

// Rename backup table to initial name
var rstat:SQLStatement = new SQLStatement();
rstat.sqlConnection = connection;
rstat.text = "ALTER TABLE " + selectedDatabase + "." + backupName + " RENAME TO " + prevTableName
rstat.execute();
tableSelect();
}

private function updateColumn():void {
col_b_update.emphasized = false;
var prevTableName:String = tableTree.selectedItem.@label;

if (col_name.text != "" && col_data.textInput.text != "" && (col_null.selected || col_default.text != "")) {
var columns:Array = getSchemaColumns();
// get the currently selected index
var currentIndex:int = columnList.selectedIndex;

// Create backup
var backupName:String = "backup";
while (!tableIsUnique(backupName)) {
backupName += "0";
}
var bstat:SQLStatement = new SQLStatement();
bstat.sqlConnection = connection;

// Compose updated column info
var newColumn:String = col_name.text + " " + col_data.textInput.text + " ";
if (col_key.selected) newColumn += "PRIMARY KEY ";
if (col_key.selected && col_conflict.selectedIndex > 0) newColumn += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_key.selected && col_auto.selected) newColumn += "AUTOINCREMENT ";
if (!col_null.selected) newColumn += "NOT NULL ";
if (!col_null.selected && col_conflict.selectedIndex > 0) newColumn += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_unique.selected) newColumn += "UNIQUE ";
if (col_unique.selected && col_conflict.selectedIndex > 0) newColumn += "ON CONFLICT " + col_conflict.selectedLabel + " ";
if (col_default.text != "") {
newColumn += "DEFAULT ";
if (isNaN(Number(col_default.text))) newColumn += '"' + col_default.text + '"';
if (!isNaN(Number(col_default.text))) newColumn += col_default.text;
}

columns[currentIndex] = newColumn;

// Compose the table creation query
var sqlText:String = "CREATE TABLE " + selectedDatabase + "." + backupName + " (";
sqlText += String(columns);
sqlText += ");";
bstat.text = sqlText;
lastStatement(sqlText);
bstat.execute( -1, new Responder(updateColumnSuccess, updateColumnError));

function updateColumnSuccess(evt:SQLResult):void { 
// Copy data to backup
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "INSERT INTO " + selectedDatabase + "." + backupName + " (" + columnNames(currentIndex) + ") SELECT " + columnNames(currentIndex) + " FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
cstat.execute(-1, new Responder(updateInsertColumnSuccess, updateInsertColumnError));

function updateInsertColumnSuccess(evt:SQLResult):void { 
// Drop existing table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label;
dstat.execute();

// Rename backup table to initial name
var rstat:SQLStatement = new SQLStatement();
rstat.sqlConnection = connection;
rstat.text = "ALTER TABLE " + selectedDatabase + "." + backupName + " RENAME TO " + prevTableName;
rstat.execute();
tableSelect();
}
function updateInsertColumnError(evt:SQLError):void {
// Delete backup
var bdstat:SQLStatement = new SQLStatement();
bdstat.sqlConnection = connection;
bdstat.text = "DROP TABLE " + selectedDatabase + "." + backupName;
bdstat.execute();
tableSelect();
Alert.show("ERROR: " + evt.details, "Error");
}
}
function updateColumnError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}else {
Alert.show("Please fill all the required fields!", "Error");
}
}

private function tableIsUnique(name:String):Boolean {
var r:Boolean = true;
for (var i:int = 0; i < dbData..tb.length(); i++) {
if (dbData..tb[i].@label == name) {
r = false;
break;
}
}
return r;
}

private function columnNames(exception:int = -1):String {
var r:String = "";
var array:Array = [];
for (var i:int = 0; i < columnData.length; i++) {
if (i != exception) array.push(columnData[i].name);
}
r = String(array);
return r;
}

private function getSchemaColumns(fromtable:String = null, fromdatabase:String = null):Array {
if (fromtable == null) {
fromtable = tableTree.selectedItem.@label;
fromdatabase = tableTree.selectedItem.@databaseName;
}
connection.loadSchema(SQLTableSchema, fromtable, fromdatabase);
var schema:SQLSchemaResult = connection.getSchemaResult();
var fullSQL:String = schema.tables[0].sql;
// extract the text inside the ( )
fullSQL = fullSQL.substring( fullSQL.indexOf("(") + 1 , fullSQL.lastIndexOf(")") );
// split all columns into an array
var columns:Array = fullSQL.split(",");
return columns;
}

private function renameTable():void {
PopUpManager.addPopUp(renameTableWindow, this);
PopUpManager.centerPopUp(renameTableWindow);
enableEverything = false;
renameTableName.text = tableTree.selectedItem.@label;
}

private function closeRenameTableWindow():void {
PopUpManager.removePopUp(renameTableWindow);
enableEverything = true;
}

private function doRenameTable():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "ALTER TABLE " + selectedDatabase + "." + tableTree.selectedItem.@label + " RENAME TO " + renameTableName.text;
lastStatement(stat.text);
stat.execute( -1, new Responder(renameSuccess, renameError));
function renameSuccess(evt:SQLResult):void {
closeRenameTableWindow();
tableTree.selectedItem.@label = renameTableName.text;
fullSelectedTable = "Selected table: " + selectedDatabase + "." + tableTree.selectedItem.@label;
}
function renameError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}

private function queryExecute():void {
if(queryText.text!="" && dbData..db.length()>0){
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = queryText.text;
stat.execute( -1, new Responder(querySuccess, queryError));
function querySuccess(evt:SQLResult):void {
var resultColumns:Array = [];
resultData = new ArrayCollection([]);
if (evt.data != null) {
// get the columns
for (var col:Object in evt.data[0]) {
var advCol:AdvancedDataGridColumn = new AdvancedDataGridColumn(String(col));
resultColumns.push(advCol);
}
// get the data
for (var i:int = 0; i < evt.data.length; i++) {
resultData.addItem(evt.data[i]);
}
}
queryResultGrid.columns = resultColumns;
refreshEverything();
lastStatement(queryText.text);
}
function queryError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}
}

private function copyTable():void {
PopUpManager.addPopUp(copyTableWindow, this);
PopUpManager.centerPopUp(copyTableWindow);
enableEverything = false;
selectedCopyTable.text = "Selected table: " + selectedDatabase + "." + tableTree.selectedItem.@label;
copyTableName.text = tableTree.selectedItem.@label;
databaseData = new ArrayCollection([]);
var selectedDbIndex:int = 0;
for (var i:int = 0; i < dbData..db.length(); i++) {
var dbid:String;
if (dbData..db[i].@numid == 1) dbid = "main";
if (dbData..db[i].@numid != 1) dbid = "db" + dbData..db[i].@numid;
databaseData.addItem({name:dbData..db[i].@name, did:dbid});
if (dbid == tableTree.selectedItem.@databaseName) {
selectedDbIndex = i;
}
}
copyDestinationDatabase.selectedIndex = selectedDbIndex;
}

private function closeCopyTableWindow():void {
PopUpManager.removePopUp(copyTableWindow);
enableEverything = true;
}

private function doCopyTable():void {
// create table
var columns:Array = getSchemaColumns();
var destDatabase:String = copyDestinationDatabase.selectedItem.did;
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "CREATE TABLE " + destDatabase + "." + copyTableName.text + " (" + String(columns) + ")";
cstat.execute( -1, new Responder(copySuccess, copyError));
lastStatement(cstat.text);
function copySuccess(evt:SQLResult):void {
// copy all the data
var istat:SQLStatement = new SQLStatement();
istat.sqlConnection = connection;
istat.text = "INSERT INTO " + destDatabase + "." + copyTableName.text + " (" + columnNames() + ") SELECT " + columnNames() + " FROM " + selectedDatabase + "." + tableTree.selectedItem.@label;
istat.execute( -1, new Responder(copyInsertSuccess, copyInsertError));
lastStatement(istat.text);
}
function copyError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
function copyInsertSuccess(evt:SQLResult):void {
// update xml
loadDataSchema(destDatabase);
tableData = new ArrayCollection([]);
tableTree.selectedIndex = -1;
// close window
closeCopyTableWindow();
}
function copyInsertError(evt:SQLError):void {
// delete new table
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + destDatabase + "." + copyTableName.text;
dstat.execute();
Alert.show("ERROR: " + evt.details, "Error");
}
}

private function joinTable():void {
PopUpManager.addPopUp(joinTableWindow, this);
PopUpManager.centerPopUp(joinTableWindow);
enableEverything = false;
var selectedtable:String = selectedDatabase + "." + tableTree.selectedItem.@label;
selectedJoinTable.text = "Join " + selectedtable + " with";

allTables = new ArrayCollection([]);
for (var u:int = 0; u < dbData..tb.length(); u++) {
var fullname:String = dbData..tb[u].@databaseName + "." + dbData..tb[u].@label;
if(fullname!=selectedtable){
allTables.addItem( { name: fullname, table: dbData..tb[u].@label, database: dbData..tb[u].@databaseName} );
}
}

databaseData = new ArrayCollection([]);
var selectedDbIndex:int = 0;
for (var i:int = 0; i < dbData..db.length(); i++) {
var dbid:String;
if (dbData..db[i].@numid == 1) dbid = "main";
if (dbData..db[i].@numid != 1) dbid = "db" + dbData..db[i].@numid;
databaseData.addItem({name:dbData..db[i].@name, did:dbid});
if (dbid == tableTree.selectedItem.@databaseName) {
selectedDbIndex = i;
}
}
joinDestinationDatabase.selectedIndex = selectedDbIndex;

joinTableName.text = "Untitled";
}

private function closeJoinTableWindow():void {
PopUpManager.removePopUp(joinTableWindow);
enableEverything = true;
}

private function doJoinTable():void {
closeJoinTableWindow();
PopUpManager.addPopUp(joinTableColumnsWindow, this);
PopUpManager.centerPopUp(joinTableColumnsWindow);
enableEverything = false;
// read all columns
var columns1:Array = getSchemaColumns();
var columns2:Array = getSchemaColumns(joinTableCombo.selectedItem.table, joinTableCombo.selectedItem.database);
var commoncolumns:Array = columns1.concat(columns2);
// read all column names
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
var columnnames1:Array = [];
var tablename1:String = tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
for (var t:int = 0; t < schema.tables[0].columns.length; t++) {
columnnames1.push({name: schema.tables[0].columns[t].name, table:1, tablename:tablename1});
}

connection.loadSchema(SQLTableSchema, joinTableCombo.selectedItem.table, joinTableCombo.selectedItem.database);
schema = connection.getSchemaResult();
var columnnames2:Array = [];
var tablename2:String = joinTableCombo.selectedItem.database + "." + joinTableCombo.selectedItem.table;
for (var u:int = 0; u < schema.tables[0].columns.length; u++) {
columnnames2.push({name: schema.tables[0].columns[u].name, table:2, tablename:tablename2});
}

var commoncolumnnames:Array = columnnames1.concat(columnnames2);
// put everything into joinColumns
joinColumns = new ArrayCollection([]);
for (var i:int = 0; i < commoncolumns.length; i++) {
var fulln:String = commoncolumns[i].replace("\n", "");
while (fulln.charAt(0) == " ") fulln = fulln.substr(1);
var gettable:int = commoncolumnnames[i].table;
var gettablename:String = commoncolumnnames[i].tablename;
var getname:String = commoncolumnnames[i].name;
joinColumns.addItem({sel:true, fullName:fulln, name:getname, table:gettable, tableName:gettablename});
}
}

private function closeJoinTableColumnsWindow():void {
PopUpManager.removePopUp(joinTableColumnsWindow);
enableEverything = true;
}

private function doJoinColumnsTable():void {
// prepare arrays
var selectedColumns:Array = [];
var secondTableColumns:Array = [];
var firstTableColumns:Array = [];
for (var i:int = 0; i < joinColumns.length; i++) {
if (joinColumns[i].sel) {
selectedColumns.push(joinColumns[i].fullName);
if (joinColumns[i].table==1) {
firstTableColumns.push(joinColumns[i].name);
}
if (joinColumns[i].table==2) {
secondTableColumns.push(joinColumns[i].name);
}
}
}
// create table
var destDatabase:String = joinDestinationDatabase.selectedItem.did;
var cstat:SQLStatement = new SQLStatement();
cstat.sqlConnection = connection;
cstat.text = "CREATE TABLE " + destDatabase + "." + joinTableName.text + " (" + String(selectedColumns) + ")";
cstat.execute( -1, new Responder(joinCreateSuccess, joinCreateError));
lastStatement(cstat.text);
function joinCreateSuccess(evt:SQLResult):void { 
// copy data from table 1
if(firstTableColumns.length>0){
var copystat:SQLStatement = new SQLStatement();
copystat.sqlConnection = connection;
copystat.text = "INSERT INTO " + destDatabase + "." + joinTableName.text + " (" + firstTableColumns + ") SELECT " + firstTableColumns + " FROM " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
copystat.execute( -1, new Responder(joinCopySuccess, joinErrorDrop));
}else {
joinCopySuccess();
}
}
function joinCopySuccess():void {
// copy data from table 2
if(secondTableColumns.length>0){
var copystat2:SQLStatement = new SQLStatement();
copystat2.sqlConnection = connection;
copystat2.text = "INSERT INTO " + destDatabase + "." + joinTableName.text + " (" + secondTableColumns + ") SELECT " + secondTableColumns + " FROM " + joinTableCombo.selectedItem.database + "." + joinTableCombo.selectedItem.table;
copystat2.execute( -1, new Responder(joinCopy2Success, joinErrorDrop));
}else {
joinCopy2Success();
}
}
function joinCopy2Success():void {
// update xml
loadDataSchema(destDatabase);
tableData = new ArrayCollection([]);
tableTree.selectedIndex = -1;
// close window
closeJoinTableColumnsWindow();
}
function joinCreateError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
}
function joinErrorDrop(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TABLE " + destDatabase + "." + joinTableName.text;
dstat.execute();
}
}

private function refreshEverything():void {
for (var i:int = 0; i < dbData..db.length(); i++) {
var dbName:String = (dbData..db[i].@numid == 1)?("main"):("db" + dbData..db[i].@numid);
loadDataSchema(dbName);
}
tableData = new ArrayCollection([]);
tableTree.selectedIndex = -1;
}

private function newIndex():void {
PopUpManager.addPopUp(indexWindow, this);
PopUpManager.centerPopUp(indexWindow);
enableEverything = false;

indexName.text = "UntitledIndex";

allTables = new ArrayCollection([]);
for (var u:int = 0; u < dbData..tb.length(); u++) {
var fullname:String = dbData..tb[u].@databaseName + "." + dbData..tb[u].@label;
allTables.addItem( { name: fullname, table: dbData..tb[u].@label, database: dbData..tb[u].@databaseName} );
}

updateIndexTableColumns();
}

private function updateIndexTableColumns():void {
indexTableColumns = new ArrayCollection([]);
connection.loadSchema(SQLTableSchema, indexTableCombo.selectedItem.table, indexTableCombo.selectedItem.database);
var schema:SQLSchemaResult = connection.getSchemaResult();
for (var i:int = 0; i < schema.tables[0].columns.length; i++) {
indexTableColumns.addItem(schema.tables[0].columns[i].name);
}
}

private function closeIndexWindow():void {
PopUpManager.removePopUp(indexWindow);
enableEverything = true;
}

private function doNewIndex():void {
var unique:String = (indexUnique.selected)?("UNIQUE "):("");
var istat:SQLStatement = new SQLStatement();
istat.sqlConnection = connection;
istat.text = "CREATE " + unique + "INDEX " + indexTableCombo.selectedItem.database + "." + indexName.text + " ON " + indexTableCombo.selectedItem.table + " (" + String(indexColumnList.selectedItems) + ");";
istat.execute( -1, new Responder(indexSuccess, indexError));
lastStatement(istat.text);
function indexSuccess(evt:SQLResult):void {
refreshEverything();
PopUpManager.removePopUp(indexWindow);
enableEverything = true;
}
function indexError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function dropIndex():void {
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP INDEX " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
dstat.execute( -1, new Responder(dropIndexSuccess, dropIndexError));
lastStatement(dstat.text);
function dropIndexSuccess(evt:SQLResult):void {
refreshEverything();
}
function dropIndexError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function newView():void {
PopUpManager.addPopUp(viewWindow, this);
PopUpManager.centerPopUp(viewWindow);
enableEverything = false;
viewName.text = "UntitledView";
viewSelect.text = "SELECT * FROM ";

databaseData = new ArrayCollection([]);
for (var i:int = 0; i < dbData..db.length(); i++) {
var dbid:String;
if (dbData..db[i].@numid == 1) dbid = "main";
if (dbData..db[i].@numid != 1) dbid = "db" + dbData..db[i].@numid;
databaseData.addItem( { name:dbData..db[i].@name, did:dbid } );
}
}

private function closeViewWindow():void {
PopUpManager.removePopUp(viewWindow);
enableEverything = true;
}

private function doNewView():void {
var vstat:SQLStatement = new SQLStatement();
vstat.sqlConnection = connection;
vstat.text = "CREATE VIEW " + viewDestinationDatabase.selectedItem.did + "." + viewName.text + " AS " + viewSelect.text;
lastStatement(vstat.text);
vstat.execute( -1, new Responder(viewSuccess, viewError));

function viewSuccess(evt:SQLResult):void {
refreshEverything();
closeViewWindow();
}
function viewError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function dropView():void {
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP VIEW " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
dstat.execute( -1, new Responder(dropViewSuccess, dropViewError));
lastStatement(dstat.text);
function dropViewSuccess(evt:SQLResult):void {
refreshEverything();
}
function dropViewError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function displayView():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT * FROM " + tableTree.selectedItem.@label;
stat.execute( -1, new Responder(querySuccess, queryError));
function querySuccess(evt:SQLResult):void {
var resultColumns:Array = [];
resultData = new ArrayCollection([]);
if (evt.data != null) {
// get the columns
for (var col:Object in evt.data[0]) {
var advCol:AdvancedDataGridColumn = new AdvancedDataGridColumn(String(col));
resultColumns.push(advCol);
}
// get the data
for (var i:int = 0; i < evt.data.length; i++) {
resultData.addItem(evt.data[i]);
}
}
queryResultGrid.columns = resultColumns;
lastStatement(queryText.text);
}
function queryError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}

private function newTrigger():void {
PopUpManager.addPopUp(triggerWindow, this);
PopUpManager.centerPopUp(triggerWindow);
enableEverything = false;
// set default name
triggerName.text = "UntitledTrigger";
// load databases
databaseData = new ArrayCollection([]);
for (var i:int = 0; i < dbData..db.length(); i++) {
var dbid:String;
if (dbData..db[i].@numid == 1) dbid = "main";
if (dbData..db[i].@numid != 1) dbid = "db" + dbData..db[i].@numid;
databaseData.addItem( { name:dbData..db[i].@name, did:dbid } );
}
// load tables
updateTriggerTables();
}

private function updateTriggerTables():void {
triggerTables = new ArrayCollection([]);
connection.loadSchema(null, null, triggerDatabase.selectedItem.did);
var schema:SQLSchemaResult = connection.getSchemaResult();
for (var i:int = 0; i < schema.tables.length; i++) {
triggerTables.addItem( {name: schema.tables[i].name} );
}
// load columns
updateTriggerTableColumns();
}

private function updateTriggerTableColumns():void {
triggerTableColumns = new ArrayCollection([]);
connection.loadSchema(SQLTableSchema, triggerTable.selectedItem.name, triggerDatabase.selectedItem.did);
var schema:SQLSchemaResult = connection.getSchemaResult();
for (var i:int = 0; i < schema.tables[0].columns.length; i++) {
triggerTableColumns.addItem(schema.tables[0].columns[i].name);
}
}

private function closeTriggerWindow():void {
PopUpManager.removePopUp(triggerWindow);
enableEverything = true;
}

private function doNewTrigger():void {
// CREATE TRIGGER database.trigger
var sqlStat:String = "CREATE TRIGGER " + triggerDatabase.selectedItem.did + "." + triggerName.text + " ";
// KEYWORD
sqlStat += triggerKeyword.selectedLabel;
// ACTION
sqlStat += triggerAction.selectedLabel;
// if UNIQUE and has columns selected
if (triggerAction.selectedIndex == 2 && triggerColumnList.selectedIndices.length > 0) {
sqlStat += "OF " + String(triggerColumnList.selectedItems) + " ";
}
// ON tablename
sqlStat += "ON " + triggerTable.selectedLabel + " ";
// FOR EACH ROW
if (triggerForEachRow.selected) sqlStat += "FOR EACH ROW ";
// WHEN (if needed)
if (triggerWhen.text.replace(" ", "") != "") {
sqlStat += "WHEN " + triggerWhen.text + " ";
}
// BEGIN code END
sqlStat += "BEGIN " + triggerBegin.text + " END";

lastStatement(sqlStat);

var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = sqlStat;
stat.execute( -1, new Responder(triggerSuccess, triggerError));

function triggerSuccess(evt:SQLResult):void {
refreshEverything();
closeTriggerWindow();
}
function triggerError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function dropTrigger():void {
var dstat:SQLStatement = new SQLStatement();
dstat.sqlConnection = connection;
dstat.text = "DROP TRIGGER " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
dstat.execute( -1, new Responder(dropTriggerSuccess, dropTriggerError));
lastStatement(dstat.text);
function dropTriggerSuccess(evt:SQLResult):void {
refreshEverything();
}
function dropTriggerError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
}
}
]]>
</fx:Script>

<s:HGroup gap="0" width="100%" height="100%" enabled="{enableEverything}">
<s:VGroup width="200" height="100%" gap="0">
<s:HGroup width="200" paddingLeft="6">
<custom:IconButton icon="@Embed('../lib/table_add.png')" toolTip="New table" enabled="{tableTree.selectedItems.length>0}" buttonMode="true" click="newTable();"/>
<custom:IconButton icon="@Embed('../lib/table_delete.png')" toolTip="Drop table" enabled="{isTableSelected}" buttonMode="true" click="dropTable();"/>
<custom:IconButton icon="@Embed('../lib/table_edit.png')" toolTip="Rename table" enabled="{isTableSelected}" buttonMode="true" click="renameTable();"/>
<custom:IconButton icon="@Embed('../lib/table_go.png')" toolTip="Copy table" enabled="{isTableSelected}" buttonMode="true" click="copyTable();"/>
<custom:IconButton icon="@Embed('../lib/table_relationship.png')" toolTip="Join table" enabled="{isTableSelected}" buttonMode="true" click="joinTable();"/>
<custom:IconButton icon="@Embed('../lib/key_add.png')" toolTip="New index" enabled="{!isTreeEmpty}" buttonMode="true" click="newIndex();"/>
<custom:IconButton icon="@Embed('../lib/key_delete.png')" toolTip="Drop index" enabled="{isIndexSelected}" buttonMode="true" click="dropIndex();"/>
</s:HGroup>
<s:HGroup width="200" paddingLeft="6">
<custom:IconButton icon="@Embed('../lib/eye_add.png')" toolTip="New view" enabled="{!isTreeEmpty}" buttonMode="true" click="newView();"/>
<custom:IconButton icon="@Embed('../lib/eye_delete.png')" toolTip="Drop view" enabled="{isViewSelected}" buttonMode="true" click="dropView();"/>
<custom:IconButton icon="@Embed('../lib/flag_add.png')" toolTip="New trigger" enabled="{!isTreeEmpty}" buttonMode="true" click="newTrigger();"/>
<custom:IconButton icon="@Embed('../lib/flag_delete.png')" toolTip="Drop trigger" enabled="{isTriggerSelected}" buttonMode="true" click="dropTrigger();"/>
</s:HGroup>
<mx:Tree id="tableTree" width="100%" height="100%" dataProvider="{dbData}" showRoot="false" labelField="@label" itemClick="tableSelect();" iconField="@icon" folderClosedIcon="@Embed('../lib/database.png')" folderOpenIcon="@Embed('../lib/database.png')" />
</s:VGroup>
<s:VGroup width="100%" height="100%" gap="0">
<mx:Box height="80" width="100%">
<s:VGroup paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10" width="100%" height="100%">
<s:HGroup width="100%" verticalAlign="middle">
<s:Label width="100%">Latest SQL statement:</s:Label>
<s:Button width="100" label="View history" click="openHistory();" />
</s:HGroup>
<s:TextArea id="statementText" editable="false" width="100%" height="30"/>
</s:VGroup>
</mx:Box>
<mx:TabNavigator width="100%" height="100%" paddingTop="0" id="tabNavigator">
<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" enabled="{isTableSelected}" click="deleteSelected();" />
<s:Button id="saveTableButton" label="Save changes" click="saveTable();" enabled="{isTableSelected}"/>
<s:Button id="newRecordButton" label="Add a record" click="newRecord();" enabled="{isTableSelected}"/>
</mx:HBox>
<mx:AdvancedDataGrid id="tableGrid" width="100%" height="100%" dataProvider="{tableData}" editable="true" itemEditBegin="saveTableButton.emphasized=true;">
<mx:columns>
<mx:AdvancedDataGridColumn dataField="" headerText="Data" editable="false" />
</mx:columns>
</mx:AdvancedDataGrid>
</s:VGroup>
</s:NavigatorContent>
<s:NavigatorContent label="Edit columns">
<s:HGroup width="100%" height="100%" >
<mx:List id="columnList" width="200" height="100%" dataProvider="{columnData}" labelField="name" change="columnSelect();" />
<s:VGroup height="100%" paddingTop="10">
<s:HGroup>
<s:Button id="col_b_add" label="Add column" enabled="{isTableSelected}" click="addColumn();" />
<s:Button id="col_b_update" label="Update selected" enabled="{columnList.selectedItems.length > 0}" click="updateColumn();" />
<s:Button id="col_b_delete" label="Delete selected" enabled="{columnList.selectedItems.length > 0}" click="deleteColumn();" />
</s:HGroup>
<mx:Form enabled="{isTableSelected}">
<mx:FormItem label="Name" required="true">
<s:TextInput id="col_name" change="formChange();"/>
</mx:FormItem>
<mx:FormItem label="Data type" required="true">
<s:ComboBox id="col_data" dataProvider="{dataTypes}" change="formChange();"/>
</mx:FormItem>
<mx:FormItem label="Primary Key">
<s:CheckBox id="col_key" change="formChange();" />
</mx:FormItem>
<mx:FormItem label="AutoIncrement">
<s:CheckBox id="col_auto" change="formChange();" enabled="{col_key.selected}" />
</mx:FormItem>
<mx:FormItem label="Unique">
<s:CheckBox id="col_unique" change="formChange();" />
</mx:FormItem>
<mx:FormItem label="Allow Null">
<s:CheckBox id="col_null" change="formChange();" selected="true" />
</mx:FormItem>
<mx:FormItem label="Default Value" required="{!col_null.selected}">
<s:TextArea id="col_default" change="formChange();"/>
</mx:FormItem>
<mx:FormItem label="On Conflict">
<mx:ComboBox id="col_conflict" dataProvider="{conflictTypes}" editable="false" change="formChange();"/>
</mx:FormItem>
</mx:Form>
</s:VGroup>
</s:HGroup>
</s:NavigatorContent>
<s:NavigatorContent label="Query">
<s:VGroup width="100%" height="100%" paddingLeft="10" paddingTop="10" paddingRight="10">
<s:Label text="{fullSelectedTable}" />
<s:Label>SQL Query:</s:Label>
<s:TextArea id="queryText" width="100%" height="160"/>
<s:Button label="Execute" width="100%" click="queryExecute();" />
<s:Label>Results:</s:Label>
<mx:AdvancedDataGrid id="queryResultGrid" width="100%" height="100%" dataProvider="{resultData}" />
</s:VGroup>
</s:NavigatorContent>
</mx:TabNavigator>
</s:VGroup>
</s:HGroup>

</s:WindowedApplication>

Thanks for reading!

No comments:

Post a Comment