2011-12-10

このエントリは、PostgreSQL Advent Calendar : ATNDの12/10分です。

今回はPostgreSQLの型拡張機能の一つである、ドメイン機能を使って何が出来るのかを簡単に紹介したいと思います。
と、その前に・・・

データの制約

当たり前のことですが、RDBMSの列には「データ型」というものが設定されています。でも、データ型だけでは厳密な制約を課すことができないケースもありえます。

ex. integer型というだけでは、その列に「負数は論理上入らない」という制約はつけられない。
ex. text型というだけでは、その列に(電話番号のように)「数字とハイフンしか入らない」という制約はつけられない。

こうした意味的なチェックというのはアプリケーションでも勿論行えますが、データベースの機能としても存在しています。

PostgreSQLで使える制約

もちろん、PostgreSQLにもデータ型以外の様々な制約機能が存在します。

検査制約
- 式によって制約を規定する。
- 例：この列は0より大きい値でなければならない。
非NULL制約
- NULLを許容しない制約を付与する。
一意性制約
- 全てのレコードで値がユニークであることを規定する。
プライマリーキー（非NULL制約＋一意性制約）
外部キー
- 別テーブルのカラムと一致することを規定する。
排他制約

ドメインは上記の「検査制約」を付与した型を作成する機能です。
PostgreSQLではユーザが自由に型を作成することが出来ます。型の作成方法には色々な種類がありますが、その一つのパターンとして、型を作成するときに、以下ような制約を付与することができます。

値の範囲を列挙（ENUM）として規定した型を作る。
値のドメイン（設定可能な値集合）を指定した型を作る。

さて、個々の列に対して前述の検査制約を付与することは元々可能なのですが、では制約付きの型を作ると何が嬉しいか？という疑問もあるかもしれません。
一番の理由は「制約付きの型を作るのはメンテナンス性向上のため」なのかなと思っています。
例えば、E-mailアドレスなど、あちこちのテーブルに散在しそうな一般的なデータなどは、ドメインとして制約を記述しておくことで、制約自体のルール変更箇所をまとめられます。
あと、教科書に従ったデータベース設計をするなら、それなりに出番はあるんじゃないかとも・・・(概念設計時にカラムの取りうる値域をきちんと定義したら、それが列挙なりドメインなりになるのではないかと）

ドメインを作成してみる。

前置きが長くなりましたが、ここからは実際にドメインを作成してみます。

CREATE DOMAIN

ドメインを作る場合には、CREATE DOMAINというコマンドを使います。このコマンドは基本型だけでなく、既に作成したドメインに対しても使うことができます(つまり、ドメインはネストできます)。
形式は

CREATE DOMAIN name [ AS ] data_type

    [ COLLATE collation ]

    [ DEFAULT expression ]

    [ constraint [ ... ] ]

また、constraintは

[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK (expression) }

という形式です。詳細はCREATE DOMAINを見てください。
では実際に作成してみます。

3の倍数と3が付く数字のときだけエラーになる整数型

そういえば、ちょっと前に「世界のナベアツ」が「3の倍数と3が付く数字のときだけアホになります」というネタをやってましたが、それにあやかって(?)「3の倍数と3が付く数字のときだけエラーになる」整数型のドメイン「nabeatsu」を定義してみようと思います。

まず、こんな感じでドメインを作成してみます。

CREATE DOMAIN nabeatsu AS INTEGER

CHECK (

  NOT (VALUE % 3 = 0 OR

  substring(VALUE::text from '3') is not null)

);

CHECK (・・・)の部分で、「3の倍数と3が付く数字のときだけエラーになる」チェックをかけています。

ドメインを作成したので、早速使ってみましょう。

CREATE TABLE hoge (

id int,

data nabeatsu

);

psqlの \d コマンドで確認すると、data列は基底となったintegerではなく、作成したドメインnabeatsuの型として表示されます。



      Table "public.hoge"

 Column |   Type   | Modifiers




























































-





























 id     | integer  |

 data   | nabeatsu |

テーブルを定義したので、実際にデータを挿入して、本当にdata列が「3の倍数と3が付く数字のときだけエラーになる」のか見てみます。

INSERT INTO hoge VALUES (1, 1);

INSERT 0 1

INSERT INTO hoge VALUES (2, 3);

ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"

STATEMENT:  INSERT INTO hoge VALUES (2, 3);

psql:nabeatsu.sql:20: ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"

INSERT INTO hoge VALUES (3, 6);

ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"

STATEMENT:  INSERT INTO hoge VALUES (3, 6);

psql:nabeatsu.sql:21: ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"

INSERT INTO hoge VALUES (4, 10);

INSERT 0 1

INSERT INTO hoge VALUES (5, 13);

ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"

STATEMENT:  INSERT INTO hoge VALUES (5, 13);

psql:nabeatsu.sql:23: ERROR:  value for domain nabeatsu violates check constraint "nabeatsu_check"

この例では、dataに対して1, 3, 6, 10, 13という数値を挿入しようとしていますが、3, 6, 13は「3の倍数と3が付く数字」なのでエラーになります。成功ですね。
このときのエラーは
ERROR: value for domain nabeatsu violates check constraint "nabeatsu_check"
のように表示されます。constraintの名称は「ドメイン名 + "_" + check」という名称になるようです。

XMLの妥当性検証を行うXML型

これだけじゃアレなので、もう少し実用的そうなものを考えてみます。

PostgreSQLにもXML型があり、XMLデータを挿入したり、XPathで検索したり出来ます。
現状PostgreSQL本体の機能としては、XMLの形式として正しいかどうか(Wel-formedな形式かどうか)は、チェックしていますが、XMLの妥当性検証(XMLの構造、値の内容等が正しいものかを検証する)を行う機能は残念ながら入っていません。
(XMLの妥当性検証については、それだけで膨大な説明が必要なので詳細は割愛します・・・)

ということで今度はXMLの妥当性検証を行うドメインをつくってみようと思います。
これはSQL定義だけでは出来ないので、事前に以下のようなPostgreSQL関数をC言語で作成しておきます。

入力としてXML型とXML Schema(妥当性検証の定義が記述されたXML)を入力として、trueかfalseを返却する関数を定義する。
関数内でlibxml2のXML Schema Validation APIを呼び出して妥当性検証を行う。
妥当性検証が成功したらtrueを、失敗したらfalseを返却する。

この関数のコードは割愛しますが、C言語で組んでも100 stepもないような簡単なものです。

次に上記のC言語関数 valid_xml をCREATE FUNCTIONコマンドを使ってPostgreSQLに登録します。

CREATE OR REPLACE FUNCTION valid_xml(xml, text)

  RETURNS boolean

AS '$libdir/valid_xml', 'valid_xml'

LANGUAGE 'C' IMMUTABLE STRICT;

で、ドメインを定義して、そのドメインを用いたテーブル定義を行います。

test=# CREATE DOMAIN test_xsd AS xml

test-# CHECK (

test(#   valid_xml(VALUE, '/tmp/test.xsd')

test(# );

CREATE DOMAIN

test=# CREATE TABLE vxml_table (data test_xsd);

CREATE TABLE

test=# \d vxml_table

   Table "public.vxml_table"

 Column |   Type   | Modifiers




























































-





























 data   | test_xsd |

valid_xml関数の第2引数には、XML Schemaファイルのパスを記述します。例えば今回の例では、以下のようなXML SchemaファイルがPostgreSQLサーバの /tmp/test.xsd に存在するとします。

XML Schemaの細かい説明は省きますが、この定義は要するに A要素の下にはB要素→C要素が順に存在しなければならないというルールを示しています。

では、XMLを挿入してみます。

test=# INSERT INTO vxml_table VALUES ('<A><B>bb</B><C>cc</C></A>');

INSERT 0 1

test=# INSERT INTO vxml_table VALUES ('<A><B>bb</B><D>dd</D></A>');

ERROR:  value for domain test_xsd violates check constraint "test_xsd_check"

test=#

最初の例も2番目の例もXMLの形式としては正しいのですが、2番目のXMLはエラーになっていまいます。このエラーはドメインのCHECK制約によるものなのですが、なぜCHECK制約に引っかかったのかというと、2番目のXMLは「A要素のD要素がある」から、XML スキーマ定義に合わなかったと判断されたためです。成功ですね。
PostgreSQL本体では、XML Schemaによる妥当性検証機能はありませんが、ドメインを使うことで、アプリケーション側ではなく、PostgreSQL側で妥当性検証を行う拡張も可能になりました。
ドメイン万歳ですね！

明日の担当は sakai_k さんです。よろしくお願いします。

日々の記録別館

DOMAINを使ってみよう : PostgreSQL Advent Calendar #10