Здравствуйте!
Ранее изменения в таблицах учетной системы хранились в единой таблице изменений в формате "ИмяТаблицы-ИмяПоля-СтароеЗначение-НовоеЗначение-Автор-ОтметкаВремени". И, в общем-то, все было хорошо до тех пор, пока не понадобилось иметь возможность посмотреть "слепок" данных на любую дату.
Дано - базовая таблица вида (к примеру - справочник):
1.
2.
3.
4.
5.
6.
7.
create table USERS(ID integer not null primary key,
LOGIN varchar(30) unique,
FULL_NAME varchar(255) unique, // для упрощения
CREATED_AT timestamp,
CREATED_BY varchar(30),
UPDATED_AT timestamp,
UPDATED_BY varchar(30));
Что хочется получить:
1. Возможность просмотра состояния БД на любую дату.
2. Минимальные изменения в структуре БД, возможность автоматизации процесса (скрипты и т,д.), сохранение бизнес-логики.
Предлагаемое решение:
Дату для "слепка" задать через контекстную переменную.
Исходную таблицу модифицировать:
1.
2.
3.
4.
alter table USERS
add is_deleted d_boolean not null default 0,
add deleted_at timestamp,
add deleted_by varchar(30);
Плюс создать для нее новый индекс по IS_DELETED и PK. Создать таблицу истории со структурой, аналогичной базовой таблице, но без вспомогательных полей, unique-ограничений:
1.
2.
3.
4.
5.
6.
create table USERS_H(ID integer not null primary key,
TS timestamp not null,
AUTHOR varchar(30),
LOGIN varchar(30),
FULL_NAME varchar(255),
IS_DELETED d_boolean);
Для эффективного поиска (select first 1 * from... where TS<:DDATE order by TS desc) дополняем ее desc-индкесом по ID и TS.
Значения из базовой таблицы в таблицу истории переносим в триггере AIU базовой таблицы.
Для выборки данных из таблицы создаем изменяемый просмотр, на который переключаем все обращения к базовой таблице:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE OR ALTER VIEW V_USERS(
ID,
LOGIN,
FULL_NAME,
CREATED_AT,
CREATED_BY,
UPDATED_AT,
UPDATED_BY)
AS
select
fs.id,
case when rdb$get_context('USER_SESSION','BASE_DATE_SET')<FS.updated_at then FSH.login else FS.login end,
case when rdb$get_context('USER_SESSION','BASE_DATE_SET')<FS.updated_at then FSH.full_name else FS.full_name end,
FS.created_at,
FS.created_by,
case when rdb$get_context('USER_SESSION','BASE_DATE_SET')<FS.updated_at then FSH.ts else FS.updated_at end,
case when rdb$get_context('USER_SESSION','BASE_DATE_SET')<FS.updated_at then FSH.author else FS.updated_by end
from USERS FS
left join USERS_H FSH on FS.id=FSH.id and (FS.updated_at>rdb$get_context('USER_SESSION','BASE_DATE_SET')) and
(select first 1 FSH1.ts from i_fin_sources_h FSH1
where FSH1.id=FS.id and (rdb$get_context('USER_SESSION','BASE_DATE_SET')>=FSH1.ts)
order by FSH1.ts desc)=FSH.ts
where FS.is_deleted=0
;
Судя по анализу производителтьности, запрос получился довольно удачным и для варианта с заданной "датой слепка" и при не заданной дате. Таким образом, пользователю видны только те значения из базовой таблицы, которые на момент "слепка" не были помечены как удаленные. При удалении данных по команде пользователя проставляется метка удаления. Теоретически можно вообще избавиться от дублирования текущих значений в базовой таблице, но, во-первых, это может негативно сказаться на производительности, а во-вторых - начинаются проблемы с ограничениями бизнес-логики (unique и прочее).
Может, я чего не учел? :-0
