Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
День добрый, поделитесь опытом.

Есть база данный MS SQL 2008 R2

В ней работает несколько разработчиков, в дальнейшем число которых может увеличиться.

Требуется создать инструмент истории изменения данных в таблицах, т.е. универсальный для всех таблиц.

При рассмотрении вариантов зашел в тупик:

1) Хранение информации в самой таблице данных,
т.е. переносить триггером данные с пометкой истории
+ Всегда правильная структура
- Утяжеление таблицы
- Проблема с формулами, инкриментами и тп - решаема
- А вот прблема с уникальными индексами, т.е. история не будет сохраняться т.к. dublicate key. Можно пробовать решить при создании искусственно добавленного в индекс изменяющегося только в истории поля, но кто за этим будет следить...

2) Хранение в отдельной таблице (или CDC)
так же переносить триггером в одноименную таблицу с постфиксом _History
+ Никаких проблем с вычисляемыми полями и индексами
+ Разгрузка основных таблиц
- Изменение структуры - мне так и не удалось придумать синхронное изменение структуры

3) Хранение всей истории всех таблиц в одной таблице
т.е. таблица структуры: ID, TableName, TableId, ColumnName, PreValue, NewValue, DateChange и тп
+ нет зависимости от структуры, индексов и тп
- я так и не смог понять как можно вывести состояние всей записи (Table+ID) на конкретную дату в истории...

Подскажите что не учел, есть ли другие варианты реализации или решение критичных минусов

Заранее спасибо!
3 авг 13, 12:24    [14657806]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
aleks2
Guest
Wrun
2) Хранение в отдельной таблице (или CDC)
так же переносить триггером в одноименную таблицу с постфиксом _History

- Изменение структуры - мне так и не удалось придумать синхронное изменение структуры


А ты копируй в таблицу истории через *
Insert History select * from deleted

автоматически структуру синхронизировать НЕ будет.
Но будет очень настойчиво напоминать при рассинхронизации.
3 авг 13, 13:11    [14657875]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
aleks2
Wrun
2) Хранение в отдельной таблице (или CDC)
так же переносить триггером в одноименную таблицу с постфиксом _History

- Изменение структуры - мне так и не удалось придумать синхронное изменение структуры


А ты копируй в таблицу истории через *
Insert History select * from deleted

автоматически структуру синхронизировать НЕ будет.
Но будет очень настойчиво напоминать при рассинхронизации.


Да, я думал о чем то таком, увы при этом пострадают пользователи, ведь сообщение им повылезает, а не админам, которые эту ситуацию создали... Подобным буду пользоваться от безысходности, ведь это скорей "костыль" чем решение (
3 авг 13, 13:14    [14657883]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
qfqwefqwef
Guest
Wrun,

вам история изменения справочников нужна? храните их содержимое в sql-файлах
ref1.sql для таблицы ref1
ref2.sql для таблицы ref2

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

sql-файлы в систему контроля версий и все. когда деву надо обновить справочник на определенную версию, он выколупывает нужную ему версию sql-файла и прогоняет его на своей копии БД локально.
3 авг 13, 14:48    [14658063]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35396
Блог
Wrun
Да, я думал о чем то таком, увы при этом пострадают пользователи, ведь сообщение им повылезает, а не админам, которые эту ситуацию создали... Подобным буду пользоваться от безысходности, ведь это скорей "костыль" чем решение (


Подавить сообщение в клиенте и перенаправить ошибку админам (с помощью того же клиента)?
3 авг 13, 14:57    [14658089]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
qfqwefqwef
Wrun,

вам история изменения справочников нужна? храните их содержимое в sql-файлах
ref1.sql для таблицы ref1
ref2.sql для таблицы ref2

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

sql-файлы в систему контроля версий и все. когда деву надо обновить справочник на определенную версию, он выколупывает нужную ему версию sql-файла и прогоняет его на своей копии БД локально.


Справочников - не справочников - ни суть, нужно история записи в таблице, что с ней какой пользователь делал, какой она была на заданную историческую дату и тп... Как то я даже не могу представить как от БД перейти к хранению в Файлах...
3 авг 13, 15:33    [14658196]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
Критик
Wrun
Да, я думал о чем то таком, увы при этом пострадают пользователи, ведь сообщение им повылезает, а не админам, которые эту ситуацию создали... Подобным буду пользоваться от безысходности, ведь это скорей "костыль" чем решение (


Подавить сообщение в клиенте и перенаправить ошибку админам (с помощью того же клиента)?


Это все логично и понятно, но если админ подправил в пятницу табличку, а все выходные начнет падать добавление записи у пользователей - сообщай, не сообщай об ошибке - приятного мало... Как уже выше говорил - подобные вещи лишь от безысходности...

Я думал даже при падении создавать _History1 и тд, а потом разбирать, но все же это черный ящик...
3 авг 13, 15:36    [14658206]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Wrun
3) Хранение всей истории всех таблиц в одной таблице
т.е. таблица структуры: ID, TableName, TableId, ColumnName, PreValue, NewValue, DateChange и тп
+ нет зависимости от структуры, индексов и тп
- я так и не смог понять как можно вывести состояние всей записи (Table+ID) на конкретную дату в истории...
Чисто теоретически - PIVOT/UNPIVOT, должно бы решить полностью, только не с двумя, а с одним (предыдущим) значением, это уже вроде обсуждалось раньше. Хотя тащить заголовочные данные в каждый столбец тоже как-то неправильно...

Вариант 2 все же лучше. А изменение структуры можно обязать делать сразу в двух таблицах, или, если не получится во второй - отложить историю со старой структурой в архив и сделать новую таблицу истории. Заполнять ли ее данными - вряд ли, хотя и возможно
3 авг 13, 15:58    [14658256]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
Cygapb-007
Wrun
3) Хранение всей истории всех таблиц в одной таблице
т.е. таблица структуры: ID, TableName, TableId, ColumnName, PreValue, NewValue, DateChange и тп
+ нет зависимости от структуры, индексов и тп
- я так и не смог понять как можно вывести состояние всей записи (Table+ID) на конкретную дату в истории...
Чисто теоретически - PIVOT/UNPIVOT, должно бы решить полностью, только не с двумя, а с одним (предыдущим) значением, это уже вроде обсуждалось раньше. Хотя тащить заголовочные данные в каждый столбец тоже как-то неправильно...

Вариант 2 все же лучше. А изменение структуры можно обязать делать сразу в двух таблицах, или, если не получится во второй - отложить историю со старой структурой в архив и сделать новую таблицу истории. Заполнять ли ее данными - вряд ли, хотя и возможно


Осталось понять как Обязать (

Пробовал DDL триггер - не удалось отследить все события, конкретно изменение типа столбца - таблица пересоздается, а DDL триггер не отслеживает перегонку данных...
3 авг 13, 16:07    [14658280]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Wrun
Cygapb-007
А изменение структуры можно обязать делать сразу в двух таблицах, или, если не получится во второй - отложить историю со старой структурой в архив и сделать новую таблицу истории.

Осталось понять как Обязать
Административно-финансово
Это ведь ваши разработчики? Составьте инструкцию по модификации структуры таблиц.

Кстати, логирующий триггер кто будет переделывать после изменения структуры таблицы?
3 авг 13, 16:18    [14658293]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
Cygapb-007
Wrun
пропущено...

Осталось понять как Обязать
Административно-финансово
Это ведь ваши разработчики? Составьте инструкцию по модификации структуры таблиц.

Кстати, логирующий триггер кто будет переделывать после изменения структуры таблицы?


Триггер нормально - вычисляет сколько и каких полей, тут все норм...
3 авг 13, 17:25    [14658417]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
aleks2
Guest
Wrun
Триггер нормально - вычисляет сколько и каких полей, тут все норм...

Что ж тибя останавливает? Раз пошла такая пЪянка - реж последний агурец.
3 авг 13, 19:29    [14658655]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
aleks2
Wrun
Триггер нормально - вычисляет сколько и каких полей, тут все норм...

Что ж тибя останавливает? Раз пошла такая пЪянка - реж последний агурец.


Либо я не очень понял что завуалированно под агурцом, но вроде вопрос стоял не мотивировать меня на действия, а предложить решения проблемы, и не финансово-организационным путем, а сугубо техническим...
3 авг 13, 22:00    [14659011]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
aleks2
Guest
Wrun
Либо я не очень понял что завуалированно под агурцом, но вроде вопрос стоял не мотивировать меня на действия, а предложить решения проблемы, и не финансово-организационным путем, а сугубо техническим...


Ну, проверяй поля таблиц по INFORMATION_SCHEME и выдавай DDL-скрипт модификации History-таблицы... только это в триггере не сканает. Но можно замутить в джобе.

Короче, фантазируй, парниша.
4 авг 13, 13:39    [14660023]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
Wrun
Требуется создать инструмент истории изменения данных в таблицах, т.е. универсальный для всех таблиц.

для ВСЕХ ТАБЛИЦ ????????????
это что-то не то.
А вообще SCD2 в таблице - то есть твой вариант номер 1 вполне рабочий. И места занимает меньше чем в варианте 2,
pk конечно будет = старый pk + date_from
5 авг 13, 13:16    [14663548]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Ivan Durak
pk конечно будет = старый pk + date_from


ага, щаз. имеем справочник, в нем записи:

ПК Дата
1 10
1 20

и надо сделать выборку по состоянию на 15 число. запрос представляете? ага, попадалово. альтернатива - хранить состояние на каждый день - перестает быть историей, но - да - упрощает выборки за счет колоссальных объемов хранения. хранить диапазоны действия? опять перестает быть историей. и я уже молчу про то, что дата реального изменения данных может не быть датой, с которой эти изменения должны "работать". а это часто надо. так что "история" - это одно, а обеспечение "воспроизводимости" отчетов на исторические даты - совсем другое. и вот для истории имхо лучше всего "тупо" сбрасывать старые значения в табличку аналогичной структуры (но с другой индексацией, разумеется) + несколько технических полей (да, тот еще вопрос как их назвать!)
5 авг 13, 13:46    [14663780]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
HelenM
Member

Откуда: Москва
Сообщений: 53
Cygapb-007
Wrun
пропущено...

Осталось понять как Обязать
Административно-финансово
Это ведь ваши разработчики? Составьте инструкцию по модификации структуры таблиц.

Кстати, логирующий триггер кто будет переделывать после изменения структуры таблицы?


Плюсую по всем пунктам!

Best praktice: разделять отладочные и промышленные базы. Тогда у пользователей точно ничего "вылезать" никуда не будет.
Наигрались на отладочных, оттестировались - можно тогда и промышленную базу "допилить".

Если нет регламентов, то анархию никакие триггеры не остановят...
5 авг 13, 14:37    [14664201]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
HelenM
Cygapb-007
пропущено...
Административно-финансово
Это ведь ваши разработчики? Составьте инструкцию по модификации структуры таблиц.

Кстати, логирующий триггер кто будет переделывать после изменения структуры таблицы?


Плюсую по всем пунктам!

Best praktice: разделять отладочные и промышленные базы. Тогда у пользователей точно ничего "вылезать" никуда не будет.
Наигрались на отладочных, оттестировались - можно тогда и промышленную базу "допилить".

Если нет регламентов, то анархию никакие триггеры не остановят...



Ох как мне "нравиться" вечный неконструктизм на этом форуме...
База используется под WEB, на котором разрабатываются системы, изолированные друг от друга на своей CMS, следовательно всегда будут проходить работы с базой, поэтому и требуется упростить жизнь разработчикам написав движок, который так же включает работу в БД, а не так, что бы каждый разрабатывал своё хисторирование и тп...

А пути решения типа: вменить, заставить, регламенты и тп - это совершенно не технически и не исключают человеческий фактор, и уж точно не имеет место при техническом вопросе. Да и в принципе подход - "Они виноваты" - это минус разработчику!
5 авг 13, 15:43    [14664832]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
Crimean
Ivan Durak
pk конечно будет = старый pk + date_from


ага, щаз. имеем справочник, в нем записи:

ПК Дата
1 10
1 20

и надо сделать выборку по состоянию на 15 число. запрос представляете? ага, попадалово. альтернатива - хранить состояние на каждый день - перестает быть историей

"эх, Вася, Вася".
ПК Дата, Дата_окончания
1 10 19
1 20 9999

запрос на 15-е выглядит как

select * from tab where @date between Дата and Дата_окончания. (очень сложно, да)
5 авг 13, 17:48    [14665608]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Ivan Durak,

хе. для начала, хранение "только даты изменения" и "диапазона дат действия изменения" - два достаточно разных решения. про вторую дату никто явно не говорил или я просмотрел. но не суть, дальше. во-первых показанная работа с диапазоном - не "сложная", а "неуникально и, как следствие - неэффективно". то есть пламенный привет спулам на радость устройствам tempdb ибо будут ошибки оценок селективности, да и без ошибок планы будут на порядок хуже чем соединение тупо по уникальному набору полей. второй вопрос - контроль тех самых диапазонов, но не о нем сейчас речь
ну и мы плавно в случае с диапазонами подходим от вопроса собственно журналирования изменений к вопросу точного "исторического" воспроизведения отчетов. а он, обычно, осложняется разными датами физического выполнения изменения и даты, с которой изменение должно "вступить в силу".. правда, и не об этом сейчас речь тоже :)
5 авг 13, 20:29    [14666343]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
Ivan Durak
Wrun
Требуется создать инструмент истории изменения данных в таблицах, т.е. универсальный для всех таблиц.

для ВСЕХ ТАБЛИЦ ????????????
это что-то не то.
А вообще SCD2 в таблице - то есть твой вариант номер 1 вполне рабочий. И места занимает меньше чем в варианте 2,
pk конечно будет = старый pk + date_from


Я вроде описывал проблему первого типа, создав кто-то уникальный индекс типа Fld1 + Fld2 - тут же получает проблему, что в этом индексе не может умещаться история!
6 авг 13, 11:06    [14668046]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
Wrun
Ivan Durak
пропущено...

для ВСЕХ ТАБЛИЦ ????????????
это что-то не то.
А вообще SCD2 в таблице - то есть твой вариант номер 1 вполне рабочий. И места занимает меньше чем в варианте 2,
pk конечно будет = старый pk + date_from


Я вроде описывал проблему первого типа, создав кто-то уникальный индекс типа Fld1 + Fld2 - тут же получает проблему, что в этом индексе не может умещаться история!

не должно быть никаких "кто-то", кто бы был не в курсе, что таблицы теперь историю хранят
6 авг 13, 12:11    [14668520]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Wrun
Member [заблокирован]

Откуда:
Сообщений: 126
Ivan Durak
Wrun
пропущено...


Я вроде описывал проблему первого типа, создав кто-то уникальный индекс типа Fld1 + Fld2 - тут же получает проблему, что в этом индексе не может умещаться история!

не должно быть никаких "кто-то", кто бы был не в курсе, что таблицы теперь историю хранят



Какие все теоретики, ну не знаете вы как ответить технически, зачем палемику разводить, смиритесь и проходите дальше, нет ведь разведут теорию, а ответов нет...
7 авг 13, 14:40    [14676021]     Ответить | Цитировать Сообщить модератору
 Re: Хисторирование данных  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
Wrun
Ivan Durak
пропущено...

не должно быть никаких "кто-то", кто бы был не в курсе, что таблицы теперь историю хранят



Какие все теоретики, ну не знаете вы как ответить технически, зачем палемику разводить, смиритесь и проходите дальше, нет ведь разведут теорию, а ответов нет...

я делюсь опытом.
7 авг 13, 15:19    [14676288]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить