Friday, January 23, 2015

KirSQLite Flex AIR Database Manager Part 44

In this tutorial well start working on Triggers.

A trigger in SQLite is a database operation that is automatically performed when an action happens. Think of it as an event listener and an event handler in one object.

You can read up on the usage and syntax of Triggers here.

First well need to make some icons to use for triggers. Im going to use the flag_red.png icon from FamFamFams Silk icon pack as the base. Except that Ill save it as flag.png in the lib folder of my project.


flag.png

Using photoshop, I made flag_add and flag_delete:


flag_add.png


flag_delete.png

Lets embed flag.png into a variable called iconTrigger:

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

Lets add a loop in loadDataSchema() that parses triggers and stores them in XML. Set each items type to "trigger" and icon to "iconTrigger":

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

Full function:

private function loadDataSchema(name:String):void {
if (name != "") {
isTreeEmpty = false;
connection.loadSchema(null, null, name, true, new Responder(schemaSuccess, schemaError));
function schemaSuccess(evt:SQLSchemaResult):void {
// Schema found! Now parsing:
var result:SQLSchemaResult = evt;
// Adding tables:
var nid:Number = (name=="main")?(1):(Number(name.replace("db", "")));
var dataNode:XMLList = dbData.db.(@numid == nid);
dataNode.setChildren(<placeholder/>);
delete dataNode.placeholder;
for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
newTable.@isBranch = false;
newTable.@databaseName = name;
newTable.@icon = "iconTable";
newTable.@type = "table";
dataNode.appendChild(newTable);
}
// 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;
}
}

Now declare a variable called isTriggerSelected, set it to false by default. This variable is similar to isViewSelceted, isIndexSelected and isTableSelected in usage.

[Bindable]
private var isTriggerSelected:Boolean = false;

Go to tableSelect() function and set isTriggerSelected to false:

isTriggerSelected = false;

Add an if...statement in this function that handlers triggers. It is similar to the one that handles indices - set isTriggerSelected to false, load a SQLTriggerSchema, change tab to Query and set queryText to the SQL query that was used to create this trigger. 

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

Full function:

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

Now lets add two buttons to the icon tool bar. Name them "New trigger" and "Drop trigger". Set their icon paths accordingly and click event handlers to newTrigger() and dropTrigger():

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

Add the functions, make them empty:

private function newTrigger():void { }

private function dropTrigger():void{ }

Now lets add an ability to add indices, views and triggers using the native menu. Go to windowMenu XML, add these 3 items under the Table menu:

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

Set their enabled properties bound to !isTreeEmpty and ids as shown above.

Handle these menu items in menuSelect():

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

Thats 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="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: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>
</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 != "") {
isTreeEmpty = false;
connection.loadSchema(null, null, name, true, new Responder(schemaSuccess, schemaError));
function schemaSuccess(evt:SQLSchemaResult):void {
// Schema found! Now parsing:
var result:SQLSchemaResult = evt;
// Adding tables:
var nid:Number = (name=="main")?(1):(Number(name.replace("db", "")));
var dataNode:XMLList = dbData.db.(@numid == nid);
dataNode.setChildren(<placeholder/>);
delete dataNode.placeholder;
for (var i:int = 0; i < result.tables.length; i++) {
var newTable:XML = new XML(<tb/>);
newTable.@label = result.tables[i].name;
newTable.@isBranch = false;
newTable.@databaseName = name;
newTable.@icon = "iconTable";
newTable.@type = "table";
dataNode.appendChild(newTable);
}
// 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;
}
}

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);
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();
}}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 {
loadDataSchema(selectedDatabase);
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] + "
");
}
}

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

Restoring 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 + "

Restoring 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("Cant 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("
", "");
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 { }

private function dropTrigger():void{ }
]]>
</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!
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)