Organizing Change History for SQL Server and Linq 2 SQL Bundles

Often when working with critical business data, a desire or need arises to keep a history of any changes to an object. Moreover, like any backup system, our system should be simple and reliable, like scrap. I decided to devote a topic to the creation of such an instrument.

1. Statement of the problem

So let's first formulate what we want to get in the output:

• The system should be as simple as possible in understanding, implementation and operation.
• It should fully work inside the database - in the event of an attacker breaking into the application and gaining access to the application account from the database, the system should continue to work and commit changes
• It must be transparent to linq2sql and be implemented in the application without modifying the code outside the linq2sql definition of the data context
• Based on standard database engines and not require any external applications or tasks
• Instant copying - no delays
• The ability to instantly rollback to any state of the entire database or a single record
• (Optional) the ability to give additional information from the application for logging

2. A simple solution in general

I will not bore you with a description of the searches, I will describe only the result.

We will use the following approach: close the application direct access to the tables, provide for viewing only specially prepared View. To update, delete and insert data we will use specially trained triggers, which will save the history of changes in separate tables, in addition we will use deletion by marking. Let's get started!

3. Simple solution in detail

You can implement your plans by using only one SQL server, without involving .NET. Consider a table for storing comments:

CREATE TABLE [dbo].[comments] (
[commentID] uniqueidentifier NOT NULL ,
[text] varchar(5000) NULL ,
[dt] datetime NULL ,
[userID] uniqueidentifier NULL ,
[topicID] uniqueidentifier NOT NULL ,
[enabled] bit NOT NULL DEFAULT ((1)) ,
[version] timestamp NOT NULL
)

* This source code was highlighted with Source Code Highlighter.

More information about the fields:
enabled - flag for comment availability (remember, we use deletion by marking)
version - field of the timestamp type that stores the version of the line; if there is such a field, Linq2sql builds more concise queries when updating data (in order to verify that the rewritable line has not been changed from the moment of reading it is not necessary to transfer all the fields to the database, version is enough).
The rest is already clear - comment fields.

Let's create a table for her to store the change history. In my opinion, it is better to store change logs in a separate table, why clutter up the main one? In the future, for example, you can “evict” the table with the logs into a separate file (separately from the main mdf and mds) and transfer the warhead of the database to an SSD disk, for example, this will give a gain in the speed of reading from the database. For the same reason, I consciously go for information redundancy. If you use a table of the form "changed field - old value - new value" then in order to roll back the line to a specific date, you need to unwind all the changes on this line back. Well, or choose the very first after the date for each cell. Both that and another are obviously more difficult than just to take a ready-made line. And the fact that the base will grow is not so scary.

CREATE TABLE [dbo].[history_comments] (
[HistoryItemID] uniqueidentifier NOT NULL ,
[HistorySavedDate] datetime NOT NULL ,
[HistorySiteUser] uniqueidentifier NULL ,
[commentID] uniqueidentifier NOT NULL ,
[text] varchar(5000) NULL ,
[dt] datetime NULL ,
[userID] uniqueidentifier NULL ,
[topicID] uniqueidentifier NOT NULL ,
[enabled] bit NOT NULL ,
[version] timestamp NOT NULL
)

* This source code was highlighted with Source Code Highlighter.

More about the fields:
HistoryItemID - identifier of the record in the archive
HistorySavedDate - date of saving to the archive
HistorySiteUser - we will need this field in the future, in the “complex” solution. Do not pay attention to it yet.

The next element of magic is view:

CREATE VIEW [applicationLevel].[comments_view] AS
SELECT
dbo.comments.commentID,
dbo.comments.text,
dbo.comments.dt,
dbo.comments.userID,
dbo.comments.topicID,
dbo.comments.version

FROM
dbo.comments
WHERE
dbo.comments.enabled = 1

* This source code was highlighted with Source Code Highlighter.

The last jerk remained - to create three triggers.

CREATE TRIGGER [applicationLevel].[onCommentUpdate]
ON [applicationLevel].[comments_view]
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON
  UPDATE [dbo].[comments]
  SET
    [comments].[text] = [inserted].[text],
    [comments].[dt] = [inserted].[dt],
    [comments].[userID] = [inserted].[userID],
    [comments].[topicID] = [inserted].[topicID]
  OUTPUT
    NEWID(),
    GETDATE(),
    '00000000-0000-0000-0000-000000000000',
    [inserted].[commentID],
    [inserted].[text],
    [inserted].[dt],
    [inserted].[userID],
    [inserted].[topicID],
    [inserted].[enabled]
  INTO [dbo].[history_comments]
  (
    [HistoryItemID],
    [HistorySavedDate],
    [HistorySiteUser],
    [commentID],
    [text],
    [dt],
    [userID],
    [topicID],
    [enabled]
  )
  FROM
    [inserted]
  WHERE
    [comments].[commentID] = [inserted].[commentID]
    AND [comments].[version] = [inserted].[version]
END

* This source code was highlighted with Source Code Highlighter.

Two more INSERT and DELETE on google docks.

4. Subtotals

It turned out pretty well. Without modifying a single line of code in C # (and, in general, in any application running with SQL Server), we ensured that all changes are safely stored in a separate table that satisfies all the requirements set out at the beginning of the article, except the last.

What is wrong with him? Let me explain: the database does not know anything about what is happening in the application, it only sees incoming SQL queries. The database cannot determine which system user made this request - for it they are all on the same person and go under the same account - SQL application account. But I would like to save at least the user ID in addition to information about the date and essence of the changes. I even, if you remember, created a field for this - HistorySiteUser. We regret that we will not succeed in doing this in this implementation. Well, we are going to spirit and go to read further.

5. We complicate the idea

In the last paragraph, we logically concluded that we cannot do with triggers alone. The new solution will be this: we select the UPDATE, INSERT, DELETE rights of our view from the application (we completely isolated it from the table in the first part of the article), and write the corresponding stored procedures that we will call from the C # application, passing them all the additional information in the parameters which we want to keep in the history of changes. The procedure will update the table, save the mutable data and all the additional data that we want to associate with the specific change operation.

5. We realize our plan

First, delete the triggers created in the fourth paragraph and take away from the application the rights to UPDATE, INSERT, DELETE actions on view.

Now we create three stored procedures:

CREATE PROCEDURE [applicationLevel].[comment_update]
 @commentID AS uniqueidentifier ,
 @version AS timestamp ,
 @text AS varchar(5000) ,
 @dt AS datetime ,
 @userID AS uniqueidentifier ,
 @topicID AS uniqueidentifier ,
 @SiteUserID AS uniqueidentifier = '00000000-0000-0000-0000-000000000000'
AS
BEGIN
UPDATE [dbo].[comments]
 SET
  [comments].[text] = @text,
  [comments].[dt] = @dt,
  [comments].[userID] = @userID,
  [comments].[topicID]= @topicID
 OUTPUT
  NEWID(),
  GETDATE(),
  @SiteUserID,
  @commentID,
  [inserted].[text],
  [inserted].[dt],
  [inserted].[userID],
  [inserted].[topicID],
  [inserted].[enabled]
 INTO [dbo].[history_comments]
 (
  [HistoryItemID],
  [HistorySavedDate],
  [HistorySiteUser],
  [commentID],
  [text],
  [dt],
  [userID],
  [topicID],
  [enabled]
 )
 WHERE
  [comments].[commentID] = @commentID
  AND version = @version
END

* This source code was highlighted with Source Code Highlighter.

Two more INSERT and DELETE again on Google Dox.

Now we have to slightly fix our C # application. We go to the dbml file and drag our functions from Server Explorer to the workspace with the mouse. Right-click in the workspace on our table (Comments) and select Configure Behavior. We select the appropriate action there, and for it our imported procedure.


The same goes for delete and paste.

Only a little remains, how to set the parameter of the siteUserId function? Very simple: create an heir to our DataContexta and redefine the function in it. To do this, select Inheritance modifier: virtual in the function properties (it is also useful to set Access: Protected there)

public class SafetyDatabaseDataContext : DatabaseDataContext
  {
    #region constructors
    public SafetyDatabaseDataContext () : base() { }
    public SafetyDatabaseDataContext (string connection) : base(connection) { }
    public SafetyDatabaseDataContext (System.Data.IDbConnection connection) : base(connection) { }
    public SafetyDatabaseDataContext (string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { }
    public SafetyDatabaseDataContext (System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { }
    #endregion

    protected override int comment_update(Guid? commentID, System.Data.Linq.Binary version, string text, DateTime? dt, Guid? userID, Guid? topicID, Guid? siteUserID)
    {
      return base.comment_update(commentID, version, text, dt, userID, topicID, siteUserID ?? HSession.UserIdOrEmpty);
    }

    protected override int comment_delete(Guid? commentID, System.Data.Linq.Binary version, Guid? siteUserID)
    {
      return base.comment_delete(commentID, version, siteUserID ?? HSession.UserIdOrEmpty);
    }

    protected override int comment_insert(Guid? commentID, string text, DateTime? dt, Guid? userID, Guid? topicID, Guid? siteUserID)
    {
      return base.comment_insert(commentID, text, dt, userID, topicID, siteUserID ?? HSession.UserIdOrEmpty);
    }
  }

* This source code was highlighted with Source Code Highlighter.

We define all standard constructors (remember that constructors are not inherited) and redefine the functions of updating, changing, and deleting so that they supplement empty parameters (siteUserID in my case). I left the opportunity for myself to set it manually (redefined only if siteUserID == null) you can choose your own behavior logic.

That's all, you just have to use it everywhere to work with the SafetyDatabaseDataContext database (fully compatible with the class that the linq2sql wizard creates). I have the HDataBase.GetDataContext () function to get the data context and I just fixed it with

public static DatabaseDataContext GetDataContext()
{
  return new DatabaseDataContext();
}

* This source code was highlighted with Source Code Highlighter.

on the

public static DatabaseDataContext GetDataContext()
{
  return new SafetyDatabaseDataContext();
}

* This source code was highlighted with Source Code Highlighter.

6. Conclusions

The solution fully meets all the requirements set out at the beginning of the article. It practically does not require modification of the application and allows you to save a change history and data associated with the change at the application level. Moreover, all operations for conducting history are performed in the database and all that a potential attacker who has gained access to the application can falsify is the data associated with the change (in this case siteUserID), it will not work to change something bypassing the history.