Lieven Cardoen

/****** 
 * Object:  StoredProcedure [dbo].[sp_MoveTreeStructure]    
 * Script Date: 08/16/2008 19:45:40 
 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_MoveTreeStructure]
	@TreeStructureId INT,		-- TreeStructure (Node) to be moved
	@ParentTreeStructureId INT,	-- Target TreeStructure to be moved to
	@INDEX INT				-- Place where TreeStructure should be moved to
AS
BEGIN
	SET NOCOUNT ON;
 
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
	DECLARE @TranName VARCHAR(20);
	SELECT @TranName = 'MoveTreeStructure';
 
	BEGIN TRANSACTION @TranName
 
	BEGIN TRY	
 
		DECLARE @OriginalVolume INT
		DECLARE @DiffTarget INT
 
		DECLARE @OriginalLeft INT
		DECLARE @OriginalRight INT
		DECLARE @OriginalTreeId INT
 
		DECLARE @TargetLeft INT
		DECLARE @TargetRight INT
		DECLARE @TargetTreeId INT
		DECLARE @TargetTreeStructureId INT
 
		DECLARE @LastPlace bit
		SET   	@LastPlace = 0
 
		/******************************************************/	
 
		/**
		 * If @ParentTreeStructureId is NULL, we asume that 
		 * movement takes place under the same Folder
		 */
 
			IF (@ParentTreeStructureId IS NULL)
				BEGIN
					SELECT @ParentTreeStructureId = dbo.fn_Edumatic3_GetParentTreeStructure(@TreeStructureId)
				END
 
		/******************************************************/	
 
 
		/**
		 * If Movement is under same ParentTreeStructure and forward, 
		 * then Index needs to be +1
		 * For example, if the first child wants to be moved to 
		 * second place, you would give an index of 1
		 * saying the node wants to be inserted between first 
		 * and second child. Because the moved node is the
		 * first child, it really needs to be inserted between child 2 
		 * and 3. Child 2 will become child 1, the inserted
		 * child (used to be child 1) will become child 2 and child 3 
		 * stays 3. Difficult to explain... Draw it and you'll see.
		 */
 
			IF @ParentTreeStructureId = dbo.fn_Edumatic3_GetParentTreeStructure(@TreeStructureId) 
				AND	@INDEX >= [dbo].[fn_Edumatic3_GetIndexOf](@TreeStructureId)
				BEGIN
					SET @INDEX = @INDEX + 1
				END
 
		/******************************************************/	
 
		/**
		 * Retrieve the original left/right and tree values
		 */
 
			SELECT	@OriginalLeft = LeftValue, @OriginalRight = RightValue, @OriginalTreeId = TreeId
			FROM	TreeStructures 
			WHERE	TreeStructureId = @TreeStructureId
 
		/******************************************************/	
 
		/**
		 * Retrieve the target left/right and tree values. 
		 * Here there's an exception to be caught.
		 * If the node is to be moved to the last place, 
		 * the @TargetTreeStructureId will be -1. We look for the
		 * Node before which the moved node will be inserted. 
		 * In this case there's is no last Node.
		 * If this happens, we take the last Node and insert the 
		 * Node to be moved after this last node instead of before.
		 */
 
			SET @TargetTreeStructureId = dbo.[fn_Edumatic3_GetChildTreeStructure](@ParentTreeStructureId, @INDEX)
 
			IF @TargetTreeStructureId <> -1
				BEGIN
					-- Retrieve the target left/right and tree values
					SELECT	@TargetLeft = LeftValue, 
							@TargetRight = RightValue, 
							@TargetTreeId = TreeId
					FROM	TreeStructures 
					WHERE	TreeStructureId = @TargetTreeStructureId
				END
			ELSE
				BEGIN
					SET @TargetTreeStructureId = dbo.[fn_Edumatic3_GetChildTreeStructure](@ParentTreeStructureId, (@Index-1))
 
					SELECT	@TargetLeft = LeftValue, 
							@TargetRight = RightValue, 
							@TargetTreeId = TreeId
					FROM	TreeStructures 
					WHERE	TreeStructureId = @TargetTreeStructureId
 
					SET   	@LastPlace = 1
				END
 
			SET   	@OriginalVolume = @OriginalRight - @OriginalLeft + 1
 
		/******************************************************/	
 
		/**
		 * Setting the Left and Right Values of TreeStructure 
		 * that needs to be replaced to - Left/Right Value
		 * This needs to be done because these Left and Right 
		 * values may not be changed by queries further down
		 * the road. At the end they are being made positive again. 
		 * Draw some cases if you want this to be clearder to you.
		 */
 
			UPDATE	TreeStructures 
			SET   	LeftValue = -LeftValue,
					RightValue = -RightValue
			WHERE	TreeId = @OriginalTreeId
					AND LeftValue >= @OriginalLeft
					AND RightValue <= @OriginalRight
 
		/******************************************************/	
 
		/**
		 * If the Node is not inserted to the last place, 
		 * then we insert it before the TargetTreeStructure
		 * If it is to be inserted to the last place, 
		 * we insert it after the TargetTreeStructure
		 */
		IF @LastPlace = 0
			BEGIN
				-- Update + volume left/right >= target
				UPDATE	TreeStructures 
				SET   	LeftValue = LeftValue + @OriginalVolume
				WHERE	TreeId = @TargetTreeId 
						AND LeftValue >= @TargetLeft
 
				UPDATE	TreeStructures 
				SET   	RightValue = RightValue + @OriginalVolume  
				WHERE	TreeId = @TargetTreeId 
						AND RightValue >= @TargetLeft
 
				SET   	@DiffTarget = @TargetLeft - @OriginalLeft
			END
		ELSE
			BEGIN
				-- Update + volume left/right >= target
				UPDATE	TreeStructures 
				SET   	LeftValue = LeftValue + @OriginalVolume
				WHERE	TreeId = @TargetTreeId 
						AND LeftValue > @TargetRight
 
				UPDATE	TreeStructures 
				SET   	RightValue = RightValue + @OriginalVolume  
				WHERE	TreeId = @TargetTreeId 
						AND RightValue > @TargetRight
 
				SET   	@DiffTarget = @TargetRight + 1 - @OriginalLeft
			END
 
		/******************************************************/	
 
		/**
		 * For debugging purposes
		 */
 
				--	SELECT	LTRIM(STR(@OriginalVolume)) AS OrignalVolume, 
				--			LTRIM(STR(@OriginalLeft)) AS OriginalLeft,
				--			LTRIM(STR(@OriginalRight)) AS OriginalRight,
				--			LTRIM(STR(@TargetLeft)) AS TargetLeft,
				--			LTRIM(STR(@TargetRight)) AS TargetRight,
				--			LTRIM(STR(@LastPlace)) AS LastPlace,
				--			LTRIM(STR(@Index)) AS [Index],
				--			LTRIM(STR(@DiffTarget)) AS DiffTarget,
				--			LTRIM(STR(@TargetTreeStructureId)) AS TargetTreeStructureId
 
		/******************************************************/
 
		/**
		 * Updating the left and right values of the volume that needs to be transported.
		 * They become non negative again.
		 */
 
			UPDATE	TreeStructures 
			SET   	LeftValue = -LeftValue + @DiffTarget, 
					RightValue = -RightValue + @DiffTarget,
					TreeId = @TargetTreeId
			WHERE	LeftValue <= -@OriginalLeft 
					AND RightValue >= -@OriginalRight 
					AND TreeId = @OriginalTreeId
 
		/******************************************************/
 
		/**
		 * Closing the gap in the original tree (can be the same as target tree)
		 */
 
			UPDATE	TreeStructures 
			SET   	LeftValue = LeftValue - @OriginalVolume
			WHERE	TreeId = @OriginalTreeId
					AND LeftValue > @OriginalRight
 
			UPDATE	TreeStructures 
			SET   	RightValue = RightValue - @OriginalVolume
			WHERE	TreeId = @OriginalTreeId
					AND RightValue > @OriginalRight
 
		/******************************************************/
 
		COMMIT TRANSACTION @TranName
 
		/******************************************************/
 
	END TRY
 
	BEGIN CATCH
 
		/* 	
		 *	If an error occured we Roll Back the transaction 
		 *	and raise an error that VS can catch 
		*/
 
		ROLLBACK TRANSACTION @TranName
		RAISERROR 50001 'Error while trying to move. Rollback has been executed.'
 
		/******************************************************/
	END CATCH
END

/****** 
 * Object:  UserDefinedFunction [dbo].[fn_GetChildTreeStructure]    
 * Script Date: 08/16/2008 14:03:45 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Edumatic3_GetChildTreeStructure] 
(
	@ParentTreeStructureId INT,
	@INDEX INT
)
RETURNS INT
AS
BEGIN
	DECLARE @TreeStructureId INT
	DECLARE @RightValue INT
	DECLARE @RootLeftValue INT
	DECLARE @RootRightValue INT
	DECLARE @TreeId INT
 
	SELECT	@RootLeftValue = LeftValue, 
			@RootRightValue = RightValue, 
			@TreeId = TreeId
	FROM 	TreeStructures
	WHERE	TreeStructureId = @ParentTreeStructureId
 
	SELECT	@RightValue = RightValue, 
			@TreeStructureId = TreeStructureId 
	FROM 	TreeStructures 
	WHERE	TreeId = @TreeId AND 
			LeftValue = @RootLeftValue + 1
 
	WHILE (@RightValue < (@RootRightValue - 1) AND @INDEX > 0) 
		BEGIN
			SELECT	@RightValue = RightValue, 
					@TreeStructureId = TreeStructureId 
			FROM 	TreeStructures 
			WHERE	TreeId = @TreeId AND 
					LeftValue = @RightValue + 1	
 
			SET   	@INDEX = @INDEX -1	
		END
 
	IF @INDEX <> 0
		-- Either negative index was passed or index was bigger than number of childs
		BEGIN
			SET @TreeStructureId = -1
		END
 
	RETURN @TreeStructureId
 
END

/****** 
    Object:  StoredProcedure [dbo].[sp_InsertTreeNodeAndReturnTreeStructureId]    
    Script Date: 08/16/2008 09:29:07 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertTreeNodeAndReturnTreeStructureId]
	(
		@TreeStructureId INT,    -- Parent Node
		@TreeId INT,                -- TreeId (in case no TreeStructures exist yet for the Tree)
		@INDEX INT                  -- Index where to be inserted (if null, insert at last place)
	)
AS
BEGIN
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	DECLARE @TranName NVARCHAR(MAX)
	SELECT @TranName = 'InsertTreeNodeAndReturnTreeStructureId';
	BEGIN TRANSACTION @TranName
	BEGIN TRY
 
		DECLARE @RightValue INT
		DECLARE @TargetTreeStructureId INT
		IF (@TreeStructureId IS NULL)
		   -- Tree exists but no TreeStructures yet for Tree 
		    BEGIN
		        SET @RightValue = 1
		    END
		ELSE
		    BEGIN
			IF(@TreeId IS NULL)
			    -- With TreeStructureId it's easy to get the TreeId
			    BEGIN
			        SELECT     @TreeId = TreeId 
			        FROM         TreeStructures 
			        WHERE       TreeStructureId = @TreeStructureId
			    END
			IF (@INDEX IS NULL OR @INDEX = -1)
			    BEGIN
			        -- Node will be inserted at last place
			        SELECT	@RightValue = RightValue
			        FROM	        TreeStructures
			        WHERE	TreeStructureId = @TreeStructureId
			    END
			ELSE
			    BEGIN
			        SET @TargetTreeStructureId = 
                                    dbo.[fn_Edumatic3_GetChildTreeStructure](@TreeStructureId, @INDEX)		
			        IF(@TargetTreeStructureId <> -1)
				    BEGIN
				        SELECT  @RightValue = LeftValue
				        FROM	    TreeStructures
				        WHERE   TreeStructureId = dbo.[fn_GetChildTreeStructure](@TreeStructureId, @INDEX)
				    END
					ELSE
					    BEGIN
					        SELECT	@RightValue = RightValue
					        FROM  	TreeStructures
					        WHERE	TreeStructureId = @TreeStructureId
					    END
			    END
 
			-- Move TreeStructure row
			UPDATE  TreeStructures 
			SET       RightValue = RightValue + 2 
			WHERE   RightValue > (@RightValue - 1) 
			AND       TreeId = @TreeId
 
			UPDATE  TreeStructures 
			SET       LeftValue = LeftValue + 2 
			WHERE   LeftValue > (@RightValue - 1) 
			AND       TreeId = @TreeId
		   END
 
	       -- Insert new TreeStructure row
	       INSERT 
	       INTO     TreeStructures (TreeId, LeftValue, RightValue) 
	       VALUES  (@TreeId, @RightValue, @RightValue + 1)
 
	       COMMIT TRANSACTION @TranName
 
	       RETURN @TreeStructureId
	END TRY
 
	BEGIN CATCH
 
		/* If an error occured we Roll Back the transaction and 
                    raise an error that VS can catch */
 
		ROLLBACK TRANSACTION @TranName
		RAISERROR 50001 'Error sp_InsertTreeNodeAndReturnTreeStructureId. 
                                         Rollback has been executed.'
 
		/*************************************************************/	
 
	END CATCH
 
END

/****** 
    Object:  UserDefinedFunction [dbo].[fn_GetChildTreeStructure]    
    Script Date: 08/16/2008 14:03:45 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Edumatic3_GetChildTreeStructure] 
(
	@ParentTreeStructureId INT,
	@INDEX INT
)
RETURNS INT
AS
BEGIN
	DECLARE @TreeStructureId INT
	DECLARE @RightValue INT
	DECLARE @RootLeftValue INT
	DECLARE @RootRightValue INT
	DECLARE @TreeId INT
 
	SELECT	@RootLeftValue = LeftValue, 
			@RootRightValue = RightValue, 
			@TreeId = TreeId
	FROM 	TreeStructures
	WHERE	TreeStructureId = @ParentTreeStructureId
 
	SELECT	@RightValue = RightValue, 
			@TreeStructureId = TreeStructureId 
	FROM 	TreeStructures 
	WHERE	TreeId = @TreeId AND 
			LeftValue = @RootLeftValue + 1
 
	WHILE (@RightValue < (@RootRightValue - 1) AND @INDEX > 0) 
		BEGIN
			SELECT	@RightValue = RightValue, 
					@TreeStructureId = TreeStructureId 
			FROM 	TreeStructures 
			WHERE	TreeId = @TreeId AND 
					LeftValue = @RightValue + 1	
 
			SET   	@INDEX = @INDEX -1	
		END
 
	IF @INDEX <> 0
		-- Either negative index was passed or index was bigger than number of childs
		BEGIN
			SET @TreeStructureId = -1
		END
 
	RETURN @TreeStructureId
 
END

/****** Object:  StoredProcedure [dbo].[sp_Edumatic3_MoveTreeStructure]   
Script Date: 08/14/2008 20:00:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Edumatic3_MoveTreeStructure]
    @TreeStructureId INT,
    @ParentTreeStructureId INT,
    @INDEX INT
AS
BEGIN
    SET NOCOUNT ON;
 
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
    DECLARE @TranName VARCHAR(20);
    SELECT @TranName = 'MoveTreeStructure';
 
    BEGIN TRANSACTION @TranName
 
        BEGIN TRY	
            ...
 
            COMMIT TRANSACTION @TranName
 
        END TRY
 
    BEGIN CATCH
 
        /* If an error occured we Roll Back the transaction and raise an error that VS can catch */
 
        ROLLBACK TRANSACTION @TranName
        RAISERROR 50001 'Error while trying to move. Rollback has been executed.'
 
    END CATCH
END