Skip to content

TEKSI Developer Guide

Jump to bottom Edit New page
Stefan edited this page Jun 5, 2024 · 46 revisions

General

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.

Development process

We rely on similar git based development process as QGIS, see here for more informations about how to contribute using git.

Modules

  • TEKSI wastewater & GEP : under development (actual state is QGEP project : )
  • TEKSI drinking water : waiting for TEKSI wastewater (actual state is QWAT project : )
  • TEKSI distant heating : Pilot project

Github organization :

  • TEKSI

Modules repositories :

  • wastewater
  • drinking_water
  • distance_heating

Databases names

  • wastewater
  • drinking_water
  • distance_heating

Datamodel

The datamodel is based on SQL implementation on recent versions of postgreSQL / postgis.

The datamodel structure uses English terms.

Files are stored in the datamodel/ folder.

Guidelines around datamodel choices are available here (TBD)

Datamodel Generation

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.sql psql "service=${PGSERVICE}" -v ON_ERROR_STOP=1 -f ${DIR}/12_1_roles.sql

Schemas

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)

Tables

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.

Primary key attribute: obj_id with DEFAULT to generate_oid function

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),

Custom fields

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

DB calculated fields

  • Fieldname starts with _: e.g. _width

Table constraints

  • 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;

Subclass Table trigger update_last_modified_*

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');

Value list

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. _

Views

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)

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

Triggers

QGIS project

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: service = pg_tww , ssl_mode = prefer.

Like this there will be no entry "sslmode=*" in the project-file and therefore, if automatic transaction is enabled, all tww-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.

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
  • ...

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.

Module specific QGIS plugin

Files are stored in the plugin/ folder.

Guidelines around QGIS plugin choices are available here (TBD)

Roles

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

Releases and CI/CD

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.

Roadmap

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!

Add a custom footer

TEKSI

https://www.teksi.ch

Clone this wiki locally