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

※1

fetchは，より明示的に書くならfetchrow_arrayrefとなりますが，これは後付けの名前。この短いメソッド名からもわかるように，もともとDBIはCSVなどと同じく各行を配列（リファレンス）に格納して処理することが前提となっていました。

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

もちろん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} || []};

※2

LIMITの書き方についてはお手元のデータベースにあわせて適宜読み替えてください。

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

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

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

※3

スライスの書き方がわかりづらければ「@$row_hash{@columns}」のかわりに「map {$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) { ... }