TEKSI Developer Guide
We want TEKSI modules to be technically as similar and simple as possible to avoid extra maintenance and high development costs. This Guide is meant to list mandatory elements and architecture choices for TEKSI modules.
We rely on similar git based development process as QGIS, see here for more informations about how to contribute using git.
- TEKSI wastewater & GWMP : Pre release is out, we are on the final testing phase (actual state is QGEP project : )
- TEKSI drinking water : waiting for TEKSI wastewater (actual state is QWAT project : )
- TEKSI distant heating : Pilot project
- TEKSI
- wastewater / TWW
- drinking_water / TDW
- district_heating / TDH
- wastewater
- drinking_water
- district_heating
The datamodel :
- is based on an SQL implementation on recent versions of postgreSQL / postgis.
- uses English terms.
- uses lowercase and underscores as naming convention (as proposed here)
Files used for the datamodel generation and upgrade are stored in the datamodel/ folder.
Guidelines around datamodel choices are available here (TBD)
The datamodel is created with a setup.sh script that covers the following points:
- Header:
#!/bin/bash - Description of what the script creates:
# This script will create a clean datastructure for the# TEKSI Wastewater# based on the VSA-DSS 2020 datamodel (see www.vsa.ch/model)# It will create new schemats tww_* in a postgres database. set -e- Definition of PGSERVICE and SRID (Coordination system reference ID):
PGSERVICE=${PGSERVICE:-pg_tww}SRID=2056 - Definition of DIR variable:
DIR=$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/.. - Switching to defined directory
cd $DIR - Running sql script for schema definition:
psql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/changelogs/0001/01_schema.sql - Running sql script for sys schema and tables:
psql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/changelogs/0001/02_sys.sql - Running sql script for oid generation function:
psql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/changelogs/0001/oid_generation.sql - Running sql scripts for data model creation, extensions, adaptions and dictionaries: e.g.
psql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/changelogs/0001/03_tww_db_dss.sql -v SRID=$SRID - Running sql scripts for app schema:
psql "service=${PGSERVICE}" -c "CREATE SCHEMA IF NOT EXISTS tww_app;" - Running sql script for all functions: e.g.
psql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/app/symbology_functions.sql - Running sql scripts for views creation:
${DIR}/app/view/create_views.py --pg_service ${PGSERVICE} --srid ${SRID} - Running sql scripts for triggers creation:
psql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/app/triggers/network.sql - Running sql scripts for roles creation:
psql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/12_0_roles.sqlpsql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/12_1_roles.sql
TEKSI datamodels are organized with different schemas simplifying comprehension, rights management and development.
- txx_od : Ordinary data / Main objects of the concerned infrastructure
- txx_vl : Value lists /
- txx_app : Application / necessary functions and mechanics
- txx_sys : System / Metadatas and technical tables for Sysadmins (database version, logs, ...)
Remark : txx_custom : custom being any extension name of the actual datamodel for any extension need (not used yet in profit of having as much as possible in the core to avoid high compatibility costs when upgrading core and not dependencies)
Examples from the different modules:
- tww_od, ww_vl, tww_app, tww_sys
- tdw_od, tdw_vl, tdw_app, tdw_sys
- tdh_od, tdh_vl, tdh_app, tdh_sys
(import, export, network or extensions views or application mechanics are now available in txx_app)
The Tables display the relational version of the INTERLIS datamodel of the corresponding technical organisation (e.g. VSA, SVGW, ...) for this module
Attribute name are in English.
TEKSI uses STANDARDOID with prefix (8 characters) and postfix (8 characters) as defined in INTERLIS.
The prefix is maintained in the txx_sys.prefix table
Definition: obj_id character varying(16) COLLATE pg_catalog."default" NOT NULL DEFAULT txx_sys.generate_oid('table_name_prefix'::text, 'table_name'::text),
table_name_prefix: e.g. 'txx_od'
table_name: e.g access_aid
Example:
obj_id character varying(16) COLLATE pg_catalog."default" NOT NULL DEFAULT txx_sys.generate_oid('tww_od'::text, 'access_aid'::text),
to define
* take convention from qgep with usr_* ? https://qgep.github.io/docs/admin-guide/db-customization/index.html#adding-fields
* how to name additional fields that support the TEKSI functionality, but are not calculated? teksi_
_ how to name additional classes that are not the current release of VSA-DSS. E.g. class aquifier that is still needed from the 2015 model? Should it be dss2015_aquifier instead?
TO DO: Integrate https://github.com/orgs/teksi/discussions/97
- Fieldname starts with
_: e.g._width
-
For obj_ids pkey_tww_od_*:
CONSTRAINT pkey_tww_od_access_aid_obj_id PRIMARY KEY (obj_id), -
For value list relations fk_vl_*:
CONSTRAINT fkey_vl_access_aid_kind FOREIGN KEY (kind)
REFERENCES tww_vl.access_aid_kind (code) MATCH SIMPLE
ON UPDATE RESTRICT
ON DELETE RESTRICT,
- For superclas relation oorel (if exists) Example: superclass of access_aid is structure_part
CONSTRAINT oorel_od_access_aid_structure_part FOREIGN KEY (obj_id)
REFERENCES tww_od.structure_part (obj_id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
- Define DEFERRABLE INITIALLY DEFERRED for all classes (to avoid especially with possible circle relations where related data could be imported not in order of the model hierarchy):
Example: wastewater_structure
ALTER TABLE tww_od.wastewater_structure ADD CONSTRAINT rel_wastewater_structure_owner FOREIGN KEY (fk_owner) REFERENCES tww_od.organisation(obj_id) ON UPDATE CASCADE ON DELETE set null DEFERRABLE INITIALLY DEFERRED;
CREATE TRIGGER update_last_modified_access_aid
BEFORE INSERT OR UPDATE
ON tww_od.access_aid
FOR EACH ROW
EXECUTE FUNCTION qgep_sys.update_last_modified_parent('tww_od.structure_part');
A value list table is usually defined as :
- obj_id / primary key
- code / unique integer
- normcode / code for norm correspondance
- value_en / english value
- value_short_en / english short value
- description_en / description of the attribute value
- value_*, value_short_* and description_* for additional languages
- active / a boolean (true or false) to activate / deactivate a value usage
Based on tww_sys.value_list_base
Naming convention for vl_tables: classname + _ + attributename e.g. manhole + _ + material -> manhole_material
TEKSI wastewater: These value lists are defined in the VSA-DSS datamodel. Do not change!
To discuss: vl_table-names with a prefix. In the old version, the prefix 'vl' is manually defined in the project :-( -> vl_manhole_material._ Because there are a lot of vl_tables in the project, you have to scroll a lot to the vw* tables. Suggestion: use 'zvl_' as prefix for vl_tables. _
ALL views are created in the txx_app schema.
Scripts are store in the datamodel/view folder. The corresponding scripts should be named accordingly (.sql,.yaml,.py).
Naming convention :
-
subclass views that integrate the superclass attributes are named vw_subclass. (example:
vw_maintenance) - superclass views (superclass views that integrate all subclass attributes with prefixes to the attributes of the subclass) should be named vw_txx_superclass (example: vw_tww_maintenance_event).
- dito big views that integrate other classes like the main views vw_tww_reach and vw_tww_wastewater_structure that JOIN together a series of related classes.
- other class views are named vw_class (if ever needed).
-
Extension views are prefixed with their extension name (example :
swmm_vw_conduits) - if there are abreviations used for classes in views they should use the english prefix defined in txx_sys.dictionary_od_table
Warning : We strongly discourage users to create views referencing other views. This can lead to overly complicated upgrades when dealing with datamodel alterations (postgreSQL locks).
Functions and triggers are stored in the tww_app schema.
They can be removed and recreated easily.
They include the business logic of the application and interact with the datamodel other schema to insert data at the expected place.
To be able to updgrade adaptions to the data model each change needs to be accompied with a delta sql.
This has to integrate
- the new parts of the data model
- It should support checking on the used SRID
- UPDATE statements, if content of data has to be modified
Delta files have to be placed in the respective delta folder of each module:
Use the following code as wrapper and add SQL statements with
DO $$
DECLARE
srid_value INTEGER;
BEGIN
`-- Récupérer la valeur SRID depuis la table qwat_sys.settings`
`SELECT value INTO srid_value FROM qwat_sys.settings WHERE name = 'srid';`
`...`
END $$;
Instead of for example:
ALTER TABLE qwat_od.sia405pt_protection_tube ADD COLUMN geometry3d_geometry geometry('COMPOUNDCURVEZ', :SRID);
use:
-- Construire et exécuter la commande ALTER TABLE avec la valeur SRID récupérée
EXECUTE format('ALTER TABLE qwat_od.sia405pt_protection_tube ADD COLUMN geometry3d_geometry geometry(''COMPOUNDCURVEZ'', %s)', srid_value);
Note : Always use Trackable QGIS Project Plugin to develope QGIS projects - so the file is human readable.
Project properties / datasources:
- transaction mode: automatic transaction groups
- set the option Evaluate default values on provider side
Add new layers with a connection-definition:
tww: service = pg_tww , ssl_mode = prefer.
tdh: service = pg_tdh , ssl_mode = prefer.
Like this there will be no entry "sslmode=*" in the project-file and therefore, if automatic transaction is enabled, all layers will toggle editing together. For more information QGIS Docs Creating a stored Connection
Notes : See this background article about the PostgresSQL Connection service (pgconf service).
And the new QGIS plugin PG service parser that helps you define and manage PostgresSQL Connections easily.
The .qgs project and related files are stored in the project/ folder.
The .qgs project is configured with recent functionalities developped and available with each release
Translation:
The TEKSI .qgs project should be edited in **English **and not translate any data model objects, layers, value lists etc. to another language. This will be managed with a connected CI workflow that generates a ts file (background see https://www.opengis.ch/de/2018/09/11/qgis-speaks-a-lot-of-languages/), that then will be used to make the translation of the elements on transifex and then automatically create the language *_xx.qm (e.g. German *_de.qm) for the available languages.
-
Example for setting up of transifex see https://github.com/teksi/wastewater/tree/main/docs#translation-process
-
Be aware: QGIS 3.34.10 has several restrictions concerning translations:
no translation of map theme names, style names, labels of a symbology, field-alias just in one style (!!), layergroup name inside a layergroup, variables, value map descriptions
Guidelines around QGIS project choices are available here (TBD)
- naming convention for qgis relations: names are optional. Use the referencing field/layer as name (normaly the fk_field). If there are two or more relations from the referencing field to different views, there can also the referenced layer be in the name
- use relations only, if they are necessary (e.g. to create a connection to a record on the map). Otherwise use "Wertbeziehung", e.g. for vl-tables, attribut that are similar to valuelists like "owner", "dataowner", "pipe_profil".
- naming conventions for calculated fields (not defined, no calculated fields in the tww-project)
- naming conventions on views? Should be defined under 'views'?
- project variables
$variables: naming convention:tww_* - project colors:
c_tww_*, for colors that are defined in the sia-Norm - views for subclasses - type attribute
- Map templates
- QGIS calculated fields
- How to deal with custom symbologies
- See also https://github.com/QGEP/QGEP/issues/772
- ...
Valuelists: attributes with valuelists are configured with value relations
FK-fields (foregin key) should also be configured as value relations if there is no need to add objects in the related table or to choose an object on the map. Advantage: no problem with multiedit.
Example: all relations to organisation, pipe_profile for reach
FK-fields that refer to tables with many records (> 100?) should not be configured as value relation for performance reasons. Use then relation reference !
Background maps -> swiss locator maps WMS / WMTS
Custom background maps (-> orthophoto / city plans / cadastral data Vector / WFS / WMS / WMTS)
It is so easy to define userspecific backgroundmaps as qlr-file and then just drag this qlr in the project. In the project is therefor just a simple WMS-layer. qlr-example externally.
Files are stored in the plugin/ folder.
Minimal requirements for python libraries should be stored in xxx (to be defined)
TWW: Module plugin to add functionality for
- Creating wastewater structures and reaches (Wizard)
- Network following
- Check production readyness
- INTERLIS Import / Export (planned to be moved to an extra plugin for all TEKSI modules (TEKSI INTERLIS Transfer (TIT) plugin, planned development)
TDH: No module plugin. All functionalities use QGIS functions.
TIT: Future plugin for all TEKSI modules to handle INTERLIS Import / Export including Protection tubes / Control cables
TMT: TEKSI Mangement Plugin (planned development)
Guidelines around QGIS plugin choices are available here (TBD)
The following roles are supported:
- txx_viewer
- txx_user
- txx_manager
- txx_admin
Roles definition see https://github.com/teksi/wastewater/blob/main/datamodel/12_1_roles.sql
Any module used in production should have a tagged version (Release) which is identified by a release number. Numbering should be set in a similar way for all modules. As a matter of code quality assessment, a continuous integration (CI) process should conduct proper testing before getting into a continuous deployment (CD) process.
Every Major Release is based on the Long Term Release (LTR) of any software used (QGIS, PostgreSQL, postgis, ...)
Minor Releases are built to ensure the new functionalities integration (from modules and used softwares mentionned here above)
Check the detailled roadmap for the release currently supported!
