第35回　DBI：生のSQLが散らばると言う前に

Perlとdbm

いまでは省みられることも少なくなりましたが、Perlには1989年にリリースされたバージョン3.0以降、dbmと呼ばれるシンプルなデータベースにアクセスする機構が標準で組み込まれています。このdbmは、いわゆるリレーショナルデータベースとは違ってキーと値の組み合わせをディスクに保存できるだけのものですが、ハッシュ（当時はまだ連想配列と呼んでいました）と結びつけることでタブ区切りファイルなどを読んでいくより高速に検索ができたため、ユーザ環境に永続的なデータを保存する手段のひとつとして重宝されていました。Perl 3／4の時代にはdbmopenというコマンドが使われていましたが、この機構はPerl 5になって一新され、いまではより汎用的なtieというコマンドを使うことになっています。この仲間としては古くからあるBerkeley DBやGDBMなどのほか、最近では平林幹雄氏のTokyoCabinetやKyotoCabinetも同様に扱えるようになっているので、そちらでおなじみの方もいるかもしれません。

use strict;
use warnings;
use DB_File;

tie my(%hash), "DB_File", "test" or die $!;
$hash{test} = "hello world";
untie %hash;

tie my(%new_hash), "DB_File", "test" or die $!;
print $new_hash{test}; # hello world
untie %new_hash;

DBperlとDBI

dbmは、使いどころによっては便利なものですが、本格的なデータベースのかわりになるものではありません。そのため、バージョン3.0のリリースから半年ほどたった1990年8月には、より本格的なデータベースにも対応できるよう、Perlをコンパイルする際にユーザが自分の好きなライブラリを組み込んで使えるようにする仕掛けが用意されました。この仕掛けを利用することで、Oracleのクライアントライブラリを埋め込んだOraperlや、Sybaseを扱えるようにするSybperl、Interbase対応がなされたInterperlといった派生品が生まれ、Perl本体とは別に維持・公開されていくようになったのですが、以前にも紹介したように、これらは個々のデータベースの特徴に強く依存していたため、さまざまな問題を引き起こすことにもなりました。

たとえば、諸般の事情でOracleのデータベースとSybaseのデータベースに同時にアクセスしなければならなくなったとしましょう。当時、特定のデータベースクライアントに紐づけられたPerlではほかのデータベースにアクセスできないのがふつうでしたから、両方にアクセスしたいと思ったら、まずはそれぞれに専用のPerlを用意する必要がありました。

また、Oraperlではデータベースの接続に「&ora_login($system_id, $username, $password))」というコマンドを利用しますが、Sybperlでは同じ用途に引数の順序が異なる「&dblogin($user, $pwd, $server)」というコマンドを使うとか、SQL文を用意する際も、Oraperlの場合はふつう「&ora_open($lda, $statement)」から「&ora_bind($csr, $var, ...)」と続けるのが、Sybperlでは「&dbcmd($dbproc, $str)」になるといった具合に、種々のインタフェースがデータベースごとに異なるため、開発や保守、学習にかかるコストが高くなるという問題もありました。

そのような状況を改善するため、1992年にはMicrosoft社がODBC（Open Database Connectivity）と呼ばれるリレーショナルデータベースにアクセスするための共通インタフェースをリリースし、SQL業界でもメジャーバージョンアップとなるSQL92の制定が行われたのですが、Perlの世界でもバズ・モシェッティ（Buzz Moschetti）氏らを中心にデータベースまわりの問題を議論するユーザグループが生まれ、インタフェースを統一するための話し合いが始まりました。

その成果は、当初はDBperlの名でまとめられていましたが、Perl 5のアルファ版が登場すると、それまでの関数群は新しいオブジェクト指向プログラミング風の書き方にあらためられ、Perl 5.000誕生前夜の1994年10月12日には最初のPerl 5用DBIモジュールが関係者向けのメーリングリストに公開されました。これはすぐにPerl 5のコアにも取り入れられ、その直後にリリースされたPerl 5.000gammaにはDBIも同梱されていたそうですが、プロジェクトの全体的な進捗状況も考慮した結果、作者のティム・バンス（Tim Bunce）氏は最後の最後でDBIをPerl 5.000のコアから外してもらうことを決断。惜しまれながらもコアから外れたDBIは、ラリー・ウォール氏には「あとから追加してもいいんだし」と言ってもらっていたものの、かれこれ15年がたったいまもPerlのコアモジュールには戻っていないのですが、コアに入っていようがいまいが、DBIがPerl界のデファクトスタンダードとして確固たる地位を築いていることはみなさんよくご存じの通り。いまではどんな環境であれ、Perlが入っていてデータベースが使えることになっているならDBIも入っていると期待してもよいでしょう。

DBIはPerl界標準のインタフェース

DBIという名前はDatabase Interfaceの略語とされていますが、この名前からもわかるように、DBIは個々のデータベースと直接やりとりするためのモジュールではありません。さまざまなデータベースに対応することを念頭において書かれた汎用的なインタフェースを定義・実装するためのモジュールです。

個々のデータベースエンジンに固有の部分は背後のデータベースドライバ（DBD::で始まる一連のモジュール群）がよしなに計らうように定められているため、一般的なプログラマは裏で何が起こっているかを気にする必要はありません。DBIが定義する共通インタフェース（API）さえ押さえておけば、OracleだろうがSybaseだろうが、あるいはMySQLだろうがPostgreSQLだろうが、原則的には（呼び出すドライバを切り替えるだけで）同じアプリケーションをそのまま使い回せることになっています。そのため、プロトタイプの開発にはフリーのデータベースを利用し、本番に近い環境での検証やサービス展開時にはより高価な製品に切り替えるといったことも建前上は簡単にできますし、実際、多くのデータベース関連モジュールはこの性質を利用して、基本的なテストは取り扱いの簡単なSQLiteなどで済ませ、より複雑な、あるいは特定のデータベースでしか動かない部分については、専用のテスト環境にアクセスするための情報を要求する（なければテストをスキップする）といった手法を採用しています。

ODBCとDBIの違い

もちろん同じようなことはODBCなどでもできます。

たとえば、データベースからidが1の行を取ってくる例を見てみましょう。生のODBCに近い例としてWin32::ODBCというモジュールを使うと、システムにsample_dbという名前で登録したデータソースから、idが1の行を取得できます。対応するドライバがインストールされていれば、データソースはAccessのデータベースであろうとExcelのシートであろうとタブ区切りのテキストであろうと、アプリケーション側で気にする必要はありません。

use strict;
use warnings;
use Win32::ODBC;

my $db = Win32::ODBC->new('sample_db');
$db->Sql('SELECT * FROM foo WHERE id = 1');
while($db->FetchRow) {
    my %row = $db->DataHash;
    print $row{id}, "\n";
}
$db->Close;

DBIの場合も、基本的には同じようなAPIで記述できます。ここでは比較のためにODBCのドライバを利用してみましたが、DBI->connect()の引数を変えれば特定のデータベース用に調整されたドライバを利用することもできます。

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:ODBC:sample_db');

my $sth = $dbh->prepare('SELECT * FROM foo WHERE id = ?');
$sth->execute(1);
while (my $row = $sth->fetchrow_hashref) {
    print $row->{id}, "\n";
}
$sth->finish;
$dbh->disconnect;

ただし、PerlのDBIができることはこれだけではありません。

たとえば、細かな制御が必要ないのであれば、SQLの準備と取得をひとまとめにしたAPIを使うこともできます。

my $row = $dbh->selectrow_hashref('SELECT * FROM foo WHERE id = ?', undef, 1);
print $row->{id}, "\n";

このコードは先頭1行のみを取る例ですが、該当するすべての行を一度に取得することもできます（この場合は一般的にselectall_hashrefではなくselectall_arrayrefを使うほうが扱いが簡単になることも覚えておいて損はないでしょう⁠）⁠。

my $rows = $dbh->selectall_arrayref('SELECT * FROM foo WHERE id = ?', { Slice => {} }, 1);
for my $row (@$rows) {
    print $row->{id}, "\n";
}

速さが気になる場合は、作成に時間のかかるハッシュリファレンスではなく、カラムに変数をバインドすることで速度を稼ぐこともできます。

my $sth = $dbh->prepare('SELECT * FROM foo WHERE id = ?');
$sth->bind_columns(\my $id, \my $name, \my $address);
$sth->execute(1);
while ($sth->fetch) {
    print $id, "\n";
}

DBIの便利機能

DBIにはほかにも高速化のためにコネクションなどをキャッシュする機構や、プロファイリングやトレーシングといった開発を支援する機構が標準で用意されています。

たとえば、大規模サービス向けではありませんが、永続的な環境でデータベースの接続を張りっぱなしにしておきたいときは、connectのかわりにconnect_cachedを使うと、可能な限り同じ接続を使い回せるようになります（データベースの接続が切れても必要に応じて接続を張り直してくれます⁠）⁠。

my $dbh = DBI->connect_cached(...);

また、DBIに同梱されているDBD::Goferを使うと、いまのところトランザクションなどに制約があるものの、リモートのデータベースにアクセスする際にロードバランサやキャッシュなどの仕掛けを加えられるようになります（CPANにはあがっていませんが、Gearmanを使って転送するためのモジュールもあるようです⁠）⁠。

特定のSQLの実行にかかった時間を知りたい場合は、同じくDBIに同梱されているDBI::Profileが使えます。単純なスクリプトで、実行するSQL文もそれほど多くないなら、DBI_PROFILE環境変数に適切な値を指定しておけば実行後にプロファイリングの結果が表示されます。

> DBI_PROFILE=2 perl foo.pl

特定のSQLだけプロファイリングしたい場合は、そのSQLを実行するハンドルのProfileアトリビュートに値を指定するか、

my $dbh = DBI->connect(...);
$dbh->{Profile} = 6;
my $rows = $dbh->selectall_arrayref(...)
$dbh->{Profile} = 0;

山口徹氏作のDBIx::ProfileManagerを利用するとよいでしょう。

my $dbh = DBI->connect(...);
my $pm = DBIx::ProfileManager->new;
$pm->profile_start;
my $rows = $dbh->selectall_arrayref(...)
$pm->profile_stop;
local $, = "\n";
print $pm->data_formatted;

また、デバッグの際に便利なオプションとして、最近のDBIでは接続時にShowErrorStatementというアトリビュートを渡しておくと、エラーが発生したときに問題を起こしたSQLを表示できます。

my $dbh = DBI->connect('dbi:SQLite::memory:', '', '', {
  RaiseError => 1,
  ShowErrorStatement => 1, 
});

ほかにも、エラー処理まわりに手を入れたり、各種のメソッドにコールバックをかませたりと、詳しく解説していくとそれだけで本が一冊書けてしまうほどDBIには多くの機能が用意されています。DBI::FAQにも書いてあるように、DBIは、ODBCなどに比べるとはるかに高レベルなところまで面倒を見てくれるインタフェースであるといってよいでしょう。

DBIの課題

もっとも、リリース当時に比べればDBIもずいぶんと拡張されてきたとはいえ、データベースまわりの仕様や標準もこの15年ほどの間にずいぶん変化しました。SQLひとつとってみても、DBIの検討が始まった当時のSQL92から、1995年のSQL/CLI、1996年のSQL/PSM、そしてSQL1999、SQL2003、SQL2008と拡張が続けられてきましたし、MicrosoftのODBCにしても、RDO（Remote Data Objects）やODBCDirect、あるいはADO（ActiveX Data Objects）のように、その時々の問題意識に沿った形でさまざまな拡張や後継となるAPIが登場しています。当然DBIにも未解決の課題が見られるようになっていきます。

そのため、DBIが10歳を迎えた2004年には新たにDBI::Roadmapというファイルが用意され、今後の拡張予定が共有されました。そのなかにはスレッド対応や非同期インタフェースの定義といったパフォーマンスを改善するためのものから、各種ドライバの品質を高めるための共通テスト、既存のDBD::MultiplexやDBD::Proxyといった可用性を高めるためのツールの改善、統一的なUnicode対応、バッチ処理対応、拡張性の向上、デバッグ支援機構の改善、移植性の改善、知財問題の対処といったものまで、今後も安心してDBIを使っていくうえでの大きなポイントとなりそうな項目が列挙されています。

また、このドキュメントにはParrot、Perl 6の時代を見据えて、ほかの軽量言語でも共通して使えるDBDI、DataBase Driver Interfaceの制定を検討していることも記されています。当時はPerl 5のDBIをベースにすることになっていましたが、これはその後実績もありユーザ数も多いJavaのJDBCを参考にする方向で話が進んでいます。ティム・バンス氏のブログに最近java2perl6apiプロジェクトの概要について説明された記事が掲載されているので、未見の方はあわせてご覧ください。昨今のPerl界が新しいユーザ層の獲得先をどこに求めようとしているかという点でも興味深い記事になっていると思います。

素のDBIを使うことが悪いのではありません

DBIは、ベンダごとに異なっていたデータベースまわりのインタフェースを共通化することで移行コストや学習コストを軽減できることがメリットだったのですから、本来はDBIのインタフェースを使ってアプリケーションを書くことは、推奨されるべきことではあっても、けなされるべきことではありません。

とはいえ、DBIはかなり低レベルなところまで手を入れられるようになっているため、インタフェースとしては冗長な部分もありますし、SQLも、かなりのところまで標準化されているとはいえ、やはりベンダごとに違う部分は残っています。DBIにも最近はDBI::SQL::Nanoという最低限のSQLを解釈するためのモジュールが同梱されていますが、DBI::Roadmapにも書いてあるように、SQLのすべての方言をDBIのレイヤーで吸収することはできません。本当に移植性を考慮する必要があるのなら、もう少し上のレイヤーでSQLの差違を吸収してやる必要はありますし、一般的に言って、データベースを扱う部分は何らかのモデルクラス（群）にまとめてやるのがベストプラクティスです。

ただし、素のHTML片が散らばっているウェブアプリケーションはよろしくない、というのと同じ理屈で、素のSQL片が散らばっているアプリケーションはよろしくない、ということはできますが、素のHTML片をCGI.pmが提供するヘルパー関数群に置き換えたからといって本質的な解決にはならないのと同じ理由で、単にSQLをPerlコードで隠蔽したからといってふつうは本質的な解決にはなりません。

CPANにはDBIをベースにしたモデルクラスをつくるときに便利に使えるツールがいくつも公開されていますし、そのうちのいくつかについては次回以降の記事で取り上げていきますが、このようなツールにも、インタフェースが統一されていないため移行コストが高くなるとか、オブジェクトの生成などに時間がかかるため、ものによっては体感できるほど処理が重くなる、実際に発行されるSQLが隠蔽されてしまうため、効率の悪いSQLを発行していても気づかない、あるいは、Perlにはあまり詳しくないデータベースの専門家の支援を受けづらくなる、といったトレードオフは存在します。小さなスクリプトくらいであればDBIを使って生のSQLを書くほうがメンテナンス性が高くなる、ということも十分にありえますので、適材適所を心がけていただければと思います。