File size: 10,692 Bytes
c72f0a1 81a8217 c72f0a1 81a8217 c72f0a1 81a8217 c72f0a1 81a8217 c72f0a1 81a8217 c72f0a1 81a8217 c72f0a1 81a8217 c72f0a1 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 | """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
|