Purpose
Store all versions of all records maintained by the DAS-RSS database. This table is updated when new record versions are added to the Instances table by the MaintainLookups trigger.
Definition
CREATE TABLE [dbo].[InstanceHistory](
[InstanceGUID] [uniqueidentifier] NOT NULL,
[DataVersion] [int] NOT NULL,
[InstanceName] [varchar](300) NOT NULL,
[ConceptGUID] [uniqueidentifier] NOT NULL,
[Hierarchy] [varchar](500) NOT NULL,
[ParentGUID] [uniqueidentifier] NOT NULL,
[InsertedByGUID] [uniqueidentifier] NOT NULL,
[InsertedOn] [datetime2](7) NOT NULL,
[InstanceStatus] [varchar](50) NOT NULL,
[InstanceData] [xml] NOT NULL,
[CorrelationGUID] [uniqueidentifier] NULL,
CONSTRAINT [PK_InstanceHistory] PRIMARY KEY CLUSTERED
(
[InstanceGUID] ASC,
[DataVersion] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]Column Definitions
Each record is uniquely defined by an InstanceGUID and each version of a record by a combination of the InstanceGUID and DataVersion. The Instances table only stores the latest version of each record, previous versions are available in the InstanceHistory table
Each record is assigned a human readable InstanceName which is generated from the record’s contents held in the InstanceData.
Each record is an instance of a specific ConceptGUID. A ConceptGUID is used to identify the Form Definition maintained by the UX Creator that determines how the record will be displayed to a user.
Each record exists within a well-defined Parent-Child Hierarchy that is maintained by the ParentGUID and Hierarchy. The ParentGUID contains the InstanceGUID of this record’s immediate parent. The Hierarchy contains a ‘\’ separated list of the InstanceGUIDs of each of this record’s ancestors up to the Workspace in which it resides.
The status of this version of the record is maintained by the InstanceStatus. Some records will just have a simple default ‘Active’ state. However, many records that model real-world entities use a set of state transitions to model the life-cycle of the objects they represent.
The data associated with this version of the record is stored as XML in the InstanceData. This is an un-typed XML column and can contain any valid XML fragment. This includes the ability to contain any XML element/attribute hierarchy enabling the storage of complex datasets that would not normally be possible in traditional relational databases.
The InsertedByGUID and InsertedOn track the InstanceGUID of the User that inserted this version of the record and the timestamp of when the version was inserted.
The CorrelationGUID is used during bulk updates to the DAS-RSS to group together all records affected by an update. This enables tracking of changes and possible rollback in failure scenarios.
Additional Indexes
The following additional index has been found useful on past implementations.
CREATE NONCLUSTERED INDEX [IX_InstanceHistory_ConceptInsertedOn] ON [dbo].[InstanceHistory]
(
[ConceptGUID] ASC,
[InsertedOn] ASC
)Key Functionality
When creating a Form Definition for a Concept, you can specify a default number of days for which to retain InstanceHistory of records of that concept. You can then add a Scheduled Task to remove any records in InstanceHistory for that Concept older than the specified interval.
If the Form Definition for the Concept allows the current user to see a list of previous versions of the record, then the generic actions menu will contain an option called “Previous Versions”. clicking this option will open a popup modal window showing any available previous versions of the record. Clicking one of these previous versions will display the selected version of the record in read-only mode.
