たま

技術的なメモ置き場。主にJava。

閉包テーブルの更新(その1)

閉包テーブル(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)
);

サンプルデータ

構造

テーブル

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アンチパターン