Пример использования 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 могут отличаться; в данной таблице они приведены как пример.

Copyright © KVANTOM LLC, 2019-2023