Inserting into a hierachy (nested set model)

tjakobsen · #1 (**permalink**) 10-29-09, 12:24

Hello,

I have a hierachy of groups modelled as a nested set. Like Joe Celko does it in his book "Trees and hierachies in SQL for smarties".

I have a problem creating a query that will insert a new node at the rightmost position under a given parent node.

If I have the tree:

Code:

           node1
       /     |     \
     /       |       \
   /         |         \
node2      node3     node4
  |
node5

I want to insert a new node6 here:

Code:

           node1   
         
     /    |     |   \
    /     |     |    \        
   /      |     |     \
node2   node3  node4  node6
   |
node5

In Celkos book, he gives a procedure that would add the new node, given the parent node (node1), as a sibling to node4. And another procedure that would add the new node to the right of a given sibling (node4). That approach is close to what I need, but I need to add it using the parent node.

Anyone who has a procedure that can help me do that?

Thanks in advance!

Wim · #2 (**permalink**) 10-29-09, 14:31

Quote:

Joe Celko does it in his book "Trees and hierachies in SQL for smarties".

I'm no smarty, so I haven't read his book.

Can you give an example dataset before and after, so I can figure out what you want?

blindman · #3 (**permalink**) 10-29-09, 16:40

Basically, you will increment the right node of your parent node by 2 (along with all subsequent nodes of course), and then set the new child's left node to the parents old right node, and the child's right node to 1 higher than that.

MCrowley · #4 (**permalink**) 10-29-09, 16:48

If you happen to be using SQL Server 2008, why not use the hierarchyid?

tjakobsen · #5 (**permalink**) 10-30-09, 07:31

Quote:

Originally Posted by Wim

I'm no smarty, so I haven't read his book.

Can you give an example dataset before and after, so I can figure out what you want?

This is the data:

Code:

groupId   lft          rgt
138	10	11
140	12	13
148	3	4
802	9	14
945	6	7
963	4	5
980	1	8

Quote:

Originally Posted by blindman

Basically, you will increment the right node of your parent node by 2 (along with all subsequent nodes of course), and then set the new child's left node to the parents old right node, and the child's right node to 1 higher than that.

Can you help me modify my existing procedure (by Joe Celko), to do that? I can't get it working. Another problem that I ran into when trying to solve it, is when the note given as parent node has no childs (and therefor not really a parent node), then it should become a parent node, with the new node as a the only child.

Here is Joe Celko's SP (which he credit to Mark E. Barney), that will insert the new node as a child to the rightmost child of the parent node:

Code:

CREATE PROCEDURE [dbo].[InsertNewNode] (
	@new_group int, @parent_group int)
AS

DECLARE @parent int
SET @parent = (SELECT rgt
				FROM ActiveDirectory.GroupRelTest
				WHERE groupId = @parent_group)

UPDATE ActiveDirectory.GroupRelTest
	SET lft = CASE WHEN lft > @parent
				THEN lft + 2
				ELSE lft END,
		rgt = CASE WHEN rgt >= @parent
				THEN rgt + 2
				ELSE rgt END
WHERE rgt >= @parent

INSERT INTO ActiveDirectory.GroupRelTest (groupId, lft, rgt)
VALUES (@new_group, @parent, (@parent + 1))

Any help will be much appreciated.

Quote:

Originally Posted by MCrowley

If you happen to be using SQL Server 2008, why not use the hierarchyid?

Unfortunately, I'm using SQL Server 2005.