"""Evaluate structured If statement definitions and compile matching DuckDB CASE expressions.""" import json import logging import math import re from copy import deepcopy from core.user_tables import ROLLUP_CONDITION_OPS _LOG = logging.getLogger(__name__) _KEY = re.compile(r"^[a-zA-Z_][a-zA-Z0-9_]*$") _DIGITS = r"[0-9](?:_?[0-9])*" _NUMBER_PATTERN = rf"[+-]?(?:{_DIGITS}(?:\.(?:{_DIGITS})?)?|\.{_DIGITS})(?:[eE][+-]?{_DIGITS})?" _NUMBER = re.compile(_NUMBER_PATTERN) _EXPRESSION_KEYS = {"expr", "expression", "formula", "signal", "fn", "script", "code"} MAX_ROWS = 100 _TRIM_CHARS = ''.join(chr(code) for code in range(0x3100) if chr(code).isspace()) class DefinitionError(ValueError): """A named refusal for an invalid If statement definition.""" def __init__(self, code, detail): self.code = code super().__init__(f"{code}: {detail}") def _deny_expressions(raw): if isinstance(raw, dict): for key, value in raw.items(): if str(key).lower() in _EXPRESSION_KEYS: raise DefinitionError("if_expression_forbidden", str(key)) _deny_expressions(value) elif isinstance(raw, list): for value in raw: _deny_expressions(value) def _field(value): if not isinstance(value, str) or not _KEY.fullmatch(value) or len(value) > 80: raise DefinitionError("if_field_invalid", "use a field key") return value def _scalar(value): if value is None or type(value) in (str, bool, int): return value if type(value) is float and math.isfinite(value): return value raise DefinitionError("if_value_invalid", "use text, a number, a boolean or null") def _result(raw): if isinstance(raw, dict) and set(raw) == {"value"}: return {"value": _scalar(raw["value"])} if isinstance(raw, dict) and set(raw) == {"field"}: return {"field": _field(raw["field"])} raise DefinitionError("if_result_invalid", "use {value} or {field}") def clean(raw): """Validate only the If statement bag, preserving row order and literal value types.""" if isinstance(raw, str): raise DefinitionError('if_expression_forbidden', 'expression text') _deny_expressions(raw) if not isinstance(raw, dict) or set(raw) != {"rows", "default"}: raise DefinitionError("if_definition_invalid", "use rows and one default, without expression text") if not isinstance(raw["rows"], list) or len(raw["rows"]) > MAX_ROWS: raise DefinitionError("if_rows_invalid", f"use at most {MAX_ROWS} ordered rows") rows = [] for item in raw["rows"]: if not isinstance(item, dict) or set(item) != {"field", "op", "value", "then"}: raise DefinitionError("if_row_invalid", "use field, op, value and then") if not isinstance(item['op'], str) or item["op"] not in ROLLUP_CONDITION_OPS: raise DefinitionError("if_operator_invalid", str(item["op"])) rows.append({"field": _field(item["field"]), "op": item["op"], "value": _scalar(item["value"]), "then": _result(item["then"])}) return {"rows": rows, "default": _result(raw["default"])} def references(defn): """Return source keys from conditions, result copies and the default copy.""" definition = clean(defn) keys = {item["field"] for item in definition["rows"]} keys.update(item["then"]["field"] for item in definition["rows"] if "field" in item["then"]) if "field" in definition["default"]: keys.add(definition["default"]["field"]) return keys def admit_sources(defn, hidden, previous=None): """Require readable sources for a new definition while preserving an existing shared definition.""" definition = clean(defn) unchanged = json.dumps(definition, sort_keys=True) == json.dumps(previous, sort_keys=True) if not unchanged and references(definition) & set(hidden): raise DefinitionError('if_source_forbidden', 'Choose source fields that you can read.') return definition def _text(value): if value is None: return "" if type(value) is bool: return "true" if value else "false" # DuckDB uses simple Unicode lowercase, including a single i for dotted capital I. return ''.join('i' if char == '\u0130' else char.lower() for char in str(value).strip()) def _number(text): if not _NUMBER.fullmatch(text): return None try: number = float(text) return number if math.isfinite(number) else None except (ValueError, TypeError, OverflowError): return None def _matches(item, row): left, right = _text(row[item["field"]]), _text(item["value"]) op = item["op"] if op in ("is_empty", "is_not_empty"): return (left == "") if op == "is_empty" else (left != "") if op in ("contains", "not_contains"): return (right in left) if op == "contains" else (right not in left) ln, rn = _number(left), _number(right) a, b = (ln, rn) if ln is not None and rn is not None else (left, right) if op in ("eq", "neq"): return (a == b) if op == "eq" else (a != b) if left == "" or right == "": return False return {"gt": a > b, "gte": a >= b, "lt": a < b, "lte": a <= b}[op] def _available(item, keys): return item["field"] in keys and ("field" not in item["then"] or item["then"]["field"] in keys) def _copy(result, row): return deepcopy(result["value"] if "value" in result else row.get(result["field"])) def evaluate(defn, row): """Return the first matching result, skipping and reporting deleted source references.""" definition = clean(defn) row = row if isinstance(row, dict) else {} missing = references(definition) - row.keys() if missing: _LOG.warning("if_source_missing: %s", ", ".join(sorted(missing))) for item in definition["rows"]: if _available(item, row) and _matches(item, row): return _copy(item["then"], row) return _copy(definition["default"], row) def _literal(value): if value is None: return "NULL" if type(value) is bool: return "TRUE" if value else "FALSE" if type(value) is int: return repr(value) if type(value) is float: return f"CAST('{value!r}' AS DOUBLE)" return "'" + value.replace("'", "''") + "'" def _sql_text(expression): return f"lower(trim(coalesce(CAST(({expression}) AS VARCHAR), ''), {_literal(_TRIM_CHARS)}))" def _sql_number(expression): cast = f"TRY_CAST(NULLIF({expression}, '') AS DOUBLE)" return (f"CASE WHEN regexp_full_match({expression}, {_literal(_NUMBER_PATTERN)}) " f"AND isfinite({cast}) THEN {cast} ELSE NULL END") def _sql_match(item, expression): left, right = _sql_text(expression), _literal(_text(item["value"])) op = item["op"] if op in ("is_empty", "is_not_empty"): return f"{left} {'=' if op == 'is_empty' else '<>'} ''" if op in ("contains", "not_contains"): return f"strpos({left}, {right}) {'>' if op == 'contains' else '='} 0" ln, rn = _sql_number(left), _sql_number(right) operator = {"eq": "=", "neq": "<>", "gt": ">", "gte": ">=", "lt": "<", "lte": "<="}[op] test = (f"CASE WHEN ({ln}) IS NOT NULL AND ({rn}) IS NOT NULL " f"THEN ({ln}) {operator} ({rn}) ELSE {left} {operator} {right} END") if op in ("gt", "gte", "lt", "lte"): test = f"{left} <> '' AND {right} <> '' AND ({test})" return test def sql_case(defn, column_sql): """Compile a DuckDB CASE with typed results and the same first-match and missing-ref rules.""" definition = clean(defn) missing = references(definition) - column_sql.keys() if missing: _LOG.warning("if_source_missing: %s", ", ".join(sorted(missing))) def result_sql(result): expression = (_literal(result["value"]) if "value" in result else column_sql.get(result["field"], "NULL")) # VARIANT preserves mixed scalar types instead of coercing every result to text. return f"CAST(({expression}) AS VARIANT)" branches = [f"WHEN {_sql_match(item, column_sql[item['field']])} THEN {result_sql(item['then'])}" for item in definition["rows"] if _available(item, column_sql)] default = result_sql(definition["default"]) return "CASE " + " ".join(branches) + f" ELSE {default} END" if branches else default def materialize(fields, rows): """Return computed cells and diagnostics without changing input rows or field definitions.""" by_key = {str(f["key"]): f for f in fields if isinstance(f, dict) and f.get("key")} definitions = {key: clean(f["if"]) for key, f in by_key.items() if f.get("type") == "if"} if not definitions: return {}, [] diagnostics = [{"field": key, "missing": sorted(references(defn) - by_key.keys())} for key, defn in definitions.items() if references(defn) - by_key.keys()] for key, definition in definitions.items(): definition['rows'] = [item for item in definition['rows'] if _available(item, by_key)] if 'field' in definition['default'] and definition['default']['field'] not in by_key: definition['default'] = {'value': None} changes = {} for rid, row in rows.items(): context = {key: row.get(key) for key in by_key} done, active = set(), set() def resolve(key): if key in done: return if key in active: raise DefinitionError("if_cycle", key) active.add(key) for dependency in references(definitions[key]) & definitions.keys(): resolve(dependency) context[key] = evaluate(definitions[key], context) active.remove(key) done.add(key) for key in definitions: try: resolve(key) except DefinitionError as exc: context[key] = None diagnostic = {"field": key, "code": exc.code} if diagnostic not in diagnostics: diagnostics.append(diagnostic) changes[str(rid)] = {key: context[key] for key in definitions} return changes, diagnostics def project_fields(fields, hidden): """Omit the definition when it could disclose a hidden source key or comparison value.""" projected = [] for field in fields: item = dict(field) if item.get("type") == "if" and isinstance(item.get("if"), dict): if references(item["if"]) & set(hidden): item.pop("if") item["ifConfigured"] = True projected.append(item) return projected