SQLアンチパターンを読み始めたので、1つ１つ書いてのメモです
目的
- 階層構造を格納し、クエリーを実行する
- ツリー構造
- 親（ルート）、子（ノード）、葉（リーフ）
アンチパターン
-
常に親のみに依存する
- 親のIDをいれる
- 「隣接リスト」という
-
ノードの追加、サブツリーへの移動は簡単
- ノードの削除は大変
用いてもいいパターン
- ノードの直近の親と子を簡単に取得できること
- 階層構造を持つデータに対して必要な操作が、列の挿入のみである場合
- WITH キーワードの後に共通テーブル式が使える場合
- 再帰クエリ構文
解決策
- 代替ツリーモデルを使用する
- 経路列挙モデル (Path Enumeration)
- 入れ子集合モデル(Nested Set)
- 閉包テーブルモデル(Closure Table)
経路列挙モデル
- 経路を
1/5/7など入れる - 信号無視と同じアンチパターンが発生する
入れ子集合モデル
- よくわからない...
閉包テーブルモデル
- エントリーと関連構造を示すテーブルでツリー構造を示す
CREATE TABLE Comments (
comment_id serial primary key,
bug_id bigint unsigned not null,
author bigint unsigned not null,
comment_date datetime not null,
comment text not null
-- FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
-- FOREIGN KEY (author) REFERENCES Accounts(account_id)
);
CREATE TABLE TreePaths (
ancestor BIGINT UNSIGNED NOT NULL,
descendant BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (ancestor, descendant)
-- FOREIGN KEY (ancestor) REFERENCES Comments(comment_id)
-- FOREIGN KEY (descendant) REFERENCES Comments(comment_id)
);
CREATE TABLE Authors (
author_id serial primary key,
author_name VARCHAR(1000)
);
INSERT INTO Authors (author_id, author_name) VALUES (DEFAULT, 'ガイア');
INSERT INTO Authors (author_id, author_name) VALUES (DEFAULT, 'マッシ');
INSERT INTO Authors (author_id, author_name) VALUES (DEFAULT, 'オルテガ');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 1, now(), 'このバグの原因は何かな?');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 2, now(), 'ヌルポインターのせいじゃないかな?');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 1, now(), 'そうじゃないよ。それは確認済みだ。');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 3, now(), '無効なインプットを調べてみたら?');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 2, now(), 'そうか、バグの原因はそれだな。');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 1, now(), 'よし、じゃあチェック機能を追加して もらえるかな?');
INSERT INTO Comments (comment_id, bug_id, author, comment_date, comment) VALUES (DEFAULT, 1, 3, now(), '了解。修正したよ。');
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 1);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 2);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 3);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 4);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 5);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 6);
INSERT INTO TreePaths (ancestor, descendant) VALUES (1, 7);
INSERT INTO TreePaths (ancestor, descendant) VALUES (2, 2);
INSERT INTO TreePaths (ancestor, descendant) VALUES (2, 3);
INSERT INTO TreePaths (ancestor, descendant) VALUES (3, 3);
INSERT INTO TreePaths (ancestor, descendant) VALUES (4, 4);
INSERT INTO TreePaths (ancestor, descendant) VALUES (4, 5);
INSERT INTO TreePaths (ancestor, descendant) VALUES (4, 6);
INSERT INTO TreePaths (ancestor, descendant) VALUES (4, 7);
INSERT INTO TreePaths (ancestor, descendant) VALUES (5, 5);
INSERT INTO TreePaths (ancestor, descendant) VALUES (6, 6);
INSERT INTO TreePaths (ancestor, descendant) VALUES (6, 7);
INSERT INTO TreePaths (ancestor, descendant) VALUES (7, 7);
まとめ
- エントリーと関連構造で成り立つ
- 要件によって、設計を決めると良い
感想
- やっぱり、やったことある。けれど、そこまで大変ではなかった記憶があって、ブログのカテゴリーなどで使ったぐらいだからかな？
- 閉包テーブルがありなのかな、と思うけれど、Railsでどうするんだろう？（面倒そう、調べていないけれど）
