127 lines
5.2 KiB
Python
127 lines
5.2 KiB
Python
"""compose(pick, recon) → ComposeResult.
|
|
|
|
Deterministic SQL emission from a typed Pick. The composer assembles a single
|
|
sqlglot expression tree and renders it once — there is no hand-built SQL string
|
|
and no after-the-fact re-parse; the tree *is* the structure, and
|
|
`.sql(dialect="postgres", identify=True)` (every identifier quoted) is the one
|
|
place a string is produced.
|
|
|
|
The work is split across focused modules, each returning `exp` nodes:
|
|
|
|
aliases.py table → alias allocation (AliasMap)
|
|
joins.py join-graph walk → JOIN specs
|
|
filters.py typed Filter → predicate node
|
|
metrics.py metric YAML fragment → alias-qualified expression
|
|
dates.py date_range → BETWEEN node (encoding-aware)
|
|
order.py OrderBy → Ordered node
|
|
encodings.py default + dataset storage-encoding library
|
|
|
|
No LLM call anywhere. The Pick is the constraint boundary: if it can't be
|
|
expressed against recon the composer raises `PickValidationError`, which the
|
|
caller surfaces — it never fabricates SQL. New capability is additive: a new
|
|
filter shape is a branch in filters.py; a new Pick `kind` is a builder here.
|
|
Widening what a Pick can express is a deliberate decision — see
|
|
`def/schema-as-constraint.md` before doing so.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
from sqlglot import exp
|
|
|
|
from api.composer.aliases import AliasMap
|
|
from api.composer.encodings import effective_encodings
|
|
from api.composer.filters import render_filter
|
|
from api.composer.joins import build_joins
|
|
from api.composer.metrics import qualify_metric_expr
|
|
from api.composer.order import default_order_by, render_order_by
|
|
from api.composer.types import ComposeResult, Pick, PickValidationError
|
|
from api.recon.types import Recon
|
|
|
|
|
|
def compose(pick: Pick, recon: Recon) -> ComposeResult:
|
|
"""Render `pick` to SQL against `recon`. Raises PickValidationError on any
|
|
reference the recon can't resolve."""
|
|
|
|
# 1. Metric → base table.
|
|
if pick.metric not in recon.metrics:
|
|
raise PickValidationError(
|
|
f"metric {pick.metric!r} not in recon (known: {sorted(recon.metrics)})"
|
|
)
|
|
metric = recon.metrics[pick.metric]
|
|
if metric.from_table not in recon.tables:
|
|
raise PickValidationError(
|
|
f"metric {pick.metric!r} declares from_table={metric.from_table!r} "
|
|
f"but no such table in recon"
|
|
)
|
|
|
|
aliases = AliasMap()
|
|
base_alias = aliases.alias(metric.from_table)
|
|
encodings = effective_encodings(recon)
|
|
|
|
# 2. Resolve group_by columns → owning table + output alias. Tables other
|
|
# than the metric's need joins.
|
|
group_bindings: list[tuple[str, str, str]] = [] # (table, col_name, out_alias)
|
|
extra_tables: list[str] = []
|
|
dim_select_alias: dict[str, str] = {}
|
|
for ref in pick.group_by:
|
|
table, col = recon.resolve_column(ref)
|
|
if not aliases.has(table):
|
|
if table != metric.from_table:
|
|
extra_tables.append(table)
|
|
aliases.alias(table)
|
|
out_alias = col.name if ref == col.name else ref.replace(".", "_")
|
|
group_bindings.append((table, col.name, out_alias))
|
|
dim_select_alias[ref] = out_alias
|
|
|
|
# 3. Resolve filters → predicate nodes (may also introduce new tables).
|
|
filter_nodes: list[exp.Expression] = []
|
|
for f in pick.where:
|
|
table, _ = recon.resolve_column(f.column)
|
|
if not aliases.has(table):
|
|
if table != metric.from_table:
|
|
extra_tables.append(table)
|
|
aliases.alias(table)
|
|
filter_nodes.append(render_filter(f, recon, aliases, encodings))
|
|
|
|
# 4. JOINs for the union of extra tables.
|
|
join_specs = build_joins(recon, metric.from_table, extra_tables, aliases)
|
|
|
|
# 5. SELECT list: dimensions (in group_by order), then the metric.
|
|
select_nodes: list[exp.Expression] = [
|
|
exp.alias_(aliases.col(table, col_name), out_alias, quoted=True)
|
|
for table, col_name, out_alias in group_bindings
|
|
]
|
|
select_nodes.append(
|
|
exp.alias_(qualify_metric_expr(metric.sql, base_alias), metric.name, quoted=True)
|
|
)
|
|
|
|
# 6. WHERE: metric's default filter (if any) ANDed with the Pick's filters.
|
|
where_nodes: list[exp.Expression] = []
|
|
if metric.filter:
|
|
where_nodes.append(qualify_metric_expr(metric.filter, base_alias))
|
|
where_nodes.extend(filter_nodes)
|
|
|
|
# 7. ORDER BY: explicit, else largest-metric-first for grouped queries.
|
|
if pick.order_by is not None:
|
|
order_node = render_order_by(pick.order_by, metric.name, dim_select_alias)
|
|
elif group_bindings:
|
|
order_node = default_order_by(metric.name)
|
|
else:
|
|
order_node = None
|
|
|
|
# 8. Assemble one Select and render once.
|
|
sel = exp.select(*select_nodes).from_(aliases.aliased_table(metric.from_table))
|
|
for table_expr, on_expr in join_specs:
|
|
sel = sel.join(table_expr, on=on_expr, join_type="")
|
|
for node in where_nodes:
|
|
sel = sel.where(node)
|
|
if group_bindings:
|
|
sel = sel.group_by(*(exp.column(oa, quoted=True) for _, _, oa in group_bindings))
|
|
if order_node is not None:
|
|
sel = sel.order_by(order_node)
|
|
if pick.limit is not None:
|
|
sel = sel.limit(pick.limit)
|
|
|
|
sql = sel.sql(dialect="postgres", identify=True)
|
|
return ComposeResult(sql=sql, used_tables=aliases.tables())
|