Организация истории изменений в связке SQL Server и Linq 2 SQL

Часто при работе с критичными бизнес данными возникает желание или необходимость сохранять историю любых изменений объекта. Причем как и любая система резервного копирования наша система должна быть простой и надежной, как лом. Созданию такого инструмента я и решил посвятить топик.

1. Постановка задачи

Итак давайте для начала сформулируем, что хотим получить на выходе:

• Система должна быть максимально простой в понимании, реализации и работе
• Должна полностью работать внутри базы данных — в случае взлома приложения злоумышленником и получения доступа к аккаунту приложения от базы данных система должна продолжать работать и фиксировать изменения
• Должна быть прозрачна для linq2sql и внедряться в приложение без модификации кода за пределами определения linq2sql датаконтекста
• Основываться на стандартных механизмах базы данных и не требовать каких-либо внешних приложений или задач
• Снятие копии мгновенное — никаких задержек
• Возможность мгновенного отката к любому состоянию как всей базы данных так и отдельной записи
• (Опционально) возможность отдавать для логирования дополнительную информацию из приложения

2. Простое решение в общем виде

Не буду утомлять вас описанием поисков, опишу лишь результат.

Будем использовать следующий подход: закроем приложению прямой доступ к таблицам, предоставим для просмотра только специально подготовленные View. Для обновления, удаления и вставки данных будем использовать специально обученные триггеры, которые и осуществят сохранение истории изменений в отдельных таблицах, кроме того будем использовать удаление маркированием. Приступим!

3. Простое решение подробно

Осуществить задуманное можно силами одного лишь SQL сервера, не привлекая .NET. Рассмотрим таблицу для хранения комментариев:

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.

Подробнее о полях:
enabled — флаг доступности комментария (помните, мы используем удаление маркированием)
version — поле типа timestamp хранящее версию строки, при наличии такого поля Linq2sql строит более лаконичные запросы при обновлении данных (для того чтобы проверить что перезаписываемая строка не была изменена с момента чтения не нужно передавать в базу все поля, достаточно version).
Остальное и так понятно — поля комментария.

Создадим ей в пару таблицу для хранения истории изменений. Помоему, хранить логи изменений лучше в отдельной таблице, зачем захламлять основную? В дальнейшем можно например «выселить» таблицу с логами в отдельный файл (отдельно от основных mdf и mds) и перевести боевую часть базы например на SSD диск, это даст выигрыш в скорости чтения из базы. По этой же причине я сознательно иду на избыточность информации. Если использовать таблицу вида «измененное поле — старое значение — новое значение» то для того чтобы откатить строчку к какой-то конкретной дате нужно разматывать все изменения по этой строке назад. Ну или выбирать самое первое после даты для каждой ячейки. И то и другое явно сложнее чем просто взять готовую строчку. А то что база вырастет — не так страшно. Если конкретно в вашем случае такая модель не подходит вы все равно сможете применить общую идею топика, скорректировав запросы под свою модель хранения изменений.

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.

Подробнее о полях:
HistoryItemID — идентификатор записи в архиве
HistorySavedDate — дата сохранения в архив
HistorySiteUser — это поле понадобится нам в дальнейшем, в «сложном» решении. Пока не обращайте на него внимания.

Следующий элемент волшебства — 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.

Остался последний рывок — создать три триггера.

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.

Еще два INSERT и DELETE на гугл докс.

4. Промежуточные итоги

Получилось неплохо. Мы, не модифицируя ни строчки кода в C# (а, а в общем то и в любом работающем с SQL Server) приложении, обеспечили надежное сохранение всех изменений в отдельную таблицу, удовлетворяющее всем изложенным в начале статьи требованиям, кроме последнего.

Что с ним не так? Поясню: база данных не знает ничего о том, что происходит в приложении, она видит только приходящие SQL запросы. База не может определить какой пользователь системы сделал этот запрос — для неё все они на одно лицо и ходят под одним аккаунтом — SQL аккаунтом приложения. Но как хотелось бы кроме информации о дате и сути изменений сохранить хотя бы идентификатор пользователя. Я даже, если помните, поле под это создал — HistorySiteUser. С прискорбием признаем, что в данной реализации сделать это у нас не получится никак. Что ж, собираемся с духом и идем читать дальше.

5. Усложняем идею

В прошлом пункте мы логически заключили что одними триггерами нам не обойтись. Новое решение будет таким: отбираем у приложения права UPDATE, INSERT, DELETE нашего view (от таблицы мы его изолировали полностью еще в первой части статьи), и напишем соответствующие хранимые процедуры, которые будем вызывать из C# приложения, передавая им в параметрах всю дополнительную информацию которую мы хотим сохранить в истории изменений. Процедура будет обновлять таблицу, сохранять изменяемые данные и все те дополнительные которые мы хотим ассоциировать с конкретной операцией изменения.

5. Реализуем задуманное

Сначала удаляем созданные в четвертом пункте триггеры и забираем у приложения права на действия UPDATE, INSERT, DELETE над view.

Теперь создаем три хранимых процедуры:

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.

Еще две INSERT и DELETE снова на гугл докс.

Теперь нам придется немного исправить наше C# приложение. Идем в dbml файл и перетаскиваем мышкой наши функции из Server Explorer в рабочую область. Правой кнопкой кликаем в рабочей области на нашу таблицу (Comments) и выбираем пункт Configure Behavior. Выбираем там по соответствующее действие, и для него нашу импортированную процедуру.


Тоже самое для удаления и вставки.

Осталась самая малость, как задать параметр функции siteUserId? Очень просто: создадим наследника нашего DataContexta и переопределим функцию в нем. Для этого в свойствах функции выбираем Inheritance modifier: virtual (там же полезно выставить 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.

Определяем все стандартные конструкторы (помним что конструкторы не наследуются) и переопределяем функции обновления, изменения и удаления таким образом чтобы они дозаполняли пустые параметры (siteUserID в моем случае). Я для себя оставил возможность задать его вручную (переопределяется только если siteUserID == null) вы можете выбрать свою логику поведения.

На этом все, вам остается только везде использовать для работы с базой SafetyDatabaseDataContext (полностью совместимый с тем классом который создает мастер linq2sql). У меня для получения датаконтекста есть функция HDataBase.GetDataContext() и я просто исправил её с

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. Выводы

Решение полностью удовлетворяет всем требованиям изложенным в начале статьи. Оно практически не требует модификации приложения и позволяет сохранять историю изменений и данные ассоциированные с изменением на уровне приложения. Причем все операции по ведению истории совершаются в базе данных и все что сможет фальсифицировать потенциальный злоумышленник получивший доступ к приложению это ассоциированные с изменением данные (в данном случае siteUserID), изменить что-то в обход истории не получится.