root/as3/SQLControls/samples/SQLiteDataManager/SQLiteDataManager.as

import flash.events.*;
import mx.events.*;
import mx.controls.Alert;
import mx.collections.ArrayCollection;
import org.libspark.utils.SqlUtil;
import org.libspark.snippets.controls.SQLDataGrid;

[Bindable]
private var cbTableList_data:Array;

private var columns:ArrayCollection;

private function buildSQL(table:String):String {
    columns = SqlUtil.getColumnDef(connection, table);
    var sql:String = "SELECT ";
    for each(var val:* in columns)
        sql += val.name + ", ";
    sql = sql.substring(0, sql.length - 2);
    sql += " FROM " + table;
    return sql;
}

private function cbTableList_close(event:DropdownEvent):void {
    dg.executeQuery(buildSQL(String(event.target.selectedItem)));
}

private function bAddRow_click(event:MouseEvent):void {
    var rowData:ArrayCollection = new ArrayCollection();
    for each(var val:* in columns)
        rowData.addItem({column: val.name, type: val.type, value: null});
    var status:Boolean = SqlUtil.insertRow(connection, String(cbTableList.selectedItem), rowData);
    dg.executeQuery(buildSQL(String(cbTableList.selectedItem)));
}

private function bDelRow_click(event:MouseEvent):void {
    if (dg.selectedIndex < 0)
        return;

    var record_id:int = dg.selectedItem.record_id;
    var status:Boolean = SqlUtil.deleteRow(connection, String(cbTableList.selectedItem), "record_id", record_id);
    dg.executeQuery(buildSQL(String(cbTableList.selectedItem)));
}

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

private function onItemEditBegin(event:DataGridEvent):void {
    if (event.columnIndex == 0)
        event.preventDefault();
}

private function onItemEditEnd(event:DataGridEvent):void {
    if (event.reason == DataGridEventReason.CANCELLED)
        return;

    var item:Object = event.itemRenderer.data;
    var column:String = event.dataField;
    var columnType:String;
    var itemEditor:Object = dg.itemEditorInstance;

    if (itemEditor.text == item[column])
        return;

    for each(var val:* in columns) {
        if (val.name == column) {
            columnType = val.type;
            break;
        }
    }

    var status:Boolean = SqlUtil.updateRow(connection, String(cbTableList.selectedItem), "record_id", item.record_id,
                                           column, itemEditor.text, columnType);
}

private var connection:SQLConnection = new SQLConnection();
private var dg:SQLDataGrid = new SQLDataGrid();

private function init():void {
    addEventListener(Event.CLOSING, onClosing);
    dg.x = 20; dg.y = 64; dg.width = 680; dg.height = 396;
    dg.editable = true;
    dg.addEventListener(DataGridEvent.ITEM_EDIT_BEGIN, onItemEditBegin);
    dg.addEventListener(DataGridEvent.ITEM_EDIT_END, onItemEditEnd);
    addChild(dg);
    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;
        getTableList();
    } 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;
        getTableList();
    } catch(error:Error) {
        Alert.show(error.message, "ERROR!");
    }
}

private function getTableList():void {
    cbTableList_data = SqlUtil.getTableList(connection);
    dg.executeQuery(buildSQL(cbTableList_data[0]));
}