Логирование и откат правок

[LexL]

Мы насчет экономии места не парились, NULLы в базу не писали, зато запросы даже по историческим данным летают:

select * from H_DATA where (key, HLoad) in
(select t.key, max(t.HLoad) from H_DATA t
 where t.ValidFrom <= Globals.FocusTo() and Globals.FocusTo() < t.ValidUntil
   and t.ValidFrom <= Globals.FocusFrom()
 group by t.key) and Deleted is null

Globals — package, где сессионными переменными можно фокус времени задать. Как фокус куда, так и фокус откуда.
Фокус откуда — это с какого времени мы смотрим (т.е. более поздние обновления как бы не видим)
Фокус куда — на какую точку во времени.

key — это business logic key данный, HLoad (date) момент внесения изменений, ValidFrom/ValidUntil(date) — на какой промежуток времени существует изменение (вечные изменения пишутся как ValidUntil = 9.12.9999, типо того).

Фактически сверху код вьюхи для доступа к интересующему срезу данных…

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

[maovrn]

Похоже в вашем запросе хабрапарсер кусок с ValidUntil отъел. Но идея понятна. Главное правило оптимизации — отсечь все лишнее. Но что если пользователь захочет просмотреть правки за год или больше? Довольно широкий фокус получается.

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

Основная идея, как мне кажется, в плясках с NULL. Ведь так в базе хранятся diffы, как в SVN или GIT. При том что таблица имеет почти сотню полей и каждая строка правится в среднем 4-5 раз за жизненный цикл, экономия получается существенная. Это было веской причиной создания велосипеда.

[LexL]

Парсер ничего не отъел :)

У нас данные обновлялись на 5-20% каждый день. Но нам было важно, чтобы не проседал доступ к историческим данным (мы их «историческими» даже не называли, просто информация на определенную дату, например, изменения котировок акций).

Можно нормальные индексы повесить, запросы по ним делать. Если кодировать с NULL, то всё удобство и простота подхода сводится на нет. Кроме того, удаление данных из NULL упакованной таблицы — непростая задача. Из песни слов не выкинешь. В моем случае, можно удалять данные, не боясь запороть историю…

[maovrn]

Прошу прощения: смотрел с маленького экрана, кусок запроса под границу блока прятался.
Таблица у меня не упакованная, про такой нюанс не думал. Значит есть повод для развития идеи.

[GrossHo]

Да, у меня похожим образом сделано. Поэтому вопрос — со вторичными ключами как нибудь решали проблему? То есть, когда предыдущую запись уже пометил устаревшей, и попытался вставить новую запись, и она не прокатывает, поскольку вторичный ключ обнаружил дубликат в новых данных.

[LexL]

У нас использовался т.н. identity механизм, который мог находить в истории старые записи и реанимировать их :)

[xtender]

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

[maovrn]

Да, обработка спецзначений тут выглядит громоздко. Была мысль отказаться от операции стирания поля вообще, но система должна подстраиваться под человека, а не человек под систему. Зато так поле в строке истории самодостаточно. Все выглядит предельно понятно даже при просмотре из sqlplus. Для обработки результата не нужны более никакие преобразования.

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

[abyrvalg]

Можно, помимо копии столбцов из основной таблицы, держать в исторической таблице столбец XMLType. Убегаю, поэтому скину неразборчиво без форматирования:

— основная таблица
create table T
(
ID INTEGER,
TXT VARCHAR2(10),
D DATE
);

— историческая
create table T_HIST
(
ID INTEGER,
TXT VARCHAR2(10),
D DATE,
WHO_ VARCHAR2(30),
WHEN_ DATE,
WHAT_ VARCHAR2(1),
DATA_BEFORE_ XMLTYPE,
DATA_AFTER_ XMLTYPE
)

триггер на основной

create or replace trigger t_trg_ar_iud
after insert or update or delete on t
for each row
declare
oper_ varchar2(1);
begin
oper_ := case when inserting then 'I'
when updating then 'U'
when deleting then 'D'
end;

insert into t_hist
select :new.id as id, :new.txt as txt, :new.d as d
, user as who_, sysdate as when_
, oper_ as what_
, ( select xmlelement( «row», xmlforest(:old.id as id, :old.txt as txt, :old.d as d)) from dual ) as data_before_
, ( select xmlelement( «row», xmlforest(:new.id as id, :new.txt as txt, :new.d as d)) from dual ) as data_after_
from dual;

end t_trg_ar_iud;

Делается триггер на ddl on shema, в котором отлавливаем события и делаем перегенерацию триггера и исторической таблицы.
Идея сырая, придумал только что.

[maovrn]

Встречал такую или похожую реализацию :-). Вернее ее результаты в виде пачки xml файлов, которые мне надо было перегонять обратно в таблицу в другой базе. Так все работает, все логично, один минус — много места на диске требует.