Spaces:
Paused
Paused
Download api/rollup_sql.py from aios-loopable/cfo-os: direct link, hf CLI and curl.
- Browser
- Download file 8.32 kB
-
https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/api/rollup_sql.py
- Command line
-
hf download hf://spaces/aios-loopable/cfo-os/api/rollup_sql.py
-
curl -L -o rollup_sql.py https://huggingface.co/spaces/aios-loopable/cfo-os/resolve/main/api/rollup_sql.py
8.32 kB
| """rollup_sql.py β the READ-THROUGH rollup: one grouped SQL query answers every parent row. | |
| β WHY THIS EXISTS. A linked rollup folds rows that live in the store, and `MAX_ROWS` bounds them | |
| for a measured reason: every `ut_*` table lives inside ONE `user_tables.json` that is parsed and | |
| copied on essentially every request. β THE CAP MOVED TO 60,000 ON 2026-08-09 AND THAT DID NOT | |
| RETIRE THIS MODULE β the arithmetic is why. (This line said 100,000 until wave 28: that number was | |
| a candidate REJECTED during its own derivation, and three files repeated it for a day. A comment | |
| naming a cap is a claim about a constant eight lines away β check it, do not copy it.) | |
| Measured against tenant #0's own mirror, Royal's | |
| **256,810 order lines are 63.9 MB and 2.57 s per copy**: 4.3x the new cap and 8x any tolerable | |
| per-request cost. Four Odoo databases that DO fit (customers, products, invoices, orders) already | |
| total 17.81 MB / ~320 ms. Order lines never live in that document, at any cap. | |
| β SO THIS KIND NEVER COPIES THE ROWS AT ALL. It names a governed semantic TOPIC and a METRIC KEY, | |
| and `store_query(..., group_by=[dim])` answers EVERY parent in one pass against DuckDB. | |
| MEASURED: 1,748 customers over 256,810 order lines in **232 ms**. The row count stops being the | |
| product's problem and becomes the database's, which is the whole of D-87's "read-through binding". | |
| β A METRIC KEY, NEVER A FILTER FRAGMENT β the decision this module is built around. | |
| `model/metrics/*.yml` already carries each metric's scope, its `store_filter_sql` AND the matching | |
| `live_domain`, whose own comment reads *"BOTH or store_parity compares two different questions"*. | |
| Binding a rollup to the KEY inherits the scope and the live-parity oracle for free. Letting a | |
| rollup carry SQL would mint a second definition of a number the semantic layer exists to define | |
| once, and the two would drift in silence. | |
| β TRUNCATION IS A WRONG NUMBER HERE, NOT A SHORT LIST. A row window is honest because counts and | |
| totals are computed over the full scope beside it; a GROUP window has no such companion β the | |
| groups ARE the answer. `store_query` now reports `truncated`, and this module REFUSES to write a | |
| single cell when it is set. Half a rollup is worse than none: it looks finished. | |
| """ | |
| import sys | |
| from pathlib import Path | |
| _HERE = Path(__file__).resolve().parent | |
| _PLATFORM = _HERE.parents[1] / "platform" | |
| for _p in (str(_HERE), str(_PLATFORM)): | |
| if _p not in sys.path: | |
| sys.path.insert(0, _p) | |
| class RollupSourceError(Exception): | |
| """The rollup could not be computed HONESTLY β no cells are written when this is raised.""" | |
| def _ut(): | |
| import core.user_tables as user_tables | |
| return user_tables | |
| def source_fields(table_def): | |
| """Every source-backed rollup field on a table definition, in declaration order.""" | |
| out = [] | |
| for f in ((table_def or {}).get("fields") or []): | |
| if not isinstance(f, dict) or f.get("type") != "rollup": | |
| continue | |
| bag = (f.get("rollup") or {}).get("source") | |
| if isinstance(bag, dict) and bag.get("topic") and bag.get("measure"): | |
| out.append(f) | |
| return out | |
| def group_values(bag, today=None): | |
| """`{group_id: value}` for one source bag β ONE grouped query over the whole scope. | |
| β Keyed by the dim's `_id` column, never its label. Two customers can share a display name; | |
| `partner_id` is what the parent row actually joins on. | |
| """ | |
| from harness import semantic as sem | |
| from harness import windows as W | |
| topic = str(bag.get("topic") or "") | |
| measure = str(bag.get("measure") or "") | |
| dim = str(bag.get("groupBy") or "") | |
| window = str(bag.get("window") or "").strip().lower() | |
| date_from = date_to = None | |
| if window and window != "all_time": | |
| # β RESOLVED HERE, AGAINST `today`, not stored as a date pair. `harness.windows` owns what | |
| # "ytd" means so there is one implementation; a literal year-start baked into the field | |
| # would be right until 1 January and wrong afterwards with nothing to notice. | |
| rng = W.resolve({"kind": window}, today or _today()) | |
| if rng is None: | |
| raise RollupSourceError( | |
| f"window {window!r} could not be resolved β refusing rather than widening this " | |
| f"rollup to all time") | |
| date_from, date_to = rng | |
| try: | |
| res = sem.store_query(topic, [measure], group_by=[dim], | |
| date_from=date_from, date_to=date_to, | |
| limit=sem.MAX_GROUPS, today=today) | |
| except Exception as e: # noqa: BLE001 | |
| raise RollupSourceError(f"{type(e).__name__}: {e}") from e | |
| if res.get("truncated"): | |
| # β THE REFUSAL THAT MATTERS. See the module header: a truncated GROUP set is a wrong | |
| # answer per parent, not a shortened one, and it would look completely normal on screen. | |
| raise RollupSourceError( | |
| f"{topic}/{measure} grouped by {dim} exceeded {sem.MAX_GROUPS} groups β refusing to " | |
| f"write cells from a truncated result") | |
| # β THE KEY COLUMN IS NOT ALWAYS `<dim>_id`, and reading it unconditionally was a SILENT | |
| # BLANK. `store_query` emits `<dim>_id` only when the dim declares a `name_col` | |
| # (`f"{d['col']} AS {g}_id" if d.get("name_col") else f"{d['col']} AS {g}"`) β an id plus a | |
| # display name. A dim whose value IS its own label emits the bare `<dim>` instead, and two | |
| # shipped dims are that shape: `receivables.payment_state` and `gl_lines.account_type`. | |
| # Against either, this returned an EMPTY map and every cell was written blank β a configured | |
| # column that computes nothing, with no error anywhere. `payment_state` is not hypothetical: | |
| # it is a real column on `ut_odoo_invoices` and an obvious thing to roll up against. | |
| # β `_id` is tried FIRST so an id-keyed dim can never be keyed by its display name, which | |
| # would silently merge two customers who share one. | |
| id_col = f"{dim}_id" | |
| key_col = id_col if any(id_col in (r or {}) for r in (res.get("rows") or [])) else dim | |
| out = {} | |
| for row in res.get("rows") or []: | |
| gid = row.get(key_col) | |
| if gid is None: | |
| continue | |
| out[str(gid)] = row.get(measure) | |
| return out | |
| def _today(): | |
| import datetime as _dt | |
| return _dt.date.today().strftime("%Y-%m-%d") | |
| def compute(rt, table_key, today=None, tables=None): | |
| """Write every source-backed rollup cell on `table_key`. Returns `{field_key: cells_written}`. | |
| `tables` (a live `user_tables` dict) is the gate's injection point β the same shape | |
| `automation_engine.compute_relation_cells` takes, so this can be proven against a fixture | |
| without a store. | |
| """ | |
| ut = _ut() | |
| owned = tables is not None | |
| blob = tables if owned else (rt.get(ut.STORE_KEY) or {}) | |
| tdef = (blob or {}).get(str(table_key)) or {} | |
| fields = source_fields(tdef) | |
| if not fields: | |
| return {} | |
| stamp = today or _today() | |
| plans = [] | |
| for f in fields: | |
| bag = f["rollup"]["source"] | |
| # β Resolved BEFORE anything is written. A rollup that refuses must leave every cell as it | |
| # was β a partially applied pass would mix two vintages of the same column. | |
| plans.append((f["key"], str(bag.get("on") or ""), group_values(bag, today=stamp))) | |
| written = {} | |
| def _apply(cur): | |
| t = cur.get(str(table_key)) | |
| if t is None: | |
| return cur | |
| rows = t.setdefault("rows", {}) | |
| for fkey, on, values in plans: | |
| n = 0 | |
| for row in rows.values(): | |
| if not isinstance(row, dict): | |
| continue | |
| join = str(row.get(on) or "").strip() | |
| if not join: | |
| continue | |
| # β int-ish join keys arrive as "5280" or 5280 depending on who wrote them. | |
| hit = values.get(join) | |
| if hit is None and join.endswith(".0"): | |
| hit = values.get(join[:-2]) | |
| row[fkey] = "" if hit is None else str(hit) | |
| n += 1 | |
| written[fkey] = n | |
| return cur | |
| if owned: | |
| _apply(blob) | |
| else: | |
| rt.update(ut.STORE_KEY, _apply, flush="async") | |
| return written | |