Управление версиями базы данных без таблиц истории
Я просматривал этот пост для контроля версий таблиц на уровне записей. Я заметил, что архитектура имеет дело с использованием таблиц истории. Однако мой сценарий требует не отката, а извлечения записей во времени. Здесь я попытался с дизайном использовать одну таблицу для управления версиями. Обратите внимание, что это пустые данные таблицы (без ограничений, индексов и т. Д.). Я намерен индексировать на основе идентификатора, так как это включает в себя группирование по колонке.
Например, у меня есть таблица теста, где
id - это идентификатор,
modstamp - это временная метка данных (никогда не ноль)
В дополнение к вышеприведенным столбцам таблица будет содержать столбцы учета
local_modstamp - это отметка времени, в которую запись была обновлена
del_modstamp - это отметка времени, когда запись была удалена
Во время резервного копирования все записи получены из источника и вставлены туда, где записи будут иметь значения local_modstamp = null и del_stamp = null.
id |modstamp |local_modstamp |del_modstamp |
---|---------------------------|---------------|-------------|
1 |2016-08-01 15:35:32 +00:00 | | |
2 |2016-07-29 13:39:45 +00:00 | | |
3 |2016-07-21 10:15:09 +00:00 | | |
Как только записи получены, это сценарии для обработки данных (при условии, что эталонное время [ref_time] является временем, в которое выполняется процесс):
Вставьте как обычно.
Обновление: обновить самую последнюю запись с помощью local_modstamp = ref_time. Затем вставьте новую запись. Запрос будет следующим: обновить набор тестов local_modstamp =, где id =, а local_modstamp не равен нулю, а del_modstamp не равен нулю, вставить в значения теста (...)
Удалить: обновить самую последнюю запись с помощью del_modstamp = ref_time. обновить тестовый набор del_modstamp =, где id = и local_modstamp не равны нулю, а del_modstamp не равны нулю
Целью проекта является получение последних записей, где local_modstamp не равен нулю, а del_modstamp не равен нулю. Тем не менее, я столкнулся с проблемой, где я собираюсь получить момент времени, используя запрос (самый внутренний запрос):
select id, max(modstamp) from test where modstamp <= <ref_time> and (del_modstamp is null || del_modstamp <= <ref_time>) group by id;
Похоже, что я допустил ошибку (не так ли?), Использовав ноль в качестве заполнителя для определения последних записей таблицы. Есть ли способ использовать существующий дизайн для получения записей о времени?
Если нет, я думаю, что вероятное решение состоит в том, чтобы установить local_modstamp к последним записям. Это потребует обновления логики с использованием max(local_modstamp) в случае обновлений. Могу ли я сохранить свою существующую архитектуру для получения данных на определенный момент времени?
Я использую SQL-сервер прямо сейчас, но этот дизайн может быть распространен и на другие продукты баз данных. Я намерен использовать более общий подход для извлечения данных вместо использования хаков, специфичных для разных поставщиков.
1 ответ
Представляем версию нормальной формы. Рассмотрим эту таблицу:
create table Entities(
ID int identity primary key,
S1 [type], -- Static data
Sn [type], -- more static data
V1 [type], -- Volatile data
Vn [type] -- more volatile data
);
Статические данные - это данные, которые не изменяются в течение всего времени существования объекта или не требуют отслеживания. Изменчивые изменения данных и эти изменения должны отслеживаться.
Переместите изменчивые атрибуты в отдельную таблицу:
create table EntityVersions(
ID int not null,
Effective date not null default sysdate(),
Deleted bit not null default 0,
V1 [type],
Vn [type],
constraint PK_EntityVersions primary key( ID, Effective ),
constraint FK_EntityVersionEntity foreign key( ID )
references Entities( ID )
);
Таблица Entities больше не содержит переменные атрибуты.
Операция вставки создает основную запись объекта со статическими данными, генерируя уникальное значение идентификатора. Это значение используется для вставки первой версии с начальными значениями изменчивых данных. Обновление, как правило, ничего не делает с основной таблицей (если только статическое значение не изменяется), а новая версия новых изменчивых данных записывается в таблицу версий. Обратите внимание, что в существующие версии не вносятся изменения, особенно в последнюю или "текущую" версию. Новая версия вставлена, конец операции.
Чтобы "отменить" последнюю версию или любую другую версию, просто удалите эту версию из таблицы версий.
Например, таблица "Сотрудники" со следующими атрибутами:
EmployeeNum, HireDate, FirstName, LastName, PayRate, Dept, PhoneExt
Конечно, EmployeeNum будет статичным вместе с HireDate и FirstName. PhoneExt может меняться время от времени, но нам все равно. Так оно обозначено как статическое. Окончательный дизайн:
Employees_S
===========
EmployeeNum (PK), HireDate, FirstName, PhoneExt
Employees_V
===========
EmployeeNum (PK), Effective (PK), IsDeleted, LastName, PayRate, Dept
1 января 2016 года мы наняли Салли Смит. Статические данные вставляются в Employees_S, создавая значение EmployeeNum, равное 1001. Мы используем это значение также для вставки первой версии.
Employees_S
===========
1001, 2016-01-01, Sally, 12345
Employees_V
===========
1001, 2016-01-01, 0, Smith, 35.00, Eng
1 марта она получает повышение заработной платы:
Employees_S
===========
1001, 2016-01-01, Sally, 12345
Employees_V
===========
1001, 2016-01-01, 0, Smith, 35.00, Eng
1001, 2016-03-01, 0, Smith, 40.00, Eng
1 мая она выходит замуж:
Employees_S
===========
1001, 2016-01-01, Sally, 12345
Employees_V
===========
1001, 2016-01-01, 0, Smith, 35.00, Eng
1001, 2016-03-01, 0, Smith, 40.00, Eng
1001, 2016-05-01, 0, Jones, 40.00, Eng
Обратите внимание, что версии одной и той же сущности, за исключением ограничения на то, что даты вступления в силу не могут быть одинаковыми, полностью независимы друг от друга.
Чтобы увидеть, как выглядит текущее состояние сотрудника 1001, вот запрос:
select s.EmployeeNum, s.HireDate, s.FirstName, v.LastName, v.PayRate, v.Dept, s.PhoneExt
from Employees_S s
join Employees_V v
on v.EmployeeNum = s.EmployeeNum
and v.Effective = ( select Max( Effective )
from Employees_V
where EmployeeNum = v.EmployeeNum
and Effective <= SysDate() )
where s.EmployeeNum = 1001
and v.IsDeleted = 0;
Вот классная часть. Чтобы посмотреть, как выглядел штат сотрудников 1001, скажем, 11 февраля, вот запрос:
select s.EmployeeNum, s.HireDate, s.FirstName, v.LastName, v.PayRate, v.Dept, s.PhoneExt
from Employees_S s
join Employees_V v
on v.EmployeeNum = s.EmployeeNum
and v.Effective = ( select Max( Effective )
from Employees_V
where EmployeeNum = v.EmployeeNum
and Effective <= '2016-02-11' )
where s.EmployeeNum = 1001
and v.IsDeleted = 0;
Это тот же запрос - за исключением последней строки подзапроса. Текущие и исторические данные находятся в одной и той же таблице и запрашиваются одним и тем же оператором.
Вот еще одна интересная особенность. Это 1 июля, и мы знаем, что 1 сентября Салли переходит в отдел маркетинга с очередным повышением зарплаты. Документы уже прошли. Идите вперед и вставьте новые данные:
Employees_S
===========
1001, 2016-01-01, Sally, 12345
Employees_V
===========
1001, 2016-01-01, 0, Smith, 35.00, Eng
1001, 2016-03-01, 0, Smith, 40.00, Eng
1001, 2016-05-01, 0, Jones, 40.00, Eng
1001, 2016-09-01, 0, Jones, 50.00, Mkt
Ближайшая к последней версии по-прежнему будет отображаться как текущая версия, но первый запрос, выполненный 1 сентября или позднее, покажет маркетинговые данные.
Вот слайды презентации, которую я несколько раз делал на технических ярмарках. Он содержит более подробную информацию о том, как все вышеперечисленное может быть сделано, включая запросы. И вот документ, который входит в гораздо более подробно.