askrobots / dbbasic-object-server
Public
Runs live, versioned Python application objects on one VM, with a suite of apps: projects, notes, tasks, files and more.
danq9
signup: the person chooses the password and name on the confirm page
83c7aa6
2 weeks, 5 days ago
History
object_computed.py · 8.9 KB · 266 lines
| 1 | """Formula and rollup fields: derived values, materialized on write. |
| 2 | |
| 3 | The spreadsheet feature (plan/formula-rollup-spec.md): a `type: computed` |
| 4 | field may declare either |
| 5 | |
| 6 | {"name": "full_name", "type": "computed", |
| 7 | "formula": "first_name + \" \" + last_name"} |
| 8 | |
| 9 | {"name": "debit_total_cents", "type": "computed", |
| 10 | "rollup": {"collection": "fin_journal_lines", "fk_field": "journal_id", |
| 11 | "op": "sum", "field": "debit_cents"}} |
| 12 | |
| 13 | and object_records keeps the stored value current: formulas recompute on |
| 14 | every write of the record itself; rollups recompute on writes/deletes in the |
| 15 | source collection (see object_records._recompute_rollups_for_source). Values |
| 16 | are STORED in the TSV — so lists, tables, detail, search, 58 filters, sort, |
| 17 | realtime, and backups all see them with zero UI changes. |
| 18 | |
| 19 | The formula language is deliberately tiny and is evaluated by a |
| 20 | recursive-descent interpreter in this module — never Python eval. Grammar: |
| 21 | |
| 22 | expr := term (("+" | "-") term)* |
| 23 | term := factor (("*" | "/") factor)* |
| 24 | factor := NUMBER | STRING | IDENT | "(" expr ")" | "-" factor |
| 25 | |
| 26 | `+` is numeric addition when both operands parse as numbers, else string |
| 27 | concatenation. Failure posture: a broken formula (unknown token, division by |
| 28 | zero, non-numeric arithmetic) yields an EMPTY value and never fails the |
| 29 | caller's write — derived values are non-authoritative, the opposite of |
| 30 | pre-write hooks (a broken gate must fail closed; a broken caption must not |
| 31 | block a save). |
| 32 | """ |
| 33 | |
| 34 | from __future__ import annotations |
| 35 | |
| 36 | from typing import Any, Iterable, Mapping |
| 37 | |
| 38 | ROLLUP_OPS = ("sum", "count", "min", "max", "avg") |
| 39 | |
| 40 | |
| 41 | class FormulaError(ValueError): |
| 42 | """Raised internally for any formula parse/eval problem.""" |
| 43 | |
| 44 | |
| 45 | # --------------------------------------------------------------------------- |
| 46 | # Tokenizer |
| 47 | # --------------------------------------------------------------------------- |
| 48 | |
| 49 | _OPS = set("+-*/()") |
| 50 | |
| 51 | |
| 52 | def _tokenize(text: str) -> list[tuple[str, str]]: |
| 53 | tokens: list[tuple[str, str]] = [] |
| 54 | i, n = 0, len(text) |
| 55 | while i < n: |
| 56 | ch = text[i] |
| 57 | if ch.isspace(): |
| 58 | i += 1 |
| 59 | continue |
| 60 | if ch in _OPS: |
| 61 | tokens.append(("op", ch)) |
| 62 | i += 1 |
| 63 | continue |
| 64 | if ch == '"': |
| 65 | j = i + 1 |
| 66 | while j < n and text[j] != '"': |
| 67 | j += 1 |
| 68 | if j >= n: |
| 69 | raise FormulaError("Unterminated string literal") |
| 70 | tokens.append(("str", text[i + 1 : j])) |
| 71 | i = j + 1 |
| 72 | continue |
| 73 | if ch.isdigit() or (ch == "." and i + 1 < n and text[i + 1].isdigit()): |
| 74 | j = i |
| 75 | while j < n and (text[j].isdigit() or text[j] == "."): |
| 76 | j += 1 |
| 77 | tokens.append(("num", text[i:j])) |
| 78 | i = j |
| 79 | continue |
| 80 | if ch.isalpha() or ch == "_": |
| 81 | j = i |
| 82 | while j < n and (text[j].isalnum() or text[j] == "_"): |
| 83 | j += 1 |
| 84 | tokens.append(("ident", text[i:j])) |
| 85 | i = j |
| 86 | continue |
| 87 | raise FormulaError(f"Unexpected character in formula: {ch!r}") |
| 88 | return tokens |
| 89 | |
| 90 | |
| 91 | # --------------------------------------------------------------------------- |
| 92 | # Parser / evaluator |
| 93 | # --------------------------------------------------------------------------- |
| 94 | |
| 95 | |
| 96 | def _to_number(value: Any): |
| 97 | if isinstance(value, (int, float)): |
| 98 | return value |
| 99 | text = str(value).strip() |
| 100 | if not text: |
| 101 | return None |
| 102 | try: |
| 103 | number = float(text) |
| 104 | except ValueError: |
| 105 | return None |
| 106 | return int(number) if number.is_integer() else number |
| 107 | |
| 108 | |
| 109 | class _Evaluator: |
| 110 | def __init__(self, tokens: list[tuple[str, str]], record: Mapping[str, Any]): |
| 111 | self.tokens = tokens |
| 112 | self.pos = 0 |
| 113 | self.record = record |
| 114 | |
| 115 | def _peek(self): |
| 116 | return self.tokens[self.pos] if self.pos < len(self.tokens) else (None, None) |
| 117 | |
| 118 | def _next(self): |
| 119 | token = self._peek() |
| 120 | self.pos += 1 |
| 121 | return token |
| 122 | |
| 123 | def expr(self): |
| 124 | value = self.term() |
| 125 | while self._peek() == ("op", "+") or self._peek() == ("op", "-"): |
| 126 | _, op = self._next() |
| 127 | right = self.term() |
| 128 | if op == "+": |
| 129 | left_n, right_n = _to_number(value), _to_number(right) |
| 130 | if left_n is not None and right_n is not None: |
| 131 | value = left_n + right_n |
| 132 | else: |
| 133 | value = str(value) + str(right) |
| 134 | else: |
| 135 | value = self._arith(value, right, "-") |
| 136 | return value |
| 137 | |
| 138 | def term(self): |
| 139 | value = self.factor() |
| 140 | while self._peek() == ("op", "*") or self._peek() == ("op", "/"): |
| 141 | _, op = self._next() |
| 142 | right = self.factor() |
| 143 | value = self._arith(value, right, op) |
| 144 | return value |
| 145 | |
| 146 | def factor(self): |
| 147 | kind, text = self._next() |
| 148 | if kind == "num": |
| 149 | return _to_number(text) |
| 150 | if kind == "str": |
| 151 | return text |
| 152 | if kind == "ident": |
| 153 | return self.record.get(text, "") |
| 154 | if (kind, text) == ("op", "("): |
| 155 | value = self.expr() |
| 156 | if self._next() != ("op", ")"): |
| 157 | raise FormulaError("Missing closing parenthesis") |
| 158 | return value |
| 159 | if (kind, text) == ("op", "-"): |
| 160 | inner = _to_number(self.factor()) |
| 161 | if inner is None: |
| 162 | raise FormulaError("Cannot negate a non-number") |
| 163 | return -inner |
| 164 | raise FormulaError(f"Unexpected token: {text!r}") |
| 165 | |
| 166 | @staticmethod |
| 167 | def _arith(left, right, op): |
| 168 | left_n, right_n = _to_number(left), _to_number(right) |
| 169 | if left_n is None or right_n is None: |
| 170 | raise FormulaError(f"Non-numeric operand for {op!r}") |
| 171 | if op == "-": |
| 172 | return left_n - right_n |
| 173 | if op == "*": |
| 174 | return left_n * right_n |
| 175 | if right_n == 0: |
| 176 | raise FormulaError("Division by zero") |
| 177 | return left_n / right_n |
| 178 | |
| 179 | |
| 180 | def _result_to_string(value: Any) -> str: |
| 181 | number = _to_number(value) if not isinstance(value, str) else None |
| 182 | if isinstance(value, str): |
| 183 | return value |
| 184 | if number is None: |
| 185 | return str(value) |
| 186 | if isinstance(number, int): |
| 187 | return str(number) |
| 188 | return str(int(number)) if float(number).is_integer() else str(number) |
| 189 | |
| 190 | |
| 191 | def evaluate_formula(formula: str, record: Mapping[str, Any]) -> str: |
| 192 | """Evaluate one formula against a record; raises FormulaError on problems.""" |
| 193 | evaluator = _Evaluator(_tokenize(formula), record) |
| 194 | value = evaluator.expr() |
| 195 | if evaluator.pos != len(evaluator.tokens): |
| 196 | raise FormulaError("Trailing tokens in formula") |
| 197 | return _result_to_string(value) |
| 198 | |
| 199 | |
| 200 | # --------------------------------------------------------------------------- |
| 201 | # Field helpers (used by object_records) |
| 202 | # --------------------------------------------------------------------------- |
| 203 | |
| 204 | |
| 205 | def _is_computed(field: Mapping[str, Any]) -> bool: |
| 206 | return bool(field.get("computed")) or str(field.get("type") or "").lower() == "computed" |
| 207 | |
| 208 | |
| 209 | def formula_fields(fields: Iterable[Mapping[str, Any]]) -> list[Mapping[str, Any]]: |
| 210 | return [ |
| 211 | f for f in fields |
| 212 | if _is_computed(f) and isinstance(f.get("formula"), str) and f["formula"].strip() |
| 213 | ] |
| 214 | |
| 215 | |
| 216 | def rollup_fields(fields: Iterable[Mapping[str, Any]]) -> list[Mapping[str, Any]]: |
| 217 | out = [] |
| 218 | for f in fields: |
| 219 | rollup = f.get("rollup") |
| 220 | if _is_computed(f) and isinstance(rollup, Mapping): |
| 221 | if rollup.get("collection") and rollup.get("fk_field") and rollup.get("op") in ROLLUP_OPS: |
| 222 | out.append(f) |
| 223 | return out |
| 224 | |
| 225 | |
| 226 | def apply_formulas(fields: Iterable[Mapping[str, Any]], record: dict[str, Any]) -> dict[str, Any]: |
| 227 | """Return record with every formula field recomputed (rollup values on the |
| 228 | record are visible to formulas -- callers apply rollups first).""" |
| 229 | specs = formula_fields(fields) |
| 230 | if not specs: |
| 231 | return record |
| 232 | out = dict(record) |
| 233 | for field in specs: |
| 234 | name = field["name"] |
| 235 | try: |
| 236 | out[name] = evaluate_formula(field["formula"], out) |
| 237 | except FormulaError: |
| 238 | out[name] = "" |
| 239 | return out |
| 240 | |
| 241 | |
| 242 | def compute_rollup(rollup: Mapping[str, Any], children: list[Mapping[str, Any]]) -> str: |
| 243 | """Aggregate child rows per the rollup spec; returns the stored string.""" |
| 244 | where = rollup.get("where") |
| 245 | if isinstance(where, Mapping) and where: |
| 246 | children = [ |
| 247 | c for c in children |
| 248 | if all(str(c.get(k, "")) == str(v) for k, v in where.items()) |
| 249 | ] |
| 250 | op = rollup["op"] |
| 251 | if op == "count": |
| 252 | return str(len(children)) |
| 253 | values = [] |
| 254 | for child in children: |
| 255 | number = _to_number(child.get(rollup.get("field", ""), "")) |
| 256 | if number is not None: |
| 257 | values.append(number) |
| 258 | if not values: |
| 259 | return "0" if op == "sum" else "" |
| 260 | if op == "sum": |
| 261 | return _result_to_string(sum(values)) |
| 262 | if op == "min": |
| 263 | return _result_to_string(min(values)) |
| 264 | if op == "max": |
| 265 | return _result_to_string(max(values)) |
| 266 | return _result_to_string(sum(values) / len(values)) |