Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

Share this Page URL

8.3 Hybrid Models > 8.3.2 Nested Sets with Depth Model - Pg. 185

176 CHAPTER 8: OTHER MODELS FOR TREES lft INTEGER DEFAULT 0 NOT NULL, rgt INTEGER DEFAULT 0 NOT NULL); INSERT INTO Tree VALUES ('A', NULL, 1, 18), ('B', 'A', 2, 3), ('C', 'A', 4, 11), ('D', 'C', 5, 6), ('E', 'C', 7, 8), ('F', 'C', 9, 10), ('G', 'A', 12, 17), ('H', 'G', 13, 14), ('I', 'G', 15, 16); The advantage of this model is that you can insert nodes using this statement and let the default values take effect. INSERT INTO Tree (node, parent) VALUES (:my_node, :my_parent); The clean-up procedure has to detect any (0, 0) pairs in the tree table. If there is at least one such pair, we know nodes have been added, so the procedure needs to perform a complete rebuild of the tree from the (child, parent) columns. If there is no such pair we know that nodes might have been deleted, so the procedure needs to rerenumber the (lft, rgt) columns. 8.3.2 Nested Sets with Depth Model This approach retains the level or depth in each row of a nested sets model, disregarding constraints, thus: CREATE TABLE Tree (node CHAR(5) NOT NULL, "depth" INTEGER NOT NULL, -- depth is reserved in Standard SQL lft INTEGER NOT NULL, rgt INTEGER NOT NULL); INSERT INTO Tree VALUES ('A', 1, 1, 18), ('B', 2, 2, 3), ('C', 2, 4, 11),