Skip to content

README.md

Latest commit

 

History

History
52 lines (42 loc) · 4.19 KB

README.md

File metadata and controls

52 lines (42 loc) · 4.19 KB

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

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.
  • Relationships: Represents relationships between topics, segmented by a RelationshipKey.

Note: Neither Topics nor Relationships are subject to tracking versions. Changes to these records 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, 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 based on a @TopicId.
  • MoveTopic: Moves an existing topic based on a @TopicId, @ParentId, and @SiblingId.
  • UpdateTopic: Updates an existing topic based on a @TopicId, an AttributeValues list of @Attributes, and an XML @ExtendedAttributes. Optionally deletes all relationships; these will need to be re-added using UpdateRelationships. Old attributes are persisted as previous versions.
  • 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).

Functions

  • GetTopicID: Retrieves a topic's TopicId based on a corresponding @TopicKey.
  • GetParentID: Retrieves a topic's parent's TopicId based the child's @TopicId.

Views

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

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.