Example of Using Qbim with Linked Objects
Installing the Extension
create extension qbim cascade;
Creating Test Objects
This example considers an implementation with two objects linked to each other.
To describe the relationship, a composite primary key <id, sd, ed> will be used in one table, and a "triplet" <r_position, sd, ed> in the second. Records (versions) will be considered linked if the first field in the specified triples matches, and the intervals created by the remaining two values intersect.
The mechanisms for describing relationships (foreing key) in the temporal paradigm do not yet allow this to be done at the level of specifying table constraints.
Note
But such a link assignment will probably be implemented in future versions of QHB.
The subject area being modeled is personnel records. Two objects are created: "person" and "position". To demonstrate the capabilities of Qbim, the attribute composition of the objects is reduced.
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 'Person';
comment on column person.id is 'Identifier';
comment on column person.sd is 'Version start time';
comment on column person.ed is 'Version end time';
comment on column person.last_name is 'Last name';
comment on column person.first_name is 'First name';
comment on column person.patronym is 'Patronym';
comment on column person.r_position is 'Link to position (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 'Position';
comment on column position.id is 'Identifier';
comment on column position.sd is 'Version start time';
comment on column position.ed is 'Version end time';
comment on column position.position_name is 'Position name';
Description in Object Description Tables
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);
Creating Object Implementations
Примечание
From here on, when calling interface methods, a random user ID '463404e8-ee5d-4597-b9a9-e3b69e29d18a'::uuid is specified, which is used when registering an action.
Positions. We are creating three positions from January 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','Engineer');
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','Senior Engineer');
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','Manager');
Employees. We create employees from the date of their actual hiring, assignment to specific positions:
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','Nachalnikov',
'first_name','Igor',
'patronym','Petrovich',
'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','Polovinkin',
'first_name','Semen',
'patronym','Andreevich',
'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','Ivanov',
'first_name','Petr',
'patronym','Vasilievich',
'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','Malkin',
'first_name','Ivan',
'patronym','Olegovich',
'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','Selivanov',
'first_name','Ilya',
'patronym','Maksimovich',
'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','Vasin',
'first_name','Sergey',
'patronym','Petrovich',
'r_position',1);
Let's output generalized information on the division. As intervals, we display
minimum/maximum interval boundaries. The condition tsrange(p.sd,p.ed) && tsrange(d.sd,d.ed) means the intersection of intervals [p.sd,p.ed) and
[d.sd,d.ed), respectively.
Generalized Information Request
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 "Full name",
d.position_name as "Position"
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;
Result:
| id | sd | ed | Full name | Position |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Nachalnikov Igor Petrovich | Manager |
| 2 | 2021-01-12 00:00:00 | infinity | Polovinkin Semen Andreevich | Senior Engineer |
| 3 | 2021-01-15 00:00:00 | infinity | Ivanov Petr Vasilievich | Engineer |
| 4 | 2021-01-16 00:00:00 | infinity | Malkin Ivan Olegovich | Senior Engineer |
| 5 | 2021-01-17 00:00:00 | infinity | Selivanov Ilya Maksimovich | Engineer |
| 6 | 2021-01-18 00:00:00 | infinity | Vasin Sergey Petrovich | Engineer |
Request for Information "As of Date"
The condition '2021-02-15 00:00:00.0'::timestamp <@ tsrange(t.sd,t.ed) means
that the specified time must be contained within the interval.
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 "Full name",
d.position_name as "Position"
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;
The result looks the same:
| id | sd | ed | Full name | Position |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Nachalnikov Igor Petrovich | Manager |
| 2 | 2021-01-12 00:00:00 | infinity | Polovinkin Semen Andreevich | Senior Engineer |
| 3 | 2021-01-15 00:00:00 | infinity | Ivanov Petr Vasilievich | Engineer |
| 4 | 2021-01-16 00:00:00 | infinity | Malkin Ivan Olegovich | Senior Engineer |
| 5 | 2021-01-17 00:00:00 | infinity | Selivanov Ilya Maksimovich | Engineer |
| 6 | 2021-01-18 00:00:00 | infinity | Vasin Sergey Petrovich | Engineer |
Changes
As of March 1, the employee with id=5 was transferred to another position:
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
);
Let's display snapshots for the dates February 28 and March 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 "Full name",
d.position_name as "Position"
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 "Full name",
d.position_name as "Position"
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;
The results are different. The employee in the first case has one position, and in the second — another.
Result as of February 28:
| id | sd | ed | Full name | Position |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Nachalnikov Igor Petrovich | Manager |
| 2 | 2021-01-12 00:00:00 | infinity | Polovinkin Semen Andreevich | Senior Engineer |
| 3 | 2021-01-15 00:00:00 | infinity | Ivanov Petr Vasilievich | Engineer |
| 4 | 2021-01-16 00:00:00 | infinity | Malkin Ivan Olegovich | Senior Engineer |
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Selivanov Ilya Maksimovich | Engineer |
| 6 | 2021-01-18 00:00:00 | infinity | Vasin Sergey Petrovich | Engineer |
Result as of March 1:
| id | sd | ed | Full name | Position |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Nachalnikov Igor Petrovich | Manager |
| 2 | 2021-01-12 00:00:00 | infinity | Polovinkin Semen Andreevich | Senior Engineer |
| 3 | 2021-01-15 00:00:00 | infinity | Ivanov Petr Vasilievich | Engineer |
| 4 | 2021-01-16 00:00:00 | infinity | Malkin Ivan Olegovich | Senior Engineer |
| 5 | 2021-03-01 00:00:00 | infinity | Selivanov Ilya Maksimovich | Senior Engineer |
| 6 | 2021-01-18 00:00:00 | infinity | Vasin Sergey Petrovich | Engineer |
Let's output generalized information for the department (see Section Generalized Information Request). It is clear that the record corresponding to the transferred employee displays two versions, corresponding to his two different positions:
| id | sd | ed | Full name | Position |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Nachalnikov Igor Petrovich | Manager |
| 2 | 2021-01-12 00:00:00 | infinity | Polovinkin Semen Andreevich | Senior Engineer |
| 3 | 2021-01-15 00:00:00 | infinity | Ivanov Petr Vasilievich | Engineer |
| 4 | 2021-01-16 00:00:00 | infinity | Malkin Ivan Olegovich | Senior Engineer |
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Selivanov Ilya Maksimovich | Engineer |
| 5 | 2021-03-01 00:00:00 | infinity | Selivanov Ilya Maksimovich | Senior Engineer |
| 6 | 2021-01-18 00:00:00 | infinity | Vasin Sergey Petrovich | Engineer |
As of March 15, there has been a change — the position name "Engineer" has been changed to "Junior Engineer":
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','Junior Engineer');
Let's see how the output of the generalized information has changed:
| id | sd | ed | Full name | Position |
|---|---|---|---|---|
| 1 | 2021-01-11 00:00:00 | infinity | Nachalnikov Igor Petrovich | Manager |
| 2 | 2021-01-12 00:00:00 | infinity | Polovinkin Semen Andreevich | Senior Engineer |
| 3 | 2021-01-15 00:00:00 | 2021-03-15 00:00:00 | Ivanov Petr Vasilievich | Engineer |
| 3 | 2021-03-15 00:00:00 | infinity | Ivanov Petr Vasilievich | Junior Engineer |
| 4 | 2021-01-16 00:00:00 | infinity | Malkin Ivan Olegovich | Senior Engineer |
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Selivanov Ilya Maksimovich | Engineer |
| 5 | 2021-03-01 00:00:00 | infinity | Selivanov Ilya Maksimovich | Senior Engineer |
| 6 | 2021-01-18 00:00:00 | 2021-03-15 00:00:00 | Vasin Sergey Petrovich | Engineer |
| 6 | 2021-03-15 00:00:00 | infinity | Vasin Sergey Petrovich | Junior Engineer |
You can see that all employees with the position "Engineer" have new versions with the new position name.
Option to Request Information "As of Date" about a Specific Employee
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 "Full name",
d.position_name as "Position"
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 | Full name | Position |
|---|---|---|---|---|
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Selivanov Ilya Maksimovich | Engineer |
Option to Request Information about a Specific Employee for the Entire Period
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 "Full name",
d.position_name as "Position"
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 | Full name | Position |
|---|---|---|---|---|
| 5 | 2021-01-17 00:00:00 | 2021-03-01 00:00:00 | Selivanov Ilya Maksimovich | Engineer |
| 5 | 2021-03-01 00:00:00 | infinity | Selivanov Ilya Maksimovich | Senior Engineer |
The versions “before” and “after” the transfer to another position are displayed.
Another Option to Request Information about a Specific Employee for the Entire Period
If after changing the position name you transfer an employee with id=6 to
another position:
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);
and request all versions for this employee using the query Option to Request Information about a Specific Employee for the Entire Period,
but with id=6, then the results will display versions corresponding to the
periods "before" and "after" the position name change, as well as the period
"after" the transfer to another position:
| id | sd | ed | Full name | Position |
|---|---|---|---|---|
| 6 | 2021-01-18 00:00:00 | 2021-03-15 00:00:00 | Vasin Sergey Petrovich | Engineer |
| 6 | 2021-03-15 00:00:00 | 2021-03-20 00:00:00 | Vasin Sergey Petrovich | Junior Engineer |
| 6 | 2021-03-20 00:00:00 | infinity | Vasin Sergey Petrovich | Senior Engineer |
Action Registration
Since in the example the object descriptions indicated that it was necessary to log information on actions with objects (field obj.is_logging), all creations and changes of objects specified in the example are reflected in the service table 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 |
The field values are described in Section Description of Versioned Tables.
Note
The fct_dt and pid fields may differ; they are provided in this table as an example.