Organization of change history in SQL Server and Linq 2 SQL
Often, when working with critical business data, there is a desire or need to preserve the history of any changes to an object. And like any backup system, our system should be as simple and reliable as scrap. I decided to devote a topic to creating such a tool.
So let's start with what we want to get at the output:
')
I will not bore you with a description of the search, I will describe only the result.
We will use the following approach: we will close the application with direct access to the tables, we will provide for viewing only specially prepared View. For updating, deleting and inserting data we will use specially trained triggers that will save the change history in separate tables, in addition we will use deletion by marking. Let's get started!
It is possible to carry out the conception by means of only one SQL server, without attracting .NET. Consider a table for storing comments:
More about fields:
enabled - comment availability flag (remember, we use mark removal)
version - a timestamp type field storing a version of a line; if such a field is present, Linq2sql builds more concise queries when updating data (to verify that the rewritable string has not been changed since it was read, it is not necessary to transfer all fields to the database, version is enough)
The rest is clear - comment fields.
Create it in a couple table for storing the history of changes. Wash, it is better to store change logs in a separate table, why clutter up the main one? In the future, you can, for example, “evict” a table with logs into a separate file (separately from the main mdf and mds) and transfer the combat part of the database, for example, to an SSD disk, this will give a gain in reading speed from the database. For the same reason, I consciously go to the redundancy of information. If you use a table like "changed field - old value - new value", then in order to roll back the line to a specific date, you need to unwind all changes on this line back. Well, or choose the very first after the date for each cell. Both are clearly more difficult than simply taking a finished line. And the fact that the base will grow is not so bad. If specifically in your case such a model does not fit, you can still apply the general idea of ​​the topic by adjusting the requests for your change storage model.
More about fields:
HistoryItemID - record identifier in the archive
HistorySavedDate - date saved to archive
HistorySiteUser - we will need this field in the future, in a “complex” solution. Do not pay attention to him yet.
The next element of magic is the view:
The last step left is to create three triggers.
Two more INSERT and DELETE on Google Dox.
It turned out well. We, without modifying a single line of code in C # (a, and generally, in any application working with SQL Server), ensured that all changes are safely saved into a separate table that satisfies all the requirements stated at the beginning of the article, except the last one.
What's wrong with him? Let me explain: the database does not know anything about what is happening in the application, it sees only incoming SQL queries. The database cannot determine which user of the system made this request - for it, they are all on the same person and go under the same account - the SQL account of the application. But as it would be desirable, in addition to the information on the date and essence of the changes, at least save the user ID. I even, if you remember, created a field for this - HistorySiteUser. With regret we recognize that in this implementation we cannot do it in any way. Well, we are going with the spirit and go read on.
In the last paragraph, we logically concluded that with triggers alone we can not do. The new solution will be as follows: we select the UPDATE, INSERT, DELETE rights of our view from the application (we isolated it completely 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 change history. The procedure will update the table, save the changeable data and all the additional data that we want to associate with a specific change operation.
First, we delete the triggers created in the fourth paragraph and retrieve the rights from the application for UPDATE, INSERT, DELETE actions on the view.
Now we create three stored procedures:
Two more INSERT and DELETE again on Google Docs.
Now we have to fix our C # application a little. 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 Comments table and select Configure Behavior. We select there by the appropriate action, and for it our imported procedure.
Same for deletion and insertion.
Just a little remains, how to set the parameter of the siteUserId function? Very simple: create a successor to our DataContexta and override the function in it. To do this, select Inheritance modifier: virtual in the properties of the function (in the same place it is useful to set Access: Protected)
We define all standard constructors (remember that constructors are not inherited) and override the update, change and delete functions so that they fill in the empty parameters (siteUserID in my case). I left the possibility for myself to set it manually (redefined only if siteUserID == null) you can choose your own behavior logic.
That's all you have to do is to use everywhere to work with the SafetyDatabaseDataContext database (fully compatible with the class created by the linq2sql master). I have a HDataBase.GetDataContext () function for retrieving datacontex and I just fixed it with
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 the change history and data associated with changes at the application level. Moreover, all history operations are performed in the database and all that a potential attacker who has access to the application can falsify is the data associated with the change (in this case siteUserID), it will not be possible to change anything bypassing the history.
1. Statement of the problem
So let's start with what we want to get at the output:
- The system should be as simple as possible in understanding, implementation and operation.
- Must fully work inside the database - in case of an application being hacked by an attacker and getting access to the application account from the database, the system should continue to work and commit the changes
- Must be transparent to linq2sql and injected into the application without modifying the code outside the definition of the linq2sql datakontext
- Build on standard database engines and do not require any external applications or tasks.
- Instant copying - no delay
- The ability to instantly roll back to any state as the entire database or a separate record
- (Optional) the ability to provide additional information from the application for logging
')
2. A simple solution in general
I will not bore you with a description of the search, I will describe only the result.
We will use the following approach: we will close the application with direct access to the tables, we will provide for viewing only specially prepared View. For updating, deleting and inserting data we will use specially trained triggers that will save the change history in separate tables, in addition we will use deletion by marking. Let's get started!
3. Simple solution detail
It is possible to carry out the conception by means of only one SQL server, without attracting .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 about fields:
enabled - comment availability flag (remember, we use mark removal)
version - a timestamp type field storing a version of a line; if such a field is present, Linq2sql builds more concise queries when updating data (to verify that the rewritable string has not been changed since it was read, it is not necessary to transfer all fields to the database, version is enough)
The rest is clear - comment fields.
Create it in a couple table for storing the history of changes. Wash, it is better to store change logs in a separate table, why clutter up the main one? In the future, you can, for example, “evict” a table with logs into a separate file (separately from the main mdf and mds) and transfer the combat part of the database, for example, to an SSD disk, this will give a gain in reading speed from the database. For the same reason, I consciously go to the redundancy of information. If you use a table like "changed field - old value - new value", then in order to roll back the line to a specific date, you need to unwind all changes on this line back. Well, or choose the very first after the date for each cell. Both are clearly more difficult than simply taking a finished line. And the fact that the base will grow is not so bad. If specifically in your case such a model does not fit, you can still apply the general idea of ​​the topic by adjusting the requests for your change storage model.
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 fields:
HistoryItemID - record identifier in the archive
HistorySavedDate - date saved to archive
HistorySiteUser - we will need this field in the future, in a “complex” solution. Do not pay attention to him yet.
The next element of magic is the 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 step left is 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 Dox.
4. Subtotals
It turned out well. We, without modifying a single line of code in C # (a, and generally, in any application working with SQL Server), ensured that all changes are safely saved into a separate table that satisfies all the requirements stated at the beginning of the article, except the last one.
What's wrong with him? Let me explain: the database does not know anything about what is happening in the application, it sees only incoming SQL queries. The database cannot determine which user of the system made this request - for it, they are all on the same person and go under the same account - the SQL account of the application. But as it would be desirable, in addition to the information on the date and essence of the changes, at least save the user ID. I even, if you remember, created a field for this - HistorySiteUser. With regret we recognize that in this implementation we cannot do it in any way. Well, we are going with the spirit and go read on.
5. Complicate the idea
In the last paragraph, we logically concluded that with triggers alone we can not do. The new solution will be as follows: we select the UPDATE, INSERT, DELETE rights of our view from the application (we isolated it completely 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 change history. The procedure will update the table, save the changeable data and all the additional data that we want to associate with a specific change operation.
5. We realize our plans
First, we delete the triggers created in the fourth paragraph and retrieve the rights from the application for UPDATE, INSERT, DELETE actions on the 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 Docs.
Now we have to fix our C # application a little. 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 Comments table and select Configure Behavior. We select there by the appropriate action, and for it our imported procedure.
Same for deletion and insertion.
Just a little remains, how to set the parameter of the siteUserId function? Very simple: create a successor to our DataContexta and override the function in it. To do this, select Inheritance modifier: virtual in the properties of the function (in the same place it is useful to set Access: Protected)
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 override the update, change and delete functions so that they fill in the empty parameters (siteUserID in my case). I left the possibility for myself to set it manually (redefined only if siteUserID == null) you can choose your own behavior logic.
That's all you have to do is to use everywhere to work with the SafetyDatabaseDataContext database (fully compatible with the class created by the linq2sql master). I have a HDataBase.GetDataContext () function for retrieving datacontex and I just fixed it with
public static DatabaseDataContext GetDataContext()
{
return new DatabaseDataContext();
}
* This source code was highlighted with Source Code Highlighter .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 the change history and data associated with changes at the application level. Moreover, all history operations are performed in the database and all that a potential attacker who has access to the application can falsify is the data associated with the change (in this case siteUserID), it will not be possible to change anything bypassing the history.
Source: https://habr.com/ru/post/121846/
All Articles