Merge branch 'improvement/SQL-DATETIME2' into develop

JeremyCaney · JeremyCaney · commit 5d0ea8015de0 · 2021-01-26T13:23:20.000-08:00
Previously, the SQL database objects—including tables, stored procedures, and functions—used `DATETIME` for e.g. `Version` parameters and columns. The `DATETIME` has severe limitations, however, and is only intended for backward compatibility. Instead, it is preferred for databases to use the new(er) `DATETIME2` data type, which supports not only a wider date range, but also higher precision. As part of this, we were able to establish full compatibility with the .NET CLR's `Int32` (`int`) type, whereas previously we needed to truncate its values using `SqlDateTime`.

We may not need the full `DATETIME2(7)` precision established here; certainly, it's rare that a topic is updated twice in as many ticks—though certainly not unheard of, and especially during automated updates or tests. We may opt to reevaluate this precision later.
diff --git a/OnTopic.Data.Sql.Database/Functions/GetAttributes.sql b/OnTopic.Data.Sql.Database/Functions/GetAttributes.sql
@@ -13,7 +13,7 @@ RETURNS	@Attributes		TABLE
 	AttributeKey		NVARCHAR(255)	NOT NULL,
 	AttributeValue		NVARCHAR(MAX)	NOT NULL,
 	IsExtendedAttribute	BIT,
-	Version		DATETIME
+	Version		DATETIME2(7)
 )
 AS
 
diff --git a/OnTopic.Data.Sql.Database/Functions/GetChildTopicIDs.sql b/OnTopic.Data.Sql.Database/Functions/GetChildTopicIDs.sql
@@ -22,7 +22,7 @@ BEGIN
   ------------------------------------------------------------------------------------------------------------------------------
   IF (@TopicID IS NULL)
     BEGIN
-      SET	@TopicID		= ''
+      SET	@TopicID		= -10
     END
 
   ------------------------------------------------------------------------------------------------------------------------------
@@ -32,7 +32,7 @@ BEGIN
   INTO	@Topics
   SELECT	TopicID
   FROM	Topics
-  WHERE	ISNULL(ParentID, '')	= @TopicID
+  WHERE	ISNULL(ParentID, -10)	= @TopicID
 
   ------------------------------------------------------------------------------------------------------------------------------
   -- RETURN
diff --git a/OnTopic.Data.Sql.Database/Scripts/Upgrade from OnTopic 3 to OnTopic 4.sql b/OnTopic.Data.Sql.Database/Scripts/Upgrade from OnTopic 3 to OnTopic 4.sql
@@ -32,7 +32,7 @@ INTO	topics_TopicAttributes
 SELECT	SourceTopicID,
 	'Type',
 	AttributeTypes.AttributeValue,
-	GETDATE()
+	SYSUTCDATETIME()
 FROM (
   SELECT	TopicID		AS SourceTopicID,
 	AttributeKey,
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/CreateTopic.sql b/OnTopic.Data.Sql.Database/Stored Procedures/CreateTopic.sql
@@ -11,14 +11,14 @@ CREATE PROCEDURE [dbo].[CreateTopic]
 	@Attributes		AttributeValues		READONLY,
 	@ExtendedAttributes 	XML		= NULL,
 	@References		TopicReferences		READONLY,
-	@Version		DATETIME		= NULL
+	@Version		DATETIME2(7)		= NULL
 AS
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- SET DEFAULT VERSION DATETIME
 --------------------------------------------------------------------------------------------------------------------------------
 IF	@Version		IS NULL
-SET	@Version		= GETUTCDATE()
+SET	@Version		= SYSUTCDATETIME()
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- DECLARE AND SET VARIABLES
@@ -119,6 +119,7 @@ IF @ReferenceCount > 0
   BEGIN
     EXEC	UpdateReferences	@TopicID,
 			@References,
+			@Version,
 			1
   END
 
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/GetTopicUpdates.sql b/OnTopic.Data.Sql.Database/Stored Procedures/GetTopicUpdates.sql
@@ -5,7 +5,7 @@
 --------------------------------------------------------------------------------------------------------------------------------
 
 CREATE PROCEDURE [dbo].[GetTopicUpdates]
-	@Since		DATETIME
+	@Since		DATETIME2(7)
 AS
 
 --------------------------------------------------------------------------------------------------------------------------------
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/GetTopicVersion.sql b/OnTopic.Data.Sql.Database/Stored Procedures/GetTopicVersion.sql
@@ -6,7 +6,7 @@
 
 CREATE PROCEDURE [dbo].[GetTopicVersion]
 	@TopicID		INT	= -1,
-	@Version		DATETIME	= NULL
+	@Version		DATETIME2(7)	= NULL
 AS
 
 --------------------------------------------------------------------------------------------------------------------------------
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/UpdateAttributes.sql b/OnTopic.Data.Sql.Database/Stored Procedures/UpdateAttributes.sql
@@ -8,7 +8,7 @@
 CREATE PROCEDURE [dbo].[UpdateAttributes]
 	@TopicID		INT,
 	@Attributes		AttributeValues		READONLY		,
-	@Version		DATETIME		= NULL		,
+	@Version		DATETIME2(7)		= NULL		,
 	@DeleteUnmatched	BIT		= 0
 AS
 
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/UpdateExtendedAttributes.sql b/OnTopic.Data.Sql.Database/Stored Procedures/UpdateExtendedAttributes.sql
@@ -7,7 +7,7 @@
 CREATE PROCEDURE [dbo].[UpdateExtendedAttributes]
 	@TopicID		INT,
 	@ExtendedAttributes	XML		= NULL		,
-	@Version		DATETIME		= NULL		,
+	@Version		DATETIME2(7)		= NULL		,
 	@DeleteUnmatched	BIT		= 0
 AS
 
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/UpdateReferences.sql b/OnTopic.Data.Sql.Database/Stored Procedures/UpdateReferences.sql
@@ -7,15 +7,15 @@
 CREATE PROCEDURE [dbo].[UpdateReferences]
 	@TopicID		INT,
 	@ReferencedTopics	TopicReferences		READONLY		,
-	@Version		DATETIME		= NULL		,
+	@Version		DATETIME2(7)		= NULL		,
 	@DeleteUnmatched	BIT		= 0
 AS
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- SET DEFAULT VERSION DATETIME
 --------------------------------------------------------------------------------------------------------------------------------
 IF	@Version		IS NULL
-SET	@Version		= GETUTCDATE()
+SET	@Version		= SYSUTCDATETIME()
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- INSERT NOVEL VALUES
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/UpdateRelationships.sql b/OnTopic.Data.Sql.Database/Stored Procedures/UpdateRelationships.sql
@@ -8,15 +8,15 @@ CREATE PROCEDURE [dbo].[UpdateRelationships]
 	@TopicID		INT,
 	@RelationshipKey	VARCHAR(255),
 	@RelatedTopics		TopicList		READONLY		,
-	@Version		DATETIME		= NULL		,
+	@Version		DATETIME2(7)		= NULL		,
 	@DeleteUnmatched	BIT		= 0
 AS
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- SET DEFAULT VERSION DATETIME
 --------------------------------------------------------------------------------------------------------------------------------
 IF	@Version		IS NULL
-SET	@Version		= GETUTCDATE()
+SET	@Version		= SYSUTCDATETIME()
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- INSERT NOVEL VALUES
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/UpdateTopic.sql b/OnTopic.Data.Sql.Database/Stored Procedures/UpdateTopic.sql
@@ -10,15 +10,15 @@ CREATE PROCEDURE [dbo].[UpdateTopic]
 	@ContentType		VARCHAR(128)		= NULL		,
 	@Attributes		AttributeValues		READONLY		,
 	@ExtendedAttributes	XML		= NULL		,
-	@Version		DATETIME		= NULL		,
+	@Version		DATETIME2(7)		= NULL		,
 	@DeleteUnmatched	BIT		= 0
 AS
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- SET DEFAULT VERSION DATETIME
 --------------------------------------------------------------------------------------------------------------------------------
 IF	@Version		IS NULL
-SET	@Version		= GETUTCDATE()
+SET	@Version		= SYSUTCDATETIME()
 
 --------------------------------------------------------------------------------------------------------------------------------
 -- UPDATE KEY ATTRIBUTES
diff --git a/OnTopic.Data.Sql.Database/Tables/Attributes.sql b/OnTopic.Data.Sql.Database/Tables/Attributes.sql
@@ -9,9 +9,9 @@
 CREATE
 TABLE	[dbo].[Attributes] (
 	  [TopicID]		INT	NOT NULL,
-	  [AttributeKey]	VARCHAR (128)	NOT NULL,
-	  [AttributeValue]	NVARCHAR (255)	NOT NULL,
-	  [Version]		DATETIME	NOT NULL	DEFAULT GETUTCDATE()
+	  [AttributeKey]	VARCHAR(128)	NOT NULL,
+	  [AttributeValue]	NVARCHAR(255)	NOT NULL,
+	  [Version]		DATETIME2(7)	NOT NULL	DEFAULT SYSUTCDATETIME()
   CONSTRAINT	  [PK_Attributes]	PRIMARY KEY
   CLUSTERED (	    [TopicID]		ASC,
 	    [AttributeKey]	ASC,
diff --git a/OnTopic.Data.Sql.Database/Tables/ExtendedAttributes.sql b/OnTopic.Data.Sql.Database/Tables/ExtendedAttributes.sql
@@ -9,7 +9,7 @@ CREATE
 TABLE	[dbo].[ExtendedAttributes] (
 	  [TopicID]		INT	NOT NULL,
 	  [AttributesXml]	XML	NOT NULL,
-	  [Version]		DATETIME	NOT NULL	DEFAULT GETUTCDATE(),
+	  [Version]		DATETIME2(7)	NOT NULL	DEFAULT SYSUTCDATETIME(),
   CONSTRAINT	  [PK_ExtendedAttributes]		PRIMARY KEY	CLUSTERED (
 	    [TopicID]		ASC,
 	    [Version]		DESC
diff --git a/OnTopic.Data.Sql.Database/Tables/Relationships.sql b/OnTopic.Data.Sql.Database/Tables/Relationships.sql
@@ -9,7 +9,7 @@ TABLE	[dbo].[Relationships] (
 	  [Source_TopicID]	INT	NOT NULL,
 	  [RelationshipKey]	VARCHAR(255)	NOT NULL,
 	  [IsDeleted]		BIT	NOT NULL	DEFAULT 0,
-	  [Version]		DATETIME	NOT NULL	DEFAULT GETUTCDATE()
+	  [Version]		DATETIME2(7)	NOT NULL	DEFAULT SYSUTCDATETIME()
   CONSTRAINT	  [PK_Relationships]	PRIMARY KEY
   CLUSTERED (	    [Source_TopicID]	ASC,
 	    [RelationshipKey]	ASC,
diff --git a/OnTopic.Data.Sql.Database/Tables/TopicReferences.sql b/OnTopic.Data.Sql.Database/Tables/TopicReferences.sql
@@ -8,7 +8,7 @@ TABLE	[dbo].[TopicReferences] (
 	  [Source_TopicID]	INT	NOT NULL,
 	  [ReferenceKey]	VARCHAR(128)	NOT NULL,
 	  [Target_TopicID]	INT	NULL,
-	  [Version]		DATETIME	NOT NULL	DEFAULT GETUTCDATE()
+	  [Version]		DATETIME2(7)	NOT NULL	DEFAULT SYSUTCDATETIME()
   CONSTRAINT	  [PK_TopicReferences]	PRIMARY KEY
   CLUSTERED (	    [Source_TopicID]	ASC,
 	    [ReferenceKey]	ASC,
diff --git a/OnTopic.Data.Sql.Database/Tables/Topics.sql b/OnTopic.Data.Sql.Database/Tables/Topics.sql
@@ -12,7 +12,7 @@ TABLE	[dbo].[Topics] (
 	  [TopicKey]		VARCHAR(128)	NOT NULL,
 	  [ContentType]		VARCHAR(128)	NOT NULL,
 	  [ParentID]		INT	NULL,
-	  [LastModified]	DATETIME	NOT NULL	DEFAULT GETUTCDATE()
+	  [LastModified]	DATETIME2(7)	NOT NULL	DEFAULT SYSUTCDATETIME()
   CONSTRAINT	  [PK_Topics]		PRIMARY KEY
   CLUSTERED (     [TopicID]		ASC
   ),
diff --git a/OnTopic.Data.Sql.Database/Utilities/Stored Procedures/ConsolidateVersions.sql b/OnTopic.Data.Sql.Database/Utilities/Stored Procedures/ConsolidateVersions.sql
@@ -8,8 +8,8 @@
 --------------------------------------------------------------------------------------------------------------------------------
 
 CREATE PROCEDURE [Utilities].[ConsolidateVersions]
-	@StartDate		DATETIME	= NULL,
-	@EndDate		DATETIME	= NULL
+	@StartDate		DATETIME2(7)	= NULL,
+	@EndDate		DATETIME2(7)	= NULL
 AS
 
 --------------------------------------------------------------------------------------------------------------------------------
@@ -37,7 +37,7 @@ ELSE
 --------------------------------------------------------------------------------------------------------------------------------
 IF (@StartDate IS NULL)
   BEGIN
-    SET @StartDate = CONVERT(DATETIME, '2000-01-01')
+    SET @StartDate = CONVERT(DATETIME2(7), '2000-01-01')
   END
 
 --------------------------------------------------------------------------------------------------------------------------------
diff --git a/OnTopic.Data.Sql/SqlCommandExtensions.cs b/OnTopic.Data.Sql/SqlCommandExtensions.cs
@@ -79,7 +79,7 @@ internal static void AddParameter(this SqlCommand command, string sqlParameter,
     /// <param name="sqlParameter">The SQL parameter.</param>
     /// <param name="fieldValue">The SQL field value.</param>
     internal static void AddParameter(this SqlCommand command, string sqlParameter, DateTime fieldValue)
-      => AddParameter(command, sqlParameter, fieldValue, SqlDbType.DateTime);
+      => AddParameter(command, sqlParameter, fieldValue, SqlDbType.DateTime2);
 
     /// <summary>
     ///   Wrapper function that adds a SQL parameter to a command object.
@@ -152,6 +152,7 @@ private static void AddParameter(
         parameter.Value         = sqlDbType switch {
           SqlDbType.Bit         => (bool)fieldValue,
           SqlDbType.DateTime    => (DateTime)fieldValue,
+          SqlDbType.DateTime2   => (DateTime)fieldValue,
           SqlDbType.Int         => (int)fieldValue,
           SqlDbType.Xml         => (string)fieldValue,
           SqlDbType.Structured  => (DataTable)fieldValue,
diff --git a/OnTopic.Data.Sql/SqlTopicRepository.cs b/OnTopic.Data.Sql/SqlTopicRepository.cs
@@ -341,7 +341,7 @@ public override void Save([NotNull]Topic topic, bool isRecursive = false) {
       /*------------------------------------------------------------------------------------------------------------------------
       | Establish dependencies
       \-----------------------------------------------------------------------------------------------------------------------*/
-      var version               = new SqlDateTime(DateTime.UtcNow);
+      var version               = DateTime.UtcNow;
       var unresolvedTopics      = new List<Topic>();
 
       using var connection      = new SqlConnection(_connectionString);
@@ -396,7 +396,7 @@ private void Save(
       bool isRecursive,
       SqlConnection connection,
       List<Topic> unresolvedRelationships,
-      SqlDateTime version
+      DateTime version
     ) {
 
       /*------------------------------------------------------------------------------------------------------------------------
@@ -531,7 +531,7 @@ SqlDateTime version
         command.AddParameter("Key", topic.Key);
         command.AddParameter("ContentType", topic.ContentType);
       }
-      command.AddParameter("Version", version.Value);
+      command.AddParameter("Version", version);
       if (areAttributesDirty) {
         command.AddParameter("Attributes", attributeValues);
         command.AddParameter("ExtendedAttributes", extendedAttributes);
@@ -561,11 +561,11 @@ SqlDateTime version
           PersistReferences(topic, version, connection);
         }
 
-        if (!topic.VersionHistory.Contains(version.Value)) {
-          topic.VersionHistory.Insert(0, version.Value);
+        if (!topic.VersionHistory.Contains(version)) {
+          topic.VersionHistory.Insert(0, version);
         }
 
-        topic.Attributes.MarkClean(version.Value);
+        topic.Attributes.MarkClean(version);
 
       }
 
@@ -700,7 +700,7 @@ public override void Delete(Topic topic, bool isRecursive = false) {
     /// </summary>
     /// <param name="topic">The topic object whose relationships should be persisted.</param>
     /// <param name="connection">The SQL connection.</param>
-    private static void PersistRelations(Topic topic, SqlDateTime version, SqlConnection connection) {
+    private static void PersistRelations(Topic topic, DateTime version, SqlConnection connection) {
 
       /*------------------------------------------------------------------------------------------------------------------------
       | Return blank if the topic has no relations.
@@ -734,7 +734,7 @@ private static void PersistRelations(Topic topic, SqlDateTime version, SqlConnec
           command.AddParameter("TopicID", topicId);
           command.AddParameter("RelationshipKey", key);
           command.AddParameter("RelatedTopics", targetIds);
-          command.AddParameter("Version", version.Value);
+          command.AddParameter("Version", version);
           command.AddParameter("DeleteUnmatched", topic.Relationships.IsFullyLoaded);
 
           command.ExecuteNonQuery();
@@ -773,7 +773,7 @@ private static void PersistRelations(Topic topic, SqlDateTime version, SqlConnec
     /// </summary>
     /// <param name="topic">The topic object whose references should be persisted.</param>
     /// <param name="connection">The SQL connection.</param>
-    private static void PersistReferences(Topic topic, SqlDateTime version, SqlConnection connection) {
+    private static void PersistReferences(Topic topic, DateTime version, SqlConnection connection) {
 
       /*------------------------------------------------------------------------------------------------------------------------
       | Persist relations to database
@@ -793,7 +793,7 @@ private static void PersistReferences(Topic topic, SqlDateTime version, SqlConne
         // Add Parameters
         command.AddParameter("TopicID", topicId);
         command.AddParameter("ReferencedTopics", references);
-        command.AddParameter("Version", version.Value);
+        command.AddParameter("Version", version);
         command.AddParameter("DeleteUnmatched", topic.References.IsFullyLoaded);
 
         command.ExecuteNonQuery();

Original file line number	Diff line number	Diff line change
`@@ -13,7 +13,7 @@ RETURNS @Attributes TABLE`
`13`	`13`	`AttributeKey NVARCHAR(255) NOT NULL,`
`14`	`14`	`AttributeValue NVARCHAR(MAX) NOT NULL,`
`15`	`15`	`IsExtendedAttribute BIT,`
`16`		`- Version DATETIME`
	`16`	`+ Version DATETIME2(7)`
`17`	`17`	`)`
`18`	`18`	`AS`
`19`	`19`