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

[serkuzm]

Лично мне больше по душе писать в history-таблицу не полностью строку данных таблицы, а только те поля, которые реально были изменены, остальные поля — null. Если вести историю широкой таблицы, мне кажется это будет более компактным решением, плюс — в запросе видно сразу изменяемые поля.

[Yavanosta]

Это дело вкуса, можно хранить изменения в виде «айди записи — дата — измененное поле — старое значение — новое значение». Вам нужно только переписать под себя триггер\хранимую процедуру и создать свою хистори таблицу.

Основная сложность — вклиниться в linq2sql. Это не так просто, например нельзя создать DataContext на основе своего наследника SqlConnection или своей реализации IDbConnection — получите рантайм эррор. Это первый путь которым я хотел пойти.

[serkuzm]

Я извиняюсь, но разве DatabaseDataContext не объявлен как partial (in FW 4.0)?
Если создать dbml-файл, перетянуть туда таблицу и три процедуры, но НЕ назначать процедуры на CRUD-операции для сущности. В таком случае будут созданы три partial метода Insertcomment(comment instance), Updatecomment(comment instance), Deletecomment(comment instance) в классе контекста.
И где-то рядом определить эти partial методы уже с вызовом функции-хранимой процедуры с расширенными параметрами. Тогда не нужно будет определять стандартные конструкторы.
В основном я использую EntityFramework, возможно я не понял идеи.

[Yavanosta]

Ну вы правы в общем-то, возможно надо рассмотреть ваш вариант :-)

[osypchuk]

Я перекрываю SubmitChanges, получаю список объектов через GetChangeSet() и логгирую изменения с помощью reflection, заранее отметив атрибутами поля, которые мне нужны.

Для сохранения раньше вызывал SP, сейчас делаю внешний XML файл.

У меня нужно было отслеживать порядка 30 таблиц и идти путем, который описали вы, было бы довольно накладно.

[Yavanosta]

У вашего решения есть свои плюсы, но мое отличается тем что логика полностью работает в БД, данные тем самым защищены от взлома или сбоя на уровне приложения.

[osypchuk]

Я понимаю и уже записал себе в favorites.
Осталось только придумать, как масштабировать ваше решение на N таблиц

[Yavanosta]

У меня поток данных не очень большой. Я бы начал с того что описал в статье, выселил хистори таблицы в отдельный mdf\mds и на отдельный диск. Если у вас вставок не много то вы не сильно проиграете в скорости. Далее можно исключить их ежедневного\ежечасного инкрементального бэкапа и включить например только в еженедельный фулл бэкап. Тоже можно немного выйграть.

На хистори таблицах можно снять все индексы кроме PK по HistoryItemID и индекса по id сущности (commentID в данном случае). Это еще чуть-чуть ускорит вставку. В данном примере оригинальная таблица comments явно будет иметь индекс по topicID.

Прощу прощения что долго, пишу раз в 5 минут :-)

[AigizK]

А можете показать свой класс, где содержится данный код?

public static DatabaseDataContext GetDataContext()

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

[Yavanosta]

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

Примерно так и выглядит. Дата контекст использую так:

[Yavanosta]

раньше отправилось

using(DatabaseDataContext dc = HDataBase.GetDataContext())
{
	//работа с датаконтекстом
}

[ComodoHacker]

1. Я не совсем понял такой момент. В триггере есть условие
WHERE
[comments].[commentID] = [inserted].[commentID]
AND [comments].[version] = [inserted].[version]
Если данную строку кто-то изменил раньше нас, то update не пройдет, но кто это заметит? Получается lost update или я ошибаюсь?

2. Ваше решение подходит, если история изменений сохраняется только с целью аудита. Но есть приложения, где требуется увидеть состояние системы на определенный момент времени (делать запросы, в том числе из нескольких таблиц), оно уже не подходит. Это недостаток.

3. Опыт промышленной эксплуатации есть?

[Smi1e]

1. А кто ее изменит и откуда? Если все изменения идут из приложения и проходят через этот триггер. Если только кто-то вручную в БД залезет, но это уже другой вопрос.

2. Это не недостаток, а реализация для конкретных целей — аудит. Откатываться к предыдущей версии придется, конечно же, вручную.

3. У меня есть. Используется похожий принцип — для поставленных целей вполне подходит. Но сверхвысокой важности данных нет, так что все нюансы и возможные недочеты не изучал.

[ComodoHacker]

> А кто ее изменит и откуда?

Другой пользователь из другой сессии того же приложения. Ведь, как вы сами пишете, поле version введено именно для этого, «для того чтобы проверить что перезаписываемая строка не была изменена с момента чтения». Так вот поле есть, а проверки нет. Или я что-то упустил?

2. Я имел в виду не откат изменений, а системы, где оперирование данными на определенный момент времени — часть бизнес-логики. Биллинги разные, например. Но я вас понял.

[Smi1e]

Я не автор статьи, так что пишу не я :)
Но по поводу потери информации, возможно, вы и правы. Тут нужно подумать о возможных сценариях.

[Yavanosta]

ComodoHacker прав. Решить это можно, как мне кажется, убрав в триггерах SET NOCOUNT ON или возвращая из процедуры флаг успешности операции.
Надо попробовать :-)

[butaji]

Спасибо за статью, делал нечто подобное логируя через событие сохранения DataContext.
Недавно узнал, что в sql server есть встроенный history log, который можно включить и пользоваться им.

[Smi1e]

А по-подробнее можно? Насколько я понимаю, единственный минус логирования средствами СУБД — изолированность БД от приложения и, как следствие, невозможность передавать дополнительные параметры. Мне пришлось отказаться из-за триггеров все по той же причине — СУБД не могла знать, какой пользователь делает изменения, поэтому пришлось выкручиваться из самого приложения.