Hatena::ブログ(Diary)

日々の記録 別館

2012-04-26

xml_fdwふたたび

もしかすると次回のJPUG勉強会で進捗を聞かれるかもしれないので、そろそろ準備をしておかないとまずいか。

TODO再整理

とりあえずTODOを再整理しておこう。

  • ソースの公開
    • githubとかでいいのかな?こういうのやったことないから・・・
  • XPathがヒットしないときにNULL値を設定する処理。
  • 属性対応。
    • NodeType判定+αの処理を追加。
  • 名前空間への対応
    • xpathとセットでnamespace(prefix, url)リストを渡す?
    • 複数対応が必要。
  • カラム取得用XPathで複数ヒットしたときの扱い。とりあえず3案?
    • 複数ヒットしたときはエラー
    • 先頭1個のみを取得
    • 全て連結したテキスト
    • 配列で返却するというオプションがあってもいいのかも?
  • XML文書のデータソース指定の柔軟化。
    • URL指定、XMLデータベースへの拡張など。
    • libxml2のDOMパーサがURL受け付けてくれると早いのだが。
  • カラム取得用XPath生成に使う情報の定義
    • 現状は、CREATE FOREIGN TABLEのOPTIONでXPath式を直接指定。
    • カラム名・型定義情報から本当は取得したいところ。
    • 面倒なのでたぶんやらない。
  • XPathコンテキストのキャッシュ(生成コスト削減)
  • WHERE句のXPath-pushdown。
    • かなり限定された範囲ならpushdown可能?
  • XML文書に対するコスト計算・EXPLAIN仕様の検討
    • これはすぐには無理。というか熱意がない。
  • XQuery対応
    • libxml2では対応していないから、別のC/C++ XMLプロセッサでの書き換えがたぶん必要。
  • XSLT対応
    • XQuery対応よりは難易度低い?(libxml2ベースで開発できそう)

利用イメージ

何か利用イメージを考えると改造する意欲が湧いてくるかも。

  • RSSをPostgreSQLで読み込む
    • 既にRSS-FDWはあるっぽいけど。
    • 名前空間対応、URL指定対応が出来ればOKのはず。
    • 最悪、curlでRSSをダウンロードして読み込むか・・・?
  • XHTMLページをPostgreSQLで読み込む?

2012-04-25

Windows版PostgreSQL+pgAdmin3のロケール

久々にWindows版のPostgreSQL+pgAdmin3を使ってデータベースを生成したときに気づいたんだけど、何も考えずにデータベースを生成するとロケール(照合順序)がCではなくJapanese.Japan.932(SJIS?)になるのな。
ロケールをCで作成したい場合には、作成時に「定義」タブに移動して、Templateとしてtemplate0を選択し、その上で「コーレーション」や「文字型」をCに設定しないといけないのか。ちょっと面倒。

f:id:nuko_yokohama:20120425114047j:image

なお、template0を指定しないと(たぶんtemplate1のロケールがJapanese.Japan.932になっているから)、作成時に元のテンプレートと照合順序が違うというエラーで怒られてしまう。

2012-04-18

plpgsql関数の中でplpgsql関数を作成する

背景

今まであんまり真面目にplpgsqlを使ったことがなかったんだけど、今やっている仕事の一環として、デバッグ用のスクリプトをplpgsqlで組んでいて、ちょっと困ったことになっていた。

  • 構造は同じなんだけど、テーブル名が異なるテーブル群に対して、1つの関数でテーブル名を指定して検索したい。
  • こういう場合は関数内部でクエリ文字列を動的に生成して、EXECUTEで実行しないといけない。
  • しかし、どうやら動的に生成したSQL(検索結果は複数行になる)の結果をうまく返せない。

解決案?

ということで、ちょっと考えてみたのだがplpgsqlの中では動的にSQLコマンドを生成して実行できるわけだから、CREATE FUNCTIONの中でCREATE FUNCTIONコマンドを記述して、動的に関数を生成し、その関数を実行出来るんじゃないかと思えてきた。
で、その動的に生成する関数内では静的なSQLとしてSELECT文を記述(実行時に決定するテーブル名なんかは文字列を生成するときに参照して生成し、生成したSQLの形としては静的なものにする)する。
生成した関数を実行し、その結果をQUERY RETURNで呼び元に返却してあげればいいんじゃないかと。

関数定義例

例えば、構造が同じ複数のテーブル(log_201204, log_201205, log_201206みたいなテーブル)があるとして、これに対して「テーブル名」「整数の検索条件値」を与えて検索するような関数を定義してみる。

CREATE OR REPLACE FUNCTION search_log(table_name text, value integer) RETURNS TABLE (data text) AS
$$
-- 指定したtable_nameに対してvalueと等しい行を取得する。
DECLARE
cf_sql text; -- CREATE FUNCTION用SQL
df_sql text; -- DROP FUNCTION用SQL
BEGIN

-- メモ:plpgsqlの制約(動的SQL実行による複数行返却不可)回避
-- 一時的にCREATE FUNCTIONで関数を作成
cf_sql := 'CREATE OR REPLACE FUNCTION tmp_search_log() RETURNS TABLE (data text) AS $_$ SELECT data FROM ' || table_name || ' WHERE value = ' || value || ' $_$ LANGUAGE sql;';
EXECUTE cf_sql;

RETURN QUERY SELECT tmp_search_log() ;

df_sql := 'DROP FUNCTION tmp_search_log()';
EXECUTE df_sql;

RETURN;
END;
$$ LANGUAGE plpgsql;

この関数(search_log)の中で、CREATE FUNCTION文のテキストをパラメータから生成し(cf_sql)、それをEXECUTEで実行して tmp_search_log という関数を登録する。
で、直後に RETURN QUERY SELECT tmp_search_log() で関数を実行し、その関数結果をRETURN QUERY でsearch_log関数の呼び元へ返却し、生成した tmp_search_log 関数は削除する。

実験

一応、動作を確認してみた。

INSERT INTO log_201204 VALUES( DEFAULT, generate_series(1,100) % 10, 'data' || ( (generate_series(1,100) % 10) )::text );
INSERT 0 100
INSERT INTO log_201205 VALUES( DEFAULT, generate_series(1,50) % 10, 'data' || ( (generate_series(1,10) % 10) )::text );
INSERT 0 50
INSERT INTO log_201206 VALUES( DEFAULT, generate_series(1,100) % 10, 'data' || ( (generate_series(1,100) % 10) )::text );
INSERT 0 100
SELECT search_log('log_201204', 5);
search_log
------------
data5
data5
data5
data5
data5
data5
data5
data5
data5
data5
(10 rows)

SELECT search_log('log_201205', 4);
search_log
------------
data4
data4
data4
data4
data4
(5 rows)

うむ、一応うまく動いたみたいだ(valueと同じ値の行をのdataを返却している。dataはvalueの数値と同じ数字を"data"の後に付与した文字列を設定している)。

2012-04-16

特定のルート要素名と名前空間をもつXMLを特定する

要素名や名前空間を条件にしたいとき

xml型に任意のスキーマを持つ(ルート要素も異なる)文書を放り込むような使い方をする場合には、ルート要素が何かというのを特定しなくちゃいけないが、要素名そのものや名前空間を条件にするのは、ちょっと面倒だったりする。
PostgreSQLxml型にXMLを放り込んでxpath()で検索というのはよく行うけれど、そういう場合って要素の値や属性値を条件にすることが多い。

やり方

要素名や名前空間そのものは、xpath関数のlocal-name()でローカル名(要素名)を、namespace-uri()で名前空間URIを取得できるんだけど、これをXPathの述語として与え、その評価結果が空値でないかで判断しなくちゃならない。
例によって、xpath()がxml配列を返却するから、それをtext型配列にcastして、その先頭要素を取り出してリテラルと比較する。xpathの述語が真なら空値じゃない値が返却されるので、それをWHERE句に記述することになる。書いているだけでも面倒くさい・・・。

xmldb=# \d xml_test
Table "public.xml_test"
Column | Type | Modifiers
--------+------+-----------
data | xml |

xmldb=# INSERT INTO xml_test VALUES ('<r:root xmlns:r="http://hoge.hoge"><a><b>B1</b><c>C1</c></a></r:root>');
INSERT 0 1
xmldb=# INSERT INTO xml_test VALUES ('<r:nuko xmlns:r="http://hoge.hoge"><a><b>B1</b><c>C1</c></a></r:nuko>');
INSERT 0 1
xmldb=# INSERT INTO xml_test VALUES ('<r:root xmlns:r="http://foo.bar"><a><b>B1</b><c>C1</c></a></r:root>');
INSERT 0 1
xmldb=# select * from xml_test;
data
-----------------------------------------------------------------------
<r:root xmlns:r="http://hoge.hoge"><a><b>B1</b><c>C1</c></a></r:root>
<r:nuko xmlns:r="http://hoge.hoge"><a><b>B1</b><c>C1</c></a></r:nuko>
<r:root xmlns:r="http://foo.bar"><a><b>B1</b><c>C1</c></a></r:root>
(3 rows)

xmldb=#

で、これに対して以下のようにSELECTしてみる。
xmldb=# select data from xml_test WHERE (xpath('/*[local-name() = "nuko" and namespace-uri() ="http://hoge.hoge"]', data )::text[])[1] <> '';
data
-----------------------------------------------------------------------
<r:nuko xmlns:r="http://hoge.hoge"><a><b>B1</b><c>C1</c></a></r:nuko>
(1 row)

xmldb=# select data from xml_test WHERE (xpath('/*[local-name() = "root" and namespace-uri() ="http://hoge.hoge"]', data )::text[])[1] <> '';
data
-----------------------------------------------------------------------
<r:root xmlns:r="http://hoge.hoge"><a><b>B1</b><c>C1</c></a></r:root>
(1 row)

xmldb=# select data from xml_test WHERE (xpath('/*[local-name() = "root" and namespace-uri() ="http://foo.bar"]', data )::text[])[1] <> '';
data
---------------------------------------------------------------------
<r:root xmlns:r="http://foo.bar"><a><b>B1</b><c>C1</c></a></r:root>
(1 row)

xmldb=#

一応、所要の動きになってくれた。DELETE/UPDATEも同じようにできるはず。

追記

  • しかしこれだと文書数が多くなったときに関数インデックスが使えないという問題が・・・。

  • まあ、関数インデックスを使わなくてもルート要素名と名前空間URIxml格納カラムとは別に持てばいいのだろうけど、なんかそれは負けた気がする。
  • ルート要素名を返す関数名前空間URIを返すC関数を作ってCREATE FUNCTIONで組み込むしかないのかなあ。それはそれで面倒な話。
Connection: close