loopable / platform /harness /summary_tables.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
6e96321 verified
Raw History Blame Contribute Delete
8.75 kB
"""Derived groupings installed in the same transaction as their tenant's source publication."""
import hashlib
import json
import re
from core import connector_spec
from harness import datastore, filter_sql
CATALOG = "_mirror_summary_catalog"
NUMERIC_TOTALS = ("sum", "average", "min", "max", "count", "count_distinct", "median")
def _quote(name):
return '"' + str(name).replace('"', '""') + '"'
def _name(source, table, group):
digest = hashlib.sha256(json.dumps([source, table, group]).encode()).hexdigest()[:24]
return "_mirror_summary_" + digest
def _definitions(con, table):
declared = connector_spec.mirror_summary_groups(table)
columns = {row[0].lower() for row in con.execute(
"SELECT column_name FROM information_schema.columns WHERE table_schema='main' AND table_name=?",
[table]).fetchall()}
for group, kind in declared.get("groups", ()):
if group.lower() not in columns:
continue
gspec = {"sql": _quote(group), "type": kind}
totals = {}
for column, ctype, expression in (*declared.get("measures", ()), (group, kind, _quote(group))):
if column.lower() not in columns:
continue
spec = {"sql": expression, "type": ctype}
for agg in NUMERIC_TOTALS if ctype in filter_sql.NUMERIC_TYPES else ("count", "count_distinct"):
sql = filter_sql.aggregate_sql(spec, agg)
totals.setdefault(sql, f"v{len(totals)}")
yield group, filter_sql.group_sql(gspec), totals
def invalidate(con, source):
"""Remove derived tables before a repair that owns its own source transactions."""
con.execute(f"CREATE TABLE IF NOT EXISTS {CATALOG} ("
"source VARCHAR, source_table VARCHAR, group_column VARCHAR, tenant VARCHAR, "
"snapshot_id VARCHAR, summary_table VARCHAR, definition VARCHAR, "
"PRIMARY KEY (source, source_table, group_column))")
previous = con.execute(f"SELECT summary_table FROM {CATALOG} WHERE source=?", [source]).fetchall()
for name, in previous:
if not re.fullmatch(r"_mirror_summary_[0-9a-f]{24}", name):
raise ValueError("Invalid derived summary name")
con.execute(f"DROP TABLE IF EXISTS {_quote(name)}")
con.execute(f"DELETE FROM {CATALOG} WHERE source=?", [source])
def _holistic(sql):
return bool(re.search(r"\bDISTINCT\b|\bquantile|\bmedian\b|\bmode\s*\(", sql, re.IGNORECASE))
def _build(con, name, table, expression, totals):
"""One summary table: the plain totals in one pass, then each holistic total in its own pass."""
# Trap: one GROUP BY holding every DISTINCT and quantile state per group exceeds the sync
# worker's 4GB memory plus 4GB spill on a 3M-group table. Each holistic pass alone fits.
# Trap: one many-way join of the passes plans a near cross product on _overall inside the
# install transaction. Each pass joins the accumulated table on its own instead.
holistic = [(sql, slot) for sql, slot in totals.items() if _holistic(sql)]
def grouped(target, part, rows):
columns = "".join(f", {sql} AS {_quote(slot)}" for sql, slot in part)
con.execute(f"CREATE TABLE {_quote(target)} AS SELECT {expression} AS _key, "
f"GROUPING({expression}) AS _overall{', count(*) AS _rows' if rows else ''}"
f"{columns} FROM {_quote(table)} GROUP BY GROUPING SETS (({expression}), ())")
if not holistic:
grouped(name, totals.items(), True)
return
# The caller's transaction rolls these work tables back on failure.
accumulated = f"{name}_a0"
grouped(accumulated, [(sql, slot) for sql, slot in totals.items() if not _holistic(sql)], True)
for index, (sql, slot) in enumerate(holistic, 1):
part = f"{name}_p{index}"
grouped(part, [(sql, slot)], False)
if index == len(holistic):
target, select = name, "a._key, a._overall, a._rows" + "".join(
f", {'b' if other == slot else 'a'}.{_quote(other)}" for other in totals.values())
else:
target, select = f"{name}_a{index}", f"a.*, b.{_quote(slot)}"
con.execute(f"CREATE TABLE {_quote(target)} AS SELECT {select} FROM {_quote(accumulated)} a "
f"JOIN {_quote(part)} b ON b._overall = a._overall AND b._key IS NOT DISTINCT FROM a._key")
con.execute(f"DROP TABLE {_quote(part)}")
con.execute(f"DROP TABLE {_quote(accumulated)}")
accumulated = target
def rebuild(con, source, manifest, tables):
"""Rebuild available declarations using SQL inside the caller's source install transaction."""
invalidate(con, source)
tenant, snapshot = manifest.get("tenant"), manifest.get("snapshot_id")
if not tenant or not snapshot:
return
for table in sorted(tables):
for group, expression, totals in _definitions(con, table):
name = _name(source, table, group)
_build(con, name, table, expression, totals)
definition = json.dumps({"group": expression, "totals": totals}, sort_keys=True)
con.execute(f"INSERT INTO {CATALOG} VALUES (?,?,?,?,?,?,?)",
[source, table, group, tenant, snapshot, name, definition])
def is_current(con, source, manifest, tables):
"""Require every declared summary to match the tenant, snapshot and current SQL definition."""
tenant, snapshot = manifest.get("tenant"), manifest.get("snapshot_id")
if not tenant or not snapshot:
return False
resident = {row[0] for row in con.execute(
"SELECT table_name FROM information_schema.tables WHERE table_schema='main' "
"AND table_type='BASE TABLE'").fetchall()}
if CATALOG not in resident:
return False
expected = {}
for table in sorted(set(tables) | set(manifest.get("tables") or {})):
if not connector_spec.mirror_summary_groups(table):
continue
if table not in resident:
return False
for group, expression, totals in _definitions(con, table):
name = _name(source, table, group)
if name not in resident:
return False
expected[(table, group)] = (tenant, snapshot, name,
json.dumps({"group": expression, "totals": totals}, sort_keys=True))
rows = con.execute(f"SELECT source_table, group_column, tenant, snapshot_id, summary_table, definition "
f"FROM {CATALOG} WHERE source=?", [source]).fetchall()
return bool(expected) and {(row[0], row[1]): row[2:] for row in rows} == expected
def read(con, *, tenant, table, group_column, group_expression, plan, where, params,
group_offset, group_limit):
"""Read an exact unfiltered grouping from the publication on this request's own cursor."""
# The complete compiled predicate includes permission walls, pins, ids and default scopes.
if str(where or "").strip() or params:
return None
if not table or not group_column or not connector_spec.mirror_summary_groups(table):
return None
exists = con.execute("SELECT 1 FROM information_schema.tables WHERE table_schema='main' "
"AND table_name=?", [CATALOG]).fetchone()
if not exists:
return None
row = con.execute(f"SELECT source, tenant, snapshot_id, summary_table, definition FROM {CATALOG} "
"WHERE source_table=? AND group_column=?", [table, group_column]).fetchone()
if row is None:
return None
source, owner, snapshot, name, encoded = row
publication = datastore._publication(con, source)
if owner != tenant or publication.get("tenant") != tenant or publication.get("snapshot_id") != snapshot:
return None
definition = json.loads(encoded)
if definition["group"] != group_expression or any(sql not in definition["totals"] for _, _, sql in plan):
return None
if not re.fullmatch(r"_mirror_summary_[0-9a-f]{24}", name):
return None
projection = ", ".join(_quote(definition["totals"][sql]) for _, _, sql in plan)
overall = datastore.window(table=name, select="_rows" + (", " + projection if projection else ""),
where="_overall=1", order_by="1", limit=1, cur=con)["rows"]
if len(overall) != 1:
return None
groups = datastore.window(table=name, select="_key, _rows" + (", " + projection if projection else ""),
where="_overall=0", order_by="2 DESC, 1 ASC", offset=group_offset,
limit=group_limit, cur=con)
return {"overall": overall[0], "groups": groups["rows"], "total_groups": groups["total"]}