В некотором царстве-государстве жил-был грозный царь. И было у царя множество бояр, что день-деньской отчеты готовили: сколько войск на службе, да велика ли казна царская, уродилась ли пшеница в году этом, да много ли коровы дают молока.
Захотелось царю, чтоб в отчетах супротив каждой циферки имя боярина отражалось, ее посчитавшего. Да приказал плотникам механизмус построить по чертежам заморским, чтоб если кто из бояр ошибется – того на дыбу, а циферки его лживые взад вернуть.
Повертели в руках плотники чертежи, что Oracle Flashback зовутся, да засомневались. Неужто рычажок UNDO_RETENTION, по-умолчанию в 3 минуты установленный, можно без последствий до года докрутить, ничего не потерять и не утонуть в гигабайтах лишних данных. Решили свой механизмус собрать.
Сказки кончились, дальше суровая реализация механизмуса.
Основная таблица широкая, включает в себя ID записи, сотню не связанных друг с другом информационных полей и служебную информацию: дату создания или изменения строки, идентификатор боярина, создавшего или отредактировавшего строку через веб-интерфейс. Каждый боярин в разные промежутки времени заполняет строку известными ему данными, либо редактирует уже заполненные другими боярами поля.
create table MAIN_TABLE (
ID number primary key,
INFO_FIELD1 number,
INFO_FIELD2 varchar2(100),
INFO_FIELD3 date,
…
CREATE_DATE date default sysdate,
CREATE_USER_ID number NOT NULL,
UPDATE_DATE date,
UPDATE_USER_ID number
)
partition by range ( CREATE_DATE ) …
;
Таблица истории правок практически полностью повторяет структуру основной таблицы, разве что лишена полей CREATE_DATE, CREATE_USER_ID – эти поля остаются неизменными, нет смысла хранить историю по ним. Кроме того, историческая таблица не участвует в государственных процессах, ключи ей не нужны. Секционирование также перенесено с даты создания строки на дату изменения, это позволит перемещать или удалять секции с устаревшими правками.
create table HISTORY_TABLE (
ID number,
INFO_FIELD1 number,
INFO_FIELD2 varchar2(100),
INFO_FIELD3 date,
…
UPDATE_DATE date,
UPDATE_USER_ID number
)
partition by range ( UPDATE_DATE ) …
;
Теперь при изменении основной таблицы надо сохранять прежнее значение отредактированного поля (полей) в таблицу истории, создав новую строку. Таким образом, в основной таблице хранится актуальная на текущий момент информация. В исторической таблице на каждый ID строки из основной таблицы приходится несколько строк с тем же ID, различающихся по UPDATE_DATE.
Следствие 1: число строк исторической таблицы равно количеству правок основной.
Следствие 2: если строка создана в основной таблице, но ни разу не редактировалась, ее ID нет в исторической таблице.
Для заполнения таблицы истории плотники воспользовались триггером – наиболее надежным способом. Чтобы выделить изменившееся поле среди всех прочих, стали сверять старое и новое значение каждого поля. Изменений нет – пишем NULL. Это положительно сказывается на объеме: не нужно дублировать данные, которые не изменились с предыдущей правки.
Но вот незадача, вредные бояре могут стирать уже заполненные поля. Такой случай надо обрабатывать отдельно, NULL писать нельзя – это отсутствие изменений. Вместо NULL стали подставлять в историю логически невозможное значение. Например, ни один казначей в мире по собственной воле не укажет в отчете царю отрицательную величину казны, да и веб-интерфейс этого не позволит. Если считать значение -99 (01.01.1970 для полей типа дата) кодом операции стирания поля, гармония восстанавливается.
create or replace trigger TRG_MAIN_BEFORE_UPD
before update on MAIN_TABLE
for each row
declare
HIST HISTORY_TABLE %ROWTYPE;
begin
HIST.ID := :OLD.ID;
if (:OLD.UPDATE_DATE is null) then
-- строка правится первый раз - сохраняем в историю всё как есть
HIST.UPDATE_DATE := :OLD.CREATE_DATE;
HIST.UPDATE_USER_ID := :OLD.CREATE_USER_ID;
HIST.INFO_FIELD1 := :OLD.INFO_FIELD1;
HIST.INFO_FIELD2 := :OLD.INFO_FIELD2;
HIST.INFO_FIELD3 := :OLD.INFO_FIELD3;
else
-- строка правится повторно - сохраняем только измененные поля,
-- если поле не изменилось, сохраняем NULL
-- если изменено на NULL, вставляем невозможное значение
HIST.UPDATE_DATE := :OLD.UPDATE_DATE;
HIST.UPDATE_USER_ID := :OLD.UPDATE_USER_ID;
if nvl(:NEW.INFO_FIELD1, -99) <> nvl(:OLD.INFO_FIELD1, -99) then
HIST.INFO_FIELD1 := nvl(:OLD.INFO_FIELD1, -99);
end if;
if nvl(:NEW.INFO_FIELD2, '-99') <> nvl(:OLD.INFO_FIELD2, '-99') then
HIST.INFO_FIELD2 := nvl(:OLD.INFO_FIELD2, '-99');
end if;
if nvl(:NEW.INFO_FIELD3, to_date('01.01.1970', 'dd.mm.yyyy')) <>
nvl(:OLD.INFO_FIELD3, to_date('01.01.1970', 'dd.mm.yyyy')) then
HIST.INFO_FIELD3 :=
nvl(:OLD.INFO_FIELD3, to_date('01.01.1970', 'dd.mm.yyyy'));
end if;
end if;
-- Вставка результата в таблицу истории
insert into HISTORY_TABLE (ID, UPDATE_DATE, UPDATE_USER_ID,
INFO_FIELD1, INFO_FIELD2, INFO_FIELD3)
values (HIST.ID, HIST.UPDATE_DATE, HIST.UPDATE_USER_ID,
HIST.INFO_FIELD1, INFO_FIELD2, HIST.INFO_FIELD3);
end TRG_MAIN_BEFORE_UPD;
* This source code was highlighted with Source Code Highlighter.Выбирая из таблицы истории, сразу видно, кто какой вклад внёс:
select *
from HISTORY_TABLE
where ID = 1
oder by UPDATE_DATE;
---------------------------------------------------------------------------
ID | INFO_FIELD1 | INFO_FIELD2 | INFO_FIELD3 | UPDATE_DATE | UPDATE_USER_ID
---------------------------------------------------------------------------
1 12 AAA 05.11.2010 1
1 -99 01.11.2010 06.11.2010 2
1 BBB 07.11.2010 3
Пользователь 1 при создании записи заполнил первых два информационных поля, проигнорировав (оставив пустым) третье. Пользователь 2 на следующий день стер значение первого поля (видимо посчитал некорректным) и заполнил третье. Еще через день пользователь 3 исправил второе поле, первое же так и осталось незаполненным. Не забываем, что это исторические правки, значит редакция 07.11.2010 не последняя. В основной таблице хранится актуальная версия этой записи, которая может кардинально отличаться от прежних версий.
Очевидно, поиск по ID и дате изменения наиболее популярный в таблице истории. Стоит создать локальные индексы по этим полям.
Чтобы удовлетворить потребности царя, в механизмус была добавлена возможность восстанавливать данные на любой период времени. Для этого дырки (NULLы) в таблице истории заменяются значением, актуальным на тот период времени. А невозможные значения -99 подменяются на NULLы. Плотники сделали представление для удобства:
create or replace view V_HISTORY_RESTORE (
ID,
INFO_FIELD1,
INFO_FIELD2,
INFO_FIELD3,
UPDATE_DATE,
UPDATE_USER_ID
)
as
select
ID,
case
when LAST_VALUE(INFO_FIELD1 ignore NULLS) over (partition by ID order by UPDATE_DATE) = -99
then NULL
else
LAST_VALUE(INFO_FIELD1 ignore NULLS) over (partition by ID order by UPDATE_DATE)
end as INFO_FIELD1,
case
when LAST_VALUE(INFO_FIELD2 ignore NULLS) over (partition by ID order by UPDATE_DATE) = '-99'
then NULL
else
LAST_VALUE(INFO_FIELD2 ignore NULLS) over (partition by ID order by UPDATE_DATE)
end as INFO_FIELD2,
case
when LAST_VALUE(INFO_FIELD3 ignore NULLS) over (partition by ID order by UPDATE_DATE) = to_date('01.01.1970', 'dd.mm.yyyy')
then NULL
else
LAST_VALUE(INFO_FIELD3 ignore NULLS) over (partition by ID order by UPDATE_DATE)
end as INFO_FIELD3,
UPDATE_DATE,
UPDATE_USER_ID
from HISTORY_TABLE;
* This source code was highlighted with Source Code Highlighter.Довольно громоздко, но работает быстро. Здесь используется аналитическая функция LAST_VALUE с конструкцией ignore NULLS, которая берет для каждой строки значение поля из предыдущей (в сортировке по дате) строки, если оно не NULL. Если в предыдущей строке оно NULL – двигаемся выше, пока не наткнемся на непустое значение.
Выбирая строки из представления для восстановления истории, видно состояние основной таблицы на тот или иной момент времени:
select *
from V_HISTORY_RESTORE
where ID = 1
oder by UPDATE_DATE;
---------------------------------------------------------------------------
ID | INFO_FIELD1 | INFO_FIELD2 | INFO_FIELD3 | UPDATE_DATE | UPDATE_USER_ID
---------------------------------------------------------------------------
1 12 AAA 05.11.2010 1
1 AAA 01.11.2010 06.11.2010 2
1 BBB 01.11.2010 07.11.2010 3
Теперь для восстановления строки достаточно выбрать только дату правки и сделать update для основной таблицы. Так как в основную таблицу идет update, получается еще одна строка в таблице истории. А значит, в будущем можно вернуться к моменту до начала возврата в прошлое (если фраза звучит противоречиво, пересмотрите фильм “Назад в будущее”).
Для предания анафеме вклада провинившихся холопов достаточно удалить их строки из таблицы истории, а в основной таблице для строк, содержащих плохие правки, восстановить предыдущее состояние строки.
Вот и сказочке конец. Царь остался доволен.
Механизмус работает, гибко настраивается, какие именно поля, каких таблиц хранить. История становится более управляемой. При необходимости можно удалять ненужные правки строк, заверенные царем, освобождая место в хранилище.
Одно плохо — плотники утомились все это пилить. И опасность есть, что придет левша, добавит полей в основную таблицу, а про историю забудет. Против кривых рук защиты нет.
На закуску статья Версионность и история данных
p.s. Велосипед мой. Не претендую на новаторство, но гугление аналогов не выявило. Картинка для статьи нарыта в интернетах.
