OnTopic-Library/OnTopic.Data.Sql.Database/Utilities/Stored Procedures/ConsolidateVersions.sql at 67296a877a4fdf15dc81c98769454c3b0734de59 · OnTopicCMS/OnTopic-Library · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
--------------------------------------------------------------------------------------------------------------------------------
-- CONSOLIDATE VERSIONS
--------------------------------------------------------------------------------------------------------------------------------
-- Given a start date and an end date, will consolidate all versions within that range into a single, new version. This has the
-- benefit of reducing the number of versions in the database, reducing the database size, and making some database queries
-- faster. If the end date parameter is not specified, it defaults to 2000-01-01, in which case ALL historical data will be
-- collapsed prior to the start date.
--------------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [Utilities].[ConsolidateVersions]
	@StartDate		datetime	= 20000101,
	@EndDate		datetime	= null
AS

--------------------------------------------------------------------------------------------------------------------------------
-- DECLARE AND SET VARIABLES
--------------------------------------------------------------------------------------------------------------------------------
DECLARE	@IsNestedTransaction	BIT;

BEGIN TRY

--------------------------------------------------------------------------------------------------------------------------------
-- BEGIN TRANSACTION
--------------------------------------------------------------------------------------------------------------------------------
IF (@@TRANCOUNT = 0)
  BEGIN
    SET @IsNestedTransaction = 0;
    BEGIN TRANSACTION;
  END
ELSE
  BEGIN
    SET @IsNestedTransaction = 1;
  END

--------------------------------------------------------------------------------------------------------------------------------
-- SELECT TOPIC ATTRIBUTES
--------------------------------------------------------------------------------------------------------------------------------
;WITH	TopicAttributes
AS (
  SELECT	Version,
	RowNumber		= ROW_NUMBER() OVER (
	  PARTITION BY		TopicID,
			AttributeKey
	  ORDER BY		Version		DESC
	)
  FROM	Attributes
  WHERE	Version		> @StartDate
    AND	Version		<= @EndDate
)
UPDATE	TopicAttributes
SET	Version		= @EndDate
WHERE	RowNumber		= 1

--------------------------------------------------------------------------------------------------------------------------------
-- DELETE CONSOLIDATED ATTRIBUTES
--------------------------------------------------------------------------------------------------------------------------------
DELETE
FROM	Attributes
WHERE	Version		> @StartDate
  AND	Version		< @EndDate

--------------------------------------------------------------------------------------------------------------------------------
-- SELECT EXTENDED ATTRIBUTES
--------------------------------------------------------------------------------------------------------------------------------
;WITH	TopicExtendedAttributes
AS (
  SELECT	Version,
	RowNumber		= ROW_NUMBER() OVER (
	  PARTITION BY		TopicID
	  ORDER BY		Version		DESC
	)
  FROM	ExtendedAttributes
  WHERE	Version		> @StartDate
    AND	Version		<= @EndDate
)
UPDATE	TopicExtendedAttributes
SET	Version		= @EndDate
WHERE	RowNumber		= 1

--------------------------------------------------------------------------------------------------------------------------------
-- DELETE CONSOLIDATED ATTRIBUTES
--------------------------------------------------------------------------------------------------------------------------------
DELETE
FROM	ExtendedAttributes
WHERE	Version		> @StartDate
  AND	Version		< @EndDate

--------------------------------------------------------------------------------------------------------------------------------
-- COMMIT TRANSACTION
--------------------------------------------------------------------------------------------------------------------------------
IF (@@TRANCOUNT > 0 AND @IsNestedTransaction = 0)
  BEGIN
    COMMIT
  END
END TRY

--------------------------------------------------------------------------------------------------------------------------------
-- HANDLE ERRORS
--------------------------------------------------------------------------------------------------------------------------------
BEGIN CATCH
  IF (@@TRANCOUNT > 0 AND @IsNestedTransaction = 0)
    BEGIN
      ROLLBACK;
    END;
  THROW
  RETURN;
END CATCH