recon_gen.common.sql.dialect

Dialect-specific SQL helpers — Phase P.2 catalog + P.3 Oracle fill + P.3.e cleanup + X.3 SQLite.

Every helper takes an explicit Dialect (no defaults — see “P.3.e cleanup” below) and returns a dialect-appropriate SQL string. The helpers split into two output shapes:

Statement helpers — return a fully terminated, self-contained statement. Postgres branch ends in ;; Oracle branch wraps in a PL/SQL block ending in END;. Callers concatenate without appending a separate ;.

drop_table_if_exists

drop_matview_if_exists

drop_index_if_exists

drop_view_if_exists

refresh_matview

analyze_table

Fragment helpers — return an expression-level SQL string with no trailing ;. Substituted into larger SQL templates by the caller, which decides where the statement boundary lives.

Type names (serial_type, timestamp_type, text_type, varchar_type, decimal_type, boolean_type, decimal_type)

Casts (cast, typed_null, to_date, date_trunc_day)

Date arithmetic (epoch_seconds_between, interval_days, date_minus_days)

Constraints (json_check)

Other clauses (with_recursive, create_matview, matview_options)

Phase notes: - P.2 shipped every helper with a Postgres branch only. - P.3 filled in the Oracle branches (19c Standard Edition target). - P.3.e dropped the dialect: Dialect = Dialect.POSTGRES defaults

so call sites must be explicit. Every in-tree caller already passed a dialect by then; defaults were a rollout convenience that’s no longer load-bearing.

_matview_options moved into this module from common.l2.schema in P.3.e — it’s a pure dialect helper and belongs alongside create_matview / refresh_matview.
X.3 added the SQLite branch (3.38+, ships with stdlib sqlite3). SQLite has no CREATE MATERIALIZED VIEW: matviews are emitted as CREATE TABLE … AS SELECT and refreshed by DELETE + INSERT. JSON metadata uses SQLite’s JSON1 functions (json_valid, json_extract); the IS JSON constraint becomes a CHECK (json_valid(col)). The dialect is the local-iteration loop’s storage — single-file or in-memory, no server required.

Functions

`analyze_table`(name, dialect)	Refresh planner statistics on a table or matview.
`bigint_type`(dialect)	Signed 64-bit integer column type.
`boolean_type`(dialect)	Boolean column type.
`cast`(expr, type_name, dialect)	Cast `expr` to `type_name`.
`column_name`(name, dialect)	Per-dialect natural case for an unquoted column identifier.
`concat_agg`(column_expr, separator, dialect)	Aggregate text values from a group into a delimited string.
`create_matview`(name, body_sql, dialect)	`CREATE MATERIALIZED VIEW` with the right options per dialect.
`date_literal`(iso_value, dialect)	A SQL date literal that compares correctly on every dialect.
`date_minus_days`(date_expr, n, dialect)	Subtract `n` days from a date expression.
`date_trunc_day`(timestamp_expr, dialect)	Truncate a timestamp expression to its day boundary, preserving a timestamp-shaped result type so downstream JOINs against TIMESTAMP columns don't fall through implicit conversion.
`day_text`(timestamp_expr, dialect)	Render a timestamp expression as its `YYYY-MM-DD` day string.
`decimal_type`(precision, scale, dialect)	Fixed-precision decimal.
`drop_index_if_exists`(name, dialect)	Idempotent DROP INDEX.
`drop_matview_if_exists`(name, dialect)	Idempotent DROP MATERIALIZED VIEW.
`drop_table_if_exists`(name, dialect)	Idempotent DROP TABLE — emits CASCADE so dependent FKs / views drop transitively (where the dialect supports CASCADE).
`drop_view_if_exists`(name, dialect)	Idempotent DROP VIEW.
`dual_from`(dialect)	Suffix that makes a constant SELECT valid on both dialects.
`epoch_seconds_between`(later, earlier, dialect)	Difference between two timestamps in whole + fractional seconds.
`fetch_first_one_row`(dialect)	Return the row-limiting clause that picks the first row of an ORDER BY — `LIMIT 1` for Postgres + SQLite, `FETCH FIRST 1 ROW ONLY` for Oracle.
`greatest`(*args, dialect)	Row-wise greatest of two or more expressions.
`interval_days`(n, dialect)	A SQL interval literal of `n` days.
`json_array_iterate`(json_expr, array_path, *, ...)	`LEFT JOIN` clause that iterates a JSON array within `json_expr` at `array_path`, exposing each element under `alias.value` (and `alias.key` on SQLite — unused but harmless).
`json_check`(col, dialect)	`CHECK (col IS NULL OR col IS JSON)` in PG / Oracle; SQLite uses `CHECK (col IS NULL OR json_valid(col))`.
`json_field_extract`(value_expr, field_path, ...)	Extract a scalar field from a per-row JSON element (e.g. one iteration of json_array_iterate).
`json_text_type`(dialect)	Bounded text type for columns holding JSON metadata documents.
`json_value`(col, path_expr, dialect)	Extract a scalar from a JSON-shaped column via SQL/JSON path.
`lob_substr`(expr, n, dialect)	Coerce a (potentially CLOB-typed) expression to a bounded VARCHAR by extracting its first `n` characters.
`matview_create_keyword`(dialect)	The `CREATE …` keyword the matview templates emit per dialect.
`matview_options`(dialect)	Per-dialect suffix between `CREATE MATERIALIZED VIEW <name>` and `AS <body>`.
`order_by_day_expr`(day_col, dialect)	Per-dialect `ORDER BY` projection for date-keyed window functions paired with `range_interval_days`.
`range_interval_days`(n, dialect)	Day-interval expression for use inside a window-function `RANGE BETWEEN <expr> PRECEDING` clause.
`refresh_matview`(name, dialect)	`REFRESH MATERIALIZED VIEW` per dialect.
`serial_type`(dialect)	Auto-incrementing 64-bit append-only key.
`text_type`(dialect)	Unbounded character data.
`timestamp_type`(dialect)	TZ-naive timestamp, identical on both dialects.
`to_date`(timestamp_expr, dialect)	Truncate a timestamp expression to its date component.
`typed_null`(type_name, dialect)	Typed NULL literal.
`varchar_type`(n, dialect)	Bounded variable-length character.
`with_recursive`(dialect)	Recursive-CTE preamble keyword.

Classes

Dialect(*values)

Target SQL dialect.

class recon_gen.common.sql.dialect.Dialect(*values)[source]

Bases: str, Enum

Target SQL dialect.

Postgres covers Postgres 17+ (the version floor required by the SQL/JSON path syntax we already use). Oracle covers Oracle 19c Standard Edition (the long-term-support version Phase P targets). SQLite covers SQLite 3.38+ (the version floor for the JSON1 functions we depend on; ships with Python’s stdlib sqlite3). DuckDB is in-process (in-Python wheel), columnar, with native MATERIALIZED VIEW + a vectorized executor that handles correlated-subquery rewrites natively (per the CA.0 spike).

Phase CA in progress. DUCKDB is being added as part of the CA.0 → CA.8 swap that replaces SQLITE. During the migration both enum values coexist; CA.8 deletes SQLITE. Each dialect-dispatch helper gets its DuckDB branch in CA.2; until then, DUCKDB may silently fall through to the Oracle fallback shape in helpers that haven’t been ported — exercising it without that port will misbehave. The CA.0 audit at docs/audits/ca_0_duckdb_spike.md is the source of truth for each helper’s DuckDB binding.

DUCKDB = 'duckdb'

ORACLE = 'oracle'

POSTGRES = 'postgres'

recon_gen.common.sql.dialect.analyze_table(name, dialect)[source]

Refresh planner statistics on a table or matview.

Postgres: ANALYZE name;. Oracle: BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, 'name'); END;. SQLite: ANALYZE name; (same syntax as Postgres). Returned string is fully terminated.

Return type:

str

Parameters:

name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.bigint_type(dialect)[source]

Signed 64-bit integer column type.

Postgres + SQLite BIGINT (SQLite accepts BIGINT as an alias of INTEGER affinity — both store as 64-bit when needed). Oracle has no BIGINT; NUMBER(19) is the canonical signed-64-bit column type (covers the full ±9.2×10^18 BIGINT range).

Introduced for AO.1’s money-as-integer-cents migration; the three money columns (amount_money / money / expected_eod_balance) use this type instead of DECIMAL(20,2) so SQLite stores them as exact INTEGER (no REAL float dust) and the read-side cents_to_dollars_sql helper projects back to dollars when needed.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.boolean_type(dialect)[source]

Boolean column type.

Postgres has a native BOOLEAN; Oracle 19c does not, so the canonical encoding is NUMBER(1) with a CHECK (col IN (0, 1)). SQLite has no native BOOLEAN — uses INTEGER (0/1) by convention. The helper returns just the type name — callers that need the CHECK constraint compose it themselves.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.cast(expr, type_name, dialect)[source]

Cast expr to type_name.

Postgres expr::type / Oracle CAST(expr AS type) / SQLite CAST(expr AS type) (SQLite uses standard SQL CAST syntax; type-name aliasing remaps Postgres-shape names to SQLite affinity names where they differ).

Return type:

str

Parameters:

expr (str)
type_name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.column_name(name, dialect)[source]

Per-dialect natural case for an unquoted column identifier.

Postgres + SQLite case-fold unquoted identifiers to lowercase; Oracle case-folds to UPPERCASE. Storing column names in the dialect’s natural case lets every reference (DDL column, dataset SQL projection, App2 outer-SELECT alias, QuickSight Columns declaration) use the same string without quote-juggling — and lets unquoted refs in dataset SQL pick up the matching column on every dialect.

Used at every site that emits a column name into either SQL output or the QuickSight Dataset.Columns metadata: callers pass the canonical lowercase column name from the contract, this helper folds to the dialect’s natural case, and the result is the string that both the database and QuickSight will agree on.

The bridge this replaces (_oracle_lowercase_alias_wrapper in common/dataset_contract.py) wrapped Oracle dataset SQL in an outer alias-rename to back-port UPPERCASE → quoted-lowercase for QS. The wrapper produced an asymmetry App2’s wrap_for_visual couldn’t follow (ORA-00904: "ACCOUNT_ID": invalid identifier), and every new dialect-port surfaced fresh case-confusion edge cases — see Y.3.f for the full bridge-removal plan.

Return type:

str

Parameters:

name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.concat_agg(column_expr, separator, dialect)[source]

Aggregate text values from a group into a delimited string.

First introduced for AB.3.3’s _xor_group_violation matview (fired_rails column — comma-separated rail names per Transfer). Dialect mapping:

Postgres: STRING_AGG(col, ',') — null-safe (NULLs skipped), no implicit ordering.
Oracle: LISTAGG(col, ',') WITHIN GROUP (ORDER BY col) — deterministic ordering by value; truncation at 4000 chars by default (acceptable for fired-rails: bounded by leg_rails count).
SQLite: GROUP_CONCAT(col, ',') — null-safe, no implicit ordering (matches PG’s contract).

separator is wrapped in single quotes; callers pass the bare delimiter (e.g., ',' or ', '). column_expr is interpolated raw — caller’s responsibility to quote / cast as appropriate for the dialect.

Return type:

str

Parameters:

column_expr (str)
separator (str)
dialect (Dialect)

recon_gen.common.sql.dialect.create_matview(name, body_sql, dialect)[source]

CREATE MATERIALIZED VIEW with the right options per dialect.

Postgres: bare CREATE MATERIALIZED VIEW name AS body (build- on-create + manual refresh are the defaults). Oracle: explicit BUILD IMMEDIATE REFRESH ON DEMAND so behavior matches the Postgres expectation; without those options Oracle defaults to REFRESH FORCE ON DEMAND (incremental fast-refresh attempt first), which has more setup requirements. SQLite has no CREATE MATERIALIZED VIEW: emits CREATE TABLE name AS body (the matview becomes a plain table populated at create time). Refresh becomes DELETE FROM name; INSERT INTO name <body>; (see refresh_matview).

Note this helper does NOT add a trailing ; — it’s a one-shot convenience for callers that want the whole CREATE in one string, but most callers in this codebase splice the BUILD IMMEDIATE … suffix into a template via matview_options(dialect) instead so the SELECT body stays inline + readable.

Return type:

str

Parameters:

name (str)
body_sql (str)
dialect (Dialect)

recon_gen.common.sql.dialect.date_literal(iso_value, dialect)[source]

A SQL date literal that compares correctly on every dialect.

iso_value is the YYYY-MM-DD string form (caller produces it via date.isoformat()). The helper wraps it in the per-dialect syntax that produces a value comparable against the dialect’s DATE / TIMESTAMP / TEXT-shaped date columns.

Postgres + Oracle: DATE 'YYYY-MM-DD' — the SQL-standard date literal, accepted by both. Compares natively against DATE and coerces correctly against TIMESTAMP columns.

SQLite: 'YYYY-MM-DD' — a plain text literal. SQLite has no native DATE type and stores ISO dates as TEXT; lexicographic TEXT comparison happens to be correct for ISO-8601 (‘2030-01-01’ < ‘2030-01-02’ lexically, same as date-wise). The SQL-standard DATE 'literal' form is rejected by SQLite (parses DATE as a column reference), and CAST('YYYY-MM-DD' AS DATE) coerces to INTEGER 2030 (NUMERIC affinity extracts the leading digits) — both are wrong for SQLite. Use this helper instead of inline string formatting at every audit / matview / dataset SQL site that needs a date literal in a WHERE / CASE WHEN comparison.

Return type:

str

Parameters:

iso_value (str)
dialect (Dialect)

recon_gen.common.sql.dialect.date_minus_days(date_expr, n, dialect)[source]

Subtract n days from a date expression.

Postgres uses date - INTERVAL '<n> day'; Oracle’s DATE arithmetic interprets date - n as N days directly. SQLite uses date(expr, '-N days').

Return type:

str

Parameters:

date_expr (str)
n (int)
dialect (Dialect)

recon_gen.common.sql.dialect.date_trunc_day(timestamp_expr, dialect)[source]

Truncate a timestamp expression to its day boundary, preserving a timestamp-shaped result type so downstream JOINs against TIMESTAMP columns don’t fall through implicit conversion.

Postgres DATE_TRUNC('day', expr) returns the same type as the input (TIMESTAMP → TIMESTAMP at 00:00:00). Oracle’s TRUNC(X) on a TIMESTAMP returns a DATE, which loses subseconds + the timestamp shape; wrapping in CAST(... AS TIMESTAMP) puts it back in the timestamp domain so the L1 invariant matviews compare equality the same way on both dialects. SQLite uses datetime(expr, 'start of day') — returns YYYY-MM-DD HH:MM:SS text at midnight, sortable + groupable + JOIN-able against the other text-shaped timestamp columns.

Distinct from to_date (which returns DATE-shape on both): use date_trunc_day when the result needs to behave as a timestamp in joins / comparisons; use to_date when the result is the final column the dashboard reads as a date.

Return type:

str

Parameters:

timestamp_expr (str)
dialect (Dialect)

recon_gen.common.sql.dialect.day_text(timestamp_expr, dialect)[source]

Render a timestamp expression as its YYYY-MM-DD day string.

Unlike date_trunc_day() (which keeps a timestamp-shaped result for JOIN/equality against TIMESTAMP columns), this collapses to a plain text day key for comparisons that must tolerate either side being a string. Used by the Daily Statement balance-date narrow (AO.2/AO.10): the pushed-down <<$pL1DsBalanceDate>> param arrives as an ISO string in every renderer, and TRUNC(<string>) is an ORA-00932 on Oracle — comparing day-text sidesteps it. Pair with SUBSTR(<param>, 1, 10) on the param side (date or datetime → its YYYY-MM-DD prefix); ISO day strings also compare correctly with < / >= lexically.

Postgres + Oracle: TO_CHAR(expr, 'YYYY-MM-DD'). SQLite + DuckDB: strftime('%Y-%m-%d', expr) (DuckDB has no TO_CHAR in the core build — strftime ships natively and accepts the same Postgres-style %Y-%m-%d format spec; SQLite stores datetimes as YYYY-MM-DD HH:MM:SS text → its date portion).

Return type:

str

Parameters:

timestamp_expr (str)
dialect (Dialect)

recon_gen.common.sql.dialect.decimal_type(precision, scale, dialect)[source]

Fixed-precision decimal.

Postgres DECIMAL(p,s) / Oracle NUMBER(p,s) / SQLite NUMERIC (SQLite is typeless and stores all numerics as one of INTEGER / REAL / TEXT per its dynamic typing rules; NUMERIC is the affinity that prefers exact representation).

Return type:

str

Parameters:

precision (int)
scale (int)
dialect (Dialect)

recon_gen.common.sql.dialect.drop_index_if_exists(name, dialect)[source]

Idempotent DROP INDEX.

Postgres DROP INDEX IF EXISTS …; / Oracle PL/SQL block catching ORA-01418 (index does not exist) / SQLite native DROP INDEX IF EXISTS …;. Returned string is fully terminated.

Return type:

str

Parameters:

name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.drop_matview_if_exists(name, dialect)[source]

Idempotent DROP MATERIALIZED VIEW.

Postgres DROP MATERIALIZED VIEW IF EXISTS …; / Oracle PL/SQL block catching ORA-12003 (matview does not exist). SQLite has no CREATE MATERIALIZED VIEW: matviews are emitted as plain tables (CREATE TABLE name AS SELECT …), so the SQLite branch drops them as ordinary tables (DROP TABLE IF EXISTS name;). Returned string is fully terminated — same convention as drop_table_if_exists.

Oracle half-dropped-state hardening (Y.7-followup): a CREATE MATERIALIZED VIEW interrupted mid-flight (e.g. a SIGTERM-killed schema apply) can leave the matview’s container table behind without the matview metadata — DROP MATERIALIZED VIEW then swallows ORA-12003 (“no such matview”) while the stray table survives, and the next CREATE MATERIALIZED VIEW hits ORA-00955 (“name already used”). So the Oracle branch emits a DROP TABLE of the same name on the LINE AFTER the matview drop (swallowing ORA-00942 = nothing there, and ORA-12083 = it’s a live container the matview drop already handled). On a healthy matview the table drop is a harmless no-op; on a half-dropped one it’s the cleanup that makes the re-apply idempotent.

The two blocks are joined with ``

`` (not a space): ``split_oracle: _script`` is a line scanner that treats a line beginning BEGIN / DECLARE and ending END; as one PL/SQL block — two blocks on one line would be handed to cursor.execute as a single BEGIN…END; BEGIN…END; string, which Oracle’s PL/SQL parser rejects (PLS-00103 on the second BEGIN). All callers ``”

“.join`` the drop strings, so an internal newline is fine.

Return type:

str

Parameters:

name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.drop_table_if_exists(name, dialect)[source]

Idempotent DROP TABLE — emits CASCADE so dependent FKs / views drop transitively (where the dialect supports CASCADE).

Postgres has native DROP TABLE IF EXISTS … CASCADE. Oracle 19c needs a PL/SQL block that catches ORA-00942 (table not found). SQLite has DROP TABLE IF EXISTS … but no CASCADE keyword — SQLite enforces FK-cascading via PRAGMA foreign_keys plus ON DELETE CASCADE declarations on the FK; the schema we emit has no FKs, so omitting CASCADE has no behavioral impact.

Returned string is fully terminated (Postgres trailing ;, Oracle END; PL/SQL terminator, SQLite trailing ;). Callers concatenate directly without appending ; to avoid a double-semicolon that Oracle’s PL/SQL parser rejects.

Return type:

str

Parameters:

name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.drop_view_if_exists(name, dialect)[source]

Idempotent DROP VIEW.

Postgres DROP VIEW IF EXISTS …; / Oracle PL/SQL block catching ORA-00942 / SQLite native DROP VIEW IF EXISTS …;. Returned string is fully terminated.

Return type:

str

Parameters:

name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.dual_from(dialect)[source]

Suffix that makes a constant SELECT valid on both dialects.

Postgres accepts SELECT 'x' AS col with no FROM. Oracle 19c requires every SELECT to have a FROM clause; the canonical Oracle pseudo-table for “one row, no real source” is dual. SQLite accepts the bare SELECT (like Postgres).

Returns "" on Postgres / SQLite / DuckDB and " FROM dual" on Oracle. Compose inline at the end of the SELECT list: f"SELECT {expr} AS col{dual_from(dialect)}". Combine with WHERE 1=0 (works on every dialect) for an empty-row sentinel branch — WHERE FALSE is Postgres-only and breaks Oracle.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.epoch_seconds_between(later, earlier, dialect)[source]

Difference between two timestamps in whole + fractional seconds.

Postgres EXTRACT(EPOCH FROM (later - earlier)). Oracle has no EPOCH unit; the equivalent for TIMESTAMP arithmetic (which yields INTERVAL DAY TO SECOND) is the sum of EXTRACT(DAY/HOUR/MINUTE/SECOND FROM …) terms. SQLite uses (julianday(later) - julianday(earlier)) * 86400 — julianday returns the Julian Day Number as a REAL, so subtracting two julianday values yields fractional days; multiplying by 86400 gives seconds.

Return type:

str

Parameters:

later (str)
earlier (str)
dialect (Dialect)

recon_gen.common.sql.dialect.fetch_first_one_row(dialect)[source]

Return the row-limiting clause that picks the first row of an ORDER BY — LIMIT 1 for Postgres + SQLite, FETCH FIRST 1 ROW ONLY for Oracle. Oracle 19c rejects LIMIT; Postgres + SQLite accept both forms but LIMIT is the established convention here.

Return type:: str
Parameters:: dialect (Dialect)

Compose at the tail of a SELECT after ORDER BY:: f”SELECT … ORDER BY x DESC {fetch_first_one_row(dialect)}”

recon_gen.common.sql.dialect.greatest(*args, dialect)[source]

Row-wise greatest of two or more expressions.

PG / Oracle: GREATEST(a, b, ...) — the SQL-standard scalar function (not an aggregate). SQLite: MAX(a, b, ...) — SQLite overloads MAX to be a row-wise scalar when called with 2+ arguments and the column-aggregate when called with 1. Both forms have identical semantics for our use case (clamping expressions like GREATEST(x - y, 0)).

Return type:

str

Parameters:

args (str)
dialect (Dialect)

recon_gen.common.sql.dialect.interval_days(n, dialect)[source]

A SQL interval literal of n days.

Postgres INTERVAL '<n> day' / Oracle INTERVAL '<n>' DAY. SQLite has no INTERVAL type — date arithmetic uses the date() function with a '+N days' modifier (see date_minus_days); a bare interval literal isn’t usable on its own. This helper returns the plain string '<n> days' for SQLite so callers that compose it with date(expr, '<interval>') work; sites that try to subtract a bare interval (e.g. RANGE BETWEEN INTERVAL ... PRECEDING) should use range_interval_days instead, which adapts to SQLite’s numeric-only RANGE frames via a Julian-day projection.

Return type:

str

Parameters:

n (int)
dialect (Dialect)

recon_gen.common.sql.dialect.json_array_iterate(json_expr, array_path, *, alias, dialect)[source]

LEFT JOIN clause that iterates a JSON array within json_expr at array_path, exposing each element under alias.value (and alias.key on SQLite — unused but harmless).

Per-dialect renderings (AW.0.b spike confirmed the SQLite shape; PG + Oracle use the SQL/JSON-standard JSON_TABLE form per the project’s portability constraint — no JSONB, no ->>, only SQL/JSON path syntax; PG 17+ required for native JSON_TABLE):

SQLite: json_each(<expr>, '<path>') — table-valued function over JSON1; each row has key + value columns.
DuckDB: json_each(<expr>, '<path>') — same shape as SQLite (DuckDB ships json_each natively; columns include key and value plus type metadata the callers don’t read).
PG 17+: JSON_TABLE(<expr>::json, '<path>[*]' COLUMNS (value JSON PATH '$')) — SQL/JSON-standard, no JSONB needed.
Oracle 12c+: same JSON_TABLE shape; VARCHAR2(4000) FORMAT JSON PATH '$' for the per-row value column (Oracle doesn’t have a JSON type pre-21c; uses VARCHAR2 + FORMAT JSON hint).

json_expr is the SQL expression yielding the JSON document (typically a scalar subquery like (SELECT l2_yaml FROM <p>_config)). array_path is the SQL/JSON path to the array (e.g. '$.rails'). alias is the per-row alias the matview SQL uses to reference the iteration (e.g. rail).

Return type:

str

Parameters:

json_expr (str)
array_path (str)
alias (str)
dialect (Dialect)

recon_gen.common.sql.dialect.json_check(col, dialect)[source]

CHECK (col IS NULL OR col IS JSON) in PG / Oracle; SQLite uses CHECK (col IS NULL OR json_valid(col)).

The IS JSON SQL/JSON-standard constraint is supported in Postgres 16+ and Oracle 12.2+ — bytes-identical there. SQLite + DuckDB have no IS JSON predicate but both ship json_valid(text) (returns 1 / true if the argument is well-formed JSON; 0 / false otherwise) — SQLite via the JSON1 extension (built into stdlib sqlite3 since 3.38), DuckDB natively.

Return type:

str

Parameters:

col (str)
dialect (Dialect)

recon_gen.common.sql.dialect.json_field_extract(value_expr, field_path, dialect)[source]

Extract a scalar field from a per-row JSON element (e.g. one iteration of json_array_iterate).

All dialects use SQL/JSON-standard path syntax — no JSONB-specific operators (the project bans ->>, ->, @> per the portability constraint).

SQLite: json_extract(<value_expr>, '<field_path>')
DuckDB: json_extract_string(<value_expr>, '<field_path>') — unwraps the scalar (DuckDB’s JSON_VALUE returns quoted JSON form; see json_value for the underlying gotcha).
PG 12+ / Oracle 12c+: JSON_VALUE(<value_expr>, '<field_path>') — SQL/JSON-standard scalar extract.

field_path is the SQL/JSON path (e.g. '$.name').

Return type:

str

Parameters:

value_expr (str)
field_path (str)
dialect (Dialect)

recon_gen.common.sql.dialect.json_text_type(dialect)[source]

Bounded text type for columns holding JSON metadata documents.

Diverges from text_type (which returns Postgres TEXT / Oracle CLOB) by emitting a bounded VARCHAR(4000) / VARCHAR2(4000) so the columns behave like ordinary strings on both dialects. Why bound:

Oracle CLOB can’t be aggregated (MIN / MAX / GROUP BY reject CLOB with ORA-00932), can’t appear in DISTINCT / ORDER BY, and fails IN comparisons against VARCHAR2 literals. Queries that pick a representative metadata per transfer via MAX(metadata) need it bounded.
Bounding Postgres to the same 4000-char limit keeps the two dialects symmetric so a “data too long” failure surfaces on either DB instead of leaking past PG and breaking only on Oracle.

4000 chars covers every JSON metadata document the L2 schema emits (typically 5–20 keys with short values). Banks with longer documents either trim at the ETL boundary or enable Oracle’s MAX_STRING_SIZE=EXTENDED (lifts VARCHAR2 to 32767) and bump this helper.

SQLite uses TEXT here too — it’s typeless under the hood (VARCHAR(N) parses but the length is purely advisory), so matching the symmetric “string-shaped” treatment by emitting plain TEXT keeps the SQL readable.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.json_value(col, path_expr, dialect)[source]

Extract a scalar from a JSON-shaped column via SQL/JSON path.

PG / Oracle: JSON_VALUE(col, path_expr) — the SQL/JSON-standard function (Postgres 12+, Oracle 12.2+). SQLite: json_extract(col, path_expr) — the JSON1 extension’s equivalent (built into stdlib sqlite3 3.38+). DuckDB: json_extract_string(col, path_expr) — DuckDB also has JSON_VALUE and json_extract but their semantics diverge from the SQL/JSON-standard (they return quoted JSON form rather than unwrapped scalar text); json_extract_string unwraps the scalar, matching the PG/Oracle/SQLite contract. Captured by CA.0 spike audit (docs/audits/ca_0_duckdb_spike.md — JSON portability). All four return scalar TEXT for the path’s leaf; missing paths return NULL.

path_expr is the SQL expression (already wrapped in quotes / constructed at the call site, e.g. "'$.customer_id'" or "'$.' || pKey") — same shape on every dialect, so the helper only swaps the function name.

Return type:

str

Parameters:

col (str)
path_expr (str)
dialect (Dialect)

recon_gen.common.sql.dialect.lob_substr(expr, n, dialect)[source]

Coerce a (potentially CLOB-typed) expression to a bounded VARCHAR by extracting its first n characters.

Needed for BC.12’s <prefix>_config_kv.value CLOB column: Oracle rejects MAX(CASE WHEN k='X' THEN value END) with ORA-22849 when value is CLOB (the MAX aggregate doesn’t accept LOB types). Wrap the CLOB read in DBMS_LOB.SUBSTR(value, n, 1) to coerce to VARCHAR2 inside the aggregate. PG + SQLite use plain SUBSTRING / SUBSTR so the same dialect-blind expression shape compiles everywhere.

Oracle: DBMS_LOB.SUBSTR(<expr>, <n>, 1) — extract n chars from offset 1; result is VARCHAR2(n) capped at 4000.
PG: SUBSTRING(<expr> FROM 1 FOR <n>) — SQL-standard substring on TEXT.
SQLite: SUBSTR(<expr>, 1, <n>) — standard.

Callers wrap the matview-consumed leaf fields (name, max_pending_age_seconds, cap, etc.) in this helper inside the typed projection views’ MAX(CASE WHEN ...) aggregates so the matview-source view body stays Oracle-LOB-safe.

Return type:

str

Parameters:

expr (str)
n (int)
dialect (Dialect)

recon_gen.common.sql.dialect.matview_create_keyword(dialect)[source]

The CREATE … keyword the matview templates emit per dialect.

Postgres + Oracle: CREATE MATERIALIZED VIEW. SQLite: just CREATE TABLE (matviews land as plain tables — refresh becomes a DELETE + INSERT pair, see refresh_matview). Used by common.l2.schema so the per-matview template strings can stay one-line + dialect-clean instead of branching at every site.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.matview_options(dialect)[source]

Per-dialect suffix between CREATE MATERIALIZED VIEW <name> and AS <body>. Postgres takes none; Oracle needs BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND to match Postgres’s build-on-create + manual-REFRESH semantics (without it Oracle defaults to REFRESH FORCE ON DEMAND, which has more setup requirements). SQLite uses CREATE TABLE … AS (see matview_create_keyword); no per-keyword suffix applies, so returns the empty string.

Used by common.l2.schema to splice the suffix into per-matview template strings (so the SELECT body stays inline + readable). Returns the empty string on Postgres + SQLite so the substitution is a no-op.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.order_by_day_expr(day_col, dialect)[source]

Per-dialect ORDER BY projection for date-keyed window functions paired with range_interval_days.

PG / Oracle: bare column name (intervals work directly against DATE / TIMESTAMP). SQLite: wrap in julianday(<col>) so the RANGE frame’s numeric arithmetic lands on the same scale as range_interval_days(N, SQLITE) = str(N).

Return type:

str

Parameters:

day_col (str)
dialect (Dialect)

recon_gen.common.sql.dialect.range_interval_days(n, dialect)[source]

Day-interval expression for use inside a window-function RANGE BETWEEN <expr> PRECEDING clause.

PG / Oracle take ordinary INTERVAL literals — same form interval_days returns. SQLite’s RANGE BETWEEN only accepts numeric expressions (the ORDER BY column must be numeric too), so the call site needs to project posted_day through julianday() and use a bare integer here. Returns str(n) for SQLite so a RANGE BETWEEN N PRECEDING frame, paired with ORDER BY julianday(posted_day), gives the same per-day semantics PG / Oracle deliver via INTERVAL.

Return type:

str

Parameters:

n (int)
dialect (Dialect)

recon_gen.common.sql.dialect.refresh_matview(name, dialect)[source]

REFRESH MATERIALIZED VIEW per dialect.

Postgres: REFRESH MATERIALIZED VIEW name;. Oracle: a PL/SQL block invoking DBMS_MVIEW.REFRESH('name', method => 'C') — C = complete refresh, matching Postgres semantics. SQLite: NOT a single statement — refresh on a matview-as-table is a DELETE + INSERT pair, but the body SELECT lives in the schema template, not here. The SQLite branch returns a sentinel that callers in common.l2.schema.refresh_matviews_sql route through _emit_sqlite_refresh_block (which knows the SELECT body for each matview). Returned string is fully terminated for PG / Oracle.

Return type:

str

Parameters:

name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.serial_type(dialect)[source]

Auto-incrementing 64-bit append-only key.

Postgres BIGSERIAL / Oracle NUMBER GENERATED BY DEFAULT AS IDENTITY. DuckDB falls through to a bare BIGINT paired with a CREATE SEQUENCE + DEFAULT nextval('<seq>') on the column (see _entry_column_decl in common/l2/schema.py; CA.3 lands the runner setup).

CB.17.i (2026-06-04) — Oracle moved from GENERATED ALWAYS to GENERATED BY DEFAULT. The two differ only on explicit-value INSERTs: ALWAYS rejects them with ORA-32795, BY DEFAULT accepts the supplied value and uses the sequence only when the column is omitted from the INSERT. Production seed paths omit the column either way (no behavior change); the V-overlay clone_base_to_v_sql does INSERT INTO v_X SELECT * FROM X which sends the source’s identity values in the column position and broke under ALWAYS. BY DEFAULT is the same semantic Postgres BIGSERIAL defaults to (DEFAULT-on-omit, accept-on- supply) and matches DuckDB’s sequence-DEFAULT shape.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.text_type(dialect)[source]

Unbounded character data.

Postgres TEXT / DuckDB TEXT / Oracle CLOB / SQLite TEXT.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.timestamp_type(dialect)[source]

TZ-naive timestamp, identical on both dialects.

Returns TIMESTAMP regardless of dialect (P.9a). Timezone normalization is the integrator’s contract — the L2 schema does not store timezone metadata and does not convert across zones. Banks reading from sources in multiple zones MUST normalize at the ETL boundary (typically to UTC or the institution’s local business zone).

Why standardized: the prior split helpers (timestamp_tz_type + pk_safe_timestamp_type) bridged Postgres TIMESTAMPTZ / Oracle TIMESTAMP WITH TIME ZONE for non-PK columns and demoted to plain TIMESTAMP for PK columns (Oracle rejects TZ-aware TIMESTAMPs in PKs with ORA-02329). The split surfaced as a cross-dialect divergence with no compensating value — neither engine performs query-time TZ conversion in a way the dashboards rely on, and the demotion was already happening for half the columns. Unifying on plain TIMESTAMP makes the schema byte- identical between dialects.

Return type:: str
Parameters:: dialect (Dialect)

recon_gen.common.sql.dialect.to_date(timestamp_expr, dialect)[source]

Truncate a timestamp expression to its date component.

Postgres expr::date / Oracle TRUNC(expr) / SQLite DATE(expr) (SQLite has no native DATE type — DATE() returns the date portion as YYYY-MM-DD text, which is sortable + groupable the same way the typed counterparts are).

Return type:

str

Parameters:

timestamp_expr (str)
dialect (Dialect)

recon_gen.common.sql.dialect.typed_null(type_name, dialect)[source]

Typed NULL literal.

Postgres NULL::type / Oracle CAST(NULL AS type) / SQLite CAST(NULL AS type) (same standard SQL CAST as Oracle; type aliasing maps to SQLite affinity names).

Return type:

str

Parameters:

type_name (str)
dialect (Dialect)

recon_gen.common.sql.dialect.varchar_type(n, dialect)[source]

Bounded variable-length character.

Postgres VARCHAR(n) / Oracle VARCHAR2(n) / SQLite TEXT (SQLite is typeless internally; the (n) would parse but enforce nothing, so emit plain TEXT for clarity).

Return type:

str

Parameters:

n (int)
dialect (Dialect)

recon_gen.common.sql.dialect.with_recursive(dialect)[source]

Recursive-CTE preamble keyword.

Postgres requires the explicit WITH RECURSIVE keyword. Oracle 19c infers recursion from the CTE body’s self-reference and accepts (but does not require) RECURSIVE — emit just WITH for portability across older Oracle releases. SQLite requires WITH RECURSIVE (same as Postgres).

Return type:: str
Parameters:: dialect (Dialect)