"""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"]}