postgreSQLでcompositeパターン

このエントリはPostgreSQL Advent Calendarの12/17担当分のエントリとなります。

postgreSQLにはテーブルの継承と配列型という、他RDBMSではあまり馴染みのない機能があります。
少し思いついたのでこの2つを利用してComposite パターンを試してみました。
(枝(容器/ディレクトリ)と葉(中身/ファイル)を同一視するインターフェースで構造を構築するパターンです)

先ず、同一視する共通インターフェースを定義します。

create table entity (
	id serial primary key,
	name text not null
);

次に「葉」となるfile tableを定義。INHERITS句で継承するテーブルを指定します。
カラムはファイルの情報となるものをサンプルで追加してみました。

create table file (
	contents text,
	size integer default 0 not null,
	type text
)INHERITS(entity);

今度は「枝」となるdirectory tableを定義。こちらもentity tableを継承。
そしてentity.id を格納するcollection カラム定義しました。

create table directory (
	collection integer[]
)INHERITS(entity);

テーブルの準備が出来たところでデータを登録していきます。

composite=# insert into directory ( name )values( '/' );
INSERT 0 1

composite=# SELECT * from directory ;
 id | name | collection 
----+------+------------
  1 | /    | 
(1 row)
composite=# SELECT * from entity;
 id | name 
----+------
  1 | /
(1 row)

親テーブルのentityからもレコードが参照できることが確認出来ます。

続いてツリー構造を作成して行きます。
オブジェクトの登録

composite=# insert into directory ( name )values( 'home' );
INSERT 0 1
composite=# insert into directory ( name )values( 'usr' );
INSERT 0 1
composite=# insert into directory ( name )values( 'var' );
INSERT 0 1

枝ノードに子ノードをセット
array_append関数でentity.idを設定していきます。

composite=# UPDATE directory set collection = array_append(collection, 2) where id = 1;
UPDATE 1
composite=# UPDATE directory set collection = array_append(collection, 3) where id = 1;
UPDATE 1
composite=# UPDATE directory set collection = array_append(collection, 4) where id = 1;
UPDATE 1
composite=# UPDATE directory set collection = array_append(collection, 5) where id = 2;
UPDATE 1
composite=# UPDATE directory set collection = array_append(collection, 6) where id = 2;
UPDATE 1
composite=# UPDATE directory set collection = array_append(collection, 7) where id = 2;
UPDATE 1

ここまででレコードは以下のようになりました。

composite=# SELECT * from directory order by id;
 id | name  | collection 
----+-------+------------
  1 | /     | {2,3,4}
  2 | home  | {5,6,7}
  3 | usr   | 
  4 | var   | 
  5 | user1 | 
  6 | user2 | 
  7 | user3 | 
(7 rows)

「葉」となるファイルノードを登録。

composite=# insert into file (name, contents, size, type)values( 'hoge', 'abc', length('abc'), '.txt' );
INSERT 0 1
composite=# insert into file (name, contents, size, type)values( 'foo', '<?php print "hello world"; ?>', length('<?php print "hello world"; ?>'), '.php' );
INSERT 0 1
composite=# insert into file (name, contents, size, type)values( 'bar', 'my memo...', length('my memo...'), '.txt' );
INSERT 0 1

composite=# SELECT * from file order by id;
 id | name |           contents            | size | type 
----+------+-------------------------------+------+------
  8 | hoge | abc                           |    3 | .txt
  9 | foo  | <?php print "hello world"; ?> |   29 | .php
 10 | bar  | my memo...                    |   10 | .txt

のように登録しました。

ここまででentityテーブルは以下となります。

composite=# SELECT * from entity order by id;
 id | name  
----+-------
  1 | /
  2 | home
  3 | usr
  4 | var
  5 | user1
  6 | user2
  7 | user3
  8 | hoge
  9 | foo
 10 | bar
(10 rows)

「葉」ノードを「枝」に登録します。

composite=# update directory set collection = array_append(collection, 8) where id = 5;
UPDATE 1
composite=# update directory set collection = array_append(collection, 9) where id = 5;
UPDATE 1
composite=# update directory set collection = array_append(collection, 10) where id = 5;
UPDATE 1

最終的に「枝」の構造を以下にしました。

composite=# SELECT * from directory order by id;
 id | name  | collection 
----+-------+------------
  1 | /     | {2,3,4}
  2 | home  | {5,6,7}
  3 | usr   | 
  4 | var   | 
  5 | user1 | {8,9,10}
  6 | user2 | 
  7 | user3 | 
(7 rows)

最後に構造を巡回するスクリプトをplpgsqlで作りました。

create or replace function composite( entity.id%TYPE, TEXT ) RETURNS setof TEXT AS $$
declare
	sql text;
	relname text;
	name text;
	current text;
	size integer;
	type text;
begin
        --アクセスされたentity.idが格納されているオブジェクト名を取得
        --ここでは「directory」 or 「file」が返ってきます。
	EXECUTE 'select p.relname, name from entity e, pg_class p where e.tableoid = p.oid and e.id = '||$1 into relname, name;
	IF relname = 'directory' THEN
		IF name = '/' THEN
			current := name;
		ELSE
                        --子ノードの時は第二引数の親ノード名を追加。
			current := $2||name||'/';
		END IF;
                --現在のノード名称を返却
		return next current;
                --子ノードを探索。自分自身のノード名称を渡します。
		sql := 'select composite(unnest(collection), '''||current||''' ) from directory where id = ' ||$1;
		return QUERY EXECUTE sql;
	ELSE
                --「葉」ノード時はファイル情報を出力
		current := $2||name;
		EXECUTE 'select size, type from file where id = ' ||$1 into size, type;
		return next current||type||'('||size||')';
	END IF;
end;
$$ language plpgsql;

CREATE FUNCTION

使用例。

composite=# SELECT composite(id, '') from entity where id = 1;
     print_composite     
-------------------------
 /
 /home/
 /home/user1/
 /home/user1/hoge.txt(3)
 /home/user1/foo.php(29)
 /home/user1/bar.txt(10)
 /home/user2/
 /home/user3/
 /usr/
 /var/
(10 rows)

setof next returnなのでfrom句でも使用できます。

composite=# select * from composite(1, '');
     print_composite     
-------------------------
 /
 /home/
 /home/user1/
 /home/user1/hoge.txt(3)
 /home/user1/foo.php(29)
 /home/user1/bar.txt(10)
 /home/user2/
 /home/user3/
 /usr/
 /var/
(10 rows)

home/ 以下を探索だと、、、

composite=# SELECT composite(id, '') from entity where name = 'home';
       composite        
------------------------
 home/
 home/user1/
 home/user1/hoge.txt(3)
 home/user1/foo.php(29)
 home/user1/bar.txt(10)
 home/user2/
 home/user3/
(7 rows)

こうなりますね。

以上、postgreSQLの継承、配列型を利用したcompositeパターンのご紹介でした。
(データのinsert, updateもインターフェースとなるentity tableを使用するべきですね。。。)
※ 継承、配列型は他RDBMSには移植し辛い機構なので、実務で利用するには十分注意して下さい（笑）

明日のPostgreSQL Advent Calendarは@maropuさんです。宜しくお願いします。