# PostGIS Versioning - pgVersion 2.1.8

### Dr. Horst Duester, 2016, horst.duester@sourcepole.ch

# 1. Prerequisits

## 1.1. Ubuntu

You have to install Qt4SQL and the Qt4SQL PostgreSQL driver with:

`$ sudo apt-get install libqt4-sql libqt4-sql-psql python-qt4-sql`

# 2. Introduction

Versioning of Postgis-Layers will become essential, when more than one person edits the same layer concurrently. To manage concurrencing editing of a single Postgis Layer the pgVersion management system supports your work. The idea is to create a versioning system for editing PostGIS-Layers similar to source code versioning systems like CVS or Subversion. A http://www.portailsig.org/content/pgversion-le-plugin-de-versionnement-pour-postgis-qgis french was created by Nicolas Rochard.

# 3. Download:

Download the createFunctions.sql from http://www.kappasys.org/pgtools/pgversion/createFunctions.sql

# 4. Functions

To create the new `pgvs` functions you should run the SQL-Script:

    psql -d <databasename> -U <username> -h <hostname> \
         -f <Path to>/createFunctions.sql

After running this command, a new schema is created:

    versions

and some new functions have been created in the schema `versions`:

    pgvsinit()
    pgvscommit()
    pgvsmerge()
    pgvsdrop()
    pgvsrevert()
    pgvsrevision()
    pgvslogview()
    pgvsrollback()
    pgvscheckout()

This schema contains all information which are neccessary to manage the versioned tables. I highly advise that you should not make any changes in the schema `versions`. Now your database is ready for versioning. You also have the option to install the `pgvs` environment in the `template1` database. In this case every new created database will automatically contain the `pgvs` environment.

## 4.1. pgvsinit

The `pgvsinit()` function initializes the versioning environment for a single geometry layer.

The init command:

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

The initialisation process works in 3 steps:

- creating a view with the name `<tablename>_version`. This view has the same structure as the origional table.
- creating of some rules and triggers for the new view
- add a record to the meta table `versions.version_tables`

All future editings have to be done on the view `<tablename>_version`. If you want to change the geometry or an attribute value of a versioned PostGIS-Layer you can do this in the same way like you would edit a real table. After saving of changes on the Layer, you will see all changes you made. But your changes are only visible for you, they are saved in a temporary state. To make your changes available for the rest of the world, you have to commit your changes to the database.

It is not possible to change the structure of the underlaying table. If you want to do this, you have to drop the versioning system from the table like described later. Than you can make your changes. As the last step you have to initialize the versioning system for the table again.

## 4.2. pgvscommit

When you finish your editings after a while, you have to commit your editings to the main PostGIS-Layer table. In this way you make your editings available for the rest of the world.

The commit command:

    select * from versions.psvscommit('<schema>.<tablename>','<log-message>';

Sometimes it happens, that two or more different users edit the same table record. In this case `pgvscommit()` lists the conflicting records. The conflicting objects are not saved to the database. Please contact the user mentioned in the error message to discuss, which change should be committed to the database.

## 4.3. pgvsmerge

To solve conflicts user the command:

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

## 4.4. pgvsdrop

To remove the versioning system from a specific table, you can use the command:

    select * from versions.pgvsdrop('<tablename>');

Than all versioning stuff will be removed from the PostGIS-Layer table. You only can drop versioning from a table when all changes of all users are committed. Don't worry, the command `pgvsdrop('<tablename>');` only removes the versioning system. The main PostGIS-Layer table with all former committs still exists of course!

## 4.5. pgvsrevert

The pgvsrevert function gives you the option to remove all your uncommitted editings and set back your data to the HEAD revision. The revision number of the HEAD revision will be returned.

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

## 4.6. pgvsrevision

The pgvsrevision function returns the installed revison of pgvs:

    select * from versions.pgvsrevision();

## 4.7. pgvslogview

The pgvslogview function returns all logs of a specific versioned table:

    select * from versions.pgpslogview('<tablename>');

## 4.8. pgvsrollback

The pgvsrollback function will push a revision into HEAD revision:

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

## 4.9. pgvscheckout

The pgvscheckout function will checkout a specific revision from repository:

    select * from versions.pgvscheckout('<tablename>',revision integer);

# 5. QGIS Plugin

To make your life easier, the pgversion plugin for QGIS supports you by the use of `pgvs`.

To make the plugin available for your QGIS installation add the kappasys plugin repository

http://www.kappasys.org/qgis/plugins.xml

to the 3rd party repositories of the plugin manager. Starting with QGIS 1.7 the kappasys plugin repository will be added automatically.

## 5.1. Prepare layer for versioning

With this option you start the versioning for a PostGIS layer. You have to do this once for every layer versioning should be available. Select the layer and the versioning system will be available for this layer. After this step you have to remove the layer from QGIS. To work with the versioned layer you have to do the next step.

## 5.2. Load versioned layer

Now you can select the versioned layer from your database. In fact the corresponding layer view is loaded. Select your QGIS-PostGIS-Connection. You also will see the connected user. Than select the versioned layer from the list. The layer will be loaded to the QGIS Mapcanvas and you can start with editing.

## 5.3. Commit changes

When you have finished your editings you can commit your changes to the database. When no conflicts between your editings and editings of an other user have been detected you get a dialog to enter a log message. After a successful commit you will see this message.

In the case that a different editor has changed one or more objects you edited as well, a new window will appear to support the merging of concurrent objects.

The conflicting objects are shown up in red with and labled with the corresponding username.

To hightlight the object of a single record from the table below the map, click a row and the corresponding object will be hightlighted in blue color.

Now you can select the object to commit from the select box.

Click to complete merging for this single object.

## 5.4. Revert to head revision

Sometimes you like to remove your non committed changes. That means that you can set your view back to the HEAD revision. In this case all of your editings will be removed. To revert your editings activate the corresponding layer in the legend and select the menue "Revert to head revision"

## 5.5. Drop versioning from layer

Drop versioning from layer does not drop the main layer itself. It only drops the versioning environment of the layer. This is necessary in the case you changed the model of the main table or you don't want to use the versioning environment in the future. To drop versioning from layer activate the corresponding layer in the legend and select "Drop versioning from layer" in the menue.

## 5.6. Help

You will get this help.

# 6. License

LICENSING INFORMATION:

pgvs Postgres-Functions and PgVersion QGIS Plugin is copyright (C) 2010 Dr. Horst Duester

horst.duester@kappasys.ch

QGIS-Plugin icons is copyright (C) 2010 Robert Szczepanek

robert@szczepanek.pl

Licensed under the terms of GNU GPL 2

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.