giswater-admin

Headless CLI and engine for the Giswater database schema lifecycle (create, upgrade, drop, inspect). It reads the same YAML manifests under dbmodel/manifests/ and runs the same SchemaBuilder as the QGIS plugin (GwSchemaBuilderTask + QtDbAdapter), so automation, CI, and the desktop UI do not diverge on SQL execution logic.

See also: dbmodel README — Schema architecture · dbmodel README — Testing

Table of contents

1. Overview
2. Where to run it
3. Architecture
4. Install
5. Connection
6. Invocation rules
7. Global options
8. Commands reference
9. Kinds and manifest profiles
10. Timing and output
11. QGIS integration
12. Unit tests
13. Extending the model
14. Keeping docs in sync

Overview

Legacy shims at package root (paths.py, user_config.py, releases.py) re-export from install/ for backward compatibility.

Typical flow for a new database:

1. Create an empty PostgreSQL database (outside this CLI).
2. db init — cluster extensions once per database.
3. schema main create / schema addon create — build project and satellite schemas.
4. schema addon integrate — wire satellites into ws/ud parents.
5. network show — inspect topology; network update — lockstep upgrade.

Where to run it

Requirements: Python 3.9+, PostgreSQL with PostGIS/pgRouting on the server.

dbmodel resolution order (when --dbmodel-path is omitted):

1. --dbmodel-path
2. GW_DBMODEL_PATH environment variable
3. User config with source: dev (gw dbmodel use dev …)
4. Sibling dbmodel/ in a plugin repo checkout (overrides stale release cache)
5. Release cache (gw dbmodel install …)

If nothing matches, run gw dbmodel install latest or gw dbmodel use dev --root /path/to/repo.

Architecture

flowchart TB
  subgraph hosts [Execution contexts]
    CLI["gw / python -m giswater_admin"]
    QGIS["GwSchemaBuilderTask + QtDbAdapter"]
    CI["dbmodel/test/bootstrap_inner.sh"]
  end
  subgraph engine [giswater_admin/engine]
    Manifest["manifests/*.yaml"]
    Builder["SchemaBuilder"]
    Runner["sql_runner"]
  end
  subgraph dbmodel_tree [dbmodel/]
    Schemas["schemas/..."]
    Updates["updates M/m/p"]
  end
  PG[(PostgreSQL)]
  CLI --> Builder
  QGIS --> Builder
  CI --> CLI
  Builder --> Manifest
  Builder --> Runner
  Runner --> Schemas
  Runner --> Updates
  Runner --> PG

Phase types (declared in manifests, implemented in engine/manifest.py):

Install

Global CLI (Windows / Linux / macOS)

Install pipx once, then install the CLI package:

# macOS
brew install pipx && pipx ensurepath

# Windows / Linux (if pipx is not installed yet)
python3 -m pip install --user pipx
python3 -m pipx ensurepath

# All platforms
pipx install giswater-cli

Download a published dbmodel (from the same plugin ZIP used by QGIS):

gw dbmodel install latest --set-active
gw version

Typical first run:

gw db init --conn "postgresql://user:pass@127.0.0.1:5432/mydb"
gw schema main create --type ws --name demo --profile empty --conn "postgresql://user:pass@127.0.0.1:5432/mydb"

Development without a release (use your local checkout):

gw dbmodel use dev --root /path/to/plugin
gw schema main create --type ws --name test_dev --conn "$CONN" --check

Config and cache locations:

From the plugin repository (contributors)

cd /path/to/plugin
python3 -m pip install -e .
# or legacy:
python3 -m pip install -r giswater_admin/requirements.txt

Optional virtualenv:

python3 -m venv .venv && source .venv/bin/activate
pip install -e .

OS packages (server side) — names vary by distro; install PostGIS and pgRouting for your PostgreSQL major version:

• Debian/Ubuntu: postgresql-16-postgis-3, postgresql-16-pgrouting, …
• macOS (Homebrew): postgresql@16, postgis
• Windows: PostGIS/pgRouting matching your PostgreSQL installer

Verify:

gw --help
# or
python3 -m giswater_admin --help

dbmodel management commands

Versioning

Two independent version numbers:

A single CLI release can create or upgrade schemas for any installed dbmodel version:

gw dbmodel install 4.14.0 && gw dbmodel use 4.14.0
gw create --kind ws --schema legacy --conn "$CONN"

gw dbmodel install 4.16.0 && gw dbmodel use 4.16.0
gw create --kind ws --schema current --conn "$CONN"

Use --plugin-version X.Y.Z to override the schema release when auto-detection is not possible (e.g. a custom --dbmodel-path without metadata.txt).

CLI releases (cli-v*)

The CLI is released independently from the QGIS plugin:

Maintain giswater_admin/CHANGELOG.md (Keep a Changelog format) before each release.

First release (0.1.0) — document changes under ## [Unreleased], then:

python3 scripts/prepare_cli_release.py 0.1.0 --create-github-release
python3 scripts/prepare_cli_release.py 0.1.0 --execute --create-github-release

(pyproject.toml and giswater_admin/__version__.py must already be 0.1.0.)

Subsequent releases (e.g. 0.2.0):

pip install ruff   # required: prepare_cli_release runs ruff before tagging
python3 scripts/bump_cli_version.py 0.2.0
# Add changes under ## [Unreleased] in giswater_admin/CHANGELOG.md
python3 scripts/prepare_cli_release.py 0.2.0 --create-github-release
python3 scripts/prepare_cli_release.py 0.2.0 --execute --create-github-release

prepare_cli_release.py runs ruff check giswater_admin scripts (see repo ruff.toml) before creating the tag or GitHub release. Fix lint issues first; the script aborts on failure.

When the GitHub Release is published, CI (.github/workflows/release-cli.yml) runs tests, validates versions, builds dist/*, publishes to PyPI via OIDC (Trusted Publisher, environment pypi), and attaches wheels to the release.

One-time PyPI setup: register a pending publisher at https://pypi.org/manage/account/publishing/ with project giswater-cli, owner giswater, repository plugin, workflow release-cli.yml, environment pypi. Create the matching pypi environment in GitHub repo settings.

Users install the tool once (pipx install giswater-cli) and refresh schema SQL with gw dbmodel install when a new plugin version ships.

Testing locally

cd /path/to/plugin
python3 -m pip install -e .

# Ensure gw is on PATH (macOS user install example)
export PATH="$HOME/Library/Python/3.9/bin:$PATH"

gw version
python3 -m pytest test/cli/ -q

# Offline schema plan (no DB)
gw create --kind ws --schema test --profile empty --check \
  --conn "postgresql://user@127.0.0.1:5432/mydb"

Network E2E (manual / release lockstep)

Per-commit dbmodel validation runs in PostgreSQL Tests (21 checks: sample pgTAP, profiles, updates, satellites, network). Release verifies those checks on the tag SHA; plugin releases also run network lockstep (update_network, PG 18).

Local (direct, needs Postgres + CONN):

export CONN='postgresql://postgres:postgres@127.0.0.1:5432/gw_db'
chmod +x scripts/gw_e2e_*.sh scripts/gw_bootstrap_network.sh

./scripts/gw_e2e_profiles.sh              # ws/ud × empty|sample|inventory
./scripts/gw_e2e_update_isolated.sh       # ws + ud solo @ FROM→TO
SATELLITES=utils,cibs ./scripts/gw_e2e_addons_integrate.sh
./scripts/gw_e2e_network_update.sh        # guards + lockstep
./scripts/gw_e2e_update_all.sh            # full release suite

Local (Docker, same stack as CI):

./dbmodel/test/run_e2e.sh update_all
./dbmodel/test/run_e2e.sh profiles
PG_MAJOR=17 ./dbmodel/test/run_e2e.sh update_network

# Satellite pgTAP (phase 2)
./dbmodel/test/run_satellite_tests.sh utils
./dbmodel/test/run_satellite_tests.sh network_ws

CI: GitHub Actions → PostgreSQL Tests on every PR/push touching dbmodel/** (21 jobs). Network E2E (Giswater) is manual (workflow_dispatch) for ad-hoc scenarios. Release workflows verify CI checks instead of re-running the full matrix.

Env: SATELLITES=utils,cibs, PARENT_PROFILE=empty|sample|inventory, PLUGIN_VER / TARGET_VER (default: last two semver patches).

Connection

Resolution order (first match wins):

1. --conn — postgresql://user:pass@host:port/dbname (or postgres://…)
2. --config — YAML with host, port, user, password, dbname, and/or service
3. User config — gw config set database.conn … or database.config /path/to/conn.yaml
4. Environment — PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, PGSERVICE

Persist a default connection (no --conn on every command):

gw config set database.conn "postgresql://gisadmin:secret@127.0.0.1:5432/giswater_cli"
gw schema list

# or point at a separate YAML (keeps passwords out of config.yaml)
gw config set database.config /path/conn.yaml
gw config set database.conn null   # clear URL

Superuser: mutating commands (db init, schema create/integrate/update/drop, network update, and --check with a live connection) require a PostgreSQL superuser. Read-only commands (schema list, network show) work with any role that can SELECT Giswater schemas and pg_catalog.

Linux / macOS

export CONN='postgresql://gisadmin:secret@127.0.0.1:5432/giswater_cli'
python3 -m giswater_admin status --conn "$CONN"

Windows (PowerShell)

$env:CONN = "postgresql://gisadmin:secret@127.0.0.1:5432/giswater_cli"
py -3 -m giswater_admin status --conn $env:CONN

Config file

host: 127.0.0.1
port: 5432
user: gisadmin
password: secret
dbname: giswater_cli

python3 -m giswater_admin status --config /path/conn.yaml

--check: many subcommands only print the plan or SQL and do not write to the database. db init --check does not require a connection.

Invocation rules

Global options are on the parent parser of each subcommand. They must appear after the subcommand name:

# Correct
python3 -m giswater_admin schema main create --type ws --name demo --profile empty --json

# Wrong (root parser does not define --json)
python3 -m giswater_admin --json create ...

Global options

Available on subcommands that include the shared parent parser (schema …, network …, db init, legacy aliases, manifest list|validate).

Connection group (where applicable):

Commands reference

Base syntax:

python3 -m giswater_admin <subcommand> [subcommand options] [global options]

Exit codes: 0 success, 1 failure (parse, I/O, PostgreSQL, SQL, invalid plan).

Command tree

gw db init
gw schema main   create | update | drop
gw schema addon  create | integrate | update | drop
gw project       create
gw network       show | update

Legacy aliases (create, update, drop, status, init-db, update-network, audit …) still work but print a deprecation warning to stderr and are hidden from --help.

Use --version X.Y.Z everywhere (replaces --plugin-version / --to-version).

db init

Creates extensions in order: postgis → postgis_raster → tablefunc → pgrouting → unaccent (optional postgres_fdw with --with-fdw). Run once per database before the first schema create.

gw db init --conn "$CONN"
gw db init --conn "$CONN" --with-fdw --with-pgtap

schema main

ws/ud project schemas.

schema main create

gw schema main create --type ws --name ws1 --profile sample --lang es_ES --conn "$CONN"
gw schema main create --type ud --profile empty --conn "$CONN"

schema main update

Upgrades one isolated ws/ud schema. Blocked if the schema belongs to an interconnected network (use network update). Downgrades forbidden.

gw schema main update --name ws1 --version 4.16.0 --conn "$CONN"

schema main drop

schema addon

Shared satellite schemas (utils, cibs, cm, am, audit).

schema addon create

Bootstrap a standalone addon (no parent wiring yet).

gw schema addon create --type utils --conn "$CONN"
gw schema addon create --type cibs --name cibs --conn "$CONN"
gw schema addon create --type audit --conn "$CONN"

schema addon integrate

Wire an addon into one ws/ud parent. Run once per parent (e.g. integrate utils with ws, then again with ud).

gw schema addon integrate --type utils --parent ws --conn "$CONN"
gw schema addon integrate --type cibs --parent ud --conn "$CONN"
gw schema addon integrate --type audit --parent ws --conn "$CONN"

schema addon update

Upgrades a standalone addon only. If integrated in a network → use network update. Downgrades forbidden.

gw schema addon update --type cibs --version 4.16.0 --conn "$CONN"

schema addon drop

gw schema addon drop --type utils --yes --cascade --conn "$CONN"

schema list

Read-only inventory of schemas with sys_version. No superuser required.

gw schema list --conn "$CONN"
gw schema list --conn "$CONN" --tier main
gw schema list --conn "$CONN" --tier addon --type cibs --json

project create

Creates a .qgs file from the layer metadata and styles stored in an existing ws/ud schema. This command requires a QGIS installation with PyQGIS. The CLI auto-detects the QGIS Python on macOS, Linux and Windows; set QGIS_PYTHON only when detection fails or you need a specific install.

# Optional override when auto-detect is wrong:
#   macOS:   export QGIS_PYTHON=/Applications/QGIS-LTR.app/Contents/MacOS/python
#   Linux:   export QGIS_PYTHON=/usr/bin/python3   # needs python3-qgis
#   Windows: set QGIS_PYTHON=C:\OSGeo4W\bin\python-qgis.bat
gw project create --schema ws --type ws --out ./qgs --force --conn "$CONN"

network show

Scans the whole database (all schemas with sys_version) and shows the interconnected Giswater network: ws/ud networks, shared satellites, integration links, version skew, and unlinked schemas.

gw network show --conn "$CONN"
gw network show --flat --conn "$CONN" --json

network update

Lockstep upgrade of the discovered network. For each semver folder (e.g. 4.16.0), runs utils → cibs → ws → ud → … before advancing. Downgrades forbidden (target must be ≥ every member version).

gw network update --version 4.16.0 --conn "$CONN" --check
gw network update --version 4.16.0 --conn "$CONN"

E2E smoke (linked network @ FROM, lockstep upgrade @ TO — see Network E2E):

export CONN='postgresql://user@host:port/giswater_admin_cli'
chmod +x scripts/gw_e2e_*.sh scripts/gw_bootstrap_network.sh
./scripts/gw_e2e_network_update.sh
# or full release suite:
./scripts/gw_e2e_update_all.sh

Fixtures under dbmodel/schemas/**/updates/4/16/0/ (gw_lockstep_*) validate cross-schema ordering.

Legacy commands (deprecated)

The following still work with stderr warnings:

• create — use schema main create or schema addon create / integrate
• update — use schema main update or schema addon update
• drop — use schema main drop or schema addon drop
• status — use network show --flat
• init-db — use db init
• update-network — use network update
• audit structure|activate|drop — use schema addon create|integrate|drop --type audit

See the migration table at the top of this section.

manifest

manifest list

Lists YAML files under --dbmodel-path/manifests/.

python3 -m giswater_admin manifest list
python3 -m giswater_admin manifest list --dbmodel-path ./dbmodel --json

manifest validate

Validates a manifest file.

python3 -m giswater_admin manifest validate dbmodel/manifests/ws.yaml
python3 -m giswater_admin manifest validate dbmodel/manifests/ws.yaml --json

End-to-end example (ws + ud + utils)

set -e
export CONN='postgresql://gisadmin:secret@127.0.0.1:5432/giswater_cli'

gw db init --conn "$CONN"
gw schema main create --type ws --name ws_test --profile sample --conn "$CONN"
gw schema main create --type ud --name ud_test --profile sample --conn "$CONN"
gw schema addon create --type am --profile empty --conn "$CONN"
gw schema addon create --type am --profile sample --conn "$CONN"
gw schema addon integrate --type am --parent ws_test --conn "$CONN"
gw schema addon integrate --type am --profile sample --parent ws_test --conn "$CONN"
gw network show --flat --conn "$CONN" --json | python3 -m json.tool

Kinds and manifest profiles

ws/ud profiles (from manifests/ws.yaml):

Details and folder layout: dbmodel README — Schema architecture.

Timing and output

Example stderr with -v --timing:

── Schema build: ws / gw_ws_test  profile=empty  v4.9.0 ──
[581/723]  phase  updates
[581/723]   1.2s  ws/updates/4/2/0/dml.sql
── Done  10.4s  723 files ──
  updates              7.1s  (612 files, 68.0%)
Slowest:
   3241ms  updates  ws/updates/4/2/0/dml.sql

Paths are shortened using --dbmodel-path as the root prefix.

# Slow SQL files during create
python3 -m giswater_admin create --kind ws --schema gw_ws_test --profile empty \
  --timing --timing-top 30 -v --timing-threshold-ms 30 \
  --conn "$CONN" 2>&1 | tee /tmp/gw_create_timing.log

# JSON timing for jq
python3 -m giswater_admin create --kind ws --schema gw_ws_test --profile empty \
  --timing --timing-detail --json --conn "$CONN" 2>/dev/null | \
  jq '.timing.slowest_by_phase.updates[:20]'

Timing is per SQL file only (not per PL/pgSQL function step).

QGIS integration

The plugin builds BuildParams, runs SchemaBuilder in core/threads/schema_builder_task.py via QtDbAdapter, and can show the same formatted progress in the Giswater PY log. After a build, summarize_build() from engine/timing_report.py feeds the create-project dialog timing label.

Unit tests

No Docker required:

# From plugin repo root
python3 -m pytest test/cli test/engine -v

Optional smoke tests against a real cluster (skipped without PGSERVICE / PGDATABASE):

PGSERVICE=localhost_giswater python3 -m pytest test/engine/smoke -v

Database integration tests: dbmodel/README.md#testing.

Extending the model

1. Add or edit dbmodel/manifests/<kind>.yaml.
2. For a new kind, register it in giswater_admin/cli.py (--kind choices and command validation if needed).
3. Validate: python3 -m giswater_admin manifest validate dbmodel/manifests/<kind>.yaml.

Supported phase types: sql_dir, version_walk (root: or roots:), sql_file, sql_function, sql_inline. dir_walk is deprecated.

Keeping docs in sync

When you add or change CLI flags, update both giswater_admin/cli.py and this README (global options + affected subcommand tables). A future improvement could dump argparse help in CI; that is not automated yet.