SQL::Abstract を使い倒す
今更、携帯小説にハマってる id:ZIGOROu です。モバゲーのオンライン3って小説が面白いですよ！
今日は SQL::Abstract を使い倒すと言うネタで行きます。
まず超基本編
簡単な SQL 文の生成から始めましょう。
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract;
my $s = SQL::Abstract->new;
my ($stmt, @bind) = $s->select(
"activity", # tables
[qw/id title sender created_on/], # columns
+{ guid => 100 }, # where
);
print dump( $stmt, \@bind );
これは次のようになります。
( "SELECT id, title, sender, created_on FROM activity WHERE ( guid = ? )", [100], )
select は、
- table
- columns
- where
- order
と言う感じで指定していくわけですね。WHERE の部分は値を指定して行くだけでよしなに bind パラメータ化してくれます。
少し複雑な例
実行例をさらに挙げましょう。
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract;
my $s = SQL::Abstract->new;
my ($stmt, @bind) = $s->select(
'activity', # tables
[qw/id title guid sender created_on/], # columns
+{ guid => 100, created_on => +{ '>=' => \'UNIX_TIMESTAMP() - 300' } }, # where
[ +{ -desc => [qw/created_on/] } ] # order
);
print dump($stmt, \@bind);
( "SELECT id, title, guid, sender, created_on FROM activity WHERE ( ( created_on >= UNIX_TIMESTAMP() - 300 AND guid = ? ) ) ORDER BY created_on DESC", [100], )
ここでは幾つかポイントがあります。
スカラーリファレンス
まず、「値」としてスカラーリファレンスを入れた場合、SQL::Abstract はそのまま文字列として展開します。
\'UNIX_TIMESTAMP() - 300'
の部分がまさにそれです。困ったらスカラーリファレンスで何とかなるだろう、と思って間違いないです。
イコール以外の比較
単純にイコールで比較ではなく不等号 ( >= ) で比較したい場合には、
created_on => +{ '>=' => \'UNIX_TIMEATAMP() - 300' }
のように用います。簡単ですね。IS NOT NULL とかやりたい場合もこんな感じです。
サブクエリ
こいつも簡単です。最もサブクエリは大規模なサービスでは分散の仕方によってまったく使えなかったりするんですが、
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract::Limit;
my $s = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );
my ($stmt, @bind);
($stmt, @bind) = $s->select(
'friends',
[ qw/friend_guid/ ],
+{ guid => 100, },
[ +{ -asc => [qw/created_on/] } ],
100,
0,
);
($stmt, @bind) = $s->select(
'users',
[qw/guid nickname gender created_on updated_on/],
+{ guid => +{ IN => \[ $stmt, @bind ] } }
);
print dump($stmt, \@bind);
( "SELECT guid, nickname, gender, created_on, updated_on FROM users WHERE ( guid IN ( SELECT friend_guid FROM friends WHERE ( guid = ? ) ORDER BY created_on ASC LIMIT 100 ) )", [100], )
ここでは SQL::Abstract::Limit と言う、いわゆる MySQL で言うところの LIMIT, OFFSET の処理を抽象化してくれる SQL::Abstract のサブクラスです。この部分、RDBMS によって結構方言の違いがある所なんですよね。
でサブクエリの部分なんですけど、直前に作った select 文のステートメント文とバインド値を埋め込むのに、ARRAYREF のさらにリファレンスを用いると言う手段を取ってます。
ARRAYREFのリファレンスを用いると、ステートメントをバインド値を保持したまま埋め込む事が出来ると言う事を覚えておいて下さい。
SQL::Abstract の苦手な所
まず JOIN とかは書けません（ｷﾘｯ
まぁ書けなくも無いんですけどね。
とりあえず、複数のテーブルの INNER JOIN は FROM 句に並べる形で、
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract;
my $s = SQL::Abstract->new;
my ($stmt, @bind);
($stmt, @bind) = $s->select(
['users', 'friends'],
[ qw/users.nickname/ ],
+{ 'friends.guid' => 100, 'friends.friend_guid' => \'= users.guid' },
);
print dump($stmt, \@bind);
( "SELECT users.nickname FROM users, friends WHERE ( ( friends.friend_guid = users.guid AND friends.guid = ? ) )", [100], )
と言うのであればまぁ書けなくは無いです。
この辺りから最早、使いやすいのか使いにくいのか分からなくなってきますが、LEFT JOIN ならこんなんでやれなくもないです。
ちょっと順を追ってやっていきます。
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract;
my $s = SQL::Abstract->new;
my ($stmt, @bind);
($stmt, @bind) = $s->_recurse_where(+{
'friends.friend_guid' => \'= users.guid',
'friends.guid' => 100,
});
print dump($stmt, \@bind);
の実行結果を見ると、
( "( friends.friend_guid = users.guid AND friends.guid = ? )", [100], )
のようになります。_recurse_where は where 文を作る為の内部 API です。ここで出来た SQL の断片は ON 句に使えそうですよね。
が FROM users LEFT JOIN friends ON の文字列をどこに差し込むかと言うと、tables 指定のところしかなく、ここには ARRAYREF のリファレンスを埋め込む事は出来ないので、バインド値を差し込む事は出来ません、、、無念。
やりたいならば、
#!/usr/bin/perl
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract;
my $s = SQL::Abstract->new;
my ($stmt, @bind);
$stmt = $s->_recurse_where(+{
'friends.friend_guid' => \'= users.guid'
});
($stmt, @bind) = $s->select(
'users' . $s->_sqlcase(' left join ') . 'friends' . $s->_sqlcase(' on ') . $stmt,
[ 'users.nickname' ],
+{ 'friends.guid' => 100, }
);
print dump( $stmt, \@bind );
( "SELECT users.nickname FROM users LEFT JOIN friends ON friends.friend_guid = users.guid WHERE ( friends.guid = ? )", [100], )
はい、このあたりになると普通に SQL を大人しく書きましょうって話だと思います。GROUP BY になると手も足も出ません。
SQL::Abstract::Plugin::InsertMulti
hacker トラックは自作モジュールについて語るんでした。
SQL::Abstract における insert, update, delete は物凄く扱いやすいです。
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract;
my $s = SQL::Abstract->new;
my ($stmt, @bind);
($stmt, @bind) = $s->insert(
'app_data',
+{
name => 'pokes',
data => 10,
created_on => \'UNIX_TIMESTAMP()',
updated_on => \'UNIX_TIMESTAMP()',
},
);
print dump( $stmt, \@bind );
( "INSERT INTO app_data ( created_on, data, name, updated_on) VALUES ( UNIX_TIMESTAMP(), ?, ?, UNIX_TIMESTAMP() )", [10, "pokes"], )
とまぁ、だいぶ直感的に使う事が出来ますが、VALUES に複数の値を列挙してまとめて INSERT する BULK INSERT がどうしても書けないんですよね。
それを出来るようにしたのが拙作の SQL::Abstract::Plugin::InsertMulti です。自分が SQL::Abstract::Limit を実際使っている事から、単純な継承で拡張する訳にもいかず中で強引な事をしてます。まぁ何はともあれ例としては、
#!/usr/bin/perl
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract;
use SQL::Abstract::Plugin::InsertMulti;
my $app_data = +{
pokes => 10,
last_poke => '2009-11-28',
};
my $s = SQL::Abstract->new;
my ($stmt, @bind);
($stmt, @bind) = $s->insert_multi(
'app_data',
[
map { +{
name => $_,
data => $app_data->{$_},
created_on => \'UNIX_TIMESTAMP()',
updated_on => \'UNIX_TIMESTAMP()',
} }
keys %$app_data,
],
);
print dump( $stmt, \@bind );
( "INSERT INTO app_data ( created_on, data, name, updated_on ) VALUES ( UNIX_TIMESTAMP(), ?, ?, UNIX_TIMESTAMP() ), ( UNIX_TIMESTAMP(), ?, ?, UNIX_TIMESTAMP() )", ["2009-11-28", "last_poke", 10, "pokes"], )
とまぁこういう感じで出来上がる訳です。カラムの順序は保持されてる方が嬉しいよーって方の場合は、
#!/usr/bin/perl
use strict;
use warnings;
use Data::Dump qw(dump);
use SQL::Abstract;
use SQL::Abstract::Plugin::InsertMulti;
my $app_data = +{
pokes => 10,
last_poke => '2009-11-28',
};
my $s = SQL::Abstract->new;
my ($stmt, @bind);
($stmt, @bind) = $s->insert_multi(
'app_data',
[qw/name data created_on updated_on/],
[
map { [ $_, $app_data->{$_}, \'UNIX_TIMESTAMP()', \'UNIX_TIMESTAMP()', ] }
keys %$app_data,
],
);
print dump( $stmt, \@bind );
( "INSERT INTO app_data ( name, data, created_on, updated_on ) VALUES ( ?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP() ), ( ?, ?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP() )", ["last_poke", "2009-11-28", "pokes", 10], )
のようにすると期待通りの出力になります。
他にも色々と細かな設定が出来るので詳しくは [http://search.cpan.org/dist/SQL-Abstract-Plugin-InsertMulti/lib/SQL/Abstract/Plugin/InsertMulti.pm=title:perldoc SQL::Abstract::Plugin::InsertMulti] を見てください。
まとめ
- SQL::Abstract は大体のクエリは書けるよ
- 困ったらスカラーリファレンスや配列リファレンスのリファレンスを使うと大概の事は出来るよ
- でもどう考えても生SQL書いた方が早いよって時には大人しく諦めましょう
- LIMIT, OFFSET は SQL::Abstract::Limit で。
- Bulk Insert, Bulk Update をしたい場合は SQL::Abstract::Plugin::InsertMulti を use すればおｋです
次は何故か既に記事がコミットされてる id:dann さんです。
