つれずれなるままの開発日記(最近はFlexばかり..)

Archive for the ‘DB’ Category

あまりにも久しぶり（２か月）で自分でもどこまで記述したか忘れてしまったので、再度、過去を整理。
ちょっと、ZIPライブラリにどっぷりつかりすぎてしまいました。

過去、どのような記事を書いたかといえば、

flex:DataGrid sqlを使ってdataProviderを自作する ( Part 1 )
flex:DataGrid sqlを使ってdataProviderを自作する ( Part2: 大きなデータを扱う時にページングって本当に必要ですか？）

ただ、内容的には、Part1の続きです。（Part2はどちらかといえば、番外編コラム的内容です。）

気を取り直して、DataGridのdataProviderを作成するために必要なのは、

TableCollectionView

ICollectionViewを継承したクラス。Array(ArrayListのほうが正しい)に対するArrayCollectionの関係のクラスです

TableList

こちらは、データそのものを表すクラス。今度はArrayCollecitonに対するArray(ArrayList)に相当するクラスです。

の２つのクラスです。
そうそう、思い出してきました。

ArrayとArrayCollectionの関係も参考になるので、この関係がわからない方は、こちらも読んでください。

TableListのクラスはIListインターフェースを実装する必要があります。
このインターフェースのすべてを実装する必要はありません。

そもそも大量なデータの参照の場合には、通常、登録処理はドラッグ＆ドロップなどでやるということはあまりないはずです。
したがって、ここのインターフェースを使って登録や・更新されるようなことは今回考えません。

では、実装するメソッドは何があるの・・・・って

• get length():int – プロパティとなっていますが、getterなので・・
• getItemAt():Object
• getItemIndex():Object

たったこれだけです。
もちろん、IListのインターフェースを満たす必要がありますので、それ以外のメソッドはnullを返すようなメソッドを実装しておいてください。
（本当は、ランタイムエラーを返すようにしておいたほうがいいかもしれません。はっきり使えないことがわかりますので・・・）

さて、length()では

SELECT count(*) as count FROM [テーブル名];

のようなSQLを実行すればいいわけですので、TableListクラスでは逆にSQLが実行できるような環境設定が必要というわけなので、

package com.coltware.commons.db
{
  public class TableList extends EventDispatcher implements IList{

    private var _conn:SQLConnection = null;
    private var _tableName:String;

    public function set sqlConnection(conn:SQLConnection):void{
      this._conn = conn;
    }
    public function set tableName(name:String):void{
      this._tableName = name;
    }
  }
}

のように、SQLの接続とテーブル名だけでも外部から設定しておけるようにしておけば当面は間に合うと思います。
上のコードにIListのインターフェースを満たす実装をしていきます。

以下が、lengthのgetterメソッドです。

public function get length():int
  var stmt:SQLStatement = new SQLStatement();
  stmt.sqlConnection = this._conn;
  stmt.text = "SELECT count(*) as count FROM " + this._tableName;
  stmt.execute();
  var result:SQLResult = stmt.getResult();
  if( result != null && result.data != null ){
    var retObj:Object = new Object();
    var size:int = result.data.length;
    if(size > 0 ){
      return result.data[0]["count"];
    }
  }
  return 0;
}

ここで、0以上が帰れば、次に呼ばれるのは getItemAtです。

public function getItemAt(index:int, prefetch:int=0):Object
{
	var sql:String = "SELECT * FROM " + this._tableName + " LIMIT 1 OFFSET " + index;
	//  後は、length()のメソッドを参考に実装をしてください。
}

のようになります。
ここで、SQLとして、「あれ？」と思う部分は・・・・
そう、ソート部分がないのです。とりあえず、ちょっと話がややこしくなるので、今のところはここは無視しください。
（後で、ソートについても実装していきます。）

また、１行、１行、SELECTするのってどうなの？と思う方もいるかもしれませんが、JOINなどをしないのであれば毎回このように取ってきても問題ないと思います。
だって、表示されている部分しか取ってきませんし・・・
（気になる方は、キャッシュ機能（ページング機能）を実装してください。）

後は、getItemIndexですが、ただし、ちょっと疑問？な点があります。
オブジェクトからそのオブジェクトが何番目のオブジェクトなのか？ってどうやってわかるの？
だいたい、何番目ってどこを基準に？
って話です。

しかし、そもそも、getItemAtで、「n番目のデータをください！」ってお願いされているのですから、
ここで、このオブジェクトはn番目ってしるしをつけておくとよいでしょう。
そして、基準もここである前提（ソートが関係）のもと聞いているのですから、問題ないと思います。

私はそのために管理オブジェクトを作るのも何なので、

public function getItemAt(index:int, prefetch:int=0):Object
{

  : ( 省略 )
  var result:SQLResult = stmt.getResult();

  if( result != null && result.data != null ){
     var obj:Object = result.data[0];
     obj['_internal_index'] = index;
     return obj;
  }
  return null;
}

のように、オブジェクト自身に新たにプロパティを設けて、ここに保存してしまいます。
まず、_internal_indexなんているカラムは作らないと思います。

これで、

public function getItemIndex(item:Object):int
{
  return item['_internal_index'];
}

のようになるわけです。

さて、これで使わないが宣言だけ必要なメソッドを実装すれば完成です。

次回は、TableCollectionViewを実装、そして次に、ソート部分の対応をしていきます。

私はテーブルを作成するときには、いつもXMLで自分流に定義して、それを使ってテーブルをcreateする。
こうするといいことがいくつかある。

1. ドキュメントをxsltなどで自動的につくれる。

   いろいろなライブラリがすでにあるテーブルからDBにアクセス用に自動的にオブジェクトを作るが、私はXMLから作成している。
   それは、結局ドキュメント化されなければいけないのであれば、やっぱりドキュメントが自動のほうが嬉しい。

2. テーブルの変化（バージョン管理）もXML(ドキュメント)に含められる。

   だいたい、規定していない属性、項目は処理しない。したがってそれを使ってドキュメントに使える。
   また、テーブル追加や削除( alter table )もそこでやったこともあったなー。これはいらなかったかな。

3. データベースの違いを吸収できる。

   まあ、AIRの場合にはSQLiteしかないから関係ないが・・・
   でも、サーバでMySQLを使って、そのデータをAIRのSQLiteでキャッシュするというときなんか、同じようなフォーマットで共通化できるから便利じゃないかな。
   たとえば、booleanとか。MySQLのむかーしのバージョンではなかったんだよね。(4.1から導入されたらしい）
   だからこれで定義があると、tinyint(1)と書き換えていた。

でも、いいことばかりじゃない。
複雑なテーブルは作りにくい。でも、逆にいえばこれはいいことともいえる。
そして、これが一番の難題。自分で”create table foo(….. ” あれ？つぎなんだっけ？と、文法を忘れる。
したがって、自分で手で作成するときにはリファレンスがないとわからない。
あと、３でも書いたが、違いを吸収してしまうので、新しくできた機能、変化を気にしなくなる。

さて、では実際にはどんな感じでXMLを書くのか？

< ?xml version="1.0" encoding="UTF-8" ?>
<table name="status" dbName="its">
    <field name="sysid" 	type="integer" primary="true" auto_increment="true" >
        <title>システム用ID</title>
    </field>
    <field name="server_id" type="integer" required="true" >
        <title>サーバID</title>
        <description>どのサーバのデータか？</description>
    </field>
    <field name="uid" 		type="integer" required="true" >
        <title>ユーザID</title>
        <description>サーバ上のユニークIDが入る。ただし、ローカルの場合にはsequenceを使う</description>
    </field>
    <field name="createdAt" type="timestamp" 	required="true" >
        <title>作成日時</title>
    </field>
    <field name="updatedAt" type="timestamp" 	required="true" />
    <field name="createdBy" type="integer" 		required="true" />
    <field name="updatedBy" type="integer" 		required="true" />
    <field name="sync"		type="integer" 		required="true" />
    <field name="syncAt"	type="timestamp" 	/>

    <!--  人が管理するためのフィールド -->
    <field name="title" 	type="varchar" size="255" required="true" />
    <field name="type"      type="integer"      default="-1" />
    <field name="memo"		type="text"	/>
    <field name="enableNew" type="boolean"      required="true" />
    <field name="isRoot"    type="boolean"      required="true" />
    <field name="weight"	type="integer"		default="0">
        <title>表示する際の重み</title>
    </field>
    <sequence name="uid" />
</table>

ってな感じです。
titleとか、descriptionとかはドキュメント用。
あと、sequence はcreate sequenceなんだけど、SQLiteにはないから代替の処理。
MySQLにもないけど、PostgreSQLにはあるからこの場合にはふつうにcreate sequenceにする。

/**
 *  Copyright (c)  2009 coltware@gmail.com
 *  http://www.coltware.com
 *
 * XMLからテーブルを作成する
 *
 */
package com.coltware.cise.db
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;

	import mx.logging.ILogger;
	import mx.logging.Log;

	public class TableFactory {

		private var _conn:SQLConnection;
		private var _xml:XML;

		private static const _log:ILogger = Log.getLogger("com.coltware.cise.db.TableFactory");

		public var lastSql:String = "";

		public function TableFactory() {

		}

		/**
		*
		*/
		public function set connection(conn:SQLConnection):void{
			this._conn = conn;
		}
		/**
		*  定義XMLの設定
		*/
		public function set xml(defxml:XML):void{
			this._xml = defxml;
		}

		public function dropIfExist():String{
			var tableName:String = this._xml.@name;
			lastSql = "DROP TABLE IF EXISTS " + tableName;
			_log.debug("SQL : " + lastSql);
			return lastSql;
		}

		public function drop(resultFunc:Function = null,errorFunc:Function = null):SQLStatement{
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = this._conn;
			stmt.text = dropIfExist();
			if(resultFunc != null){
				stmt.addEventListener(SQLEvent.RESULT,resultFunc);
			}
			if(errorFunc != null){
				stmt.addEventListener(SQLErrorEvent.ERROR,errorFunc);
			}
			stmt.execute();
			return stmt;
		}

		public function handleCreate(e:SQLEvent):void{
			var stmt:SQLStatement = e.target as SQLStatement;
			var result:SQLResult = stmt.getResult();
			_log.debug("handleCreate " + result.lastInsertRowID);
		}

		public function create(resultFunc:Function = null,errorFunc:Function = null,ifNotExists:Boolean = false):SQLStatement{
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = this._conn;
			stmt.text = createSQL(ifNotExists);
			if(resultFunc != null){
				stmt.addEventListener(SQLEvent.RESULT,resultFunc);
			}
			if(errorFunc != null){
				stmt.addEventListener(SQLErrorEvent.ERROR,errorFunc);
			}
			stmt.execute();
			return stmt;
		}

		public function createSQL(ifNot:Boolean = false):String{
			var sql:String;

			var tableName:String = this._xml.@name;

			var ifNotStr:String = "";
			if(ifNot == true){
				ifNotStr = " IF NOT EXISTS ";
			}

			sql = "CREATE TABLE " + ifNotStr + tableName + "( \n";

			var pkey:Boolean  = false;
			var cnt:int = 0;

			var pkeyList:Array = new Array();

			for each(var child:XML in this._xml.field){
				var name:String = child.@name;
				var type:String = child.@type;
				type = type.toUpperCase();

				if(cnt > 0 ){
					sql = sql + ",\n";
				}
				cnt++;

				var primary:String = child.@primary;
				var ai:String = child.@auto_increment;

				var primaryKey:String = "";
				if((primary == "true" || primary == "TRUE" )){
					if(ai == "true" || ai == "TRUE"){
						primaryKey = " PRIMARY KEY AUTOINCREMENT ";
						pkey = true;
					}
					else{
						pkeyList.push(name);
					}
				}
				var nn:String = child.@required;
				var notNull:String = "";
				if(nn == "true" || nn == "TRUE"){
					notNull = " NOT NULL ";
				}

				switch(type){
					case "INTEGER":
					case "INT":
						sql = sql + name + " INTEGER " +primaryKey + notNull;
						break;
					case "VARCHAR":
					case "CAHR":
						sql = sql + name + " VARCHAR(" + child.@size + ")" + notNull;
						break;
					case "TEXT":
						sql = sql + name + " TEXT " + notNull;
						break;
					case "TIMESTAMP":
						sql = sql + name + " DATE " + notNull;
						break;
					case "BOOL":
					case "BOOLEAN":
						sql = sql + name + " BOOLEAN " + notNull;
						break;
				}

				var def:String = child.@default;
				if(def != null && def.length > 0 ){
					sql = sql + " DEFALUT '" + def + "'";
				}

			}

			if(pkey == false && pkeyList.length > 0){
				sql = sql + ",\n PRIMARY KEY (" + pkeyList.join(",") + ")";
			}

			sql = sql + ");";

			// シーケンスがある場合
			for each(var c:XML in this._xml.sequence){
				// TODO シーケンスの作成で、エラー処理がない
				this.createSeq(tableName,c.@name);
			}

			_log.debug("sql : " + sql);
			lastSql = sql;
			return sql;
		}

		private function createSeq(tableName:String,fieldName:String):void{
			var n:String = "seq_" + tableName + "_" + fieldName;
			var sql:String = "CREATE TABLE IF NOT EXISTS " + n + "( seq INTEGER PRIMARY KEY AUTOINCREMENT );";
			var stmt:SQLStatement = new SQLStatement();
			stmt.text = sql;
			stmt.sqlConnection = this._conn;
			stmt.execute();
		}
	}
}

ってな感じです。

DataGridでテーブルの結果をデータとして表示したいときに、

stmt.text = "SELECT * FROM ......";
stmt.execute();
var result:SQLResult = stmt.getResult();
datagrid.dataProvider = new ArrayCollection(result.data);

のようにして、データを取得してArrayCollectionとして設定すればいい。

しかし、これではデータが大きかろうが、小さかろうがデータをすべて事前に作成する必要がある。
もし、これが２０行しか表示されずに、そのままそのページを閉じられたとしてもだ。

これは何とも非効率だ。
そこで、必要なデータを、必要な時に作成するようにしたいと思っていろいろと調べていたが、
一番よいやり方は、DataProviderに設定できるICollectionViewのインターフェースをもったクラスを自作するのが一番だろうということになり、このクラスを自作することにした。

まだ、完全にはできていないものの、このクラスを自作しながらレポートしていく。

必要なサンプルクラスをさがす

dataProviderに設定するにはICollecitonViewのインターフェースをもったクラスだから、
この必要メソッドを作成していけば、いいわけだが、やみくもに作ってもしょうがない。

そこで、既存の実装でよいサンプルを探す。
やはり、このサンプルとして一番いいのは、ArrayCollecitonだろう。
データ自体がArrayなので、内容も把握しやすい。

そこでArrayCollecitonというクラスを見てみると、ほんの150行程度で実装が終わっている。
ようはやっていることは

(今回関係ある部分のみしか記述していません）

public class ArrayCollection extends ListCollectionView{

  public function set source(s:Array):void{
    list = new ArrayList(s);
  }
}

というように、listプロパティに、IListの実装としてArrayListを設定しているListCollectionViewということがいえる。

したがって、今回自作するクラスもこのような形として実装すればなんとかなりそうだ。

実装するクラス

前述の通り、今回実装するクラスは

• TableCollecitonView
• TableList

とする。

ちょうど、TableCollecitonViewがArrayCollectionを参考にし、TableListがArrayListを参考に実装すればよいというわけだ。

では、これらの実装を次回からしていくことにする。

参考までに、ArrayListというクラス、publicなクラスだが、@privateとなっていてドキュメントからは表示されないようになっている。

private function exec():void{
  var stmt:SQLStatement = new SQLStatement();
  stmt.sqlConnection = _conn;
  stmt.addEventHandler(SQLEvent.RESULT,handleData);
  stmt.itemClass = com.coltware.sample.Foo;
  stmt.sql = _sql;
  stmt.execute();
}

private function handleData(event:SQLEvent):SQLStatment{
  var stmt:SQLStatement = event.target as SQLStatement;
  var result:SQLResult = stmt.getResult();
  if(result != null && result.data != null){
  if(result.data.length > 0 ){
  trace(result.data[0]); <------ ここで指定したクラスのオブジェクトになっていますよ。
  }
}

ちなみに、executeの前に itemClass を指定したらきちんと帰ってきたが、 executeの後に指定したら、nullになってしまった。