Lieven Cardoen

/****** 
    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