order_byメソッドとOrderedViewクラスの実装

Koherent_DB

実装の楽なlimitメソッドから書こうかと思いましたが、order_byメソッドなくしてlimitメソッドは使い物にならないので、先にorder_byメソッドの実装について書きます。

order_byメソッドではSQLのORDER BYのように検索結果をソートして取得するためのメソッドです。ここではソートを実現するorder_byメソッドと、order_byメソッドの戻り値の型となるOrderedViewクラスの実装について書きます。

例で使用するテーブル

これまで同様、次のようなテーブルがあるものとして話を進めます。

members: メンバーを格納したテーブル
member_id family_name first_name age sex group_id
1011 中田 祐輔 22 0 1
1234 山下 沙希 17 1 2
1999 宮川 崇 18 0 2
2112 石田 菜々 21 1 1
2345 山崎 千佳 19 1 1
PerlShellKoherent::DBでのテーブルの取得

次のようにして、$membersがTableオブジェクトを事前に保持しているものとします。

my $members = $database->table('members');

order_byメソッドの使い方

SELECTのAPI(2) 様々なSELECT文への対応に書いたように、

SELECT * FROM members ORDER BY age;

のようにデータをソートして参照するには、order_byメソッドを用いて下のように書きます。

my $result = $members->order_by(sub{
    my ($row1, $row2) = @_;
    $row1->{'age'} <=> $row2->{'age'}
});

order_byメソッドに渡しているのは比較サブルーチンです。比較サブルーチンは二つの行の大小関係を定義します。二つの行$row1、$row2を引数にとり、$row1が$row2より小さい場合に負の数を、$row1と$row2が等しい場合に0を、$row1が$row2より大きい場合に正の数を返します。order_byメソッドの戻り値は、比較サブルーチンを用いてソート済データを返すViewオブジェクトです。比較サブルーチンを用いることで、複数のフィールドをキーとするソートや、より複雑な計算結果によるソートなどをPerlを用いて柔軟に記述することができます。これは、whereメソッドに条件サブルーチンを渡すのと同じ考え方です。

フィールド値を文字列としてソートする場合に限り、次のようにソートキーとなるフィールド名を渡してorder_byメソッドを呼ぶこともできます。

$result = $members->order_by('family_name');

第2引数を渡すとそれを降順でソートするかどうかのフラグ（真の場合は降順ソート、偽の場合は昇順ソート）と認識し、ソートの昇順・降順を指定できるようにします。

$result = $members->order_by('family_name', 1);

第2引数を省略した場合はPerlの仕様上undefとして受け取るため、降順フラグが偽となり昇順ソートが行われます。第1引数に比較サブルーチンを渡した場合でも第2引数を利用できますが、降順フラグで昇順ソートを指定するよりも比較サブルーチン中で正負を逆転することでそれを実現した方が高速に動作します*1。

使用頻度が高いと思うので、数値としてのソート専用のメソッドを用意することも検討していますが現段階では保留します*2。

order_byメソッドの実装

次にorder_byメソッドの実装について考えます。

whereメソッドが条件にマッチする行だけを返すConditionedViewクラスを返したように*3、order_byメソッドもソート済のビューを表すViewクラス（を継承したクラス）を返します。このViewクラスをOrderedViewクラスとします。

order_byメソッドは比較サブルーチンをOrderedViewクラスに渡して処理を丸投げするだけですが、第1引数にフィールド名を渡した場合はorder_byメソッドの内部で比較サブルーチンを作成する必要があります（OrderedViewクラスの内部で対処することもできますが、order_byメソッドの中で対処した方がプログラムが簡潔になります）。また、降順フラグが真の場合にはソート順を逆転させるために比較サブルーチンをサブルーチンでラップして戻り値の正負を逆転させます。

コードにすると次のようになります。order_byメソッドに関係ない部分は省略しています。

package Koherent::DB::View;

# 省略

use Koherent::DB::OrderedView;

# 省略

sub order_by{
    my $self = shift;

    # 比較サブルーチン
    my $compare = shift;

    # 降順フラグ
    my $desc = shift;

    if(ref $compare ne 'CODE'){
        # $compareにフィールド名が渡された場合は、
        # そのフィールドをソートキーとしてソートを行う。

        my $field_name = $compare;

        # $compareでサブルーチンが渡された場合にように
        # 戻り値を正負逆転させるサブルーチンでラップすると
        # そのサブルーチン呼び出しのためのオーバーヘッドが発生するため、
        # 降順の際には初めから正負反転した結果を返すサブルーチンを作成する。
        if($desc){
            # 文字列降順にソートするための比較サブルーチン
            $compare = sub{
                my ($row1, $row2) = @_;
                -($row1->{$field_name} cmp $row2->{$field_name});
            };
        }else{
            # 文字列昇順にソートするための比較サブルーチン
            $compare = sub{
                my ($row1, $row2) = @_;
                $row1->{$field_name} cmp $row2->{$field_name};
            };
        }
    }elsif($desc){
        # $compareでサブルーチンが渡され、かつ$descが真の場合は、
        # ソート順を逆転させるために$compareの戻り値の正負を逆転する。

        my $original_compare = $compare;
        $compare = sub{$original_compare->(@_) * -1} if $desc;
    }

    # order_byメソッドを呼び出したViewを元にソート済データを返す
    # OrderedViewクラスのインスタンスを返す。
    Koherent::DB::OrderedView->new($self, $compare);
}

# 省略

OrderedViewクラスの実装

OrderedViewクラスでもiteratorメソッドの実装が重要となります。

ConditionedViewクラスの場合には、ConditionedViewクラスとセットでConditionedViewIteratorクラスを実装しました。OrderedViewクラスに対してもOrderedViewIteratorクラスを考えてもいいのですが、ここでは少し発想を広げてみます。

ソートの処理はストレージ上ではなくメモリ上で行う予定です。テーブルが巨大になると全データをメモリ上に乗せることはできなくなってしまうかもしれません。しかし、ソートは時間計算量がO(n log n)である比較的重い処理*4です。そもそもメモリ上に乗らないような件数のデータをストレージ上でソートしようとすると、処理が重すぎて終わらないのではないかと思います。それほど大量のデータをソートするにはインデックスを用いるのが普通です。PerlShellKoherent::DBではインデックスを用いたソートにはorder_byメソッドとは別のメソッドを利用するため、order_byメソッドでは全件をメモリ上に乗せても問題がない程度の件数を想定し、メモリ上でソートを行おうと思います。

メモリ上でソートを行うのであれば、OrderedViewクラスのiteratorメソッドが呼び出された際の処理は次のようになります。

  1. order_byメソッドの呼び出し元Viewオブジェクトからイテレータを取得する。
  2. 取得したイテレータを介して全件データを取得する（ソートのために配列に格納する）。
  3. 取得した全件データを比較サブルーチンを用いてソートする。
  4. ソート済の全件データを格納した配列をOrderedViewクラス用のイテレータに渡す。

このように考えると、OrderedViewクラス用のイテレータは配列に格納された各行のデータを先頭から順に返すだけでよいことになります。

そのようなイテレータをOrderedViewIteratorクラスとしても良いですが、配列で各行を保持し順に読み出すという処理はより汎用的なものとしてライブラリ化しておくと便利そうです。そこで、配列で行を保持するクラスをArrayViewクラス、ArrayViewクラスからデータを読み出すクラスをArrayViewIteratorクラスとして実装します。

ソート自体は、当初はperlのsort関数を使うことを考えていました。しかし、パッケージ越しのソートサブルーチンがうまく働かないに書いたようにパッケージ越しにソートサブルーチンを渡してソートを行うとうまく動作しなかったため、独自にソートの処理を実装することにしました。このソートはKoherent::PerlShellDB::Util::qsortとして定義しています*5。qsortサブルーチンは第1引数にソート対象の配列を、第2引数にその配列の要素に対する比較サブルーチンをとります。qsortサブルーチンのアルゴリズムにはクイックソートを用いています*6。

以上を踏まえてOrderedViewクラスを実装すると次のようになります。なお、Viewオブジェクトからの全件取得のためにrowsメソッドを使用しています。rowsメソッドはViewオブジェクトが自身のイテレータを介して全件を取得し、配列リファレンスとして返すというメソッドです。

package Koherent::DB::OrderedView;

# 省略

use base qw(Koherent::DB::View);
use Koherent::DB::ArrayView;
use Koherent::DB::Util qw(qsort);

sub new{
    my ($class, $view, $compare) = @_;

    my $self = $class->SUPER::new;
    $self->{'view'} = $view;
    $self->{'compare'} = $compare;

    $self;
}

sub iterator{
    my $self = shift;

    # order_byメソッド呼び出し下Viewオブジェクトから全件を取得
    my $rows = $self->{'view'}->rows;

    my $compare = $self->{'compare'};

    # 全件データをソート
    qsort($rows, $compare);

    # ソート結果を格納したArrayViewオブジェクトを生成しイテレータを取得
    Koherent::DB::ArrayView->new($rows)->iterator;
}

# 省略

package Koherent::DB::View;
# 省略

sub iterator{
    # 抽象メソッドのためオーバーライドが必要
    croak "iterator is an abstract method. Override it.";
}

sub rows{
    my $self = shift;
    my $rows = [];

    my $iterator = $self->iterator;

    # イテレータを用いて全件を取得
    while(my $row = $iterator->next){
        push @{$rows}, $row;
    }

    $rows;
}

# 省略

ArrayViewクラスの実装

ArrayViewクラスは配列として行データを保持し、それにアクセスするためのイテレータをiteratorメソッドで提供します。

実装上難しい部分はないため、下記にコードのみを示します。ArrayViewクラスではupdateメソッド等の更新系メソッドはサポートしません。このため、コンストラクタに与えた配列を直接操作しない限り、生成時のデータを保持し続けることになります。

package Koherent::DB::ArrayView;

# 省略

use base qw(Koherent::DB::View);
use Koherent::DB::ArrayViewIterator;

sub new{
    my $class = shift;
    my $rows = shift;

    # 引数を省略した際に、
    # 空のArrayViewオブジェクトを生成するため
    $rows = [] unless defined($rows);

    # $rowsが配列リファレンスでなければ
    # ArrayViewIteratorがエラーを起こすため
    croak 'ROWS must be an array reference.'
        unless UNIVERSAL::isa($rows, 'ARRAY');

    my $self = $class->SUPER::new;
    $self->{'rows'} =  $rows;

    $self;
}

sub iterator{
    my $self = shift;
    Koherent::DB::ArrayViewIterator->new($self);
}

# 省略

ArrayViewIteratorクラスの実装

ArrayViewIteratorクラスはArrayViewクラスの保持する配列の各要素に順番にアクセスし返します。順番にアクセスするためには、現在何番目の要素を指しているかを記録しておかなければなりません。下記コードでは、$pointerという変数に現在指している要素番号を格納しています。

package Koherent::DB::ArrayViewIterator;

# 省略

use base qw(Koherent::DB::ViewIterator);

sub new{
    my ($class, $array_view) = @_;

    my $self = {
        array_view => $array_view,
        # 現在何番目の要素を指しているかを表すポインタ
        pointer => 0, # 最初の要素を指すため初期値は0
    };

    bless $self, $class;
}

sub next{
    my $self = shift;

    my $pointer = $self->{'pointer'};

    my $row;

    # ポインタの指す要素（行）のデータを取得
    $row = $self->{'array_view'}->{'rows'}->[$pointer];

    # ポインタを一つ進めて記録
    $self->{'pointer'} = $pointer + 1;

    $row;
}

# 省略

まとめ

SQLのORDER BYに相当するorder_byメソッドを実装しました。

order_byメソッドには比較サブルーチンを与えることで、単純なフィールドによるソートに留まらない複雑なソート条件を記述できるようにしました。また、第2引数である降順フラグに真を渡すことで、比較サブルーチンの結果を反転し結果を降順で得られるように実装しました。またショートカットとして、比較サブルーチンの代わりにフィールド名を渡すことで、フィールド値を文字列として評価しソートを行えるようにしました。

whereメソッド同様に、order_byメソッド、OrderedViewクラスをセットで実装しました。しかし、OrderedViewクラスのデータ参照にはOrderedViewIteratorクラスを用意するのではなく、より汎用的なクラスとしてArrayViewクラスとArrayViewIteratorクラスを用いることにしました。ArrayViewクラスは単純に表データを配列として保持するクラスです。OrderedViewクラスのiteratorメソッドが呼び出された際には、全件データをソートして、それを格納するArrayViewオブジェクトを生成します。iteratorメソッドの戻り値には、そのArrayViewオブジェクトのイテレータを返します。

*1:order_byメソッドの実装で書いたように、降順フラグに真を渡して降順ソートを行う場合は、比較サブルーチンをラップして戻り値の正負を逆転するサブルーチンを挟むため、余分なサブルーチン呼び出しと正負の反転のオーバーヘッドが発生します。

*2:引数で数値・文字列の指定を行う形も検討しましたが、例えば第3引数に数値・文字列のフラグを渡すようにすると、第3引数のフラグが真の場合は昇順の場合でも第2引数の降順フラグを省略できなくなってしまい、やや使いづらいように感じます。数値によるソートを別メソッドで切り出す方法も考えられます。しかし、order_byメソッドは引数によって比較サブルーチンとフィールド名が渡された場合を区別するのに、数値用ソートメソッド（例えばorder_by_number）はフィールド名のみ受け取るというのも気持ち悪いように思います。order_by（比較サブルーチンのみ）、order_by_text（フィールド名のみ受け取り文字列としてソート）、order_by_number（フィールド名のみ受け取り数値としてソート）の三つを用意することもできますが、やや冗長ですし、他のメソッドはすべて引数で挙動をコントロールしているので、order_byだけ複数のメソッドを用意するのも一貫性に欠けます。フィールドの型をTableクラスから引継ぎ自動判別することも考えましたが、selectメソッドで自由に式を埋め込まれた場合（例えばsub{$_[0]->{'age'} - 1}を渡された場合）、その結果が数値なのか文字列なのかを判別することができません。どのような方法をとるのがベストかの判断が難しく、また数値によるソートは比較サブルーチンで実現できるため、現断簡では保留としました。

*3:whereメソッドとConditionedViewクラスの実装参照。

*4:例えば、線形探索はO(n)、二分探索はO(log n)でソートより軽い処理です。ソートの計算量もアルゴリズムによりますが、比較によるソートではO(n log n)より高速なソートが存在しないことが数学的に証明されています（参考文献：定本 Cプログラマのためのアルゴリズムとデータ構造 p.180）。O(n log n)となる比較によるソートアルゴリズムは、クイックソート、マージソート、ヒープソートなどです。なお、比較によらないソートにはビンソートや基数ソートのように、O(n)でソートを行うことができるアルゴリズムも存在します。しかし、比較によらないソートには制約が多く、また処理上の様々なオーバーヘッドから、現実的なパフォーマンスを考えると通常は比較によるソートが使われます。

*5:qsortのqはクイックソートのqです。最初はサブルーチン名をsortにしようとしたのですが、Perlのsort関数と衝突してうまく動作しなかったためsort以外の名前にしました。quick_sortという名前も考えましたが、C言語のqsort関数にちなんでqsortとしました。

*6:クイックソートはデータがソート済である場合に計算量がO(n^2)になってしまうため、左端、中央、右端のデータの中央値をピボットとする方法を用いています。3値の中央値をピボットとする手法でも、巧妙にデータを並べれば計算量がO(n^2)とすることはでき、最大計算量はO(n^2)のままです。しかし、そのような並びが自然に作られることはほぼないと考え、現実的なレベルでの対策として3値の中央値をピボットとする手法を採用しました。より抜本的な対策としては、クイックソートとヒープソートを組み合わせたイントロソートというアルゴリズムが存在します。

whereメソッドとConditionedViewクラスの実装

Koherent_DB

友人の結婚式に出るために関西に帰ったり、Module::Starterを触ったり、PostgreSQLのトランザクションの実装を調べたりしている間にずいぶん間が空いてしまいました。

さて、前回までにSELECT/INSERT/UPDATE/DELETE、つまりCRUDのためのAPIを一通り考えた*1ので実装に移りたいと思います。トランザクション*2やCREATE/DROPなどのDMLのためのAPIも検討しなければなりませんが、それらは別途考えます。

例で使用するテーブル

これまで同様、次のようなテーブルがあるものとして話を進めます。

members: メンバーを格納したテーブル
member_id family_name first_name age sex group_id
1011 中田 祐輔 22 0 1
1234 山下 沙希 17 1 2
1999 宮川 崇 18 0 2
2112 石田 菜々 21 1 1
2345 山崎 千佳 19 1 1
PerlShellKoherent::DBでのテーブルの取得

次のようにして、$membersがTableオブジェクトを事前に保持しているものとします。

my $members = $database->table('members');

whereメソッドの使い方

whereメソッドの使い方を再掲しておきます。

-- 成人のメンバーを取得
SELECT * FROM members WHERE age >= 20;

上記のようなSQL相当の検索を実現するために、PerlShellKoherent::DBでは次のようなコードを書くことを想定していました。

my $adults = $members->where(sub{
    my $row = shift;
    $row->{'age'} >= 20;
});

whereメソッドに渡しているのは条件サブルーチンです。条件サブルーチンは引数として行（$row）をとり、その行が条件に適合するかを判別します。条件に適合する場合には真を、条件に適合しない場合には偽を返します。whereメソッドの戻り値は、渡された条件サブルーチンに適合する行だけを保持したViewオブジェクトです*3。

$adultsからデータを得るには、イテレータを用いて次のように行います。詳細はSELECTのAPI(3) イテレータによるデータアクセスの通りです。

# $adultsからイテレータを取得
my $iterator = $adults->iterator;

# 1行ずつデータを読み出して表示
while(my $row = $iterator->next){
    print "Family name: $row->{'family_name'}, First name: $row->{'first_name'}\n";
}

whereメソッドの実装

前節を元に実装を考えてみましょう。

whereメソッドはViewクラスのインスタンスメソッドであり、Viewオブジェクトを返す必要があります。引数は前節で述べたように条件サブルーチンです。戻り値となるViewオブジェクトは、当然Viewクラスを継承したクラスのインスタンスである必要があります。

whereメソッドの戻り値となるViewオブジェクトのクラスをConditionedViewクラスとしましょう。そうすると、whereクラスが行うことは条件サブルーチンを用いてConditionedViewオブジェクトを生成し、それを返すことと言えます。ConditionedViewクラスのコンストラクタには何を渡せば良いでしょうか。当然、条件サブルーチンを渡さなければなりません。加えて、どのViewオブジェクトに対する条件なのかという情報がなければならないので、whereメソッドを呼び出したViewオブジェクト自身を渡す必要があります。

コードにすると次のようになります。whereメソッドに関係する部分以外は省略しています。

package Koherent::DB::View;

use Koherent::DB::ConditionedView;

# 省略

sub where{
    my $self = shift;

    # 条件サブルーチンの受け取り
    my $condition = shift;

    # 条件サブルーチンを用いて生成したConditionedViewオブジェクトを返す。
    # whereメソッドを呼び出したViewオブジェクト自身を一つ目の引数で渡している。
    Koherent::DB::ConditionedView->new($self, $condition);
}

# 省略

ConditionedViewクラスの実装

whereメソッドではConditionedViewクラスに処理を丸投げしただけなので、次にConditionedViewクラスの実装を考えます。ConditionedViewクラスで考えなければならないのは、コンストラクタであるnewメソッドと、データアクセスのためのイテレータを返すiteratorメソッドです。

コンストラクタでは特別な処理はありません。受け取った引数を必要なときに取り出せるように、フィールド（メンバ変数）として格納しておくだけです。

iteratorメソッドも特別な処理はしません。データアクセスはイテレータを介して行うため、ここでもイテレータに処理を丸投げするだけです。この、丸投げする先のイテレータを表すクラスをConditionedViewIteratorクラスとしましょう。条件判別のために必要な情報はすべてConditionedViewオブジェクトが保持しているため、ConditionedViewIteratorクラスのコンストラクタに渡さなければならないものはConditionedViewオブジェクトのみで良いでしょう。

コードにすると次のようになります。Viewクラスはnewとiterator以外にも様々なメソッドを持つ予定ですが、ややこしくなるのでここでは省略します。

package Koherent::DB::ConditionedView;

use base qw(Koherent::DB::View);
use Koherent::DB::ConditionedViewIterator;

# 省略

sub new{
    my ($class, $view, $condition) = @_;

    # 受け取った$viewと$conditionをConditionedViewクラスのフィールドとして格納
    my $self = $class->SUPER::new;
    $self->{'view'} = $view;
    $self->{'condition'} = $condition;

    $self;
}

sub iterator{
    my $self = shift;

    # 実際に条件判別をしながらデータアクセスを行う
    # ConditionedViewIteratorオブジェクトを生成して返す。
    Koherent::DB::ConditionedViewIterator->new($self);
}

# 省略

ConditionedViewIteratorクラスの実装

ConditionedViewクラスでも処理を丸投げしただけだったので、条件判別のための処理はConditionedViewIteratorクラスに記述されることになります。

ConditionedViewIteratorクラスはViewIteratorクラスを継承しています。ViewIteratorクラスは、順次データにアクセスするためのnextメソッドのみを持ちます。nextメソッドは、対象とするテーブルやビューの行（のデータを格納したハッシュへのリファレンス）を返します。すべての行を取り出した後はundefを返します。

nextメソッドで行う処理は次の三つです。

  1. whereメソッドの呼び出し元Viewオブジェクトのイテレータを利用し、行を取り出す。
  2. 取り出した行を条件サブルーチンに渡し、対象であればそれを返す。
  3. 取り出した行が対象でなければ1に戻り、次の行を取り出す。

SELECTのAPI(3) イテレータによるデータアクセス 条件が評価されるタイミングで述べたように、イテレータが返すデータはiteratorメソッドが呼ばれた時点でのものです。このため、whereメソッドの呼び出し元Viewオブジェクトからイテレータを取得するのは、ConditionedViewオブジェクトのiteratorメソッドが呼ばれたタイミングとなります。つまり、ConditionedViewIteratorクラスのコンストラクタの内部で元のViewオブジェクトのiteratorメソッドを呼び、イテレータを取得する必要があります。

これらを考慮してConditionedViewIteratorクラスを実装すると、次のようになります。

package Koherent::DB::ConditionedViewIterator;
use base qw(Koherent::DB::ViewIterator);

# 省略

sub new{
    my ($class, $conditioned_view) = @_;

    my $self = {
        conditioned_view => $conditioned_view,
        # whereメソッドの呼び出し元Viewオブジェクトからイテレータを取得
        original_iterator => $conditioned_view->{'view'}->iterator,
    };

    bless $self, $class;
}

sub next{
    my $self = shift;

    my $original_iterator = $self->{'original_iterator'};
    my $row;

    # 1. whereメソッドの呼び出し元Viewオブジェクトのイテレータを利用し、行を取り出す。
    while($row = $original_iterator->next){
        # 2. 取り出した行を条件サブルーチンに渡し、対象であればそれを返す。
        my $condition = $self->{'conditioned_view'}->{'condition'};
        # 3. 取り出した行が対象でなければ1に戻り、次の行を取り出す。
        last if $condition->($row);
    }

    $row;
}

# 省略

アクセサメソッドを介さないConditionedViewオブジェクトのフィールドの参照

上記では、ConditionedViewIteratorオブジェクトからConditionedViewオブジェクトのデータを参照する際にアクセサメソッドを介さずにフィールドを直接参照しています。

ConditionedViewIteratorクラスとConditionedViewクラスの結び付きは通常の独立したクラス同士よりも強く、例えばJavaでこれを記述しようとするとConditionedViewIteratorクラスはConditionedViewクラスの内部クラスとして記述されるでしょう。内部クラスと考えれば、ConditionedViewIteratorオブジェクトからConditionedViewオブジェクトのフィールドを直接参照しても不自然ではありません。逆にConditionedViewIteratorオブジェクトからConditionedViewオブジェクトのデータを参照するためだけにアクセサメソッドを作れば、ConditionedViewクラスがアクセサメソッドだらけになり、ユーザ視点ではわかりづらくなるだけだと思います。このような理由から、ConditionedViewクラスにアクセサメソッドを作らずにConditionedViewIteratorクラスから直接フィールドを参照しています。

なお、これはConditionedViewクラスとConditionedViewIteratorクラスだけでなく、select、order_by、limitなどの各種メソッドにおけるViewクラスとViewIteratorクラスでも同じような実装になるでしょう。

まとめ

SQLのWHERE句のように条件に適合するデータの取得を実現するために、whereメソッド、ConditionedViewクラス、ConditionedViewIteratorクラスの三つを実装しました*4。

whereメソッドに限らず、今後も

  • Viewクラスの検索用メソッド（select、order_by、limitなど）
  • そのメソッドの挙動を実現するためのViewクラス（を継承したクラス）
  • そのViewクラスにアクセスするためのViewIteratorクラス（を継承したクラス）

の三つを1セットとして実装することになると考えられます。

*1:SELECTのAPI(1) 概要、SELECTのAPI(2) 様々なSELECT文への対応、SELECTのAPI(3) イテレータによるデータアクセス、SELECTのAPI(4) インデックスの利用、UPDATE、DELETE、INSERTのAPIを参照。

*2:トランザクションはひとまず扱わない予定でしたが、最初から追記型MVCCで書いた方が楽な気がしてきたので心が揺れています。

*3:正確には行を保持しているわけではなく、イテレータがデータアクセス時に条件に適合する行だけを返すことになります。

*4:インデックスを利用した検索に関してはwhereメソッドでは実現できません。詳細はSELECTのAPI(4) インデックスの利用を参照。

UPDATE、DELETE、INSERTのAPI

Koherent_DB

これまでは参照（SELECT）のためのAPIを考えてきましたが、次に更新（UPDATE）、削除（DELETE）、挿入（INSERT）のAPIを考えたいと思います。

なお、GROUP BYに当たる検索方法を検討していませんが、これらはプログラム上で実現できるうえに実装が大変そうなので、実装の優先度を下げたいと思います。

例で使用するテーブル

これまで同様、次のようなテーブルがあるものとして話を進めます。

members: メンバーを格納したテーブル
member_id family_name first_name age sex group_id
1011 中田 祐輔 22 0 1
1234 山下 沙希 17 1 2
1999 宮川 崇 18 0 2
2112 石田 菜々 21 1 1
2345 山崎 千佳 19 1 1
groups: メンバーの所属するグループを格納したテーブル
group_id group_name
1 大学生
2 高校生
PerlShellKoherent::DBでのテーブルの取得

次のようにして、$membersと$groupsがTableオブジェクトを事前に保持しているものとします。また、更新件数格納用に$countを定義してあるものとします。

my $members = $database->table('members');
my $groups = $database->table('groups');

my $count;

UPDATEのAPI

まず始めに、UPDATE、DELETE、INSERTの中でも記述が最も複雑になると考えられるUPDATEについて考えます。

全件更新
UPDATE members SET age = age + 1;

上記のような更新を行うために、Tableクラスにupdateメソッドを用意しようと思います。対応するコードは下記のようになります。戻り値は実際に更新された件数です。

$count = $members->update(sub{
    my $row = shift;
    $row->{'age'} = $row->{'age'} + 1;
});

# 省略形
$count = $members->update(sub{$_[0]->{'age'}++});
対象を指定した更新

上記のように全件に対して更新を行うことはまれで、通常は対象を指定して更新を行います。PerlShellKoherent::DBでも対象を指定する方法を提供しなければなりません。

対象を指定する方法は、データ参照時と共通である方が使いやすいでしょう。SQLでは、SELECT同様WHERE句によって、

-- 男性のメンバーの年齢を1増加
UPDATE members SET age = age + 1 WHERE sex = 0;

のように更新対象を指定します。PerlShellKoherent::DBでも、参照時同様whereメソッドで更新対象を指定しようとすると、次のようなコードになるでしょう。

$count = $members->where(sub{$_[0]->{'sex'} == 0})
                 ->update(sub{$_[0]->{'age'}++});

この方法は一見うまく機能するように思えますが、ある問題を抱えています。

PerlShellKoherent::DBではView（およびそれを継承したTable）クラスではwhereメソッドによって行の絞られたViewオブジェクトを得ます。Tableオブジェクトから直接updateメソッドを呼び出した場合はともかく、whereメソッドで得たViewオブジェクトから元のテーブルを更新にいくためにはどうすればよいでしょうか。しかも、更新対象となる行は、そのViewによって指定されたものでなければなりません。

これを解決するために、テーブルの各行を一意に識別するための行IDというものを導入しようと思います。すべての行は行IDを持つため、whereメソッドで得たViewオブジェクトを介して取り出した各行は、元々のテーブルのどの行のデータであったのかを追跡できるます。これを用いて、更新対象を指定します。

行はハッシュで表されているので、単純に行IDを追加するだけで実現できます。ハッシュに格納する際のキーは、フィールド名との重複を考えて-RID-のような通常フィールド名に用いないような文字を含んだ文字列にします。

ハッシュ以外にも任意のリファレンスを行として用いることができる実装となったため、行IDは行データに含まれるのではなく、内部的にやりとりされるようになりました。
(2009-11-24)

行IDによる更新

SQLと違い、PerlShellKoherent::DBでは行IDを用いて更新を行えるようにしようと思います。具体的には、行IDを含んだ行データを用いて次のように行います。

$count = $members->update($updated_member);

このような更新方法が可能になれば、あるデータを取得し、変更を加え、DBを更新するという一連の処理を次のようなコードで実現できるようになります。

# member_idが1234のメンバーを検索して取得
my $member = $members->index('member_id')->eq(1234)->iterator->next;

if($member){
    # メンバーが存在する場合

    # 姓を変更（結婚など）
    $member{'family_name'} = '佐藤';

    # 新しいデータでDBを更新
    $members->update($member);
}

複数行を更新したいケースも多いと思うので、引数に配列リファレンスを渡すと各行に対して更新する仕様にしたいと思います。

実際には行IDではなくPKによって更新されるような実装となりました。
(2009-11-24)

結合を用いた更新

RDBMS間で統一はされていませんが、結合を用いて更新を行うSQLも存在します。MySQL、PostgreSQL、Oracleの場合について、その例を挙げます。

-- 結合を用いた更新（MySQLの場合）
-- group_nameが'大学生'であるグループに所属するメンバーを更新
UPDATE members INNER JOIN groups ON members.group_id = groups.group_id
SET age = age + 1 WHERE groups.group_name = '大学生';

-- 結合を用いた更新（PostgreSQLの場合）
-- group_nameが'大学生'であるグループに所属するメンバーを更新
UPDATE members AS m SET age = age + 1 FROM groups AS g
WHERE g.group_id = m.group_id AND g.group_name = '大学生';

-- 結合を用いた更新（Oracleの場合）
UPDATE (
    SELECT age FROM members m, groups g
    WHERE g.group_id = m.group_id AND g.group_name = '大学生'
) SET age = age + 1;

PerlShellKoherent::DBでもこのような結合を用いた更新をサポートしようと思います。これについても、データ参照時との共通化を考えると次のようなコードになるでしょうか。

# インデックスを利用しない例
$count = $members->inner_join($groups,
        sub{$_[0]->{'group_id'} == $_[1]->{'group_id'}}
        )->update(sub{$_[0]->{'age'}++});

# インデックスを利用する例
$count = $members->index_inner_join($groups->index('group_id'),
        sub{$_[1]->eq($_[0]->{'group_id'})}
        )->update(sub{$_[0]->{'age'}++});

いずれも、結合を行うメソッドから得たViewオブジェクトのupdateメソッドを呼び出しています。結合の際には結合後の各行に対して、外部表（駆動表）の行IDを引き継ぐことでこれを実現しようと思います。

結合条件によっては外部表が膨れる（外部表のある行が結果表で複数行になる）場合があります。その場合、同一行IDを持つ行が複数更新対象になってしまいます。更新において外部表が膨れるケースは結合条件の記述ミスではないかと考えられるため、エラーを返すのが親切かもしれません。しかし、同一行IDに対して複数の更新が行われたかをチェックするオーバーヘッドを考えると、1行ごとにチェックを行うのはパフォーマンス上望ましくないと思います。ですので、外部表が膨れるケースでは単純に同一行に複数回の更新を行うように実装しようと思います。

DELETEのAPI

DELETEのAPIはUPDATEとほとんど同じです。UPDATEでは更新内容を引数に取りましたが、DELETEでは不要であるために対象の指定のみを行います。

全件削除
DELETE FROM members;

$count = $members->delete;
対象を指定した削除
DELETE FROM members WHERE sex = 0;

$count = $members->where(sub{$_[0]->{'sex'} == 0})->delete;
行IDによる削除
$count = $members->delete($deleted_member);

上記の$deleted_memberは行IDを含んでいる必要があります。例えば、次のような使い方ができます。

# member_idが1234のメンバーを検索して取得
my $member = $members->index('member_id')->eq(1234)->iterator->next;

if($member){
    # メンバーが存在する場合

    # メンバーを削除
    $members->delete($member);
}
結合を用いた削除
# インデックスを利用しない例
$count = $members->inner_join($groups,
        sub{$_[0]->{'group_id'} == $_[1]->{'group_id'}}
        )->delete;

# インデックスを利用する例
$count = $members->index_inner_join($groups->index('group_id'),
        sub{$_[1]->eq($_[0]->{'group_id'})}
        )->delete;

INSERTのAPI

INSERTは新しいデータを追加するだけなので簡単です。UPDATEやDELETEのように対象を指定する方法を考える必要がありません。

INSERT INTO members (member_id, family_name, first_name, age, sex, group_id)
VALUES (3000, '植田', '志保', 21, 1, 0);

上記を実現するには、Tableクラスにinsertメソッドを用意するのが自然でしょう。

my $new_member = {member_id => 3000, family_name => '植田', first_name => '志保',
    age => 21, sex => 1, group_id => 0};
$count = $members->insert($new_member);

# 省略形
$count = $members->insert({member_id => 3000, family_name => '植田',
        first_name => '志保', age => 21, sex => 1, group_id => 0});

配列リファレンスを用いて複数行のデータを渡した場合には、複数のデータを登録できるようにすると便利でしょう。また、下記のようなSELECTを用いたINSERTに対応するために、引数にViewオブジェクトを受け取った場合は、そのViewオブジェクトからイテレータを受け取り登録するという仕様を考えています。

INSERT INTO members SELECT * FROM other_members;

$count = $members->insert($other_members);

まとめ

SQLのUPDATE・DELETE・INSERTはそれぞれ、Tableクラスのupdate、delete、insertメソッドを用いて行うこととします。

whereメソッドやinner/outer_joinメソッドを用いて、データ参照時と同様に更新・削除の対象を指定する仕様とします。whereメソッドなどで得られたViewオブジェクトを介して元のテーブルを更新するために、行を一意に識別するための行IDを導入しようと思います。

SELECTのAPI(4) インデックスの利用

Koherent_DB

SELECTのAPI(2) 様々なSELECT文への対応で述べた検索ではインデックスを活用した検索を実現できません。ここではインデックスを用いた検索のAPIを考えます。

例で使用するテーブル

これまで同様、次のようなテーブルがあるものとして話を進めます。

members: メンバーを格納したテーブル
member_id family_name first_name age sex group_id
1011 中田 祐輔 22 0 1
1234 山下 沙希 17 1 2
1999 宮川 崇 18 0 2
2112 石田 菜々 21 1 1
2345 山崎 千佳 19 1 1
groups: メンバーの所属するグループを格納したテーブル
group_id group_name
1 大学生
2 高校生
PerlShellKoherent::DBでのテーブルの取得

次のようにして、$membersと$groupsがTableオブジェクトを事前に保持しているものとします。また、結果格納用に$resultを定義してあるものとします。

my $members = $database->table('members');
my $groups = $database->table('groups');

my $result;

なぜインデックスを特別扱いするのか

様々な条件での検索を簡単に実装するために、ViewクラスのwhereメソッドではPerlのプログラムで検索条件を記述する仕様にしました。例えば、

-- 結婚可能なメンバーを取得（※日本では男性18歳以上、女性16歳以上が結婚可）
SELECT * FROM members WHERE (sex = 0 AND age >= 18) OR (sex = 1 AND age >= 16);

というSELECT文を考えてみましょう。サブルーチンを渡して複雑な条件を指定する代わりに、条件をオブジェクトで表す次のような方法も考えられます。

$result = $members->where(
        Or->new(
            And->new(
                Eq->new('sex', 0),
                Ge->new('age', 18)
                ),
            And->new(
                Eq->new('sex', 1),
                Ge->new('age', 16)
                )
            )
        );

この例では、Condition（条件）クラスを継承したAndクラスやOrクラス、Ge（以上：greater than or equal to)クラスのインスタンスを使って複雑な条件を表しています。しかし、この方法は見るからにわずらわしい上に可読性も損なっています。そのため、PerlShellKoherent::DBでは次のように条件サブルーチンを渡す仕様にしました。サブルーチンが真を返す行のみを結果として返します。

$result = $members->where(sub{
    my ($sex, $age) = ($_[0]->{'sex'}, $_[0]->{'age'});
    ($sex == 0 && $age >= 18) || ($sex == 1 && $age >=16);
});

見た目にはすっきりしますし余計なオブジェクトを生成する必要もありません。Perlのプログラムで直接条件を記述するため自由度が高く、また実装上もAndやOrのような演算子を改めて実装する必要がありません。

しかし、条件をコードで記述しているために（PerlShellKoherent::DB上での構文解析が発生しないために）、内部的にどのインデックスを活用すべきかを判断することが容易ではありません。AndクラスやOrクラスを使う方法であれば、その条件の構造を解析し適切なインデックスを利用して検索することもできるでしょう。

このような理由から、インデックスの利用に際してはwhereメソッドとは別のメソッドが必要となります。

インデックスを利用するメソッド

インデックスはテーブル、またはビューを対象として構築されます。そのため、インデックスを表すIndexオブジェクトは、対象となるViewオブジェクトから得るのが自然です。Indexクラスは検索のためのメソッドを提供し、それらの戻り値はViewオブジェクトとなります。

例えば、membersテーブルのプライマリーキーであるmember_idで検索をするためには、次のようなコードが考えられます。

# member_idが1234のメンバーを取得
result = $members->index('member_id')->eq(1234);

このときに指定している'member_id'はmember_idフィールドに対するインデックスという意味ではなく、membersテーブルに所属しているmember_idという名称のインデックスという意味になります。インデックスの名称はインデックスを作成する際に指定します。eqはイコールを表すメソッドです。

上記例のように、インデックスを利用するにはIndexオブジェクトを使用します。

基本的なレンジスキャン

PerlShellKoherent::DBでは、ツリーを用いてインデックスを実装し、レンジスキャン（範囲検索）を可能とする予定です。レンジスキャンを用いるには、レンジスキャン用のメソッド郡を提供する必要があります。

Indexクラスの基本的なレンジスキャン用メソッドとして、次のようなものを考えています。

# <, lt
$result = $members->index('member_id')->lt(1234);

# >, gt
$result = $members->index('member_id')->gt(1234);

# <=, le
$result = $members->index('member_id')->le(1234);

# >=, ge
$result = $members->index('member_id')->ge(1234);

BETWEEN

SQLのBETWEENを実現するために、betweenメソッドも提供しようと思います。

Indexクラスのgeメソッドとleメソッドを組み合わせてBETWEENを実現することはできません。これは、ツリーに対するレンジスキャンの結果に対してさらにレンジスキャンを実行する際に、インデックスの活用が困難なためです。このため、between用のメソッドを提供予定です。。

betweenメソッドは次のような使い方を考えています。

-- member_idが1000番台のメンバーを取得
SELECT * FROM members WHERE member_id BETWEEN 1000 AND 1999;

$result = $members->index('member_id')->between(1000, 1999);

複雑なレンジスキャン

BETWEENの他にも、1000 <= member_id < 2000のように、lt、gt、le、geを組み合わせたレンジスキャンが考えられます。これらのすべての組み合わせを用意することもできますが、冗長であるため次のようなrangeメソッドに集約することを考えています。

$result = $members->index('member_id')->range(1000, 1, 2000, 0);

rangeメソッドの引数は順に、「最小値、最小値を範囲に含むか、最大値、最大値を範囲に含むか」となります。

なお、このようなrangeメソッドを一つ実装するだけで、上記の様々なメソッドはすべて内部でrangeメソッドを呼び出すだけで実装できます。例えば、betweenメソッドはrange($min, 1, $max, 1)に対応します。最小値や最大値がundefの場合は、下限、上限がないものとすることで、ltやgt等のメソッドも実装できます。eqメソッドすらも、range($value, 1, $value, 1)として実装できますが、eqメソッドは多様されることになると考えられるため、パフォーマンスも考慮して独自実装した方が良いかもしれません（大したオーバーヘッドではないようにも思いますが）。

挟まれないレンジスキャン

12歳未満および65歳以上のような、上記のrangeメソッドで扱えないような範囲を検索する場合を考えます（ageに対してもインデックスが作成されているものとします）。

SQLで表すと

-- 12歳未満および65歳のメンバーを取得
SELECT * FROM members WHERE age < 12 OR age >= 60;

となります。PerlShellKoherent::DBでは、インデックスを利用した検索結果をunionメソッドで結合することでこれを実現します。

$result = $members->index('age')->lt(12)->union(
        $members->index('age')->ge(60));

インデックスを用いた結合

結合時にインデックスを用いるにも、独自メソッドを利用する必要があります。

SELECT * FROM members
INNER JOIN groups ON members.group_id = groups.group_id;

上記のような内部結合は、インデックスを用いない場合、次のようなコードで表されていました。

$result = $members->inner_join($groups, sub{
    my ($outer_row, $inner_row) = @_;
    $outer_row->{'group_id'} == $inner_row->{'group_id'};
});

しかし、上記のように外部表と内部表のそれぞれの行に対する結合条件を記述したのではインデックスを活用できません。なぜなら、外部表の行に対して内部表全体から検索を行う際にインデックスを利用するからです。そのため、インデックスを利用して結合を行う場合、行と行ではなく、行と表（のインデックス）に対する検索ロジックを記述しなければなりません。例えば、次のようなコードが考えられます。

$result = $members->index_inner_join($groups->index('group_id'), sub{
    my ($outer_row, $inner_index) = @_;
    $inner_index->eq($outer_row->{'group_id'});
});

引数のサブルーチンが返す行のみが、結合結果として採用されます。

考察

インデックス使用時、未使用時でメソッドを区別することで、ユーザは検索の度にインデックスを意識しなければならならなくなります。このため、SQLのような手軽な（オプティマイザが自動で適切にインデックスを選択してくれるような）検索はできなくなってしまいます。

一方で、SQLであってもパフォーマンスを意識すればインデックスの使用を考慮する必要があり、適切なインデックス利用のためのSQLチューニングが行われることも多々あります。Oracleのヒント句のように、インデックスの使用方法を無理やり指定することすらあります。そのような事情を考えると、初めからインデックスの使用方法を指定する仕様の方が望ましいとも考えられます。

僕の私見では、インデックスの仕組みを学ぶ機会もないままにSQLのようなインデックスの利用が隠蔽された環境におかれると、インデックスが何を行っているのか、インデックスには何ができるのかを理解していないケースも多いのではないかと思います。インデックスの使用方法の明記を強制することで開発者の理解が進み、インデックスの効かないとんでもない検索クエリが記述されてしまうケースを減らすことができるのではないでしょうか。

まとめ

PerlShellKoherent::DBでは、インデックスを利用して検索を行うにはIndexクラスのメソッドを用いる仕様とします（whereメソッドの内部で自動的にインデックスを活用するような実装は仕様上困難です）。IndexオブジェクトはViewオブジェクトのindexメソッドで取得します。

通常の検索とインデックスを用いた検索を分離することで、ユーザは検索の度にインデックスの利用を意識しなければならなくなります。しかし、実際には一つ一つのSQLのパフォーマンスが検討されることも多いため、インデックスの使用方法を明記しなければならないことが、一概に悪いとは言えません。また個人的には、インデックスを意識した記述が強制されることによって、インデックスが有効でない的外れな検索クエリを書いてしまうケースも減らせるのではないかと思います。

SELECTのAPI(3) イテレータによるデータアクセス

Koherent_DB

これまで、テーブルからのデータの読み出しそのものに関しては触れませんでした。ここでは、どのようにテーブルからデータを読み出すかについて考えます。

テーブルから数百万件というような大量のデータを読み出す場合、すべてのデータをメモリ上に読み込んでからデータを返すとメモリが溢れてしまいます。このため、通常のRDBMSではイテレータを介して1行ずつデータを読み出せるようになっています。PerlShellKoherent::DBでもイテレータによってデータの読み出しを行うようにしたいと思います。

イテレータの利用

membersテーブルから全データを読み出すためのコードは、次のようなものを考えています。

my $iterator = $members->iterator;

while(my $row = $iterator->next){
    print "Family name: $row->{'family_name'}, First name: $row->{'first_name'}\n";
}

Viewオブジェクトのiteratorメソッドを用いてViewIteratorオブジェクトを得ます。ViewIteratorオブジェクトからはnextメソッドを用いて行を取り出します。最後の行を取り出した後にnextメソッドを呼び出すとundefを返すので、上記のようなwhileループですべての行を取り出すことができます。

iteratorメソッドはViewクラスのインスタンスメソッドであり、whereやorder_by、limit等の各種メソッドの戻り値はViewオブジェクトであるため、次のような使い方もできます。

my $iterator = $members->where(sub{
        $_[0]->{'age'} >= 20;
    })->order_by('family_name')->limit(1)->iterator;

while(my $row = $iterator->next){
    print "Family name: $row->{'family_name'}, First name: $row->{'first_name'}\n";
}

whereやorder_byなど、複数の条件を連ねた最後にiteratorを呼び出すことで、それらのすべての条件を適用したViewオブジェクトのデータにアクセスすることができます。

条件が評価されるタイミング

where等の条件が評価されるのはiteratorメソッドを呼び出したタイミングであるべきだと考えられます。

例えば、次のようなコードを考えてみます。

# 成人のメンバーだけを保持するView、$adultsを取得
my $adults = $members->where(sub{$_[0]->{'age'} >= 20});

# 成人のメンバーだけを取り出すイテレータを取得
my $iterator_before = $adults->iterator;

# membersテーブルにメンバーを1人追加
$members->insert($new_member);

# 再度、成人のメンバーだけを取り出すイテレータを取得
my $iterator_after = $adults->iterator;

もしも$adultsがwhereメソッドを用いて$adultsを作成した時点のデータを保持するのであれば、その後membersテーブルを更新してもその結果は$adultsには影響を及ぼしません。つまり、$iterator_beforeと$iterator_afterは同じ結果を返すことになります。

しかし、whereメソッドが呼び出された時点では条件は評価せずにiteratorメソッドが呼び出されて初めて条件が評価されるように実装すると、$adultsはまるでRDBMSにおけるビューのような存在になります。この場合、$iterator_afterから得られるデータは、$iterator_beforeから得られるデータに加えて$new_memberも返すようになります。このような実装を行うことで、検索のコードの再利用性を高めることができます。

実装は次のように行います。

  1. $adultsのViewIteratorオブジェクトは内部で$membersのViewIteratorオブジェクトを保持する。
  2. そのViewIteratorオブジェクトから得られた行に対してwhereで与えられたメソッドを適用し、その行が条件に適合するかどうかを判別する。

これは、whereメソッドが返すViewオブジェクトは条件判別専用のViewクラス（例えば、Viewクラスを継承した条件判別用のConditionedViewクラス）のインスタンスであるということになります。同様に、order_by用のViewクラス、limit用のViewクラスなどを用意し、それぞれの返すViewIteratorオブジェクトの挙動を変化させることでこれまでに述べたような挙動を実現できるでしょう。

まとめ

PerlShellKoherent::DBでは他のRDBMS同様、イテレータを介してデータアクセスを行うことにします。イテレータはViewオブジェクトのiteratorメソッドを用いて取得します。

where、limit等の各種メソッドの条件が評価されるのはそれらのメソッドが呼び出されたときではなく、それらのメソッドで返されたViewオブジェクトのiteratorメソッドが呼び出されたときとします。そうすることで、Viewオブジェクトをビューのように扱えるようになり、検索のコードの再利用性を高めることができると考えられます。

SELECTのAPI(2) 様々なSELECT文への対応

Koherent_DB

パッケージ越しにソートサブルーチンを渡してもうまく動作しないようなので、ORDER BYでは通常のサブルーチンを渡すように変更しました。
（参考）パッケージ越しのソートサブルーチンがうまく働かない - $koherent->diary
(2009.09.21)

APIを考えるにあたって、ここでは様々なパターンのSELECT文に対して、どのようなコードでそれを実現するかを考えてみます。

例で使用するテーブル

以下、次のようなテーブルがあるものとして話を進めます。

members: メンバーを格納したテーブル
member_id family_name first_name age sex group_id
1011 中田 祐輔 22 0 1
1234 山下 沙希 17 1 2
1999 宮川 崇 18 0 2
2112 石田 菜々 21 1 1
2345 山崎 千佳 19 1 1
groups: メンバーの所属するグループを格納したテーブル
group_id group_name
1 大学生
2 高校生
PerlShellKoherent::DBでのテーブルの取得

次のようにして、$membersと$groupsがTableオブジェクトを事前に保持しているものとします。また、検索結果（を保持するViewオブジェクト）はここで定義した$resultに格納されるものとします。

my $members = $database->table('members');
my $groups = $database->table('groups');

my $result;

全件検索

SELECT * FROM members;

$result = $members;

WHERE

SELECT * FROM members WHERE age < 20;

WHEREは、その行が条件に適合するかを判別して真／偽を返すサブルーチンを渡すことで実現しようと思います。whereメソッドの内部では1行ずつテーブル（Viewオブジェクト）の行をサブルーチンに渡し、適合したものだけを保持するViewオブジェクトを返します。

$result = $members->where(sub{
    my $row = shift;
    $row->{'age'} < 20;
});

# 省略形
$result = $members->where(sub{$_[0]->{'age'} < 20});

単純なSELECTリスト

SELECT family_name, first_name FROM members;

$result = $members->select('family_name', 'first_name');

複雑なSELECTリスト

SELECT family_name, first_name, age - 1 AS last_year_age,
       CASE WHEN sex = 0 THEN '男' ELSE '女' END AS sex_text
FROM members;

SELECTリスト中の式などの複雑な表現を再現するためには、単にSELECTするフィールド名を渡すのではなく、その式を表すサブルーチンと新しいフィールド名を格納した配列リファレンスを渡すようにします。

$result = $members->select('family_name', 'first_name',
    ['last_year_age', sub{$_[0]->{'age'} - 1}],
    ['sex_text', sub{$_[0]->{'sex'} == 0 ? '男' : '女'}]);

サブクエリ

-- 冗長なサブクエリだけど例なのでお気になさらず
SELECT * FROM (
    SELECT * FROM members WHERE sex = 1;
) WHERE age < 20;

メソッドがViewオブジェクトを返すため、メソッドを二つ連ねることでサブクエリを実現できます。

$result = $members->where(sub{$_[0]->{'sex'} == 1})
    ->where(sub{$_[0]->{'age'} < 20});

文字列によるソート

-- 昇順
SELECT * FROM members ORDER BY family_name;
-- 降順
SELECT * FROM members ORDER BY family_name DESC;

# 昇順
$result = $members->order_by('family_name');
# 降順
$result = $members->order_by('family_name', 1);

文字列以外によるソート

パッケージ越しにソートサブルーチンを渡してもうまく動作しないようなので、ORDER BYでは通常のサブルーチンを渡すように変更しました。
（参考）パッケージ越しのソートサブルーチンがうまく働かない - $koherent->diary
(2009.09.21)

SELECT * FROM members ORDER BY age;


Perlのsortを活用してソートを行おうと思うので、デフォルトのソート順以外を用いる場合はソートサブルーチンを渡す必要があります。

# Perlのソートサブルーチンを利用
$result = $members->order_by(sub{$a->{'age'} <=> $b->{'age'}});

order_byメソッドにはソート順を指定するためのサブルーチンを渡す必要があります。サブルーチンは、$row1の値の方が小さいときに-1（負の値）、$row1と$row2の値が等しいときに0、$row1の値の方が大きいときに1（正の値）を返すようにします。

# Perlのソートサブルーチンを利用
$result = $members->order_by(sub{
    my ($row1, $row2) = @_;
    $row1->{'age'} <=> $row2->{'age'}
});

# 省略形
$result = $members->order_by(sub{$_[0]->{'age'} <=> $_[1]->{'age'}});

LIMITとOFFSET

-- LIMITとOFFSETは標準SQLで定められていないので、MySQLにならう
SELECT * FROM members LIMIT 3;
SELECT * FROM members LIMIT 2, 3; -- LIMIT 3 OFFSET 2

$result = $members->limit(3);
$result = $members->limit(2, 3);

内部結合

SELECT * FROM members
INNER JOIN groups ON members.group_id = groups.group_id;

$result = $members->inner_join($groups, sub{
    my ($outer_row, $inner_row) = @_;
    $outer_row->{'group_id'} == $inner_row->{'group_id'};
});

# 省略形
$result = $members->inner_join($groups, sub{
    $_[0]->{'group_id'} == $_[1]->{'group_id'};
});

外部結合

SELECT * FROM members
LEFT OUTER JOIN groups ON members.group_id = groups.group_id;

$result = $members->outer_join($groups, sub{
    $_[0]->{'group_id'} == $_[1]->{'group_id'};
});

複雑なSELECT

SELECT * FROM (
    SELECT family_name, first_name, group_name,
           CASE WHEN sex = 0 THEN '男' ELSE '女' END AS sex_text
    FROM members
    INNER JOIN groups ON members.group_id = groups.group_id
    WHERE group_name = '大学生'
) WHERE sex_text = '女' ORDER BY first_name LIMIT 1, 1;

$result = $members->inner_join($groups, sub{
    $_[0]->{'group_id'} == $_[1]->{'group_id'}
        && $_[1]->{'group_name'} eq '大学生';
})->select('family_name', 'first_name', 'group_name',
    ['sex_text', sub{$_[0]->{'sex'} == 0 ? '男' : '女'}])
->where(sub{$_[0]->{'sex_text'} eq '女'})
->order_by('first_name')->limit(1, 1);

考察

Viewクラス、Tableクラスおよび各メソッドを実装すれば、上記のようなコードでSELECTを行えそうです。SQLと比べるとやや読みづらいように感じますが（慣れの問題？）、長さは同程度で書くことができそうです。それだけではSQLでないメリットがないように思うかもしれませんが、このようにプログラムの形で検索条件を記述することができれば、SQLでは難しかった再利用が容易になると考えられます。

例えば、アプリケーションの広範なテーブルに対して論理削除をdel_flagというフィールドで管理しているとすると、論理削除されていないデータだけを得るためのサブルーチンを次のように定義できます。

sub valid{
    my $table = shift;
    $table->where(sub{$_[0]->{'del_flag'} == 0);
}

上記のようなサブルーチンを用いることで、SELECTの度にdel_flag = 0の判定を行う必要もなくなります。SQLを用いると

SELECT * FROM members WHERE del_flag = 0;
SELECT * FROM groups WHERE del_flag = 0;
SELECT * FROM members WHERE age >= 20 AND del_flag = 0;

というような検索も、上記のサブルーチンを用いれば

my $valid_members = &valid($members);
my $valid_groups = &valid($groups);
my $valid_adults = &valid($members->where(sub{$_[0]->{'age'} >= 20));

というように書けます。このようにしてvalidサブルーチンで一元管理をしておけば、「del_flag = 2は一時的に論理削除から復活状態にあるものとする」というような仕様変更がされても、

sub valid{
    my $table = shift;
    $table->where(sub{$_[0]->{'del_flag'} == 0
        && sub{$_[0]->{'del_flag'} == 2);
}

と書き換えるだけで事足ります。SQLに条件をべた書きにしている場合には、数十、数百ものSQLを書き換えなければならないことでしょう*1。

ただし、上記のコードだけでは不完全な点があります。PerlShellKoherent::DBではオプティマイザが自動的にインデックスを活用してくれない仕様*2のため（というよりも、上記のようなwhereの書き方を許容すると、内部で判断して適切にインデックスを活用することが困難であるため）、インデックスを利用した検索は専用の別メソッドを介さなければなりません。

結合に関しても上記の方法ではインデックスなしのNested Loop Joinを行うしかないため、外部表M件、内部表N件の際にO(MN)になってしまいます。インデックスを利用した結合専用のメソッドも用意する必要があるでしょう。

まとめ

PerlShellKoherent::DBで各種SELECT文相当の検索を実現するためのAPI（およびその使い方）を考案しました。コードを書いてみたところ、やや読みづらいもののSQLとほぼ同程度の長さで検索条件を記述することが可能であることがわかりました。SQLではなくプログラムとして検索条件を指定することで、再利用性が高まると考えられます。

インデックスの利用に関しては、この記事で書いたメソッドとは別のメソッドを用意して独自に扱う必要があるため、追加検討が必要です。

*1:SQLでもPL/SQLなどを用いてがんばれば一元管理できるでしょうが、少なくとも僕の携わったプロジェクトではそのようなことはされていませんでした。なので、同じ修正を数十箇所に（ｒｙ

*2:実装の方針

パッケージ越しのソートサブルーチンがうまく働かない

Perl

Perlのソートサブルーチン（ソート定義サブルーチン）が次の「Case C: パッケージ越しのソートサブルーチンの利用」でうまく働いてくれません。

ソース

#!/usr/bin/perl
use strict;

my $by_num = sub{
    $a <=> $b;
};

############################################
# Case A: 通常のソートサブルーチンの利用
print 'Case A: ', join(', ', sort $by_num (3, 6, 2, 4, 5)), "\n";

############################################
# Case B: サブルーチンを介したソートサブルーチンの利用
sub sort_test_b{
    my $by_num = shift;
    print 'Case B: ', join(', ', sort $by_num (3, 6, 2, 4, 5)), "\n";
};

&sort_test_b($by_num);

############################################
# Case C: パッケージ越しのソートサブルーチンの利用
{
    package SortTest;

    sub sort_test_c{
        my $by_num = shift;
        print 'Case C: ', join(', ', sort $by_num (3, 6, 2, 4, 5)), "\n";
    };
}

SortTest::sort_test_c($by_num);

実行結果

Case A: 2, 3, 4, 5, 6
Case B: 2, 3, 4, 5, 6
Case C: 3, 6, 2, 4, 5

考察

$by_numのサブルーチンの中にprintを入れて検証してみましたが、どうやらCase Cでは$a、$bがundefになっているようです。なにかヒントはないかと思い、『初めてのPerl』を読み直してみました。

本当のことを話せば、sortの呼び出しの回りを取り囲んだプライベートなブロックの中で、Perlがlocal($a, $b)を実行したかのようになります。なぜなら、これらの変数は、実際にはレキシカル変数ではなく、グローバル変数だからです。（中略）ソートサブルーチンの中では、どこから来たかは気にせずに、素直に$aと$bを使いましょう。またさらに付け加えるならば、もしスコープのどこかにレキシカル変数$aまたは$bがあると、このサブルーチン定義はうまく動作しません。

初めてのPerl 第3版 p.259

どうやら、$a, $bはグローバル変数として動作しているようなので、パッケージを越えてしまうと正常に動作しないようです。

対策

グローバル変数ならば、下記のようにやればうまくいくのではないかと思い試してみました。

my $by_num_c = sub{
    $SortTest::a <=> $SortTest::b;
};

SortTest::sort_test_c($by_num_c);

結果は次の通り。

Case C: 2, 3, 4, 5, 6

見事にソートされています。やはり、$a, $bがパッケージに属しているためにうまく動作しなかったようです。

ただ、パッケージプレフィックスを指定するとうまく動くとはいえ、これではソートサブルーチンの汎用性を著しく損ねているため、対策と言えるかはわかりません。

まとめ

ソートサブルーチンの$a, $bはグローバル変数として扱われているため、パッケージ越しにソートサブルーチンを使うときには$a, $bという書き方では想定どおりに動作しないようです。ソートが実行されるパッケージのプレフィックスを指定して$a, $bを記述すれば動作するようです（ただし、パッケージごとにサブルーチンを書き換えないといけないため、汎用性が低くなります）。

より良い対策をご存知の方はいらっしゃいませんか？