airの練習がてらSQLiteConsoleを作ってみている
はてなでファイルのUPとかって出来ないのか！？
しょうがないベタに貼っちゃえ。
#ソースはこの記事下部へ
機能・仕様
テーブル一覧とビュー一覧がみれる
ついでにカラム名一覧もみれる #ビューのカラム一覧はバグあり
SQLの入力履歴が使える。#履歴からSQL呼び出し可能
SQLを実行した結果のダンプが見れる。#テーブル表示はまだ・・・orz
DB名は"main"固定。 #DB名も今後で対応
スクショ
とりあえず
mxmlとasのつなぎ目が汚い！
どうやって書くのがスタンダードなの！？
今度ネットを探してみよう。
mxmlの書き方は
はてな技術発表会日記：8月17日の技術勉強会 - Flexレイアウト手書き勉強会
http://hatena.g.hatena.ne.jp/hatenatech/20070821/1187670828
を参考にしたよ。
あとは、クラス化とかまだ全然やってないけど、
これ以上分割出来る様な気もしない・・・。
旨い方法あるかしら。
ソース
main.mxml
<?xml version="1.0"?> <mx:Application xmlns:mx="http://www.adobe.com/2006/mxml" width="100%" height="100%" paddingBottom="0" paddingLeft="0" paddingRight="0" paddingTop="0" backgroundImage="" backgroundColor="#000" creationComplete="init(event);" > <mx:Script source="header.as" /> <mx:HDividedBox width="100%" height="100%"> <mx:VDividedBox width="80%" height="100%"> <mx:HDividedBox width="100%" height="90%"> <mx:TextArea width="80%" height="100%" id="query_result"/> <mx:List width="20%" height="100%" id="query_hist_list"/> </mx:HDividedBox> <mx:HBox width="100%" height="10%" > <mx:TextArea height="100%" width="100%" id="query_txt" /> <mx:Button label="query_exe" height="100%" id="query_btn" enabled="false" /> </mx:HBox> </mx:VDividedBox> <mx:VBox width="20%" height="100%"> <mx:Spacer width="100%" height="1"/> <mx:Button label="load schem" width="100%" height="20" id="btn_schem" /> <mx:Tree width="100%" height="100%" id="tree_shem" labelFunction="treeLabel"/> </mx:VBox> </mx:HDividedBox> </mx:Application>
header.as
import flash.filesystem.File
import umezo.util.SQLiteConsole;
private function init(event:FlexEvent):void {
var file:File = File.documentsDirectory.resolvePath("user.db");
var console:SQLiteConsole = new SQLiteConsole(query_result, query_txt, query_btn, query_hist_list, btn_schem , tree_shem);
console.openSQLite(file);
return;
}
private function treeLabel(item:Object):String {
var node:XML = new XML(item);
return node.localName();
}
umezo/util/SQLiteConsole.as
package umezo.util {
import flash.filesystem.File;
import flash.xml.XMLDocument;
import flash.xml.XMLNode;
import mx.collections.ArrayCollection;
import mx.collections.errors.CollectionViewError;
import mx.collections.XMLListCollection;
import mx.controls.Button;
import mx.controls.List;
import mx.controls.TextArea;
import mx.controls.TextInput;
import mx.controls.Tree;
import mx.events.FlexEvent;
import mx.events.ListEvent;
import flash.events.SQLEvent;
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.data.SQLSchemaResult;
import flash.data.SQLTableSchema;
import mx.utils.ObjectUtil;
import flash.errors.IllegalOperationError;
import flash.errors.SQLError;
import flash.events.SQLErrorEvent;
public class SQLiteConsole {
private var query_result:TextArea;
private var query_txt:TextArea;
private var query_btn:Button;
private var query_hist_list:List;
private var conn:SQLConnection;
private var stmt:SQLStatement;
private var histData:ArrayCollection;
private var schem_btn:Button;
private var schem_tree:Tree;
public function SQLiteConsole(result:TextArea , txt:TextArea , btn:Button , list:List , schmBtn:Button , schmTree:Tree) {
this.query_result = result;
this.query_txt = txt;
this.query_btn = btn;
this.query_hist_list = list;
this.schem_btn = schmBtn;
this.schem_tree = schmTree;
histData = new ArrayCollection();
histData.enableAutoUpdate();
this.initUI();
}
private function initSQL():void {
conn = new SQLConnection();
conn.addEventListener(SQLEvent.OPEN, this.onOpen );
conn.addEventListener(SQLEvent.SCHEMA, this.onSchema );
conn.addEventListener(SQLErrorEvent.ERROR, this.onError );
stmt = new SQLStatement();
stmt.sqlConnection = conn;
stmt.addEventListener(SQLEvent.RESULT, this.onResult);
stmt.addEventListener(SQLErrorEvent.ERROR, this.onError );
}
private function initUI():void {
this.setAllButtonEnable(false);
this.query_btn.addEventListener(FlexEvent.BUTTON_DOWN , this.onButtonDown);
this.schem_btn.addEventListener(FlexEvent.BUTTON_DOWN , this.onSchemBtn);
this.query_hist_list.addEventListener(ListEvent.ITEM_CLICK , this.onHistorySelected);
this.query_hist_list.dataProvider = histData;
}
public function openSQLite(file:File):void {
this.initSQL();
conn.open(file);
}
private function query(text:String):void {
stmt.text = text;
try{
stmt.execute();
}catch (e : IllegalOperationError) {
trace("illeagal");
}catch (e : SQLErrorEvent) {
trace("error");
}
}
private function setPromptText(text:String):void {
query_txt.text = text;
}
private function setTreeData(data:XMLListCollection):void {
schem_tree.dataProvider = data;
}
private function appendConsoleText(text:String):void {
query_result.text += text + "\n";
}
private function addHistory(text:String) :void{
histData.addItem( { label:text, data:text } );
}
private function setAllButtonEnable(enable:Boolean):void {
query_btn.enabled = enable;
schem_btn.enabled = enable;
}
private function toXMLListCollection(schm:SQLSchemaResult):XMLListCollection {
var list : XMLDocument = new XMLDocument();
var main : XMLNode = list.createElement("main");
var tables : XMLNode = list.createElement("tables");
var views : XMLNode = list.createElement("views");
this.appendTblSchmTo(list , tables , schm.tables);
this.appendTblSchmTo(list , views , schm.views);
list.appendChild(tables);
list.appendChild(views);
return new XMLListCollection(new XMLList(list));
}
private function appendTblSchmTo(doc : XMLDocument , target:XMLNode , tblSchmArry : Array):void {
for (var i:String in tblSchmArry) {
var tblSchm : SQLTableSchema = tblSchmArry[i] as SQLTableSchema;
var tblNode : XMLNode = doc.createElement(tblSchm.name);
tblNode.attributes = tblSchm.sql;
trace(ObjectUtil.toString(tblNode.attributes));
for (var j : String in tblSchm.columns) {
tblNode.appendChild(doc.createElement(tblSchm.columns[j].name));
}
target.appendChild(tblNode);
}
}
//------------------------------------------------------------------------------------------
//-- event handler -------------------------------------------------------------------------
//------------------------------------------------------------------------------------------
private function onButtonDown (event:FlexEvent):void {
this.query( query_txt.text );
}
private function onSchemBtn (event:FlexEvent):void {
conn.loadSchema();
}
private function onHistorySelected(event:ListEvent):void {
this.setPromptText(event.target.selectedItem.data);
}
private function onOpen (event:SQLEvent):void {
this.setAllButtonEnable(true);
}
private function onResult(event:SQLEvent):void {
this.addHistory( event.target.text );
this.appendConsoleText(ObjectUtil.toString(event.target.getResult()));
}
private function onSchema(event:SQLEvent):void {
this.setTreeData(toXMLListCollection(event.target.getSchemaResult()));
}
private function onError(event:SQLErrorEvent):void {
trace(ObjectUtil.toString(event));
}
}
}
