Пример использования Qbim со связанными объектами
Установка расширения
create extension qbim cascade;
Создание тестовых объектов
В данном примере рассматривается реализация с двумя объектами, связанными между собой.
Для описания связи будет использоваться составной первичный ключ <id, sd, ed> в одной таблице, и «тройка» <r_position, sd, ed> во второй. Записи (версии) будут считаться связанными, если первое поле в указанных тройках совпадает, а интервалы, создаваемые оставшимися двумя значениями, пересекаются.
Механизмы описания связей (foreing key) в темпоральной парадигме пока не позволяют сделать это на уровне задания ограничений таблиц.
Примечание
Но такое задание связи, вероятно, будет реализовано в следующих версиях QHB.
Моделируется предметная область — кадровый учет. Создаются два объекта: «персона» и «должность». Для демонстрации возможностей Qbim атрибутивный состав объектов сокращен.
create table person (
id bigserial not null,
sd timestamp not null default current_timestamp,
ed timestamp not null default 'infinity'::timestamp,
last_name varchar(50) not null,
first_name varchar(50) not null,
patronym varchar(50),
r_position bigint not null,
constraint person_id_sd_ed_excl exclude using gist (id with =, tsrange(sd, ed) with &&) where ((sd <> ed))
);
comment on table person is 'Персона';
comment on column person.id is 'Идентификатор';
comment on column person.sd is 'Время начала действия версии';
comment on column person.ed is 'Время окончания действия версии';
comment on column person.last_name is 'Фамилия';
comment on column person.first_name is 'Имя';
comment on column person.patronym is 'Отчество';
comment on column person.r_position is 'Ссылка на должность (OBJ#2)';
create table position (
id bigserial not null,
sd timestamp not null default current_timestamp,
ed timestamp not null default 'infinity'::timestamp,
position_name varchar(100) not null,
constraint position_id_sd_ed_excl exclude using gist (id with =, tsrange(sd, ed) with &&) where ((sd <> ed))
);
comment on table position is 'Должность';
comment on column position.id is 'Идентификатор';
comment on column position.sd is 'Время начала действия версии';
comment on column position.ed is 'Время окончания действия версии';
comment on column position.position_name is 'Название доллжности';
Описание в таблицах описания объектов
select qbim_describe_object(i_table_name => 'person'::text, i_obj_id => 1::bigint);
select qbim_describe_object(i_table_name => 'position'::text, i_obj_id => 2::bigint);
select setval('obj_id_seq',2);
Создание реализаций объектов
Примечание
Здесь и далее при вызове интерфейсных методов указывается случайный идентификатор пользователя '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid, который используется при регистрации действия.
Должности. Создаем три должности с 1 января 2021 года:
select qbim_create(2::bigint, null::text, null::bigint,
'2021-01-01 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'position_name','Инженер');
select qbim_create(2::bigint, null::text, null::bigint,
'2021-01-01 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'position_name','Старший инженер');
select qbim_create(2::bigint, null::text, null::bigint,
'2021-01-01 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'position_name','Менеджер');
Сотрудники. Создаем сотрудников с даты их фактического приема в штат, назначения на конкретные должности:
select qbim_create(1::bigint, null::text, null::bigint,
'2021-01-11 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'last_name','Начальников',
'first_name','Игорь',
'patronym','Петрович',
'r_position',3);
select qbim_create(1::bigint, null::text, null::bigint,
'2021-01-12 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'last_name','Половинкин',
'first_name','Семен',
'patronym','Андреевич',
'r_position',2);
select qbim_create(1::bigint, null::text, null::bigint,
'2021-01-15 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'last_name','Иванов',
'first_name','Петр',
'patronym','Васильевич',
'r_position',1);
select qbim_create(1::bigint, null::text, null::bigint,
'2021-01-16 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'last_name','Малкин',
'first_name','Иван',
'patronym','Олегович',
'r_position',2);
select qbim_create(1::bigint, null::text, null::bigint,
'2021-01-17 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'last_name','Селиванов',
'first_name','Илья',
'patronym','Максимович',
'r_position',1);
select qbim_create(1::bigint, null::text, null::bigint,
'2021-01-18 00:00:00.0'::timestamp, null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'last_name','Васин',
'first_name','Сергей',
'patronym','Петрович',
'r_position',1);
Выведем обобщенную информацию по подразделению. В качестве интервалов выводим
минимальные/максимальные границы интервалов. Условие tsrange(p.sd,p.ed) && tsrange(d.sd,d.ed) означает пересечение интервалов [p.sd,p.ed) и [d.sd,d.ed)
соответственно.
Запрос обобщенной информации
select
p.id,
greatest(p.sd,d.sd) as sd,
least(p.ed,d.ed) as ed,
p.last_name||' '||p.first_name||' '||p.patronym as "ФИО",
d.position_name as "Должность"
from person p join position d
on p.r_position = d.id and tsrange(p.sd,p.ed) && tsrange(d.sd,d.ed)
order by 1,2;
Результат:
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Начальников Игорь Петрович | Менеджер |
| 2 | 2021-01-12 00:00:00 | infinity | Половинкин Семен Андреевич | Старший инженер |
| 3 | 2021-01-15 00:00:00 | infinity | Иванов Петр Васильевич | Инженер |
| 4 | 2021-01-16 00:00:00 | infinity | Малкин Иван Олегович | Старший инженер |
| 5 | 2021-01-17 00:00:00 | infinity | Селиванов Илья Максимович | Инженер |
| 6 | 2021-01-18 00:00:00 | infinity | Васин Сергей Петрович | Инженер |
Запрос информации «на дату»
Условие '2021-02-15 00:00:00.0'::timestamp <@ tsrange(t.sd,t.ed) означает, что
указанный момент времени должен содержаться внутри интервала.
select * from (
select
p.id,
greatest(p.sd,d.sd) as sd,
least(p.ed,d.ed) as ed,
p.last_name||' '||p.first_name||' '||p.patronym as "ФИО",
d.position_name as "Должность"
from person p join position d
on p.r_position = d.id and tsrange(p.sd,p.ed) && tsrange(d.sd,d.ed)
) t
where '2021-02-15 00:00:00.0'::timestamp <@ tsrange(t.sd,t.ed)
order by 1,2;
Результат выглядит одинаково:
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Начальников Игорь Петрович | Менеджер |
| 2 | 2021-01-12 00:00:00 | infinity | Половинкин Семен Андреевич | Старший инженер |
| 3 | 2021-01-15 00:00:00 | infinity | Иванов Петр Васильевич | Инженер |
| 4 | 2021-01-16 00:00:00 | infinity | Малкин Иван Олегович | Старший инженер |
| 5 | 2021-01-17 00:00:00 | infinity | Селиванов Илья Максимович | Инженер |
| 6 | 2021-01-18 00:00:00 | infinity | Васин Сергей Петрович | Инженер |
Изменения
С 1 марта сотрудник с id=5 был переведен на другую должность:
select qbim_change(1::bigint,null::text,5::bigint,
'2021-03-01 00:00:00.0'::timestamp,null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'r_position',2::bigint
);
Выведем моментальные снимки на даты 28 февраля и 1 марта:
select * from (
select
p.id,
greatest(p.sd,d.sd) as sd,
least(p.ed,d.ed) as ed,
p.last_name||' '||p.first_name||' '||p.patronym as "ФИО",
d.position_name as "Должность"
from person p join position d
on p.r_position = d.id and tsrange(p.sd,p.ed) && tsrange(d.sd,d.ed)
) t
where '2021-02-28 00:00:00.0'::timestamp <@ tsrange(t.sd,t.ed)
order by 1,2;
select * from (
select
p.id,
greatest(p.sd,d.sd) as sd,
least(p.ed,d.ed) as ed,
p.last_name||' '||p.first_name||' '||p.patronym as "ФИО",
d.position_name as "Должность"
from person p join position d
on p.r_position = d.id and tsrange(p.sd,p.ed) && tsrange(d.sd,d.ed)
) t
where '2021-03-01 00:00:00.0'::timestamp <@ tsrange(t.sd,t.ed)
order by 1,2;
Результаты различаются. Сотрудник в первом случае имеет одну должность, а во втором — другую.
Результат на 28 февраля:
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Начальников Игорь Петрович | Менеджер |
| 2 | 2021-01-12 00:00:00 | infinity | Половинкин Семен Андреевич | Старший инженер |
| 3 | 2021-01-15 00:00:00 | infinity | Иванов Петр Васильевич | Инженер |
| 4 | 2021-01-16 00:00:00 | infinity | Малкин Иван Олегович | Старший инженер |
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Селиванов Илья Максимович | Инженер |
| 6 | 2021-01-18 00:00:00 | infinity | Васин Сергей Петрович | Инженер |
Результат на 1 марта:
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Начальников Игорь Петрович | Менеджер |
| 2 | 2021-01-12 00:00:00 | infinity | Половинкин Семен Андреевич | Старший инженер |
| 3 | 2021-01-15 00:00:00 | infinity | Иванов Петр Васильевич | Инженер |
| 4 | 2021-01-16 00:00:00 | infinity | Малкин Иван Олегович | Старший инженер |
| 5 | 2021-03-01 00:00:00 | infinity | Селиванов Илья Максимович | Старший инженер |
| 6 | 2021-01-18 00:00:00 | infinity | Васин Сергей Петрович | Инженер |
Выведем обобщенную информацию по подразделению (см. раздел Запрос обобщенной информации). Видно, что у записи, соответствующей переведенному сотруднику, выводится две версии, соответствующие его двум различным должностям:
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Начальников Игорь Петрович | Менеджер |
| 2 | 2021-01-12 00:00:00 | infinity | Половинкин Семен Андреевич | Старший инженер |
| 3 | 2021-01-15 00:00:00 | infinity | Иванов Петр Васильевич | Инженер |
| 4 | 2021-01-16 00:00:00 | infinity | Малкин Иван Олегович | Старший инженер |
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Селиванов Илья Максимович | Инженер |
| 5 | 2021-03-01 00:00:00 | infinity | Селиванов Илья Максимович | Старший инженер |
| 6 | 2021-01-18 00:00:00 | infinity | Васин Сергей Петрович | Инженер |
С 15 марта произошло изменение — название должности «Инженер» изменили на «Младший инженер»:
select qbim_change(2::bigint,null::text,1::bigint,
'2021-03-15 00:00:00.0'::timestamp,null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'position_name','Младший инженер');
Посмотрим, как изменился вывод обобщенной информации:
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Начальников Игорь Петрович | Менеджер |
| 2 | 2021-01-12 00:00:00 | infinity | Половинкин Семен Андреевич | Старший инженер |
| 3 | 2021-01-15 00:00:00 | 2021-03-15 00:00:00 | Иванов Петр Васильевич | Инженер |
| 3 | 2021-03-15 00:00:00 | infinity | Иванов Петр Васильевич | Младший инженер |
| 4 | 2021-01-16 00:00:00 | infinity | Малкин Иван Олегович | Старший инженер |
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Селиванов Илья Максимович | Инженер |
| 5 | 2021-03-01 00:00:00 | infinity | Селиванов Илья Максимович | Старший инженер |
| 6 | 2021-01-18 00:00:00 | 2021-03-15 00:00:00 | Васин Сергей Петрович | Инженер |
| 6 | 2021-03-15 00:00:00 | infinity | Васин Сергей Петрович | Младший инженер |
Видно, что у всех сотрудников, имеющих должность «Инженер», появились новые версии с новым названием должности.
Вариант запроса информации «на дату» для конкретного сотрудника
select
p.id,
greatest(p.sd,d.sd) as sd,
least(p.ed,d.ed) as ed,
p.last_name||' '||p.first_name||' '||p.patronym as "ФИО",
d.position_name as "Должность"
from person p join position d
on p.r_position = d.id
where p.id=5 and '2021-02-28 00:00:00.0'::timestamp <@ tsrange(p.sd,p.ed)
and '2021-02-28 00:00:00.0'::timestamp <@ tsrange(d.sd,d.ed)
order by 1,2;
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Селиванов Илья Максимович | Инженер |
Вариант запроса информации для конкретного сотрудника за весь период
select
p.id,
greatest(p.sd,d.sd) as sd,
least(p.ed,d.ed) as ed,
p.last_name||' '||p.first_name||' '||p.patronym as "ФИО",
d.position_name as "Должность"
from person p join position d
on p.r_position = d.id and tsrange(p.sd,p.ed) && tsrange(d.sd,d.ed)
where p.id=5
order by 1,2;
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Селиванов Илья Максимович | Инженер |
| 5 | 2021-03-01 00:00:00 | infinity | Селиванов Илья Максимович | Старший инженер |
Отражаются версии «до» и «после» перевода на другую должность.
Еще один вариант запроса информации для конкретного сотрудника за весь период
Если уже после смены названия должности перевести сотрудника с id=6 на другую
должность:
select qbim_change(1::bigint,null::text,6::bigint,
'2021-03-20 00:00:00.0'::timestamp,null::timestamp,
'463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid,
'r_position',2::bigint);
и запросить все версии по этому сотруднику с помощью запроса
Вариант запроса информации для конкретного сотрудника за весь период, но с id=6,
то в результате отобразятся версии, соответствующие периодам «до» и «после» смены
названия должности, а также периоду «после» перевода на другую должность:
| id | sd | ed | ФИО | Должность |
|---|---|---|---|---|
| 6 | 2021-01-18 00:00:00 | 2021-03-15 00:00:00 | Васин Сергей Петрович | Инженер |
| 6 | 2021-03-15 00:00:00 | 2021-03-20 00:00:00 | Васин Сергей Петрович | Младший инженер |
| 6 | 2021-03-20 00:00:00 | infinity | Васин Сергей Петрович | Старший инженер |
Регистрация действий
Поскольку в примере при описании объектов указывалось, что необходимо протоколировать информацию по действиям с объектами (поле obj.is_logging), все указанные в примере создания и изменения объектов отражаются в служебной таблице action.
select
r_object, lnk_dt, fct_dt, action, pid, userid
from action;
| r_object | lnk_dt | fct_dt | action | pid | userid |
|---|---|---|---|---|---|
| 2:1 | 2021-01-01 00:00:00 | 2021-04-30 19:43:24.586786 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 2:2 | 2021-01-01 00:00:00 | 2021-04-30 19:43:27.352193 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 2:3 | 2021-01-01 00:00:00 | 2021-04-30 19:44:10.525477 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:1 | 2021-01-11 00:00:00 | 2021-04-30 19:55:28.109557 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:2 | 2021-01-12 00:00:00 | 2021-04-30 19:55:30.363525 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:3 | 2021-01-15 00:00:00 | 2021-04-30 19:55:32.670876 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:4 | 2021-01-16 00:00:00 | 2021-04-30 19:55:34.283938 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:5 | 2021-01-17 00:00:00 | 2021-04-30 19:55:39.770958 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:6 | 2021-01-18 00:00:00 | 2021-04-30 19:55:42.7058 | 1 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:5 | 2021-01-17 00:00:00 | 2021-04-30 20:43:03.597479 | 3 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:5 | 2021-03-01 00:00:00 | 2021-04-30 20:43:03.597673 | 2 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 2:1 | 2021-01-01 00:00:00 | 2021-04-30 20:56:12.488844 | 3 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 2:1 | 2021-03-15 00:00:00 | 2021-04-30 20:56:12.489592 | 2 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:6 | 2021-01-18 00:00:00 | 2021-04-30 21:38:37.966772 | 3 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
| 1:6 | 2021-03-20 00:00:00 | 2021-04-30 21:38:37.966962 | 2 | 2521 | 463404e8-ee5d-4597-b9a9-e3b69e29d18a |
Значения полей описаны в разделе Описание версионируемых таблиц.
Примечание
Поля fct_dt и pid могут отличаться; в данной таблице они приведены как пример.