ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / История значений - покритикуйте решение
13 сообщений из 13, страница 1 из 1
История значений - покритикуйте решение
    #38735146
Kirill Razuvaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kirill Razuvaev
Участник
Здравствуйте!
Ранее изменения в таблицах учетной системы хранились в единой таблице изменений в формате "ИмяТаблицы-ИмяПоля-СтароеЗначение-НовоеЗначение-Автор-ОтметкаВремени". И, в общем-то, все было хорошо до тех пор, пока не понадобилось иметь возможность посмотреть "слепок" данных на любую дату.
Дано - базовая таблица вида (к примеру - справочник):
 
Код: sql
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. Минимальные изменения в структуре БД, возможность автоматизации процесса (скрипты и т,д.), сохранение бизнес-логики.
Предлагаемое решение:
Дату для "слепка" задать через контекстную переменную.
Исходную таблицу модифицировать:
 
Код: sql
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-ограничений:
 
Код: sql
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 базовой таблицы.
Для выборки данных из таблицы создаем изменяемый просмотр, на который переключаем все обращения к базовой таблице:
 
Код: sql
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
...
Рейтинг: 0 / 0
02.09.2014, 16:03
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735180
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
В спойлеры не заглядывал. У меня историчность делается на трех дополниительных полях таблиц с датами:
begin_date not null, end_date null, sp_end_date not null (последний заполняется триггером исходя из end_date).
Пользователь не нужен пока что. Удалить нельзя, можно только выставить end_date и добавить новую строку с begin_date = (prev)end_date+1.
...
Рейтинг: 0 / 0
02.09.2014, 16:28
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735213
Kirill Razuvaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kirill Razuvaev
Участник
wadmanbegin_date not null, end_date null,А какой смысл в диапазонах, если по смыслу одно значение должно сменять другое? Достаточно же по идее только даты вступления в силу значения. Или за счет этого Вы разрывы (отсутствие данных) в промежутке времени отслуживаете?
...
Рейтинг: 0 / 0
02.09.2014, 16:54
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735215
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
Kirill RazuvaevА какой смысл в диапазонах, если по смыслу одно значение должно сменять другое?
Для индекса по sp_end_date.
...
Рейтинг: 0 / 0
02.09.2014, 16:55
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735220
Kirill Razuvaev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kirill Razuvaev
Участник
wadmanKirill RazuvaevА какой смысл в диапазонах, если по смыслу одно значение должно сменять другое?
Для индекса по sp_end_date.А в чем разница между двумя последними полями?
...
Рейтинг: 0 / 0
02.09.2014, 16:58
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735222
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
Kirill Razuvaevwadmanпропущено...

Для индекса по sp_end_date.А в чем разница между двумя последними полями?
wadmanend_date null, sp_end_date not null (последний заполняется триггером исходя из end_date)
...
Рейтинг: 0 / 0
02.09.2014, 17:00
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735225
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
Честно говоря я и не помню уже деталей и преимуществ этой схемы (три даты вместо одной). Придумал не я, но в свое время погонял её, результатом остался доволен и теперь безусловно её применяю. :)
...
Рейтинг: 0 / 0
02.09.2014, 17:03
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735398
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
Нафига нужен sp_end_date я тоже не понял,
а две даты вместо одной удобны тем, что
позволяют выбрать "конец" сразу, одним
запросом, без доп.запроса (ну или без EB).
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
02.09.2014, 20:03
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735712
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
Гаджимурадов РустамНафига нужен sp_end_date я тоже не понял
Вот так делается:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE TABLE TEST (
    BEGIN_DATE   DATENOTNULL DEFAULT current_date /* DATENOTNULL = DATE NOT NULL */,
    END_DATE     DATENULL /* DATENULL = DATE */,
    SP_END_DATE  DATENOTNULL /* DATENOTNULL = DATE NOT NULL */,
)

CREATE OR ALTER TRIGGER TEST_BU9 FOR TEST
ACTIVE BEFORE INSERT OR UPDATE POSITION 9
as
begin
  new.sp_end_date = coalesce(new.end_date, cast('01.01.3000' as date));
end

CREATE INDEX TEST_IDX_END_DATE ON TEST (SP_END_DATE);



Гаджимурадов Рустама две даты вместо одной удобны тем, что
позволяют выбрать "конец" сразу, одним
запросом, без доп.запроса (ну или без EB).
Верно, без ограничения выборки, если брать до определенной даты и сразу одной строкой. Экономия...
...
Рейтинг: 0 / 0
03.09.2014, 09:17
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735776
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
wadman> Вот так делается

Я не про триггер говорил, он итак
очевиден, а про два идентичных поля.

wadman> Верно, без ограничения выборки, если брать до определенной
wadman> даты и сразу одной строкой. Экономия...

Нет там никакой экономии и пр., любые выборки -
активная, активная на дату, до даты, последняя и
даже предпоследняя, предыдущая и пр. - можно
получить и без второго поля. А само второе поле
нужно именно для *информационных* целей, а
не для фильтра - чтобы когда понадобится не
лезть в таблицу второй раз и не выбирать сразу
2 строки - эдакая мини-денормализация.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
03.09.2014, 10:13
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735802
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
Гаджимурадов РустамЯ не про триггер говорил, он итак
очевиден, а про два идентичных поля.
Уже не помню "очевидных преимуществ", которые поведали об этой схеме. end_date null зачем-то нужен был той системе, где такая схема использовалась, т.к. внутрях используется исключительно sp_end_date и иногда begin_date.

P.S. Не показывать-же пользователю дату 01.01.3000
...
Рейтинг: 0 / 0
03.09.2014, 10:27
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38735807
Фотография wadman
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wadman
Участник
wadmanт.к. у меня внутрях
...
Рейтинг: 0 / 0
03.09.2014, 10:28
| Ответить | Цитировать | Написать  
История значений - покритикуйте решение
    #38736179
Гаджимурадов Рустам
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гаджимурадов Рустам
Участник
wadman> P.S. Не показывать-же пользователю дату 01.01.3000

Это, конечно, от конкретного бизнес-случая зависит,
но пользователю обычно вообще конечную дату не
нужно показывать, а чаще всего - вообще только 1
запись - активная "на сегодня" или "на дату".
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
03.09.2014, 13:58
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / История значений - покритикуйте решение
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&msg=38735712&tid=1563362]:
 0ms
get settings:
 7ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 161ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 60ms
get tp. blocked users:
 1ms
others: 220ms
total:  484ms
созд. / загр.: 484ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]