QGIS Versioning now supports foreign keys!
QGIS-versioning is a QGIS and PostGIS plugin dedicated to data versioning and history management. It supports :
- Keeping full table history with all modifications
- Transparent access to current data
- Versioning tables with branches
- Work offline
- Work on a data subset
- Conflict management with a GUI
QGIS versioning conflict management
In a previous blog article we detailed how QGIS versioning can manage data history, branches, and work offline with PostGIS-stored data and QGIS. We recently added foreign key support to QGIS versioning so you can now historize any complex database schema.
This QGIS plugin is available in the official QGIS plugin repository, and you can fork it on GitHub too !
Foreign key support
TL;DR
When a user decides to historize its PostgreSQL database with QGIS-versioning, the plugin alters the existing database schema and adds new fields in order to track down the different versions of a single table row. Every access to these versioned tables are subsequently made through updatable views in order to automatically fill in the new versioning fields.
Up to now, it was not possible to deal with primary keys and foreign keys : the original tables had to be constraints-free. This limitation has been lifted thanks to this contribution.
To make it simple, the solution is to remove all constraints from the original database and transform them into a set of SQL check triggers installed on the working copy databases (SQLite or PostgreSQL). As verifications are made on the client side, it’s impossible to propagate invalid modifications on your base server when you “commit” updates.
Behind the curtains
When you choose to historize an existing database, a few fields are added to the existing table. Among these fields, versioning_ididentifies one specific version of a row. For one existing row, there are several versions of this row, each with a different versioning_id but with the same original primary key field. As a consequence, that field cannot satisfy the unique constraint, so it cannot be a key, therefore no foreign key neither.
We therefore have to drop the primary key and foreign key constraints when historizing the table. Before removing them, constraints definitions are stored in a dedicated table so that these constraints can be checked later.
When the user checks out a specific table on a specific branch, QGIS-versioning uses that constraint table to build constraint checking triggers in the working copy. The way constraints are built depends on the checkout type (you can checkout in a SQLite file, in the master PostgreSQL database or in another PostgreSQL database).
What do we check ?
That’s where the fun begins ! The first thing we have to check is key uniqueness or foreign key referencing an existing key on insert or update. Remember that there are no primary key and foreign key anymore, we dropped them when activating historization. We keep the term for better understanding.
You also have to deal with deleting or updating a referenced row and the different ways of propagating the modification : cascade, set default, set null, or simply failure, as explained in PostgreSQL Foreign keys documentation .
Nevermind all that, this problem has been solved for you and everything is done automatically in QGIS-versioning. Before you ask, yes foreign keys spanning on multiple fields are also supported.
What’s new in QGIS ?
You will get a new message you probably already know about, when you try to make an invalid modification committing your changes to the master database
Error when foreign key constraint is violated
Partial checkout
One existing Qgis-versioning feature is partial checkout. It allows a user to select a subset of data to checkout in its working copy. It avoids downloading gigabytes of data you do not care about. You can, for instance, checkout features within a given spatial extent.
So far, so good. But if you have only a part of your data, you cannot ensure that modifying a data field as primary key will keep uniqueness. In this particular case, QGIS-versioning will trigger errors on commit, pointing out the invalid rows you have to modify so the unique constraint remains valid.
Error when committing non unique key after a partial checkout
Tests
There is a lot to check when you intend to replace the existing constraint system with your own constraint system based on triggers. In order to ensure QGIS-Versioning stability and reliability, we put some special effort on building a test set that cover all use cases and possible exceptions.
What’s next
There is now no known limitations on using QGIS-versioning on any of your database. If you think about a missing feature or just want to know more about QGIS and QGIS-versioning, feel free to contact us at [email protected]. And please have a look at our support offering for QGIS.
Many thanks to eHealth Africa who helped us develop these new features. eHealth Africa is a non-governmental organization based in Nigeria. Their mission is to build stronger health systems through the design and implementation of data-driven solutions.
Refresh your maps FROM postgreSQL !
Continuing our love story with PostgreSQL and QGIS, we asked QGIS.org a grant application during early 2017 spring.
The idea was to take benefit of very advanced PostgreSQL features, that probably never were used in a Desktop GIS client before.
Today, let’s see what we can do with the PostgreSQL NOTIFY feature!
Ever dreamt of being able to trigger things from outside QGIS? Ever wanted a magic stick to trigger actions in some clients from a database action?
NOTIFY is a PostgreSQL specific feature allowing to generate notifications on a channel and optionally send a message — a payload in PG’s dialect .
In short, from within a transaction, we can raise a signal in a PostgreSQL queue and listen to it from a client.
In action
We hardcoded a channel named “qgis” and made QGIS able to LISTEN to NOTIFY events and transform them into Qt’s signals. The signals are connected to layer refresh when you switch on this rendering option.
Optionnally, adding a message filter will only redraw the layer for some specific events.
This mechanism is really versatile and we now can imagine many possibilities, maybe like trigger a notification message to your users from the database, interact with plugins, or even code a chat between users of the same database (ok, this is stupid) !
More than just refresh layers?
The first implementation we chose was to trigger a layer refresh because we believe this is a good way for users to discover this new feature.
But QGIS rocks hey, doing crazy things for limited uses is not the way.
Thanks to feedback on the Pull Request, we added the possibility to trigger layer actions on notification.
That should be pretty versatile since you can do almost anything with those actions now.
Caveats
QGIS will open a permanent connection to PostgreSQL to watch the notify signals. Please keep that in mind if you have several clients and a limited number of connections.
Notify signals are only transmitted with the transaction, so when the COMMIT is raised. So be aware that this might not help you if users are inside an edit session.
QGIS has a lot of different caches, for attribute table for instance. We currently have no specific way to invalidate a specific cache, and then order QGIS to refresh it’s attribute table.
There is no way in PG to list all channels of a database session, that’s why we couldn’t propose a combobox list of available signals in the renderer option dialog. Anyway, to avoid too many issues, we decided to hardcode the channel name in QGIS with the name “qgis”. If this is somehow not enough for your needs, please contact us!
Conclusion
The github pull request is here : https://github.com/qgis/QGIS/pull/5179
We are convinced this would be really useful for real time application, let us know if that makes some bells ring on your side!
More to come soon, stay tuned!
Undo Redo stack is back QGIS Transaction groups
Let’s keep on looking at what we did in QGIS.org grant application of early 2017 spring.
At Oslandia, we use a lot the transaction groups option of QGIS. It was an experimental feature in QGIS 2.X allowing to open only one common Postgres transaction for all layers sharing the same connection string.
Transaction group option
When activated, that option will bring many killer features:
- Users can switch all the layers in edit mode at once. A real time saver.
- Every INSERT, UPDATE or DELETE is forwarded immediately to the database, which is nice for:
- Evaluating on the fly if database constraints are satisfied or not. Without transaction groups this is only done when saving the edits and this can be frustrating to create dozens of features and having one of them rejected because of a foreign key constraint…
- Having triggers evaluated on the fly. QGIS is so powerful when dealing with “thick database” concepts that I would never go back to a pure GIS ignoring how powerful databases can be !
- Playing with QgsTransaction.ExecuteSQL allows to trigger stored procedures in PostgreSQL in a beautiful API style interface. Something like
- However, the implementation was flagged “experimental” because some caveats where still causing issues:
- Committing on the fly was breaking the logic of the undo/redo stack. So there was no way to do a local edit. No Ctrl+Z! The only way to rollback was to stop the edit session and loose all the work. Ouch.. Bad!
- Playing with ExecuteSQL did not dirty the QGIS edit buffer. So, if during an edit session no edit action was made using QGIS native tools, there was no clean way to activate the “save edits” icon.
- When having some failures in the triggers, QGIS may loose DB connection and thus create a silent ROLLBACK.
We decided to try to restore the undo/redo stack by saving the history edits in PostgreSQL SAVEPOINTS and see if we could restore the original feature in QGIS.
And.. it worked!
Let’s see that in action:
Potential caveats ?
At start, we worried about how heavy all those savepoints would be for the database. It turns out that maybe for really massive geometries, and heavy editing sessions, this could start to weight a bit, but honestly far away from PostgreSQL capabilities.
Up to now, we didn’t really find any issue with that..
And we didn’t address the silent ROLLBACK that occurs sometimes, because it is generated by buggy stored procedures, easy to solve.
Some new ideas came to us when working in that area. For instance, if a transaction locks a feature, QGIS just… wait for the lock to be released. I think we should find a way to advertise those locks to the users, that would be great! If you’re interested in making that happen, please contact us.
More to come soon, stay tuned!
