하위 트리 이동

UPDATE categories
SET parent_id = 3
WHERE category_id = 5;

SELECT parent_id
FROM categories
WHERE category_id = 5;

SELECT ancestor.category_id,
       ancestor.category_name,
       path.depth
FROM category_closure AS path
JOIN categories AS ancestor
  ON ancestor.category_id = path.ancestor_id
WHERE path.descendant_id = 7
ORDER BY path.depth;

adjacency parent of Spring(5): 3

closure ancestors of 데이터베이스(7):
7 데이터베이스       depth 0
5 Spring       depth 1
2 백엔드       depth 2
1 개발  depth 3

adjacency/closure drift rows: 2

UPDATE categories
SET parent_id = 2
WHERE category_id = 5;

DELETE FROM category_closure;

INSERT INTO category_closure
  (ancestor_id, descendant_id, depth)
WITH RECURSIVE paths (ancestor_id, descendant_id, depth) AS (
  SELECT category_id, category_id, 0
  FROM categories
  UNION ALL
  SELECT p.ancestor_id, c.category_id, p.depth + 1
  FROM paths AS p
  JOIN categories AS c
    ON c.parent_id = p.descendant_id
  WHERE p.depth < 20
)
SELECT ancestor_id, descendant_id, depth FROM paths;

DROP PROCEDURE IF EXISTS move_category_subtree;
DELIMITER //
CREATE PROCEDURE move_category_subtree(
  IN p_node_id BIGINT UNSIGNED,
  IN p_new_parent_id BIGINT UNSIGNED
)
procedure_body: BEGIN
  DECLARE v_node_count INT DEFAULT 0;
  DECLARE v_parent_count INT DEFAULT 0;
  DECLARE v_cycle_count INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  SELECT COUNT(*) INTO v_node_count
  FROM categories
  WHERE category_id = p_node_id
  FOR UPDATE;

  IF v_node_count = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'node not found';
  END IF;

  IF p_new_parent_id IS NOT NULL THEN
    SELECT COUNT(*) INTO v_parent_count
    FROM categories
    WHERE category_id = p_new_parent_id
    FOR UPDATE;

    IF v_parent_count = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'new parent not found';
    END IF;

    SELECT COUNT(*) INTO v_cycle_count
    FROM category_closure
    WHERE ancestor_id = p_node_id
      AND descendant_id = p_new_parent_id;

    IF v_cycle_count > 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cycle-producing move';
    END IF;
  END IF;

  DELETE path
  FROM category_closure AS path
  JOIN category_closure AS old_ancestor
    ON old_ancestor.ancestor_id = path.ancestor_id
   AND old_ancestor.descendant_id = p_node_id
   AND old_ancestor.ancestor_id <> p_node_id
  JOIN category_closure AS subtree
    ON subtree.ancestor_id = p_node_id
   AND subtree.descendant_id = path.descendant_id;

  IF p_new_parent_id IS NOT NULL THEN
    INSERT INTO category_closure
      (ancestor_id, descendant_id, depth)
    SELECT new_ancestor.ancestor_id,
           subtree.descendant_id,
           new_ancestor.depth + 1 + subtree.depth
    FROM category_closure AS new_ancestor
    JOIN category_closure AS subtree
      ON subtree.ancestor_id = p_node_id
    WHERE new_ancestor.descendant_id = p_new_parent_id;
  END IF;

  UPDATE categories
  SET parent_id = p_new_parent_id
  WHERE category_id = p_node_id;

  COMMIT;
END//
DELIMITER ;

CALL move_category_subtree(5, 3);

moved subtree root: 5
subtree nodes: 2
old external paths removed: 4
new external paths inserted: 4

ancestors of 데이터베이스:
7 데이터베이스      depth 0
5 Spring      depth 1
3 프론트엔드    depth 2
1 개발 depth 3

drift rows: 0

SELECT a.category_name AS ancestor_name,
       p.depth
FROM category_closure AS p
JOIN categories AS a
  ON a.category_id = p.ancestor_id
WHERE p.descendant_id = 7
ORDER BY p.depth;

SELECT d.category_name AS descendant_name,
       p.depth
FROM category_closure AS p
JOIN categories AS d
  ON d.category_id = p.descendant_id
WHERE p.ancestor_id = 5
ORDER BY p.depth;

SELECT c.category_id
FROM categories AS c
LEFT JOIN category_closure AS p
  ON p.ancestor_id = c.parent_id
 AND p.descendant_id = c.category_id
 AND p.depth = 1
WHERE c.parent_id IS NOT NULL
  AND p.ancestor_id IS NULL;

SELECT p.ancestor_id, p.descendant_id
FROM category_closure AS p
LEFT JOIN categories AS c
  ON c.parent_id = p.ancestor_id
 AND c.category_id = p.descendant_id
WHERE p.depth = 1
  AND c.category_id IS NULL;