Transactd 2.0 その1 SQLライクなクエリーと結果セット
いよいよTransactd 2.0をリリースします。Transactd 2.0で新しくなったのは主に、
です。順番に詳しく紹介してゆきます。今回は「SQLライクなクエリーと結果セット」です。
Transactdの従来のAPIはどんな処理でも書けますが、SQLに慣れた方にとってはあまりフレンドリーではありませんでした。特に、JOINを含むレコードの読み取り・検索は、SQLと大きく異なっています。
そこで、SQLライクなAPIと結果を扱うrecordset
クラスが追加されました。
recordset
は連想配列によく似た結果セットで、rs[1]["name"]
のように行・列に添え字でアクセスできます。内部実装はC++であり、PHPでもRubyでも高速にSQLライクな処理ができます。
紹介のためのサンプルコードはPHPで書いてみます。メソッドの多くは、メソッドチェーンができるようにthisを返します。
OrderBy
まずはOrderBy
。recordset rs
の"group"
というフィールドで昇順にソートします。
$rs->orderBy("group");
逆順にするにはreverse
を使います。
$rs->reverse();
複数のフィールドで昇順、降順が混在するorderBy
は、sortFields
クラスにフィールド名とソート方向をセットして渡します。
"group"
で昇順、"tel"
で降順にするならば、以下のようになります。
$sort = new sortFields(); $sort->add('group', true).add('tel', false); $rs->orderBy($sort);
GroupBy
次に、GroupBy
。groupQuery
オブジェクトに情報をセットしてrecordset
のgroupBy
メソッドに渡します。
$gq = new groupQuery();
...
$rs->groupBy(gq);
groupQuery
オブジェクトには複数の計算関数を追加できます。計算関数には対象のフィールドと結果フィールド名を指定します。
例は"group"
でグルーピングして各グループに属するレコード数をカウントし"group_count"
という列に結果を格納します。
$gq = new groupQuery(); $gq->keyField("group"); //<---- グルーピングキーフィールドの指定 $func = new count("group_count"); //<---- 関数と結果列名の指定 $gq->addFunction($func); $rs->groupBy($gq);
計算関数にはcount
の他にもsum
avg
min
max
があります。
レコードごとに計算の対象とするかどうか指定したいときは、計算関数のオブジェクトにWhen
フィルタを設定できます。
"tel" != ""
の時だけカウントしたいときは
$func->when("tel", "<>" ,"");
でフィルタリングできます。さらに、
$func->when("tel", "<>" ,"")->and_("tel" , "<>", "090*");
のようにand_()
とor_()
条件をメソッドチェーンでつないでいくこともできます。
また、グルーピングのキーは複数のフィールドとすることもできます。
$gq->keyField("group", "name");
MatchBy
結果セットを得たあとでフィルタリングできます。マッチしない行はレコードセットから削除されます。
$rq = new recordsetQuery(); $rq->when("count", ">", "1")->and_("tel", "<>", ""); $rs->matchBy(rq);
途中結果をコピー
SQLでは完全な結果を得るのみで途中結果を得ることはできません。
たとえば、売上の明細とその金額の合計を得たい場合、明細のクエリーと合計を計算するクエリーの2つが必要です。(または合計を自分で計算します。)
Transactdでは、処理をクライアント側で順番に書くので、途中の結果セットをコピーしておくだけで両方の結果を得られ効率的です。
... $rs2 = $rs->clone(); ... $rs2->groupBy(gq); // rsは明細 // rs2は合計
レコードの読み取り
新しく読取専用クラスactiveTable
クラスが追加されました。
activeTable
には主に、read
, join
, outerJoin
の3つのメソッドしかありません。
まずは単純なread
です。データベースとテーブル名からactiveTable
を生成します。
次にクエリーを作成してactiveTable
のread
に渡すと、結果セット(recordset
)が返ってきます。
クエリーは、select
, where
, and_
, or_
メソッドでSQLライクに記述できます。
まず、activeTable
を生成します。
$at = new activeTable($db, "user");
検索には必ずindex
を番号で指定します。
$at->index(1);
検索を開始する先頭レコードの値を指定します。この値はindex
で指定したキーフィールドの値です。
$at->index(1)->keyValue("1");
次にquery
オブジェクトを使ってSQLライクに検索条件を指定します。
$q = new query(); $q->select("id", "名前", "group")->where("group", "<=", "2")
フィールドに別名を付けてみましょう。
$at->alias("名前", "name");
最後に読み取ります。
$rs = $at->read($q);
全部まとめて書くと (クラスの名前空間 BizStation\Transactdは省略)
$db = new database(); $uri = "tdap://localhost/test?dbfile=transactd_schema"; $db->open($uri, TYPE_BDF, OPEN_NORMAL); $at = new activeTable($db, "user"); $at->alias("名前", "name"); $q = new query(); $q->select("id", "name", "group")->where("group", "<=", "2") $rs = $at->index(1)->keyValue("1")->read($q);
すべてのフィールドを読み取りたいときは、select
メソッドでフィールドを選択しないようにします。そうするとデフォルトですべてのフィールドが返ります。
$q = new query(); $q->where("group", "<=", "2") $rs = $at->index(1)->keyValue("1")->read($q);
Join
さあいよいよ、核心のJoin
です。
TransactdのJoin
は、サーバーサイドでの結合ではなく、クライアントサイドで結合します。結合は、事前に取得したrecordset
とこれから読み取るテーブルのフィールドで行います。
1対1のJoin
例として$rs
は上記の結果セットとします。結果セットのgroup
フィールドにはグループ番号が入っています。この番号からgroups
テーブルのname
フィールドをJoinしてみましょう。
$at2 = new activeTable($db, "groups"); $q->select('name'); //<--- name 列だけを読み取る $at2->index(0)->join($rs, $q, "group"); //<--- 結果セットのgroup列をキーとしてJoin
これで、user
テーブルから得た結果セットにgroups
テーブルのname
フィールドをJoinしました。
内部では、recordset
のgroup
フィールドの値をgroups
のindex
で指定されたキーフィールドにセットして、順にレコードを取得しています。この場合、index(0)
はgroups
のプライマリーユニークキーなので、1対1のJoinです。
キーがマルチセグメントキーの場合は、それぞれのセグメントに対応するrecordset
のフィールドを指定します。
例えば、groups
テーブルのprimaryキーがtype + id
なら
$at2 = new activeTable($db, "groups"); $q->select('name'); $at2->index(0)->join($rs, $q, "type", "group");
のようにjoinメソッドで2つのフィールドを指定します。
1対多のJoin
recordset
内の1行に対して複数の相手レコードがある場合は1対多のJoinです。
この場合、指定するインデックスは、重複可能なキーか、マルチセグメントのユニークキーで下位セグメントが未指定、のどちらかになります。
呼び出しは、1対1の場合とほとんど同じです。
例えば、まず、1~100までのグループを読み取って、そこに属するユーザーをすべてJoinしてみましょう。
最初に1~100までのグループを読み取ります。
$at = new activeTable($db, "groups"); $at2->alias("id", "group_id"); $q = new query(); $q->where("group_id", "<=", "100") $rs = $at->index(0)->keyValue("1")->read($q);
次にそこに属するユーザーのid
とname
をJoinしてみます。
$at2 = new activeTable($db, "user"); $at2->alias("名前", "name"); $q->reset()->select("id", "name"); $at2->index(1)->join($rs, $q, "group_id"); //<--- index=1はgroupフィールドの重複可キー
これで、読み取ることができました。$q->reset()
はquery
オブジェクトの状態をリセットして、オブジェクトを使いまわすために呼び出しています。
OuterJoin
先ほどまでのJoinはInnerJoin
で、相手レコードが見つからなかった場合は、recordset
内の該当行は削除されます。
それに対して、outerJoin
メソッドを使うと、相手レコードが見つからなかった場合でも元の行はそのまま残ります。追加されたフィールドは数値0
、文字列では""
で初期化されます。
Joinの最適化オプション
TransactdのJoinには1つだけ最適化オプションがあります。
Joinメソッドは、テーブルにアクセスする前にrecordset
内の各行のJoinのキー値を調べ、同じものがないか走査し、同値であればそれを省いて読み取りを行い、読み取られるレコード数を削減する処理を行っています。
ところが、各行のJoinのキー値がユニークであることがわかっている場合、この処理はムダです。そこでこの処理を省くためのオプションが用意されています。
recordeset
の各行のJoinキー値がユニークであるとわかっている場合は、query
オブジェクトのoptimize
にjoinHasOneOrHasMany
を指定します。
$q->optimize(joinHasOneOrHasMany);
Union
2つのrecordset
オブジェクトを和結合できます。$rs
に$rs2
を結合します。
$rs->unionRecordset($rs2);
ただし、2つのレコードセットのフィールドは順序も含めてすべて同じでなければなりません。もし異なる場合は例外が投げられます。
recordsetのそのほかの主な機能
列情報
recordset
のfieldDefs
メソッドで、fielddef
オブジェクトのコレクションが返ります。
例は、すべてのフィールド名を表示しています。
$fds = $rs->fieldDefs(); foreach($fd in $fds) { echo $fd->name().PHP_EOL; }
Top
recordset
の先頭N行を返します。
$rsTop10 = new recordset(); $rs->top($rsTop10, 10)
もし、$rs
の行数がN行に満たない場合は、すべての行がコピーされます。
FirstとLast
先頭行および最終行を返します。
$firstRecord = $rs->first(); $lastRecord = $rs->last();
removeField
N番目(0Orign)の列を削除します。
$rs->removeField(3);
appendField
列を末尾に追加します。
$rs->appendField("mobile phone", ft_myvarchar/*type*/, 30/*length*/);
まとめ
ご覧いただいた通り、activeTable
とrecordset
の組み合わせを用いると、SQLライクな表現でSQLとほぼ同様な結果を簡単に得ることができます。処理の順序や使用するインデックスの指定など、プログラマの思い通りにとてもフレキシブルに処理できます。
なお、SQLのような四則演算や文字列操作関数は、各言語で可能な処理ですので用意されていません。SQLは独立した言語なのでそのような関数がありますが、TransactdはAPIですので、それぞれの言語に用意されたものを使用できます。
次回は、今回ご紹介したSQLライクなクエリーのソースコードををVisualな操作で簡単に生成してくれる、queryBuilder
をご紹介したいと思います。queryBuilder
は、WindowsとMac OS X 10.9 でC++/PHP/Ruby/JScriptのソースを生成できます。