3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT NOT NULL, FOREIGN KEY (parent_id) REFERENCES Comments(comment_id) );
3.1
3.2 Trees/anti/adjacency-list.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, bug_id BIGINT UNSIGNED NOT NULL, author BIGINT UNSIGNED NOT NULL, comment_date DATETIME NOT NULL, comment TEXT NOT NULL, FOREIGN KEY (parent_id) REFERENCES Comments(comment_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id), FOREIGN KEY (author) REFERENCES Accounts(account_id) );
Trees/anti/parent.sql SELECT c1.*, c2.* FROM Comments c1 LEFT OUTER JOIN Comments c2 ON c2.parent_id = c1.comment_id;
Trees/anti/ancestors.sql SELECT c1.*, c2.*, c3.*, c4.* FROM Comments c1 -- 1 LEFT OUTER JOIN Comments c2 ON c2.parent_id = c1.comment_id -- 2 LEFT OUTER JOIN Comments c3 ON c3.parent_id = c2.comment_id -- 3 LEFT OUTER JOIN Comments c4 ON c4.parent_id = c3.comment_id; -- 4 Trees/anti/all-comments.sql SELECT * FROM Comments WHERE bug_id = 1234;
Trees/anti/insert.sql INSERT INTO Comments (bug_id, parent_id, author, comment) VALUES (1234, 7, Kukla, Thanks! ); Trees/anti/update.sql UPDATE Comments SET parent_id = 3 WHERE comment_id = 6; Trees/anti/delete-subtree.sql SELECT comment_id FROM Comments WHERE parent_id = 4; -- SELECT comment_id FROM Comments WHERE parent_id = 5; -- SELECT comment_id FROM Comments WHERE parent_id = 6; -- SELECT comment_id FROM Comments WHERE parent_id = 7; -- DELETE FROM Comments WHERE comment_id IN ( 7 ); DELETE FROM Comments WHERE comment_id IN ( 5, 6 ); DELETE FROM Comments WHERE comment_id = 4; Trees/anti/delete-non-left.sql SELECT parent_id FROM Comments WHERE comment_id = 6; -- UPDATE Comments SET parent_id = 4 WHERE parent_id = 6; DELETE FROM Comments WHERE comment_id = 6;
3.3
3.4
Trees/legit/cte.sql WITH CommentTree (comment_id, bug_id, parent_id, author, comment, depth) AS ( SELECT *, 0 AS depth FROM Comments WHERE parent_id IS NULL UNION ALL SELECT c.*, ct.depth+1 AS depth FROM CommentTree ct JOIN Comments c ON (ct.comment_id = c.parent_id) ) SELECT * FROM CommentTree WHERE bug_id = 1234; Trees/legit/connect-by.sql SELECT * FROM Comments START WITH comment_id = 9876 CONNECT BY PRIOR parent_id = comment_id; 3.5
Trees/soln/path-enum/create-table.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, path VARCHAR(1000), 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) );
Trees/soln/path-enum/ancestors.sql SELECT * FROM Comments AS c WHERE 1/4/6/7/ LIKE c.path % ; Trees/soln/path-enum/descendants.sql SELECT * FROM Comments AS c WHERE c.path LIKE 1/4/ % ;
Trees/soln/path-enum/count.sql SELECT COUNT(*) FROM Comments AS c WHERE c.path LIKE 1/4/ % GROUP BY c.author; Trees/soln/path-enum/insert.sql INSERT INTO Comments (author, comment) VALUES ( Ollie, Good job! ); UPDATE Comments SET path = (SELECT path FROM Comments WHERE comment_id = 7) LAST_INSERT_ID() / WHERE comment_id = LAST_INSERT_ID();
Trees/soln/nested-sets/create-table.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, nsleft INTEGER NOT NULL, nsright INTEGER NOT NULL, 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) );
Trees/soln/nested-sets/descendants.sql SELECT c2.* FROM Comments AS c1 JOIN Comments as c2 ON c2.nsleft BETWEEN c1.nsleft AND c1.nsright WHERE c1.comment_id = 4; Trees/soln/nested-sets/ancestors.sql SELECT c2.* FROM Comments AS c1 JOIN Comment AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright WHERE c1.comment_id = 6; Trees/soln/nested-sets/depth.sql -- depth = 3 SELECT c1.comment_id, COUNT(c2.comment_id) AS depth FROM Comment AS c1 JOIN Comment AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright WHERE c1.comment_id = 7 GROUP BY c1.comment_id;
DELETE FROM Comment WHERE comment_id = 6; -- depth = 2 SELECT c1.comment_id, COUNT(c2.comment_id) AS depth FROM Comment AS c1 JOIN Comment AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright WHERE c1.comment_id = 7 GROUP BY c1.comment_id; Trees/soln/nested-sets/parent.sql SELECT parent.* FROM Comment AS c JOIN Comment AS parent ON c.nsleft BETWEEN parent.nsleft AND parent.nsright LEFT OUTER JOIN Comment AS in_between ON c.nsleft BETWEEN in_between.nsleft AND in_between.nsright AND in_between.nsleft BETWEEN parent.nsleft AND parent.nsright WHERE c.comment_id = 6 AND in_between.comment_id IS NULL;
Trees/soln/nested-sets/insert.sql -- NS 8 9 UPDATE Comment SET nsleft = CASE WHEN nsleft >= 8 THEN nsleft+2 ELSE nsleft END, nsright = nsright+2 WHERE nsright >= 7; -- #5, NS 8, 9 INSERT INTO Comment (nsleft, nsright, author, comment) VALUES (8, 9, Fran, Me too! ); Trees/soln/closure-table/create-table.sql 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) ); Trees/soln/closure-table/descendants.sql SELECT c.* FROM Comments AS c JOIN TreePaths AS t ON c.comment_id = t.descendant WHERE t.ancestor = 4;
Trees/soln/closure-table/ancestors.sql SELECT c.* FROM Comments AS c JOIN TreePaths AS t ON c.comment_id = t.ancestor WHERE t.descendant = 6;
Trees/soln/closure-table/insert.sql INSERT INTO TreePaths (ancestor, descendant) SELECT t.ancestor, 8 FROM TreePaths AS t WHERE t.descendant = 5 UNION ALL SELECT 8, 8; Trees/soln/closure-table/delete-left.sql DELETE FROM TreePaths WHERE descendant = 7; Trees/soln/closure-table/delete-subtree.sql DELETE FROM TreePaths WHERE descendant IN (SELECT descendant WHERE descendant IN (FROM TreePaths WHERE descendant IN (WHERE ancestor = 4);
Trees/soln/closure-table/move-subtree.sql DELETE FROM TreePaths WHERE descendant IN (SELECT descendant WHERE descendant IN (FROM TreePaths WHERE descendant IN (WHERE ancestor = 6) AND ancestor IN (SELECT ancestor AND ancestor IN (FROM TreePaths AND ancestor IN (WHERE descendant = 6 AND ancestor IN (AND ancestor!= descendant); Trees/soln/closure-table/move-subtree.sql INSERT INTO TreePaths (ancestor, descendant) SELECT supertree.ancestor, subtree.descendant FROM TreePaths AS supertree CROSS JOIN TreePaths AS subtree WHERE supertree.descendant = 3 AND subtree.ancestor = 6;
Trees/soln/closure-table/child.sql SELECT * FROM TreePaths WHERE ancestor = 4 AND path_length = 1;
SQL Antipatterns Tip