Symfoware

Symfowareについての考察blog

PostgreSQL + mysql_fdwでMySQLに仮想的なxml列を実現する

mysql_fdwをインストールしてみました。
mysql_fdwで、PostgreSQLからMySQLのデータベースに接続する

ふと、
「MySQLのtext列にあるxmlな文字列を、PostgreSQLのxmlとして扱えるのか?」
と思ったので試してみます。


PostgreSQLでxml列の操作は、過去試したことがあるのでこれを参考にしました。
PostgreSQLのXML列にJavaからデータを登録、取得する
PostgreSQLのXML列をJavaから検索する



MySQL側での準備



適当に、idとtext列を持つテーブル「xml_test」を作成します。


  1. create table xml_test(
  2. id int not null primary key,
  3. address text);




事前にサンプルデータを投入しておきました。


  1. insert into xml_test(id, address) values (1,
  2. '<doc>
  3.     <name>test1</name>
  4.     <value type="string">test1_str</value>
  5.     <value type="int">1</value>
  6. </doc>');
  7. insert into xml_test(id, address) values (2,
  8. '<doc>
  9.     <name>test2</name>
  10.     <value type="string">test2_str</value>
  11.     <value type="int">2</value>
  12. </doc>');
  13. insert into xml_test(id, address) values (3,
  14. '<doc>
  15.     <name>test3</name>
  16.     <value type="string">test3_str</value>
  17.     <value type="int">3</value>
  18. </doc>');






PostgreSQL側での準備



MySQLで作成したテーブルとリンクします。
この時、MySQL側ではデータ型を「text」とした列を、
データ型「xml」として作成しました。


  1. CREATE FOREIGN TABLE xml_test(
  2. id int,
  3. address xml)
  4. SERVER mysql_server
  5. OPTIONS (dbname 'test', table_name 'xml_test');






検索テスト



普通に検索。


  1. SELECT * FROM xml_test



647_01.png


nameタグの値だけ検索


  1. SELECT xpath('/doc/name/text()', address)
  2. FROM xml_test



647_02.png

動きました。正直、動かないと思っていたので驚きました。



「value」のアトリビュート「type」を取り出してみます。


  1. SELECT
  2. xpath('/doc/value/@type', address)
  3. FROM xml_test



647_03.png


「value」のアトリビュート「type」が「string」の値を取り出します。


  1. SELECT
  2. xpath('/doc/value[@type="string"]/text()', address)
  3. FROM
  4. xml_test



647_04.png


普通に使えて驚きました。




注意点




PostgreSQLからインサートしようとするとエラーになります。


  1. insert into xml_test(id, address) values (4, '<doc></doc>');




ERROR: cannot convert constant value to MySQL value
SQLステート:HV004
ヒント:Constant value data type: 142





MySQLのtextフィールドにxmlではない、空白などのデータがあると
検索エラーです。


・MySQL


  1. insert into xml_test(id, address) values (4, '');




・PostgreSQL


  1. SELECT
  2. xpath('/doc/value[@type="string"]/text()', address)
  3. FROM
  4. xml_test




ERROR: invalid XML content
SQLステート:2200N




いい線ついてると思ったのですが、使用できる局面は少ないかも。

関連記事

テーマ:サーバ - ジャンル:コンピュータ

  1. 2015/10/12(月) 19:52:42|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<Python 正規表現でマッチした部分の文字列を取得する(finditer) | ホーム | mysql_fdwで、PostgreSQLからMySQLのデータベースに接続する>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1799-4b67b6c1
この記事にトラックバックする(FC2ブログユーザー)