Test Generator → Python web

Current width is varchar(64) — sized for SHA-256 hex, which is exactly 64 characters. Argon2id's encoded form runs to roughly 95–100 characters. Widen to varchar(255), which leaves room for any PHC-string algorithm and costs nothing on any of the five engines.

Decision Argon2id, since the width constraint is lifted. Migration is lazy, not bulk: existing MD5/SHA-256 values stay; on the next successful login the verified plaintext is re-hashed with Argon2id and written back. A prefix check ($argon2id$) selects the verifier. Bulk re-hashing is impossible — the plaintext is not recoverable from the old hashes.

The old MD5/SHA-256 acceptance path must stay until every active account has logged in once. Add a report to the admin app showing how many accounts remain on legacy hashes, so there is a defensible moment to switch the old path off.

Per Q4: add a converted column beside each RTF column; fill it lazily on first read; read only the converted value afterwards. The columns that hold RTF, verified against the schema:

The trigger is content-sniffed, not column-typed. RTFUtils.convertToHtml() tests s.startsWith("{\\rtf") and passes anything else through as escaped plain text. Any of these columns may legitimately hold plain text. The Python converter must replicate that check — converting a non-RTF string as if it were RTF produces garbage.

The schema has zero foreign keys today — verified by grep across the extracted DDL. Adding them is the single largest stability improvement available, and it is the change most likely to fail on real customer data, because nothing has ever prevented orphans from accumulating.

The conversion step must therefore be three stages, not one:

1. Audit. For every intended FK, count violating rows first. Ship this as a read-only report the customer can run before upgrading — select count(*) from detail d where not exists (select 1 from summary s where s.su_id = d.su_id) and its equivalents. No ALTER runs until the audit is clean or the disposition is agreed.
2. Resolve. Orphans are either deleted or re-parented. This is a customer decision per table — orphaned detail rows are junk, but orphaned summary rows are somebody's exam result and deleting them silently is not acceptable.
3. Constrain. Only then add the constraints, engine by engine.

Start with the relationships that are unambiguous and high-value: detail.su_id → summary.su_id, summary.tst_id → tests.tst_id, summary.ta_id → takers.ta_id, tg_questions.tst_id → tests.tst_id, sections.tst_id → tests.tst_id, summary_section.su_id → summary.su_id, and the join tables takers_classes / tests_classes / takers_tests / takers_topics.

Explicitly excluded: the four tmp_* scratch tables and surveyorder. They are populated and truncated per report run (§09) and constraining them would only add lock contention. Also excluded are the five tables with no primary key at all — admin, logobin, mediabin, standalone, uld_history — which cannot be an FK target until they have one.

Per the finding in §01, clsPostgres.updateDB is a stub that can only assert "already at 2.4.20". Since 2.4.21 introduces the first-ever PostgreSQL schema change, that stub must become a real version block or PostgreSQL customers cannot be upgraded at all.

The good news is the chain is short: PostgreSQL databases can only exist at 2.4.20, so clsPostgres needs exactly one block — 2.4.20.0 → 2.4.21.0 — not the twenty its siblings carry.

Generate db/schema.py from the extracted SQL rather than hand-writing it, so the Python definition is provably in step with the vendor DDL. Alongside it, one dialect module implementing the §02 matrix: boolean encoding, date literals, blob access, identifier quoting, and ID allocation.

Do not use runtime reflection. It behaves unpredictably on the five PK-less tables, hides lo columns, and removes the schema from code review.

This is the easiest thing to get wrong, because the engines split into two camps and the code must do both:

# Oracle and PostgreSQL — fetch first, then insert explicitly
new_id = conn.execute("select nextval('takers_ta_id_seq')").scalar()
conn.execute(insert(takers).values(ta_id=new_id, …))

# SQL Server, MySQL, Access — insert, then read back
conn.execute(insert(takers).values(…))
new_id = conn.execute("select @@identity").scalar()   # or last_insert_id()

The 16 sequences are free-standing on PostgreSQL — not SERIAL, not GENERATED, not column defaults. Keep it that way; the pre-allocated ID is used to build dependent rows inside the same transaction. After any data load, every sequence's last_value must be ≥ max() of its column, mirroring clsPostgres.doIndexUpdateDbPostgres().

The concrete payoff of the move, and the precondition for 2.4.21-c. Today "start an attempt" writes summary, then detail, then locks, with no transaction spanning them — because the original target was Jet, which had none. Wrap each of these as one unit:

• Begin attempt — insert summary, seed detail, acquire the locks row.
• Submit answer — update detail, recompute summary aggregates.
• Finish attempt — set su_finished, write summary_section, release lock, append to logs.
• Report run — fill and read the tmp_* tables inside one transaction (§09).

Decision READ COMMITTED, with SELECT … FOR UPDATE on the locks row to serialise concurrent entry into the same test. On MySQL this requires InnoDB — which the schema already specifies, though via the obsolete type=innodb syntax that needs updating.

Convert each .sql template into a Python function returning a SQLAlchemy Core construct with bound parameters. Escape-mode usage across the corpus: integer 231, html 165, sql 92, js 24, date 23, bool 21, decimal 15, url 2.

Decision Do not port the template engine. Binding parameters instead of substituting strings makes SQL injection structurally impossible rather than dependent on 92 correct annotations — and it also deletes the backslash bug below by construction.

The 40 Crystal .rpt files are a closed binary format with no Python reader — treat them as unrecoverable layouts. The queries, which carry the business logic, are plain VB string literals in TGReports/modReportSQL.bas: 32 getTGRT_* functions, each returning complete SQL with a selstring filter spliced in. Extract all 32 mechanically and parameterise that splice properly.

The 8 .jrxml JasperReports files are XML and are readable — taker summary, test summary, test detail, course summary, each with a Chinese variant. They give the intended web layouts directly.

Cross-engine caveat: the recovered SQL uses Access-style nested parenthesised joins and unqualified date arithmetic. It runs on PostgreSQL as-is, but each of the 32 needs verifying on Oracle and SQL Server too — this is the part of the port most likely to hide dialect bugs.

Item analysis and several other reports do not query live tables. They read tmp_questions, tmp_answer, tmp_summary and surveyorder, which the application fills and then deletes:

' MainMenu/modReports.bas:181-184
delete from tmp_questions where currusr_id = <user id>
delete from tmp_summary   where currusr_id = <user id>
delete from surveyorder   where currusr_id = <user id>
delete from tmp_answer    where currusr_id = <user id>

currusr_id is the only thing separating concurrent users' intermediate results, and it is part of the composite primary key on all four tables. Adequate for a single-user desktop app; not adequate for a web app with a connection pool. It must be bound to the session, filled and consumed inside one transaction, and cleaned up in a finally. Get this wrong and reports silently return another user's rows — with no error.

Rewrite SecureTG.exe as a Python desktop app — PySide6 + QtWebEngine — which embeds Chromium and loads the Python web tester. Genuinely Python, while keeping the native privileges the job requires.

Today the kiosk proves itself with two constant MD5 hashes in HTTP headers — MD5("TGAdmin") in X-Tester and MD5("SecureTG") as User-Agent — and the server accepts Referer as a fallback. Anything that can set two headers is indistinguishable from a locked-down exam machine. Replace with either:

1. Per-session HMAC — server issues a nonce; the kiosk returns HMAC(device_key, nonce ‖ session_id) per request. Device keys provisioned per machine, revocable. No PKI.
2. Mutual TLS — client certificate per kiosk, verified at the reverse proxy. Stronger, at the cost of running a small CA.

Either way the server records which kiosk an attempt came from, which the current design cannot do at all.