Пример использования 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 WITHOUT TIME ZONE, 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)) ); CREATE TABLE position ( id BIGSERIAL NOT NULL, sd TIMESTAMP NOT NULL DEFAULT current_timestamp, ed TIMESTAMP NOT NULL DEFAULT 'infinity'::TIMESTAMP WITHOUT TIME ZONE, position_name VARCHAR(100) NOT NULL, CONSTRAINT position_id_sd_ed_excl exclude USING gist (id WITH =, tsrange(sd, ed) WITH &&) WHERE ((sd <> ed)) );

Описание в таблицах описания объектов

INSERT INTO obj (id,tab,dsc,parent,is_temporal,is_logging,is_global) VALUES (1,'person','Персона',NULL,true,true,false); INSERT INTO obj (id,tab,dsc,parent,is_temporal,is_logging,is_global) VALUES (2,'position','Должность',NULL,true,true,false); INSERT INTO obj_item (up,ord,"name",dsc) VALUES (1,1,'id','идентификатор'), (1,2,'sd','время начала действия версии'), (1,3,'ed','время окончания действия версии'), (1,4,'last_name','Фамилия'), (1,5,'first_name','Имя'), (1,6,'patronym','Отчество'), (1,7,'r_position','Ссылка на должность'); INSERT INTO obj_item (up,ord,"name",dsc) VALUES (2,1,'id','идентификатор'), (2,2,'sd','время начала действия версии'), (2,3,'ed','время окончания действия версии'), (2,4,'position_name','Название доллжности');

Создание реализаций объектов

Примечание
Здесь и далее при вызове интерфейсных методов указывается случайный идентификатор пользователя '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;

Результат:

idsdedФИОДолжность
12021-01-11 00:00:00infinityНачальников Игорь ПетровичМенеджер
22021-01-12 00:00:00infinityПоловинкин Семен АндреевичСтарший инженер
32021-01-15 00:00:00infinityИванов Петр ВасильевичИнженер
42021-01-16 00:00:00infinityМалкин Иван ОлеговичСтарший инженер
52021-01-17 00:00:00infinityСеливанов Илья МаксимовичИнженер
62021-01-18 00:00:00infinityВасин Сергей ПетровичИнженер

Запрос информации "на дату"

Условие '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;

Результат выглядит одинаково:

idsdedФИОДолжность
12021-01-11 00:00:00infinityНачальников Игорь ПетровичМенеджер
22021-01-12 00:00:00infinityПоловинкин Семен АндреевичСтарший инженер
32021-01-15 00:00:00infinityИванов Петр ВасильевичИнженер
42021-01-16 00:00:00infinityМалкин Иван ОлеговичСтарший инженер
52021-01-17 00:00:00infinityСеливанов Илья МаксимовичИнженер
62021-01-18 00:00:00infinityВасин Сергей ПетровичИнженер

Изменения

С 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 февраля:

idsdedФИОДолжность
12021-01-11 00:00:00infinityНачальников Игорь ПетровичМенеджер
22021-01-12 00:00:00infinityПоловинкин Семен АндреевичСтарший инженер
32021-01-15 00:00:00infinityИванов Петр ВасильевичИнженер
42021-01-16 00:00:00infinityМалкин Иван ОлеговичСтарший инженер
52021-01-17 00:00:002021-03-01 00:00:00Селиванов Илья МаксимовичИнженер
62021-01-18 00:00:00infinityВасин Сергей ПетровичИнженер

Результат на 1 марта:

idsdedФИОДолжность
12021-01-11 00:00:00infinityНачальников Игорь ПетровичМенеджер
22021-01-12 00:00:00infinityПоловинкин Семен АндреевичСтарший инженер
32021-01-15 00:00:00infinityИванов Петр ВасильевичИнженер
42021-01-16 00:00:00infinityМалкин Иван ОлеговичСтарший инженер
52021-03-01 00:00:00infinityСеливанов Илья МаксимовичСтарший инженер
62021-01-18 00:00:00infinityВасин Сергей ПетровичИнженер

Выведем обобщенную информацию по подразделению (см. раздел Запрос обобщенной информации). Видно, что у записи, соответствующей переведенному сотруднику, выводится две версии, соответствующие его двум различным должностям:

idsdedФИОДолжность
12021-01-11 00:00:00infinityНачальников Игорь ПетровичМенеджер
22021-01-12 00:00:00infinityПоловинкин Семен АндреевичСтарший инженер
32021-01-15 00:00:00infinityИванов Петр ВасильевичИнженер
42021-01-16 00:00:00infinityМалкин Иван ОлеговичСтарший инженер
52021-01-17 00:00:002021-03-01 00:00:00Селиванов Илья МаксимовичИнженер
52021-03-01 00:00:00infinityСеливанов Илья МаксимовичСтарший инженер
62021-01-18 00:00:00infinityВасин Сергей ПетровичИнженер

С 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','Младший инженер');

Посмотрим, как изменился вывод обобщенной информации:

idsdedФИОДолжность
12021-01-11 00:00:00infinityНачальников Игорь ПетровичМенеджер
22021-01-12 00:00:00infinityПоловинкин Семен АндреевичСтарший инженер
32021-01-15 00:00:002021-03-15 00:00:00Иванов Петр ВасильевичИнженер
32021-03-15 00:00:00infinityИванов Петр ВасильевичМладший инженер
42021-01-16 00:00:00infinityМалкин Иван ОлеговичСтарший инженер
52021-01-17 00:00:002021-03-01 00:00:00Селиванов Илья МаксимовичИнженер
52021-03-01 00:00:00infinityСеливанов Илья МаксимовичСтарший инженер
62021-01-18 00:00:002021-03-15 00:00:00Васин Сергей ПетровичИнженер
62021-03-15 00:00:00infinityВасин Сергей ПетровичМладший инженер

Видно, что у всех сотрудников, имеющих должность «Инженер», появились новые версии с новым названием должности.


Вариант запроса информации «на дату» для конкретного сотрудника

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;
idsdedФИОДолжность
52021-01-17 00:00:002021-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;
idsdedФИОДолжность
52021-01-17 00:00:002021-03-01 00:00:00Селиванов Илья МаксимовичИнженер
52021-03-01 00:00:00infinityСеливанов Илья МаксимовичСтарший инженер

Отражаются версии «до» и «после» перевода на другую должность.


Еще один вариант запроса информации для конкретного сотрудника за весь период

Если уже после смены названия должности перевести сотрудника с 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, то в результате отобразятся версии, соответствующие периодам «до» и «после» смены названия должности, а также периоду «после» перевода на другую должность:

idsdedФИОДолжность
62021-01-18 00:00:002021-03-15 00:00:00Васин Сергей ПетровичИнженер
62021-03-15 00:00:002021-03-20 00:00:00Васин Сергей ПетровичМладший инженер
62021-03-20 00:00:00infinityВасин Сергей ПетровичСтарший инженер

Регистрация действий

Поскольку в примере при описании объектов указывалось, что необходимо логировать информацию по действиям с объектами (поле obj.is_logging), все указанные в примере создания и изменения объектов отражаются в служебной таблице action.

SELECT r_object, lnk_dt, fct_dt, action, pid, userid FROM action;
r_objectlnk_dtfct_dtactionpiduserid
2:12021-01-01 00:00:002021-04-30 19:43:24.58678612521463404e8-ee5d-4597-b9a9-e3b69e29d18a
2:22021-01-01 00:00:002021-04-30 19:43:27.35219312521463404e8-ee5d-4597-b9a9-e3b69e29d18a
2:32021-01-01 00:00:002021-04-30 19:44:10.52547712521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:12021-01-11 00:00:002021-04-30 19:55:28.10955712521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:22021-01-12 00:00:002021-04-30 19:55:30.36352512521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:32021-01-15 00:00:002021-04-30 19:55:32.67087612521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:42021-01-16 00:00:002021-04-30 19:55:34.28393812521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:52021-01-17 00:00:002021-04-30 19:55:39.77095812521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:62021-01-18 00:00:002021-04-30 19:55:42.705812521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:52021-01-17 00:00:002021-04-30 20:43:03.59747932521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:52021-03-01 00:00:002021-04-30 20:43:03.59767322521463404e8-ee5d-4597-b9a9-e3b69e29d18a
2:12021-01-01 00:00:002021-04-30 20:56:12.48884432521463404e8-ee5d-4597-b9a9-e3b69e29d18a
2:12021-03-15 00:00:002021-04-30 20:56:12.48959222521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:62021-01-18 00:00:002021-04-30 21:38:37.96677232521463404e8-ee5d-4597-b9a9-e3b69e29d18a
1:62021-03-20 00:00:002021-04-30 21:38:37.96696222521463404e8-ee5d-4597-b9a9-e3b69e29d18a

Значения полей описаны в разделе Описание версионируемых таблиц.

Примечание
Поля fct_dt и pid могут отличаться; в данной таблице они приведены как пример.