SQL: Closure Table Scripts

closure.sql

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS comment_paths;

CREATE TABLE comments (
  comment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(comment_id)
);

CREATE TABLE comment_paths (
  ancestor BIGINT UNSIGNED NOT NULL,
  descendant BIGINT UNSIGNED NOT NULL,
  depth BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(ancestor, descendant),
  CONSTRAINT FOREIGN KEY (ancestor) REFERENCES comments(comment_id) ON DELETE CASCADE, 
  CONSTRAINT FOREIGN KEY (descendant) REFERENCES comments(comment_id) ON DELETE CASCADE
);

INSERT INTO comments (comment_id) VALUES
  (1),
  (2),
  (3),
  (4),
  (5),
  (6),
  (7);

INSERT INTO comment_paths (ancestor, descendant, depth) VALUES
  (1, 1, 0), (1, 2, 1), (1, 3, 2), (1, 4, 1), (1, 5, 2), (1, 6, 2), (1, 7, 3),
  (2, 2, 0), (2, 3, 1),
  (3, 3, 0),
  (4, 4, 0), (4, 5, 1), (4, 6, 1), (4, 7, 2),
  (5, 5, 0),
  (6, 6, 0), (6, 7, 1),
  (7, 7, 0);

SET FOREIGN_KEY_CHECKS = 1;

-- Get comment tree
SELECT c.comment_id
FROM comment_paths AS cp
JOIN comments AS c ON c.comment_id = cp.descendant
WHERE cp.ancestor = 4;

-- Get comment children
SELECT c.comment_id
FROM comment_paths AS cp
JOIN comments AS c ON c.comment_id = cp.descendant
WHERE cp.ancestor = 4
AND cp.depth = 1;

-- Remove comment #4 and all descendants
/* DELETE p1, c FROM comment_paths AS p1
JOIN comment_paths AS p2 USING (descendant)
JOIN comments AS c ON c.comment_id = p1.descendant
WHERE p2.ancestor = 4; */

-- Remove all descendants of comment #4
/* DELETE p1, c FROM comment_paths AS p1
JOIN comment_paths AS p2 USING (descendant)
JOIN comments AS c ON c.comment_id = p1.descendant
WHERE p2.ancestor = 4 AND p2.descendant != 4; */

-- Move subtree 6 into 2
/* DELETE cp1 FROM comment_paths AS cp1
JOIN comment_paths AS cp2 ON cp2.descendant = cp1.descendant
LEFT JOIN comment_paths AS cp3 ON cp3.ancestor = cp2.ancestor AND cp3.descendant = cp1.ancestor
WHERE cp2.ancestor = 6 AND cp3.ancestor IS NULL;

INSERT INTO comment_paths (ancestor, descendant, depth)
SELECT 
    cp1.ancestor, 
    cp2.descendant,
    cp1.depth + cp2.depth + 1
FROM comment_paths AS cp1 
JOIN comment_paths AS cp2
WHERE cp2.ancestor = 6
AND cp1.descendant = 2; */