SELECTのAPI(1) 概要
SQLと言えばまずはSELECT文です。ということで、最初に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 |
どのようにしてSELECTを行うか
SQLのSELECTは柔軟性に富んでおり、多様な操作を受け付けます。特に、結合やサブクエリをどのように扱うかということはよく考えなければなりません。PerlShellKoherent::DBのAPIでは、テーブルもクエリの結果もすべてKoherent::PerlShell::Tableクラスのインスタンスとして扱うことでこれを解決しようと思います。
RDBでは、SELECTにおけるインプットとアウトプットの両方を表として表すことが出来ます。例えば、
-- 成人のメンバーを取得 - クエリA SELECT * FROM members WHERE age >= 20;
というクエリのインプットは上記のmembersテーブルであり、アウトプットは下記のような表となります。
member_id | family_name | first_name | age | sex | group_id |
---|---|---|---|---|---|
1011 | 中田 | 祐輔 | 22 | 0 | 1 |
2112 | 石田 | 菜々 | 21 | 1 | 1 |
これは複雑な結合やサブクエリに関しても言えることです。次のようなサブクエリを用いたSELECT文の場合でも、テーブルをインプット/アウトプットとする2段階の問い合わせと考えられます(例なので冗長なサブクエリになっていますが、目を瞑って下さい)。
-- 男性のメンバーを取得 - クエリB SELECT * FROM ( SELECT member_id, family_name, first_name, CASE WHEN sex = 0 THEN '男' ELSE '女' END AS sex_text FROM members ) WHERE sex_text = '男';
この場合、1段階目のインプットはmembersテーブルでそのアウトプットは次のようになります。
member_id | family_name | first_name | sex_text |
---|---|---|---|
1011 | 中田 | 祐輔 | 男 |
1234 | 山下 | 沙希 | 女 |
1999 | 宮川 | 崇 | 男 |
2112 | 石田 | 菜々 | 女 |
2345 | 山崎 | 千佳 | 女 |
これがそのまま2段階目のインプットとなり、最終的なアウトプットは次のようになります。
member_id | family_name | first_name | sex_text |
---|---|---|---|
1011 | 中田 | 祐輔 | 男 |
1999 | 宮川 | 崇 | 男 |
このように表をインプット/アウトプットと考えれば、サブクエリを特別なものとして扱わなくても単純なSELECTの繰り返しと考えられます。
コードの例
PerlShellKoherent::DBで上記のようなSELECT文をどのように書くかを考えて見ます。
上記クエリAは、例えば次のようなコードで書けるでしょう。
# 成人のメンバーを取得 - クエリA相当 my $adults = $members->where(sub{ my $row = shift; $row->{'age'} < 20; });
このとき、$members、$adultsともにTableオブジェクトとなります。同様にクエリBは次のようなコードになります。
# 男性のメンバーを取得 - クエリB相当 # 1段階目 my $mid_result = $members->select( 'member_id', 'family_name', 'first_name', ['sex_text', sub{ my $row = shift; $row->{'sex'} == 0 ? '男' : '女'; }] ); # 2段階目 my $men = $mid_result->where(sub{ my $row = shift; $row->{'sex_text'} == '男'; });
上記のコードを一つにまとめて、次のように書くこともできます。
# 男性のメンバーを取得(一つにまとめた場合) - クエリB相当 my $men = $members->select( 'member_id', 'family_name', 'first_name', ['sex_text', sub{ my $row = shift; $row->{'sex'} == 0 ? '男' : '女'; }] )->where(sub{ my $row = shift; $row->{'sex_text'} == '男'; });
このようにSELECTの結果をTableオブジェクトとして扱うことで、サブクエリのような複雑な問い合わせに対しても独自にメソッドを用意せずに対応できそうです。
実際には、テーブルに対してはUPDATEやINSERT、DELETEなどを行うことができますが、SELECTの結果に対してはSELECTしか行うことができないため、これらを区別する必要があります。そのため、SELECTしか行えない表をKoherent::PerlShellDB::Viewクラス、テーブルはそれを継承したKoherent::PerlShellDB::Tableクラスとして扱おうと思います。
まとめ
SELECTのインプット/アウトプットは表と考えられるため、テーブルもSELECTの結果もKoherent::PerlShellDB::Viewクラスで扱います。更新が可能なテーブルには更新用のメソッドを用意する必要があるため、Viewクラスを継承したKoherent::PerlShellDB::Tableクラスとして扱います。