Teng で検索あれこれ
前回までの説明で、Teng を何となく使えるようになると思う。今回は Teng が提供する検索メソッドについて触れてなかった箇所についての説明をする。
Teng の検索メソッド
以下の4つのメソッドが提供されている。
- Teng#search
- Teng#single
- Teng#search_named
- Teng#search_by_sql
上2つのメソッドは、Teng で CRUD をしてみる - amari3のはてなダイアリー で説明しているのでこちらを参照ください。
また、説明に使用するテーブルやスキーマ等は、こちら Teng でリレーションを使う方法 - amari3のはてなダイアリー と同じものです。
Teng#search_named メソッド
Teng#search_named メソッドは生(に近い)SQL を記述する時に便利なメソッド。Teng::Iterator オブジェクトが返ってくる。
サンプルコードです。
my $it = $teng->search_named( q{SELECT * FROM entry WHERE ( id IN :ids )}, +{ ids => [2, 4] } );
少し見慣れない表記が含まれているけど、実際には以下の様な SQL 文になる。
SELECT * FROM entry WHERE ( id IN ( ?,? ) ) bind [2,4]
IN 演算子の値の個数が動的に変わる場合でも、呼び出し側は意識せずに利用できて便利。
Teng#search_by_sql メソッド
Teng#search_by_sql メソッドは生 SQL 文を記述することができる。Teng::Iterator オブジェクトが返ってくる。
サンプルコードです。
my $it = $teng->search_by_sql( q{SELECT * FROM entry WHERE id > ?}, [ 2 ] );
普通に SQL 文が記述できるので、難しいところは無いと思う。
Teng で WHERE 句の条件やソート条件
ここからは Teng で WHERE 句の条件の記述方法やソート条件の記述方法を説明をする。Teng でと銘打ってはいるけど、Teng のクエリビルダである SQL::Maker の機能の説明になるので、SQL::Maker のドキュメントもあわせて読むのが良いでしょう。
BETWEEN 演算子を使う
範囲検索でよく使う BETWEEN 演算子の記述方法です。
my $it = $teng->search(entry => +{ id => +{ between => [2, 4] } });
カラム名に、ハッシュリファレンスで条件を記述することになる。難しいところは無いと思う。『>』や『!=』等も基本的には同じように記述する。
ORDER BY 句を使う
検索結果のソートをする、order by の記述方法です。
my $it = $teng->search(entry => +{ id => +{ '>' => 2 }}, +{ order_by => 'id DESC' });
Teng#search メソッドの第3引数に記述することで実現できる。こちらも難しいところは無いと思う。
最後に
今回は検索メソッドについて、少しだけ踏み込んだ説明をしました。複雑な検索方法も分かってきたので、業務や趣味プログラムでそろそろ使いたいと思います。
Teng でリレーションを使う方法
前回は Teng でトランザクション処理をする方法を紹介しました。実際に色々試して、ブログに書くと頭にいい感じで入ってくるので続けていきたいです。今回は Teng でリレーションを使う方法を紹介していきます。
題材
説明に使用する題材は、掲示板へのエントリとそれに対するコメントの様なものを想定。
使用するテーブル
以下の2つのテーブルを使用します。
test@localhost:testdb> desc entry; +------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(64) | NO | | NULL | | | title | varchar(128) | NO | | NULL | | | body | text | NO | | NULL | | | created_at | datetime | NO | | 0000-00-00 00:00:00 | | +------------+------------------+------+-----+---------------------+----------------+ 5 rows in set (0.00 sec) test@localhost:testdb> desc comment; +--------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | entry_id | int(10) unsigned | NO | MUL | NULL | | | name | varchar(64) | NO | | NULL | | | body | text | NO | | NULL | | | is_invisible | tinyint(4) | NO | | 0 | | | created_at | datetime | NO | | 0000-00-00 00:00:00 | | +--------------+------------------+------+-----+---------------------+----------------+ 6 rows in set (0.00 sec)
使用するデータ
すでに以下のデータが入れてあります。
test@localhost:testdb> select * from entry; +----+-----------------+-----------------------+--------------------------------------------------------+---------------------+ | id | name | title | body | created_at | +----+-----------------+-----------------------+--------------------------------------------------------+---------------------+ | 1 | amari3 | 肉まん食べたい | 肉まんが食べたいでござる | 2011-11-30 15:07:41 | | 2 | あまりさん | シュークリーム | シュークリームがいっぱい食べたいよ！ | 2011-11-30 15:07:41 | | 3 | amari3 | 魔法ジュース | 魔法ジュース久しぶりに飲みたいなぁ | 2011-11-30 15:07:41 | +----+-----------------+-----------------------+--------------------------------------------------------+---------------------+ 3 rows in set (0.03 sec) test@localhost:testdb> select * from comment; +----+----------+-----------------+-----------------------------+--------------+---------------------+ | id | entry_id | name | body | is_invisible | created_at | +----+----------+-----------------+-----------------------------+--------------+---------------------+ | 1 | 2 | 名無しさん | 俺も俺も | 0 | 2011-11-30 15:07:41 | | 2 | 2 | マヒャド | うまいよねー | 0 | 2011-11-30 15:07:41 | | 3 | 2 | ヒャダルコ | たまに食いたくなる | 0 | 2011-11-30 15:07:41 | | 4 | 3 | 名無しさん | 何それ！ | 0 | 2011-11-30 15:07:41 | +----+----------+-----------------+-----------------------------+--------------+---------------------+ 4 rows in set (0.00 sec)
モデルクラス
Teng を使うためのモデルクラスを定義します。
package My::DB; use parent 'Teng'; 1;
スキーマクラス
テーブル情報を持つスキーマクラスを定義します。
package My::DB::Schema; use Teng::Schema::Declare; use DateTime::Format::MySQL; table { name 'entry'; pk 'id'; columns qw( id name title body created_at ); inflate 'created_at' => sub { DateTime::Format::MySQL->parse_datetime(shift); }; deflate 'created_at' => sub { DateTime::Format::MySQL->format_datetime(shift); }; }; table { name 'comment'; pk 'id'; columns qw( id entry_id name body is_invisible created_at ); inflate 'created_at' => sub { DateTime::Format::MySQL->parse_datetime(shift); }; deflate 'created_at' => sub { DateTime::Format::MySQL->format_datetime(shift); }; }; 1;
上記の様に、entry/comment のテーブル情報を定義します。
entry/comment テーブルのリレーションシップを設定
Teng でリレーション設定をするには、Teng::Row(以下、Rowオブジェクト) へメソッドを追加することで実現できます。
entry.id と comment.entry_id がそれぞれのテーブルで関係する ID となるので、これで関連付けをします。関連付けをすることにより、あるエントリに対するコメント一覧等の取得が簡単にできるようになります。
任意の entry に関連する comment のイテレータを返すメソッドを定義
entry → comment は1対多の has_many なリレーションとなります。
package My::DB::Row::Entry; use strict; use warnings; use parent 'Teng::Row'; sub to_comments { my $self = shift; $self->{teng}->search(comment => +{ entry_id => $self->id }); } 1;
comment の Rowオブジェクトに関連する entry を取得するメソッドを定義
comment → entry は多対1 のbelongs_to なリレーションとなります。
package My::DB::Row::Comment; use strict; use warnings; use parent 'Teng::Row'; sub to_entry { my $self = shift; $self->{teng}->single(entry => +{ id => $self->entry_id }); } 1;
サンプルコードと実行結果
これまででリレーションの設定ができたので、実際に使ってみます。
まずは has_many なリレーションのサンプルコードです。
use strict; use warnings; use utf8; use feature qw( say ); use FindBin; use lib "$FindBin::Bin/lib"; use My::DB; my $teng = My::DB->new(connect_info => [ 'dbi:mysql:database=testdb', 'test', 'tes10', +{ RaiseError => 1, PrintError => 0, AutoCommit => 1, on_connect_do => [ "SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'", ], }, ]); my $entry = $teng->single(entry => +{ id => 2 }); say "entry.id: ", $entry->id, "\n", "entry.name: ", $entry->name, "\n", "entry.title: ", $entry->title, "\n", "entry.body: ", $entry->body, "\n", "entry.created_at: ", $entry->created_at; say "----"; my $it = $entry->to_comments; while (my $comment = $it->next) { say "comment.id: ", $comment->id, "\n", "comment.entry_id: ", $comment->entry_id, "\n", "comment.name: ", $comment->name, "\n", "comment.body: ", $comment->body, "\n", "comment.is_invisible: ", $comment->is_invisible, "\n", "comment.created_at: ", $comment->created_at; say "--"; }
実行結果です。
entry.id: 2 entry.name: あまりさん entry.title: シュークリーム entry.body: シュークリームがいっぱい食べたいよ！ entry.created_at: 2011-11-30T15:07:41 ---- comment.id: 1 comment.entry_id: 2 comment.name: 名無しさん comment.body: 俺も俺も comment.is_invisible: 0 comment.created_at: 2011-11-30T15:07:41 -- comment.id: 2 comment.entry_id: 2 comment.name: マヒャド comment.body: うまいよねー comment.is_invisible: 0 comment.created_at: 2011-11-30T15:07:41 -- comment.id: 3 comment.entry_id: 2 comment.name: ヒャダルコ comment.body: たまに食いたくなる comment.is_invisible: 0 comment.created_at: 2011-11-30T15:07:41 --
続いて、belongs_to なリレーションのサンプルコードです。(重複するコードは割愛)
my $it = $teng->search(comment => +{ entry_id => 2 }); while (my $comment = $it->next) { my $entry = $comment->to_entry; say "entry.name: ", $entry->name, "\n", "entry.title: ", $entry->title, "\n", "comment.id: ", $comment->id, "\n", "comment.name: ", $comment->name, "\n", "comment.body: ", $comment->body, "\n", "comment.created_at: ", $comment->created_at; say "--"; }
実行結果です。
entry.name: あまりさん entry.title: シュークリーム comment.id: 1 comment.name: 名無しさん comment.body: 俺も俺も comment.created_at: 2011-11-30T15:07:41 -- entry.name: あまりさん entry.title: シュークリーム comment.id: 2 comment.name: マヒャド comment.body: うまいよねー comment.created_at: 2011-11-30T15:07:41 -- entry.name: あまりさん entry.title: シュークリーム comment.id: 3 comment.name: ヒャダルコ comment.body: たまに食いたくなる comment.created_at: 2011-11-30T15:07:41 --
最後に
Teng でリレーションを使うのは、非常に簡単だということ分かりました。今回は 1対1 や、多対多のリレーションは扱いませんでしたが、同じ様に簡単に扱えると思います。
Teng でトランザクション処理 & はまった点
前回は、Teng で一通りの CRUD の仕方を紹介したんですが、トランザクション処理を考慮していなかったので、Teng でトランザクション処理をする方法を紹介します。
準備
テーブルは前回使用した memo テーブルを使います。
事前に以下の様なデータを入れておきました。
mysql> select * from memo order by id; +----+-------+---------+---------------------+---------------------+ | id | title | body | created_at | updated_at | +----+-------+---------+---------------------+---------------------+ | 1 | Hello | World | 2011-11-24 23:05:56 | 2011-11-24 23:05:56 | | 2 | red | green | 2011-11-24 23:05:56 | 2011-11-24 23:05:56 | | 3 | black | white | 2011-11-24 23:05:56 | 2011-11-24 23:05:56 | | 4 | foo | bar baz | 2011-11-24 23:05:56 | 2011-11-24 23:05:56 | +----+-------+---------+---------------------+---------------------+
サンプルコード
前回紹介した、データの更新にトランザクション処理をするための処理を追加してあります。
use strict; use warnings; use feature qw( say ); use lib "lib"; use My::DB; use DateTime; use Data::Dumper; my $teng = My::DB->new(connect_info => [ 'dbi:mysql:database=testdb', 'test', 'tes10', +{ RaiseError => 1, PrintError => 0, AutoCommit => 1, }, ]); $teng->txn_begin; my $iter = $teng->search(memo => +{ id => [1, 2] }); while (my $row = $iter->next) { $row->update({ body => 'transaction test' }); } $teng->txn_commit; say $teng->single(memo => +{ id => 1 })->body; say $teng->single(memo => +{ id => 2 })->body;
$teng->txn_begin; と $teng->txn_commit; を追加したのみです。
実行結果です。
transaction test transaction test
これでうまく動いているのですが、実ははまった箇所があるのでその説明です。
AutoCommit off 設定でエラー
トランザクション処理をするということで、AutoCommit を off に。これがはまる原因で、最初はインスタンスの生成を以下の様なコードにしていました。
my $teng = My::DB->new(connect_info => [ 'dbi:mysql:database=testdb', 'test', 'tes10', +{ RaiseError => 1, PrintError => 0, AutoCommit => 0, }, ]);
そして、実行してみると以下の様なエラーが出ます。
DBD::mysql::db begin_work failed: Already in a transaction at /home/tanida/perl5/perlbrew/perls/perl-5.12.1/lib/site_perl/5.12.1/DBIx/TransactionManager.pm line 32.
begin_work という見慣れないメソッドが出てきたので、DBI のドキュメントを読んでみたところ、AutoCommit を off にするメソッドでした。さらに読み進めると、AutoCommit が off の状態で呼び出すとエラーになるという記述が。
これが原因だと考え、以下の簡単なコードを書いて確認。
use strict; use warnings; use feature qw( say ); use DBI; use Data::Dumper; my $dbh = DBI->connect('dbi:mysql:database=testdb', 'test', 'tes10', +{ RaiseError => 1, PrintError => 0, AutoCommit => 0, }); $dbh->begin_work; $dbh->do( qq{update memo set body = ? where id in (?, ?)}, undef, 'hogehoge', 1, 2 ); $dbh->commit; $dbh->disconnect if $dbh;
実行結果です。
DBD::mysql::db begin_work failed: Already in a transaction at begin_work_test.pl line 11.
期待したとおりエラーになってくれました。
Teng#txn_begin の内部で呼び出している、DBIx::TransactionManager#txn_begin で DBI#begin_work を呼び出しているので、AutoCommit は on にする必要がありそうです。(調べが足りてないだけかもなので、もう少し調査します)
Teng で CRUD をしてみる
id:nekokak さんが開発されている、Teng で CRUD を一通りやってみました。
僕自身、DBIx::Class を以前使っていたんですが、あまりにも機能が富豪的すぎて使うのをやめてしまいました。それからは生 DBI を使っていたんですが、開発効率を考えたときに、ORM は使うべきだと再度考えるようになり、軽量な ORM、Teng に注目している所です。
準備
テスト用のテーブル
+------------+-----------+------+-----+---------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+-----------+------+-----+---------------------+-----------------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | title | text | NO | | NULL | | | body | text | NO | | NULL | | | created_at | datetime | NO | | 0000-00-00 00:00:00 | | | updated_at | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +------------+-----------+------+-----+---------------------+-----------------------------+
まずは、モデルとスキーマのクラスを作成
package My::DB; use strict; use warnings; use parent 'Teng'; package My::DB::Schema; use strict; use warnings; use Teng::Schema::Declare; use DateTime::Format::MySQL; table { name 'memo'; pk 'id'; columns qw( id title body created_at updated_at ); inflate qr/_at$/ => sub { DateTime::Format::MySQL->parse_datetime(shift); }; deflate qr/_at$/ => sub { DateTime::Format::MySQL->format_datetime(shift); }; }; 1;
inflate/deflate の第一引数には正規表現が書けるので、複数のカラムを一度に inflate/deflate 設定できるようです。
データの作成
use strict; use warnings; use feature qw( say ); use lib "lib"; use My::DB; use DateTime; use Data::Dumper; my $teng = My::DB->new(connect_info => [ 'dbi:mysql:database=testdb', 'test', 'tes10', +{ RaiseError => 1, PrintError => 0, AutoCommit => 1, }, ]); my $row = $teng->insert(memo => +{ id => 1, # auto_incrementを指定しているけどテスト用に固定値を入れる title => 'Hello', body => 'World', created_at => DateTime->now(time_zone => 'local'), }); say Dumper($row->get_columns); # Teng::Row が必要ない場合 my $last_insert_id = $teng->fast_insert(memo => +{ id => 2, title => 'red', body => 'green', created_at => DateTime->now(time_zone => 'local'), }); say "last_insert_id: ", $last_insert_id; # テスト用データを作成 $teng->fast_insert(memo => +{ id => 3, title => 'black', body => 'white', created_at => DateTime->now(time_zone => 'local'), }); $teng->fast_insert(memo => +{ id => 4, title => 'foo', body => 'bar baz', created_at => DateTime->now(time_zone => 'local'), });
Teng#insert を実行すると、Teng::Row オブジェクトが返ってきます。必要ない場合は、Teng#fast_insert が使えます。
実行結果
$VAR1 = {
'body' => 'World',
'created_at' => '2011-11-21 00:30:47',
'updated_at' => '2011-11-21 00:30:47',
'id' => '1',
'title' => 'Hello'
};
last_insert_id: 2
データの検索
use strict; use warnings; use feature qw( say ); use lib "lib"; use My::DB; use DateTime; use Data::Dumper; my $teng = My::DB->new(connect_info => [ 'dbi:mysql:database=testdb', 'test', 'tes10', +{ RaiseError => 1, PrintError => 0, AutoCommit => 1, }, ]); my $iter = $teng->search(memo => +{ id => [1, 2] }); while (my $row = $iter->next) { say Dumper($row->get_columns); say $row->created_at->ymd; # DateTime } say ""; # 一件のみ取得 my $memo = $teng->single(memo => +{ id => 2 }); say Dumper($memo->get_columns); say $memo->created_at->ymd; # DateTime
複数行を取得する時は、Teng#search が使えます。Teng::Iterator オブジェクトが返り値となり、Teng::Iterator#next で Teng::Row オブジェクトが取得できます。
一行だけ取得する場合は、Teng#single が使えます。created_at カラムは、DateTime オブジェクトになっています。なお、Teng::Row#get_columns で取得した場合は、単純な値になるみたいです。
実行結果
$VAR1 = {
'body' => 'World',
'created_at' => '2011-11-21 00:30:47',
'updated_at' => '2011-11-21 00:30:47',
'id' => '1',
'title' => 'Hello'
};
2011-11-21
$VAR1 = {
'body' => 'green',
'created_at' => '2011-11-21 00:30:47',
'updated_at' => '2011-11-21 00:30:47',
'id' => '2',
'title' => 'red'
};
2011-11-21
$VAR1 = {
'body' => 'green',
'created_at' => '2011-11-21 00:30:47',
'updated_at' => '2011-11-21 00:30:47',
'id' => '2',
'title' => 'red'
};
2011-11-21
データの更新
use strict; use warnings; use feature qw( say ); use lib "lib"; use My::DB; use DateTime; use Data::Dumper; my $teng = My::DB->new(connect_info => [ 'dbi:mysql:database=testdb', 'test', 'tes10', +{ RaiseError => 1, PrintError => 0, AutoCommit => 1, }, ]); my $iter = $teng->search(memo => +{ id => [1, 2] }); while (my $row = $iter->next) { $row->update({ body => 'update test' }); } say $teng->single(memo => +{ id => 1 })->body; say $teng->single(memo => +{ id => 2 })->body;
Teng#update を実行します。難しいところはないと思います。
実行結果
update test update test
データの削除
use strict; use warnings; use feature qw( say ); use lib "lib"; use My::DB; use DateTime; use Data::Dumper; my $teng = My::DB->new(connect_info => [ 'dbi:mysql:database=testdb', 'test', 'tes10', +{ RaiseError => 1, PrintError => 0, AutoCommit => 1, }, ]); my $rows = $teng->delete(memo => +{ id => 1 } ); say "deleted rows: ", $rows; # 削除した件数 # 別なやり方 my $iter = $teng->search(memo => +{ id => [2, 4] }); while (my $row = $iter->next) { $row->delete; }
Teng#delete と Teng::Row#delete を使用することができます。
実行結果
deleted rows: 1
使ってみた印象
実際に使ってみたところ、非常に簡単に使えるなと言うのが第一印象です。また、Web開発をするにあたり、通常使用する分には必要十分な機能を兼ね揃えていると思います。
最後に
今回は入門編として、トランザクション等の考慮はしておらず、とにかく Teng を触って覚えることを主旨としました。今後は、Teng でのリレーションの仕方等を覚えていこうと思います。
