Download platform/harness/summary_tables.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 8.75 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/summary_tables.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/harness/summary_tables.py
-
curl -L -o summary_tables.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/summary_tables.py
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"]} | |