PostgreSQLでXML(3): Perl DBIから使ってみよう(配列とDBI)
注: 諸事情で、今週からPostgreSQL 9.0を使うようにしたので、以下の記述は9.0ベースとなります。まあ、XMLサポートに関しては8.4と9.0にあんまり違いはないんですが(少なくとも今回の話題に関しては)。
さて、PostgreSQLでXMLを使う話、今回は本題からちょっとそれて、 Perl DBIから使う件です。
とは言っても、DBI自体の解説をしたいわけではなくて、 XMLデータを使う上でのPerl DBIの利用法の紹介をしたいと思います。 そして、PostgreSQLにとってXPathの結果は配列である以上、 DBIにおける配列の使い方と言い換えてもあまり不適切ではないでしょう。
なお、DBIハンドルは$dbで初期化できているものとします。 テーブルtextxmlは、前回定義したものと同一です。
CREATE TABLE testxml ( id SERIAL PRIMARY KEY, xmldoc XML );
INSERTする
まずはINSERTです。$xmlに入ったXML文書をINSERTしたいとすると、 次のように書くことができます。
my $xxml = $db->quote($xml); my $sth = $db->prepare(<<EOT); INSERT INTO testxml(xmldoc) VALUES ($xxml) EOT $sth->execute(); $sth->finish();
ですが、あんまりこの書き方はよろしくないですよね。 ちゃんとプレースホルダ使いましょう。
my $sth = $db->prepare(<<EOT);
INSERT INTO testxml(xmldoc)
VALUES (?)
EOT
$sth->bind_param(1, $xml, {pg_type => PG_XML});
$sth->execute();
$sth->finish();
なお、bind_param()の第三引数による型指定をきちんと書くのは私の趣味です。
前にこれを省略して、
PostgreSQL拡張型まわりで意味不明のエラーにはまったことがあるので
(XML型かBYTEA型だったような…)。
なお、この第三引数をきちんと書く場合は、use DBIの部分は、
次のようにして必要なシンボルをエクスポートする必要があります。
use DBD::Pgも省略できません。
use DBI qw(:sql_types); use DBD::Pg qw(:pg_types);
bind_param()の第三引数は、SQL標準の型の場合とPostgreSQL拡張の場合で書き方が異なります。
前者は sql_type に対して SQL_* を設定、後者は pg_type に対して PG_* を設定します。
例をいくつか示します。
$sth->bind_param(1, $int_value, {sql_type => SQL_INTEGER});
$sth->bind_param(2, $charvalue, {sql_type => SQL_CHAR});
$sth->bind_param(3, $textvalue, {pg_type => PG_TEXT});
$sth->bind_param(4, $blobvalue, {pg_type => PG_BYTEA});
XPathの結果をSELECTする
SELECTに関しては多少複雑なので、SELECTの結果としてXPathの値が帰ってくる場合と、 WHERE内にXPathが入る場合に分けて解説します。 まずはSELECTの結果としてXPathの値が帰ってくる場合です。 今回はサンプルコード全体をまず出してみましょう。
#!/usr/bin/perl
use strict;
use utf8;
use DBI qw(:sql_types);
use DBD::Pg qw(:pg_types);
binmode STDIN, ":utf8";
binmode STDOUT, ":utf8";
binmode STDERR, ":utf8";
my $db = DBI->connect
("dbi:Pg:dbname=testxml", 'pgsql', '',
{ RaiseError => 1,
PrintError => 0,
AutoCommit => 0, });
if (not $db) {
die 'Died.';
}
my $sth = $db->prepare(<<EOT);
SELECT xpath('//title/text()', xmldoc) FROM testxml
EOT
$sth->execute();
while (my @data = $sth->fetchrow_array()) {
my $title = $data[0];
if (ref $title) {
foreach my $i (@$title) {
utf8::decode($i);
print "$i\n";
}
}
}
$sth->finish();
$db->commit();
まずはprepare()する部分です。
こちらは特にbind_param()するプレースホルダもないので、
そのままですね。
XML文書中の<title>...</title>の、...の部分を取ってきています。
my $sth = $db->prepare(<<EOT);
SELECT xpath('//title/text()', xmldoc) FROM testxml
EOT
psqlから実行してみるとわかりますが、 XMLの文法上は複数の<title>タグがあっても何も不思議はないので、 返される値は配列となります。
testxml=# SELECT xpath('//title/text()', xmldoc) FROM testxml;
xpath
--------------------------------------------------------------------------------------------------------------------------
{"KOKIA/elly/aika/Key/友香「水の音 vol.11」@渋谷O-West"}
{"白鳥マイカ「陽光線-hikousen-」@ duo music exchange"}
{"『つきよみ☆ライブVol.7 最終回Sepcial〜ありがとう〜』@ cafeSANTA"}
{タテタカコ/野狐禅『狐につままれた羊』@渋谷クラブクアトロ}
...(略)...
従って、DBI側でも配列で受け取らなければなりません。 これはDBIが結構賢くて、配列で結果が返された場合、 検索結果は配列への参照として返されてくるのです。 そのため、次のようなコードでデータを受け取ることができます。
while (my @data = $sth->fetchrow_array()) {
my $title = $data[0];
if (ref $title) {
foreach my $i (@$title) {
utf8::decode($i);
print "$i\n";
}
}
}
fetchrow_array()で取ってきたデータの最初の要素が配列へのリファレンスとなっているので、
その中身を取り出すのは、Perlのリファレンスの使い方に慣れていれば簡単ですよね。
ただし、UTF-8フラグは落ちているので、
場合によってはutf8::decode()してあげる必要があります。
実行例は次のようになります。
% ./testxml2.pl KOKIA/elly/aika/Key/友香「水の音 vol.11」@渋谷O-West 白鳥マイカ「陽光線-hikousen-」@ duo music exchange 『つきよみ☆ライブVol.7 最終回Sepcial〜ありがとう〜』@ cafeSANTA タテタカコ/野狐禅『狐につままれた羊』@渋谷クラブクアトロ ...(略)...
では、XPathの結果を加工して、 配列ではないものが帰ってくるようにした場合を考えてみます。たとえば上記のSQLを、
SELECT (xpath('//title/text()', xmldoc)::text[])[1] FROM testxml
にすると、XPathの返してくる値をtext型配列にキャストして、 その一番最初の要素をSELECTする形になっています。 従って、帰ってくる値はtext型のスカラーです。
この場合は、次のようにスカラーとして普通にfetchすることができるわけです。 実行結果は同じです(複数の<title>が同一XML文書中にない限り)。
my $sth = $db->prepare(<<EOT);
SELECT (xpath('//title/text()', xmldoc)::text[])[1] FROM testxml
EOT
$sth->execute();
while (my @data = $sth->fetchrow_array()) {
my $title = $data[0];
utf8::decode($title);
print "$title\n";
}
$sth->finish();
$db->commit();
XPathの結果でWHERE条件を作る
では、今度はWHERE条件の中にXPathが入った場合です。
XPathとの比較は基本的には配列の比較となるので、
今度はbind_param()で配列を渡す必要があります。
もちろん、次のような条件にすれば、bind_param()ではスカラーとの比較に出来るのですが、前回紹介したように、
このようなWHERE条件にはインデックスが効かないのでお勧めできません。
SELECT id WHERE
(xpath('//title/text()', xmldoc)::text[])[1] = 'タイトル名'
インデックスが効く、配列集合の包含関係による演算は以下のとおりです。 なお、包含関係の演算子「@>」を等号「=」にしてもインデックスを用いた検索ができますし、 この場合は意味も変わらないのですが、 私の経験則として、「=」より「@>」の方がずっと高速です。
SELECT id WHERE
xpath('//title/text()', xmldoc)::text[] @> '{タイトル名}'
いずれの場合も、比較対象が、'タイトル名'ではなく、'{タイトル名}'のように、 配列としてエスケープされていることに注目してください。
では、これを用いたクエリーの例です。 本サイトのベースとなっているXMLでは、 年月日と場所がそれぞれ、<year>, <month>, <day>, <place>というエレメントに記述されているので、 それもSELECTしてきてみましょう。
my $searchtitle = 'テレビを買いに行く前にソンタナ';
my $sth = $db->prepare(<<EOT);
SELECT id,
(xpath('//year/text()', xmldoc)::text[])[1],
(xpath('//month/text()', xmldoc)::text[])[1],
(xpath('//day/text()', xmldoc)::text[])[1],
(xpath('//place/text()', xmldoc)::text[])[1]
FROM testxml
WHERE xpath('//title/text()', xmldoc)::text[] @> ?
EOT
$sth->bind_param(1, [$searchtitle], {pg_type => PG_TEXTARRAY});
$sth->execute();
while (my @data = $sth->fetchrow_array()) {
my $id = $data[0];
my $year = $data[1];
my $month = $data[2];
my $date = $data[3];
my $place = $data[4];
utf8::decode($id);
utf8::decode($year);
utf8::decode($month);
utf8::decode($date);
utf8::decode($place);
print "$id, $year/$month/$date: $place\n";
}
$sth->finish();
実行結果は次のようになり、この記事が検索されてきます。
% ./testxml4.pl 1436, 2010/10/17: 経堂
ここではTEXT型の配列をbind_param()に渡しているのですが、
この場合の文法は次のようになります。
$sth->bind_param(1, [$searchtitle], {pg_type => PG_TEXTARRAY});
検索タイトルの入った$searchtitleを括って、
[$searchtitle]とすることで、無名配列リファレンスを作っています。
これは、fetchの結果が配列のリファレンスで返されてきたのと同様です。
そしてbind_param()の第三引数は、{pg_type => PG_TEXTARRAY}とすることで、
PostgreSQL拡張のTEXT型の配列として、bind_param()に指定されています。
ちなみに配列型のシンボルとしては他にも、PG_INT4ARRAYとかPG_CHARARRAYとかいろいろありますが、XMLを使うのに必要となるのはPG_TEXTARRAYだけでしょう。必要であれば定義はDBD/Pg.pm中にあるので、それを参照すればよいでしょう。
あとは基本的にはこれらの例の応用で、クエリーを書くことができます。
注意: XPathのエスケープとXPathインジェクション
XPath中にも、何らかの文字列を与えて条件検索を行う場合があります。
たとえば、次の例を見てみます。
本サイトのXMLでは、<amazonlink asin="...">というタグで、
与えられたASIN番号のAmazon.co.jpへのリンクを作成できるようになっています。
SELECT id,
xpath('//amazonlink[@asin="B002HGB1CG"]/text()', xmldoc)::text[]
FROM testxml
WHERE xpath('//tag/text()', xmldoc)::text[] @> '{ホームベーカリー}'
この例では、<tag>に「ホームベーカリー」が指定されているXML文書で、
<amazonlink asin="B002HGB1CG">のリンクがされている文字列の部分を全て検索するものです。実行結果は次のようになります。
testxml=# SELECT id,
testxml-# xpath('//amazonlink[@asin="B002HGB1CG"]', xmldoc)::text[]
testxml-# FROM testxml
testxml-# WHERE xpath('//tag/text()', xmldoc)::text[] @> '{ホームベーカリー}';
id | xpath
------+-------------------------------------------------------------------------
893 | {"「Panasonic ホームベーカリー シルバーホワイト SD-BMS101-SW」",Amazon}
898 | {SD-BMS101-SW}
899 | {SD-BMS101-SW}
903 | {ホームベーカリー(SD-BMS101-SW)}
912 | {}
916 | {うちのホームベーカリー,ホームベーカリー}
928 | {}
933 | {}
948 | {}
949 | {}
950 | {}
975 | {パナソニックのSD-BMS101-SW}
976 | {}
979 | {}
1006 | {"Panasonic SD-BMS101-SW"}
1007 | {}
1019 | {}
1021 | {}
1026 | {ホームベーカリー}
1035 | {}
1052 | {}
1061 | {ホームベーカリー}
1080 | {}
1189 | {}
1275 | {}
1535 | {}
1467 | {}
1497 | {}
(28 行)
たとえば最初のid=893番の記事はこちらですが、該当部分のXMLは次のようになっています。
...(略)... 買ったのは、パナソニックの新製品 <amazonlink asin="B002HGB1CG">「Panasonic ホームベーカリー シルバーホワイト SD-BMS101-SW」</amazonlink> です。 ...(略)... で、調べた限りでは<amazonlink asin="B002HGB1CG">Amazon</amazonlink>が安かったので、 ...(略)...
空集合の検索結果が多いのは、まずWHEREで検索した結果の反映としてidのデータが検索され、その絞られたxmldocをさらにXPathで検索した結果が右側に表示されているためです。
で、肝心の問題は、このXPath内の文字列の、簡便なエスケープ手段が見つからないんですよ。SQLにおけるプレースホルダみたいな機能が希望なんですが。 検索してみるとこんな質問もあったりして…(これも結局本質的に解決していない)。
ということで、ASINなら文字集合も分かっているからいろいろやりようがあるんですが、 ユーザから入力された文字をXPathに加えるなら、XPathインジェクションの危険性がかなりあるでしょう。何か簡便な手段があれば、一番上の「ツイートする」ボタンあたりから教えていただけるととても嬉しいです。
基本的には、「\」→「\\」, 「'」→「\'」, 「"」→「\"」すればいいのかも知れないけどなんか不安。