Merge branch 'sql-code-analysis-cleanup' into develop

JeremyCaney · JeremyCaney · commit a16ea489e345 · 2020-02-25T16:36:55.000-08:00
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/MoveTopic.sql b/OnTopic.Data.Sql.Database/Stored Procedures/MoveTopic.sql
@@ -220,7 +220,7 @@ BEGIN
   WHERE	RangeLeft
     BETWEEN	@InsertionPoint
       AND	@OriginalRight
-  OR	RangeRight
+  OR	ISNULL(RangeRight, -1)
     BETWEEN	@InsertionPoint
     AND	@OriginalRight
 
@@ -290,7 +290,7 @@ BEGIN
   WHERE	RangeLeft
     BETWEEN	@OriginalLeft
       AND	@InsertionPoint - 1
-  OR	RangeRight
+  OR	ISNULL(RangeRight, 0)
     BETWEEN	@OriginalLeft
       AND	@InsertionPoint - 1
 
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/UpdateRelationships.sql b/OnTopic.Data.Sql.Database/Stored Procedures/UpdateRelationships.sql
@@ -20,29 +20,29 @@ DECLARE	@Existing_TopicIDs	TopicList
 --------------------------------------------------------------------------------------------------------------------------------
 INSERT
 INTO	@Existing_TopicIDs (
-	  TopicId
+	  TopicID
 	)
-SELECT	Target_TopicId
+SELECT	Target_TopicID
 FROM	Relationships
-WHERE	Source_TopicId		= @TopicID
+WHERE	Source_TopicID		= @TopicID
   AND	RelationshipKey		= @RelationshipKey
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- INSERT NOVEL VALUES
 --------------------------------------------------------------------------------------------------------------------------------
 INSERT
 INTO	Relationships (
-	  Source_TopicId,
+	  Source_TopicID,
 	  RelationshipKey,
-	  Target_TopicId
+	  Target_TopicID
 	)
 SELECT	@TopicId,
 	@RelationshipKey,
-	Target.TopicId
+	Target.TopicID
 FROM	@RelatedTopics		Target
 FULL JOIN	@Existing_TopicIDs	Existing
-  ON	Existing.TopicId	= Target.TopicId
-WHERE	Existing.TopicId	is null
+  ON	Existing.TopicID	= Target.TopicID
+WHERE	Existing.TopicID	is null
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- RETURN TOPIC ID
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/ValidateHierarchy.sql b/OnTopic.Data.Sql.Database/Stored Procedures/ValidateHierarchy.sql
@@ -20,18 +20,20 @@ AS
 --------------------------------------------------------------------------------------------------------------------------------
 PRINT	'Detect range overlaps'
 
-SELECT	*
+SELECT	TopicID,
+	RangeLeft,
+	RangeRight
 FROM	Topics		OuterTopics
 WHERE (
   SELECT	COUNT(TopicID)
   FROM	Topics		InnerTopics
   WHERE (	RangeLeft		< OuterTopics.RangeLeft
-    AND	RangeRight		< OuterTopics.RangeRight
-    AND	RangeRight		> OuterTopics.RangeLeft
+    AND	ISNULL(RangeRight, -1)	< ISNULL(OuterTopics.RangeRight, -1)
+    AND	ISNULL(RangeRight, -1)	> OuterTopics.RangeLeft
   )
   OR (	RangeLeft		> OuterTopics.RangeLeft
-    AND	RangeRight		> OuterTopics.RangeRight
-    AND	RangeLeft		< OuterTopics.RangeRight
+    AND	ISNULL(RangeRight, -1)	> ISNULL(OuterTopics.RangeRight, -1)
+    AND	RangeLeft		< ISNULL(OuterTopics.RangeRight, -1)
   )
 ) > 0
 
@@ -54,11 +56,11 @@ WHERE (
   WHERE	TopicID		!= OuterTopics.TopicID
     AND (	RangeLeft
       IN (	  OuterTopics.RangeLeft,
-	  OuterTopics.RangeRight
+	  ISNULL(OuterTopics.RangeRight, -1)
         )
-    OR	RangeRight
+    OR	ISNULL(RangeRight, 0)
       IN (	  OuterTopics.RangeLeft,
-	  OuterTopics.RangeRight
+	  ISNULL(OuterTopics.RangeRight, -1)
       )
     )
 ) > 0
@@ -74,7 +76,7 @@ SELECT	TopicID,
 	RangeLeft,
 	RangeRight
 FROM	Topics
-WHERE	RangeLeft	>= RangeRight
+WHERE	RangeLeft	>= ISNULL(RangeRight, -1)
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- DETECT PARENT ID MISMATCHES
@@ -88,4 +90,4 @@ SELECT	TopicID,
 	AttributeValue
 FROM	Attributes
 WHERE	AttributeKey		= 'ParentID'
-AND	AttributeValue		!= dbo.GetParentID(TopicID)
+AND	AttributeValue		!= CAST(dbo.GetParentID(TopicID) AS VARCHAR(255))
diff --git a/OnTopic.Data.Sql.Database/Types/TopicList.sql b/OnTopic.Data.Sql.Database/Types/TopicList.sql
@@ -7,6 +7,6 @@
 CREATE
 TYPE	[dbo].[TopicList]
 AS TABLE (
-	TopicId		INT	NOT NULL
-  PRIMARY KEY (	TopicId )
+	TopicID		INT	NOT NULL
+  PRIMARY KEY (	TopicID )
 )
diff --git a/OnTopic.Data.Sql/SqlTopicRepository.cs b/OnTopic.Data.Sql/SqlTopicRepository.cs
@@ -342,7 +342,7 @@ private static void SetVersionHistory(SqlDataReader reader, Dictionary<int, Topi
       /*------------------------------------------------------------------------------------------------------------------------
       | Identify attributes
       \-----------------------------------------------------------------------------------------------------------------------*/
-      var sourceTopicId         = Int32.Parse(reader?["TopicId"]?.ToString(), CultureInfo.InvariantCulture);
+      var sourceTopicId         = Int32.Parse(reader?["TopicID"]?.ToString(), CultureInfo.InvariantCulture);
       var dateTime              = reader?.GetDateTime(reader?.GetOrdinal("Version")?? 0)?? DateTime.Now;
 
       /*------------------------------------------------------------------------------------------------------------------------

Original file line number	Diff line number	Diff line change
`@@ -7,6 +7,6 @@`
`7`	`7`	`CREATE`
`8`	`8`	`TYPE [dbo].[TopicList]`
`9`	`9`	`AS TABLE (`
`10`		`- TopicId INT NOT NULL`
`11`		`- PRIMARY KEY ( TopicId )`
	`10`	`+ TopicID INT NOT NULL`
	`11`	`+ PRIMARY KEY ( TopicID )`
`12`	`12`	`)`