1qVwziAT

1/* This script deletes:
2 * 1. Oprhan data
3 * 2. All nodes from the trash
4 * 3. All previous node versions which are not publish or not newest unpublished
5 */
6
7DECLARE @documentNodeObjectType uniqueidentifier = N'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'; 
8DECLARE @trashPath varchar(5) = '%-20%';
9
10PRINT 'orphan data'
11
12/* DELETE NON-EXISTING DOCUMENTS */
13delete from cmsDocument where nodeId not in (select id from umbracoNode)
14;
15/* CLEAN UNUSED CONTENT ROWS */
16delete from cmsContent where nodeId not in (select id from umbracoNode)
17;
18/* CLEAN UNUSED VERSIONS */
19delete from cmsContentVersion where contentid not in (select nodeId from cmsContent)
20;
21
22/* CLEAN UNUSED XML */
23delete from cmsContentXml where nodeid not in (select nodeId from cmsContent)
24;
25/* DELETE ALL NOTIFICATIONS THAT NO LONGER HAVE NODES */
26delete from umbracoUser2NodeNotify where nodeId not in (select id from umbracoNode)
27;
28
29/* DELETE ALL NOTIFICATIONS THAT NO LONGER HAVE USERS */
30delete from umbracoUser2NodeNotify where userId not in (select id from umbracoUser)
31;
32/* DELETE UMBRACO NODE DATA THAT IS FLAGGED AS A DOCUMENT OBJECT TYPE THAT DOESN'T EXIST IN THE CONTENT TABLE ANY LONGER */
33delete from umbracoNode where id not in
34(select nodeId from cmsContent) and nodeObjectType = @documentNodeObjectType
35;
36/* DELETE PERMISSIONS THAT RELATED TO NON-EXISTING USERS */ 
37delete from umbracoUser2NodePermission where userId not in (select id from umbracoUser)
38;
39
40/* DELETE PERMISSIONS THAT RELATED TO NON-EXISTING NODES */
41delete from umbracoUser2NodePermission where nodeId not in (select id from umbracoNode)
42;
43/* 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 */
44DELETE FROM cmsPreviewXml WHERE VersionID NOT IN (SELECT VersionId FROM cmsContentVersion)
45;
46
47PRINT 'recycle bin'
48/* Clear recycle bin*/
49
50-- Create a temporary table for all trashed node ids's to delete
51CREATE TABLE #TrashNodeIds (id int)
52TRUNCATE TABLE #TrashNodeIds
53INSERT INTO #TrashNodeIds
54	SELECT id FROM umbracoNode WHERE PATH LIKE @trashPath AND id > 0 AND nodeObjectType = @documentNodeObjectType 
55
56delete from cmsPreviewXml where versionID in (select versionid from cmsContentVersion where ContentId in (select nodeId from cmsContent where nodeId in (select id from #TrashNodeIds)))
57delete from cmsContentVersion where ContentId in (select nodeid from cmsContent where nodeId in (select id from #TrashNodeIds))
58delete from cmsPropertyData where contentNodeId in (select id from #TrashNodeIds)
59delete from cmsContentXML where nodeId in (select nodeid from cmsContent where nodeId in (select id from #TrashNodeIds))
60delete from cmsDocument where nodeId in (select id from #TrashNodeIds)
61delete from cmsContent where nodeId in (select id from #TrashNodeIds)
62delete from umbracoUser2NodePermission where nodeId in (select id from #TrashNodeIds)
63delete from umbracoRelation where parentId in (select id from #TrashNodeIds)
64delete from umbracoRelation where childId in (select id from #TrashNodeIds)
65delete from umbracoNode where id in (select id from #TrashNodeIds)
66
67DROP TABLE #TrashNodeIds
68
69PRINT 'unused document verions'
70/* clean unused document versions*/
71
72-- Create a temporary table for all versionId's to delete
73CREATE TABLE #Versions (id UniqueIdentifier)
74
75-- Delete all rows if it exists before
76TRUNCATE TABLE #Versions
77
78-- Insert all versionId's where not published and not newest unpublished
79INSERT INTO #Versions
80    SELECT DISTINCT d.versionId 
81    FROM cmsDocument d
82	JOIN umbracoNode n on d.nodeId = n.id
83    WHERE d.published = 0 AND d.newest = 0 AND d.nodeId > 0
84	AND n.nodeObjectType = @documentNodeObjectType
85
86-- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument
87-- from the nodes which are not published and which are newest not published
88DELETE FROM cmsPreviewXml where versionID in (SELECT id FROM #Versions)
89DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
90DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions)
91DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)
92
93DROP TABLE #Versions