DBIの泣き所
いわゆるLAMPないしそれに似た環境でウェブサービスばかり書いている方にはあまり実感がないかもしれませんが,
典型的なところでは,
DBIのほうでもプレースホルダやクォートの仕方についてはベンダ間の違いをなるべく吸収する努力が行われていますが,
非対称なハッシュの扱い
もちろん
たとえば,
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);
}
こうして流し込んだデータは,
my $new_sth = $dbh->prepare('SELECT * FROM foo WHERE ...');
while(my $row = $new_sth->fetch) { ... } ※1
- ※1
fetchは,
より明示的に書くならfetchrow_ arrayrefとなりますが, これは後付けの名前。この短いメソッド名からもわかるように, もともとDBIはCSVなどと同じく各行を配列 (リファレンス) に格納して処理することが前提となっていました。
ただし,
もちろんSQLのレベルで取得・
今回はデータベース話の2回目として,
DBIレベルでの解答
まずはもっとも基本的な対策として,
my $dbh = DBI->connect(...);
my $sth = $dbh->column_info(undef, undef, 'foo', undef);
my @columns = map { $_->[3] } @{ $sth->fetchall_arrayref };
なお,
my $sth = $dbh->prepare('SELECT * FROM foo LIMIT 1'); ※2
$sth->execute;
my @columns = @{$sth->{NAME_lc} || []};
- ※2
LIMITの書き方についてはお手元のデータベースにあわせて適宜読み替えてください。
また,
カラム名のリストが用意できたら,
my $sth = $dbh->prepare('INSERT INTO foo VALUES (?,?,?)');
$sth->execute(@$row_hash{@columns});
- ※3
スライスの書き方がわかりづらければ
「@$row_ hash{@columns}」 のかわりに 「map {$row_ hash->{$_}} @columns」 のように書いてもかまいません。
これでハッシュに入っているデータをデータベースに流し込めるようになったのですが,
my $placeholders = substr '?,' x @columns, 0, -1;
my $sth = $dbh->prepare("INSERT INTO foo VALUES ($placeholders)");
もう少し汎用的にするなら,
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の場合はこれでおしまいですが,
たとえば,
my $placeholders = join ',', map {"$_ = ?"} @columns;
my $sth = $dbh->prepare("UPDATE foo SET $placeholders");
$sth->execute(@$row_hash{@columns});
もちろんこのままではすべてのデータが一色に塗りつぶされてしまいますので,
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節の中身をベタ書きしましたが,
- ※4
なお,
ハッシュのスライスではなくmapを使ってスライスを作っている場合は, 後続のmapの結果が先頭のmapに食われてしまわないよう, かっこで影響範囲を明確にしてください。 $sth->execute(map({$row_
hash->{$_}} @columns), map({$row_ hash->{$_}} qw/id/));
SELECTについては,
my %row_hash;
$sth->execute;
$sth->bind_columns(\(@row_hash{@columns}));
while($sth->fetch) { ... }