Tutorial Online

private function parseImportJSONColumns():void {
importColumnData = new ArrayCollection([]);
var columns:Array = importGridJsonColumns;
var c:int;
var d:int;
var comboChoices:Array = [ { name:"---", empty:true } ];
if(columns.length>0){
// update the column data grid if "data only":
if(importJsonData == null || importJsonData.selected){
for (c = 0; c < columns.length; c++) {
comboChoices.push({name: columns[c].headerText, empty:false});
}
for (d = 0; d < columnData.length; d++) {
importColumnData.addItem( { tableColumn: columnData[d].name, jsonColumn: comboChoices[0], availableColumns: comboChoices } );
}
}
// update the column data grid if "full table":
if(importJsonData != null && importJsonFull.selected){
for (c = 0; c < columns.length; c++) {
comboChoices.push({name: columns[c].headerText, empty:false});
}
for (d = 1; d < comboChoices.length; d++) {
importColumnData.addItem( { tableColumn: comboChoices[d].name, jsonColumn: comboChoices[d], availableColumns: comboChoices } );
}
}
canImport = true;
}
}

var correct:Boolean = true;
var selected:String = "data";
if (importJsonData != null && importJsonFull.selected) selected = "full";
var errorMsg:String = "";

try{
var importedJSON:Object = new JSONDecoder(importedUTF, true).getValue();
}catch (e:Error){
correct = false;
Alert.show("The JSON string is malformed and cannot be parsed. Error: 
" + e, "Error");
return correct;
}

// "data only" selected and no data found
if (selected == "data" && (importedJSON.rows == null || importedJSON.rows.length == 0)) {
errorMsg += "No rows found.
"
correct = false;
}

// "full table" selected and no table name found
if (selected == "full" && importedJSON.tableName == null) {
errorMsg += "No tableName found.
"
correct = false;
}

// "full table" selected and no createTable node found
if (selected == "full" && importedJSON.createTable == null) {
errorMsg += "No createTable found.
"
correct = false;
}

if (!correct) Alert.show("The JSON string is malformed and cannot be parsed. Errors: 
" + errorMsg, "Error");
canImport = correct;
return correct;

private function checkJsonCorrect():Boolean {
var correct:Boolean = true;
var selected:String = "data";
if (importJsonData != null && importJsonFull.selected) selected = "full";
var errorMsg:String = "";

try{
var importedJSON:Object = new JSONDecoder(importedUTF, true).getValue();
}catch (e:Error){
correct = false;
Alert.show("The JSON string is malformed and cannot be parsed. Error: 
" + e, "Error");
return correct;
}

// "data only" selected and no data found
if (selected == "data" && (importedJSON.rows == null || importedJSON.rows.length == 0)) {
errorMsg += "No rows found.
"
correct = false;
}
// "full table" selected and no table name found
if (selected == "full" && importedJSON.tableName == null) {
errorMsg += "No tableName found.
"
correct = false;
}
// "full table" selected and no createTable node found
if (selected == "full" && importedJSON.createTable == null) {
errorMsg += "No createTable found.
"
correct = false;
}
if (!correct) Alert.show("The JSON string is malformed and cannot be parsed. Errors: 
" + errorMsg, "Error");
canImport = correct;
return correct;
}

private function parseImportJSON():void {
var columns:Array = [];
importGridJsonColumns = [];
importData = new ArrayCollection([]);
if (checkJsonCorrect() == false) return;
// import data
var importedJSON:Object = new JSONDecoder(importedUTF, true).getValue();
// decode data
for (var i:int = 0; i < importedJSON.rows.length; i++) {
for (var obj:Object in importedJSON.rows[i]) {
importedJSON.rows[i][obj] = symbolDecode(importedJSON.rows[i][obj]);
}
}
// apply data
importData = new ArrayCollection(importedJSON.rows);
// import columns
for (var col:Object in importedJSON.rows[0]) {
columns.push(new AdvancedDataGridColumn(String(col)));
}
importGridJsonColumns = columns;

parseImportJSONColumns();
}

<?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 id="settingsdb" label="Database settings" enabled="{hasDatabase}"/>
<menuitem id="unloaddb" label="Unload everything" enabled="{hasDatabase}"/>
</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="exportData">
</mx:ArrayCollection>
<mx:ArrayCollection id="importData">
</mx:ArrayCollection>
<mx:ArrayCollection id="importColumnData">
</mx:ArrayCollection>
<mx:ArrayCollection id="exportColumns">
</mx:ArrayCollection>
<mx:ArrayCollection id="tableData">
</mx:ArrayCollection>
<mx:ArrayCollection id="columnData">
</mx:ArrayCollection>
<mx:ArrayCollection id="resultData">
</mx:ArrayCollection>
<mx:ArrayCollection id="databaseData">
</mx:ArrayCollection>
<mx:ArrayCollection id="allTables">
</mx:ArrayCollection>
<mx:ArrayCollection id="joinColumns">
</mx:ArrayCollection>
<mx:ArrayCollection id="indexTableColumns">
</mx:ArrayCollection>
<mx:ArrayCollection id="triggerTableColumns">
</mx:ArrayCollection>
<mx:ArrayCollection id="triggerTables">
</mx:ArrayCollection>
<mx:ArrayCollection id="conflictTypes">
<fx:String>---</fx:String>
<fx:String>ABORT</fx:String>
<fx:String>FAIL</fx:String>
<fx:String>IGNORE</fx:String>
<fx:String>ROLLBACK</fx:String>
<fx:String>REPLACE</fx:String>
</mx:ArrayCollection>
<mx:ArrayCollection id="dataTypes">
<fx:String>NONE</fx:String>
<fx:String>INTEGER</fx:String>
<fx:String>TEXT</fx:String>
<fx:String>REAL</fx:String>
<fx:String>NUMERIC</fx:String>
</mx:ArrayCollection>
<mx:ArrayCollection id="triggerKeywordOptions">
<fx:String>BEFORE </fx:String>
<fx:String>AFTER </fx:String>
<fx:String>INSTEAD OF </fx:String>
<fx:String> </fx:String>
</mx:ArrayCollection>
<mx:ArrayCollection id="triggerActionOptions">
<fx:String>DELETE </fx:String>
<fx:String>INSERT </fx:String>
<fx:String>UPDATE </fx:String>
</mx:ArrayCollection>
<mx:AdvancedDataGridColumn id="checkboxColumn" headerText=" " width="30" sortable="false" editable="false">
<mx:itemRenderer>
<fx:Component>
<mx:Box width="30" horizontalAlign="center">
<mx:CheckBox selected="@{data.sel}" />
</mx:Box>
</fx:Component>
</mx:itemRenderer>
</mx:AdvancedDataGridColumn>
<mx:TitleWindow id="newTableWindow" title="Create new table" close="closeNewTableWindow();" showCloseButton="true">
<s:VGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table name: </s:Label>
<s:TextInput id="newTableName" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Key column: </s:Label>
<s:TextInput id="keyName" />
</s:HGroup>
<s:Button click="createNewTable();" label="Create" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="newRecordWindow" title="Add new record" close="closeNewRecordWindow();" showCloseButton="true">
<s:VGroup width="100%" height="100%">
<mx:AdvancedDataGrid id="recordDataGrid" width="100%" height="50" editable="true"/>
<s:Button click="addNewRecord();" label="Add record" width="100%"/>
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="historyWindow" title="SQL History" close="closeHistoryWindow();" showCloseButton="true">
<mx:Box width="100%" height="100%" paddingLeft="10" paddingRight="10" paddingTop="10" paddingBottom="10">
<s:TextArea id="historyText" width="100%" height="100%" editable="false" />
</mx:Box>
</mx:TitleWindow>
<mx:TitleWindow id="renameTableWindow" title="Rename table" close="closeRenameTableWindow();" showCloseButton="true">
<s:VGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table name: </s:Label>
<s:TextInput id="renameTableName" />
</s:HGroup>
<s:Button click="doRenameTable();" label="Rename" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="copyTableWindow" title="Copy table" close="closeCopyTableWindow();" showCloseButton="true">
<s:VGroup paddingTop="8">
<s:Label id="selectedCopyTable" />
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Destination database: </s:Label>
<mx:ComboBox editable="false" id="copyDestinationDatabase" dataProvider="{databaseData}" labelField="name" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>New table name: </s:Label>
<s:TextInput id="copyTableName" />
</s:HGroup>
<s:Button click="doCopyTable();" label="Copy" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="joinTableWindow" title="Join two tables" close="closeJoinTableWindow();" showCloseButton="true">
<s:VGroup paddingTop="4">
<s:Label id="selectedJoinTable" />
<mx:ComboBox editable="false" id="joinTableCombo" dataProvider="{allTables}" labelField="name" width="100%" />
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>New table name: </s:Label>
<s:TextInput id="joinTableName" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Destination database: </s:Label>
<mx:ComboBox editable="false" id="joinDestinationDatabase" dataProvider="{databaseData}" labelField="name" />
</s:HGroup>
<s:Button click="doJoinTable();" label="Join" width="100%" enabled="{joinTableCombo.selectedIndex>-1}" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="joinTableColumnsWindow" title="Choose which columns to leave" close="closeJoinTableColumnsWindow();" showCloseButton="true">
<s:VGroup paddingTop="4">
<mx:AdvancedDataGrid id="joinColumnsGrid" width="500" height="300" dataProvider="{joinColumns}" editable="false">
<mx:columns>
<mx:AdvancedDataGridColumn headerText=" " width="30" sortable="false" draggable="false" resizable="false" editable="false">
<mx:itemRenderer>
<fx:Component>
<mx:Box width="30" horizontalAlign="center">
<mx:CheckBox selected="@{data.sel}" />
</mx:Box>
</fx:Component>
</mx:itemRenderer>
</mx:AdvancedDataGridColumn>
<mx:AdvancedDataGridColumn dataField="fullName" headerText="Column" />
<mx:AdvancedDataGridColumn dataField="tableName" headerText="Table" width="90" />
</mx:columns>
</mx:AdvancedDataGrid>
<s:Button click="doJoinColumnsTable();" label="Join" width="100%"/>
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="indexWindow" title="Add a new index" close="closeIndexWindow();" showCloseButton="true">
<s:VGroup paddingTop="4">
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>New index name: </s:Label>
<s:TextInput id="indexName" />
</s:HGroup>
<mx:CheckBox label="Unique" id="indexUnique"/>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table: </s:Label>
<mx:ComboBox editable="false" id="indexTableCombo" dataProvider="{allTables}" labelField="name" width="100%" change="updateIndexTableColumns();"/>
</s:HGroup>
<s:Label>Column(s):</s:Label>
<s:List allowMultipleSelection="true" width="100%" height="160" id="indexColumnList" dataProvider="{indexTableColumns}" />
<s:Button click="doNewIndex();" label="Add index" width="100%" enabled="{indexColumnList.selectedIndex>-1}" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="viewWindow" title="Add a new view" close="closeViewWindow();" showCloseButton="true" width="400">
<s:VGroup paddingTop="4" width="100%">
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>View name: </s:Label>
<s:TextInput id="viewName" width="100%" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Destination database: </s:Label>
<mx:ComboBox editable="false" id="viewDestinationDatabase" dataProvider="{databaseData}" labelField="name" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Select statement: </s:Label>
<s:TextInput id="viewSelect" width="100%" />
</s:HGroup>
<s:Button click="doNewView();" label="Add view" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="triggerWindow" title="Add a new trigger" close="closeTriggerWindow();" showCloseButton="true" width="400">
<s:VGroup paddingTop="4" width="100%">
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Trigger name: </s:Label>
<s:TextInput id="triggerName" width="100%" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Trigger database: </s:Label>
<mx:ComboBox editable="false" id="triggerDatabase" dataProvider="{databaseData}" labelField="name" change="updateTriggerTables();" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Table: </s:Label>
<mx:ComboBox editable="false" id="triggerTable" dataProvider="{triggerTables}" labelField="name" change="updateTriggerTableColumns();" />
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Keyword: </s:Label>
<mx:ComboBox editable="false" id="triggerKeyword" dataProvider="{triggerKeywordOptions}"/>
</s:HGroup>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Action: </s:Label>
<mx:ComboBox editable="false" id="triggerAction" dataProvider="{triggerActionOptions}"/>
</s:HGroup>
<s:Label>Optional: UPDATE column(s): </s:Label>
<s:List enabled="{triggerAction.selectedIndex==2}" allowMultipleSelection="true" width="100%" height="100" id="triggerColumnList" dataProvider="{triggerTableColumns}" />
<s:CheckBox id="triggerForEachRow" label="FOR EACH ROW" />
<s:Label>Optional: WHEN</s:Label>
<s:TextArea id="triggerWhen" width="100%" height="50" />
<s:Label>BEGIN</s:Label>
<s:TextArea id="triggerBegin" width="100%" height="100" />
<s:Label>END</s:Label>
<s:Button click="doNewTrigger();" label="Add trigger" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="settingsWindow" title="Database settings" close="closeSettingsWindow();" showCloseButton="true" width="400">
<s:VGroup paddingTop="4" width="100%">
<s:Button click="doAnalyze();" label="Analyze all databases" width="100%" />
<s:Button click="doDeanalyze();" label="Deanalyze all databases" width="100%" />
<s:Button click="doCompact();" id="buttonCompact" label="Compact main database" width="100%" />
<s:Button click="doReencrypt();" id="buttonReencrypt" label="Reencrypt main database" enabled="{isMainEncrypted}" width="100%" />
<s:Label id="settingsMessage" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="encryptWindow" title="Encrypt database" close="closeEncryptWindow();" showCloseButton="true" width="500">
<s:VGroup paddingTop="4" width="100%">
<s:Label>Choose an encryption password if you want to encrypt the database.</s:Label>
<s:Label>Otherwise, leave the field blank.</s:Label>
<s:TextInput id="encryptField" width="100%"/>
<s:Button label="Continue" click="doEncryptDatabase();" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="openEncryptedWindow" title="Open an encrypted database" close="closeEncryptedWindow();" showCloseButton="true" width="400">
<s:VGroup paddingTop="4" width="100%">
<s:Label>Enter encryption password:</s:Label>
<s:TextInput id="openEncryptedField" width="100%" />
<s:Button click="doOpenEncrypted();" label="Open database" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="reencryptWindow" title="Reencrypt database" close="closeReencryptWindow();" showCloseButton="true" width="500">
<s:VGroup paddingTop="4" width="100%">
<s:Label>New encryption password:</s:Label>
<s:TextInput id="reencryptField" width="100%"/>
<s:Button label="Continue" click="doReencryptDatabase();" width="100%" />
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="exportWindow" title="Export data" close="closeExportWindow();" showCloseButton="true" width="500">
<s:VGroup paddingTop="4" width="100%">
<mx:AdvancedDataGrid dataProvider="{exportData}" width="100%" height="300" id="exportGrid" />
<mx:TabNavigator width="100%" height="120" id="exportNavigator">
<s:NavigatorContent label="CSV" width="100%">
<s:VGroup width="100%" height="100%" paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10">
<mx:CheckBox id="exportHeaders" label="Include headers" selected="true" />
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Separator: </s:Label>
<s:RadioButton groupName="csvSeparator" id="separatorComma" label="Comma" selected="true" />
<s:RadioButton groupName="csvSeparator" id="separatorSemicolon" label="Semi-colon" />
<s:RadioButton groupName="csvSeparator" id="separatorTab" label="Tab" />
<s:RadioButton groupName="csvSeparator" id="separatorPipe" label="Pipe" />
</s:HGroup>
<s:Button label="Export" click="doExportCSV();" width="100%" />
</s:VGroup>
</s:NavigatorContent>
<s:NavigatorContent label="SQL" width="100%">
<s:VGroup width="100%" height="100%" paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10">
<s:HGroup width="100%" verticalAlign="middle">
<s:RadioButton groupName="exportSqlMode" id="exportSqlFull" label="Export full table" selected="true" />
<s:RadioButton groupName="exportSqlMode" id="exportSqlData" label="Export data only" />
</s:HGroup>
<s:Button label="Export" click="doExportSQL();" width="100%" />
</s:VGroup>
</s:NavigatorContent>
<s:NavigatorContent label="XML" width="100%">
<s:VGroup width="100%" height="100%" paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10">
<s:HGroup width="100%" verticalAlign="middle">
<s:RadioButton groupName="exportXmlMode" id="exportXmlFull" label="Export full table" selected="true" />
<s:RadioButton groupName="exportXmlMode" id="exportXmlData" label="Export data only" />
</s:HGroup>
<s:Button label="Export" click="doExportXML();" width="100%" />
</s:VGroup>
</s:NavigatorContent>
<s:NavigatorContent label="JSON" width="100%">
<s:VGroup width="100%" height="100%" paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10">
<s:HGroup width="100%" verticalAlign="middle">
<s:RadioButton groupName="exportJsonMode" id="exportJsonFull" label="Export full table" selected="true" />
<s:RadioButton groupName="exportJsonMode" id="exportJsonData" label="Export data only" />
</s:HGroup>
<s:Button label="Export" click="doExportJSON();" width="100%" />
</s:VGroup>
</s:NavigatorContent>
</mx:TabNavigator>
</s:VGroup>
</mx:TitleWindow>
<mx:TitleWindow id="importWindow" title="Import data" close="closeImportWindow();" showCloseButton="true" width="500">
<s:VGroup paddingTop="4" width="100%">
<s:Label>Import from: </s:Label>
<mx:TabNavigator width="100%" height="460" id="importNavigator" change="parseImport();">
<s:NavigatorContent label="CSV" width="100%">
<mx:VBox width="100%" height="100%" paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10">
<mx:AdvancedDataGrid dataProvider="{importData}" width="100%" height="200" id="importGrid" />
<mx:CheckBox id="importHeaders" label="First line is a headers line" selected="true" change="parseImport();" />
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>Separator: </s:Label>
<s:RadioButton groupName="importSeparator" id="importSeparatorComma" label="Comma" selected="true" click="parseImport();"/>
<s:RadioButton groupName="importSeparator" id="importSeparatorSemicolon" label="Semi-colon" click="parseImport();" />
<s:RadioButton groupName="importSeparator" id="importSeparatorTab" label="Tab" click="parseImport();" />
<s:RadioButton groupName="importSeparator" id="importSeparatorPipe" label="Pipe" click="parseImport();" />
</s:HGroup>
<s:Label>Assign CSV columns to table columns:</s:Label>
<mx:AdvancedDataGrid id="importCsvColumnGrid" width="100%" height="100%" dataProvider="{importColumnData}">
<mx:columns>
<mx:AdvancedDataGridColumn headerText="Table column" dataField="tableColumn" sortable="false" editable="false" />
<mx:AdvancedDataGridColumn headerText="CSV column" sortable="false" editable="false">
<mx:itemRenderer>
<fx:Component>
<mx:Box width="30" horizontalAlign="center">
<mx:ComboBox selectedItem="@{data.csvColumn}" dataProvider="{data.availableColumns}" labelField="name"/>
</mx:Box>
</fx:Component>
</mx:itemRenderer>
</mx:AdvancedDataGridColumn>
</mx:columns>
</mx:AdvancedDataGrid>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>On conflict: </s:Label>
<mx:ComboBox id="importConflict" dataProvider="{conflictTypes}" editable="false" />
</s:HGroup>
</mx:VBox>
</s:NavigatorContent>
<s:NavigatorContent label="SQL" width="100%">
<mx:VBox width="100%" height="100%" paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10">
<s:Label>Query:</s:Label>
<s:TextArea width="100%" height="100%" id="importQueryText" text="@{importQuery}" />
</mx:VBox>
</s:NavigatorContent>
<s:NavigatorContent label="XML" width="100%">
<mx:VBox width="100%" height="100%" paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10">
<mx:AdvancedDataGrid dataProvider="{importData}" width="100%" height="200" id="importGridXml" columns="{importGridXmlColumns}" />
<s:HGroup width="100%" verticalAlign="middle">
<s:RadioButton groupName="importXmlMode" id="importXmlFull" label="Import full table" click="parseImportXML();" />
<s:RadioButton groupName="importXmlMode" id="importXmlData" label="Import data only" selected="true"  click="parseImportXML();" />
</s:HGroup>
<s:Label>Assign XML nodes to table columns:</s:Label>
<mx:AdvancedDataGrid id="importXmlColumnGrid" width="100%" height="100%" dataProvider="{importColumnData}">
<mx:columns>
<mx:AdvancedDataGridColumn headerText="Table column" dataField="tableColumn" sortable="false" editable="false" />
<mx:AdvancedDataGridColumn headerText="XML node" sortable="false" editable="false">
<mx:itemRenderer>
<fx:Component>
<mx:Box width="30" horizontalAlign="center">
<mx:ComboBox selectedItem="@{data.xmlColumn}" dataProvider="{data.availableColumns}" labelField="name"/>
</mx:Box>
</fx:Component>
</mx:itemRenderer>
</mx:AdvancedDataGridColumn>
</mx:columns>
</mx:AdvancedDataGrid>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>On conflict: </s:Label>
<mx:ComboBox id="importXmlConflict" dataProvider="{conflictTypes}" editable="false" />
</s:HGroup>
</mx:VBox>
</s:NavigatorContent>
<s:NavigatorContent label="JSON" width="100%">
<mx:VBox width="100%" height="100%" paddingTop="10" paddingLeft="10" paddingRight="10" paddingBottom="10">
<mx:AdvancedDataGrid dataProvider="{importData}" width="100%" height="200" id="importGridJson" columns="{importGridJsonColumns}" />
<s:HGroup width="100%" verticalAlign="middle">
<s:RadioButton groupName="importJsonMode" id="importJsonFull" label="Import full table" click="parseImportJSON();" />
<s:RadioButton groupName="importJsonMode" id="importJsonData" label="Import data only" selected="true"  click="parseImportJSON();" />
</s:HGroup>
<s:Label>Assign JSON attributes to table columns:</s:Label>
<mx:AdvancedDataGrid id="importJsonColumnGrid" width="100%" height="100%" dataProvider="{importColumnData}">
<mx:columns>
<mx:AdvancedDataGridColumn headerText="Table column" dataField="tableColumn" sortable="false" editable="false" />
<mx:AdvancedDataGridColumn headerText="JSON attribute" sortable="false" editable="false">
<mx:itemRenderer>
<fx:Component>
<mx:Box width="30" horizontalAlign="center">
<mx:ComboBox selectedItem="@{data.jsonColumn}" dataProvider="{data.availableColumns}" labelField="name"/>
</mx:Box>
</fx:Component>
</mx:itemRenderer>
</mx:AdvancedDataGridColumn>
</mx:columns>
</mx:AdvancedDataGrid>
<s:HGroup width="100%" verticalAlign="middle">
<s:Label>On conflict: </s:Label>
<mx:ComboBox id="importJsonConflict" dataProvider="{conflictTypes}" editable="false" />
</s:HGroup>
</mx:VBox>
</s:NavigatorContent>
</mx:TabNavigator>
<s:Button label="Import" click="doImport();" width="100%" enabled="{canImport}" />
</s:VGroup>
</mx:TitleWindow>
</fx:Declarations>

<fx:Script>
<![CDATA[
import com.adobe.serialization.json.JSONEncoder;
import com.adobe.serialization.json.JSONDecoder;
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.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;
import flash.filesystem.FileStream;
import flash.net.FileFilter;
import flash.net.FileReference;
import flash.net.Responder;
import flash.ui.ContextMenu;
import flash.ui.ContextMenuItem;
import flash.utils.ByteArray;
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;
import com.adobe.air.crypto.EncryptionKeyGenerator;

[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;
[Bindable]
private var hasDatabase:Boolean = false;
private var tempFileInfo:Object;
[Bindable]
private var isMainEncrypted:Boolean = false;
private var importedUTF:String;
[Bindable]
private var importQuery:String = "";
[Bindable]
private var importGridXmlColumns:Array = [];
[Bindable]
private var importGridJsonColumns:Array = [];
[Bindable]
private var canImport:Boolean = false;

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);
(evt.item.@id == "settingsdb")?(databaseSettings()):(void);
(evt.item.@id == "unloaddb")?(doUnload()):(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;
function newSelect(evt:Event):void {
if (file.exists) {
Alert.show("File already exists, cannot overwrite.", "Nope");
return;
}
file.nativePath += ".db";
tempFileInfo = {f: file};
encryptDatabase();
}
}

private function encryptDatabase():void {
PopUpManager.addPopUp(encryptWindow, this);
PopUpManager.centerPopUp(encryptWindow);
enableEverything = false;
encryptField.text = "";
}

private function doEncryptDatabase():void {
var password:String = encryptField.text;
var keyGenerator:EncryptionKeyGenerator = new EncryptionKeyGenerator(); 
var file:File = tempFileInfo.f;
if (password == "") {
parseDatabase(file);
closeEncryptWindow();
return;
}
if (!keyGenerator.validateStrongPassword(password)) {
Alert.show("The password must be 8-32 characters long. It must contain at least one lowercase letter, at least one uppercase letter, and at least one number or symbol.", "Error");
return;
}
var encryptionKey:ByteArray = keyGenerator.getEncryptionKey(password);

parseDatabase(file, false, encryptionKey);
closeEncryptWindow();
}

private function closeEncryptWindow():void {
PopUpManager.removePopUp(encryptWindow);
enableEverything = true;
}

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 {
parseDatabase(file, true);
}
}

private function saveCopy():void {
var databasePath:String;
if (selectedDatabase == "main") databasePath = dbData.db[0].@path;
if (selectedDatabase != "main") {
var newNum:int = Number(selectedDatabase.replace("db", ""));
var newInd:int;
for (var i:int = 0; i < dbData.db.length(); i++) {
if (dbData.db[i].@numid == newNum) {
newInd = i;
break;
}
}
databasePath = dbData.db[newInd].@path;
}
var file:File = new File(databasePath);
file.browseForSave("Save copy of database");
file.addEventListener(Event.SELECT, onCopySelect);
function onCopySelect(evt:Event):void {
if(notAlreadyOpen(file)){
var initFile:File = new File(databasePath);
initFile.copyTo(file, true);
}else {
Alert.show("Cannot overwrite a file that is currently open.", "Nope");
}
}
}

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

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

private function parseDatabase(file:File, needCheck:Boolean = false, enKey:ByteArray = null):void {
if (!needCheck || file.exists) {
if(!needCheck || notAlreadyOpen(file)){
var newDB:XML;
if (dbData.db.length() == 0) {
try{
connection.open(file, "create", false, 1024, enKey);
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);
if (enKey != null) isMainEncrypted = true;
loadDataSchema("main");
hasDatabase = true;
} catch (evt:SQLError) {
if(evt.errorID != EncryptionKeyGenerator.ENCRYPTED_DB_PASSWORD_ERROR_ID){
Alert.show("ERROR: " + evt.details, "Error"); 
}else{
tempFileInfo = { f:file, op: "open" };
openEncrypted();
}
}
}else
if (dbData.db.length() > 0) {
var newnum:int = dbData.db.length() + 1;
connection.attach("db" + newnum.toString(), file, new Responder(attachSuccess, attachError), enKey);
function attachSuccess():void {
newDB = <db/>
newDB.@label = file.name + "(db" + newnum.toString() + ")";
newDB.@name = file.name;
newDB.@numid = newnum.toString();
newDB.@isBranch = true;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
loadDataSchema("db" + newnum.toString());
}
function attachError(evt:SQLError):void {
if(evt.errorID != EncryptionKeyGenerator.ENCRYPTED_DB_PASSWORD_ERROR_ID){
Alert.show("ERROR: " + evt.details, "Error"); 
}else{
tempFileInfo = { f:file, op: "attach" };
openEncrypted();
}
}
}}else {
Alert.show("Database already opened.", "Error");
}
}else {
Alert.show("File not found.", "Error");
}
}

private function notAlreadyOpen(file:File):Boolean{ 
var r:Boolean = true;
for (var i:int = 0; i < dbData.db.length(); i++) {
if (file.nativePath == dbData.db[i].@path) {
r = false;
}
}
return r;
}

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();
//if(schema!=null{
for (var i:int = 0; i < schema.tables[0].columns.length; i++) {
columnData.addItem({name:schema.tables[0].columns[i].name});
var aColumn:AdvancedDataGridColumn = new AdvancedDataGridColumn();
aColumn.headerText = schema.tables[0].columns[i].name;
aColumn.dataField = "db_" + schema.tables[0].columns[i].name;
if (schema.tables[0].columns[i].autoIncrement) aColumn.editable = false;
if (schema.tables[0].columns[i].primaryKey) tableTree.selectedItem.@primaryKeyColumn = schema.tables[0].columns[i].name;
newColumns.push(aColumn);
}
//}
tableGrid.columns = newColumns;
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT * FROM " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
stat.execute(-1, new Responder(tableSuccess, tableError));
}
// view
if (tableTree.selectedItem.@isBranch == false && tableTree.selectedItem.@type == "view") {
isViewSelected = true;
connection.loadSchema(SQLViewSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var viewschema:SQLSchemaResult = connection.getSchemaResult();
if(tabNavigator.selectedIndex!=2){
tabNavigator.addEventListener(IndexChangedEvent.CHANGE, tabChangeView);
tabNavigator.selectedIndex = 2;
}else {
queryText.text = viewschema.views[0].sql;
displayView();
}
function tabChangeView(evt:IndexChangedEvent):void {
queryText.text = viewschema.views[0].sql;
displayView();
tabNavigator.removeEventListener(IndexChangedEvent.CHANGE, tabChangeView);
}
}
// trigger
if (tableTree.selectedItem.@isBranch == false && tableTree.selectedItem.@type == "trigger") {
isTriggerSelected = true;
connection.loadSchema(SQLTriggerSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var triggerschema:SQLSchemaResult = connection.getSchemaResult();
if(tabNavigator.selectedIndex!=2){
tabNavigator.addEventListener(IndexChangedEvent.CHANGE, tabChangeTrigger);
tabNavigator.selectedIndex = 2;
}else {
queryText.text = triggerschema.triggers[0].sql;
}
function tabChangeTrigger(evt:IndexChangedEvent):void {
queryText.text = triggerschema.triggers[0].sql;
tabNavigator.removeEventListener(IndexChangedEvent.CHANGE, tabChangeTrigger);
}
}
// index
if (tableTree.selectedItem.@isBranch == false && tableTree.selectedItem.@type == "index") {
isIndexSelected = true;
connection.loadSchema(SQLIndexSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var indschema:SQLSchemaResult = connection.getSchemaResult();
if(tabNavigator.selectedIndex!=2){
tabNavigator.addEventListener(IndexChangedEvent.CHANGE, tabChange);
tabNavigator.selectedIndex = 2;
}else {
queryText.text = indschema.indices[0].sql;
}
function tabChange(evt:IndexChangedEvent):void {
queryText.text = indschema.indices[0].sql;
tabNavigator.removeEventListener(IndexChangedEvent.CHANGE, tabChange);
}
}
function tableSuccess(evt:SQLResult):void {
if (evt.data != null) {
for (var item:Object in evt.data) {
var obj:Object = new Object();
for (var value:Object in evt.data[item]) {
obj["db_"+value] = evt.data[item][value];
}
tableData.addItem(obj);
}
}
}
function tableError(evt:SQLError):void {
Alert.show("Unable to read table data.", "Error");
}
}

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

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

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

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

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

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

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 statements:Array = queryText.text.split(";");
for (var i:int = 0; i < statements.length; i++) {
var replacedString:String = statements[i].replace(" ", "");
replacedString = replacedString.replace("
", "");
if (replacedString!=""){
executeStatement(statements[i]);
lastStatement(statements[i]);
}
}
}
}

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

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

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

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

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

lastStatement(sqlStat);

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

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

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

private function databaseSettings():void {
PopUpManager.addPopUp(settingsWindow, this);
PopUpManager.centerPopUp(settingsWindow);
enableEverything = false;
buttonCompact.label = "Compact main database (" + dbData..db[0].@name + ")";
buttonReencrypt.label = "Reencrypt main database (" + dbData..db[0].@name + ")";
settingsMessage.text = "";
}

private function closeSettingsWindow():void{
PopUpManager.removePopUp(settingsWindow);
enableEverything = true;
}

private function doAnalyze():void {
connection.analyze(null, new Responder(analyzeSuccess, analyzeError));
function analyzeSuccess():void {
settingsMessage.text = "All databases successfully analyzed!";
}
function analyzeError(evt:SQLError):void {
settingsMessage.text = "An error occured.";
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function doDeanalyze():void {
connection.deanalyze(new Responder(deanalyzeSuccess, deanalyzeError));
function deanalyzeSuccess():void {
settingsMessage.text = "All databases successfully deanalyzed!";
}
function deanalyzeError(evt:SQLError):void {
settingsMessage.text = "An error occured.";
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function doCompact():void {
connection.compact(new Responder(compactSuccess, compactError));
function compactSuccess():void {
settingsMessage.text = "Main database successfully compacted!";
}
function compactError(evt:SQLError):void {
settingsMessage.text = "An error occured.";
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function doReencrypt():void {
closeSettingsWindow();
PopUpManager.addPopUp(reencryptWindow, this);
PopUpManager.centerPopUp(reencryptWindow);
reencryptField.text = "";
enableEverything = false;
}

private function closeReencryptWindow():void {
PopUpManager.removePopUp(reencryptWindow);
enableEverything = true;
}

private function doReencryptDatabase():void {
var keyGenerator:EncryptionKeyGenerator = new EncryptionKeyGenerator();
if (!keyGenerator.validateStrongPassword(reencryptField.text)) {
Alert.show("The password must be 8-32 characters long. It must contain at least one lowercase letter, at least one uppercase letter, and at least one number or symbol.", "Error");
return;
}
var encryptionKey:ByteArray = keyGenerator.getEncryptionKey(reencryptField.text);
connection.reencrypt(encryptionKey, new Responder(reencryptSuccess, reencryptError));
function reencryptSuccess():void {
closeReencryptWindow();
}
function reencryptError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error"); 
}
}

private function openEncrypted():void {
PopUpManager.addPopUp(openEncryptedWindow, this);
PopUpManager.centerPopUp(openEncryptedWindow);
openEncryptedField.text = "";
focusManager.setFocus(openEncryptedField);
enableEverything = false;
}

private function closeEncryptedWindow():void {
PopUpManager.removePopUp(openEncryptedWindow);
enableEverything = true;
}

private function doOpenEncrypted():void {
var keyGenerator:EncryptionKeyGenerator = new EncryptionKeyGenerator();
if (!keyGenerator.validateStrongPassword(openEncryptedField.text)) {
Alert.show("The password must be 8-32 characters long. It must contain at least one lowercase letter, at least one uppercase letter, and at least one number or symbol.", "Error");
return;
}
var newDB:XML;
var newnum:int = dbData.db.length() + 1;
var bytearray:ByteArray = keyGenerator.getEncryptionKey(openEncryptedField.text);
var file:File = tempFileInfo.f;

if (tempFileInfo.op == "open") {
try{
connection.open(file, "create", false, 1024, bytearray);
openEncryptedSuccess();
} catch (error:SQLError) {
openEncryptedError(error);
}
}
if (tempFileInfo.op == "attach") {
connection.attach("db" + newnum.toString(), file, new Responder(attachEncryptedSuccess, openEncryptedError), bytearray);
}
function openEncryptedSuccess():void {
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);
loadDataSchema("main");
hasDatabase = true;
isMainEncrypted = true;
closeEncryptedWindow();
}
function attachEncryptedSuccess():void {
newDB = <db/>
newDB.@label = file.name + "(db" + newnum.toString() + ")";
newDB.@name = file.name;
newDB.@numid = newnum.toString();
newDB.@isBranch = true;
newDB.@path = file.nativePath;
dbData[0].appendChild(newDB);
loadDataSchema("db" + newnum.toString());
closeEncryptedWindow();
}
function openEncryptedError(evt:SQLError):void {
if(evt.errorID == EncryptionKeyGenerator.ENCRYPTED_DB_PASSWORD_ERROR_ID){
Alert.show("Invalid encryption key. ", "Error"); 
}else {
Alert.show(evt.message + " " + evt.details, "Error"); 
}
}
}

private function doUnload():void {
Alert.show("Close all databases?", "Confirm", Alert.YES | Alert.NO, this, unloadClose);
function unloadClose(evt:CloseEvent):void {
if(evt.detail == Alert.YES){
connection.close();
resetVariables();
}
}
}

private function deleteDatabase():void {
var parentRef:* = this;
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);

Alert.show("You are about to delete a database at this location:
" + databasePath + "
Are you sure?", "Confirm", Alert.YES | Alert.NO, this, deleteClose);

function deleteClose(evt:CloseEvent):void {
if (evt.detail == Alert.YES) {
if (selectedDatabase == "main") {
if (dbData..db.length() > 1) {
Alert.show("Since the database youre trying to delete is the main database, all the remaining opened databases will have to be closed to proceed. Continue?", "Confirm", Alert.YES | Alert.NO, parentRef, closeAllClose);
}
if (dbData..db.length() == 1) {
closeAndDelete();
}
}
if (selectedDatabase != "main") {
var xmlNode:XMLList = dbData.db.(@path == databasePath);
xmlNode[0] = <deleteThis/>;
delete dbData.deleteThis;
isTableSelected = false;
isViewSelected = false;
isIndexSelected = false;
isTriggerSelected = false;
connection.detach(selectedDatabase, new Responder(deleteFile, null));
}
}
}

function closeAllClose(evt:CloseEvent):void{
if (evt.detail == Alert.YES) {
closeAndDelete();
}
}

function closeAndDelete():void {
connection.close(new Responder(deleteFile, null));
}

function deleteFile():void {
if (!connection.connected) {
resetVariables();
}
file.moveToTrash();
refreshEverything();
}
}

private function resetVariables():void{
connection = new SQLConnection();
dbData = new XMLList();
tableData = new ArrayCollection([]);
columnData = new ArrayCollection([]);
resultData = new ArrayCollection([]);
isMainEncrypted = false;
isTreeEmpty = true;
isTableSelected = false;
isViewSelected = false;
isIndexSelected = false;
isTriggerSelected = false;
hasDatabase = false;
}

private function exportTable():void {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = "SELECT * FROM " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
stat.execute( -1, new Responder(selectSuccess, selectError));
function selectSuccess(evt:SQLResult):void {
exportCSV(evt.data);
}
function selectError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}

private function exportQuery():void {
var query:String = queryText.text;
if (query.indexOf(";") > 0) {
query = query.substr(0, query.indexOf(";"));
}
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = query;
stat.execute( -1, new Responder(selectSuccess, selectError));
lastStatement(stat.text);
function selectSuccess(evt:SQLResult):void {
exportCSV(evt.data);
}
function selectError(evt:SQLError):void {
Alert.show("ERROR: " + evt.details, "Error");
}
}

private function exportCSV(obj:Object):void {
PopUpManager.addPopUp(exportWindow, this);
PopUpManager.centerPopUp(exportWindow);
enableEverything = false;
exportData = new ArrayCollection([]);
exportColumns = new ArrayCollection([]);
var advColumns:Array = [];
if (obj != null) {
// get the columns
for (var col:Object in obj[0]) {
exportColumns.addItem(String(col));
advColumns.push(new AdvancedDataGridColumn(String(col)));
}
exportGrid.columns = advColumns;
// get the data
for (var i:int = 0; i < obj.length; i++) {
exportData.addItem(obj[i]);
}
}
}

private function doExportCSV():void {
var finalString:String = "";
var separator:String;
if (separatorComma.selected) separator = ",";
if (separatorSemicolon.selected) separator = ";";
if (separatorTab.selected) separator = "	";
if (separatorPipe.selected) separator = "|";

if (exportHeaders.selected) {
for (var i:int = exportColumns.length-1; i >= 0; i--) {
finalString += exportColumns[i] + separator;
}
finalString = finalString.substr(0, finalString.length - 1);
}

for (var u:int = 0; u < exportData.length; u++) {
finalString += "
";
for (var t:int = exportColumns.length-1; t >= 0; t--) {
var val:String = exportData[u][exportColumns[t]];
if (val == null) val = "";
finalString += val + separator;
}
finalString = finalString.substr(0, finalString.length - 1);
}

if (!exportHeaders.selected) {
finalString = finalString.substr(1);
}

var file:File = File.documentsDirectory.resolvePath("exported_data.csv");
file.browseForSave("Save the exported CSV file");
file.addEventListener(Event.SELECT, exportSelect);

function exportSelect(evt:Event):void {
var filestream:FileStream = new FileStream();
filestream.open(file, FileMode.WRITE);
filestream.writeUTFBytes(finalString);
filestream.close();
}
}

private function doExportSQL():void {
var finalString:String = "";

if (exportSqlFull.selected) {
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
finalString += schema.tables[0].sql + ";
";
}

for (var u:int = 0; u < exportData.length; u++) {
var arr:Array = [];
for (var t:int = exportColumns.length - 1; t >= 0; t--) {
var val:String;
var dataValue:String = exportData[u][exportColumns[t]];

if (isNaN(Number(dataValue))) val = " + dataValue + ";
if (!isNaN(Number(dataValue))) val = dataValue;
if (val == "" || dataValue==null) val = "NULL";
arr.push(val);
}
finalString += "INSERT INTO " + tableTree.selectedItem.@label + " (" + String(exportColumns) + ") VALUES (" + String(arr) + ");
"
}

var file:File = File.documentsDirectory.resolvePath("exported_data.sql");
file.browseForSave("Save the exported SQL file");
file.addEventListener(Event.SELECT, exportSelect);

function exportSelect(evt:Event):void {
var filestream:FileStream = new FileStream();
filestream.open(file, FileMode.WRITE);
filestream.writeUTFBytes(finalString);
filestream.close();
}
}

private function doExportXML():void {
var tableXML:XML = new XML(<table/>);

if (exportXmlFull.selected) {
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
var createXML:XML = new XML("<createTable>" + symbolEncode(schema.tables[0].sql) + "</createTable>");
tableXML.appendChild(createXML);
tableXML.@tableName = tableTree.selectedItem.@label;
}

for (var u:int = 0; u < exportData.length; u++) {
var rowXML:XML = new XML(<row/>);
for (var t:int = exportColumns.length - 1; t >= 0; t--) {
var val:String;
var dataValue:String = exportData[u][exportColumns[t]];

if (isNaN(Number(dataValue))) val = " + dataValue + ";
if (!isNaN(Number(dataValue))) val = dataValue;
if (val == "" || dataValue == null) val = "NULL";
val = symbolEncode(val);
var columnXML:XML = new XML("<" + exportColumns[t] + ">" + val + "</" + exportColumns[t] + ">");
rowXML.appendChild(columnXML);
}
tableXML.appendChild(rowXML);
}

var finalXML:String = <?xml version = "1.0" encoding = "UTF-8" ?> + tableXML.toXMLString();

var file:File = File.documentsDirectory.resolvePath("exported_data.xml");
file.browseForSave("Save the exported XML file");
file.addEventListener(Event.SELECT, exportSelect);

function exportSelect(evt:Event):void {
var filestream:FileStream = new FileStream();
filestream.open(file, FileMode.WRITE);
filestream.writeUTFBytes(finalXML);
filestream.close();
}
}

private function doExportJSON():void {
var jsonObject:Object = new Object();

if (exportJsonFull.selected) {
connection.loadSchema(SQLTableSchema, tableTree.selectedItem.@label, tableTree.selectedItem.@databaseName);
var schema:SQLSchemaResult = connection.getSchemaResult();
jsonObject.createTable = symbolEncode(schema.tables[0].sql);
jsonObject.tableName = String(tableTree.selectedItem.@label);
}

jsonObject.rows = [];

for (var u:int = 0; u < exportData.length; u++) {
var rowObject:Object = new Object();
for (var t:int = exportColumns.length - 1; t >= 0; t--) {
var val:String = exportData[u][exportColumns[t]];
if (val != null) val = symbolEncode(val);
rowObject[exportColumns[t]] = val;
}
jsonObject.rows.push(rowObject);
}

var finalJSON:String = new JSONEncoder(jsonObject).getString();

var file:File = File.documentsDirectory.resolvePath("exported_data.txt");
file.browseForSave("Save the exported JSON file");
file.addEventListener(Event.SELECT, exportSelect);

function exportSelect(evt:Event):void {
var filestream:FileStream = new FileStream();
filestream.open(file, FileMode.WRITE);
filestream.writeUTFBytes(finalJSON);
filestream.close();
}
}

private function closeExportWindow():void {
PopUpManager.removePopUp(exportWindow);
enableEverything = true;
}

private function importTable():void {
var file:File = new File();
file.browseForOpen("Open data file", [new FileFilter("All files", "*"), new FileFilter("CSV files", "*.csv"), new FileFilter("SQL files", "*.sql"), new FileFilter("XML files", "*.xml"), new FileFilter("Text files", "*.txt"),]);
file.addEventListener(Event.SELECT, importSelect);
function importSelect(evt:Event):void {
var filestream:FileStream = new FileStream();
filestream.open(file, FileMode.READ);
importedUTF = filestream.readUTFBytes(filestream.bytesAvailable);
openImportWindow(file);
}
}

private function openImportWindow(file:File):void {
PopUpManager.addPopUp(importWindow, this);
PopUpManager.centerPopUp(importWindow);
importData = new ArrayCollection([]);
importGrid.columns = [new AdvancedDataGridColumn("Data")];
enableEverything = false;
if (file.extension == "csv") {
importNavigator.selectedIndex = 0;
}
if (file.extension == "sql") {
importNavigator.selectedIndex = 1;
}
if (file.extension == "xml") {
importNavigator.selectedIndex = 2;
}
if (file.extension == "txt") {
importNavigator.selectedIndex = 3;
}
parseImport();
}

private function closeImportWindow():void {
PopUpManager.removePopUp(importWindow);
enableEverything = true;
}

private function parseImport():void {
importData = new ArrayCollection([]);
// csv:
if (importNavigator.selectedIndex == 0) {
canImport = true;
parseImportCSV();
}
// sql:
if (importNavigator.selectedIndex == 1) {
canImport = true;
parseImportSQL();
}
// xml:
if (importNavigator.selectedIndex == 2) {
canImport = false;
parseImportXML();
}
// json:
if (importNavigator.selectedIndex == 3) {
canImport = false;
parseImportJSON();
}
}

private function doImport():void {
if (importNavigator.selectedIndex == 0) {
doImportCSV();
}
if (importNavigator.selectedIndex == 1) {
doImportSQL();
}
if (importNavigator.selectedIndex == 2) {
doImportXML();
}
}

private function parseImportCSV():void {
var loadedData:Array = [];
var parsedData:Array = [];
var columns:Array = [];
var separator:String;
if (importSeparatorComma.selected) separator = ",";
if (importSeparatorSemicolon.selected) separator = ";";
if (importSeparatorTab.selected) separator = "	";
if (importSeparatorPipe.selected) separator = "|";
loadedData = importedUTF.split(/

|
|
/);

// add values
for (var i:int=0; i<loadedData.length; i++){
var rowArray:Array = loadedData[i].split(separator);
var rowObject:Object = new Object();
for (var u:int = 0; u < rowArray.length; u++) {
if (i > 0 || !importHeaders.selected) rowObject["column" + u] = rowArray[u].toString();
if (i==0) { 
var col:AdvancedDataGridColumn;
if(!importHeaders.selected){
col = new AdvancedDataGridColumn("column" + u);
}
if(importHeaders.selected){
col = new AdvancedDataGridColumn(rowArray[u].toString());
}
col.dataField = "column" + u;
columns.push(col);
}
}
if (i > 0 || !importHeaders.selected) importData.addItem(rowObject);
}
importGrid.columns = columns;
importGrid.dataProvider = importData;

// update the column data grid:
importColumnData = new ArrayCollection([]);
var comboChoices:Array = [{name:"---", empty:true}];
for (var c:int = 0; c < columns.length; c++) {
comboChoices.push({name: columns[c].headerText, empty:false, id:columns[c].dataField});
}
for (var d:int = 0; d < columnData.length; d++) {
importColumnData.addItem( { tableColumn: columnData[d].name, csvColumn: comboChoices[0], availableColumns: comboChoices } );
}
}

private function parseImportSQL():void {
importQuery = importedUTF;
}

private function parseImportXML():void {
var columns:Array = [];
importGridXmlColumns = [];
importData = new ArrayCollection([]);
if (checkXmlCorrect() == false) return;
var importedXML:XML = new XML(importedUTF);

for (var i:int = 0; i < importedXML[0].row.length(); i++) {
var rowObject:Object = new Object();
for (var u:int = 0; u < importedXML[0].row[i].children().length(); u++) {
try{
// add columns for the first time
if (i == 0) {
columns.push(new AdvancedDataGridColumn(importedXML[0].row[i].children()[u].name().localName));
}
rowObject[importedXML[0].row[i].children()[u].name().localName] = symbolDecode(importedXML[0].row[i].children()[u]);
}catch (e:Error) {
Alert.show("The XML is malformed and cannot be parsed. Error: 
Column not found in row.", "Error");
return;
}
}
importData.addItem(rowObject);
}
importGridXmlColumns = columns;

parseImportXMLColumns();
}

private function parseImportXMLColumns():void {
importColumnData = new ArrayCollection([]);
var columns:Array = importGridXmlColumns;
var c:int;
var d:int;
var comboChoices:Array = [ { name:"---", empty:true } ];
if(columns.length>0){
// update the column data grid if "data only":
if(importXmlData == null || importXmlData.selected){
for (c = 0; c < columns.length; c++) {
comboChoices.push({name: columns[c].headerText, empty:false});
}
for (d = 0; d < columnData.length; d++) {
importColumnData.addItem( { tableColumn: columnData[d].name, xmlColumn: comboChoices[0], availableColumns: comboChoices } );
}
}
// update the column data grid if "full table":
if(importXmlData != null && importXmlFull.selected){
for (c = 0; c < columns.length; c++) {
comboChoices.push({name: columns[c].headerText, empty:false});
}
for (d = 1; d < comboChoices.length; d++) {
importColumnData.addItem( { tableColumn: comboChoices[d].name, xmlColumn: comboChoices[d], availableColumns: comboChoices } );
}
}
canImport = true;
}
}

private function checkXmlCorrect():Boolean {
var correct:Boolean = true;
var selected:String = "data";
if (importXmlData != null && importXmlFull.selected) selected = "full";
var errorMsg:String = "";

try{
var importedXML:XML = new XML(importedUTF);
}catch (e:Error){
correct = false;
Alert.show("The XML is malformed and cannot be parsed. Error: 
" + e, "Error");
return correct;
}
// "data only" selected and no data found
if (selected == "data" && importedXML[0].row.length() == 0) {
errorMsg += "No <row> nodes found.
"
correct = false;
}
// "full table" selected and no table name found
if (selected == "full" && "@tableName" in importedXML[0] == false) {
errorMsg += "No tableName found.
"
correct = false;
}
// "full table" selected and no createTable node found
if (selected == "full" && importedXML[0].createTable.length() == 0) {
errorMsg += "No createTable node found.
"
correct = false;
}
if (!correct) Alert.show("The XML is malformed and cannot be parsed. Errors: 
" + errorMsg, "Error");
canImport = correct;
return correct;
}

private function parseImportJSON():void {
var columns:Array = [];
importGridJsonColumns = [];
importData = new ArrayCollection([]);
if (checkJsonCorrect() == false) return;
// import data
var importedJSON:Object = new JSONDecoder(importedUTF, true).getValue();
// decode data
for (var i:int = 0; i < importedJSON.rows.length; i++) {
for (var obj:Object in importedJSON.rows[i]) {
importedJSON.rows[i][obj] = symbolDecode(importedJSON.rows[i][obj]);
}
}
// apply data
importData = new ArrayCollection(importedJSON.rows);
// import columns
for (var col:Object in importedJSON.rows[0]) {
columns.push(new AdvancedDataGridColumn(String(col)));
}
importGridJsonColumns = columns;

parseImportJSONColumns();
}

private function parseImportJSONColumns():void {
importColumnData = new ArrayCollection([]);
var columns:Array = importGridJsonColumns;
var c:int;
var d:int;
var comboChoices:Array = [ { name:"---", empty:true } ];
if(columns.length>0){
// update the column data grid if "data only":
if(importJsonData == null || importJsonData.selected){
for (c = 0; c < columns.length; c++) {
comboChoices.push({name: columns[c].headerText, empty:false});
}
for (d = 0; d < columnData.length; d++) {
importColumnData.addItem( { tableColumn: columnData[d].name, jsonColumn: comboChoices[0], availableColumns: comboChoices } );
}
}
// update the column data grid if "full table":
if(importJsonData != null && importJsonFull.selected){
for (c = 0; c < columns.length; c++) {
comboChoices.push({name: columns[c].headerText, empty:false});
}
for (d = 1; d < comboChoices.length; d++) {
importColumnData.addItem( { tableColumn: comboChoices[d].name, jsonColumn: comboChoices[d], availableColumns: comboChoices } );
}
}
canImport = true;
}
}

private function checkJsonCorrect():Boolean {
var correct:Boolean = true;
var selected:String = "data";
if (importJsonData != null && importJsonFull.selected) selected = "full";
var errorMsg:String = "";

try{
var importedJSON:Object = new JSONDecoder(importedUTF, true).getValue();
}catch (e:Error){
correct = false;
Alert.show("The JSON string is malformed and cannot be parsed. Error: 
" + e, "Error");
return correct;
}

// "data only" selected and no data found
if (selected == "data" && (importedJSON.rows == null || importedJSON.rows.length == 0)) {
errorMsg += "No rows found.
"
correct = false;
}
// "full table" selected and no table name found
if (selected == "full" && importedJSON.tableName == null) {
errorMsg += "No tableName found.
"
correct = false;
}
// "full table" selected and no createTable node found
if (selected == "full" && importedJSON.createTable == null) {
errorMsg += "No createTable found.
"
correct = false;
}
if (!correct) Alert.show("The JSON string is malformed and cannot be parsed. Errors: 
" + errorMsg, "Error");
canImport = correct;
return correct;
}

private function doImportCSV():void {
var tableColumns:Array = [];
var csvColumns:Array = [];
var conflict:String = (importConflict.selectedIndex > 0)?("OR " + importConflict.selectedLabel + " "):("");
for (var t:int = 0; t < importColumnData.length; t++) {
if (importColumnData[t].csvColumn.empty == false) {
tableColumns.push(importColumnData[t].tableColumn);
csvColumns.push(importColumnData[t].csvColumn.id);
}
}
if (tableColumns.length > 0) {
connection.begin();
for (var i:int = 0; i < importData.length; i++) {
var st:SQLStatement = new SQLStatement();
st.sqlConnection = connection;
st.text = "INSERT " + conflict + "INTO " + tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label + " (" + String(tableColumns) + " ) VALUES (" + getCsvValues(i) + ");";
st.execute( -1, new Responder(null, insertError));
lastStatement(st.text);
}
connection.commit(new Responder(insertSuccess, insertError));
}

function getCsvValues(ind:int):String {
var arr:Array = [];
for (var c:int = 0; c < csvColumns.length; c++) {
var val:String;
var dataValue:String = importData[ind][csvColumns[c]];
if (isNaN(Number(dataValue))) val = " + dataValue + ";
if (!isNaN(Number(dataValue))) val = dataValue;
if (val == "") val = "NULL";
arr.push(val);
}
return String(arr);
}

function insertSuccess():void {
closeImportWindow();
tableSelect();
}

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

private function doImportSQL():void {
if(tabNavigator.selectedIndex!=2){
tabNavigator.addEventListener(IndexChangedEvent.CHANGE, tabChangeTrigger);
tabNavigator.selectedIndex = 2;
}else {
queryText.text = importQuery;
queryExecute();
}
function tabChangeTrigger(evt:IndexChangedEvent):void {
queryText.text = importQuery;
queryExecute();
tabNavigator.removeEventListener(IndexChangedEvent.CHANGE, tabChangeTrigger);
}
closeImportWindow();
}

private function doImportXML():void {
var importedXML:XML = new XML(importedUTF);
var tableColumns:Array = [];
var xmlColumns:Array = [];
var tableName:String = tableTree.selectedItem.@databaseName + "." + tableTree.selectedItem.@label;
var conflict:String = (importXmlConflict.selectedIndex > 0)?("OR " + importXmlConflict.selectedLabel + " "):("");
var i:int;
var t:int;

// create table if full
if (importXmlFull.selected) {
var stat:SQLStatement = new SQLStatement();
stat.sqlConnection = connection;
stat.text = symbolDecode(importedXML[0].createTable[0]);
stat.execute();
lastStatement(stat.text);
tableName = importedXML[0].@tableName;

refreshEverything();
}

// import selected columns
for (t = 0; t < importColumnData.length; t++) {
if (importColumnData[t].xmlColumn.empty == false) {
tableColumns.push(importColumnData[t].tableColumn);
xmlColumns.push(importColumnData[t].xmlColumn.name);
}
}

if (tableColumns.length > 0) {
connection.begin();
for (i = 0; i < importData.length; i++) {
var st:SQLStatement = new SQLStatement();
st.sqlConnection = connection;
st.text = "INSERT " + conflict + "INTO " + tableName + " (" + String(tableColumns) + " ) VALUES (" + getXmlValues(i) + ");";
st.execute( -1, new Responder(null, insertError));
lastStatement(st.text);
}
connection.commit(new Responder(insertSuccess, insertError));
}

function getXmlValues(ind:int):String {
var arr:Array = [];
for (var c:int = 0; c < xmlColumns.length; c++) {
var val:String = importData[ind][xmlColumns[c]];
arr.push(val);
}
return String(arr);
}

function insertSuccess():void {
closeImportWindow();
if(importXmlData.selected) tableSelect();
}

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

private function openQuery():void {
var file:File = new File();
file.browseForOpen("Open SQL query file", [new FileFilter("SQL files", "*.sql"), new FileFilter("All files", "*")]);
file.addEventListener(Event.SELECT, fileSelect);
function fileSelect():void {
var filestream:FileStream = new FileStream();
filestream.open(file, FileMode.READ);
queryText.text = filestream.readMultiByte(filestream.bytesAvailable, "utf-8");
filestream.close();
}
}

private function symbolEncode(str:String):String {
str = str.replace(/&/g, &amp;);
str = str.replace(/"/g, &quot;);
str = str.replace(//g, &apos;);
str = str.replace(/</g, &lt;);
str = str.replace(/>/g, &gt;);
return str;
}

private function symbolDecode(str:String):String{
str = str.replace(/&quot;/g, ");
str = str.replace(/&apos;/g, "");
str = str.replace(/&lt;/g, <);
str = str.replace(/&gt;/g, >);
str = str.replace(/&amp;/g, &);
return str;
}
]]>
</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/database_add.png)" toolTip="New database" enabled="true" buttonMode="true" click="newDatabase();" />
<custom:IconButton icon="@Embed(../lib/folder_database.png)" toolTip="Open database" enabled="true" buttonMode="true" click="openDatabase();" />
<custom:IconButton icon="@Embed(../lib/database_delete.png)" toolTip="Delete database" enabled="{tableTree.selectedItems.length>0}" buttonMode="true" click="deleteDatabase();" />
<custom:IconButton icon="@Embed(../lib/database_save.png)" toolTip="Save database" enabled="{tableTree.selectedItems.length>0}" buttonMode="true" click="saveCopy();"/>
<custom:IconButton icon="@Embed(../lib/database_gear.png)" toolTip="Database settings" enabled="{hasDatabase}" buttonMode="true" click="databaseSettings();"/>
<custom:IconButton icon="@Embed(../lib/database_refresh.png)" toolTip="Unload everything" enabled="{hasDatabase}" buttonMode="true" click="doUnload();"/>
</s:HGroup>
<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/table_save.png)" toolTip="Export table" enabled="{isTableSelected}" buttonMode="true" click="exportTable();"/>
<custom:IconButton icon="@Embed(../lib/table_import.png)" toolTip="Import table" enabled="{isTableSelected}" buttonMode="true" click="importTable();"/>
</s:HGroup>
<s:HGroup width="200" paddingLeft="6">
<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();"/>
<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/eye_save.png)" toolTip="Export view" enabled="{isViewSelected}" buttonMode="true" click="exportTable();"/>
<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:HGroup>
<s:Button label="Execute query" click="queryExecute();" />
<s:Button label="Execute statement and export results" click="exportQuery();" />
<s:Button label="Open query file" click="openQuery();" />
</s:HGroup>
<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>