DB2/400 で「木と階層構造のデータを扱う」

SQLパズル 第2版 プログラミングが変わる書き方/考え方」という本を買って少しずつ勉強しているのですが、この本の翻訳をされているミックさんという方が「リレーショナル・データベースの世界」というページを開設されています。

いろいろ興味深いトピックがたくさんあります。
その中で「SQLで木と階層構造のデータを扱う(1)―― 入れ子集合モデル」という記事を、実際に例をDB2/400 で実行しながら読んでみました。実行する上でちょっとした修正が必要になったので、それをここに書いておきたいと思います。内容はもちろんリンク先の記事を見てくださいね。


「2-2.ノードの深さを計算する」

「2-2.ノードの深さを計算する」にある

SELECT Children.emp , COUNT(Parents.emp) AS level
  FROM OrgChart Parents, OrgChart Children
 WHERE Children.lft BETWEEN Parents.lft AND Parents.rgt
GROUP BY Children.emp;

は画面例どおりに出すには

SELECT Children.emp , COUNT(Parents.emp) AS level
  FROM OrgChart Parents, OrgChart Children
 WHERE Children.lft BETWEEN Parents.lft AND Parents.rgt
GROUP BY Children.emp
ORDER BY 2;

でした。

「2-4.木をインデント付きで表示する」

「2-4.木をインデント付きで表示する」にある次の文は

SELECT LPAD(Mgrs.emp, LENGTH(Mgrs.emp) + CAST(COUNT(*) AS INTEGER) + 1, ' ') AS emp
  FROM OrgChart Mgrs, OrgChart MidMgrs, OrgChart Workers
 WHERE Mgrs.lft BETWEEN MidMgrs.lft AND MidMgrs.rgt
   AND MidMgrs.lft BETWEEN Workers.lft AND Workers.rgt
GROUP BY Mgrs.emp, Mgrs.lft
ORDER BY MAX(Mgrs.lft);

「LPAD 関数は実装依存の関数のため、Oracle、PostgreSQL、MySQL でのみ使用可能」であり、「DB2 では REPEAT」で代用できるということなので↓のように書き替えてみました。

SELECT REPEAT(' ', LENGTH(Mgrs.emp) + CAST(COUNT(*) AS INTEGER) + 1) || Mgrs.emp AS emp 
  FROM OrgChart Mgrs, OrgChart MidMgrs, OrgChart Workers 
 WHERE Mgrs.lft BETWEEN MidMgrs.lft AND MidMgrs.rgt 
   AND MidMgrs.lft BETWEEN Workers.lft AND Workers.rgt 
GROUP BY Mgrs.emp, Mgrs.lft 
ORDER BY MAX(Mgrs.lft); 

表示結果は↓のようになります。

「2-7.パスを列挙する(列持ちバージョン)」

「2-7.パスを列挙する(列持ちバージョン)」の「SQL のロジックを本当に理解していないと書くことのできない見事なもの」と言われている次の SQL が、「ある意味で盲点を突いた技なので、動かない DB も少なからずあります。PostgreSQL では問題なく動作しますが、Oracle ではこの点がネックで動きません」と書いてあり、どきどきしながら実行したのですが↓のようになりました。

実行でき、個々の行の結果、集合としては正しいので「実行できた」ということになるのだとは思いますが、並び順が載っている例と違うのでちょっと悩んでいるところです。

ちなみに、「ほとんどの実装で動作する」ように書き替えた例として載っている SQL の実行例は↓のようになります。

「3-1.部分木の削除」

「3-1.部分木の削除」で関連した部分木の削除の例が載っています。
欠番になった添え字の削除用のSQL も載っていますが、対象となる部分が変数になっているものです。「添え字の 5 〜 8 が欠番になっています」ということなので、:drop_lft に 5、:drop_rgt に 8をそれぞれ代入して実行してみれば、今回の結果の確認をすることができます。

「3-2.単一ノードの削除」

「3-2.単一ノードの削除」で紹介されているSQL には、以下のような注意があります。

>>

実は DB によっては動かない可能性もあります。標準 SQL には沿っていますし、Oracle では正しく動作するのですが、問題となる点は以下の二つです。  

  1. この更新が実行される最中、一時的に emp 列に重複が生じるため、主キー違反となる。
  2. UPDATE での更新時に同一テーブルの参照をサポートしていない。

PostgreSQL は第一の理由によってエラーとなります。確かに、このクエリのロジックを順に追っていくと、削除する親の名前を第一子の名前で上書きするために、一時的に次のように emp 列が重複する期間があるのです。

>>

実際、DB2/400 で実行してみると、おそらく PostgreSQL と同様の理由でエラーになりました。

「3-7.隣接リストモデルから入れ子集合モデルへ変換する」

PL/SQL のプロシージャを、「Oracle PL/SQL 入門」と DB2 for i5/OS の Redbook を参考にしながら、DB2 の SQL/PSM にかなり機械的に変換してみました。

変数の定義

counter     INTEGER :=2;
DECLARE    counter     INTEGER DEFAULT 2;

WHILE [条件] LOOP を WHILE [条件] DO

これは単なる構文の違いですね。ちなみに PL/SQL のただの LOOP は EXIT で抜けますが、DB2 の SQL PSM では LEAVE で抜けます。

    WHILE counter <= max_counter- 1
    LOOP

    END LOOP;
    WHILE counter <= max_counter- 1
    DO

    END WHILE;

INSERT で NULL の処理

            INSERT INTO Stack
            SELECT (current_top + 1), MIN(T1.node), counter, NULL
              FROM Stack S1, Tree T1
             WHERE S1.node = T1.parent
               AND S1.stack_top = current_top;

INSERT するカラムを、全選択ではなく個別に指定して、デフォルトが NULL になるようにするか、

            INSERT INTO Stack (stack_top, node, lft)
            SELECT (current_top + 1), MIN(T1.node), counter
              FROM Stack S1, Tree T1
             WHERE S1.node = T1.parent
               AND S1.stack_top = current_top;

内容が NULL である変数(定数) を定義して、それを該当カラムのセット内容とするか、というやり方を考えてみました。

DECLARE    int_null  INTEGER DEFAULT NULL;

            INSERT INTO Stack
            SELECT (current_top + 1), MIN(T1.node), counter, int_null
              FROM Stack S1, Tree T1
             WHERE S1.node = T1.parent
               AND S1.stack_top = current_top;

と、やってみたところで、もっとそれらしいやり方を発見しました。
これが元々に一番近そうです。

            INSERT INTO Stack
            SELECT (current_top + 1), MIN(T1.node), counter, CAST(NULL as Integer)
              FROM Stack S1, Tree T1
             WHERE S1.node = T1.parent
               AND S1.stack_top = current_top;
CREATE PROCEDURE TreeTraversal
LANGUAGE SQL

BEGIN

DECLARE    counter     INTEGER DEFAULT 2;
DECLARE    max_counter INTEGER DEFAULT 0;
DECLARE    current_top INTEGER DEFAULT 1;
DECLARE    current_cnt INTEGER DEFAULT 0;

    --カウンタの最大値を初期化(一つのノードについて二度巡回するので2倍している)
    SELECT COUNT(*) * 2 INTO max_counter FROM Tree;

    --スタックの初期化
    DELETE FROM Stack;

    --ルートをプッシュ
    INSERT INTO Stack
    SELECT 1, node, 1, max_counter
      FROM Tree
     WHERE parent IS NULL;

    --プッシュしたノードを元の木から削除
    DELETE FROM Tree WHERE parent IS NULL;

    WHILE counter <= max_counter- 1
    DO

           --プッシュしたノードが子を持つか調べる
           SELECT COUNT(*) INTO current_cnt
             FROM Stack S1, Tree T1
            WHERE S1.node = T1.parent
              AND S1.stack_top = current_top;

        IF current_cnt > 0 THEN 
            -- ノードが子を持っていれば、子を一つプッシュする
            INSERT INTO Stack (stack_top, node, lft)
            SELECT (current_top + 1), MIN(T1.node), counter
              FROM Stack S1, Tree T1
             WHERE S1.node = T1.parent
               AND S1.stack_top = current_top;

            -- プッシュしたノードを元の木から削除
            DELETE FROM Tree
             WHERE node = (SELECT node
                             FROM Stack
                            WHERE stack_top = current_top + 1);

            -- カウンタとスタック・ポインタのインクリメント
            SET counter = counter + 1;
            SET current_top = current_top + 1;
        ELSE
            -- スタックをポップして、rgtの値を決める
            UPDATE Stack
               SET rgt = counter,
                   stack_top = -stack_top -- スタックのポップ
             WHERE stack_top = current_top;

            -- カウンタはインクリメントするが、スタック・ポインタはデクリメント
            SET counter = counter + 1;
            SET current_top = current_top - 1;
        END IF;

    END WHILE;
END;

こちらが NULL にセットした定数を使用したバージョンです。

CREATE PROCEDURE TreeTraversal
LANGUAGE SQL

BEGIN

DECLARE    counter     INTEGER DEFAULT 2;
DECLARE    max_counter INTEGER DEFAULT 0;
DECLARE    current_top INTEGER DEFAULT 1;
DECLARE    current_cnt INTEGER DEFAULT 0;
DECLARE    int_null  INTEGER DEFAULT NULL;

    --カウンタの最大値を初期化(一つのノードについて二度巡回するので2倍している)
    SELECT COUNT(*) * 2 INTO max_counter FROM Tree;

    --スタックの初期化
    DELETE FROM Stack;

    --ルートをプッシュ
    INSERT INTO Stack
    SELECT 1, node, 1, max_counter
      FROM Tree
     WHERE parent IS NULL;

    --プッシュしたノードを元の木から削除
    DELETE FROM Tree WHERE parent IS NULL;

    WHILE counter <= max_counter- 1
    DO

           --プッシュしたノードが子を持つか調べる
           SELECT COUNT(*) INTO current_cnt
             FROM Stack S1, Tree T1
            WHERE S1.node = T1.parent
              AND S1.stack_top = current_top;

        IF current_cnt > 0 THEN 
            -- ノードが子を持っていれば、子を一つプッシュする
            INSERT INTO Stack
            SELECT (current_top + 1), MIN(T1.node), counter, int_null
              FROM Stack S1, Tree T1
             WHERE S1.node = T1.parent
               AND S1.stack_top = current_top;

            -- プッシュしたノードを元の木から削除
            DELETE FROM Tree
             WHERE node = (SELECT node
                             FROM Stack
                            WHERE stack_top = current_top + 1);

            -- カウンタとスタック・ポインタのインクリメント
            SET counter = counter + 1;
            SET current_top = current_top + 1;
        ELSE
            -- スタックをポップして、rgtの値を決める
            UPDATE Stack
               SET rgt = counter,
                   stack_top = -stack_top -- スタックのポップ
             WHERE stack_top = current_top;

            -- カウンタはインクリメントするが、スタック・ポインタはデクリメント
            SET counter = counter + 1;
            SET current_top = current_top - 1;
        END IF;

    END WHILE;
END;

NULL を直接 CAST して使うやり方です。

CREATE PROCEDURE TreeTraversal
LANGUAGE SQL

BEGIN

DECLARE    counter     INTEGER DEFAULT 2;
DECLARE    max_counter INTEGER DEFAULT 0;
DECLARE    current_top INTEGER DEFAULT 1;
DECLARE    current_cnt INTEGER DEFAULT 0;

    --カウンタの最大値を初期化(一つのノードについて二度巡回するので2倍している)
    SELECT COUNT(*) * 2 INTO max_counter FROM Tree;

    --スタックの初期化
    DELETE FROM Stack;

    --ルートをプッシュ
    INSERT INTO Stack
    SELECT 1, node, 1, max_counter
      FROM Tree
     WHERE parent IS NULL;

    --プッシュしたノードを元の木から削除
    DELETE FROM Tree WHERE parent IS NULL;

    WHILE counter <= max_counter- 1
    DO

           --プッシュしたノードが子を持つか調べる
           SELECT COUNT(*) INTO current_cnt
             FROM Stack S1, Tree T1
            WHERE S1.node = T1.parent
              AND S1.stack_top = current_top;

        IF current_cnt > 0 THEN 
            -- ノードが子を持っていれば、子を一つプッシュする
            INSERT INTO Stack
            SELECT (current_top + 1), MIN(T1.node), counter, CAST(NULL as Integer)
              FROM Stack S1, Tree T1
             WHERE S1.node = T1.parent
               AND S1.stack_top = current_top;

            -- プッシュしたノードを元の木から削除
            DELETE FROM Tree
             WHERE node = (SELECT node
                             FROM Stack
                            WHERE stack_top = current_top + 1);

            -- カウンタとスタック・ポインタのインクリメント
            SET counter = counter + 1;
            SET current_top = current_top + 1;
        ELSE
            -- スタックをポップして、rgtの値を決める
            UPDATE Stack
               SET rgt = counter,
                   stack_top = -stack_top -- スタックのポップ
             WHERE stack_top = current_top;

            -- カウンタはインクリメントするが、スタック・ポインタはデクリメント
            SET counter = counter + 1;
            SET current_top = current_top - 1;
        END IF;

    END WHILE;
END;

「3-8.入れ子集合モデルから隣接リストモデルへ変換する」

OrgChart_AL というテーブルが必要になるのですが、↓のようなかんじで作成してみました。

「3-9.入れ子集合モデルから経路列挙モデルへ変換する」

OrgChart_PE というテーブルが必要になるので、↓のように作成してみました。

「3-10.XML データを入れ子集合モデルに変換する」

OrgChart_XML というテーブルが必要になるので、↓のように作成してみました。

「4.木の構造を比較する」

「4.木の構造を比較する」はすべて何の問題もなく実行できました。