An example of a simple versioning of PostgreSQL entries
Once I saw the implementation of the “history of records” - versioning, on the side of the program working with the SQL database. Before changing the record, the old version was obtained from the database, recorded in XML, and the resulting XML string was recorded in a separate version table.
Initially, I planned to do versioning in my program sometime later, there was no urgent need. I remember there was a desire to use the jsonb data type somewhere, as soon as I thought of a simple and concise implementation of versioning on the SQL side, I could not do it. Only one version table with 5 columns and one trigger function in 3 lines of code.
To describe the implementation of a single version table, it is not enough, so you will need to describe a few more tables for example.
')
In almost all databases, with rare exceptions, there is a table users - users. The history of changes - user versions is useful to store, for example, to be able to roll back to the old version, by the user himself.
Example user table:
The last two fields in the picture are needed for the version table, you can also call them "version author" and "version date", but if you prefer, you can do without them.
Version table:
Trigger function to save versions:
The first two fields are filled in from the saved record OLD.changestamp and OLD.userid.
In the version table, not only the users table records can be stored, the third MD5 field hash of the name of the versioned table converted to uuid.
The examples previously described a very simple structure, but, as a rule, various reference data may have additional tables with a one-to-many relationship.
For example, the “User Groups” table.
And the second table "Users of groups", the group - the users included in the group.
In order not to complicate the simple versioning mechanism, you can make a small duplication of data in the group table, add a jsonb field that follows the structure of the "Users group" table.
To simplify work with duplicated data, you can make an additional trigger function, with INSERT or UPDATE, populate the table “Users of groups” from the jsonb field.
The duplication described above is only necessary when it is necessary to obtain data from a table frequently and as quickly as possible. For example, if you frequently make a query to the "Users group" table to determine if the user is in the Administrators group. In other cases, the data can be obtained by a query directly from the jsonb field, and not use a duplicate table.
Full sample code by reference
Initially, I planned to do versioning in my program sometime later, there was no urgent need. I remember there was a desire to use the jsonb data type somewhere, as soon as I thought of a simple and concise implementation of versioning on the SQL side, I could not do it. Only one version table with 5 columns and one trigger function in 3 lines of code.
To describe the implementation of a single version table, it is not enough, so you will need to describe a few more tables for example.
')
In almost all databases, with rare exceptions, there is a table users - users. The history of changes - user versions is useful to store, for example, to be able to roll back to the old version, by the user himself.
Example user table:
The last two fields in the picture are needed for the version table, you can also call them "version author" and "version date", but if you prefer, you can do without them.
Version table:
Trigger function to save versions:
The first two fields are filled in from the saved record OLD.changestamp and OLD.userid.
In the version table, not only the users table records can be stored, the third MD5 field hash of the name of the versioned table converted to uuid.
The examples previously described a very simple structure, but, as a rule, various reference data may have additional tables with a one-to-many relationship.
For example, the “User Groups” table.
And the second table "Users of groups", the group - the users included in the group.
In order not to complicate the simple versioning mechanism, you can make a small duplication of data in the group table, add a jsonb field that follows the structure of the "Users group" table.
To simplify work with duplicated data, you can make an additional trigger function, with INSERT or UPDATE, populate the table “Users of groups” from the jsonb field.
The duplication described above is only necessary when it is necessary to obtain data from a table frequently and as quickly as possible. For example, if you frequently make a query to the "Users group" table to determine if the user is in the Administrators group. In other cases, the data can be obtained by a query directly from the jsonb field, and not use a duplicate table.
Full sample code by reference
Source: https://habr.com/ru/post/423399/
All Articles