2021 04 06 - Sql Antipatterns 계층구조 만들기 - 클로저테이블

클로저 테이블

클로저 테이블

클로저 테이블은 다른 트리모델과는 달리, 트리의 모든 경로를 데이터와 분리하여 별개의 테이블에 저장한다.

이 테이블에는 자기자신을 참조하는 행을 포함한, 트리의 조상, 자손 관계를 가진 모든 노드 들의 관계를 한 행으로 저장한다.

그러니까 자기 자신을 기준으로 자신의 자손노드 전부를 한 행으로 갖는다.

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)
)

조회하기

답글 #4의 자손을 얻으려면 TreePaths에서 ancestor가 4인 행을 가져오면 된다.

SELECT c.*
FROM Comments AS C
    JOIN TreePaths AS t ON c.comment_id = t.descendant
WHERE t.ancestor = 4;

추가하기

예를들어, 답글 #5에 새로운 댓글을 추가한다고 가정했을때,

• 댓글의 시퀀스는 8을 가정하자.
• 자기 자신을 참조하는 행을 추가한다.
• TreePaths에서 답글 #5를 descemdant로 참조하는 모든 행(자기 자신 포함)에 #8을 추가한다.

  INSERT INTO TreePaths (ancestor, descemdant)
    SELECT t.ancestor, 8
    FROM TreePaths AS t
    WHERE t.descendant = 5
    UNION ALL
        SELECT 8, 8 --자기자신을 참조하는 행을 함께 INSERT 하기 위해서.

  SELECT 와 INSERT INTO를 사용하면 SELECT 결과를 INSERT 할 수 있다. 심지어 다중행도 가능!

삭제하기

대댓글이 없는 #7 댓글을 삭제하기.

단순히 TreePaths에서 7을 참조하는 맵핑데이터를 삭제하면 됩니다.

TreePaths에서 데이터를 삭제한다고 해서 댓글을 삭제하는것이 아님을 유의! 그저 맵핑 데이터일 뿐입니다.

DELETE FROM TreePaths WHERE descendant = 7;

대댓글이 존재하는 #4 댓글을 삭제하기.

DELETE FROM TreePAths
WHERE descemdant IN(SELECT descendant
                    FROM TreePaths
                    WHERE ancestor = 4)