Re: Nested Sets and a typical Extranet Example

>> Since I'm relatively new to this concept and still don't fully
grasp the concept of LEFT and RIGHT values .. <<

Have you used algebra, HTML or anything with parens in its notation? That is what the (lft, rgt) pairs are! And don't use the reserved words LEFT and RIGHT for the columns. You are just counting the parens from left to right. Easy, unh?

>> I would like to start blank with only the root group. I would then
build the front end which would allow new groups (parents and children) to be added.<<

The nested set model has an implied ordering of siblings which the adjacency list model does not. To insert a new node, G1, under part G.  We can insert one node at a time like this:

BEGIN ATOMIC
DECLARE rightmost_spread INTEGER;

SET rightmost_spread

• (SELECT rgt FROM Frammis WHERE part = 'G'); UPDATE Frammis SET lft = CASE WHEN lft > rightmost_spread THEN lft + 2 ELSE lft END, rgt = CASE WHEN rgt >= rightmost_spread THEN rgt + 2 ELSE rgt END WHERE rgt >= rightmost_spread;

 INSERT INTO Frammis (part, lft, rgt)
 VALUES ('G1', rightmost_spread, (rightmost_spread + 1));  COMMIT WORK;
END; The idea is to spread the (lft, rgt) numbers after the youngest child of the parent, G in this case, over by two to make room for the new addition, G1. This procedure will add the new node to the rightmost child position, which helps to preserve the idea of an age order among the siblings.

I have a whole book in April on Trees and Hierarchies in SQL. Received on Fri Sep 05 2003 - 21:46:58 CEST