2.1 Included database functions

Incidentally, you also have the option to install the pgvs environment in the template1 database of the PostgreSQL server. In this case, any newly created database will automatically contain the pgvs environment.

2.1.1 pgvsinit

select * from versions.pgvsinit('<schema>.<table_name>');

The initialization works in 3 steps:

• A view with the name <table_name>_version is created. This has the same structure as the original table.
• It will create some rules and triggers for the new view.
• It will be a meta-table record versions.version_tables added.

After the changes have been saved for the layer, they will be visible - but only for you. For this they are stored in a temporary state, as a memory layer. To make the changes visible to the rest of the world, you must commit your changes to the database.

It is not possible to change the structure of the underlying table. If you want to do this, you must delete the versioning system from the table, as described later. Then you can make your changes. Then you have to initialize the versioning system for the table again.

2.1.2 pgvscommit

The commit command is:

select * from versions.psvscommit('<schema>.<table_name>', '<_log-message_>');

Sometimes it happens that two or more users are working on the same table object. In this case, the pgvscommit () function lists the conflicting records. The conflict objects are not stored in the database. In this case, please contact the other user in the error message to discuss which change should be applied to the database.

2.1.3 pgvsmerge

select * from versions.pgvsmerge('<schema>.<table_name>',<record-id>,'<userrname>');

2.1.4 pgvsdrop

To remove the versioning for a specific table, use the command: select * from versions.pgvsdrop('<table_name>');

This removes all versioning entries from the PostGIS table. You can only delete the versioning of a table if all changes are committed by all users.

You do not have to worry about that, because the command pgvsdrop('<table_name>'); only removes the versioning system. Of course, the Master PostGIS table with all former changes (committs) still exists.

2.1.5 pgvsrevert

select * from versions.pgvsrevert('<table_name>');

2.1.6 pgvsrevision

select * from versions.pgvsrevision();

2.1.7 pgvslogview

select * from versions.pgpslogview ('<import_table_name>', '<versioned_table_name>');

2.1.8 pgvsrollback

select * from versions.pgvsrollback('<table_name>', revision integer);

This feature works reliably from the pgvs db version 1.8.4 and the QGIS plugin version 2.0.2. If there are previous revisions of a layer, they are not ready for rollback.

2.1.9 pgvsincrementalupdate

select * from versions.pgvsincrementalupdate ('<import_table_name>', '<versioned_table_name>');

3. Implementation as PgVersion plugin in QGIS

3.1 Connection to the database

3.2 Layer provided with the versioning system

After confirming this step, another window opens, confirming the initialization and informing you to adjust the user rights for the view if required and the layer for further editing via the functionality of the QGIS pgversion plugin "Loading the versioned layer" to load.

Confirm this message by clicking OK. The layer is now removed from the layer window. The rights described in Chapter 1 have been assigned to the newly versioned layer as DEFAULT PRIVILEGES via the group role versions.

To work with the versioned layer you have to load it again via the plugin tools. Depending on the rights, each user can version tables. However, we recommend leaving this to the administration of the database.

3.3 Loading the versioned layer

Choose your database connection. You will then see the connected users and can select the versioned layer from a list, load it into QGIS and start editing. If a versioned layer is already loaded in QGIS, you will be notified accordingly.

3.4 Commit changes

Now click on the icon "Transfer changes". If no conflicts between your changes and another user's changes have been detected for the edited objects, a dialog opens in which you must enter a log message.

If you do not enter a message, the last commit message below will be used. It is therefore not possible to commit without a commit message. Confirm this dialog with OK. With the icon "Show Logs" you can see the log information of the last commits.

In the event that another user has changed one or more objects that you have also edited, a new window will open, showing the conflicts with the other changes and giving you the option of choosing one version or the other.

To mark the conflict variants of the users of an object from the table below the map, select the line and thus the object variant of a user. This is then highlighted in blue.

You have two options for resolving conflicts.

• You can process the "conflict" candidates one after the other and decide individually which candidate should be accepted.
• You choose to accept all commits of a user.

3.5 Reset to the HEAD revision

In this case, any changes that have been made so far will be removed. To use this feature, select the appropriate layer and click the "Reset to HEAD Revision" icon.

3.6 Show differences

A difference layer is created for the selected map section, which shows you with a green line which geometries have been added and with a red line which geometries have been removed. The difference layers are memory layers and can be easily deleted from the layer window when you no longer need them.

If you want to compare the changes in the attribute table, you can also do so by clicking the object with the QGIS Query Objects tool and Top to Bottom mode. Then the query results display the attributes for visual comparison.

3.7 Show logs

Possible approaches are:

• You select a revision and then click on the "Check out selected revision" button to display it in QGIS.
• If you want to compare a saved but not yet committed change with the current HEAD revision in the database - see also chapter 3.6, you can do this with the icon "Differences to HEAD Revision".
• You select a revision and then click the "Reset to the selected revision" button to make it the current version (HEAD). Previously created revisions are retained.
• You select a revision and click the right mouse button to mark the revision and save it as a "tag". In the picture as example "Completion phase 1 on 28.3.2017". These can now be selected, checked out and viewed in QGIS.

3.8 Delete selected objects directly in the database

3.9 Delete versioning of the level

It is "only" about the versioning environment of the layer. This is necessary the moment you want to change the model of the layers, e.g. add a new attribute column or no longer want to use versioning for the layer.

Activate the corresponding layer and click on "Delete layer versioning" in the menu.

4. Tips for the practice

4.1 Basic settings in QGIS

• Under Settings → Options → Data sources, set the following:
  • Attribute Table Behavior = Show Visible Objects of the Map
  • Attribute table row cache = ~ 200000 (depending on the maximum number of objects in your layers)
• In the Settings → Options → Appearance menu, set the following:
  • Uncheck "Normally, all new layers are displayed"
  • Activate "Use presentation cache"
  • Check "Draw parallel layers with many CPU cores" and specify a maximum number of cores to use.
• In the menu, select Settings → Options → Digitization:
  • Check "Show Snap Options Dialog in a Dock Window". After a one-time QGIS reboot, you can then set the capture mode in the dock window and also specify whether topological functionality should be used when digitizing.

4.2 Historization of a layer with bigint data type of the ID column

First, the following value is determined for the existing data record for the column id:

SELECT MAX(id)+1 FROM "bauleitplanung_nds";

The result (for example: 44151) is then used to create a sequence and assign it to the id column so that it is converted into the serial data type. The following additional steps are necessary for this:

CREATE SEQUENCE bauleitplanung_nds_id_seq start with 44151 owned by "bauleitplanung_nds"."id";

ALTER TABLE bauleitplanung_nds ALTER COLUMN id SET DEFAULT nextval('bauleitplanung_nds_id_seq');

ALTER SEQUENCE bauleitplanung_nds_id_seq OWNED BY "bauleitplanung_nds"."id";

The PgVersion plugin checks when initializing a new layer whether the PrimaryKey data type is "serial" or "bigserial". If this is not the case, the process is aborted with an error.

4.3 Customize existing object geometries of a layer

• Load the historized layer grenzen_bkg via the pgversion plugin and the WMS service WebAtlasde via the tool Add WMS layer.
• Activate the edit mode of the layer borders_bkg, zoom in on an area and flatten the vertices of a line.
• Save the change in QGIS. The modified layer now gets the addition (modified). The change has not yet been returned to the historization.
• Take a look at the difference between the two versions by clicking the PgVersion Show Differences tool. You can now see in red which line area is removed and in green which line area has been added.
• Select the pgversion tool "Pass change" and enter a change description in the Commit window, e.g. "Line course adjusted". Then click OK". The addition (modified) disappears again.
• You can understand this first change by clicking on the "Show Logs" PgVersion tool.

4.4 Customize existing attributes of a layer

• Load the historized layer grenzen_bkg via the PgVersion plugin.
• Activate the editing mode of the layer borders_bkg, open the layer's attribute table and write something in the column "notice".
• Save the change in QGIS. The modified layer now gets the addition (modified). The change has not yet been returned to the historization.
• Take a look at the difference between the two versions by clicking the PgVersion Show Differences tool. If you then select both versions and compare them using a query in the Layer Selection mode, you can understand the changes.
• Next, select the PgVersion "Submit Change" tool and enter a change description in the Commit window, e.g. Note entry in column. Then click OK". The addition (modified) disappears again.
• You can understand this change by clicking on the "Show Logs" PgVersion tool.

4.5 Add a new object to a layer

• Load the historized layer grenzen_bkg via the PgVersion plugin.
• Activate the editing mode of the layer borders_bkg, zoom into an area and add a new borderline anywhere.
• Save the change in QGIS. The modified layer now gets the addition (modified). The change has not yet been returned to the historization.
• Take a look at the difference between the two versions by clicking the PgVersion Show Differences tool. If you then select both versions and compare them using a query in the Layer Selection mode, you can understand the changes.
• Next, select the PgVersion "Submit Change" tool and enter a change description in the Commit window, e.g. "Added new border". Then click OK". The addition (modified) disappears again.
• You can understand this change by clicking on the "Show Logs" PgVersion tool.

4.6 Delete objects in a layer

• Deleting objects in a layer Load the historicized layer windenergie via the pgversion plugin.
• Activate the edit mode of the layer windenergie, zoom to the area in the middle of the North Sea, mark it and then delete. You have two options:
  • They can remove the surface via the QGIS tool "Delete Selected".
  • You can use the PgVersion tool "Delete directly in the database".
  The latter is always the better option when dealing with large amounts of data. Otherwise, this can take a long time with the QGIS tool.
• Save the change in QGIS. The modified layer now gets the addition (modified). The change has not yet been returned to the historization.
• Now click on the PgVersion tool "Reset to Head Revision". As a result, your changes are lost, they are discarded.

4.7 Discard recent changes

• Load the historized layer grenzen_bkg via the PgVersion plugin.
• Activate the editing mode of the layer borders_bkg, zoom in on an area and move the vertices of a line.
• Save the change in QGIS. The modified layer now gets the addition (modified). The change is not yet returned to the versioning.
• Select the PgVersion "Submit Change" tool and enter a change description in the Commit window, e.g. "Area deleted". Then click OK". The addition (modified) disappears again.
• You can understand this first change by clicking on the "Show Logs" PgVersion tool.

4.8 Go back to a previous revision of a layer

• Load the historized layer grenzen_bkg via the PgVersion plugin.
• Click the PgVersion Show Logs tool, and then select a revision to which you want to reset the layer.
• Now click on the button "Reset to the selected revision". The previous steps are still historicized. However, you have reset the state of the layer to the status of the selected revision.

4.9 Remove a layer from versioning

• Download the historicized polygon layer navigation via the PgVersion plugin.
• Select the layer and go to the menu Database → PgVersion.
• There you can remove the layer from the versioning with the tool "Delete Versioning of the Layer".
• Please remember, this step is irrevocable!

4.10 Resolve a conflict while editing

• Load the historized polygon layer navigation via the PgVersion plugin.
• Select the layer, make a change to a polygon, and save it locally without committing it.
• Now load the historized polygon layer navigation via the PgVersion plugin as another user. Make a change to the same polygon and commit it back to the versioning.
• Then switch back to the 1 user and try to commit your change as well.
• A window will now open, allowing you to solve the conflict together with the other user.

4.11 Update with copy / paste on the QGIS screen

• Download the versioned record rail_version
• Download the continuation Entwurf_LROP_2014_Schiene.shp
• Bring both layers into edit mode and then copy / paste the versioned dataset with the geometries and attributes from the continuation.

4.12 NULL values of the primary-key column not allowed

There are 2 ways to handle it.

• You can always enter any value. This will be overwritten when passing the changes to the versioning.
• Once after the initial versioning of a layer, you define this in the Layer properties menu → Attribute form for the "ID" column in the Set control element type area, that this will become a hiding place. Then save the style as default in the database, so that it is always used automatically for each user.