root/as3/SQLControls/samples/SQLiteTableEditor/SQLiteTableEditor.as

import flash.events.*;
import mx.events.*;
import mx.collections.ArrayCollection;
import mx.controls.Alert;
import flash.data.SQLConnection;
import flash.filesystem.File;
import mx.binding.utils.ChangeWatcher;
import org.libspark.utils.SqlUtil;
import org.libspark.snippets.controls.SQLTableGrid;

[Bindable]
public var tableDefs:ArrayCollection = new ArrayCollection([
    {name: "record_id", type: "INTEGER", primaryKey: true, autoIncrement: false, allowNull: false}
]);

[Bindable]
public var indexDefs:ArrayCollection = new ArrayCollection();

[Bindable]
public var columnList:ArrayCollection = new ArrayCollection([{label: "record_id"}]);

[Bindable]
public var indexList:ArrayCollection = new ArrayCollection();

private static const typeData:Array = new Array("INTEGER", "FLOAT", "TEXT", "BLOB");

private var connection:SQLConnection;

private function bAddColumn_click(event:MouseEvent):void {
    tableDefs.addItem(
        {name: "", type: "TEXT", primaryKey: false, autoIncrement: false, allowNull: true}
    );
}

private function bDelColumn_click(event:MouseEvent):void {
    if (dg.selectedIndex >= 0)
        tableDefs.removeItemAt(dg.selectedIndex);
}

private function bDelIndex_click(event:MouseEvent):void {
    if (dgIndexList.selectedIndex >= 0)
        indexDefs.removeItemAt(dgIndexList.selectedIndex);
}

private function bCancelColumnIndex_click(event:MouseEvent):void {
    indexList.removeAll();
}

private function bAddIndex_click(event:MouseEvent):void {
    if (tIndex.text == "") {
        Alert.show("Please input index name", "CONFIRM");
        return;
    }

    if (SqlUtil.existsIndex(connection, null, tIndex.text)) {
        Alert.show("The index " + tIndex.text + " already exists in database. Please change the index name." , "CONFIRM");
    } else {
        var indexColumns:String = "";
        for each(var val:* in indexList)
            indexColumns += val + ", ";
        indexColumns = indexColumns.substr(0, indexColumns.length - 2);
        indexDefs.addItem({name: tIndex.text, columns: indexColumns});
        tIndex.text = "";
        indexList.removeAll();
    }
}

private function bCreateTable_click(event:MouseEvent):void {
    if (tTable.text == "") {
        Alert.show("Please input table name", "CONFIRM");
        return;
    }
    if(SqlUtil.existsTable(connection, tTable.text)) {
        Alert.show("Table " + tTable.text + " already exists in database. Please change the table name.", "CONFIRM");
    } else {
        var status:Boolean = SqlUtil.createTable(connection, tTable.text, tableDefs);
        if (status) {
            status = SqlUtil.createIndex(connection, tTable.text, indexDefs);
            Alert.show("Table " + tTable.text + " created", "CONFIRM");
            tTable.text = "";
            indexList.removeAll();
            indexDefs.removeAll();
        }
    }
}

private function bCancel_click(event:MouseEvent):void {
    this.close();
}

private function onTableColumnChange(event:Event):void {
    columnList.removeAll();
    indexList.removeAll();
    for each (var val:* in tableDefs)
        columnList.addItem(val.name);
}

private var dg:SQLTableGrid;

private function init():void {
    addEventListener(Event.CLOSING, onClosing);
    tableDefs.addEventListener(CollectionEvent.COLLECTION_CHANGE, onTableColumnChange);
    dg = new SQLTableGrid(true);
    dg.x = 20; dg.y = 75; dg.width = 360; dg.height = 180;
    dg.dataProvider = tableDefs;
    dg.editable = true;
    addChild(dg);
    connection = new SQLConnection();
    openDB();
}

private function openDB():void {
    var dbFile:File = new File();
    var dbFilter:FileFilter = new FileFilter("DB File", "*.db");
    try {
        dbFile.browseForOpen("Open", [dbFilter]);
        dbFile.addEventListener(Event.SELECT, dbFileSelected);
        dbFile.addEventListener(Event.CANCEL, dbFileCanceled);
    } catch (error:Error) {
        Alert.show(error.message, "ERROR!");
    }
}

private function dbFileSelected(event:Event):void {
    try {
        connection.open(File(event.target), SQLMode.READ);
        dg.sqlConnection = connection;
    } catch(error:Error) {
        Alert.show(error.message, "ERROR!");
    }
}

private function dbFileCanceled(event:Event):void {
    try {
        var path:String = "file://" + File.documentsDirectory.nativePath + "/user.db";
        connection.open(new File(path), SQLMode.CREATE);
        dg.sqlConnection = connection;
    } catch(error:Error) {
        Alert.show(error.message, "ERROR!");
    }
}

private function onClosing(event:Event):void {
    trace("Application closing...");
    if (connection != null)
        if(connection.connected)
            connection.close();
}