Moved legacy Hierarchy to Utilities schema, renamed AdjacencyList

JeremyCaney · JeremyCaney · commit 17b98b4b3265 · 2020-09-09T11:03:31.000-07:00
While `Hierarchy` is a preexisting table, and thus this is a breaking change, it's not a table that is publicly documented, and it's exclusively used by the `GenerateNestedSet` stored procedure—which is _also_ a preexisting object which was moved to the `Utilities` schema as part of a minor release under the same justification. As with other similar updates (e.g., e565f0e), this helps keep the `dbo` schema focused on items which are publicly documented and expected to be used directly as part of the library.
diff --git a/OnTopic.Data.Sql.Database/OnTopic.Data.Sql.Database.sqlproj b/OnTopic.Data.Sql.Database/OnTopic.Data.Sql.Database.sqlproj
@@ -71,6 +71,7 @@
     <Folder Include="Utilities\" />
     <Folder Include="Utilities\Stored Procedures" />
     <Folder Include="Utilities\Views" />
+    <Folder Include="Utilities\Tables" />
   </ItemGroup>
   <ItemGroup>
     <Build Include="Utilities\Schema.sql" />
@@ -87,10 +88,10 @@
     <Build Include="Functions\GetUniqueKey.sql" />
     <Build Include="Utilities\Views\UniqueKeyIndex.sql" />
     <Build Include="Utilities\Views\LeftRightRange.sql" />
+    <Build Include="Utilities\Tables\AdjacencyList.sql" />
   </ItemGroup>
   <ItemGroup>
     <Build Include="Tables\ExtendedAttributes.sql" />
-    <Build Include="Tables\Hierarchy.sql" />
     <Build Include="Tables\Relationships.sql" />
     <Build Include="Tables\Topics.sql" />
     <Build Include="Tables\Attributes.sql" />
diff --git a/OnTopic.Data.Sql.Database/Utilities/Stored Procedures/GenerateNestedSet.sql b/OnTopic.Data.Sql.Database/Utilities/Stored Procedures/GenerateNestedSet.sql
@@ -15,19 +15,19 @@ SET IDENTITY_INSERT Topics ON
 --------------------------------------------------------------------------------------------------------------------------------
 -- Delete original content
 DELETE
-FROM	Hierarchy
+FROM	AdjacencyList
 
 -- Insert data from Attributes
 INSERT
-INTO	Hierarchy
+INTO	AdjacencyList
 SELECT	TopicID		AS TopicID,
 	CONVERT(Int, AttributeValue)	AS ParentID,
 	GETDATE()		AS DateAdded
 FROM	Attributes
 WHERE	AttributeKey		= 'ParentID'
 
 -- Address root node
-UPDATE	Hierarchy
+UPDATE	AdjacencyList
 SET	Parent_TopicID		= null
 WHERE	ISNULL(Parent_TopicID, -1)	= -1
 
@@ -41,7 +41,7 @@ BEGIN
 
 SET	@max_RangeLeft_RangeRight	= 2 * (
   SELECT	COUNT(*)
-  FROM	Hierarchy
+  FROM	AdjacencyList
 );
 
 INSERT
@@ -55,14 +55,14 @@ SELECT	1,
 	TopicID,
 	1,
 	@max_RangeLeft_RangeRight
-FROM	Hierarchy
+FROM	AdjacencyList
 WHERE	Parent_TopicID		IS NULL;
 
 SET	@RangeLeft_RangeRight	= 2;
 SET	@pointer	= 1;
 
 DELETE
-FROM	Hierarchy
+FROM	AdjacencyList
 WHERE	Parent_TopicID		IS NULL;
 
 -- The topics is now loaded and ready to use
@@ -72,7 +72,7 @@ BEGIN
   IF EXISTS (
     SELECT	*
     FROM	Topics		AS S1
-    JOIN	Hierarchy		AS T1
+    JOIN	AdjacencyList	AS T1
       ON	S1.TopicID		= T1.Parent_TopicID
       AND	S1.Stack_Top		= @pointer
   )
@@ -91,13 +91,13 @@ BEGIN
 	@RangeLeft_RangeRight,
 	NULL
       FROM	Topics		AS S1
-      JOIN	Hierarchy		AS T1
+      JOIN	AdjacencyList	AS T1
         ON	S1.TopicID		= T1.Parent_TopicID
         AND	S1.Stack_Top		= @pointer;
 
       -- remove this row from hierarchy
       DELETE
-      FROM	Hierarchy
+      FROM	AdjacencyList
       WHERE	TopicID		= (
         SELECT	TopicID
         FROM	Topics
diff --git a/OnTopic.Data.Sql.Database/Utilities/Tables/AdjacencyList.sql b/OnTopic.Data.Sql.Database/Utilities/Tables/AdjacencyList.sql
@@ -1,12 +1,12 @@
 ﻿--------------------------------------------------------------------------------------------------------------------------------
--- HIERARCHY (TABLE)
+-- ADJACENCY LIST (TABLE)
 --------------------------------------------------------------------------------------------------------------------------------
 -- Provides temporary storage for representing the topic hierarchy as an adjacency list. This isn't the preferred format for
 -- representing the topic hierarchy, and is not used in production code. Nevertheless, having this available is useful for
 -- processing migrations from other data formats, or rebuilding the nested set hierarchy should it become corrupted.
 --------------------------------------------------------------------------------------------------------------------------------
 CREATE
-TABLE	[dbo].[Hierarchy] (
+TABLE	[Utilities].[AdjacencyList] (
 	  [TopicID]		INT	NOT NULL,
 	  [Parent_TopicID]	INT	NULL,
 	  [DateAdded]		DATETIME	NOT NULL,
