Skip to content

Instantly share code, notes, and snippets.

@Shazwazza
Last active November 16, 2022 11:05
Show Gist options
  • Save Shazwazza/1a04dcd1c1b6f16d3b7f167874770a84 to your computer and use it in GitHub Desktop.
Save Shazwazza/1a04dcd1c1b6f16d3b7f167874770a84 to your computer and use it in GitHub Desktop.
Checks and fixes invalid path data in umbracoNode table
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment