Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Nested Sets Insertion

Re: Nested Sets Insertion

From: Enu <ewiner_at_adelphia.net>
Date: Mon, 12 May 2003 02:42:03 GMT
Message-ID: <fe2ubv01u3vhn6p7ivrkivds46ucbbobhb@4ax.com>


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 Sun May 11 2003 - 21:42:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US