Wiki

Case Status Kiln
Log In

Wiki

 
Procedures»Adding Revision History
  • RSS Feed

Last modified on 8/7/2013 9:13 AM by User.

Tags:

Adding Revision History

The following tables should be created to support revision history

CREATE TABLE UserTransactions (
UserTransactionId int NOT NULL CONSTRAINT pkUserTransactions PRIMARY KEY( UserTransactionId ),
TransactionDateAndTime datetime2 NOT NULL,
UserId int NULL CONSTRAINT fkUserTransactionsToUsers REFERENCES Users
)

CREATE TABLE Revisions (
RevisionId int NOT NULL CONSTRAINT pkRevisions PRIMARY KEY( RevisionId ),
LatestRevisionId int NOT NULL CONSTRAINT fkRevisionsToRevisions REFERENCES Revisions,
UserTransactionId int NOT NULL CONSTRAINT fkRevisionsToUserTransactions REFERENCES UserTransactions,
CONSTRAINT UniqueLatestRevisionIdAndUserTransactionId UNIQUE( LatestRevisionId, UserTransactionId )
)

Revision History Tables

Tables using revision history should have a suffix of "revisions."

Custom queries attempting to get only the most recent version of an entity (which is most queries, except those trying to show historical data) need to include "INNER JOIN revisions r ON r.revision_id = tableAlias.tablePrimaryKey AND r.latest_revision_id = r.revision_id".

To add revision history to an existing table

You must insert one new User Transaction and N new Revisions where N is the number of rows in the table you are enabling revision history on. The RevisionId and the LatestRevisionId of each new row should be identical, and equal to the primary key of the row you are adding the revision for. It will look something like this:

INSERT INTO MainSequence DEFAULT VALUES
DECLARE @userTransactionId int
userTransactionId = @@IDENTITY
INSERT INTO UserTransactions VALUES( userTransactionId, GET_DATE(), NULL )

/*Create revisions for existing practices.*/
DECLARE @practiceId int
DECLARE practiceRow CURSOR FOR SELECT PracticeId FROM Practices
OPEN practiceRow
FETCH NEXT FROM practiceRow INTO @practiceId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Revisions VALUES( @practiceId, @practiceId, @userTransactionId )
FETCH NEXT FROM practiceRow INTO @practiceId
END
CLOSE practiceRow
DEALLOCATE practiceRow

To add a new column to a table that is already under revision history

This section is not yet complete. M+Vision has a sample of what to do (when adding files to applicant revisions). You need to back-fill bogus "old" values for the new piece of data if it does not allow null.