Closure Table operations SQL fragments

closure-table-operations.sql

-- Retrieve descendants
-- ====================
-- retrieve descendants of #4
SELECT c.*
  FROM Comments AS c
    JOIN TreePaths AS t ON c.comment_id = t.descendant
 WHERE t.ancestor = 4;


-- Retrieve ancestors
-- ==================
-- retrieve ancestors of #6
SELECT c.*
  FROM Comments AS c
    JOIN TreePaths AS t ON c.comment_id = t.ancestor
 WHERE t.descendant = 6;


-- Insert Leaf node
-- ================
-- insert leaf node #8 as a child of #5
INSERT INTO TreePaths (ancestor, descendant, path_length)
  SELECT t.ancestor, 8, t.path_length + 1
    FROM TreePaths AS t
   WHERE t.descendant = 5
   UNION ALL
     SELECT 8, 8;


-- Delete Leaf node
-- ================
-- delete leaf node #7
DELETE FROM TreePaths WHERE descendant = 7;


-- Delete Subtree
-- ==============
-- delete #4 and all children from the tree
DELETE FROM TreePaths
 WHERE descendant IN (SELECT descendant
                        FROM TreePaths
                       WHERE ancestor = 4);


-- Move Subtree (2 steps)
-- ============
-- reparent #6 from #4 -> #3
--
-- Step 1: Disconnect from current ancestors
-- -----------------------------------------
-- delete all paths that end at descendants in the current node's subtree
-- and that begin at ancestors of the current node (6).
DELETE FROM TreePaths
WHERE descendant IN (SELECT descendant
                       FROM TreePaths
                      WHERE ancestor = 6)
    AND ancestor IN (SELECT ancestor
                       FROM TreePaths
                      WHERE descendant = 6
                        AND ancestor != descendant);

-- Step 2: Insert rows matching ancestors of insertion point and descendants of subtree
-- ------------------------------------------------------------------------------------
-- This uses CROSS JOIN to get the cross product of the new parent's ancestors, including the new parent,
-- with the subtree's nodes. This is one case where the full cartesian product is useful.
INSERT INTO TreePaths (ancestor, descendant, path_length)
  SELECT 
    supertree.ancestor, 
    subtree.descendant, 
    supertree.path_length + subtree.path_length + 1 AS path_length
    FROM TreePaths AS supertree
      CROSS JOIN TreePaths AS subtree
   WHERE supertree.descendant = 3
     AND subtree.ancestor = 6;