2.3. Initialisation de la base de données
Vous pouvez utiliser pgAdmin afin d’accéder et gérer le serveur de base de données.
Note
En cliquant sur ce lien vous apprendrez à install pgAdmin
2.3.1. Procédure
Dans pgAdmin 4
Se connecter au serveur de la base de données
Create a new database with UTF8 encoding
E.g. tww_demo, avoid spaces, dots, uppercases and special characters As proposed in https://stackoverflow.com/questions/2878248/postgresql-naming-conventions
2.3.1.1. Create minimal roles and access
TWW roles are defined in the + 12_0_roles.sql (per cluster) + 12_1_roles.sql (per database)
12_0_roles.sql has to be run before restoring the demodata database. 12_1_roles.sql has to be run if you initialize your module with with the commandline.
An evolution of the roles management is in progress and will be available soon.
It is highly recommended to use these when using TWW in a production environment.
Copy paste and run the .sql files content in the query editor of pgAdmin (Tools > Query Tool).
2.3.1.2. Restore data model with demodata
Avertissement
Demodata only available as an INTERLIS file The undeling procedure is now providing an empty datamodel with value lists.
The demodata is now only available in INTERLIS format but can be easily imported with the plugin IMPORT tool.
Restaurer le dernier modèle de données qui inclut également les données de démonstration:
Download demo data * https://github.com/teksi/wastewater/releases/latest * download tww_vx.y.z_structure_and_demo_data.backup
Back in pgAdmin :
Right click the tww_demo database
Cliquer sur Restore
Load your download of tww_vx.y.z_structure_and_demo_data.backup
Cliquer dans l’onglet “Restore Options” et cocher ces options
Cliquer sur Restore
Check whether in Message window Restoring backup on the server is successfully completed.
Note
If the Restore is failed and the detail reads something like « pg_restore: [archiver] unsupported version (1.13) in file header » or in German « pg_restore: [Archivierer] nicht unterstützte Version (1.13) im Dateikopf » try updating your PostgreSQL, see https://stackoverflow.com/questions/49064209/getting-archiver-unsupported-version-1-13-in-file-header-when-running-pg-r
Note
If the Restore failed and the detail reads something like « pg_restore: error: could not execute query: FEHLER: role »tww_viewer« does not exist then you have not created the minimal roles (see above)
Fermer la fenêtre restauration.
Clic-droit sur la base de données et cliquer sur Rafraîchir
Propably you want to rename the database: Right click the database, click Properties… and rename the database.
There are now 6 schemas in the database
public
tww_app
tww_cfg
tww_od
tww_sys
tww_vl
2.3.1.3. Modèle de données vide
Vous avez également l’option de restaurer le dernier modèle de données vide (sans données de démonstration)
Download the data model by going to https://github.com/teksi/wastewater/releases and by downloading the latest tww_vx.y.z_structure_with_value_lists.sql.
Note
If you run the sql in a Requêtes SQL Window, you will get an error. You have to use a BAT-File.
Use a BAT-File like that, to create the database, the extensions and the schemas with valuelist (replace x.y.z with your version):
@echo off set filename="tww_vx.y.z_structure_with_value_lists.sql" if not exist %filename% ( echo "File %filename% does not exist. Please download the latest datamodel from https://github.com/TWW/datamodel/releases (structure_with_value_lists.sql) and adjust filename in this batch file." PAUSE exit -1 ) set /p db="Please enter the database name? (e.g. tww_community) " set /p password="Please enter the password for user postgres? " set port=5432 set PATH=%PATH%;C:\Program Files\PostgreSQL\15\bin set PGPASSWORD=%password% createdb -U postgres -p %port% %db% psql -U postgres -h localhost -p %port% -d %db% -f %filename% psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA public TO ""tww""" psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA tww_vl TO ""tww""" psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA tww_od TO ""tww""" psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA tww_import TO ""tww""" psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA tww_swmm TO ""tww""" psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA tww_sys TO ""tww""" psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tww_od TO ""tww"""; psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tww_swmm TO ""tww"""; psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA tww_import TO ""tww"""; psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT ON ALL TABLES IN SCHEMA tww_vl TO ""tww"""; psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT ON ALL TABLES IN SCHEMA tww_sys TO ""tww"""; psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO ""tww""" psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON ALL SEQUENCES IN SCHEMA tww_od TO ""tww""" psql -U postgres -h localhost -p %port% -d %db% -c "REFRESH MATERIALIZED VIEW tww_od.vw_network_node" psql -U postgres -h localhost -p %port% -d %db% -c "REFRESH MATERIALIZED VIEW tww_od.vw_network_segment" PAUSE
Note
Vous êtes libre de choisir le nom de la base de données.
Update privileges for the tww_od, tww_sys, tww_vl, tww_network, tww_import, tww_swmm schema as described in the chapter Create minimal roles and access.
2.3.1.4. Générer le modèle de données sous Linux
Vous pouvez également générer le modèle de données sous Linux.
Télécharger le modèle de données:
git clone https://github.com/TWW/datamodel cd datamodel
Setup the
pg_service.conf
file and add thepg_tww
service as in the Linux/macOS pg_service section.Créer la base de données
psql -U postgres -c 'CREATE DATABASE tww;'
Lancer le script de generation:
./scripts/db_setup.sh
Si vous souhaitez utiliser un SRID différend, vous devrez utiliser l’option -s
. Par exemple, lancer ./scripts/db_setup.sh -s 2056
pour 2056 SRID.
Si vous avez déjà un modèle de données et désirez forcer la régénération du modèle, vous pouvez également utiliser l’option -f
: ./scripts/db_setup.sh -f
.
You can use the -r
option to add roles (tww_viewer
, tww_user
, tww_manager
, tww_sysadmin
).
Viewer: Peut consulter les tables et les vues.
Utilisateur: Peut éditer les données
Gestionnaire: Peut éditer les données et les listes de valeurs.
Admin: Administrateur base de données