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: --CELKO-- <71062.1056_at_compuserve.com>
Date: 10 May 2003 20:16:24 -0700
Message-ID: <c0d87ec0.0305101916.3a6f873d@posting.google.com>


>> 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

 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);
Received on Sat May 10 2003 - 22:16:24 CDT

Original text of this message

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