Subject | How to insert a new node in a tree - nested sets model |
---|---|

Author | constantijnw |

Post date | 2005-11-27T13:40:47Z |

Hi,

I have a table TESTTREE representing a tree structure with the nested

sets model.

CREATE TABLE TESTTREE (

ID INTEGER NOT NULL,

LFT INTEGER NOT NULL,

RGT INTEGER NOT NULL,

NODENAME VARCHAR(20) NOT NULL);

For inserting a new node I specify its position in the tree by giving

the parent's ID and

its rank among other children of this parent.

My problem is to get the RGT value of the CHILDRANK - 1 child? How does the

SQL statement look like?

CREATE PROCEDURE INS_NODE (

IDPARENT INTEGER,

CHILDRANK INTEGER,

NODENAME VARCHAR(20))

AS

DECLARE VARIABLE NEW_LFT INTEGER;

DECLARE VARIABLE NEW_RGT INTEGER;

DECLARE VARIABLE PRNT_LFT INTEGER;

DECLARE VARIABLE PRNT_RGT INTEGER;

BEGIN

/*

Determine the right nested sets value. Every LFT and RGT equal or

higher must be

increased by 2. Then the actual insert can be done.

*/

-- Get LFT and RGT of the parent

SELECT LFT, RGT

FROM TESTTREE

WHERE ID = :IDPARENT

INTO :PRNT_LFT, :PRNT_RGT;

-- Determine NEW_RGT according to the CHILDRANK specified.

IF (PRNT_RGT - PRNT_LFT = 1) OR (CHILDRANK = 1) THEN

-- The parent has no children yet, so CHILDRANK doesn't matter or

the leftmost position is specified.

NEW_LFT = PRNT_LFT + 1;

ELSE IF ((CHILDRANK IS NULL) OR (CHILDRANK = 0)) THEN

-- Append the new childnode at the right

NEW_LFT = PRNT_RGT;

ELSE

BEGIN

-- Get the RGT value of the child left of the new node.

?????

????????

END

NEW_RGT = NEW_LFT + 1;

-- Update LFT and RGT values of all nodes after the new one

UPDATE TESTTREE

SET LFT = CASE WHEN LFT >= NEW_LFT THEN LFT + 2 ELSE LFT,

RGT = CASE WHEN RGT >= NEW_RGT THEN RGT + 2 ELSE RGT

-- Do the actual insert of the new node

INSERT INTO TESTTREE

(LFT, RGT, NODENAME)

VALUES

(:NEW_LFT, :NEW_RGT, :NODENAME);

END

I have a table TESTTREE representing a tree structure with the nested

sets model.

CREATE TABLE TESTTREE (

ID INTEGER NOT NULL,

LFT INTEGER NOT NULL,

RGT INTEGER NOT NULL,

NODENAME VARCHAR(20) NOT NULL);

For inserting a new node I specify its position in the tree by giving

the parent's ID and

its rank among other children of this parent.

My problem is to get the RGT value of the CHILDRANK - 1 child? How does the

SQL statement look like?

CREATE PROCEDURE INS_NODE (

IDPARENT INTEGER,

CHILDRANK INTEGER,

NODENAME VARCHAR(20))

AS

DECLARE VARIABLE NEW_LFT INTEGER;

DECLARE VARIABLE NEW_RGT INTEGER;

DECLARE VARIABLE PRNT_LFT INTEGER;

DECLARE VARIABLE PRNT_RGT INTEGER;

BEGIN

/*

Determine the right nested sets value. Every LFT and RGT equal or

higher must be

increased by 2. Then the actual insert can be done.

*/

-- Get LFT and RGT of the parent

SELECT LFT, RGT

FROM TESTTREE

WHERE ID = :IDPARENT

INTO :PRNT_LFT, :PRNT_RGT;

-- Determine NEW_RGT according to the CHILDRANK specified.

IF (PRNT_RGT - PRNT_LFT = 1) OR (CHILDRANK = 1) THEN

-- The parent has no children yet, so CHILDRANK doesn't matter or

the leftmost position is specified.

NEW_LFT = PRNT_LFT + 1;

ELSE IF ((CHILDRANK IS NULL) OR (CHILDRANK = 0)) THEN

-- Append the new childnode at the right

NEW_LFT = PRNT_RGT;

ELSE

BEGIN

-- Get the RGT value of the child left of the new node.

?????

????????

END

NEW_RGT = NEW_LFT + 1;

-- Update LFT and RGT values of all nodes after the new one

UPDATE TESTTREE

SET LFT = CASE WHEN LFT >= NEW_LFT THEN LFT + 2 ELSE LFT,

RGT = CASE WHEN RGT >= NEW_RGT THEN RGT + 2 ELSE RGT

-- Do the actual insert of the new node

INSERT INTO TESTTREE

(LFT, RGT, NODENAME)

VALUES

(:NEW_LFT, :NEW_RGT, :NODENAME);

END