-
-
Save neilgaietto/f27ca4287337f18324a3170b2df3f779 to your computer and use it in GitHub Desktop.
Umbraco - delete version history for all content
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* This script deletes: | |
* 1. Oprhan data | |
* 2. All nodes from the trash | |
* 3. All previous node versions which are not publish or not newest unpublished | |
*/ | |
DECLARE @documentNodeObjectType uniqueidentifier = N'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'; | |
DECLARE @trashPath varchar(5) = '%-20%'; | |
PRINT 'orphan data' | |
/* DELETE NON-EXISTING DOCUMENTS */ | |
delete from cmsDocument where nodeId not in (select id from umbracoNode) | |
; | |
/* CLEAN UNUSED CONTENT ROWS */ | |
delete from cmsContent where nodeId not in (select id from umbracoNode) | |
; | |
/* CLEAN UNUSED VERSIONS */ | |
delete from cmsContentVersion where contentid not in (select nodeId from cmsContent) | |
; | |
/* CLEAN UNUSED XML */ | |
delete from cmsContentXml where nodeid not in (select nodeId from cmsContent) | |
; | |
/* DELETE ALL NOTIFICATIONS THAT NO LONGER HAVE NODES */ | |
delete from umbracoUser2NodeNotify where nodeId not in (select id from umbracoNode) | |
; | |
/* DELETE ALL NOTIFICATIONS THAT NO LONGER HAVE USERS */ | |
delete from umbracoUser2NodeNotify where userId not in (select id from umbracoUser) | |
; | |
/* DELETE UMBRACO NODE DATA THAT IS FLAGGED AS A DOCUMENT OBJECT TYPE THAT DOESN'T EXIST IN THE CONTENT TABLE ANY LONGER */ | |
delete from umbracoNode where id not in | |
(select nodeId from cmsContent) and nodeObjectType = @documentNodeObjectType | |
; | |
/* DELETE PERMISSIONS THAT RELATED TO NON-EXISTING USERS */ | |
delete from umbracoUser2NodePermission where userId not in (select id from umbracoUser) | |
; | |
/* DELETE PERMISSIONS THAT RELATED TO NON-EXISTING NODES */ | |
delete from umbracoUser2NodePermission where nodeId not in (select id from umbracoNode) | |
; | |
/* Though this should not have to be run because it's a new install, you need to clean the previews if you've been testing before the RC */ | |
DELETE FROM cmsPreviewXml WHERE VersionID NOT IN (SELECT VersionId FROM cmsContentVersion) | |
; | |
PRINT 'recycle bin' | |
/* Clear recycle bin*/ | |
-- Create a temporary table for all trashed node ids's to delete | |
CREATE TABLE #TrashNodeIds (id int) | |
TRUNCATE TABLE #TrashNodeIds | |
INSERT INTO #TrashNodeIds | |
SELECT id FROM umbracoNode WHERE PATH LIKE @trashPath AND id > 0 AND nodeObjectType = @documentNodeObjectType | |
delete from cmsPreviewXml where versionID in (select versionid from cmsContentVersion where ContentId in (select nodeId from cmsContent where nodeId in (select id from #TrashNodeIds))) | |
delete from cmsContentVersion where ContentId in (select nodeid from cmsContent where nodeId in (select id from #TrashNodeIds)) | |
delete from cmsPropertyData where contentNodeId in (select id from #TrashNodeIds) | |
delete from cmsContentXML where nodeId in (select nodeid from cmsContent where nodeId in (select id from #TrashNodeIds)) | |
delete from cmsDocument where nodeId in (select id from #TrashNodeIds) | |
delete from cmsContent where nodeId in (select id from #TrashNodeIds) | |
delete from umbracoUser2NodePermission where nodeId in (select id from #TrashNodeIds) | |
delete from umbracoRelation where parentId in (select id from #TrashNodeIds) | |
delete from umbracoRelation where childId in (select id from #TrashNodeIds) | |
delete from umbracoNode where id in (select id from #TrashNodeIds) | |
DROP TABLE #TrashNodeIds | |
PRINT 'unused document verions' | |
/* clean unused document versions*/ | |
-- Create a temporary table for all versionId's to delete | |
CREATE TABLE #Versions (id UniqueIdentifier) | |
-- Delete all rows if it exists before | |
TRUNCATE TABLE #Versions | |
-- Insert all versionId's where not published and not newest unpublished | |
INSERT INTO #Versions | |
SELECT DISTINCT d.versionId | |
FROM cmsDocument d | |
JOIN umbracoNode n on d.nodeId = n.id | |
WHERE d.published = 0 AND d.newest = 0 AND d.nodeId > 0 | |
AND n.nodeObjectType = @documentNodeObjectType | |
-- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument | |
-- from the nodes which are not published and which are newest not published | |
DELETE FROM cmsPreviewXml where versionID in (SELECT id FROM #Versions) | |
DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions) | |
DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions) | |
DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions) | |
DROP TABLE #Versions |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment