Add @TopicKey and @ContentType to CreateTopic, UpdateTopic

JeremyCaney · JeremyCaney · commit a74389d00727 · 2020-12-23T14:11:24.000-08:00
In a previous commit, the key attributes of `Key`, `ContentType`, and `ParentID` were migrated from `Attributes` to `Topics`. This updates the `CreateTopic` and `UpdateTopic` stored procedure—as well as its call from `SqlTopicRepository`—to include `@TopicKey` and `@ContentType`. (`@ParentID` was already required for `CreateTopic`, and continues to be handled via `MoveTopic` for updates.)

Technically, the `UpdateTopic` stored procedure only requires the `@TopicKey` or `@ContentType` parameters if they have changed; it's smart enough to fall back to the existing value if they aren't passed. That said, it doesn't hurt anything to hard code them in absence of library support for tracking their state change.

These three attributes continue to be saved in the `Attributes` table for now, and are thus maintained in two locations. That will be removed in subsequent commits.
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/CreateTopic.sql b/OnTopic.Data.Sql.Database/Stored Procedures/CreateTopic.sql
@@ -5,7 +5,9 @@
 --------------------------------------------------------------------------------------------------------------------------------
 
 CREATE PROCEDURE [dbo].[CreateTopic]
-	@ParentID		int		= -1,
+	@Key		VARCHAR(128)		,
+	@ContentType		VARCHAR(128)		,
+	@ParentID		INT		= -1,
 	@Attributes		AttributeValues		READONLY,
 	@ExtendedAttributes 	Xml		= null,
 	@Version		datetime		= null
@@ -53,11 +55,17 @@ IF (@ParentID > -1)
 --------------------------------------------------------------------------------------------------------------------------------
 INSERT INTO	Topics (
 	RangeLeft,
-	RangeRight
+	RangeRight,
+	TopicKey,
+	ContentType,
+	ParentID
 )
 Values (
 	@RangeRight,
-	@RangeRight		+ 1
+	@RangeRight		+ 1,
+	@Key,
+	@ContentType,
+	@ParentID
 )
 
 DECLARE	@TopicID		INT
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/UpdateTopic.sql b/OnTopic.Data.Sql.Database/Stored Procedures/UpdateTopic.sql
@@ -6,6 +6,8 @@
 
 CREATE PROCEDURE [dbo].[UpdateTopic]
 	@TopicID		INT		= -1		,
+	@Key		VARCHAR(128)		= NULL		,
+	@ContentType		VARCHAR(128)		= NULL		,
 	@Attributes		AttributeValues		READONLY		,
 	@ExtendedAttributes	XML		= null		,
 	@Version		DATETIME		= null		,
@@ -18,6 +20,28 @@ AS
 IF	@Version		IS NULL
 SET	@Version		= GetDate()
 
+--------------------------------------------------------------------------------------------------------------------------------
+-- UPDATE KEY ATTRIBUTES
+--------------------------------------------------------------------------------------------------------------------------------
+
+IF @Key IS NOT NULL OR @ContentType IS NOT NULL
+  BEGIN
+    UPDATE	Topics
+    SET	TopicKey		=
+      CASE
+        WHEN	@Key		IS NULL
+        THEN	TopicKey
+        ELSE	@Key
+      END,
+	ContentType		=
+      CASE
+        WHEN	@ContentType		IS NULL
+        THEN	TopicKey
+        ELSE	@ContentType
+      END
+    WHERE	TopicID		= @TopicID
+  END
+
 --------------------------------------------------------------------------------------------------------------------------------
 -- INSERT NEW ATTRIBUTES
 --------------------------------------------------------------------------------------------------------------------------------
diff --git a/OnTopic.Data.Sql/SqlTopicRepository.cs b/OnTopic.Data.Sql/SqlTopicRepository.cs
@@ -423,6 +423,8 @@ SqlDateTime version
       else if (topic.Parent is not null) {
         command.AddParameter("ParentID", topic.Parent.Id);
       }
+      command.AddParameter("Key", topic.Key);
+      command.AddParameter("ContentType", topic.ContentType);
       command.AddParameter("Version", version.Value);
       command.AddParameter("ExtendedAttributes", extendedAttributes);
       command.AddParameter("Attributes", attributeValues);

Original file line number	Diff line number	Diff line change
`@@ -423,6 +423,8 @@ SqlDateTime version`
`423`	`423`	`else if (topic.Parent is not null) {`
`424`	`424`	`command.AddParameter("ParentID", topic.Parent.Id);`
`425`	`425`	`}`
	`426`	`+ command.AddParameter("Key", topic.Key);`
	`427`	`+ command.AddParameter("ContentType", topic.ContentType);`
`426`	`428`	`command.AddParameter("Version", version.Value);`
`427`	`429`	`command.AddParameter("ExtendedAttributes", extendedAttributes);`
`428`	`430`	`command.AddParameter("Attributes", attributeValues);`