Skip to content

Commit 31144cc

Browse files
JeremyCaneyJeremyCaney
committed
Merge branch 'feature/TopicReferenceDictionary' into develop
Historically, 1:1 topic references were stored as attributes, with the TopicID serialized as a string. There are a number of problems with this. Notably, there's no referential integrity enforced if the target topic changes. Additionally, when the value is set on the `Topic`, there isn't any enforcement that it's tied to an actual `Topic` reference. These issues are resolved by the introduction of the `TopicReferences` table (fade4d0), which is similar to the existing `Relationships` table, except that it models a 1:1 relationship (per key) instead of a 1:n relationship (per key). This is interfaced via an `UpdateReferences` stored procedure (1a62c23) and a `TopicReferences` user-defined, table-valued type (5716723), with a corresponding `TopicReferencesDataType` (5716723) and `TopicReferenceDictionary` (e523e09) in the OnTopic library, all of which is integrated via a new `Topic.References` property (573a800). Now, topic references are stored as strongly typed references via `Topic.References` with full referential integrity enforced via the `TopicReferences` table, and easy maintenance via the `UpdateReferences` stored procedure. As part of this, the migration script has been updated to migrate all suspected topic references (i.e., attributes which end in `Id` and have a value that corresponds to a `TopicID`) from `Attributes` to `TopicReferences` (db35570). This update also renames the backing field for `DerivedTopic` from `TopicID` to `DerivedTopic`, in both `TopicReferences` as well as in `Topic.DerivedTopic`.
2 parents 75dea66 + 2580002 commit 31144cc

26 files changed

Lines changed: 1188 additions & 188 deletions

‎OnTopic.Data.Sql.Database/OnTopic.Data.Sql.Database.sqlproj‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,14 +96,17 @@
9696
<Build Include="Stored Procedures\GetTopics.sql" />
9797
<Build Include="Stored Procedures\GetTopicVersion.sql" />
9898
<Build Include="Stored Procedures\MoveTopic.sql" />
99+
<Build Include="Stored Procedures\UpdateReferences.sql" />
99100
<Build Include="Stored Procedures\UpdateRelationships.sql" />
100101
<Build Include="Stored Procedures\UpdateTopic.sql" />
101102
<Build Include="Tables\Attributes.sql" />
102103
<Build Include="Tables\ExtendedAttributes.sql" />
103104
<Build Include="Tables\Relationships.sql" />
105+
<Build Include="Tables\TopicReferences.sql" />
104106
<Build Include="Tables\Topics.sql" />
105107
<Build Include="Types\AttributeValues.sql" />
106108
<Build Include="Types\TopicList.sql" />
109+
<Build Include="Types\TopicReferences.sql" />
107110
<Build Include="Utilities\Schema.sql" />
108111
<Build Include="Utilities\Stored Procedures\CompressHierarchy.sql" />
109112
<Build Include="Utilities\Stored Procedures\ConsolidateVersions.sql" />

‎OnTopic.Data.Sql.Database/Scripts/Upgrade from OnTopic 4 to OnTopic 5.sql‎

Lines changed: 33 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -78,4 +78,36 @@ WHERE AttributeKey
7878
IN ( 'Key',
7979
'ContentType',
8080
'ParentID'
81-
)
81+
)
82+
83+
--------------------------------------------------------------------------------------------------------------------------------
84+
-- MIGRATE TOPIC REFERENCES
85+
--------------------------------------------------------------------------------------------------------------------------------
86+
-- In OnTopic 5, references to other topics—such as `DerivedTopic`—have been moved from the Attributes table to a new
87+
-- TopicReferences table, where they act more like relationships. This allows referential integrity to be enforced through
88+
-- foreign key constraints, and formalizes the relationship so we don't need to rely on hacks in e.g. the Topic Data Transer
89+
-- service to infer which attributes represent relationships in order to translate their values from `TopicID` to `UniqueKey`.
90+
--------------------------------------------------------------------------------------------------------------------------------
91+
92+
CREATE
93+
TABLE [dbo].[TopicReferences] (
94+
[Source_TopicID] INT NOT NULL,
95+
[ReferenceKey] VARCHAR(128) NOT NULL,
96+
[Target_TopicID] INT NOT NULL
97+
);
98+
99+
INSERT
100+
INTO TopicReferences
101+
SELECT AttributeIndex.TopicID,
102+
SUBSTRING(AttributeKey, 0, LEN(AttributeKey)-1),
103+
AttributeValue
104+
FROM AttributeIndex
105+
JOIN Topics
106+
ON Topics.TopicID = CONVERT(INT, AttributeValue)
107+
WHERE AttributeKey LIKE '%ID'
108+
AND ISNUMERIC(AttributeValue) = 1
109+
AND Topics.TopicID IS NOT NULL
110+
111+
UPDATE TopicReferences
112+
SET ReferenceKey = 'DerivedTopic'
113+
WHERE ReferenceKey = 'Topic'

‎OnTopic.Data.Sql.Database/Stored Procedures/CreateTopic.sql‎

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@ CREATE PROCEDURE [dbo].[CreateTopic]
1010
@ParentID INT = -1,
1111
@Attributes AttributeValues READONLY,
1212
@ExtendedAttributes XML = NULL,
13+
@References TopicReferences READONLY,
1314
@Version DATETIME = NULL
1415
AS
1516

@@ -73,7 +74,7 @@ DECLARE @TopicID INT
7374
SELECT @TopicID = SCOPE_IDENTITY()
7475

7576
--------------------------------------------------------------------------------------------------------------------------------
76-
-- CREATE ATTRIBUTES FROM STRING
77+
-- ADD INDEXED ATTRIBUTES
7778
--------------------------------------------------------------------------------------------------------------------------------
7879
INSERT INTO Attributes (
7980
TopicID ,
@@ -105,6 +106,20 @@ IF @ExtendedAttributes IS NOT NULL
105106
)
106107
END
107108

109+
--------------------------------------------------------------------------------------------------------------------------------
110+
-- ADD REFERENCES
111+
--------------------------------------------------------------------------------------------------------------------------------
112+
DECLARE @ReferenceCount INT
113+
SELECT @ReferenceCount = COUNT(ReferenceKey)
114+
FROM @References
115+
116+
IF @ReferenceCount > 0
117+
BEGIN
118+
EXEC UpdateReferences @TopicID,
119+
@References,
120+
1
121+
END
122+
108123
--------------------------------------------------------------------------------------------------------------------------------
109124
-- RETURN TOPIC ID
110125
--------------------------------------------------------------------------------------------------------------------------------

‎OnTopic.Data.Sql.Database/Stored Procedures/DeleteTopic.sql‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,16 @@ FROM ExtendedAttributes ExtendedAttributes
9595
INNER JOIN @Topics Topics
9696
ON Topics.TopicId = ExtendedAttributes.TopicID
9797

98+
DELETE TopicReferences
99+
FROM TopicReferences TopicReferences
100+
INNER JOIN @Topics Topics
101+
ON Topics.TopicId = TopicReferences.Source_TopicID
102+
103+
DELETE TopicReferences
104+
FROM TopicReferences TopicReferences
105+
INNER JOIN @Topics Topics
106+
ON Topics.TopicId = TopicReferences.Target_TopicID
107+
98108
DELETE Relationships
99109
FROM Relationships Relationships
100110
INNER JOIN @Topics Topics

‎OnTopic.Data.Sql.Database/Stored Procedures/GetTopicVersion.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,6 +102,15 @@ WHERE RowNumber = 1
102102
FROM Relationships
103103
WHERE Source_TopicID = @TopicID
104104

105+
--------------------------------------------------------------------------------------------------------------------------------
106+
-- SELECT REFERENCES
107+
--------------------------------------------------------------------------------------------------------------------------------
108+
SELECT ReferenceKey,
109+
Source_TopicID,
110+
Target_TopicID
111+
FROM TopicReferences TopicReferences
112+
WHERE Source_TopicID = @TopicID
113+
105114
--------------------------------------------------------------------------------------------------------------------------------
106115
-- SELECT HISTORY
107116
--------------------------------------------------------------------------------------------------------------------------------

‎OnTopic.Data.Sql.Database/Stored Procedures/GetTopics.sql‎

Lines changed: 29 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -6,9 +6,9 @@
66
--------------------------------------------------------------------------------------------------------------------------------
77

88
CREATE PROCEDURE [dbo].[GetTopics]
9-
@TopicID int = -1,
10-
@DeepLoad bit = 1,
11-
@UniqueKey nvarchar(255) = null
9+
@TopicID INT = -1,
10+
@DeepLoad BIT = 1,
11+
@UniqueKey NVARCHAR(255) = NULL
1212
AS
1313

1414
--------------------------------------------------------------------------------------------------------------------------------
@@ -88,10 +88,10 @@ ELSE
8888
-- SELECT KEY ATTRIBUTES
8989
--------------------------------------------------------------------------------------------------------------------------------
9090
SELECT Topics.TopicID,
91-
Topics.ContentType,
92-
Topics.ParentID,
93-
Topics.TopicKey,
94-
Storage.SortOrder
91+
ContentType,
92+
ParentID,
93+
TopicKey,
94+
SortOrder
9595
FROM Topics AS Topics
9696
JOIN #Topics AS Storage
9797
ON Storage.TopicID = Topics.TopicID
@@ -101,9 +101,9 @@ ORDER BY SortOrder
101101
-- SELECT TOPIC ATTRIBUTES
102102
--------------------------------------------------------------------------------------------------------------------------------
103103
SELECT Attributes.TopicID,
104-
Attributes.AttributeKey,
105-
Attributes.AttributeValue,
106-
Attributes.Version
104+
AttributeKey,
105+
AttributeValue,
106+
Version
107107
FROM AttributeIndex Attributes
108108
JOIN #Topics AS Storage
109109
ON Storage.TopicID = Attributes.TopicID
@@ -112,27 +112,37 @@ JOIN #Topics AS Storage
112112
-- SELECT EXTENDED ATTRIBUTES
113113
--------------------------------------------------------------------------------------------------------------------------------
114114
SELECT Attributes.TopicID,
115-
Attributes.AttributesXml,
116-
Attributes.Version
115+
AttributesXml,
116+
Version
117117
FROM ExtendedAttributeIndex AS Attributes
118118
JOIN #Topics AS Storage
119119
ON Storage.TopicID = Attributes.TopicID
120120

121121
--------------------------------------------------------------------------------------------------------------------------------
122122
-- SELECT RELATIONSHIPS
123123
--------------------------------------------------------------------------------------------------------------------------------
124-
SELECT Relationships.Source_TopicID,
125-
Relationships.RelationshipKey,
126-
Relationships.Target_TopicID
124+
SELECT Source_TopicID,
125+
RelationshipKey,
126+
Target_TopicID
127127
FROM Relationships Relationships
128128
JOIN #Topics AS Storage
129129
ON Storage.TopicID = Relationships.Source_TopicID
130130

131+
--------------------------------------------------------------------------------------------------------------------------------
132+
-- SELECT REFERENCES
133+
--------------------------------------------------------------------------------------------------------------------------------
134+
SELECT Source_TopicID,
135+
ReferenceKey,
136+
Target_TopicID
137+
FROM TopicReferences TopicReferences
138+
JOIN #Topics AS Storage
139+
ON Storage.TopicID = TopicReferences.Source_TopicID
140+
131141
--------------------------------------------------------------------------------------------------------------------------------
132142
-- SELECT HISTORY
133143
--------------------------------------------------------------------------------------------------------------------------------
134-
SELECT VersionHistory.TopicID,
135-
VersionHistory.Version
136-
FROM VersionHistoryIndex VersionHistory
144+
SELECT History.TopicID,
145+
Version
146+
FROM VersionHistoryIndex History
137147
JOIN #Topics AS Storage
138-
ON Storage.TopicID = VersionHistory.TopicID;
148+
ON Storage.TopicID = History.TopicID;

‎OnTopic.Data.Sql.Database/Stored Procedures/UpdateReferences.sql‎

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
--------------------------------------------------------------------------------------------------------------------------------
2+
-- UPDATE REFERENCES
3+
--------------------------------------------------------------------------------------------------------------------------------
4+
-- Saves the 1:1 mappings for referenced topics.
5+
--------------------------------------------------------------------------------------------------------------------------------
6+
7+
CREATE PROCEDURE [dbo].[UpdateReferences]
8+
@TopicID INT,
9+
@ReferencedTopics TopicReferences READONLY,
10+
@DeleteUnmatched BIT = 0
11+
AS
12+
13+
--------------------------------------------------------------------------------------------------------------------------------
14+
-- INSERT NOVEL VALUES
15+
--------------------------------------------------------------------------------------------------------------------------------
16+
INSERT
17+
INTO TopicReferences (
18+
Source_TopicID,
19+
ReferenceKey,
20+
Target_TopicID
21+
)
22+
SELECT @TopicID,
23+
Target.ReferenceKey,
24+
Target.TopicID
25+
FROM @ReferencedTopics Target
26+
LEFT JOIN TopicReferences Existing
27+
ON Source_TopicID = @TopicID
28+
AND Existing.ReferenceKey = Target.ReferenceKey
29+
WHERE ISNULL(Source_TopicID, '') = ''
30+
AND Target.TopicID > 0
31+
32+
--------------------------------------------------------------------------------------------------------------------------------
33+
-- UPDATE EXISTING VALUES
34+
--------------------------------------------------------------------------------------------------------------------------------
35+
UPDATE Existing
36+
SET Target_TopicID = TopicID
37+
FROM @ReferencedTopics Target
38+
LEFT JOIN TopicReferences Existing
39+
ON Source_TopicID = @TopicID
40+
AND Existing.ReferenceKey = Target.ReferenceKey
41+
WHERE Source_TopicID IS NOT NULL
42+
AND Target.TopicID != Target_TopicID
43+
AND Target.TopicID > 0
44+
45+
--------------------------------------------------------------------------------------------------------------------------------
46+
-- DELETE UNMATCHED VALUES
47+
--------------------------------------------------------------------------------------------------------------------------------
48+
IF @DeleteUnmatched = 1
49+
BEGIN
50+
DELETE Existing
51+
FROM @ReferencedTopics New
52+
RIGHT JOIN TopicReferences Existing
53+
ON Source_TopicID = @TopicID
54+
AND Existing.ReferenceKey = New.ReferenceKey
55+
WHERE Source_TopicID = @TopicID
56+
AND ISNULL(TopicID, '') = ''
57+
END
58+
59+
--------------------------------------------------------------------------------------------------------------------------------
60+
-- RETURN TOPIC ID
61+
--------------------------------------------------------------------------------------------------------------------------------
62+
RETURN @TopicID;

‎OnTopic.Data.Sql.Database/Stored Procedures/UpdateRelationships.sql‎

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -5,10 +5,10 @@
55
--------------------------------------------------------------------------------------------------------------------------------
66

77
CREATE PROCEDURE [dbo].[UpdateRelationships]
8-
@TopicID INT = -1,
9-
@RelationshipKey VARCHAR(255) = 'related',
8+
@TopicID INT,
9+
@RelationshipKey VARCHAR(255),
1010
@RelatedTopics TopicList READONLY,
11-
@DeleteUnmatched BIT = 1
11+
@DeleteUnmatched BIT = 0
1212
AS
1313

1414
--------------------------------------------------------------------------------------------------------------------------------
@@ -27,19 +27,21 @@ FROM @RelatedTopics Target
2727
LEFT JOIN Relationships Existing
2828
ON Target_TopicID = TopicID
2929
AND Source_TopicID = @TopicID
30+
AND RelationshipKey = @RelationshipKey
3031
WHERE Target_TopicID IS NULL
3132

3233
--------------------------------------------------------------------------------------------------------------------------------
3334
-- DELETE UNMATCHED VALUES
3435
--------------------------------------------------------------------------------------------------------------------------------
3536
IF @DeleteUnmatched = 1
3637
BEGIN
37-
DELETE EXISTING
38+
DELETE Existing
3839
FROM @RelatedTopics Relationships
3940
RIGHT JOIN Relationships Existing
4041
ON Target_TopicID = TopicID
4142
WHERE Source_TopicID = @TopicID
4243
AND ISNULL(TopicID, '') = ''
44+
AND RelationshipKey = @RelationshipKey
4345
END
4446

4547
--------------------------------------------------------------------------------------------------------------------------------

0 commit comments

Comments
 (0)