OnTopic-Library/OnTopic.Data.Sql.Database/Stored Procedures/GetTopicVersion.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
108
109
110
111
112
113
114
115
116
--------------------------------------------------------------------------------------------------------------------------------
-- GET TOPIC VERSION
--------------------------------------------------------------------------------------------------------------------------------
-- Retrieves data associated with an individual topic version, as a means of either comparing or restoring a previous version.
--------------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[GetTopicVersion]
	@TopicID		int	= -1,
	@Version		datetime	= null
AS

--------------------------------------------------------------------------------------------------------------------------------
-- DECLARE AND DEFINE VARIABLES
--------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------
-- SELECT KEY ATTRIBUTES
--------------------------------------------------------------------------------------------------------------------------------
;WITH	KeyAttributes
AS (
  SELECT	TopicID,
                AttributeKey,
                AttributeValue,
                RowNumber		= ROW_NUMBER() OVER (
                  PARTITION BY		TopicID,
			AttributeKey
                  ORDER BY		Version		DESC
                )
  FROM	Attributes
  WHERE	TopicID		= @TopicID
    AND	Version		<= @Version
    AND	AttributeKey
    IN (	'Key',
	'ParentID',
	'ContentType'
    )
)
SELECT	TopicID,
	ContentType,
	ParentID,
	[Key]		AS 'TopicKey',
	1		AS 'SortOrder'
FROM	KeyAttributes
PIVOT (	MIN(AttributeValue)
  FOR	AttributeKey
  IN (	[ContentType],
	[ParentID],
	[Key]
  )
)	AS Pvt
WHERE	RowNumber		= 1

--------------------------------------------------------------------------------------------------------------------------------
-- SELECT TOPIC ATTRIBUTES
--------------------------------------------------------------------------------------------------------------------------------
;WITH	TopicAttributes
AS (
  SELECT	TopicID,
	AttributeKey,
	AttributeValue,
	RowNumber		= ROW_NUMBER() OVER (
	  PARTITION BY		TopicID,
			AttributeKey
	  ORDER BY		Version		DESC
	)
  FROM	Attributes
  WHERE	TopicID		= @TopicID
    AND	Version		<= @Version
    AND 	AttributeKey
    NOT IN (	'Key',
	'ParentID',
	'ContentType'
    )
)
SELECT	TopicID,
	AttributeKey,
	AttributeValue
FROM	TopicAttributes
WHERE	RowNumber		= 1

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

--------------------------------------------------------------------------------------------------------------------------------
-- SELECT RELATIONSHIPS
--------------------------------------------------------------------------------------------------------------------------------
;SELECT	Source_TopicID,
	RelationshipKey,
	Target_TopicID
FROM	Relationships
WHERE	Source_TopicID		= @TopicID

--------------------------------------------------------------------------------------------------------------------------------
-- SELECT HISTORY
--------------------------------------------------------------------------------------------------------------------------------
SELECT	TopicID,
	Version
FROM	VersionHistoryIndex
WHERE	TopicID		= @TopicID