Sunday, June 3, 2012

KirSQLite - Flex AIR Database Manager: Part 33

In this tutorial we'll continue making the "Join tables" feature.

First of all, we need to go to the joinTable() function and find the line where we add items to allTables ArrayCollection. Here, we'll add two more attributes to each object - table and database:

private function joinTable():void {
PopUpManager.addPopUp(joinTableWindow, this);
PopUpManager.centerPopUp(joinTableWindow);
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";
}

Now let's go to the getSchemaColumns() function and add two optional parameters to this function. Their values will be used in loadSchema() method - these are the table and database names. Set them to null by default, and check if these values were changed when executing the function - if so, set their values to what they were before we did the changes:

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

Now go to joinTableWindow TitleWindow and set an "enabled" property value for the button. Make the button enabled if there is a selected item in joinTableCombo:

<s:Button click="doJoinTable();" label="Join" width="100%" enabled="{joinTableCombo.selectedIndex>-1}" />

Now in the Declarations tags declare a new array collection called joinColumns:

<mx:ArrayCollection id="joinColumns">
</mx:ArrayCollection>

The ArrayCollection will be used to store the data about columns from both of the tables that are being joined. After the user hits "Join", a new TitleWindow opens and the user is asked to select which columns he would like to have in the new table. This is needed for various reasons. Each table most likely has its own primary key, and the user will choose to only leave one of them. Some columns might also have same names, and the user will only pick one.

Let's create this new TitleWindow object. Call it joinTableColumnsWindow, set its close event handler to closeJoinTableColumnsWindow(). Inside of the container, add an AdvancedDataGrid called joinColumnsGrid and dataProvider bound to joinColumns. Add 2 columns for it - the first one will be a column of checkboxes, so set its itemRenderer accordingly. The second will be called "Column" and have dataField set to "fullName". Then add a Button with a click event handler set to doJoinColumnsTable():

<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:columns>
</mx:AdvancedDataGrid>
<s:Button click="doJoinColumnsTable();" label="Join" width="100%"/>
</s:VGroup>
</mx:TitleWindow>

Now let's go to doJoinTable() and add code to it.

Firstly, we close the first TitleWindow and open the second:

closeJoinTableWindow();
PopUpManager.addPopUp(joinTableColumnsWindow, this);
PopUpManager.centerPopUp(joinTableColumnsWindow);

Then we need to read the full declaration strings for each column in two tables. Use getSchemaColumns() method. Thanks to the additions we made just now, we can supply it with specific table and database names. Merge two arrays into one:

// read all columns
var columns1:Array = getSchemaColumns();
var columns2:Array = getSchemaColumns(joinTableCombo.selectedItem.table, joinTableCombo.selectedItem.database);
var commoncolumns:Array = columns1.concat(columns2);

Now we'll need to read all the column names, without all the properties. We'll call loadSchema() method for this, and loop through the columns of the needed tables to add their names to an array. Do this for two arrays and merge these arrays into one:

// read all column names
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
var columnnames1:Array = [];
for (var t:int = 0; t < schema.tables[0].columns.length; t++) {
columnnames1.push(schema.tables[0].columns[t].name);
}

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

var commoncolumnnames:Array = columnnames1.concat(columnnames2);

Now we'll use all the data we have now to populate joinColumns ArrayCollection. Loop through one of the arrays we created and add Objects to joinColumns, which have 3 attributes - sel, fullName and name. I added a few lines to remove unneeded newline and blank symbols in full name:

// 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);
joinColumns.addItem({sel:true, fullName:fulln, name:commoncolumnnames[i]});
}

Full doJoinTable() function:

private function doJoinTable():void {
closeJoinTableWindow();
PopUpManager.addPopUp(joinTableColumnsWindow, this);
PopUpManager.centerPopUp(joinTableColumnsWindow);
// 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 = [];
for (var t:int = 0; t < schema.tables[0].columns.length; t++) {
columnnames1.push(schema.tables[0].columns[t].name);
}

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

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);
joinColumns.addItem({sel:true, fullName:fulln, name:commoncolumnnames[i]});
}
}

The closeJoinTableColumnsWindow() function:

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

The doJoinColumnsTable() function (temporary):

private function doJoinColumnsTable():void {
closeJoinTableColumnsWindow();
}

Thanks for reading!

Full code:

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

<fx:Declarations>
<fx:XML id="windowMenu">
<root>
<menuitem label="Database">
<menuitem id="newdb" label="New" key="n" controlKey="true" />
<menuitem id="opendb" label="Open" key="o" controlKey="true" />
<menuitem id="savedb" label="Save a copy" key="s" controlKey="true" enabled="{tableTree.selectedItems.length>0}"/>
</menuitem>
<menuitem label="Table">
<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>
</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="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:columns>
</mx:AdvancedDataGrid>
<s:Button click="doJoinColumnsTable();" label="Join" width="100%"/>
</s:VGroup>
</mx:TitleWindow>
</fx:Declarations>

<fx:Script>
<![CDATA[
import flash.data.SQLConnection;
import flash.data.SQLResult;
import flash.data.SQLSchema;
import flash.data.SQLSchemaResult;
import flash.data.SQLStatement;
import flash.errors.SQLError;
import flash.events.Event;
import flash.events.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.managers.PopUpManager;

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

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

private function menuSelect(evt:FlexNativeMenuEvent):void {
(evt.item.@id == "newdb")?(newDatabase()):(void);
(evt.item.@id == "opendb")?(openDatabase()):(void);
(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);
}

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 != "") {
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;
dataNode.appendChild(newTable);
}
}
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 {
saveTableButton.emphasized = false;
fullSelectedTable = "";
columnData = new ArrayCollection([]);
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;
}
if (tableTree.selectedItem.@isBranch) {
isTableSelected = false;
var dataname:String;
if (tableTree.selectedItem.@numid == 1) dataname = "main";
if (tableTree.selectedItem.@numid > 1) dataname = "db" + tableTree.selectedItem.@numid;
selectedDatabase = dataname;
}
if (tableTree.selectedItem.@isBranch == false) {
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));
}
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);
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);
}

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

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

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);
renameTableName.text = tableTree.selectedItem.@label;
}

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

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;
}
function queryError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}
}

private function copyTable():void {
PopUpManager.addPopUp(copyTableWindow, this);
PopUpManager.centerPopUp(copyTableWindow);
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);
}

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

private function doJoinTable():void {
closeJoinTableWindow();
PopUpManager.addPopUp(joinTableColumnsWindow, this);
PopUpManager.centerPopUp(joinTableColumnsWindow);
// 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 = [];
for (var t:int = 0; t < schema.tables[0].columns.length; t++) {
columnnames1.push(schema.tables[0].columns[t].name);
}

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

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);
joinColumns.addItem({sel:true, fullName:fulln, name:commoncolumnnames[i]});
}
}

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

private function doJoinColumnsTable():void {
closeJoinTableColumnsWindow();
}
]]>
</fx:Script>

<s:HGroup gap="0" width="100%" height="100%">
<s:VGroup width="200" height="100%" gap="0">
<s:HGroup width="200">
<s:Button label="New" click="newTable();" toolTip="New table" width="40" enabled="{tableTree.selectedItems.length>0}"/>
<s:Button label="Drop" click="dropTable();" toolTip="Drop table" width="40" enabled="{isTableSelected}" />
<s:Button label="Rename" click="renameTable();" toolTip="Rename table" width="40" enabled="{isTableSelected}" />
<s:Button label="Copy" click="copyTable();" toolTip="Copy table" width="40" enabled="{isTableSelected}" />
<s:Button label="Join" click="joinTable();" toolTip="Join table" width="40" enabled="{isTableSelected}" />
</s:HGroup>
<mx:Tree id="tableTree" width="100%" height="100%" dataProvider="{dbData}" showRoot="false" labelField="@label" itemClick="tableSelect();"/>
</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">
<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