Qbim Extension, Example of Implementation of Bitemporal Data Model
Background
The data representation model is close to sixth normal form, but without excessive normalization.
Sixth normal form was introduced by Christopher Date as a generalization of fifth normal form for a temporal database in his book An Introduction to Database Systems.
The Qbim extension is a constructor for developing ERM on a temporal basis, i.e. the objects being modeled are represented as time slices, sequences of their states in time. The main attention and emphasis in this extension is not on "decomposition to the end", but on maintaining the data temporality. The extension implements a short set of logic for storing (creating, changing) objects in a relational DBMS (using QHB as an example), implemented in the stored procedure layer.
Data Model
The Qbim extension provides an example of implementing a data storage model with specifying of their validity time intervals. Unlike non-chronological databases that store only current data, such a model assumes the storage of data related to past as well as future time periods.
In the available implementation, along with the data tuple identifier, the time interval during which this data is considered relevant is also specified. These intervals may relate to or intersect with the present time, be in the past, or describe future values. The intervals are stored in two specially allocated fields of the timestamp type, forming a half-closed interval of the tsrange range type.
A data series with a single identifier (hereinafter object) can form non- intersecting segments, or "versions", on the "action" time axis of this data (business time).
An additional time axis (system time) records when these versions were created or changed.
Model Implementation
Terminology
| Term | Definition |
|---|---|
| Object | Some digitalized entity modeled from the real world. It has properties (attributes) and operations on them (methods). In certain time intervals, it has a fixed set of attributes. But over time, from interval to interval, it can change the set of attributes. In a database, a set of objects is represented by an entity/table, its attributes are represented by table fields, and its methods are implemented by stored procedures and functions. |
| Object implementation | One of the object representatives, distinguished from others by an identifier. In a database, it is a series of records of one table/entity. |
| Object implementation version | One of the sets of attributes of an object, constant during a certain time interval. In a database, one of the records of a table/entity belonging to an object (by identifier), with a certain interval of validity. |
| Object implementation identifier | An identifier, id (usually an integer, but can be of type UUID), that uniquely identifies an object implementation among other entity objects. All versions of the same object have the same identifier. |
| Object implementation version identifier | A tuple of version identifier and validity interval ( <id[, sd, ed]> ) that uniquely identifies the object and version |
Caveats and Defaults
Reserved Fields
Three fields are reserved for the identifier and the boundaries of the validity interval in versioned tables:
| Reserved Field | Type | Purpose |
|---|---|---|
| id | bigint or uuid | identifier |
| sd | timestamp | start date, start of validity period |
| ed | timestamp | end date, end of validity period |
Then, the term "local identifier" may be used for bigint, and "global identifier" for uuid.
Any versioned object is represented by a set of its implementation versions, linked by a unique implementation identifier (id) and a set of attributes/properties. Each version is represented by a tuple id and the period of validity of this version (the period is represented by an interval [sd, ed), with the left boundary included and the right boundary excluded, boundary types are timestamp).
Primary key of the object version is composite: <id,[sd,ed)>.
Versions do Not Overlap and do Not Contain Gaps.
The intervals of the versions are formed as tsrange(sd,ed,'[)') — a half-open
interval. The absence of interval overlapping is implemented by a constraint of
the form:
constraint ... exclude using gist (id with =, tsrange(sd, ed) with &&) where ((sd <> ed))
It should be noted that under such conditions, versions in which sd == ed are
acceptable.
This assumption is made intentionally because:
- such versions have zero length, do not overlap with other versions, do not affect many scenarios of selecting versions and version series (non-empty);
- if there is a change timestamp (system time), the link to the event version (action.link_dt field) does not disappear and does not require changing, since the link is performed specifically to the value of the sd field of the versioned table.
Description of Versioned Tables
All versioned tables and their fields are described in specially allocated tables obj and obj_item.
Table obj:
| Field | Type | Purpose |
|---|---|---|
| id | bigserial | Identifier |
| tab | text | The name of the table where the object is stored. Can be schema-qualified. |
| dsc | text | Description |
| parent | bigint | Reference to the ID of the top object in the hierarchy |
| is_temporal | bool | Object temporality flag |
| is_logging | bool | User action logging flag for an object |
| is_global | bool | Global identifier flag for an object. For global identifiers, the UUID type is used, for local identifiers — bigint |
Table obj_item:
| Field | Type | Purpose |
|---|---|---|
| id | bigserial | Identifier |
| up | bigint | Object reference (obj.id) |
| ord | bigint | Attribute ordinal number |
| name | text | Name |
| r_cls | bigint | If there is a dictionary field, the dictionary number (@CLS.ID) |
| r_obj | bigint | If there is an object reference, the object number |
| option | jsonb | Additional parameters. For example, specifying to replace the type with an array for methods |
| dsc | text | Attribute description |
Table action:
| Field | Type | Purpose |
|---|---|---|
| id | bigserial | Identifier |
| r_object | uref | A reference to the object being changed and its identifier |
| lnk_dt | timestamp | Date of linking to the event implementation version (SD) |
| fct_dt | timestamp | Real version date |
| action | bigint | The action performed. Dictionary |
| database | text | The database where the changed object is contained |
| schema | text | The diagram where changed object is contained |
| dbuser | text | The database user who performed the action |
| pid | text | The process identifier (PID) within which the change was made |
| userid | uuid | UUID of the user who logged in and performed the action |
In the action table, if specified in obj.is_logging, the reasons for actions with objects are recorded.
The "Action" (field action) has the following encoding
| Code | Action |
|---|---|
| 1 | Creation |
| 2 | Change (when creating a new version) |
| 3 | Change (when the validity period is shortened) |
| 4 | Closing the version |
ATTENTION!
For the application to function, it is important that entries in this table are written as quickly as possible. To do this, you can create the table as unlogged, and with TARQ enabled — also ... using append_only.
Methods for Describing Objects
The qbim_describe_object(...) function is provided to describe objects in the obj and obj_item tables.
Function parameters:
| Parameter | Type | Default | Purpose |
|---|---|---|---|
| i_table_name | text | The name of the object (table), with or without a schema name. In the absence of an explicit schema name, the table is searched in the schemas specified in the search_path parameter. | |
| i_obj_id | bigint | Object number. If you specify NULL, the number will be assigned automatically from the sequence for obj.id | |
| i_parent_obj_id | bigint | NULL | The parent object number if an object hierarchy is being created. |
| i_temporal | bool | true | An attribute of a versioned object (temporality). Otherwise, it is unversioned, such objects can also be described in object tables. |
| i_logging | bool | true | Flag for logging changes in the action registration table action |
| i_global | bool | false | The "global" attribute of an object instance identifier (type uuid), otherwise the identifier is "local" (type bigint/int8). |
The description uses comments for the table and its fields.
When filling in the description of the object fields, the comment can use templates of links to dictionaries and related objects (fields r_cls and r_obj in the table obj_item). The templates are CLS#[[:digit:]] and OBJ#[[:digit:]], respectively.
For example, the following comment:
COMMENT ON COLUMN object_tbl.r_domain IS 'Domain link. OBJ#22';
when describing an object, will fill in obj_item for this field with information that it is a reference to another object:
| id | up | ord | name | r_cls | r_obj | option | dsc |
|---|---|---|---|---|---|---|---|
| ... | ... | ... | r_domain | ... | 22 | ... | Domain link. OBJ#22 |
and the comment:
COMMENT ON COLUMN object_tbl.model_status IS 'Cognitive model status. CLS#18';
when describing an object, fill in obj_item for this field with information that the field is a dictionary:
| id | up | ord | name | r_cls | r_obj | option | dsc |
|---|---|---|---|---|---|---|---|
| ... | ... | ... | model_status | 18 | ... | ... | Cognitive model status. CLS#18 |
Methods of Working with Objects
Methods are provided for creating, changing, and closing objects that correctly track model constraints.
The selection methods are left to the user or application to choose. It is
possible to select a specific version in effect at a given time, or a series of
versions over a given period, with or without collapsed versions (intermediate
versions with zero duration, sd == ed). Various selections are also possible,
together with related objects.
Object Creating Methods
To create an object (the initial version of the object), the qbim_create() methods are provided:
CREATE FUNCTION qbim_create(
obj_id int8, tab text,
id int8,
sd timestamp, ed timestamp,
userid uuid,
variadic others_args "any")
returns int8 as 'MODULE_PATHNAME','create_local_reg' language c volatile security definer;
CREATE FUNCTION qbim_create(
obj_id int8, tab text,
id uuid,
sd timestamp, ed timestamp,
userid uuid,
variadic others_args "any")
returns uuid as 'MODULE_PATHNAME','create_global_reg' language c volatile security definer;
One of the first two parameters of the method specifies the created object ID: by identifier in the obj descriptor table or by the name of the object (table); it is possible to specify both the identifier and the name, but they must match the descriptor.
Depending on the type of ID used in the object, the third parameter may be of
type bigint or uuid; omitting it (specifying null::bigint or null::uuid)
requires automatic generation of the ID. In the case of bigint, the value may
be chosen from the corresponding sequence, and in the case of uuid, a random
uuid value is generated.
The next pair of parameters specifies the interval of the version validity; if
omitted (specifying null:timestamp), the defaults specified when the
table was created are used. As a rule, for sd this is the current time
(CURRENT_TIMESTAMP), and for ed it is "infinity" (infintity::timestamp).
The userid parameter can be used to specify the end user ID of the user
performing the object creation operation, if used in the application. If not
used, it can be omitted (specify null::uuid).
Then, in the variable parameter list (variadic "any"), you should pass parameters in pairs — name and value, or not pass them at all. In any case, the number of variables must be even. Object attributes that are omitted from this list will receive NULL values or will be set in accordance with the defaults
When an object is created, the action will be recorded in the action table, if specified in obj.
Object Changing Methods (Creating a Version)
To change an object, the qbim_change() methods are provided. A version can be created in any period that overlaps the version periods that the object has (there should be no gaps).
The parameters are similar to the qbim_create() methods. The defaults for sd and ed are the current time and "infinity", respectively.
A new version can complement (from any side) existing ones, overlap any number of existing versions, completely or partially cover existing versions.
If any field from the variable parameter list is omitted, it will be substituted from the corresponding earlier version of the object that matches the time.
If, as a result of the change, some of the existing versions are completely
covered by the new version, they will be considered collapsed (sd == ed).
qbim_change() methods:
CREATE FUNCTION qbim_change(
obj_id int8, tab text,
id int8,
sd timestamp, ed timestamp,
userid uuid,
variadic others_args "any")
returns int8 as 'MODULE_PATHNAME','change_local_reg' language c volatile security definer;
CREATE FUNCTION qbim_change(
obj_id int8, tab text,
id uuid,
sd timestamp, ed timestamp,
userid uuid,
variadic others_args "any")
returns int8 as 'MODULE_PATHNAME','change_global_reg' language c volatile security definer;
When an object is changed, the action will be recorded in the action table, if this is specified in obj.
Object (Version) Closing Methods
A pair of methods that close versions of an object with the current or specified date:
CREATE FUNCTION qbim_close(
obj_id int8, tab text,
id int8,
ed timestamp,
userid uuid)
returns int8 as 'MODULE_PATHNAME','close_local' language c volatile security definer;
CREATE FUNCTION qbim_close(
obj_id int8, tab text,
id uuid,
ed timestamp,
userid uuid)
returns int8 as 'MODULE_PATHNAME','close_global' language c volatile security definer;
The ed parameter specifies the time required to close the versions. If omitted
(null::timestamp is specified), the current time is set. The remaining parameters
are similar to the parameters in the qbim_create and qbim_change methods.
When closing an object, the action will be recorded in the action table, if specified in obj.
Note
For unversioned objects, the description is also available in the obj and obj_item tables, and the methods for creating, changing, and closing objects work with them. For unversioned objects, only one attribute is systemic — the identifier (id). In the corresponding methods, the sd and ed parameters do not make sense and are ignored. The change method implies a simple update of the object instance. The closing method implies deleting an instance of the object. Actions are also registered (in the action table), but the lnk_dt and fct_dt fields are filled with the same value — the time of action registration.
Installing the Extension
The extension is installed using the CREATE EXTENSION command. Since there are extensions on which the Qbim extension depends, it is recommended to use installation with the CASCADE clause. The Qbim extension is portable, so it can be installed into different database schemas using SCHEMA name_schemas.
Starting with the extension version 1.2, it is possible to use various parameters of the action table. A group of local parameters is used to control the creation of this table (see Table below).
When upgrading the extension to version 1.2 from previous versions, use the UPDATE clause of the ALTER EXTENSION command.
Table of Local Parameters to Control Table action Creation
| Parameters | Purpose | Default |
|---|---|---|
| qbim.action.use_partitions | Create a table partitioned by the fct_dt field. | true |
| qbim.action.partition_period | When creating a partitioned table, the size of the partitions that will be created. Possible options are day, week, month, 'year'. | month |
| qbim.action.partition_count | When creating a partitioned table, the number of partitions to create, not counting the two partitions for the "oldest" and "newest" values. | 5 |
| qbim.action.use_unlogged | Create a table with the unlogged parameter. Pay close attention to this parameter, it is better not to use it when working in a cluster. | false |
| qbim.action.use_appendonly | Create table as immutable (append_only). It is only possible when TARQ is enabled. | true |
| qbim.action.upgrade_exists | Migrate data from an existing action table when upgrading from an earlier version of the Qbim to version 1.2. If the extension is installed for the first time, the parameter is useless. | true |
You should set the parameters before calling the CREATE EXTENSION or ALTER EXTENSION command.
Setting examples:
create extension qbim cascade;
set qbim.action.use_partitions='true';
set qbim.action.partition_period='year';
set qbim.action.partition_count=2;
create extension qbim schema my_schema cascade;