README.md

SQL Schema

The OnTopic.Data.Sql.Database provides a default schema for supporting the SqlTopicRepository.

Note: In addition to the objects below—which are all part of the default [dbo] schema—there is also a [Utilities] schema which provides stored procedures for use by administrators in maintening the database.

Contents

• Tables
• Stored Procedures
  • Querying
  • Updating
• Functions
• Views
• Types

Tables

The following is a summary of the most relevant tables.

• Topics: Represents the core hierarchy of topics, encoded using a nested set model.
• Attributes: Represents key/value pairs of topic attributes, including historical versions.
• ExtendedAttributes: Represents an XML-based representation of non-indexed attributes, which are too long for Attributes.
• TopicReferences: Represents (1:1) references between topics, segmented by a ReferenceKey.
• Relationships: Represents (1:n) relationships between topics, segmented by a RelationshipKey.

Note: The Topics table is not subject to tracking versions. Changes to core topic values, such as TopicKey, ContentType, and ParentID, are permanent.

Stored Procedures

The following is a summary of the most relevant stored procedures.

Querying

• GetTopics: Based on an optional @TopicId or @TopicKey, retrieves a hierarchy of topics, sorted by hierarchy, alongside separate data sets for corresponding records from Attributes, ExtendedAttributes, Relationships, TopicReferences, and version history. Only retrieves the latest version data for each topic.
• GetTopicVersion: Retrieves a single instance of a topic based on a @TopicId and @Version. Not that the @Version must include miliseconds.

Updating

• CreateTopic: Creates a new topic based on a @ParentId, an AttributeValues list of @Attributes, and an XML @ExtendedAttributes. Returns a new @TopicId.
• DeleteTopic: Deletes an existing topic and all descendant based on a @TopicId.
• MoveTopic: Moves an existing topic based on a @TopicId, @ParentId, and an optional @SiblingId.
• UpdateTopic: Updates an existing topic based on a @TopicId, an AttributeValues list of @Attributes, and an XML @ExtendedAttributes. Old attributes are persisted as previous versions.
  • UpdateAttributes: Updates the indexed attributes, optionally removing any whose values aren't matched in the provided @Attributes parameter.
  • UpdateExtendedAttributes: Updates the extended attributes, assuming the @ExtendedAttributes parameter doesn't match the previous value.
• UpdateReferences: Associates a reference with a topic based on a @TopicId and a TopicReferences array of @ReferencKeys and @Target_TopicIds. Optionally deletes unmatched references.
• UpdateRelationships: Associates a relationship with a topic based on a @TopicId, TopicList array of @Target_TopicIds, and a @RelationshipKey (which can be any string label). Optionally deletes unmatched relationships.

Functions

• GetTopicID: Retrieves a topic's TopicId based on a corresponding @UniqueKey (e.g., Root:Configuration).
• GetUniqueKey: Retrieves a topic's UniqueKey based on a corresponding @TopicID.
• GetParentID: Retrieves a topic's parent's TopicID based the child's @TopicID.
• GetAttributes: Given a @TopicID, provides the latest version of each attribute value from both Attributes and ExtendedAttributes, excluding key attributes (i.e., Key, ContentType, and ParentID).
• GetChildTopicIDs: Given a @TopicID, returns a list of TopicIDs that are immediate children.
• GetExtendedAttribute: Retrieves an individual attribute from a topic's latest ExtendedAttributes record.
• FindTopicIDs: Retrieves all TopicIDs under a given @TopicID that match the @AttributeKey and @AttributeValue. Accepts @IsExtendedAttribute and @UsePartialMatch parameters.

Views

The majority of the views provide records corresponding to the latest version for each topic. These include:

• AttributeIndex: Includes TopicId, AttributeKey and nullable AttributeValue.
• ExtendedAttributesIndex: Includes TopicId and AttributeXml.
• RelationshipIndex: Includes the Source_TopicID, RelationshipKey, Target_TopicID, and IsDeleted.
• ReferenceIndex: Includes Source_TopicID, ReferenceKey, and nullable Target_TopicID.
• VersionHistoryIndex: Includes up to the last five Version records for every TopicId.

Types

User-defined table-valued types are used to relay arrays of information to (and between) the stored procedures. These can be mimicked in C# using e.g. a DataTable. These include:

• AttributeValues: Defines a table with an AttributeKey Varchar(128) and AttributeValue Varchar(255) columns.
• TopicList: Defines a table with a single TopicId Int column for passing lists of topics.
• TopicReferences: Defines a table with a ReferenceKey Varchar(128) and a Target_TopicId Int column for passing lists of topic references.