それマグで!

知識はカップより、マグでゆっくり頂きます。 takuya_1stのブログ

習慣に早くから配慮した者は、 おそらく人生の実りも大きい。

XMLに検索インデックスを貼って便利に使えるPostgreSQL

XML をデータベースに入れるのが面倒

XMLをDBのテーブルスキーマに変換して、INSERT文書くのって面倒じゃん?

  • テーブルスキーマを考えるのが面倒
  • XMLの拡張性の良さが死ぬ
  • DBに入れてないデータが欠損する
  • NULL処理どうすんの

単純にSQLに変換して、データベースに突っ込むまでは、カンタンだし全然オッケーなんだ。でもXMLってそういう用途じゃないよね。

で、XMLをいつも XMLカラムに保存してるんだけど、SQLXpath使えたら便利じゃん?

なんと、PostgreSQLならSQLXpathが使える。

最初これを見つけた時に、嘘だろと思ったんですが、実際に試してみました。

postgreSQL の準備をして、いざ実験

XML 型を使う

テーブルのカラムのタイプにXML 型というそのものズバッとのタイプがあったので、それを使うことにした

CREATE TABLE sample(
 books xml
);

これで、books カラムが XML 型になる。

XMLデータを突っ込む

XML にデータを突っ込むには、ただ単に要素を書くだけ.

INSERT INTO sample(books)         VALUES ('<title>XPATH AND PostgreSQL</title>');
INSERT INTO sample(books)         VALUES ('<name>takuya</name>');

where 句で使ってみる。

where 句やselect の設定に使えるので使ってみる。

xpath(//name) というxpath が存在するレコードを取り出す。

takuya=# select *  from sample where xpath_exists('//name',books);
        books
---------------------
 <name>takuya</name>
(1 行)

同様にtitle が存在するレコードを取り出す。

takuya=# select *  from sample where xpath_exists('//title',books);
                books
-------------------------------------
 <title>XPATH AND PostgreSQL</title>
(1 行)

おお取れる取れる。

where 区でxpathを使って、マッチを調べる

基本的にはxpath_exists さえアレば戦える。

文字列 takuya を含むノードが存在スル、xml を持つレコード

select *  from sample where xpath_exists('//name[contains(.,"takuya")]',books);

同じことをxpath() では、ちょっと手間だけど、出来る。 xpath でもwhere 条件が使える。ただし配列で条件を書くらしい

select *  from sample where (xpath('//name/text()',books)::text[])  = '{takuya}';

select で使って結果を変更する。

取り出した、XMLを処理して、XMLから特定要素を取り出して便利に使いたいよね。

takuya=# select *  from sample where xpath_exists('//name',books);
        books
---------------------
 <name>takuya</name>
takuya=# select xpath('//name/text()',books)  from sample where xpath_exists('//name',books);
  xpath
----------
 {takuya}
(1 行)

出来るじゃん。

検索インデックスも貼れる

xpath の結果をインデックスにすることが出来るらしいので、ヤってみた

create index name_samle on sample using gin (( xpath('//name/text()' , books)::text[] ));

どこまでパフォーマンス出るのか、まだ大量のデータを突っ込んでないので、そのうち調べてみたい。

XSLT の適用も出来る

まぁ、ここまでくると、腐敗臭がするけど。XSLTは一度書いておけば、結構便利なので重宝するかも、でもやるんなら、サーバーサイドではなく、ブラウザにXSLT処理やらせたほうが無難だよね。。。

XSLTXMLがもう少し柔軟ならreactjs や angular や json api は存在しなかっただろうに・・・

XML データベースそこそこ使える。

XMLをデータベースに入れておけばそこそこ使えることが分かった。でもXMLそのものをインデックスしてるわけでもないし、レコードに対する付帯情報としてのXMLだから、リレーションを省略するのには便利そう。

なにより、XMLからデータを取り出してテーブルに格納するより、XMLをガンガン放り込んでいけば、後でなんとかなるっていうXML型は魅力かも。いまさらXMLだとおもうけど、JSONを手作業で編集するってありえないし、XMLは金太郎飴でどこから取り出してもXMLってのがいいんだよね。

ヤフオクAPIとかXMLの結果は、XMLカラムにガンガン放り込んじゃえば良いのではないでしょうか。

jq の独自クエリ言語を憶えるくらいなら、xpath の方が100倍マシだわ。

参考資料

PostgreSQLでXMLを処理してみよう! — Let's Postgres

PostgreSQLでXMLを処理してみよう!(第3回) — Let's Postgres

PostgreSQL: Documentation: 9.1: XML Functions

広告を非表示にする