9-2 古くて新しい解法 〜隣接リストモデル

自己テーブル参照

code:sql

CREATE TABLE OrgChartAdjacency (

emp VARCHAR(32) NOT NULL,

boss VARCHAR(32),

CONSTRAINT pk_OrgChartAdjacency PRIMARY KEY (emp),

CONSTRAINT Ek_OrgChartAdjacency FOREIGN KEY (boss)

REFERENCES OrgChartAdjacency (emp)

);

table:組織図

emp（社員） boss（上司）

山田

佐藤 山田

田中 山田

後藤 田中

内藤 田中

小野 田中

大野 後藤

隣接リストモデルにおける検索

従来、隣接リストモデルは検索クエリが極めて複雑かつパフォーマンスが悪いことから、アンチパターンとして批判を受けていた

しかし、再帰共通表式が多くのDBMSで導入されたことにより、アンチパターンではなくなった

再帰共通表式の例

田中さんをルートとしたノード（社員）全てを取得

code:sql

WITH RECURSIVE Subordinates AS (

-- 開始

SELECT emp, boss

FROM OrgChartAdjacency

WHERE emp = '田中'

UNION ALL

-- 再帰

SELECT o.emp, o.boss

FROM OrgChartAdjacency o

INNER JOIN Subordinates s ON o.boss = s.emp

)

SELECT *

FROM Subordinates;

table:部下

emp（社員） boss（上司）

田中 山田

後藤 田中

内藤 田中

小野 田中

大野 後藤

隣接リストモデルにおける更新

内部ノードの削除

例

田中さんが退職することになり、田中さんの部下は全員、山田さんの部下になることへ

code:sql

UPDATE OrgChartAdjacency

SET boss = '山田'

WHERE boss = '田中';

DELETE FROM OrgChartAdjacency

WHERE emp = '田中';

ノードの挿入

例

佐藤さんの直属の上司が渡辺さんになり、渡辺さんの上司が山田さんとなる

code:sql

INSERT INTO OrgChartAdjacency VALUES('渡辺', '山田');

UPDATE OrgChartAdjacency

SET boss = '渡辺'

WHERE boss = '佐藤';