Table – Instances

Purpose

The Instances table stores the latest version of each record maintained by the DAS-RSS database.

It is the focus for all CRUD operations and the ultimate target for all data changes.

Definition

CREATE TABLE [dbo].[Instances](
	[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,
	[CreatedByGUID] [uniqueidentifier] NULL,
	[CreatedOn] [datetime2](7) NULL,
	[LockedBySessionGUID] [uniqueidentifier] NULL,
	[LockExpires] [datetime2](7) NULL,
	[LastUpdated] [datetime2](7) NULL,
	[OldPrimaryKey] [varchar](500) NULL,
	[LastUpdatedByGUID] [uniqueidentifier] NULL,
	[CorrelationGUID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Instances] PRIMARY KEY CLUSTERED 
(
	[InstanceGUID] 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 current status 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 current data associated with 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 CreatedByGUID and CreatedOn track the InstanceGUID of the User that inserted the first version of the record and the timestamp of when the version was created.

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 LastUpdatedByGUID and LastUpdatedOn track the InstanceGUID of the User that made the last change to this record, or any of its children, and the timestamp of when the change was made. This is useful for detecting whether a change has been made in the details of a complex data structure.

The OldPrimaryKey is used during data migration to maintain a connection between the record being inserted and its primary key in the originating system of record. This is used for analytics to verify which records have been migrated and for mapping relationships between imported records.

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.

Some updates to the record may require that other changes to the record, or its children, are locked out. The LockedBySessionGUID tracks whether the record has been locked, and by which user session. The LockExpires sets a default timeout for the lock in the case that an error occurs, and the lock is not properly released.

Additional Indexes

We have found the following indexes on the Instances table to be of use in optimizing general database and user experience performance.

CREATE NONCLUSTERED INDEX [IX_Concepts] ON [dbo].[Instances]
(
	[ConceptGUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_HierarchyConcepts] ON [dbo].[Instances]
(
	[Hierarchy] ASC,
	[ConceptGUID] ASC,
	[InstanceName] ASC
)

CREATE NONCLUSTERED INDEX [IX_InstanceName] ON [dbo].[Instances]
(
	[InstanceName] ASC
)

CREATE NONCLUSTERED INDEX [IX_Instances_InsertedOn] ON [dbo].[Instances]
(
	[InsertedOn] ASC
)

CREATE NONCLUSTERED INDEX [IX_LockedBySessionGUID] ON [dbo].[Instances]
(
	[LockedBySessionGUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_OldPrimaryKey] ON [dbo].[Instances]
(
	[OldPrimaryKey] ASC
)
INCLUDE([ParentGUID],[InstanceGUID],[Hierarchy])

CREATE NONCLUSTERED INDEX [IX_CorrelationGUID] ON [dbo].[Instances]
(
	[CorrelationGUID] ASC
)
INCLUDE([ParentGUID],[InstanceGUID],[Hierarchy])

CREATE NONCLUSTERED INDEX [IX_ParentConcepts] ON [dbo].[Instances]
(
	[ParentGUID] ASC,
	[ConceptGUID] ASC,
	[InstanceName] ASC
)

Trigger – Maintain Lookups

This trigger preforms two critical functions within the trellispark implementation:

  • It maintains the optional look up tables used to optimization record search.
  • It maintains the InstanceHistory by adding the new record versions to the table.

Optionally, this trigger can also be used to track instance changes for export to other systems. This has been useful in the past for moving data changes into analytics and reporting targets in near real-time without coupling the databases.

CREATE TRIGGER [dbo].[MaintainLookups]
ON [dbo].[Instances] FOR INSERT, UPDATE, DELETE
AS
BEGIN

	DECLARE @InstanceGUID AS UNIQUEIDENTIFIER
	DECLARE @DataVersion AS INT
	DECLARE @ConceptGUID AS UNIQUEIDENTIFIER
	DECLARE @ParentGUID AS UNIQUEIDENTIFIER
	DECLARE @Hierarchy AS VARCHAR(800)
	DECLARE @InstanceData AS VARCHAR(MAX)
	DECLARE @CreatedOn AS DATETIME2(7)
	DECLARE @InsertedOn AS DATETIME2(7)
	DECLARE @LastUpdatedByGUID AS UNIQUEIDENTIFIER
	DECLARE @OldPrimaryKey AS VARCHAR(500)

	-- Start by removing any out of date information...
	---------------------------------------------------

	DECLARE DeletedRecords CURSOR FOR 
	SELECT InstanceGUID, ConceptGUID, OldPrimaryKey FROM Deleted

	OPEN DeletedRecords

	FETCH NEXT FROM DeletedRecords INTO @InstanceGUID, @ConceptGUID, @OldPrimaryKey

	WHILE @@FETCH_STATUS = 0
	BEGIN

		EXECUTE dbo.LookupDelete @InstanceGUID, @ConceptGUID
		-- EXECUTE dbo.GI_InstanceChanged @InstanceGUID, @ConceptGUID, 'Instance Deleted', @OldPrimaryKey
		FETCH NEXT FROM DeletedRecords INTO @InstanceGUID, @ConceptGUID, @OldPrimaryKey

	END

	CLOSE DeletedRecords;
	DEALLOCATE DeletedRecords;

	-- Then insert any updated information...
	-----------------------------------------

	DECLARE InsertedRecords CURSOR FOR 
	SELECT InstanceGUID, DataVersion, ConceptGUID, ParentGUID, Hierarchy, CONVERT(VARCHAR(MAX), InstanceData), CreatedOn, InsertedOn, LastUpdatedByGUID, OldPrimaryKey FROM Inserted

	OPEN InsertedRecords

	FETCH NEXT FROM InsertedRecords INTO @InstanceGUID, @DataVersion, @ConceptGUID, @ParentGUID, @Hierarchy, @InstanceData, @CreatedOn, @InsertedOn, @LastUpdatedByGUID, @OldPrimaryKey

	WHILE @@FETCH_STATUS = 0
	BEGIN

		EXECUTE dbo.LookupInsert @InstanceGUID, @ConceptGUID
		--EXECUTE dbo.GI_InstanceChanged @InstanceGUID, @ConceptGUID, 'Instance Inserted', @OldPrimaryKey
		
		-- Cascade changes of the Last Updated field to parent...
		UPDATE Instances SET LastUpdated = @InsertedOn, LastUpdatedByGUID = @LastUpdatedByGUID WHERE InstanceGUID = @ParentGUID

		FETCH NEXT FROM InsertedRecords INTO @InstanceGUID, @DataVersion, @ConceptGUID, @ParentGUID, @Hierarchy, @InstanceData, @CreatedOn, @InsertedOn, @LastUpdatedByGUID, @OldPrimaryKey

	END

	CLOSE InsertedRecords;
	DEALLOCATE InsertedRecords;

	-- Update the version history...
	-- =============================

	-- Start by removing any possible duplicates...
	DELETE FROM InstanceHistory WHERE EXISTS (SELECT * FROM Inserted WITH(NOLOCK, FORCESEEK)
												WHERE InstanceHistory.InstanceGUID = Inserted.InstanceGUID 
												AND InstanceHistory.DataVersion = Inserted.DataVersion)

	-- Now add the new version data...
	INSERT INTO dbo.InstanceHistory(InstanceGUID, DataVersion, InstanceName, InsertedByGUID, InsertedOn,
				 Hierarchy, ParentGUID, ConceptGUID, InstanceStatus, InstanceData, CorrelationGUID)
	SELECT InstanceGUID, DataVersion, InstanceName, InsertedByGUID, InsertedOn,
				 Hierarchy, ParentGUID, ConceptGUID, InstanceStatus, InstanceData, CorrelationGUID
	FROM inserted

END
Updated on November 17, 2022

Was this article helpful?

Related Articles

Need Support?
Can’t find the answer you’re looking for? Don’t worry we’re here to help!
Contact Support

Leave a Comment