閉包テーブルの更新（その１）

閉包テーブル（Closure Table）の更新について書く。

閉包テーブルについては以下を参照。 lab.kochlein.com

サンプルテーブル

CREATE TABLE folders (
      folder_id   SERIAL PRIMARY KEY
    , name        TEXT NOT NULL
);

CREATE TABLE ctable (
      ancestor    BIGINT NOT NULL -- 先祖
    , descendant  BIGINT NOT NULL -- 子孫
    , PRIMARY KEY (ancestor, descendant)
    , FOREIGN KEY (ancestor) REFERENCES folders(folder_id)
    , FOREIGN KEY (descendant) REFERENCES folders(folder_id)
);

サンプルデータ

構造

•  
  フォルダ1
  •  
    フォルダ2
    •  
      フォルダ3
  •  
    フォルダ4
    •  
      フォルダ5

テーブル

folder_id	name
1	フォルダ1
2	フォルダ2
3	フォルダ3
4	フォルダ4
5	フォルダ5

ancestor	descendant
1	1
1	2
1	3
1	4
1	5
2	2
2	3
3	3
4	4
4	5
5	5

子孫の取得

-- フォルダ2の子孫
SELECT f.*
  FROM folders AS f INNER JOIN ctable AS ct
    ON f.folder_id = ct.descendant
 WHERE ct.ancestor = 2;

先祖の取得

-- フォルダ4の先祖
SELECT f.*
  FROM folders AS f INNER JOIN ctable AS ct
    ON f.folder_id = ct.ancestor
 WHERE ct.descendant = 4;

子の追加

-- フォルダ6を追加
INSERT INTO folders VALUES (6, 'フォルダ6');

-- フォルダ5にフォルダ6を追加
INSERT INTO ctable (ancestor, descendant)
-- フォルダ5を子孫として参照する行
SELECT ct.ancestor, 6
  FROM ctable AS ct
 WHERE ct.descendant = 5
UNION ALL
-- 自己参照の行
SELECT 6, 6;

葉ノードの削除

DELETE FROM ctable WHERE descendant = 3;

サブツリーの削除

DELETE 
  FROM ctable
 WHERE descendant IN (SELECT descendant
                        FROM ctable
                       WHERE ancestor = 2);

サブツリーの移動

-- フォルダ4をフォルダ3に移動する
-- 自己参照を除く、先祖と子孫を削除
DELETE 
  FROM ctable
 WHERE descendant IN (SELECT descendant
                        FROM ctable
                       WHERE ancestor = 4)
   AND ancestor   IN (SELECT ancestor
                        FROM ctable
                       WHERE descendant = 4
                         AND ancestor != descendant);

INSERT INTO ctable (ancestor, descendant)
SELECT super.ancestor, sub.descendant
  FROM ctable AS super
 CROSS JOIN ctable AS sub
 WHERE super.descendant = 3
   AND sub.ancestor = 4;

参考

SQLアンチパターン