Часто при работе с критичными бизнес данными возникает желание или необходимость сохранять историю любых изменений объекта. Причем как и любая система резервного копирования наша система должна быть простой и надежной, как лом. Созданию такого инструмента я и решил посвятить топик.
![](https://habrastorage.org/r/w780q1/storage/f09d11a3/831e9d85/e0116773/533bfb94.jpg)
Итак давайте для начала сформулируем, что хотим получить на выходе:
Не буду утомлять вас описанием поисков, опишу лишь результат.
Будем использовать следующий подход: закроем приложению прямой доступ к таблицам, предоставим для просмотра только специально подготовленные View. Для обновления, удаления и вставки данных будем использовать специально обученные триггеры, которые и осуществят сохранение истории изменений в отдельных таблицах, кроме того будем использовать удаление маркированием. Приступим!
Осуществить задуманное можно силами одного лишь SQL сервера, не привлекая .NET. Рассмотрим таблицу для хранения комментариев:
Подробнее о полях:
enabled — флаг доступности комментария (помните, мы используем удаление маркированием)
version — поле типа timestamp хранящее версию строки, при наличии такого поля Linq2sql строит более лаконичные запросы при обновлении данных (для того чтобы проверить что перезаписываемая строка не была изменена с момента чтения не нужно передавать в базу все поля, достаточно version).
Остальное и так понятно — поля комментария.
Создадим ей в пару таблицу для хранения истории изменений. Помоему, хранить логи изменений лучше в отдельной таблице, зачем захламлять основную? В дальнейшем можно например «выселить» таблицу с логами в отдельный файл (отдельно от основных mdf и mds) и перевести боевую часть базы например на SSD диск, это даст выигрыш в скорости чтения из базы. По этой же причине я сознательно иду на избыточность информации. Если использовать таблицу вида «измененное поле — старое значение — новое значение» то для того чтобы откатить строчку к какой-то конкретной дате нужно разматывать все изменения по этой строке назад. Ну или выбирать самое первое после даты для каждой ячейки. И то и другое явно сложнее чем просто взять готовую строчку. А то что база вырастет — не так страшно. Если конкретно в вашем случае такая модель не подходит вы все равно сможете применить общую идею топика, скорректировав запросы под свою модель хранения изменений.
Подробнее о полях:
HistoryItemID — идентификатор записи в архиве
HistorySavedDate — дата сохранения в архив
HistorySiteUser — это поле понадобится нам в дальнейшем, в «сложном» решении. Пока не обращайте на него внимания.
Следующий элемент волшебства — view:
Остался последний рывок — создать три триггера.
Еще два INSERT и DELETE на гугл докс.
Получилось неплохо. Мы, не модифицируя ни строчки кода в C# (а, а в общем то и в любом работающем с SQL Server) приложении, обеспечили надежное сохранение всех изменений в отдельную таблицу, удовлетворяющее всем изложенным в начале статьи требованиям, кроме последнего.
Что с ним не так? Поясню: база данных не знает ничего о том, что происходит в приложении, она видит только приходящие SQL запросы. База не может определить какой пользователь системы сделал этот запрос — для неё все они на одно лицо и ходят под одним аккаунтом — SQL аккаунтом приложения. Но как хотелось бы кроме информации о дате и сути изменений сохранить хотя бы идентификатор пользователя. Я даже, если помните, поле под это создал — HistorySiteUser. С прискорбием признаем, что в данной реализации сделать это у нас не получится никак. Что ж, собираемся с духом и идем читать дальше.
В прошлом пункте мы логически заключили что одними триггерами нам не обойтись. Новое решение будет таким: отбираем у приложения права UPDATE, INSERT, DELETE нашего view (от таблицы мы его изолировали полностью еще в первой части статьи), и напишем соответствующие хранимые процедуры, которые будем вызывать из C# приложения, передавая им в параметрах всю дополнительную информацию которую мы хотим сохранить в истории изменений. Процедура будет обновлять таблицу, сохранять изменяемые данные и все те дополнительные которые мы хотим ассоциировать с конкретной операцией изменения.
Сначала удаляем созданные в четвертом пункте триггеры и забираем у приложения права на действия UPDATE, INSERT, DELETE над view.
Теперь создаем три хранимых процедуры:
Еще две INSERT и DELETE снова на гугл докс.
Теперь нам придется немного исправить наше C# приложение. Идем в dbml файл и перетаскиваем мышкой наши функции из Server Explorer в рабочую область. Правой кнопкой кликаем в рабочей области на нашу таблицу (Comments) и выбираем пункт Configure Behavior. Выбираем там по соответствующее действие, и для него нашу импортированную процедуру.
![](https://habrastorage.org/r/w1560/storage1/6068e70e/086c00ce/d15a11e8/35039c09.png)
Тоже самое для удаления и вставки.
Осталась самая малость, как задать параметр функции siteUserId? Очень просто: создадим наследника нашего DataContexta и переопределим функцию в нем. Для этого в свойствах функции выбираем Inheritance modifier: virtual (там же полезно выставить Access: Protected)
![](https://habrastorage.org/r/w1560/storage1/7cc4fd08/91a8e2f5/7c3837ce/33bfd934.png)
Определяем все стандартные конструкторы (помним что конструкторы не наследуются) и переопределяем функции обновления, изменения и удаления таким образом чтобы они дозаполняли пустые параметры (siteUserID в моем случае). Я для себя оставил возможность задать его вручную (переопределяется только если siteUserID == null) вы можете выбрать свою логику поведения.
На этом все, вам остается только везде использовать для работы с базой SafetyDatabaseDataContext (полностью совместимый с тем классом который создает мастер linq2sql). У меня для получения датаконтекста есть функция HDataBase.GetDataContext() и я просто исправил её с
Решение полностью удовлетворяет всем требованиям изложенным в начале статьи. Оно практически не требует модификации приложения и позволяет сохранять историю изменений и данные ассоциированные с изменением на уровне приложения. Причем все операции по ведению истории совершаются в базе данных и все что сможет фальсифицировать потенциальный злоумышленник получивший доступ к приложению это ассоциированные с изменением данные (в данном случае siteUserID), изменить что-то в обход истории не получится.
![](https://habrastorage.org/storage/f09d11a3/831e9d85/e0116773/533bfb94.jpg)
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. Выбираем там по соответствующее действие, и для него нашу импортированную процедуру.
![](https://habrastorage.org/storage1/6068e70e/086c00ce/d15a11e8/35039c09.png)
Тоже самое для удаления и вставки.
Осталась самая малость, как задать параметр функции siteUserId? Очень просто: создадим наследника нашего DataContexta и переопределим функцию в нем. Для этого в свойствах функции выбираем Inheritance modifier: virtual (там же полезно выставить Access: Protected)
![](https://habrastorage.org/storage1/7cc4fd08/91a8e2f5/7c3837ce/33bfd934.png)
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), изменить что-то в обход истории не получится.