mirror of
https://github.com/kevinveenbirkenbach/computer-playbook.git
synced 2026-11-06 10:59:59 +00:00
kevinveenbirkenbachandClaude Opus 4.7
017f67cd53
Replaces controller-side `community.postgresql.postgresql_query` /
`community.mysql.mysql_query` calls with a project-local `database_query`
Ansible module that wraps `container exec -i <container> <psql|mysql>`
and pipes SQL via stdin. The module routes via the DB container's own
network so the same task works identically in shared mode (central
`svc-db-*` container) and non-shared mode (per-role `<entity>-database`
container) — the `container` field of `lookup('database', …)` is
shared-aware and the module just reads it. SQL payloads come from a
file (`query_file:`, preferred) or as a raw string (`query:`, escape
hatch). `named_args:` substitutes `%(name)s` placeholders with engine-
appropriate escaping (postgres single-quote-doubling, mariadb backslash
escapes), so role tasks stop string-interpolating Jinja into SQL.
New backends extend the module's `_ENGINES` dispatch table; everything
else is engine-agnostic. The module ships at `plugins/modules/` (Ansible
collection-style layout) — the shebang lint test learned to recognise
that path as a valid module location alongside the existing per-role
`library/` layout.
Two lint tests enforce the convention:
* `tests/lint/ansible/modules/test_no_raw_db_query_module_usage.py`
forbids `community.postgresql.postgresql_query` /
`community.mysql.mysql_query` in role tasks. Opt out per-line with
`# nocheck: database-query` (used by `svc-db-postgres/tasks/02_init.yml`
where the role manages its own DB and the `database` lookup would be
self-referential).
* `tests/lint/ansible/modules/test_no_raw_database_query_string.py`
forbids inline `query:` on `database_query:` tasks. Opt out with
`# nocheck: database-query-raw` (no opt-outs in the current tree —
every SQL payload reaches the module through a `.sql` file under
`files/sql/`).
Role migrations:
* `web-app-peertube` — 3 tasks migrated, 3 new `.sql` files,
`templates/upsert-oidc-plugin.sql.j2` deleted in favour of
`files/sql/upsert-oidc-plugin.sql` + `named_args:`.
* `web-app-openproject` — 3 tasks migrated, 3 new `.sql` files for
the LDAP-auth-source check/update/insert chain.
* `web-app-odoo` — 33 tasks migrated, 9 new `.sql` files for the
LDAP / admin / init-database flows; 17 pre-existing OIDC `.sql`
files renamed snake_case → kebab-case and relocated from
`files/oidc/` to `files/sql/oidc/` (via `git mv` so rename history
stays intact).
Unit tests cover every dispatch-table entry, the named_args escaper
(strings, ints, floats, bools, None, single-quote and backslash
injection vectors), and the password-redactor that hides
PGPASSWORD/MYSQL_PWD assignments from the module's returned `cmd`
field.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
16 lines
625 B
SQL
16 lines
625 B
SQL
-- Specialised upsert for `ir_config_parameter` rows whose value is the
|
|
-- internal template user's id. The value is computed by a subquery so
|
|
-- the template user's id never has to round-trip through Ansible.
|
|
-- Caller passes the parameter name as %(key)s
|
|
-- (base.template_portal_user_id or auth_signup.template_user_id).
|
|
INSERT INTO ir_config_parameter (key, value, create_date, write_date)
|
|
VALUES (
|
|
%(key)s,
|
|
(SELECT id::text FROM res_users WHERE login = '_oauth_template'),
|
|
NOW(),
|
|
NOW()
|
|
)
|
|
ON CONFLICT (key) DO UPDATE SET
|
|
value = (SELECT id::text FROM res_users WHERE login = '_oauth_template'),
|
|
write_date = NOW();
|