Transactd 2.0 その１ SQLライクなクエリーと結果セット

いよいよTransactd 2.0をリリースします。Transactd 2.0で新しくなったのは主に、

• SQLライクなクエリーと結果セット
• 分散クエリー
• O/Rマッピング

です。順番に詳しく紹介してゆきます。今回は「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では完全な結果を得るのみで途中結果を得ることはできません。
たとえば、売上の明細とその金額の合計を得たい場合、明細のクエリーと合計を計算するクエリーの２つが必要です。（または合計を自分で計算します。）

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のソースを生成できます。