Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
142 lines (124 sloc) 4.37 KB
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'P' AND [object_id] = OBJECT_ID('nsm.[MoveCatSubtree]'))
EXEC('CREATE PROCEDURE nsm.[MoveCatSubtree] AS BEGIN SET NOCOUNT ON; END')
GO
--=============================================
--Author: NJohnson9402, aka natethedba.wordpress.com
--Created: 20170104
--Description: move a "subtree" in the nsm.Cat table to a new Parent node,
--& maintain NSM (Nested Set Model) position values & Depth.
--=============================================
ALTER PROCEDURE nsm.[MoveCatSubtree]
@CatID INT
, @NewParentID INT
, @Debug BIT = 0
AS
BEGIN
SET NOCOUNT ON;
--Disable triggers during operations
ALTER TABLE nsm.Cat DISABLE TRIGGER trg_Cat_DEL;
ALTER TABLE nsm.Cat DISABLE TRIGGER trg_Cat_INS;
ALTER TABLE nsm.Cat DISABLE TRIGGER trg_Cat_UPD;
--Treat 0/-1/NULL the same: means we want to make the top of this subtree a Root node
IF (@NewParentID <= 0 OR @NewParentID IS NULL)
BEGIN
SET @NewParentID = -1;
END
--Cannot move a subtree under itself
ELSE IF @NewParentID IN (
SELECT SubCat.CatID
FROM nsm.Cat Cat
JOIN nsm.Cat SubCat
ON SubCat.PLeft BETWEEN Cat.PLeft AND Cat.PRight
WHERE Cat.CatID = @CatID)
BEGIN
RAISERROR (N'Cannot move subtree to a node within itself.', 18, 1);
RETURN;
END
--Cannot move subtree to a node that doesnt exist
ELSE IF NOT EXISTS (SELECT 1 FROM nsm.Cat WHERE CatID = @NewParentID)
BEGIN
RAISERROR (N'Cannot move subtree to a node that doesn''t exist.', 18, 1);
RETURN;
END
--Cannot move subtree that doesnt exist
ELSE IF NOT EXISTS (SELECT 1 FROM nsm.Cat WHERE CatID = @CatID)
BEGIN
RAISERROR (N'Cannot move subtree that doesn''t exist.', 18, 1);
RETURN;
END
--Get old Parent & Subtree size
DECLARE @OldParentID INT
, @SubtreeSize INT
, @SubtreeOldLeft INT
, @SubtreeOldRight INT
, @SubtreeOldDepth INT
SELECT @OldParentID = ParentID, @SubtreeSize = PRight - PLeft + 1
, @SubtreeOldLeft = PLeft, @SubtreeOldRight = PRight, @SubtreeOldDepth = Depth
FROM nsm.Cat
WHERE CatID = @CatID
--Cannot move subtree to its own Parent, i.e. there's nothing to do b/c new parent is same as old
IF @OldParentID = @NewParentID
BEGIN
RAISERROR (N'Cannot move subtree to its own parent.', 18, 1);
RETURN;
END
--Get new Parent position
DECLARE @NewParentRight INT
, @NewParentDepth INT;
--If we're going Root, place it to the Right of existing Roots
IF @NewParentID = -1
BEGIN
SELECT @NewParentRight = MAX(PRight) + 1, @NewParentDepth = -1
FROM nsm.Cat
END
--Else, place it to the Right of its new siblings-to-be
ELSE
BEGIN
SELECT @NewParentRight = PRight, @NewParentDepth = Depth
FROM nsm.Cat
WHERE CatID = @NewParentID
END
--Get new positions for use
SELECT CatID
, PLeft + @NewParentRight - @SubtreeOldLeft AS PLeft
, PRight + @NewParentRight - @SubtreeOldLeft AS PRight
, Depth + (@NewParentDepth - @SubtreeOldDepth) + 1 AS Depth
INTO #MoveNodes
FROM nsm.Cat
WHERE CatID IN (
SELECT SubCat.CatID
FROM nsm.Cat Cat
JOIN nsm.Cat SubCat
ON SubCat.PLeft BETWEEN Cat.PLeft AND Cat.PRight
WHERE Cat.CatID = @CatID
)
IF (@Debug = 1)
SELECT * FROM #MoveNodes
ORDER BY PLeft
--Make gap in tree (at destination branch) equal to the SubtreeSize
UPDATE nsm.Cat
SET PLeft = CASE WHEN PLeft > @NewParentRight THEN PLeft + @SubtreeSize ELSE PLeft END,
PRight = CASE WHEN PRight >= @NewParentRight THEN PRight + @SubtreeSize ELSE PRight END
WHERE PRight >= @NewParentRight
--Update Subtree positions to new ones
UPDATE nsm.Cat
SET PLeft = #MoveNodes.PLeft, PRight = #MoveNodes.PRight, Depth = #MoveNodes.Depth
FROM nsm.Cat
JOIN #MoveNodes
ON nsm.Cat.CatID = #MoveNodes.CatID
--Maintain the Adjacency-List part (set ParentID)
UPDATE nsm.Cat
SET ParentID = (CASE WHEN @NewParentID = -1 THEN NULL ELSE @NewParentID END)
WHERE CatID = @CatID
--Close gaps, i.e. after the Subtree is gone from its old Parent, said old parent node has no children;
--while nodes to the right & above now have inflated values, except where they include the newly moved subtree.
UPDATE nsm.Cat
SET PLeft = CASE WHEN PLeft > @SubtreeOldRight THEN PLeft - @SubtreeSize ELSE PLeft END,
PRight = CASE WHEN PRight >= @SubtreeOldRight THEN PRight - @SubtreeSize ELSE PRight END
WHERE PRight >= @SubtreeOldRight
--Re-enable triggers when done
ALTER TABLE nsm.Cat ENABLE TRIGGER trg_Cat_DEL;
ALTER TABLE nsm.Cat ENABLE TRIGGER trg_Cat_INS;
ALTER TABLE nsm.Cat ENABLE TRIGGER trg_Cat_UPD;
END
GO
You can’t perform that action at this time.