Версионность и история данных

Не увидел варианта хранения истории изменений на уровне колонки, на не таблицы. Очень помогает когда в таблице много полей, а изменения затрагивают небольшое количество колонок.
Можно получить простое решение для аудита изменений всех таблиц в бд только с одной таблицей истории, особенно если orm/dal позволяет расширить поведение при сохранении записи.

ID, Key, Value, Time.
Храню в мускуле данные как графы.

Видел еще.
«ID+Timestamp», Key1, Key2, Key 3

Особый интерес вызывает внезапное изменение структуры таблиц. Как без головной боли обновить их историю? Интересно, умеет-ли оракл сам разруливать такие ситуации.

как совет: используйте сразу SCD6(1+2+3)
с ним очень удобно да методы работы отработаны, могу пару приемчиков показать.
будет вторая статья про SCD в рунете ;)

Я прямо сейчас (решил кофейку попить и хабр почитать, а тут статья — телепатия :) ) историю состояний объектов реализую так:
таблица объектов object (id, atr1, atr2, atr3, object_state_id)
таблица состояний объектов object_state (id, object_id, atr4, atr5, atr6, date)
где atr1-3 атрибуты 0 и 1 типа, не требующие версионности, а atr4-6 требующие, то есть история значений атрибутов хранится в отдельной таблице, а в объекте ссылка на последнее состояние (по сути и она избыточна, но искать каждый раз максимальное значение date точно не тру). Остальные таблицы ссылаются на object_state, к которой при необходимости джойнится object для получения атрибутов 0 и 1 типа.

Можно сказать, наверное, что это спровоцированный тип 2 :) Не исключено, что в таблице object введу atr4-6 с текущими значениями, чтобы не делать джойн каждый раз для получения текущих значений, но это уже после нагрузочного тестирования — это получится практически тип 4. В этом случае предвижу трудности с разделением ссылок из других таблиц на те, которым нужны атрибуты 1-3 (0 и 1 типа), и на те, которым нужны 4-6(с версиями). Возможно даже в некоторых таблицах придётся вводить две ссылки на объект вообще (чтобы можно было получить текущие значения) и на его конкретную версию (чтобы получать значения на момент создания/изменения).

используем связку из трех таблиц для мониторинга изменений сложных объектов

1 таблица: сессия. какой объект был изменен, кем и когда
2 таблица: список затронутых таблиц и первичных ключей. связана с первой по ключу сессии
3 таблица: список измененных полей в строке таблицы. связана со второй по ключу изменения. в этой таблице лежит имя поля, старое значение и новое значение. для удобства, во второй таблице добавлено избыточное поле с типом операции: вставка, изменение или удаление данных

бенифиты:
— полная история изменений, в том числе у объектов, которые включают данные из многих таблиц
— возможность откатиться до нужной версии
— возможность запланировать изменение в будущем и «накатить» изменения вручную либо по крону
— возможность выбирать какие объекты логировать, а какие нет

недостатки:
— логика сохранения изменений ложится на приложение
— внешние изменения не отображаются в логе (хотя их можно вычислить)

Я использую тип 3 в одном своем приложении, чтобы запомнить предыдущее состояние выполнения job'а. Результаты выполнения job'ов хранятся в отдельной таблице, но чтобы не делать лишнюю выборку, быстрее было запоминать предпоследнее значение в самой записи.

Еще в одном проекте над которым я работаю (c#) версионность реализована как в типе 4: при любом изменении записи ее копия вставляется в другую похожую таблицу (эта табличка находится в другой базе, правда в том же экземпляре СУБД). Похожую, потому что во-первых не все оригинальные колонки записи копируются, а во-вторых есть дополнительные колонки:
— primary key из оригинальной таблицы копируется в обычную колонку новой таблице
— у новой таблицы есть свой primary key (чтобы работало с linq2sql)
— дополнительная колонка в новой таблице содержит тип изменения (удаление, update)
— и есть колонка с датой, когда было изменение

Копирование происходит не на уровне базы, а из кода c# достаточно универсально: тот же самый код используется для копирования разных сущностей. Также на уровне работы с данными есть контроль над тем, для каких сущностей вести историю (и для каких полей: по соответствию названий колонок архивной и оригинальной табличек), для каких нет.

С БД на данный момент работает только один клиент.

Поддерживать такую схему не очень удобно — нужно поддерживать две одинаковые таблички. И при bulk update все очень медленно работает. В последнем случае мы просто не поддерживаем архивирование, потому что по предметной области нет смысла вести историю этих записей.

В СУБД Cache использую что-то наподобии типа 2, только данные храню не в таблице — а в глобале (дереве):

1. все текущие версии объектов имеют 0ю версию
2. 1я версия (если она есть) — самая старая
3. по умолчанию все запросы к БД оперируют с нулевыми версиями
4. по умолчанию индексы для ненулевых версий не строятся
5. в случае регулярной работы с историей — строится дополнительный специальный индекс под те объекты историю которых необходимо мониторить

Пример глобала с объектом CommercialIntention (комерческое намерение) 45537379, текущая версия нулевая, количество предыдущих версий 4:

^Media("CommercialIntention",45537379,0,"LicenseID")=71
^Media("CommercialIntention",45537379,0,"Type")="CommercialIntention"
^Media("CommercialIntention",45537379,0,"UpdateTime")="61950,40226.87313"
^Media("CommercialIntention",45537379,0,"baseOrderList")=45999067
^Media("CommercialIntention",45537379,0,"classifiedList")=45999066
^Media("CommercialIntention",45537379,0,"editionId")=6
^Media("CommercialIntention",45537379,0,"editorLicenseID")=71
^Media("CommercialIntention",45537379,0,"statusId")=2

^Media("CommercialIntention",45537379,1,"LicenseID")=45
^Media("CommercialIntention",45537379,1,"Type")="CommercialIntention"
^Media("CommercialIntention",45537379,1,"UpdateTime")="61944,39328.740813"
^Media("CommercialIntention",45537379,1,"baseOrderList")=45537378
^Media("CommercialIntention",45537379,1,"classifiedList")=45537377
^Media("CommercialIntention",45537379,1,"editionId")=6
^Media("CommercialIntention",45537379,1,"sourceTypeId")=50140
^Media("CommercialIntention",45537379,1,"statusId")=1

^Media("CommercialIntention",45537379,2,"LicenseID")=71
^Media("CommercialIntention",45537379,2,"Type")="CommercialIntention"
^Media("CommercialIntention",45537379,2,"UpdateTime")="61944,39807.398159"
^Media("CommercialIntention",45537379,2,"baseOrderList")=45537575
^Media("CommercialIntention",45537379,2,"classifiedList")=45537574
^Media("CommercialIntention",45537379,2,"editionId")=6
^Media("CommercialIntention",45537379,2,"editorLicenseID")=71
^Media("CommercialIntention",45537379,2,"statusId")=2

^Media("CommercialIntention",45537379,3,"LicenseID")=71
^Media("CommercialIntention",45537379,3,"Type")="CommercialIntention"
^Media("CommercialIntention",45537379,3,"UpdateTime")="61950,37803.280408"
^Media("CommercialIntention",45537379,3,"adTypeId")=30202
^Media("CommercialIntention",45537379,3,"baseOrderList")=45996926
^Media("CommercialIntention",45537379,3,"classifiedList")=45996925
^Media("CommercialIntention",45537379,3,"editionId")=6
^Media("CommercialIntention",45537379,3,"editorLicenseID")=55
^Media("CommercialIntention",45537379,3,"sourceTypeId")=50140
^Media("CommercialIntention",45537379,3,"statusId")=3

^Media("CommercialIntention",45537379,4,"LicenseID")=71
^Media("CommercialIntention",45537379,4,"Type")="CommercialIntention"
^Media("CommercialIntention",45537379,4,"UpdateTime")="61950,40193.904669"
^Media("CommercialIntention",45537379,4,"baseOrderList")=45999039
^Media("CommercialIntention",45537379,4,"classifiedList")=45999038
^Media("CommercialIntention",45537379,4,"editionId")=6
^Media("CommercialIntention",45537379,4,"editorLicenseID")=71
^Media("CommercialIntention",45537379,4,"statusId")=8

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

Реализация была как раз по «типу 2». Однако структура данных была «продублирована» views, которые выдавали текущую версию процессов.

P.S. впринципе поле DATE_START не нужно, хватит одного DATE_END

Использую дополнительные поля vn(INT), is_latest( 'Y','N'),
соответственно номер версии и флаг, последняя ли она.

На все запросы к таблицам с версиями добавляется автоматом суффикс AND is_latest='Y'.

Используем 4й тип. Только в доп. таблице еще есть запись кто и когда что менял. Помогает находить злостных вредителей.

Если я не ошибаюсь, то 6-й тип — это bitemporal database. У нас например так хранится дерево (граф) объектов. Собственно, какие там есть мысли DATE_{START,END}, VERSION_{START,END}, а также для каждой записи ID и GLOBALID — то есть, ид текущий (DS,DE,VS,VE) и немутирующий идентификатор. При этом достигается гибкость в плане использования будущих периодов и фиксации старых значений (историчность). Версионность — имхо, это конечно костыль. Как написано на вики есть transaction time — его и надо учитывать, и отдельно вести список версий, то есть список точек времени с историей.

Я бы еще добавил, что правильная историчность — это один шаг для репликации на другую базу. Взаимное применение изменений баз одной на другую, их пересечение и будет актуальным состоянием базы.

у нас каждая таблица(которая должна иметь историчность) имеет следующие дополнительные поля seq (0 — для текущей записи, primary key является связка (seq,id)), плюс поля кем и когда создано, кем и когда изменено. для выборки текущих значений создаем view. при обновлении записи, сначала генерим новый seq, создаем копию текущего состояния с новым seq, кто и когда обновил, потом обновляем значения в записи с seq=0.

Я еще использовал такой способ — для полей, которые часто меняются создавал отдельную таблицу. Плюс в том, что уменьшается объем памяти, требуемый для хранения истории. Если создавать для каждого поля, то база начинает страшно тормозить из-за многочисленных join.

У нас используется метод 2. Для аудита всех таблиц ведется одна отдельная большая партиционированная таблица с инфо о том, кто изменил (ip, машина, логин, и т.д.), имеющая ссылки на измененную таблицу и id измененного объекта. По прошествии времени старые партиции отправляются в архив.

Я недавно как раз на эту тему вот такую несложную библиотечку для PostgreSQL писал:
github.com/DmitryKoterov/dklab_rowlog

Можно подключить к любой таблице логирование по типу 4 за одну команду, причем сами данные хранятся в hstore. Пример использования (в README на github-е есть еще):

— Example: monitor change of column «a» and «c». Add an entry to public.rowlog
— table if and only if one of these columns are changed.
CREATE TRIGGER t_rowlog
AFTER INSERT OR DELETE OR UPDATE ON test_src1 FOR EACH ROW
EXECUTE PROCEDURE rowlog.t_rowlog_aiud('diff=>a', 'diff=>c', 'rowlog=>public.rowlog');

Только надо учитывать 2 вещи при использовании:
1. Библиотека не рассчитана на ультравысокие нагрузки, т.к. там внутри несколько EXECUTE SQL-ей.
2. Хранение измененных данных в hstore удобно тем, что быстро новые поля можно добавлять, однако есть и недостаток — если табличка-источник сильно меняется (например, там поля удаляются или переименовываются), в hstore останутся старые версии со старой структурой.

Поэтому на практике я бы порекомендовал ее применять в основном для целей подсчета статистики различной по живой базе (KPI).

Некая разновидность 4-го типа реализована в модуле Hibernate-Envers.

Я как раз сейчас пишу подобную штуку для MySQL, чтобы автоматом создавать лог-таблицы, триггеры, а так-же мониторить изменения схемы…

Используем второй способ. Именно так реализована MVCC в InnoDB (MySQL) — оттуда о нём и узнали. Только там вместо дат используются внутренние номера транзакций.

У нас же реализация полностью на ПХП (MySQL еще недорос для перекладывания на неге какой-либо логики) как extension к Yii.

Было интересно узнать про другие способы, спасибо.

Использую вариацию 4-го метода. Вариация заключается в том, что храню только разницу по сравнению с предыдущей записью, следовательно объем хранимой информации сильно меньше (при том что таблица широкая и за раз изменяется только порядка 10% полей). Если поля в текущей и предыдущей записях эквивалентны, в таблицу логов записывается NULL. Впрочем, есть свои хитрости чтобы отличить в таблице лога реальный NULL от повторения значения. Состояние записей на любой прошлый момент времени собираются через аналитику, можно просмотреть шаги изменений, уточнить кто производил изменения и т.п… Текущее состояние видно сразу без всяких хитростей.
Полагаю, я изобрел велосипед. До сих пор не знаю как такой метод называется, но он работает не хуже flashback и позволяет более предсказуемо управлять объемами, плюс хранить дополнительную информацию по изменениям.

DateEnd = 01.01.9999
Программисты будущего в новый 9999 год будут материть нас за это =)

А куда 5й тип дели, ироды?

В случаях использования полей начала и конца версии

как один из дополнительных подходов — контролировать непрерывность интервалов с помощью констрейнтов, например так:

drop table scdtest;
create table scdtest(s_code varchar2(10), s_value varchar2(10), s_start_date date not null, s_end_date date, s_latest varchar2(10) as (decode(s_end_date, null, s_code, null)));
alter table scdtest add constraint unq_s_start_date unique (s_code, s_start_date);
alter table scdtest add constraint unq_s_latest unique (s_latest) deferrable INITIALLY DEFERRED;
alter table scdtest add constraint fk_s_s foreign key (s_code, s_end_date) references scdtest (s_code, s_start_date) deferrable INITIALLY DEFERRED;

-- открываем интервал 
insert into scdtest (s_code, s_value, s_start_date, s_end_date) values ('A' ,'A1', to_date('01.01.2000', 'DD.MM.YYYY'), null);
commit;

-- продложаем интервал
update scdtest set s_end_date = to_date('01.01.2001', 'DD.MM.YYYY') where s_latest = 'A';
insert into scdtest (s_code, s_value, s_start_date, s_end_date) values ('A' ,'A2', to_date('01.01.2001', 'DD.MM.YYYY'), null);
commit;

хотелось бы, кстати, узнать какие СУБД это поддерживают

Подозреваю, что в чистом виде никакие, ибо накладно это :) Плохо себе представляю консистентную ревалидацию например 10 террабайт данных по недетерминистической функции.

Хотя, если логика функции проверки укладывается в SQL scope, то теоретически можно построить матвьюху и навешать констрейнтов на неё, но:
— при сильно замороченной логике возможно матвьюху не удастся сделать fast refresh on commit, там вроде требования к детерминистичности те же если не жёстче
— ексепшены на нарушения придуманной логики будете ловить только при завершении транзакции ровно как с отложенными констрейнтами, ибо refresh on commit
— в 10ой версии распределенные транзакции, приводящие к обновлению матвьюхи приводят к ора-600
— двойной расход места
Не смотря на всё это я таким костылем пользовался и он оказался вполне себе живуч.

Ну и отложенную асинхронную проверку можно конечно навелосипедить в любой момент, но это уже другая опера.

Тип 0 используется в Event Sourcing (ES). Может сложиться ощущение что ES это Тип 2, но это не так. Потому что, ES разделяет понятие события и агрегат. Агрегат это искомая бизнес сущность, например документ. Событие же это то что произошло с документом и оно не изменно. Этьо уже произошло и не должно никогда меняться. Если хотим изменить просто создаем новое событие «А теперь хотим изменить поле на какое-то значение».
Сам факт наличие событий является историей данного документа. ES гораздо больше чем версионность, т.к. события могут говорить не только об изменениях в документе, но и других процессах (документ без изменений был согласован; документ без изменений перешел в архив и т.п.).
Поэтому Тип 0 во всю используется в современном стеке технологий.
Но это так дополнение, спасибо за статью :)