Re: Nested Sets Insertion

On 10 May 2003 20:16:24 -0700, 71062.1056_at_compuserve.com (--CELKO--) wrote:

>>> INSERT and UPDATE statements that can append a child to a node and
>update the left and right values of the affected nodes. <<
>
>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 and 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.
>
>To convert a nested sets model into an adjacency list model:
>
>SELECT B.emp AS boss, P.emp
> FROM OrgChart AS P
> LEFT OUTER JOIN
> OrgChart AS B
> ON B.lft
> = (SELECT MAX(lft)
> FROM OrgChart AS S
> WHERE P.lft > S.lft
> AND P.lft < S.rgt);

Thanks for the help! Are you the Joe Celko who pioneered this sort of thing? I never knew that real experts actually monitored Usenet. Received on Mon May 12 2003 - 04:42:03 CEST