Checks and fixes invalid path data in umbracoNode table

CheckAndFixPaths.sql

-- NOTES: 
-- The COMMIT TRANSACTION is commented out at the bottom.
-- Running this script as-is will produce a report of problems if any are found.
-- If no problems are found, nothing is reported.
-- To commit the changes, uncomment the COMMIT TRANSACTION and comment out ROLLBACK TRANSACTION.
-- Once committed, re-run the script and no errors should be detected.
-- This is a SQL port of the c# code: https://github.com/umbraco/Umbraco-CMS/pull/7907/files#diff-83e79f7101cd6797ca42ad8ed07f5835R480
-- By default is this execute for MEDIA but you should execute it for CONTENT too, to do that
-- see the SQL statement below that does this WHERE nodeObjectType = @mediaObjectType and change it
-- to WHERE nodeObjectType = @contentObjectType

SET NOCOUNT ON
BEGIN TRANSACTION

DECLARE @report TABLE (nodeId INT, errorType NVARCHAR(150))
DECLARE @nodesToRebuild TABLE (nodeId INT, [level] INT, [path] NVARCHAR(150), parentId INT)
DECLARE @validNodes TABLE (nodeId INT, [level] INT, [path] NVARCHAR(150), parentId INT)
DECLARE @currentParentIds TABLE (nodeId INT)
DECLARE @pathParts TABLE (id INT IDENTITY(0,1) PRIMARY KEY, part varchar(10))
INSERT INTO @currentParentIds (nodeId) VALUES (-1)
INSERT INTO @currentParentIds (nodeId) VALUES (-20)
INSERT INTO @currentParentIds (nodeId) VALUES (-21)
DECLARE @prevParentIds TABLE (nodeId INT)
INSERT INTO @prevParentIds SELECT * FROM @currentParentIds

DECLARE @nodePath VARCHAR(150)
DECLARE @level INT
DECLARE @nodeId INT
DECLARE @parentId INT

DECLARE @invalidNodePath VARCHAR(150)
DECLARE @invalidLevel INT
DECLARE @invalidNodeId INT
DECLARE @invalidParentId INT

DECLARE @lastLevel INT = -1
DECLARE @contentObjectType VARCHAR(150) = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
DECLARE @mediaObjectType VARCHAR(150) = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C'

DECLARE db_cursor CURSOR FOR  
SELECT id, [level], [path], parentId FROM umbracoNode 
	-- CHANGE THIS for different object types
	WHERE nodeObjectType = @mediaObjectType
	ORDER BY [level], parentId, sortOrder

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @nodeId, @level, @nodePath, @parentId
WHILE @@FETCH_STATUS = 0   
BEGIN  

	IF @level <> @lastLevel
	BEGIN
		-- changing levels
		DELETE FROM @prevParentIds
		INSERT INTO @prevParentIds SELECT * FROM @currentParentIds
        DELETE FROM @currentParentIds
        SET @lastLevel = @level
	END
	
	INSERT INTO @currentParentIds (nodeId) VALUES (@nodeId)
   	   
	-- split the path and remove the roots
	DELETE FROM @pathParts
	INSERT @pathParts (part) SELECT [value] FROM STRING_SPLIT (@nodePath, ',') 
	DELETE FROM @pathParts WHERE part = '-1' OR part = '-20' OR part = '-21'
	DECLARE @pathPartsLength INT = (SELECT COUNT(*) FROM @pathParts)
	
	-- assign the last part to a var
	DECLARE @lastPart VARCHAR(10)
	;WITH PathPartsArray AS 
	(SELECT ROW_NUMBER() OVER (ORDER BY id) rowNum, part FROM @pathParts) 
	SELECT @lastPart = part FROM PathPartsArray WHERE rowNum = @pathPartsLength

	-- assign the 2nd last part to a var
	DECLARE @secondLastPart VARCHAR(10)
	;WITH PathPartsArray AS 
	(SELECT ROW_NUMBER() OVER (ORDER BY id) rowNum, part FROM @pathParts) 
	SELECT @secondLastPart = part FROM PathPartsArray WHERE rowNum = (@pathPartsLength - 1)

	-- paths parts without the roots
	DECLARE @trimmedPath  VARCHAR(150) = REPLACE(@nodePath, '-1,', '') 
	SET @trimmedPath = REPLACE(@trimmedPath, '-20,', '') 
	SET @trimmedPath = REPLACE(@trimmedPath, '-21,', '') 
	DECLARE @isAtRoot INT = (SELECT CASE WHEN (@parentId = -1 OR @parentId = -20 OR @parentId = -21) THEN 1 ELSE 0 END)

	DECLARE @hasPrevParent INT = (SELECT COUNT(*) FROM @prevParentIds WHERE nodeId = @parentId)

	-- DEBUG
	-- PRINT CONCAT(@nodeId, ', ', @nodePath, ', ', @trimmedPath, ', ', @lastPart, ', ' , @secondLastPart, ', ', @isAtRoot)

	IF @hasPrevParent = 0
	BEGIN
        -- invalid, this will be because the level is wrong (which prob means path is wrong too)
		PRINT Concat(@nodeId, ' InvalidPathAndLevelByParentId')
		INSERT INTO @report (nodeId, errorType) VALUES (@nodeId, 'InvalidPathAndLevelByParentId')
		INSERT INTO @nodesToRebuild (nodeId, [level], [path], parentId) VALUES (@nodeId, @level, @nodePath, @parentId)
    END
	ELSE IF @pathPartsLength = 0
	BEGIN
		-- invalid path
		PRINT Concat(@nodeId, ' InvalidPathEmpty')
		INSERT INTO @report (nodeId, errorType) VALUES (@nodeId, 'InvalidPathEmpty')
		INSERT INTO @nodesToRebuild (nodeId, [level], [path], parentId) VALUES (@nodeId, @level, @nodePath, @parentId)
	END
	ELSE IF @pathPartsLength <> @level
	BEGIN
		-- invalid, either path or level is wrong
		PRINT Concat(@nodeId, ' InvalidPathLevelMismatch')
		INSERT INTO @report (nodeId, errorType) VALUES (@nodeId, 'InvalidPathLevelMismatch')
		INSERT INTO @nodesToRebuild (nodeId, [level], [path], parentId) VALUES (@nodeId, @level, @nodePath, @parentId)
	END	
	ELSE IF (@lastPart <> CAST(@nodeId as varchar(10)))
	BEGIN
		-- invalid path
		PRINT Concat(@nodeId, ' InvalidPathById')
		INSERT INTO @report (nodeId, errorType) VALUES (@nodeId, 'InvalidPathById')
		INSERT INTO @nodesToRebuild (nodeId, [level], [path], parentId) VALUES (@nodeId, @level, @nodePath, @parentId)
	END	
	ELSE IF (@isAtRoot = 0 AND @secondLastPart <> CAST(@parentId as varchar(10)))
	BEGIN
		-- invalid path
		PRINT Concat(@nodeId, ' InvalidPathByParentId')
		INSERT INTO @report (nodeId, errorType) VALUES (@nodeId, 'InvalidPathByParentId')
		INSERT INTO @nodesToRebuild (nodeId, [level], [path], parentId) VALUES (@nodeId, @level, @nodePath, @parentId)
	END	
	ELSE
	BEGIN
		-- its valid
		INSERT INTO @validNodes (nodeId, [level], [path], parentId) VALUES (@nodeId, @level, @nodePath, @parentId)
	END	

	FETCH NEXT FROM db_cursor INTO @nodeId, @level, @nodePath, @parentId	   
END 

CLOSE db_cursor   
DEALLOCATE db_cursor

DECLARE valid_nodes_cursor CURSOR FOR  
SELECT nodeId, [level], [path], parentId FROM @validNodes	
OPEN valid_nodes_cursor
FETCH NEXT FROM valid_nodes_cursor INTO @nodeId, @level, @nodePath, @parentId
WHILE @@FETCH_STATUS = 0   
BEGIN 
	
	DECLARE invalid_nodes_cursor CURSOR FOR  
	SELECT nodeId, [level], [path], parentId FROM @nodesToRebuild WHERE parentId = @nodeId	
	OPEN invalid_nodes_cursor
	FETCH NEXT FROM invalid_nodes_cursor INTO @invalidNodeId, @invalidLevel, @invalidNodePath, @invalidParentId
	WHILE @@FETCH_STATUS = 0   
	BEGIN 
		
		PRINT Concat('Fixing ', @invalidNodeId, ', old level = ', @invalidLevel, ', new level = ', @level + 1, ', old path = ', @invalidNodePath, ', new path = ', CONCAT(@nodePath, ',', @invalidNodeId))
		UPDATE umbracoNode SET [level] = @level + 1, [path] = CONCAT(@nodePath, ',', @invalidNodeId) WHERE (id = @invalidNodeId)

		FETCH NEXT FROM invalid_nodes_cursor INTO @invalidNodeId, @invalidLevel, @invalidNodePath, @invalidParentId
	END
	CLOSE invalid_nodes_cursor   
	DEALLOCATE invalid_nodes_cursor


	FETCH NEXT FROM valid_nodes_cursor INTO @nodeId, @level, @nodePath, @parentId
END
CLOSE valid_nodes_cursor   
DEALLOCATE valid_nodes_cursor

ROLLBACK TRANSACTION
--COMMIT TRANSACTION