dohertj2/lmxopcua
1
0
Fork 0
Code Issues 5 Pull Requests Actions Packages Projects Releases Wiki Activity

Sql driver #753 follow-ups: ODBC scope, write scope, dual writable flag, deploy-time query validation #781

New Issue
Closed
opened 2026-11-09 17:35:08 -05:00 by dohertj2 · 3 comments
dohertj2 commented 2026-11-09 17:35:08 -05:00
Owner
Copy Link
Copy Source

Follow-ups from lmxopcua#753 (Sql driver: named queries, MySql, Odbc, opt-in writes — master 2331f7ed). Each needs a decision before more code:

  1. ODBC backend scope. The Odbc provider speaks ANSI SQL:2008 (quoted identifiers, ? params, INFORMATION_SCHEMA, FETCH FIRST) — fits Postgres/Snowflake-like sources only; SQL Server/MySQL/DB2 should use native providers. Options: (a) keep narrow + document, (b) per-backend dialect variants, (c) read the catalog via ODBC SQLTables/SQLColumns. Every Odbc host (and AdminUI hosts, for browse) needs a driver manager + vendor driver installed — needs an ops note.
  2. Write scope. Only UPDATE <table> SET <col>=@v WHERE <key>=@k on exactly one existing row. The design's authored write block (Insert / Upsert / stored procedure) is not built. Also undecided: stamp the timestamp column on write? WriteIdempotent is false (no retries). Status mapping: BadNoData on 0 rows, BadCommunicationError on permission denied.
  3. Two writable flags. Tag.Writable and the tag config's "writable": true both exist; no deploy-time warning when they disagree.
  4. Read-only guard limits. Keyword check + always-rollback can't stop non-transactional side effects (sequences, external calls). Docs should require a SELECT-only login.
  5. Undeclared named query is caught at runtime only (tag skipped, reason on /hosts), not at deploy validation.
  6. Oracle integration suite (6 tests) was not run in the #753 evidence.
Follow-ups from lmxopcua#753 (Sql driver: named queries, MySql, Odbc, opt-in writes — master `2331f7ed`). Each needs a decision before more code: 1. **ODBC backend scope.** The Odbc provider speaks ANSI SQL:2008 (quoted identifiers, `?` params, `INFORMATION_SCHEMA`, `FETCH FIRST`) — fits Postgres/Snowflake-like sources only; SQL Server/MySQL/DB2 should use native providers. Options: (a) keep narrow + document, (b) per-backend dialect variants, (c) read the catalog via ODBC `SQLTables`/`SQLColumns`. Every Odbc host (and AdminUI hosts, for browse) needs a driver manager + vendor driver installed — needs an ops note. 2. **Write scope.** Only `UPDATE <table> SET <col>=@v WHERE <key>=@k` on exactly one existing row. The design's authored `write` block (Insert / Upsert / stored procedure) is not built. Also undecided: stamp the timestamp column on write? `WriteIdempotent` is false (no retries). Status mapping: `BadNoData` on 0 rows, `BadCommunicationError` on permission denied. 3. **Two writable flags.** Tag.Writable and the tag config's `"writable": true` both exist; no deploy-time warning when they disagree. 4. **Read-only guard limits.** Keyword check + always-rollback can't stop non-transactional side effects (sequences, external calls). Docs should require a SELECT-only login. 5. **Undeclared named query is caught at runtime only** (tag skipped, reason on `/hosts`), not at deploy validation. 6. Oracle integration suite (6 tests) was not run in the #753 evidence.
dohertj2 added the type:followuppriority:p2 labels 2026-11-09 17:35:08 -05:00
dohertj2 commented 2026-11-09 18:20:15 -05:00
Author
Owner
Copy Link
Copy Source

Items 3 + 5 done on master 241318e7:

  • 5: DraftValidator now rejects a Sql Query tag naming a query that isn't in the driver config's queries map (SqlQueryNotDeclared, always blocking; uses the driver's own SqlEquipmentTagParser over the same merged Driver/Device config the driver binds; ordinal key match). Configuration now references Driver.Sql.Contracts (no cycle).
  • 3: deploy WARNING (never blocking, even in TagConfigValidationMode.Error) when Tag.AccessLevel=ReadWrite and the blob's writable disagree, either direction; logged and appended to the accepted deploy message.
    Evidence (rebased, Release 0 warnings): Configuration 214, ControlPlane 321, AdminUI 1962, OpcUaServer.IntegrationTests 132. 11 + 2 new tests; disabling the new error / warning append turns 3 and 1 tests red, positive controls stay quiet.
    No AdminUI authoring-time check: the tag editor validates one blob without the driver config.

Still open: items 1 (ODBC backend scope), 2 (write scope), 4 (SELECT-only login docs), 6 (Oracle suite run) — decisions/live.

Items 3 + 5 done on master `241318e7`: - **5:** `DraftValidator` now rejects a Sql `Query` tag naming a query that isn't in the driver config's `queries` map (`SqlQueryNotDeclared`, always blocking; uses the driver's own `SqlEquipmentTagParser` over the same merged Driver/Device config the driver binds; ordinal key match). Configuration now references `Driver.Sql.Contracts` (no cycle). - **3:** deploy WARNING (never blocking, even in `TagConfigValidationMode.Error`) when Tag.AccessLevel=ReadWrite and the blob's `writable` disagree, either direction; logged and appended to the accepted deploy message. Evidence (rebased, Release 0 warnings): Configuration 214, ControlPlane 321, AdminUI 1962, OpcUaServer.IntegrationTests 132. 11 + 2 new tests; disabling the new error / warning append turns 3 and 1 tests red, positive controls stay quiet. No AdminUI authoring-time check: the tag editor validates one blob without the driver config. Still open: items 1 (ODBC backend scope), 2 (write scope), 4 (SELECT-only login docs), 6 (Oracle suite run) — decisions/live.
dohertj2 commented 2026-11-09 22:13:24 -05:00
Author
Owner
Copy Link
Copy Source

Owner decision 2026-11-09: item 1 ODBC: (a) keep narrow + document (ANSI SQL:2008 only, native providers for SQL Server/MySQL/DB2, ops note on driver manager + vendor driver on every Odbc/AdminUI host). Item 2 writes: build the full design write block (Insert / Upsert / stored procedure, plus timestamp-stamp option); keep BadNoData / BadCommunicationError mapping and WriteIdempotent=false. Item 4: document the SELECT-only login requirement. Item 6 (Oracle suite) remains live work.

**Owner decision 2026-11-09:** item 1 ODBC: **(a) keep narrow + document** (ANSI SQL:2008 only, native providers for SQL Server/MySQL/DB2, ops note on driver manager + vendor driver on every Odbc/AdminUI host). Item 2 writes: **build the full design write block** (Insert / Upsert / stored procedure, plus timestamp-stamp option); keep BadNoData / BadCommunicationError mapping and WriteIdempotent=false. Item 4: document the SELECT-only login requirement. Item 6 (Oracle suite) remains live work.
dohertj2 commented 2026-11-09 23:04:39 -05:00
Author
Owner
Copy Link
Copy Source

Items 1, 2, 4 and 6 done on master f5c30ed0 (rebased onto 2da58d03). With items 3 and 5 (241318e7), that covers the whole issue, so I'm closing it.

Item 2: the write block. The driver builds every statement itself; there is no authored SQL, every identifier passes the catalog gate, and every value is a bound parameter.

  • mode: Update is the default and is unchanged — a writable tag with no write block produces byte-identical SQL to before, and 0 rows is still BadNoData.
  • Insert writes key + value (+ stamp). On a WideRow topByTimestamp tag it appends a row.
  • Upsert is dialect-specific: SQL Server MERGE … WITH (HOLDLOCK), PostgreSQL INSERT … ON CONFLICT (key) DO UPDATE, MySQL ON DUPLICATE KEY UPDATE (1 or 2 rows count as success), Oracle MERGE … USING (SELECT 1 FROM dual).
  • Procedure calls a named stored procedure. The catalog gate resolves the name against INFORMATION_SCHEMA.ROUTINES / ALL_PROCEDURES and substitutes the catalog's spelling. valueParam / keyParam / timestampParam bind by name; MySQL binds positionally in the order value, key, timestamp. Works on Query tags too.
  • stampTimestamp: true sets the tag's timestampColumn (or topByTimestamp) to the write time in UTC.
  • Odbc serves Update and Insert only. The rule is one shared SqlWriteSupport table read by both the driver and the deploy gate.
  • Status mapping: unique-key violation → BadEntryExists; any other integrity rule (NOT NULL / FK / CHECK) or data exception (overflow / truncation) → BadOutOfRange; everything else, permission denied included, stays BadCommunicationError. Classification uses SQLSTATE, or the engine error number where the provider reports none (SqlClient, ODP.NET). BadNoData and BadConfigurationError are unchanged; WriteIdempotent stays false.
  • Authoring and runtime share one parse. The parser refuses malformed blocks (unknown keys, bad enum, bad parameter-name charset, a mode/model mismatch, a stamp with no column). DraftValidator raises blocking SqlWriteBlockInvalid with the parser's own message, plus SqlWriteModeUnsupported when the mode doesn't fit the merged provider (Upsert or Procedure on Odbc). The AdminUI editor shows the same message.

Items 1 and 4 (docs/drivers/Sql.md).

  • ODBC stays the ANSI SQL:2008 profile only. The doc points SQL Server and MySQL users to their native providers, states DB2 is unsupported, and adds an ops note: every Odbc host and every AdminUI host needs a driver manager plus the vendor's driver, with checks for Linux and Windows and a recommendation for DSN-less connection strings.
  • New Database login (required) section: read-only drivers must use a SELECT-only login. Writable drivers need a login limited to their write targets, with the exact grants listed per mode.

Evidence (Release, 0 warnings, AKKA_TEST_TIMEFACTOR=3):

  • Before rebase: Driver.Sql.Tests 657, Sql.Browser.Tests 84, Configuration 233, AdminUI 1968, ControlPlane 324, OpcUaServer.IntegrationTests 132 — 0 failures.
  • After rebase, rerun: Sql.Tests 657, Configuration 233, AdminUI 1968, OpcUaServer.IntegrationTests 132 — 0 failures.
  • Live, against the 10.100.0.35 fixtures: Sql.IntegrationTests 78 passed / 0 failed. That includes the new SqlWriteModesLiveTests, 7 tests × SQL Server, PostgreSQL, MySQL and Oracle: Update+stamp, absent key → BadNoData, Insert then duplicate → BadEntryExists, NULL into a NOT NULL column → BadOutOfRange, Upsert insert-then-update-then-same-value, procedure call, unknown procedure → gate-rejected. Rows checked directly on each server.
  • Item 6: OracleReadTests 6/6 passed (plus 7 Oracle write tests).
  • ODBC gate (run-odbc-gate.sh, unixODBC + psqlODBC): 4/4, including a new Odbc write leg covering positional Update+stamp, Insert, SQLSTATE 23505 → BadEntryExists, and Upsert → BadNotSupported. The script also gained a startup sleep, because it hit the postgres post-initdb restart once.
  • New tests: 69 in SqlWriteModeTests, 13 in DraftValidatorSqlWriteBlockTests, 2 AdminUI round-trip tests.
  • Mutation-proven, each mutation turning the named tests red:
    • unique → BadCommunicationError: unit + 4 live tests
    • procedure made to obey the row-count rule: PG and Oracle live tests
    • MySQL upsert max rows 1: MySQL upsert live test
    • deploy check unregistered: 6 config tests
    • gate skipping procedure resolution: 5 unit + 6 live tests, which also proves the case substitution
    • positional bind order swapped: ODBC plan test

Fixture changes. The seeds gained write_targets (PK) and set_target(p_value, p_key, p_ts) → proc_calls. Grants are scoped: INSERT/UPDATE on write_targets and EXECUTE on the procedure only. PG and Oracle were re-seeded in place. MySQL was brought up from ~/otopcua-sql-mysql (/opt isn't writable for my user), run, then torn down with down -v.

Items **1, 2, 4 and 6** done on master **`f5c30ed0`** (rebased onto `2da58d03`). With items 3 and 5 (`241318e7`), that covers the whole issue, so I'm closing it. **Item 2: the `write` block.** The driver builds every statement itself; there is no authored SQL, every identifier passes the catalog gate, and every value is a bound parameter. - `mode`: `Update` is the default and is unchanged — a writable tag with no `write` block produces byte-identical SQL to before, and 0 rows is still BadNoData. - `Insert` writes key + value (+ stamp). On a WideRow `topByTimestamp` tag it appends a row. - `Upsert` is dialect-specific: SQL Server `MERGE … WITH (HOLDLOCK)`, PostgreSQL `INSERT … ON CONFLICT (key) DO UPDATE`, MySQL `ON DUPLICATE KEY UPDATE` (1 or 2 rows count as success), Oracle `MERGE … USING (SELECT 1 FROM dual)`. - `Procedure` calls a named stored procedure. The catalog gate resolves the name against `INFORMATION_SCHEMA.ROUTINES` / `ALL_PROCEDURES` and substitutes the catalog's spelling. `valueParam` / `keyParam` / `timestampParam` bind by name; MySQL binds positionally in the order value, key, timestamp. Works on Query tags too. - `stampTimestamp: true` sets the tag's `timestampColumn` (or `topByTimestamp`) to the write time in UTC. - Odbc serves **Update and Insert only**. The rule is one shared `SqlWriteSupport` table read by both the driver and the deploy gate. - Status mapping: unique-key violation → **BadEntryExists**; any other integrity rule (NOT NULL / FK / CHECK) or data exception (overflow / truncation) → **BadOutOfRange**; everything else, permission denied included, stays **BadCommunicationError**. Classification uses SQLSTATE, or the engine error number where the provider reports none (SqlClient, ODP.NET). BadNoData and BadConfigurationError are unchanged; `WriteIdempotent` stays false. - Authoring and runtime share one parse. The parser refuses malformed blocks (unknown keys, bad enum, bad parameter-name charset, a mode/model mismatch, a stamp with no column). `DraftValidator` raises blocking **SqlWriteBlockInvalid** with the parser's own message, plus **SqlWriteModeUnsupported** when the mode doesn't fit the merged `provider` (Upsert or Procedure on Odbc). The AdminUI editor shows the same message. **Items 1 and 4 (docs/drivers/Sql.md).** - ODBC stays the ANSI SQL:2008 profile only. The doc points SQL Server and MySQL users to their native providers, states DB2 is unsupported, and adds an ops note: every Odbc host and every AdminUI host needs a driver manager plus the vendor's driver, with checks for Linux and Windows and a recommendation for DSN-less connection strings. - New *Database login (required)* section: read-only drivers must use a SELECT-only login. Writable drivers need a login limited to their write targets, with the exact grants listed per mode. **Evidence** (Release, 0 warnings, `AKKA_TEST_TIMEFACTOR=3`): - Before rebase: Driver.Sql.Tests 657, Sql.Browser.Tests 84, Configuration 233, AdminUI 1968, ControlPlane 324, OpcUaServer.IntegrationTests 132 — 0 failures. - After rebase, rerun: Sql.Tests 657, Configuration 233, AdminUI 1968, OpcUaServer.IntegrationTests 132 — 0 failures. - Live, against the 10.100.0.35 fixtures: Sql.IntegrationTests **78 passed / 0 failed**. That includes the new `SqlWriteModesLiveTests`, 7 tests × SQL Server, PostgreSQL, MySQL and Oracle: Update+stamp, absent key → BadNoData, Insert then duplicate → BadEntryExists, NULL into a NOT NULL column → BadOutOfRange, Upsert insert-then-update-then-same-value, procedure call, unknown procedure → gate-rejected. Rows checked directly on each server. - **Item 6:** `OracleReadTests` 6/6 passed (plus 7 Oracle write tests). - ODBC gate (`run-odbc-gate.sh`, unixODBC + psqlODBC): 4/4, including a new Odbc write leg covering positional Update+stamp, Insert, SQLSTATE 23505 → BadEntryExists, and Upsert → BadNotSupported. The script also gained a startup sleep, because it hit the postgres post-initdb restart once. - New tests: 69 in `SqlWriteModeTests`, 13 in `DraftValidatorSqlWriteBlockTests`, 2 AdminUI round-trip tests. - Mutation-proven, each mutation turning the named tests red: - unique → BadCommunicationError: unit + 4 live tests - procedure made to obey the row-count rule: PG and Oracle live tests - MySQL upsert max rows 1: MySQL upsert live test - deploy check unregistered: 6 config tests - gate skipping procedure resolution: 5 unit + 6 live tests, which also proves the case substitution - positional bind order swapped: ODBC plan test **Fixture changes.** The seeds gained `write_targets` (PK) and `set_target(p_value, p_key, p_ts)` → `proc_calls`. Grants are scoped: INSERT/UPDATE on `write_targets` and EXECUTE on the procedure only. PG and Oracle were re-seeded in place. MySQL was brought up from `~/otopcua-sql-mysql` (`/opt` isn't writable for my user), run, then torn down with `down -v`.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
auto-managed
queue management
 blocked
Blocked by dependency or external hold
 cross-driver
queue management
 cross-repo
Needs coordinated change in another family repo (wire contract etc.)
 driver/abcip
queue management
 driver/ablegacy
queue management
 driver/focas
queue management
 driver/mqtt
MQTT / Sparkplug B broker-subscribe driver
 driver/opcuaclient
queue management
 driver/s7
queue management
 driver/twincat
queue management
 migrated
Migrated from a markdown backlog (2026-11 standardization)
 phase/1
queue management
 phase/2
queue management
 phase/3
queue management
 phase/4
queue management
 phase/5
queue management
 phase/6
queue management
 priority:p0
Must-have / blocking
 priority:p1
High priority
 priority:p2
Normal priority
 priority:p3
Low priority / nice to have
 queue/blocked
queue management
 queue/done
queue management
 queue/failed
queue management
 queue/in-progress
queue management
 queue/queued
queue management
 type:bug
Defect in shipped behaviour
 type:debt
Technical debt / hygiene
 type:deploy-gate
Rollout/deploy/migration step not yet done on an environment
 type:docs
Documentation work
 type:feature
Feature implementation
 type:followup
Follow-up deferred from a plan, PR or gate
 type:infra
Infrastructure and scaffolding
 type:review-finding
Unfixed finding from an architecture/code review
 type:test
Test work
No Label priority:p2 type:followup
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
dohertj2 (dohertj2)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: dohertj2/lmxopcua#781
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?