Update GetAttributes to return correct data

JeremyCaney · JeremyCaney · commit 05ae3784e7e4 · 2020-09-09T12:19:28.000-07:00
Technically, the `GetAttributes` stored procedure is a vestigal artifact of the original version of OnTopic from over a decade ago, and is not documented or used in any production code. As such, it could be deleted. Indeed, while it's been kept up to date with schema changes, it hasn't accomodated more recent features such as `ExtendedAttributes` or `Version`, which make it entirely useless.

That said, instead of throwing it out, I've updated it to our latest standards as a useful utility function by incorporating both versioning as well as extended attributes. This includes a query to parse the XML of the latest `ExtendedAttributes` record and `UNION` it against the main `Attributes`.

While technically this could be stored in the `Utilities` schema, it has potential production use, and so I'm going to keep it under `dbo`. It's reasonably fast, and useful for pulling just the attributes of any given topic. We can reevaluate this in the next version of OnTopic, if appropriate.

It's worth noting that this does _not_ include `Key`, `ParentID`, or `ContentType`, as these are excluded from the main `Attributes` collection. We may want to reevaluate that in the future as well.
diff --git a/OnTopic.Data.Sql.Database/Stored Procedures/GetAttributes.sql b/OnTopic.Data.Sql.Database/Stored Procedures/GetAttributes.sql
@@ -1,7 +1,7 @@
 ﻿--------------------------------------------------------------------------------------------------------------------------------
 -- GET TOPIC ATTRIBUTES
 --------------------------------------------------------------------------------------------------------------------------------
--- Returns a list of attributes associated with a particular topic.
+-- Returns the most recent value of each attribute associated with a particular topic.
 --------------------------------------------------------------------------------------------------------------------------------
 CREATE PROCEDURE [dbo].[GetAttributes]
 	@TopicID		INT	= -1
@@ -13,10 +13,33 @@ AS
 SET NOCOUNT ON;
 
 --------------------------------------------------------------------------------------------------------------------------------
--- SELECT ATTRIBUTES
+-- SELECT MOST RECENT ATTRIBUTES
 --------------------------------------------------------------------------------------------------------------------------------
 SELECT	AttributeKey,
-	AttributeValue
-FROM	Attributes
+	AttributeValue,
+	0		AS IsExtendedAttribute,
+	Version
+FROM	AttributeIndex
+WHERE	TopicID		= @TopicID
+
+UNION
+
+--------------------------------------------------------------------------------------------------------------------------------
+-- PARSE MOST RECENT EXTENDED ATTRIBUTES
+--------------------------------------------------------------------------------------------------------------------------------
+SELECT	Attributes.Loc.value(
+	  '@key',
+	  'VARCHAR(255)'
+	) AS		AttributeKey,
+	Attributes.Loc.value(
+	  '.[1]',
+	  'VARCHAR(MAX)'
+	) AS		AttributeValue,
+	1		AS IsExtendedAttribute,
+	Version
+FROM	ExtendedAttributeIndex
+CROSS APPLY	AttributesXml.nodes(
+	  '/attributes/attribute'
+	) AS		Attributes(Loc)
 WHERE	TopicID		= @TopicID
 ORDER BY	AttributeKey
