1 contributor
=encoding utf8
=head1 NAME
DBIx::Custom::Guide::Ja - DBIx::Customのガイドブック
=head1 ガイド
L<DBIx::Custom>はSQLの実行を簡単に行うためのクラスです。
L<DBIx::Class>やL<DBIx::Simple>と同じように
L<DBI>のラッパクラスになっています。L<DBIx::Class>よりも簡単に、
L<DBIx::Simple>よりもはるかに柔軟なことを行うことができます。
L<DBIx::Custom>はO/Rマッパーではありません。O/Rマッパーは
便利ですが、O/Rマッパのたくさんの文法を覚える必要があります。
また、O/Rマッパによって生成されたSQLは非効率なことがありますし、
複雑なSQLを生成することができないので、
生のSQLを実行しなければならない場合がたくさんあります。
L<DBIx::Custom>はO/Rマッパとは対照的な設計が行われています。
L<DBIx::Custom>の主な目的は、SQLを尊重しつつ、L<DBI>だけでは
とてもめんどうな作業を簡単にすることです。もしSQLについて
多くの知識を持っているならば、L<DBIx::Custom>でそのまま
活用することができます。
L<DBIx::Custom>の仕組みを少しだけ説明しておきます。
L<DBIx::Custom>では、タグと呼ばれるものを
SQLの中に埋め込むことができます。
select * from book where {= title} and {=author};
{}で囲まれた部分がタグです。このSQLは実際に実行されるときには
次のようにプレースホルダに展開されます。
select * from book where title = ? and author = ?;
これらの展開にはどのような意味があるのでしょうかと質問
されるかもしれません。この簡単な仕組みの上に
便利な機能が実装されます。それは以下のようなものです。
=over 4
=item 1. プレースホルダにバインドする値をハッシュリファレンスで指定
L<DBI>を使うのであればプレースホルダにバインドする値は配列
で指定する必要があります。
$sth->execute(@bind);
L<DBIx::Custom>を利用するのであればハッシュリファレンスで指定すること
できます。
my $param = {title => 'Perl', author => 'Ken'};
$dbi->execute($sql, $param);
=item 2. 値のフィルタリング
L<DBIx::Custom>はフィルタリングの機能を提供します。
たとえば、日付の列は、Perlで扱うときにはC<Time::Piece>などの日付オブジェクト
で扱い、データベースに格納するときはデータベースの日付型に変換したい
と思うのではないでしょうか。またデータベースから取り出すときは
データベースの日付型から日付オブジェクトに変換したと思うのでは
ないでしょうか。
このようなときはフィルタ機能を使うことができます。
まずフィルタを登録します。
$dbi->register_filter(
tp_to_date => sub {
...
},
date_to_tp => sub {
...
}
);
次にテーブルの各列にこのフィルタを適用します。
$dbi->apply_filter('book',
'issue_date' => {out => 'tp_to_date', in => 'date_to_tp'}
);
outはPerlからデータベースに保存する方向、inはデータベースからPerlに取得する方向です。
多くのメソッドで自動的にこのフィルタが有効になります。
$dbi->insert(table => 'book', param => {issue_date => $tp});
=item 3. 選択的な検索条件
L<DBI>では選択的に検索条件を作成することは難しいです。
たとえば、検索条件にtitleとauthorが指定された場合は次のSQLを
select * from book where title = ? and author = ?;
titleだけの場合は次のSQLを
select * from book where title = ?;
authorだけの場合は次のSQLを実行した場合を考えましょう。
select * from book where author = ?;
これはとても大変な作業なので、通常はL<SQL::Abstract>を動的に生成してくれる
モジュールを利用することになります。
L<DBIx::Custom>はさらに簡単で便利な方法を用意しています。
# Whereオブジェクト
my $where = $dbi->where;
# 検索条件
$where->clause(
['and', '{= title}', {'= author'}]
);
# 必要な列を自動的に選択するための設定
$where->param({title => 'Perl'});
# SQLへのWhere句の埋め込み
my $sql = "select * from book $where";
詳しい説明は後ほど行いますが、上記のように記述すれば、
L<DBIx::Custom>では選択的な検索条件を持つWhere句を生成することができます。
検索条件が入れ子になった構造やorについても対応しています。
=item 4. 挿入、更新、削除、選択を行うためのメソッド
L<DBIx::Custom>では挿入、更新、削除、選択を行うための
メソッドを提供しています。
C<insert()>, C<update()>, C<delete()>,C<select()>などがあります。
my $param = {title => 'Perl', author => 'Ken'};
$dbi->insert(table => 'book', param => $param);
=item 5. テーブルのためのメソッドの登録
テーブルのためにメソッドを登録することができます。
$dbi->table('book')->method(
list => sub {
...
},
something => sub {
...
}
);
メソッドの利用です。
$dbi->table('book')->list;
多くのO/Rマッパではテーブルのためのクラスを作成する必要がありますが、
L<DBIx::Custom>では簡単です。
=back
L<DBIx::Custom>はとても便利です。
興味をもたれた方は、続きをご覧になってみてください。
=head2 1. データベースへの接続
L<DBIx::Custom>を読み込みます。
use DBIx::Custom;
データベースに接続するにはC<connect()>メソッドを使用します。
戻り値はL<DBIx::Custom>オブジェクトです。
my $dbi = DBIx::Custom->connect(
data_source => "dbi:mysql:database=bookstore",
user => 'ken',
password => '!LFKD%$&',
dbi_options => {mysql_enable_utf8 => 1}
);
C<data_source>はデータベースシステムに応じたものである必要があります。
以下はデータソースのサンプルです。
B<MySQL>
"dbi:mysql:database=$database"
"dbi:mysql:database=$database;host=$hostname;port=$port"
B<SQLite>
"dbi:SQLite:dbname=$database"
"dbi:SQLite:dbname=:memory:"
B<PostgreSQL>
"dbi:Pg:dbname=$dbname"
B<Oracle>
"dbi:Oracle:$dbname"
"dbi:Oracle:host=$host;sid=$sid"
B<ODBC(Microsoft Access)>
"dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"
B<ODBC(SQL Server)>
"dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"
認証が必要な場合は、C<user>とC<password>を指定できます。
L<DBIx::Custom>はL<DBI>のラッパークラスです。
L<DBI>のデータベースハンドルは取得するにあｈC<dbh()>を使用します。
my $dbh = $dbi->dbh;
L<DBIx::Custom>ではデータベースハンドル属性にはデフォルトで次のものが設定されます。
$dbi->dbh->{RaiseError} = 1;
$dbi->dbh->{PrintError} = 0;
$dbi->dbh->{AutoCommit} = 1;
致命的なエラーが起こるとプログラムは終了します。
SQLが実行されると自動的にコミットされます。
=head2 2. 挿入、更新、削除、選択のためのメソッド
下記のメソッドがあります。
=head3 行の挿入 C<insert()>
データベースに行を挿入するにはC<insert()>を使用します。
$dbi->insert(table => 'book',
param => {title => 'Perl', author => 'Ken'});
C<table>はテーブル名、C<param>は挿入する行のデータです。
次のSQLが実行されます。
insert into (title, author) values (?, ?);
=head3 データの更新 C<update()>
データベースの行を更新するには、C<update()>を使用します。
$dbi->update(table => 'book',
param => {title => 'Perl', author => 'Ken'},
where => {id => 5});
C<table>はテーブル名、C<param>は更新データ、C<where>は
条件です。
次のSQLが実行されます。
update book set title = ?, author = ?;
安全のためC<where>のないupdate()を実効することはできません。
もしすべての行を更新したい場合は
C<update_all()>を使用してください。
$dbi->update_all(table => 'book',
param => {title => 'Perl', author => 'Ken'});
=head3 データの削除 C<delete()>
データベースの行を1件削除するには、C<delete()>を使用します。
$dbi->delete(table => 'book',
where => {author => 'Ken'});
C<table>はテーブル名、C<where>は条件です。
次のSQLが実行されます。
delete from book where id = ?;
安全のためC<where>のないC<delete()>を実効することはできません。
もしすべての行を削除したい場合は
C<delete_all()>を使用してください。
$dbi->delete_all(table => 'book');
=head3 データの選択 C<select()>
行を選択するにはC<select()>を使用します。
my $result = $dbi->select(table => 'book');
次のSQLが実行されます。
select * from book;
戻り値はL<DBIx::Custom::Result>
オブジェクトです。行をフェッチするにはC<fetch()>を使用します。
while (my $row = $result->fetch) {
my $title = $row->[0];
my $author = $row->[1];
}
L<DBIx::Custom::Result>についてはL<3. 行のフェッチ/"3. 行のフェッチ">を見てください。
サンプルを続けます。
my $result = $dbi->select(
table => 'book',
column => ['author', 'title'],
where => {author => 'Ken'}
);
C<column>は列名、C<where>は条件です。
次のSQLが実行されます。
select author, title from book where author = ?;
次のサンプルです。
my $result = $dbi->select(
table => 'book',
where => {book.name => 'Perl'},
relation => {'book.id' => 'rental.book_id'}
);
C<relation>テーブル間の関係です。これは内部結合です。
次のSQLが実行されます。
bookテーブルのid列とrentalテーブルのbook_idが関連付けられます。
次のSQLが実行されます。
select * from book, rental where book.name = ? and book.id = rental.book_id;
次のサンプルです。
my $result = $dbi->select(
table => 'book',
where => {author => 'Ken'},
append => 'for update',
);
C<append>はSQLの末尾に追加される文字列です。
次のSQLが実行されます。
select * book where author = ? for update;
またC<append>は、C<select>だけでなくC<insert()>、C<update()>、C<update_all()>
C<delete()>、C<delete_all()>、C<select()>で使用することもできます。
C<column>とC<table>を使用する代わりに、C<selection>を使用することも
できます。列名とテーブル名をまとめて指定する場合に利用します。
my $selection = <<"EOS";
title, author, company_name
from book inner join company on book.company_id = company.id
EOS
$dbi->select(selection => $selection);
C<selection>においてはwhere句を利用できないということに注意してください。
"inner join"などの句を利用してください。
=head3 SQLの実行 C<execute()>
SQLを実行するにはC<execute()>を使用します。
$dbi->execute("select * from book;");
タグを処理してSQLを実行します。
$dbi->execute(
"select * from book {= title} and {= author};"
param => {title => 'Perl', author => 'Ken'}
);
次のSQLが実行されます。
select * from book title = ? and author = ?;
プレースホルダにtitleとauthorの値が埋め込まれます。
タグについてはL<5. タグ/"5. タグ">を見てください。
またC<execute()>のSQLの末尾にはセミコロンを置く必要はありません。
$dbi->execute('select * from book');
=head3 プライマリーキーを利用した行の更新 C<update_at()>
プライマリーを使用して行を更新するにはC<update_at()>を使用します。
$dbi->update_at(
table => 'book', primary_key => ['id'],
where => ['123'], param => {name => 'Ken'}
);
この例ではidの列が123の行が更新されます。C<where>には、配列の
リファレンスを渡す必要があることに注意してください。
なおC<param>にプライマリーキーが含まれていた場合は、そのキーが削除さされます。
=head3 プライマリーキーを利用した行の削除 C<delete_at()>
プライマリーを使用して行を削除するにはC<delete_at()>を使用します。
$dbi->delete_at(table => 'book', primary_key => ['id'], where => ['123']);
この例ではidの列が123の行が削除されます。C<where>には、配列の
リファレンスを渡す必要があることに注意してください。
また下のような記述方法も許されています。
$dbi->delete_at(table => 'book', primary_key => ['id'], param => {id => '123'});
=head3 プライマリーキーを利用した行の選択 C<select_at()>
プライマリーを使用して行を選択するにはC<select_at()>を使用します。
$dbi->select_at(table => 'book', primary_key => ['id'], where => ['123']);
この例ではidの列が123の行が選択されます。C<where>には、配列の
リファレンスを渡す必要があることに注意してください。
また下のような記述方法も許されています。
$dbi->select_at(table => 'book', primary_key => ['id'], param => {id => '123'});
=head2 3. 行のフェッチ
C<select()>メソッドの戻り値はL<DBIx::Custom::Result>オブジェクトです。
行をフェッチするためのさまざまなメソッドがあります。
=head3 1行づつフェッチ(配列) C<fetch()>
一行フェッチして配列のリファレンスに格納するにはC<fetch()>を使用します。
my $row = $result->fetch;
以下のようにすべての行を取得することができます。
while (my $row = $result->fetch) {
my $title = $row->[0];
my $author = $row->[1];
}
=head3 最初の行だけフェッチ(配列) C<fetch_first()>
一行だけフェッチして配列のリファレンスに格納するにはC<fetch_first()>
を使用します。
my $row = $result->fetch_first;
ステートメントハンドルのC<finish()>が実行される
ので残りの行をフェッチできません。
=head3 複数行を順にフェッチ(配列) C<fetch_multi()>
複数行をフェッチして配列のリファレンスを要素に持つ
配列のリファレンスに格納するにはC<fetch_multi()>を使用します。
while (my $rows = $result->fetch_multi(2)) {
my $title0 = $rows->[0][0];
my $author0 = $rows->[0][1];
my $title1 = $rows->[1][0];
my $author1 = $rows->[1][1];
}
取り出したい行数を引数に指定します。
次のようなデータを取得できます。
[
['Perl', 'Ken'],
['Ruby', 'Mark']
]
=head3 すべての行をフェッチ(配列) C<fetch_all>
すべての行をフェッチして配列のリファレンスを要素に持つ
配列のリファレンスに格納するにはC<fetch_all()>を使用します。
my $rows = $result->fetch_all;
すべての行を格納した次のようなデータを取得できます。
[
['Perl', 'Ken'],
['Ruby', 'Mark']
]
=head3 1行づつフェッチ(ハッシュ) C<fetch_hash()>
一行フェッチしてハッシュのリファレンスに格納するにはC<fetch_hash()>を使用します。
while (my $row = $result->fetch_hash) {
my $title = $row->{title};
my $author = $row->{author};
}
=head3 最初の行だけフェッチ(ハッシュ) C<fetch_hash_first()>
一行だけフェッチしてハッシュのリファレンスに格納するには
C<fetch_hash_first()>を使用します。
my $row = $result->fetch_hash_first;
ステートメントハンドルのC<finish()>が実行される
ので残りの行をフェッチできません。
=head3 複数行をフェッチ(ハッシュ) C<fetch_hash_multi()>
複数行をフェッチしてハッシュのリファレンスを要素に持つ
配列のリファレンスに格納するにはC<fetch_hash_multi()>
を使用します。
while (my $rows = $result->fetch_hash_multi(5)) {
my $title0 = $rows->[0]{title};
my $author0 = $rows->[0]{author};
my $title1 = $rows->[1]{title};
my $author1 = $rows->[1]{author};
}
引数には取り出したい行数を指定します。
次のようなデータを取得できます。
[
{title => 'Perl', author => 'Ken'},
{title => 'Ruby', author => 'Mark'}
]
=head3 すべての行をフェッチ(ハッシュ) C<fetch_hash_all()>
すべての行をフェッチしてハッシュのリファレンスを要素に持つ
配列のリファレンスに格納するにはC<fetch_hash_all()>
を使用します。
my $rows = $result->fetch_hash_all;
次のようなデータを取得できます。
[
{title => 'Perl', author => 'Ken'},
{title => 'Ruby', author => 'Mark'}
]
=head3 ステートメントハンドル C<sth()>
ステートメントハンドル取得したい場合は
<sth()>を使用します。
my $sth = $result->sth;
=head2 4. フィルタリング
L<DBIx::Custom>は値のフィルタリング機能を提供します。
たとえば、データをデータベースに登録するときは
L<Time::Piece>オブジェクトからデータベースの日付のフォーマットに、
データベースからデータを取得するときは、
データベースの日付のフォーマットからL<Time::Piece>オブジェクト
に変換を行いたいと思うことでしょう。
=head3 フィルタの登録 C<register_filter()>
フィルタを登録するにはC<register_filter()>を使用します。
$dbi->register_filter(
# Time::Piece object to DATE format
tp_to_date => sub {
my $date = shift;
return '0000-00-00' unless $tp;
return $tp->strftime('%Y-%m-%d');
},
# DATE to Time::Piece object
date_to_tp => sub {
my $date = shift;
return if $date eq '0000-00-00';
return Time::Piece->strptime($date, '%Y-%m-%d');
},
);
登録したフィルタはC<apply_filter()>などで利用することができます。
=head3 フィルタの適用 C<apply_filter()>
作成したフィルタを適用するには、C<apply_filter()>を使用します。
$dbi->apply_filter('book',
issue_date => {out => 'tp_to_date', in => 'date_to_tp'},
first_issue_date => {out => 'tp_to_date', in => 'date_to_tp'}
);
第一引数はテーブル名です。第1引数より後の引数は、列名とフィルタルールのペアを記述します。
フィルタルールのoutには、データベースにデータを送信するときに適用するフィルタを、
フィルタルールのinには、データベースからデータを取得するときに適用するフィルタを
記述します。
フィルタとしてコードリファレンスを
指定することもできます。
issue_date => {out => sub { ... }, in => sub { ... }}
適用されたフィルタはC<insert()>、C<update()>、C<update_all()>、C<delete()>、
C<delete_all()>、C<select()>で有効になります。
my $tp = Time::Piece->strptime('2010/10/14', '%Y/%m/%d');
my $result = $dbi->select(table => 'book', where => {issu_date => $tp});
データベースにデータが送信されるときに、L<Time::Piece>オブジェクトは
データベースの日付のフォーマット「2010-10-14」に変換されます。
データをフェッチするときには、データベースの日付のフォーマットは
L<Time::Piece>オブジェクトに変換されます。
my $row = $resutl->fetch_hash_first;
my $tp = $row->{issue_date};
テーブル名を含む列名を使用することもできます。
$dbi->select(
table => 'book',
where => {'book.title' => 'Perl', 'book.author' => 'Ken'}
);
フェッチを行う場合に"TABLE__COLUMN"という名前を使用した場合もフィルタは
有効になります。
my $result = $dbi->execute(
"select issue_date as book__issue_date from book");
C<in>フィルタの後に実行されるC<end>フィルタを適用することもできます。
$dbi->apply_filter('book',
issue_date => {out => 'tp_to_date', in => 'date_to_tp',
end => 'tp_to_displaydate'},
);
=head3 個別のフィルタ C<filter>
個別にフィルタを適用することもできます。
個別のフィルタはC<apply_filter()>で適用したフィルタを上書きます。
データを送信する場合に個別のフィルタを適用するには、C<filter>オプションを使用します。
このオプションはC<insert()>、C<update()>、
C<update_all()>、C<delete()>、C<delete_all()>、C<select()>、C<execute()>
で使用することができます。
$dbi->insert(
table => 'book',
param => {issue_date => $tp, first_issue_date => $tp},
filter => {issue_date => 'tp_to_date', first_issue_date => 'tp_to_date'}
);
C<execute()>の例を示します。
my $sql = <<"EOS";
select YEAR(issue_date) as issue_year
from book
where YEAR(issue_date) = {? issue_year}
EOS
my $result = $dbi->execute(
$sql,
param => {issue_year => '2010'},
filter => {issue_year => 'tp_to_year'}
);
行をフェッチするときにも個別のフィルタを適用することができます。
C<DBIx::Custom::Result>のC<filter()>を使用します。
$result->filter(issue_year => 'year_to_tp');
=head3 最後のフィルタリング : C<end_filter()>
最後にもうひとつフィルタを追加することができます。
最終的な出力を作成する場合に便利です。
最後のフィルタを登録するにはC<end_filter()>を使用します。
$result->end_filter(issue_date => sub {
my $tp = shift;
return '' unless $tp;
return $tp->strftime('%Y/%m/%d %h:%m:%s (%a)');
});
この例ではL<Time::Piece>オブジェクトを読みやすい書式に変換しています。
=head3 フィルタの適用の自動化 C<each_column()>
日付型の列は自動的にフィルタを適用できると便利です。
列のすべての情報を処理するためのC<each_column()>を利用することができます。
$dbi->each_column(
sub {
my ($self, $table, $column, $info) = @_;
my $type = $info->{TYPE_NAME};
my $filter = $type eq 'DATE' ? {out => 'tp_to_date', in => 'date_to_tp'}
: $type eq 'DATETIME' ? {out => 'tp_to_datetime', in => 'datetime_to_tp'}
: undef;
$self->apply_filter($table, $column, $filter)
if $filter;
}
);
each_columnはコールバックを受け取ります。コールバックの引数は
L<DBIx::Custom>オブジェクト、テーブル名、列名、列の情報です。
列の型名の情報をもとに自動的に、フィルタを適用しています。
=head2 5. タグ
=head3 タグの基本
SQLの中にタグを埋め込むことができます。
select * from book where {= title} and {like author};
{= title}と{like author}の部分がタグです。タグは次のような形式
を持ちます。
{タグ名 引数1 引数2 ...}
タグはC<{>で始まり、C<}>で終わります。最初のC<{>とタグ名の間
には空白を挿入しないよう注意してください。
C<{>とC<}>は予約語になっています。
もし利用したい場合は\でエスケープを行う必要があります。
select from book \\{ ... \\}
C<\>自体がPerlのエスケープ文字ですので、
C<\>は二つ必要になります。
タグはSQLが実行される前に展開されます。
select * from book where title = ? and author like ?;
タグを含むSQLを実行するにはC<execute()>を使用します。
my $sql = "select * from book where {= author} and {like title};"
$dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'});
C<param>オプションを使って、プレースホルダに埋め込みたい値を
ハッシュリファレンスで指定することができます。
C<execute()>においてもC<filter>を指定することができます。
$dbi->execute($sql, param => {title => 'Perl', author => '%Ken%'}
filter => {title => 'to_something');
C<execute>ではC<apply_filter()>で適用されたフィルタ
は有効ではないということに注意してください。
C<apply_filter()>で適用されたフィルタを有効にするには、
C<table>タグを利用します。
my $sql = "select * from {table book} where {= author} and {like title};"
=head3 タグ一覧
=head4 C<table>
{table NAME} -> NAME
これはSQLの中でテーブル名を指定する場合に利用します。
テーブル名を指定することによって、C<apply_filter()>
によるフィルタリングが有効になります。
=head4 C<?>
{? NAME} -> ?
=head4 C<=>
{= NAME} -> NAME = ?
=head4 C<E<lt>E<gt>>
{<> NAME} -> NAME <> ?
=head4 C<E<lt>>
{< NAME} -> NAME < ?
=head4 C<E<gt>>
{> NAME} -> NAME > ?
=head4 C<E<gt>=>
{>= NAME} -> NAME >= ?
=head4 C<E<lt>=>
{<= NAME} -> NAME <= ?
=head4 C<like>
{like NAME} -> NAME like ?
=head4 C<in>
{in NAME COUNT} -> NAME in [?, ?, ..]
=head4 C<insert_param>
{insert_param NAME1 NAME2} -> (NAME1, NAME2) values (?, ?)
=head4 C<update_param>
{update_param NAME1 NAME2} -> set NAME1 = ?, NAME2 = ?
=head3 同名の列の扱い
同名の列を含むタグがある場合でも大丈夫です。
二つの日付で比較しなければならない場合を
考えて見ましょう。
my $sql = "select * from table where {> date} and {< date};";
このような場合はパラメータの値を配列のリファレンスで指定します。
my $dbi->execute($sql, param => {date => ['2010-10-01', '2012-02-10']});
=head3 タグの登録 C<register_tag()>
独自のタグを登録することができます。
タグを追加するにはC<register_tag()>を使用します。
$dbi->register_tag(
'=' => sub {
my $column = shift;
return ["$column = ?", [$column]];
}
);
ここではデフォルトのC<=>タグがどのように実装されているかを示しています。
タグの形式は次のようになっています。
{タグ名 引数1 引数2 ...}
C<=>タグの場合は
{= title}
という形式ですから、サブルーチンにはtitleというひとつの列名がわたってきます。
サブルーチンの戻り値には次の形式の配列のリファレンスを返す必要があります。
[
展開後の文字列,
[プレースホルダに埋め込みに利用する列名1, 列名2, ...]
]
一つ目の要素は展開後の文字列です。この例では
'title = ?'
を返す必要があります。
二つ目の要素はプレースホルダに埋め込みに利用する列名を含む配列の
リファレンスです。今回の例では
['title']
を返す必要があります。複数のプレースホルダを含む場合は、この部分が
複数になります。
上記を合わせると
['title = ?', ['title']]
を返す必要があるということです。
タグの実装の他のサンプルはL<DBIx::Custom::Tag>のソースコード
をご覧になってみてください。
=head2 6. Where句の動的な生成
=head3 Where句の動的な生成 where()
複数の検索条件を指定して、検索を行いたい場合があります。
次の3つのケースのwhere句を考えてみましょう。
titleの値だけで検索したい場合
where {= title}
authorの値だけで検索したい場合
where {= author}
titleとauthorの両方の値で検索したい場合
where {= title} and {=author}
L<DBIx::Custom>では動的なWhere句の生成をサポートしています。
まずC<where()>でL<DBIx::Custom::Where>オブジェクトを生成します。
my $where = $dbi->where;
次にC<clause()>を使用してwhere句を記述します。
$where->clause(
['and', '{= title'}, '{= author}']
);
clauseの指定方法は次のようになります。
['or' あるいは 'and', タグ1, タグ2, タグ3]
第一引数にはorあるいはandを指定します。第二引数以降には
検索条件をタグを使って記述します。
C<clause>の指定は入れ子にすることもでき、さらに複雑な条件
を記述することもできます。
['and',
'{= title}',
['or', '{= author}', '{like date}']
]
これは "{=title} and ( {=author} or {like date} )" 意味しています。
C<clause>を設定した後にC<param>にパラメータを指定します。
$where->param({title => 'Perl'});
この例ではtitleだけがパラメータに含まれています。
この後C<to_string()>を実行すると$paramに含まれるパラメータを満たす
where句を生成することができます。
my $where_clause = $where->to_string;
パラメータはtitleだけですので、次のようなwhere句が生成されます。
where {= title}
またL<DBIx::Custom>は文字列の評価をオーバーロードして、C<to_string()>
を呼び出すようにしていますので、次のようにしてwhere句を生成することも
できます。
my $where_clause = "$where";
これはSQLの中にwhere句を埋め込むときにとても役立つ機能です。
=head3 同一の列名を含む場合
タグの中に同一の名前を持つものが存在した場合でも動的に
where句を作成することができます。
たとえば、パラメータとして開始日付と終了日付を受け取ったことを
考えてみてください。
my $param = {start_date => '2010-11-15', end_date => '2011-11-21'};
この場合はパラメータの値を配列のリファレンスにしてください。
my $p = {date => ['2010-11-15', '2011-11-21']};
同名の列を含むタグに順番に埋め込むことができます。
$where->clause(
['and', '{> date}', '{< date}']
);
$where->param($p);
また開始日付が存在しない場合は次のようなデータを作成します。
my $p = {date => [$dbi->not_exists, '2011-11-21']};
C<not_exists()>でDBIx::Custom::NotExistsオブジェクトを
取得できます。これは対応する値が存在しないことを示すためのものです。
また終了日付が存在しない場合は次のようなデータを作成します。
my $p = {date => ['2010-11-15']};
どちらも存在しない場合は次のようなデータを作成します。
my $p = {date => []};
少し難しいので一番簡単に作成できるロジックを示しておきます。
my @date;
push @date, exists $param->{start_date} ? $param->{start_date}
: $dbi->not_exists;
push @date, $param->{end_date} if exists $param->{end_date};
my $p = {date => \@date};
=head3 C<select()>との連携
L<DBIx::Custom::Where>オブジェクトは
C<select()>のC<where>に直接渡すことが
できます。
my $where = $dbi->where;
$where->clause(...);
$where->param($param);
my $result = $dbi->select(table => 'book', where => $where);
あるいはC<update()>、C<delete()>のwhereに指定することも可能です。
=head3 C<execute()>との連携
C<execute()>との連携です。SQLを作成するときに埋め込むことができます。
my $where = $dbi->where;
$where->clause(...);
$where->param($param);
my $sql = <<"EOS";
select * from book;
$where
EOS
$dbi->execute($sql, param => $param);
=head2 7. モデル
=head3 モデル
ソースコードの見通しをよくするために、
L<DBIx::Custom::Model>を継承してモデルを作成することができます。
まず最初にモデルの元になるクラスを<DBIx::Custom::Model>
を継承して作成します。
package MyModel;
use base 'DBIx::Custom::Model';
次に個々のモデルクラスを作成します。
MyModel::book
package MyModel::book;
use base 'MyModel';
sub insert { ... }
sub list { ... }
MyModel::company
package MyModel::company;
use base 'MyModel';
sub insert { ... }
sub list { ... }
このように作成したモジュールを次のように配置してください。
MyModel.pm
MyModel / book.pm
/ company.pm
このように作成したモデルはC<include_model()>で取り込むことができます。
$dbi->include_model('MyModel');
第一引数は、モデルの名前空間になります。
モデルは次のように利用することができます。
my $result = $dbi->model('book')->list;
モデルではテーブル名を指定することなしに
C<insert()>, C<update()>, C<update_all()>,
C<delete()>, C<delete_all()>, C<select()>などのメソッドを
利用できます。
$dbi->model('book')->insert(param => $param);
またモデルクラスでC<primary_key>の設定がなされていれば、
プライマリキーを指定することなしに
C<update_at()>, C<delete_at()>, C<select_at()>のメソッドを
利用できます。
$dbi->model('book')->delete_at(where => 123);
モデルはL<DBIx::Custom::Model>です。
必要であれば、C<table()>でテーブル名を取得することができます。
my $table = $model->table;
L<DBIx::Custom>オブジェクトを取得することもできます。
my $dbi = $model->dbi;
L<DBIx::Custom>とL<DBI>のすべてのメソッドを呼び出すこともできます。
# DBIx::Custom method
$model->execute($sql);
# DBI method
$model->begin_work;
$model->commit;
すべてのモデル名を取得したい場合はC<models()>のキーを取得してください。
my @models = keys %{$self->models};
モデルにはプライマリーキーを設定することもできます。
$model->primary_key(['id', 'number_id']);
ここで設定したプライマリーキーはC<update_at()>, C<delete_at()>,
C<select_at()>で利用されます。
C<filter>でC<apply_filter()>で適用されるフィルタを定義しておくこともできます。
$model->filter({
title => {out => ..., in => ..., end => ...},
author => {out => ..., in => ..., end => ...}
});
このフィルタはC<include_model()>を呼び出したときに自動的に適用されます。
モデルには列名を設定することもできます。
$model->columns(['id', 'number_id']);
列名はC<setup_model()>で自動的に設定することができます。
このメソッドはC<include_model()>の後で呼び出してください。
$dbi->setup_model;
モデルにはリレーションを設定することもできます。
$model->relation({'book.company_id' => 'company.id'});
ここで設定したリレーションはC<select()>, C<select_at()>で利用されます。
=head2 クラス名、モデル名、テーブル名
クラス名とモデル名とテーブル名の関係について書いておきます。
通常はクラス名がモデル名に利用され、テーブル名にはモデル名が利用されます。
クラス名 モデル名 テーブル名
book (クラス名) -> book (モデル名) -> book
モデル名を変更することもできます。
package MyModel::book;
use base 'MyModel';
__PACAKGE__->attr(name => 'book_model');
クラス名 モデル名 テーブル名
book book_model (モデル名) -> book_model
モデル名というのは、L<DBIx::Custom>のL<model()>で利用される名前です。
$dbi->model('book_model');
テーブル名を変更することもできます。
package MyModel::book;
use base 'MyModel';
__PACAKGE__->attr(table => 'book_table');
クラス名 モデル名 テーブル名
book (クラス名) -> book book_table
テーブル名というのは、実際にアクセスされるテーブルです。
$dbi->model('book')->insert(...); # book_tableにアクセス
=head2 列名の自動生成 : column_clause()
列名の節を自動生成するにはC<column_clause()>を使用します。
C<table>とC<columns>の値が利用されます。
my $column_clause = $model->column_clause;
C<table>の値が'book'、C<column>の値が['id', 'name']で
あった場合は次のような列名の節が生成されます。
book.id as id, book.name as name
このように列名の節を生成するのは、列名のあいまいさをなくすためです。
不必要な列がある場合はC<remove>オプションで指定することができます。
my $column_clause = $model->column_clause(remove => ['id']);
追加したい列がある場合は、C<add>オプションで追加することができます。
my $column_clause = $model->column_clause(add => ['company.id as company__id']);
=head2 モデルのサンプル
モデルのサンプルです。
package MyDBI;
use base 'DBIx::Custom';
sub connect {
my $self = shift->SUPER::connect(@_);
$self->include_model(
MyModel => [
'book',
'company'
]
);
}
package MyModel::book;
use base 'DBIx::Custom::Model';
__PACKAGE__->attr('primary_key' => sub { ['id'] };
sub insert { ... }
sub list { ... }
package MyModel::company;
use base 'DBIx::Custom::Model';
__PACKAGE__->attr('primary_key' => sub { ['id'] };
sub insert { ... }
sub list { ... }
=head2 8. パフォーマンスの改善
=head3 クエリの作成
パフォーマンスが得られない場合はC<query>オプションを使って
クエリを作成してみてください。
my $params = [
{title => 'Perl', author => 'Ken'},
{title => 'Good day', author => 'Tom'}
]
my $query = $dbi->insert(table => 'book', param => $params->[0], query => 1);
戻り値はL<DBIx::Custom::Query>オブジェクトです。
作成したクエリはC<execute()>で実行することができます。
foreach my $param (@$params) {
$dbi->execute($query, $param);
}
ステートメントハンドルが再利用されるので、パフォーマンスが
改善されます。
C<query>オプションはC<insert()>, C<update()>, C<update_all()>,
C<delete()>, C<delete_all()>で利用することができます.
クエリを作成するメソッドに渡すパラメータと
C<execute()>に渡すパラメータの個数は同じでなければならない
ことに注意してください。
C<create_query()>を使って任意のSQLのクエリを作成
することもできます。
my $query = $dbi->create_query(
"insert into book {insert_param title author};";
);
=head2 9. その他の機能
=head3 メソッドの登録
L<DBIx::Custom>オブジェクトにメソッドを追加することができます。
C<method()>を使用します。
$dbi->method(
update_or_insert => sub {
my $self = shift;
# something
},
find_or_create => sub {
my $self = shift;
# something
}
);
これらのメソッドは
L<DBIx::Custom>オブジェクトから呼び出すことができます。
$dbi->update_or_insert;
$dbi->find_or_create;
=head3 結果クラスの変更
結果クラスを変更することができます。
デフォルトはL<DBIx::Custom::Result>です。
package MyResult;
use base 'DBIx::Custom::Result';
sub some_method { ... }
1;
package main;
use MyResult;
my $dbi = DBIx::Custom->connect(...);
$dbi->result_class('MyResult');
=head3 キャッシング
タグの解析後のSQLはパフォーマンスの理由のためにキャッシュされます。
これはC<chace>で設定でき、デフォルトではキャッシュを行う設定です。
$dbi->cache(1);
キャッシュ方法はC<cache_method>で変更することができます。
デフォルトのメソッドは以下のようになっていて、
メモリ上にキャッシュが保存されます。
$dbi->cache_method(sub {
sub {
my $self = shift;
$self->{_cached} ||= {};
if (@_ > 1) {
# キャッシュの保存
$self->{_cached}{$_[0]} = $_[1]
}
else {
# キャッシュの取得
return $self->{_cached}{$_[0]}
}
}
});
第一はL<DBIx::Custom>オブジェクトです。
第二引数はタグが解析される前のSQLです。
第三引数はタグの解析後のSQLの情報です。これはハッシュのリファレンスです。
第三引数が存在した場合はキャッシュを設定し、
存在しなかった場合はキャッシュを取得する実装に
してください。
=head1 サンプル
以下のWikiでサンプルを見ることができます。
L<DBIx::Custom Wiki|https://github.com/yuki-kimoto/DBIx-Custom/wiki>
=cut