Moving nodes in a hierarchy database table

Question

To start with the bigger picture: This problem is part of a feature for dragging and dropping nodes in a hierarchy tree.

We have a table called Rooms that uses the hierarchy feature in SQL. We also have a stored procedure that re-parents a child node within this table. The stored procedure takes two parameters, the new parent id and a list of that parent's new child nodes (both old and new).

The problem we are having is the node value that the new children gets aren't unique, and the problem occurrs in the update statement in the following script.

Note: This script is created to simulate the problem and is not the actual stored procedure:

/** Declare RoomMoveList type **/
CREATE TYPE [RoomMoveList] AS TABLE(
    [RoomId] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
(
    [RoomId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)

/** Create Rooms table **/
create table Rooms
(
    RoomId int identity not null,
    RoomName nvarchar(100) not null,
    Node hierarchyid not null,
    NodePath as Node.ToString() persisted not null,
    ParentNode as Node.GetAncestor(1) persisted,
    ParentNodePath as Node.GetAncestor(1).ToString() persisted
)
go

--primary key
alter table Rooms
    add constraint PK_Rooms primary key nonclustered (RoomId)
go

--alternate key
alter table Rooms
    add constraint AK_Rooms unique clustered (Node)
go

--foreign keys
alter table Rooms
    add constraint FK_Rooms_ParentNode_Rooms_Node foreign key (ParentNode) references Rooms(Node)
go

-- insert data
set identity_insert Rooms on
INSERT INTO Rooms (RoomId, RoomName, Node) values
(1, 'Root', '/'),
(2, 'Room 7','/7/'),
(3, 'Room A', '/7/1/'),
(4, 'Booth A1', '/7/1/1/'),
(5, 'Room B', '/7/2/'),
(6, 'Booth B1', '/7/2/1/'),
(7, 'Booth B2', '/7/2/2/'),
(8, 'Booth B3', '/7/2/3/'),
(9, 'Booth B4', '/7/2/4/'),
(10, 'Booth B5', '/7/2/5/'),
(11, 'Room C', '/7/3/'),
(12, 'Booth C1', '/7/3/1/'),
(13, 'Booth C2', '/7/3/2/'),
(14, 'Booth C3', '/7/3/3/')
set identity_insert Rooms off

/* Here's the first part of the actual stored procedure */
declare
    @newParentNode hierarchyid,
    @newNode hierarchyid, -- This is a new node that currently does not exist in rooms
    @nodeToMove hierarchyid,
    @newParentRoomId int,
    @roomMoves as RoomMoveList;

    -- these are the input values for the stored procedure that we simulate here.
    set @newParentRoomId = 3
    insert into @roomMoves (RoomId) values
    (4), (5), (11)

    set transaction isolation level serializable

begin tran
    -- First select Node from parent id, and then get next new descendant
    select @newParentNode = Node from Rooms where RoomId = @newParentRoomId

    select @newNode = @newParentNode.GetDescendant(max(Node), null)
    from Rooms
    where Node.GetAncestor(1) = @newParentNode;

    -- Need to cache these since we'll use them multiple times in the real query
    select f.RoomId
            , IIF(f.ParentNode <> @newParentNode, 1, 0) as Moved
    into #TempMoves
    from Rooms f
                join @roomMoves fm
                    on fm.RoomId = f.RoomId;

    /* This is the failing update statement. */
    -- Update nodes, but only the moved nodes
    ;with CTE_ROOM_NODES as (
        select f.RoomId
                , f.Node
                , f.NodePath
        from Rooms f
                    join #TempMoves fm on fm.RoomId = f.RoomId
        where fm.Moved = 1
    )
    update Rooms
    set Node      = Rooms.Node.GetReparentedValue(ctn.Node, @newNode)
    from CTE_ROOM_NODES ctn
    where Rooms.Node.IsDescendantOf(ctn.Node) = 1;

    /* The stored procedure continues here but I'm leaving that out since the error is in the update statement above */

commit tran

select * from Rooms

drop table #TempMoves
drop table Rooms
drop type RoomMoveList

Some more information:

• If you comment out the two constraints AK_Rooms and FK_Rooms_ParentNode_Rooms_Node then you'll see the actual faulty table with non unique values.

• If you remove id 11 from the simulated input values everything works. Because the first new child node will get a unique value, but the second new child node will get the same value as the first new child node.

Here's before and after the moving of the location. We click and drag Room A to be a sibling to Room 7, and Booth A1 will be come siblings to Room B and Room C.

__________

Let me know if I've missed something or if I can improve the question!

Answer 1

You need to recalculate the hierarchyids of the moved elements relatively to their new location(position in the hierarchy).

@newNode = @newParentNode.GetDescendant(max(Node), null) -- this returns the maximum node in the new location

Rooms.Node.GetReparentedValue(ctn.Node, @newNode) --every moved room is re-parented to @newNode. 

When multiple elements/rooms are moved their re-parented values can overlap (or overlap with existing children of the new parent). eg.

B1 = "B/1", C1 = "C/1"
if they get re-parented to A:
B1 = "A/1", C1 = "A/1" and constraint conflict

/* Here's the first part of the actual stored procedure */
declare
    @newParentNode hierarchyid,
    @newNode hierarchyid, -- This is a new node that currently does not exist in rooms
    @nodeToMove hierarchyid,
    @newParentRoomId int,
    @roomMoves as RoomMoveList;

-- these are the input values for the stored procedure that we simulate here.
set @newParentRoomId = 3
insert into @roomMoves (RoomId) values
(4), (5), (11)

set transaction isolation level serializable

begin tran   


    -- First select Node from parent id, and then get next new descendant
    select @newParentNode = Node from Rooms where RoomId = @newParentRoomId

    select @newNode = @newParentNode.GetDescendant(max(Node), null)
    from Rooms
    where Node.GetAncestor(1) = @newParentNode;
    -- Need to cache these since we'll use them multiple times in the real query

--###### this will work if nodes are numeric /1/2/3/ , and not '/1/2.1/3/'
    --get the ordinal of the max node
    declare @movedroomsstartordinal int;
    select @movedroomsstartordinal = try_cast(replace(replace(isnull(max(Node).ToString(), @newParentNode.ToString()), @newParentNode.ToString(), ''), '/', '') as int)
    from Rooms
    where Node.GetAncestor(1) = @newParentNode;


    select RoomId, Moved, 
        --increase the ordinal of each moved member (max existing + ordinal of each new member)
        try_cast(concat(@newParentNode.ToString(), case Moved when 1 then @movedroomsstartordinal + rownumofmovedroom else null end, '/') as hierarchyid) as thenewnode
    into #TempMoves
    from
    (
    select f.RoomId
            , IIF(f.ParentNode <> @newParentNode, 1, 0) as Moved,
            row_number() over(partition by IIF(f.ParentNode <> @newParentNode, 1, 0) order by f.RoomId) as rownumofmovedroom
    from Rooms f
                join @roomMoves fm
                    on fm.RoomId = f.RoomId
    ) as src;

--check if anything is wrong
--if exists(select * from #TempMoves where moved=1 and thenewnode is null)....
--raiserror etc..

    -- Update nodes, but only the moved nodes
    ;with CTE_ROOM_NODES as (
        select f.RoomId
                , f.Node
                , f.NodePath
                , fm.thenewnode
        from Rooms f
                    join #TempMoves fm on fm.RoomId = f.RoomId
        where fm.Moved = 1
    )
    update Rooms
    set Node      = Rooms.Node.GetReparentedValue(ctn.Node, thenewnode)
    from CTE_ROOM_NODES ctn
    where Rooms.Node.IsDescendantOf(ctn.Node) = 1;

    /* The stored procedure continues here but I'm leaving that out since the error is in the update statement above */

commit tran