Пример использования 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;

Результат:

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