第36回　SQL::Abstract：簡単なSQLはより簡単に

DBIの泣き所

いわゆるLAMPないしそれに似た環境でウェブサービスばかり書いている方にはあまり実感がないかもしれませんが、あちらの現場ではOracleを、こちらの現場ではMicrosoft SQL Serverを、はたまた別の現場では組み込みのSQLiteを、といった受託系の仕事をしている人にとって、SQLの方言問題は避けては通れないもののひとつです。

典型的なところでは、たとえばSELECTで取得するデータの件数を制限したい場合、PostgreSQLなどでは「LIMIT ... OFFSET ...」のように書きますが、OracleではROWNUMを使いますし、MS SQL serverならSET ROWCOUNTやTOPを使います。また、いまでこそPostgreSQLとの互換性を確保するため「LIMIT ... OFFSET ...」と書けるようになっているMySQLにしたところで、もともとは「LIMIT ..., ...」という書き方をすることになっていました。

DBIのほうでもプレースホルダやクォートの仕方についてはベンダ間の違いをなるべく吸収する努力が行われていますが、それ以上の移植性を求めるのであれば、先頭から数件のデータを抽出するというごく単純な作業でさえデータベースの種類ごとに場合分けをしてやらなければなりません。

非対称なハッシュの扱い

もちろん「途中でデータベースを切り替えるなんてことはありえない」と考えて、特定のデータベースにすり寄った書き方をすることはできます（アプリケーションの場合はむしろそのように書くほうがふつうでしょう⁠）⁠。いちいちSQLの方言を気にしなくてよいのであれば、DBIが提供する標準的なインタフェースを使うことで、だれもが理解できる、学習コストの低い（人員の増強や引き継ぎをしやすい）アプリケーションを書くこともできるはずですし、実際、定型のデータを流し込んだり、既存のデータを特定の形式で取り出すだけのアプリケーションであれば、DBIのインタフェースで十分対応できます。

たとえば、典型的な例として、適当なCSVデータをデータベースに流し込む例を考えてみましょう。データはID、名前、住所という3つのカラムにわかれているとします。流し込む部分のコードはおおよそこんな感じになるでしょうか。

my $fh  = IO::File->new(...);
my $csv = Text::CSV_XS->new(...);
my $dbh = DBI->connect(...);
my $sth = $dbh->prepare('INSERT INTO foo VALUES (?,?,?)');
while(my $row = $csv->getline($fh)) {
    $sth->execute(@$row);
}

こうして流し込んだデータは、DBIのfetchメソッドを使えば、CSVデータを扱う場合とほとんど同じように操作できます。取得したデータを再度別のデータベースに挿入する場合も、（⁠スキーマの制約が許すかぎり）特にデータを変換する必要はありません。

my $new_sth = $dbh->prepare('SELECT * FROM foo WHERE ...');
while(my $row = $new_sth->fetch) { ... } ※1

ただし、このような配列（リファレンス）を使ったデータの処理は、高速ですが、中にどんなデータが入っているかわかりづらいうえ、何らかの事情でカラムの数が変わったり順序が入れ替わったりするとたちまちバグのもとになる、という問題があります。

もちろんSQLのレベルで取得・更新するカラムを明示的に指定してやればある程度問題は軽減できますが、このような場合はデータをハッシュに入れれば、読みやすく、変更にも強くなります。そのため、データの取得部分については1997年リリースのDBI 0.83で対応が行われ、新たにfetchrow_hashrefなどのメソッドが追加されたのですが、データの挿入や更新、あるいは取得の場合であってもデータを抽出する条件部分については、あまりにも多様な書き方が許されているため、DBIレベルでの対応は見送られました。これは、簡単なことだけでなく、複雑なこともできるようにしておかなければならない低レベルAPIの仕様としては妥当な判断でしたが、それほど複雑な処理を必要としない人にとっては、大きな不満のもとでもありました。tieを使ってDBMとハッシュを紐付ければハッシュの値を更新すると同時にデータベースの値も更新できるのですから、DBIでもハッシュとして取り出した値をハッシュのまま格納しなおしたいと思うのは当然のことでしょう。

今回はデータベース話の2回目として、そのような不満を感じた人が「簡単なこと」をするためにどうしてきたかを追いかけていくことにします。

DBIレベルでの解答

まずはもっとも基本的な対策として、素のDBIでハッシュのデータを扱うやり方を見ておきましょう。そのままではハッシュを受け取れないといっても、ハッシュから適切な配列を用意してやれば問題は解決できます。やり方はいくつかありますが、ここではスキーマが少しくらい変わってもコードを書き換えずにすむよう、実際にデータベースに接続してカラム名とその並び方を取得することから始めてみましょう。

my $dbh = DBI->connect(...);
my $sth = $dbh->column_info(undef, undef, 'foo', undef);
my @columns = map { $_->[3] } @{ $sth->fetchall_arrayref };

なお、お使いのドライバが万一$dbh->column_infoに対応していないようなら、接続時にあらかじめ一度ダミーのデータを取得してみてください。ステートメントハンドルにカラム名のリストが渡ってきているはずです。このリストは、ステートメントハンドルのNAMEアトリビュート経由で取得できます（ここではNAME_lcを使って取り出したカラム名を小文字に統一しています⁠）⁠。

my $sth = $dbh->prepare('SELECT * FROM foo LIMIT 1'); ※2
$sth->execute;
my @columns = @{$sth->{NAME_lc} || []};

また、手間を惜しむつもりがなく、スキーマが更新したときにはかならずコードも修正できる体制ができているなら、自分でスキーマを調べて、カラム名をベタ書きしておけば、実行速度は短縮できます。

カラム名のリストが用意できたら、今度はそれを使ってハッシュ（リファレンス）のスライスを作りましょう[3]⁠。

my $sth = $dbh->prepare('INSERT INTO foo VALUES (?,?,?)');
$sth->execute(@$row_hash{@columns});

これでハッシュに入っているデータをデータベースに流し込めるようになったのですが、せっかくデータベースからカラム名を取得しているのですから、カラムの数が変わったときにも対応できるように、プレースホルダもカラム数にあわせて自作しておきましょう。このプレースホルダの作り方についてはnipotanこと谷口公一氏がブログ記事のなかでさまざまな手法を比較していますので、未見の方はぜひご一読をば。

my $placeholders = substr '?,' x @columns, 0, -1;
my $sth = $dbh->prepare("INSERT INTO foo VALUES ($placeholders)");

もう少し汎用的にするなら、カラムの順序が変わったり、一部のカラムのデータのみ挿入するときのことも考えて、カラム名の指定も入れておきたいところ。ここではid以外のカラムに値を入れる例を用意しましたが、ブラウザからPOSTされたデータがあるならそのパラメータ名を並べておいてもよいでしょう（ただし、その場合は本当に存在しているカラムか確認しないと、予期せぬエラーが出るおそれがあります⁠）⁠。

my @my_columns = grep !/id/, @columns;
my $placeholders = substr '?,' x @my_columns, 0, -1;
my $column_names = join ',', @my_columns;
my $sth = $dbh->prepare("INSERT INTO foo ($column_names) VALUES ($placeholders)");
$sth->execute(@$row_hash{@my_columns});

INSERTの場合はこれでおしまいですが、これをUPDATEやDELETEにそのまま流用できないところがDBIの（というか、SQLの）泣き所です。

たとえば、UPDATEの場合はプレースホルダの作り方がINSERTとは異なっています。

my $placeholders = join ',', map {"$_ = ?"} @columns;
my $sth = $dbh->prepare("UPDATE foo SET $placeholders");
$sth->execute(@$row_hash{@columns});

もちろんこのままではすべてのデータが一色に塗りつぶされてしまいますので、UPDATEを使うのであれば当然WHERE節もほしいところです。

my $placeholders = join ',', map {"$_ = ?"} @columns;
my $sth = $dbh->prepare("UPDATE foo SET $placeholders WHERE id = ?");
$sth->execute(@$row_hash{@columns}, @$row_hash{qw/id/}); ※4

ここではWHERE節の中身をベタ書きしましたが、汎用性を考えるなら、ここも頑張ってハッシュで受け取った検索条件を整形してもよいでしょう。もっとも、この辺はアプリケーションのビジネスロジックと密接に結びつくところでもあるので、あまり汎用的にしすぎず、具体的な用途にあわせてSQLを調整したメソッドを並べるほうが最終的なコードは読みやすくなることもあります。用途に合わせて最適な方法を選んでください。

[4]

なお、ハッシュのスライスではなくmapを使ってスライスを作っている場合は、後続のmapの結果が先頭のmapに食われてしまわないよう、かっこで影響範囲を明確にしてください。

$sth->execute(map({$row_hash->{$_}} @columns), map({$row_hash->{$_}} qw/id/));

SELECTについては、いまではfetchrow_hashrefがありますからデータの取得部分には特に手を入れる必要はありませんが、fetchrow_hashrefは毎回ハッシュを生成するので速度的にどうもなあ、という方には、DBIのPODにも書いてあるように、生成済みのハッシュをbind_columnsで割り付ける方法があります（あまり見慣れない形かと思いますが、この「\」は配列を配列リファレンスにするものではなく、配列の各要素をリファレンスにしてくれるものです。詳しくはperldocなどでperlrefをご覧ください⁠）⁠。

my %row_hash;
$sth->execute;
$sth->bind_columns(\(@row_hash{@columns}));
while($sth->fetch) { ... }

DBIx::Abstract

このように、DBIレベルでもPerlの能力を駆使すればそれなりに簡潔で、カラムの変更にも強いコードを書くことはできます。

が、DBIベースのモデルクラスを書くたびにハッシュのスライスやプレースホルダを生成するコードを書くのはいかにも面倒です。性能を求めて、扱うデータベースが変わるたびにそのデータベース専用にモデルをチューニングしているような人ならなおさらのことでしょう。

そのため、1998年にリリースされたDBIx::Recordsetを皮切りに、1990年代末から2000年代前半にかけては、さまざまな方法でDBIを拡張して、ハッシュのやりとりを改善したり、データの取得をより簡単にするモジュールが登場しました。

初期の実装にはDBIx::CGI（1999年2月）やDBIx::HTMLinterface（1999年7月⁠）⁠、DBIx::CGITables（2000年1月⁠）⁠、DBIx::HTMLTable（2001年5月）のように、CGIやHTMLを前面に打ち出したものも多いのですが、この時期にはすでにClass::DBI（1999年12月）やDBIx::Abstract（2000年3月）のような、DBIをもう少し高いレベルで抽象化する動きも始まっています。

Class::DBIについては次回扱うとして、たとえばDBIx::Abstractを使うと、先ほどのINSERTの例はこのように書くことができます。insertの最初の引数 'foo' はテーブル名の指定で、次のハッシュリファレンスは挿入するカラムと値のペアです。

my $dbh = DBIx::Abstract->connect({ dsn => '...' });
$dbh->insert('foo', \%row_hash);

せっかくハッシュリファレンスに対応したのに引数の順序が固定なのは片手落ちじゃないかと感じる方は、引数の指定そのものもハッシュリファレンスで行えます。

# SELECT * FROM foo WHERE id = '1'
$dbh->select({
    table  => 'foo',
    fields => '*',
    where  => { id => 1 },
});

もっとも、すべてのSQL文についてこのようなメソッドが用意されているわけではありません。DBIのdoのように任意のSQLを実行したいときはqueryメソッドを使うことになっています（DBIのdoと違って、バインドパラメータはSQLのあとにすぐ並べます⁠）⁠。

$dbh->query($sql, @bind_params);

DBIx::Abstractの仕事はこのようなSQLの抽象化だけではありません。データベースの接続を確認して必要なら再接続してくれますし、fetchrow_hashのようにDBIレベルでは欠落しているAPIもいくつか追加されています。また、下の例を見てもわかるように、DBIx::Abstractではステートメントハンドルの存在を意識しなくてもよいようになっているのも大きな特徴です。

$dbh->select('*', 'foo', {id => 1});
my %row = $dbh->fetchrow_hash;

SQL::AbstractとSQL::Abstract::Limit

DBIx::Abstractは、簡単なことをしたいときには十分に便利なモジュールですが、先ほど取り上げたLIMITのサポートがなかったり、生成したSQLを微調整することができなかったりと、ちょっと複雑なことをしようと思うとかゆいところに手が届かなくなるという弱みもありました。

そのため、2002年8月には、DBIx::AbstractのSQL生成部分は（ほとんど）そのままに、インタフェースは生のDBIのものを使えるようにするSQL::Abstractが登場します。また、やや遅れて2004年には、SQL::AbstractにLIMITのサポートを追加するSQL::Abstract::Limitもリリースされています。

SQL::AbstractとDBIx::Abstractの最大の違いは、DBIのインタフェースに対する追加機能がなくなっていることですが、ほかにも、selectメソッドの引数が異なっている（テーブル指定とカラム指定が逆になっているほか、JOINやGROUP BYのサポートが脱落している）とか、DBIx::Abstractにあったハッシュリファレンスによる冗長な指定法がなくなっているといった違いもあります。

比較のために、SQL::Abstract::Limitを使ってSELECT文を発行する例を見てみましょう。SQL::Abstract::Limitのインスタンスを生成するときにDBIのハンドルを渡しているのは接続先のデータベースにあわせてLIMITの方言を生成してもらうためですが、使いたい方言がわかっている場合はその名前を渡してやればデータベースに接続せずにSQLの生成だけを行うこともできます。

my $dbh = DBI->connect('dbi:SQLite::memory:');
my $sqla = SQL::Abstract::Limit->new(limit_dialect => $dbh);

# SELECT * FROM foo WHERE ( id = ? ) ORDER BY id LIMIT 10
my ($sql, @bind) = $sqla->select('foo', '*', { id => 1 }, [qw/id/], 10);

# ここからはDBIのインタフェースを利用する
my $sth = $dbh->prepare($sql);
$sth->execute(@bind);
while($sth->fetch) { ... }

SQL::Abstract(::Limit) では一度生のSQLを受け取るようになっているため、必要なら正規表現などでSQLの内容を書き換えることもできます。次の例では挿入時にエラーが出ても無視するよう、生成されたSQLを修正してOR IGNOREを追加しています。

# INSERT INTO foo ( id, text) VALUES ( ?, ? )
my ($sql, @bind) = $sqla->insert('foo', { id => 1, text => 'bar' });

# INSERT OR IGNORE INTO...に書き換え
$sql =~ s/^INSERT /INSERT OR IGNORE /;

my $sth = $dbh->prepare($sql);
$sth->execute(@bind);
while($sth->fetch) { ... }

これまでの例はハッシュのキーとデータベースのカラムが一対一に対応していましたが、SQL::Abstractでは論理構造を含むもっと複雑なSQLを生成することもできます。次の例は、いささか恣意的ですが、SQL::Abstractが対応している機能の一例を示すために、さまざまな条件を組み合わたものです。最近ではこうして生成したSQLをサブクエリとして別のSQL::Abstractの構造のなかに埋め込むこともできるようになっているので、その気になればかなり複雑なSQLでもSQL::Abstractひとつで構築できます。

# SELECT * FROM foo WHERE ( ( ( id = ? AND text = ? ) OR ( ( id BETWEEN ? AND ? ) AND text LIKE ? ) OR id IN ( ?, ?, ?, ? ) OR text is null ) )
my ($sql, @bind) = $sqla->select('foo', '*', [
  { id => 1, text => 'bar' },
  { id => { -between => [10, 20] }, text => { -like => 'boo%' }},
  { id => { -in => [2,3,4,5]}},
  { text => \'is null' },
]);

SQL::Interp

SQL::Abstractは、上手に使えば強力なツールですし、Perl界ではモダンなO/Rマッパとして知られるDBIx::Classの根幹をなすモジュールのひとつでもありますから、基本的な書き方くらいは覚えておいても損はありません。

ただ、上の例でもわかるように、SQL::Abstractを使ってある程度複雑なSQLを書こうとすると、さまざまなリファレンスが入り乱れる、非常に見通しの悪いものになることがありますし、困ったことに、SQL::Abstactには過去の経緯からハッシュリファレンスと配列リファレンスの持つ意味を入れ替えてしまうようなオプションも用意されているため、その行を見るだけでは実際にどのようなSQL文が生成されるかわかりづらい、という弱みもあります。

SQL::AbstractはもともとDBIx::Abstractの拡張機能を捨てて生のDBIを利用することを前提につくられたのですから、SQL::Abstractでは複雑になりすぎるSQLは無理をせずそのままDBIに食わせればよいのですが、問題は、構造そのものはさほど複雑ではないけれど、OR IGNOREのようにSQL::Abstractでは直接サポートしていない機能を利用したいとき。

もちろんいちいち生成したSQLを加工してもかまいませんし、それもあわせてモデルクラスの中に隠蔽してしまえば再利用性も損なわれないのですが、プレースホルダとバインドパラメータの処理だけでよいなら、そのような処理に特化したモジュールを使ったほうが話は簡単です。

たとえば、2003年末に開発が始められたSQL::Interpolateや、そこから分岐したSQL::Interpを使うと、先ほどあげた例はこのように書き直すことができます。

use SQL::Interp ':all';

# INSERT OR IGNORE INTO foo (id, text) VALUES(?, ?)
my ($sql, @bind) = sql_interp 'INSERT OR IGNORE INTO foo', { id => 1, text => 'bar' };

UPDATEの例はこうなります。INSERTの場合とよく似た書き方ですが、SQL::Interpはある程度SQLの文脈を読んで、リファレンスを最適な形に展開できるようになっているのが特徴です。

# UPDATE foo SET id=?, text=? WHERE id=?
my ($sql, @bind) = sql_interp 'UPDATE foo SET', { id => 1, text => 'bar' }, 'WHERE', {id => 1};

長いほうの例も、ANDで接続できるパラメータのペアはハッシュリファレンスに置き換え、そうでないものはスカラーリファレンスに置き換えていけば対応できます。また、複数の候補のなかからINを使って選択する場合は配列リファレンスを使うのが簡単です。

my ($sql, @bind) = sql_interp 'SELECT * FROM foo WHERE ((', { id => 1, text => 'bar'}, 'OR (( id BETWEEN', \10, 'AND', \20, ') AND text LIKE', \'boo%', ') OR id IN', [2,3,4,5] ,'OR text is null ))';

SQL::Interpでは不要と断じて取り除かれましたが、SQL::Interpolateにはソースフィルタを使って埋め込んだ変数を展開するモジュールも同梱されています。同系統のモジュールとしては、奥一穂氏も2008年にFilter::SQLというモジュールをリリースしているので、興味のある方は試してみるのもよいでしょう。