"""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.
"""
from __future__ import annotations
from enum import Enum
[docs]
class Dialect(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.
"""
POSTGRES = "postgres"
ORACLE = "oracle"
DUCKDB = "duckdb"
# -- Identifiers -------------------------------------------------------------
[docs]
def column_name(name: str, dialect: Dialect) -> str:
"""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.
"""
if dialect is Dialect.ORACLE:
return name.upper()
return name.lower()
# -- Type names (DDL) --------------------------------------------------------
[docs]
def serial_type(dialect: Dialect) -> str:
"""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.
"""
if dialect is Dialect.POSTGRES:
return "BIGSERIAL"
if dialect is Dialect.DUCKDB:
return "BIGINT"
return "NUMBER GENERATED BY DEFAULT AS IDENTITY"
[docs]
def boolean_type(dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return "BOOLEAN"
return "NUMBER(1)"
[docs]
def text_type(dialect: Dialect) -> str:
"""Unbounded character data.
Postgres ``TEXT`` / DuckDB ``TEXT`` / Oracle ``CLOB`` / SQLite ``TEXT``.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return "TEXT"
return "CLOB"
[docs]
def json_text_type(dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return "VARCHAR(4000)"
return "VARCHAR2(4000)"
[docs]
def timestamp_type(dialect: Dialect) -> str: # noqa: ARG001
"""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 "TIMESTAMP"
[docs]
def varchar_type(n: int, dialect: Dialect) -> str:
"""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).
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"VARCHAR({n})"
return f"VARCHAR2({n})"
[docs]
def bigint_type(dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return "BIGINT"
return "NUMBER(19)"
[docs]
def decimal_type(precision: int, scale: int, dialect: Dialect) -> str:
"""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).
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"DECIMAL({precision},{scale})"
return f"NUMBER({precision},{scale})"
# -- Casts -------------------------------------------------------------------
[docs]
def cast(expr: str, type_name: str, dialect: Dialect) -> str:
"""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).
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"{expr}::{type_name}"
return f"CAST({expr} AS {_oracle_type_alias(type_name)})"
[docs]
def typed_null(type_name: str, dialect: Dialect) -> str:
"""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).
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"NULL::{type_name}"
return f"CAST(NULL AS {_oracle_type_alias(type_name)})"
[docs]
def to_date(timestamp_expr: str, dialect: Dialect) -> str:
"""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).
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"{timestamp_expr}::date"
return f"TRUNC({timestamp_expr})"
[docs]
def date_literal(iso_value: str, dialect: Dialect) -> str:
"""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 f"DATE '{iso_value}'"
# Oracle type-name canonicalization. Postgres uses lowercase
# ``numeric`` / ``bigint`` / ``date`` / ``timestamp`` per its docs;
# Oracle wants ``NUMBER`` / ``DATE`` / ``TIMESTAMP``. The
# ``_oracle_type_alias`` table keeps the helpers' callers free to
# pass Postgres-shape type names while the Oracle branch substitutes
# the right name automatically.
_ORACLE_TYPE_ALIASES = {
"numeric": "NUMBER",
"bigint": "NUMBER(19)",
"int": "NUMBER(10)",
"integer": "NUMBER(10)",
"smallint": "NUMBER(5)",
"date": "DATE",
"timestamp": "TIMESTAMP",
"text": "CLOB",
"boolean": "NUMBER(1)",
}
def _oracle_type_alias(type_name: str) -> str:
"""Return the Oracle equivalent of a Postgres-shape type name.
Direct hits in ``_ORACLE_TYPE_ALIASES`` win; otherwise the helper
rewrites ``varchar(N)`` → ``VARCHAR2(N)`` so callers can pass the
Postgres-shape parameterized type name and get the Oracle form.
Unhandled types pass through unchanged.
"""
name_lower = type_name.lower()
if name_lower in _ORACLE_TYPE_ALIASES:
return _ORACLE_TYPE_ALIASES[name_lower]
# varchar(N) → VARCHAR2(N) for parameterized varchars
import re
m = re.match(r"^varchar\((\d+)\)$", name_lower)
if m:
return f"VARCHAR2({m.group(1)})"
return type_name
# SQLite type-name canonicalization — maps Postgres-shape type names
# to SQLite affinity names. SQLite's storage classes are NULL /
# INTEGER / REAL / TEXT / BLOB; column "types" are advisory affinities
# (NUMERIC / INTEGER / REAL / TEXT / BLOB). We map exact-precision
# numerics to NUMERIC, integers to INTEGER, dates/timestamps to TEXT
# (SQLite has no native datetime type — convention is ISO-8601 text
# stored in TEXT-affinity columns + queried via ``date()`` / ``datetime()``
# / ``strftime()``), and text-y types to TEXT. Anything we forgot
# passes through unchanged.
# -- JSON --------------------------------------------------------------------
[docs]
def greatest(*args: str, dialect: Dialect) -> str:
"""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)``).
"""
joined = ", ".join(args)
return f"GREATEST({joined})"
[docs]
def json_value(col: str, path_expr: str, dialect: Dialect) -> str:
"""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.
"""
if dialect is Dialect.DUCKDB:
return f"json_extract_string({col}, {path_expr})"
return f"JSON_VALUE({col}, {path_expr})"
[docs]
def json_array_iterate(
json_expr: str, array_path: str, *, alias: str, dialect: Dialect,
) -> str:
"""``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``).
"""
if dialect in (Dialect.DUCKDB):
return f"json_each({json_expr}, '{array_path}') {alias}"
if dialect is Dialect.POSTGRES:
# PG 17+ JSON_TABLE is SQL/JSON-standard; cast to `json`
# (not jsonb — banned by the project's portability constraint).
return (
f"JSON_TABLE(({json_expr})::json, '{array_path}[*]' "
f"COLUMNS (value json PATH '$')) {alias}"
)
# Oracle 12c+: VARCHAR2(4000) FORMAT JSON for the value column.
return (
f"JSON_TABLE({json_expr}, '{array_path}[*]' COLUMNS "
f"(value VARCHAR2(4000) FORMAT JSON PATH '$')) {alias}"
)
[docs]
def lob_substr(expr: str, n: int, dialect: Dialect) -> str:
"""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.
"""
if dialect is Dialect.ORACLE:
return f"DBMS_LOB.SUBSTR({expr}, {n}, 1)"
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"SUBSTRING({expr} FROM 1 FOR {n})"
return f"SUBSTR({expr}, 1, {n})"
[docs]
def json_check(col: str, dialect: Dialect) -> str:
"""``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.
"""
if dialect in (Dialect.DUCKDB):
return f"CHECK ({col} IS NULL OR json_valid({col}))"
return f"CHECK ({col} IS NULL OR {col} IS JSON)"
# -- Date / time arithmetic --------------------------------------------------
[docs]
def epoch_seconds_between(later: str, earlier: str, dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"EXTRACT(EPOCH FROM ({later} - {earlier}))"
diff = f"({later} - {earlier})"
return (
f"(EXTRACT(DAY FROM {diff}) * 86400 "
f"+ EXTRACT(HOUR FROM {diff}) * 3600 "
f"+ EXTRACT(MINUTE FROM {diff}) * 60 "
f"+ EXTRACT(SECOND FROM {diff}))"
)
[docs]
def interval_days(n: int, dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"INTERVAL '{n} day'"
return f"INTERVAL '{n}' DAY"
[docs]
def range_interval_days(n: int, dialect: Dialect) -> str:
"""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 interval_days(n, dialect)
[docs]
def order_by_day_expr(day_col: str, dialect: Dialect) -> str:
"""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 day_col
[docs]
def date_minus_days(date_expr: str, n: int, dialect: Dialect) -> str:
"""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')``.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"({date_expr} - {interval_days(n, dialect)})"
return f"({date_expr} - {n})"
[docs]
def date_trunc_day(timestamp_expr: str, dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"DATE_TRUNC('day', {timestamp_expr})"
return f"CAST(TRUNC({timestamp_expr}) AS TIMESTAMP)"
[docs]
def day_text(timestamp_expr: str, dialect: Dialect) -> str:
"""Render a timestamp expression as its ``YYYY-MM-DD`` day string.
Unlike :func:`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).
"""
if dialect in (Dialect.DUCKDB):
return f"strftime('%Y-%m-%d', {timestamp_expr})"
return f"TO_CHAR({timestamp_expr}, 'YYYY-MM-DD')"
[docs]
def concat_agg(column_expr: str, separator: str, dialect: Dialect) -> str:
"""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.
"""
sep_literal = f"'{separator}'"
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
# ORDER BY makes the concatenation deterministic — without it the
# output depends on the engine's group-iteration order, which
# differs between PG and DuckDB and breaks reference/dogfood row
# equality.
return f"STRING_AGG({column_expr}, {sep_literal} ORDER BY {column_expr})"
return (
f"LISTAGG({column_expr}, {sep_literal}) "
f"WITHIN GROUP (ORDER BY {column_expr})"
)
# -- DDL idempotency ---------------------------------------------------------
[docs]
def drop_table_if_exists(name: str, dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"DROP TABLE IF EXISTS {name} CASCADE;"
return _oracle_drop_if_exists(
f"DROP TABLE {name} CASCADE CONSTRAINTS", ignore_codes=(-942,),
)
[docs]
def drop_matview_if_exists(name: str, dialect: Dialect) -> str:
"""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 ``\n`` (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
``"\n".join`` the drop strings, so an internal newline is fine.
"""
if dialect is Dialect.POSTGRES:
return f"DROP MATERIALIZED VIEW IF EXISTS {name};"
if dialect in (Dialect.DUCKDB):
# DuckDB has no CREATE MATERIALIZED VIEW (parser rejects the
# keyword); matviews land as plain tables via CREATE TABLE AS
# SELECT — drop them with DROP TABLE IF EXISTS, identical to
# the SQLite branch. CA.0 spike confirms (probe: "DROP
# MATERIALIZED VIEW IF EXISTS nope" → "Not implemented Error:
# Cannot drop this type yet").
return f"DROP TABLE IF EXISTS {name};"
return (
_oracle_drop_if_exists(
f"DROP MATERIALIZED VIEW {name}", ignore_codes=(-12003, -942),
)
+ "\n"
+ _oracle_drop_if_exists(
f"DROP TABLE {name} CASCADE CONSTRAINTS", ignore_codes=(-942, -12083),
)
)
[docs]
def drop_index_if_exists(name: str, dialect: Dialect) -> str:
"""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**.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"DROP INDEX IF EXISTS {name};"
return _oracle_drop_if_exists(
f"DROP INDEX {name}", ignore_codes=(-1418,),
)
[docs]
def drop_view_if_exists(name: str, dialect: Dialect) -> str:
"""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**.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"DROP VIEW IF EXISTS {name};"
return _oracle_drop_if_exists(
f"DROP VIEW {name}", ignore_codes=(-942,),
)
def _oracle_drop_if_exists(
drop_stmt: str, *, ignore_codes: tuple[int, ...],
) -> str:
"""Wrap an Oracle DROP statement in a PL/SQL block that swallows
"does not exist" errors so the script is idempotent.
Re-raises any other SQLCODE so genuine failures (privilege issues,
bad syntax) still surface. ``ignore_codes`` lists the negative
SQLCODE values to swallow per object type (e.g. ORA-00942 = -942
for TABLE / VIEW; ORA-01418 = -1418 for INDEX; ORA-12003 = -12003
for MATERIALIZED VIEW).
"""
not_in = " AND ".join(f"SQLCODE != {c}" for c in ignore_codes)
return (
f"BEGIN EXECUTE IMMEDIATE '{drop_stmt}'; "
f"EXCEPTION WHEN OTHERS THEN IF {not_in} THEN RAISE; END IF; END;"
)
# -- Materialized views ------------------------------------------------------
[docs]
def create_matview(name: str, body_sql: str, dialect: Dialect) -> str:
"""``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.
"""
if dialect is Dialect.POSTGRES:
return f"CREATE MATERIALIZED VIEW {name} AS {body_sql}"
if dialect in (Dialect.DUCKDB):
return f"CREATE TABLE {name} AS {body_sql}"
return (
f"CREATE MATERIALIZED VIEW {name} "
f"BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS {body_sql}"
)
[docs]
def matview_options(dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return ""
return " BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND"
[docs]
def matview_create_keyword(dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.DUCKDB):
return "CREATE TABLE"
return "CREATE MATERIALIZED VIEW"
[docs]
def refresh_matview(name: str, dialect: Dialect) -> str:
"""``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.
"""
if dialect is Dialect.POSTGRES:
return f"REFRESH MATERIALIZED VIEW {name};"
if dialect in (Dialect.DUCKDB):
# Sentinel — the per-matview SELECT body is needed to refresh,
# which the helper here doesn't know. ``refresh_matviews_sql``
# in ``common.l2.schema`` substitutes the right body. DuckDB
# follows the SQLite path: matviews are CREATE TABLE AS SELECT
# tables, refresh is DELETE + INSERT (per-matview).
raise NotImplementedError(
f"{dialect.value} refresh requires the matview body "
"SELECT; call refresh_matviews_sql in common.l2.schema "
"instead.",
)
return f"BEGIN DBMS_MVIEW.REFRESH('{name}', method => 'C'); END;"
[docs]
def analyze_table(name: str, dialect: Dialect) -> str:
"""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**.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return f"ANALYZE {name};"
return f"BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, '{name}'); END;"
# -- Constant SELECT (no real source table) ---------------------------------
[docs]
def fetch_first_one_row(dialect: Dialect) -> str:
"""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.
Compose at the tail of a SELECT after ``ORDER BY``:
f"SELECT ... ORDER BY x DESC {fetch_first_one_row(dialect)}"
"""
if dialect is Dialect.ORACLE:
return "FETCH FIRST 1 ROW ONLY"
return "LIMIT 1"
[docs]
def dual_from(dialect: Dialect) -> str:
"""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.
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return ""
return " FROM dual"
# -- Recursive CTE -----------------------------------------------------------
[docs]
def with_recursive(dialect: Dialect) -> str:
"""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).
"""
if dialect in (Dialect.POSTGRES, Dialect.DUCKDB):
return "WITH RECURSIVE"
return "WITH"