Coverage for gws-app/gws/base/database/__init__.py: 100%
1 statements
« prev ^ index » next coverage.py v7.17.0, created at 2026-10-23 20:06 +0200
1"""Database support.
3Base classes for database connections and for the objects that read and
4write database tables: layers, models and authorization providers. Concrete
5implementations (for PostgreSQL/PostGIS) live in ``gws.plugin.postgres`` and
6extend the classes in this package.
8Submodules
9----------
11- ``manager`` - the database manager (``root.app.databaseMgr``). It creates
12 the providers configured under ``database.providers`` and finds them by uid
13 or type. A provider that fails to configure is logged and skipped. The
14 manager registers itself as the ``db`` middleware.
15- ``provider`` - the base database provider. It wraps an SQLAlchemy ``Engine``,
16 hands out connections, reflects and caches table structures and describes
17 tables and columns.
18- ``connection`` - the connection object yielded by ``provider.begin()``,
19 a thin wrapper around an SQLAlchemy ``Connection`` with fetch helpers.
20- ``model`` - the base database model, which reads features with a SELECT built
21 by the model fields and creates, updates and deletes table rows.
22- ``layer`` - the base vector layer for a database table, with a default model
23 and finder for the table. The geometry type and CRS are taken from the table
24 description. Without configured models, the layer gets a default model. If
25 no extent is configured, it is computed from the table bounds. Unless
26 finders are configured or search is disabled, the layer gets a default
27 finder.
28- ``auth_provider`` - the base authorization provider that checks users with
29 SQL queries.
30- ``util`` - helpers for where clauses: ``text_search_clause`` for a search
31 string with ``gws.TextSearchOptions``, ``shape_intersects_clause`` for a
32 search shape.
34Providers and connections
35-------------------------
37Layers, models and other objects refer to a provider with ``dbUid``. Without
38``dbUid`` they use the default provider passed by their parent or the first
39provider of their type (see ``gws.config.util.configure_database_provider_for``).
41A provider keeps one SQLAlchemy connection per thread. ``begin()`` opens it,
42begins a transaction and yields the connection. The outermost ``begin()``
43commits on normal exit, rolls back on an exception and closes the connection.
44Inner calls join the open transaction, or create a savepoint with
45``nested=True``::
47 with db.begin() as conn:
48 conn.execute('INSERT INTO my_table (id) VALUES (:id)', {'id': 1})
49 with db.begin() as conn2:
50 # the same connection and transaction, sees the insert
51 n = conn2.fetch_int('SELECT count(*) FROM my_table')
52 # committed
54The connection itself has no ``commit()`` or ``rollback()``. To catch a
55database error and continue in the same transaction, wrap the statement in a
56savepoint::
58 try:
59 with db.begin(nested=True) as conn:
60 conn.execute('INSERT ...')
61 except sa.Error:
62 ...
64Statements that cannot run in a transaction, like ``VACUUM``, use
65``autocommit_connection()``, which is a separate connection.
67The engine is created on activation and is not pickled. Connection pooling is
68off unless ``withPool`` is set; the ``pool`` options ``disabled``,
69``pre_ping``, ``size``, ``recycle`` and ``timeout`` are passed to the engine.
71Table structures are reflected per schema and cached for
72``schemaCacheLifeTime`` seconds. ``describe`` and ``describe_column`` turn
73the reflected structure into ``gws.DataSetDescription`` and
74``gws.ColumnDescription`` objects, which models use to derive their fields.
76Models
77------
79A database model builds its SELECT from the contributions of its fields
80(``mc.dbSelect``), the search query (uids, keyword, shape, sort, limit, extra
81conditions and columns) and the configured ``sqlFilter``. If the search asks
82for uids, a keyword or a shape, but the table has no primary key or no field
83contributes a matching condition, nothing is selected. Writes call the field
84hooks (``before_create``, ``after_create`` and so on) around a single INSERT,
85UPDATE or DELETE and commit the connection. Reads and writes check the user's
86permissions on the model and raise ``gws.ForbiddenError`` if they are
87missing. Composite primary keys are not supported. See ``gws.base.model`` for
88the data flow between records, features and props.
90SQL authorization
91-----------------
93The authorization provider runs two configured SELECT queries:
95- ``authorizationSql`` receives the placeholders ``{username}``, ``{password}``
96 and ``{token}`` from an authentication method. If it returns no rows, the
97 provider does not know the user and the next provider is tried. More than one
98 row is an error. Otherwise the row must contain the columns ``validuser``
99 and ``validpassword`` (booleans, both must be true to log in) and ``uid``
100 (the user id). The column ``roles`` is a comma-separated list of roles.
101- ``getUserSql`` receives the placeholder ``{uid}`` and returns the record of
102 this user, for example to restore a user from a session.
104The column names ``validuser``, ``validpassword`` and ``uid`` are
105case-insensitive. Other columns are passed to ``gws.base.auth.user.from_record``.
106In the config file the braces of the placeholders are doubled.
108Example::
110 database.providers+ {
111 uid "main_db"
112 type "postgres"
113 serviceName "LOCAL"
114 }
116 map.layers+ {
117 type "postgres"
118 dbUid "main_db"
119 tableName "edit.poi"
120 models+ {
121 type "postgres"
122 isEditable true
123 }
124 }
126 auth.providers+ {
127 type "postgres"
128 dbUid "main_db"
130 authorizationSql '''
131 SELECT
132 user.id
133 AS uid,
134 user.first_name || ' ' || user.last_name
135 AS displayname,
136 user.login
137 AS login,
138 user.is_enabled
139 AS validuser,
140 ( passwd = crypt({{password}}, passwd) )
141 AS validpassword
142 FROM
143 public.user
144 WHERE
145 user.login = {{username}}
146 '''
148 getUserSql '''
149 SELECT
150 user.id
151 AS uid,
152 user.first_name || ' ' || user.last_name
153 AS displayname,
154 user.login
155 AS login
156 FROM
157 public.user
158 WHERE
159 user.id = {{uid}}
160 '''
161 }
163The ``authorizationSql`` example assumes Postgres with the ``pgcrypto`` extension.
164"""
166from . import provider, manager, model