SPB Git

Repository Public

make_formula_catalogue.py 11.5 KB 235 lines python

1#!/usr/bin/env python32"""Regenerates the function catalogue of docs/compatibility/formulas.md.34Inputs (all inside the repository):5  docs/compatibility/function-registry.json   dump of FunctionRegistry.standard (see below)6  Packages/FunctionsNumeric/COMPATIBILITY.md  per-function validation table7  Packages/FunctionsText/COMPATIBILITY.md     "Known deviations" bullets8  Packages/FunctionsText/Tests/FunctionsTextTests/Fixtures/function_references.json   (per-case source tags)9  Packages/FunctionsLookup/COMPATIBILITY.md   per-group validation tables1011Output: the text between the markers12  <!-- BEGIN GENERATED CATALOGUE --> ... <!-- END GENERATED CATALOGUE -->13of docs/compatibility/formulas.md is replaced. Nothing else in that file is touched.1415function-registry.json is produced by a throw-away Swift executable that depends on16Packages/SheetEngine, SheetCore and the three Functions* packages:1718    ORIXEngine.bootstrap()19    // origin of each name: register each library into a fresh FunctionRegistry()20    //   CoreBuiltins.register(into:), NumericFunctions.register(into:), TextFunctions.register(into:),21    //   LookupFunctions.register(into:) + FinancialFunctions.register(into:)22    // then encode, for every spec of FunctionRegistry.standard.all:23    //   name, category.rawValue, signature, summary, minArgs, maxArgs, isVolatile, packages24    // plus registryCount, ORIXEngine.functionCount, ORIXEngine.specialForms,25    //   ORIXEngine.functionCountsByCategory(), perPackage counts.2627Build it outside the repository (swift build --scratch-path /tmp/...) and redirect its28standard output to docs/compatibility/function-registry.json, then run this script:2930    python3 docs/compatibility/make_formula_catalogue.py [--check]31"""32from __future__ import annotations3334import collections35import json36import re37import sys38from pathlib import Path3940ROOT = Path(__file__).resolve().parents[2]41HERE = Path(__file__).resolve().parent42TARGET = HERE / "formulas.md"43BEGIN, END = "<!-- BEGIN GENERATED CATALOGUE -->", "<!-- END GENERATED CATALOGUE -->"4445CATEGORY_ORDER = ["Math", "Trigonometry", "Statistical", "Financial", "Logical", "Text", "Date & Time",46                  "Lookup & Reference", "Dynamic Array", "Database", "Engineering", "Information"]4748# Notes on the 24 functions registered by SheetCore itself (Packages/SheetCore/Sources/SheetCore/Engine/Builtins.swift).49# Each one was read in the source; they have no package COMPATIBILITY document.50CORE_NOTES = {51    "SUM": "Neumaier compensated summation.",52    "AVERAGE": "Compensated sum divided by the count; no numbers gives #DIV/0!.",53    "MIN": "No number at all gives 0.",54    "MAX": "No number at all gives 0.",55    "INDEX": "Reference form returns a reference. A 4th argument (area_num) is accepted and ignored: there are no multi-area references.",56    "INDIRECT": "A1 style only: `a1` = FALSE (R1C1) returns #REF!. Accepts a cell, range, defined name or structured reference.",57    "OFFSET": "Result outside the grid is #REF!.",58    "NOW": "One timestamp per recalculation pass (`EvalContext.now`).",59    "TODAY": "One timestamp per recalculation pass (`EvalContext.now`).",60    "RAND": "`SystemRandomNumberGenerator`; not seedable.",61    "ROW": "A multi-row reference returns a column array.",62    "COLUMN": "A multi-column reference returns a row array.",63}646566def cell(text: str) -> str:67    return text.replace("|", "\\|").replace("\n", " ").strip()686970def numeric_table() -> dict[str, tuple[str, str]]:71    out = {}72    for line in (ROOT / "Packages/FunctionsNumeric/COMPATIBILITY.md").read_text(encoding="utf-8").splitlines():73        match = re.match(r"\| `([A-Z0-9.]+)` \|([^|]*)\|([^|]*)\|([^|]*)\|([^|]*)\|([^|]*)\|\s*$", line)74        if not match:75            continue76        name, _, lo, independent, hand, remark = (part.strip() for part in match.groups())77        parts = []78        if lo != "–":79            parts.append(f"LibreOffice {lo}")80        if independent != "–":81            parts.append(independent)82        if hand != "–":83            parts.append(f"hand-written {hand}")84        out[name] = (", ".join(parts) if parts else "none listed", remark)85    return out868788def text_info(text_names: set[str]) -> dict[str, tuple[str, str]]:89    cases = json.loads((ROOT / "Packages/FunctionsText/Tests/FunctionsTextTests/Fixtures/function_references.json").read_text(encoding="utf-8"))90    counts: dict[str, collections.Counter] = collections.defaultdict(collections.Counter)91    for case in cases:92        counts[case["fn"]][case["src"]] += 193    labels = {"lo": "LibreOffice", "lo+py": "LibreOffice+Python", "py": "Python", "excel": "Excel-documented value"}94    document = (ROOT / "Packages/FunctionsText/COMPATIBILITY.md").read_text(encoding="utf-8")95    section = document.split("## Known deviations from Excel", 1)[1]96    bullets = [re.sub(r"\s+", " ", b).strip() for b in re.split(r"\n- ", "\n" + section.strip()) if b.strip()]97    notes: dict[str, list[str]] = collections.defaultdict(list)98    for bullet in bullets:99        if bullet.startswith("No result is validated"):100            continue101        tokens = set(re.findall(r"[A-Z][A-Z0-9.]+", bullet))102        hit = {name for name in text_names if name in tokens}103        if "REGEX" in tokens:104            hit |= {name for name in text_names if name.startswith("REGEX")}105        if bullet.startswith("Date text is parsed"):106            hit |= {"DATEVALUE", "TIMEVALUE", "VALUE"}107        for name in hit:108            notes[name].append(bullet)109    out = {}110    for name in text_names:111        c = counts.get(name)112        validation = ", ".join(f"{labels[k]} {c[k]}" for k in ("lo", "lo+py", "py", "excel") if c and c[k]) or "none listed"113        out[name] = (validation, " ".join(notes.get(name, [])))114    return out115116117def lookup_info(lookup_names: set[str]) -> dict[str, tuple[str, str]]:118    document = (ROOT / "Packages/FunctionsLookup/COMPATIBILITY.md").read_text(encoding="utf-8")119    out: dict[str, tuple[str, str]] = {}120    for line in document.splitlines():121        if not line.startswith("|") or line.startswith("|---"):122            continue123        cells = [c.strip() for c in line.strip().strip("|").split("|")]124        names = [n.strip() for n in re.split(r",|/", cells[0])]125        if not names or not all(n in lookup_names for n in names):126            continue127        group = "" if len(names) == 1 else f" (row shared by {', '.join(names)})"128        if len(cells) == 3:          # lookup & dynamic-array tables: function | validation | notes129            for name in names:130                note = "" if cells[2] in {"—", "-"} else cells[2]131                out[name] = (cells[1] + group, note)132        elif len(cells) == 6:        # financial table: function | XL | LO+PY | PY (LO≠) | other | notes133            def split(value: str, index: int) -> str:134                parts = [p.strip() for p in value.split("/")]135                return parts[index] if len(parts) == len(names) else value136            for index, name in enumerate(names):137                xl, lopy, py, other = (split(cells[k], index) for k in (1, 2, 3, 4))138                parts = []139                if xl and xl != "—":140                    parts.append(f"Excel-computed data {xl}")141                if lopy:142                    parts.append(f"LibreOffice+Python {lopy}")143                if py and py != "0":144                    parts.append(f"Python only (LibreOffice differs) {py}")145                if other:146                    parts.append(other)147                note = "" if cells[5] in {"—", "-"} else cells[5]148                out[name] = (", ".join(parts), note + (group if note and group else ""))149    missing = lookup_names - set(out)150    if missing:151        raise SystemExit(f"FunctionsLookup/COMPATIBILITY.md has no row for: {sorted(missing)}")152    return out153154155def build() -> str:156    registry = json.loads((HERE / "function-registry.json").read_text(encoding="utf-8"))157    functions = registry["functions"]158    by_package: dict[str, set[str]] = collections.defaultdict(set)159    for f in functions:160        if len(f["packages"]) != 1:161            raise SystemExit(f"{f['name']}: expected exactly one origin package, got {f['packages']}")162        by_package[f["packages"][0]].add(f["name"])163    numeric = numeric_table()164    if set(numeric) != by_package["FunctionsNumeric"]:165        raise SystemExit("FunctionsNumeric table and registry differ: "166                         f"{sorted(set(numeric) ^ by_package['FunctionsNumeric'])}")167    text = text_info(by_package["FunctionsText"])168    lookup = lookup_info(by_package["FunctionsLookup"])169170    lines: list[str] = []171    lines.append(f"Registry dump: {registry['registryCount']} registered functions + {len(registry['specialForms'])} special forms "172                 f"= {registry['functionCount']} (`ORIXEngine.functionCount`).")173    lines.append("")174    lines.append("| Origin package | Registered functions |")175    lines.append("|---|---:|")176    for package in ("SheetCore", "FunctionsNumeric", "FunctionsText", "FunctionsLookup"):177        lines.append(f"| `{package}` | {registry['perPackage'][package]} |")178    lines.append("")179    lines.append("Source: `docs/compatibility/function-registry.json` (each library registered into a fresh `FunctionRegistry`).")180    lines.append("")181    grouped: dict[str, list[dict]] = collections.defaultdict(list)182    for f in functions:183        grouped[f["category"]].append(f)184    unknown = set(grouped) - set(CATEGORY_ORDER)185    if unknown:186        raise SystemExit(f"unknown categories {unknown}")187    for category in CATEGORY_ORDER:188        rows = sorted(grouped.get(category, []), key=lambda f: f["name"])189        lines.append(f"### {category} ({len(rows)} registered)")190        lines.append("")191        lines.append("| Function | Signature | Summary | Package | Validation (reference cases) | Notes and deviations |")192        lines.append("|---|---|---|---|---|---|")193        for f in rows:194            package = f["packages"][0]195            if package == "SheetCore":196                validation, note = "SheetCore unit tests (`CoreTests.swift`); no external reference corpus", CORE_NOTES.get(f["name"], "")197            elif package == "FunctionsNumeric":198                validation, note = numeric[f["name"]]199            elif package == "FunctionsText":200                validation, note = text[f["name"]]201            else:202                validation, note = lookup[f["name"]]203            if f["volatile"]:204                note = ("**Volatile.** " + note).strip()205            lines.append(f"| `{f['name']}` | `{cell(f['signature'])}` | {cell(f['summary'])} | {package.replace('Functions', '')} | "206                         f"{cell(validation)} | {cell(note)} |")207        lines.append("")208        lines.append("Source: `function-registry.json` (name, signature, summary, volatility); validation and notes from "209                     "the `COMPATIBILITY.md` of the origin package (FunctionsText: counts from `function_references.json` by `src` tag).")210        lines.append("")211    return "\n".join(lines).rstrip() + "\n"212213214def main() -> int:215    generated = build()216    text = TARGET.read_text(encoding="utf-8")217    if BEGIN not in text or END not in text:218        raise SystemExit(f"{TARGET} lacks the {BEGIN} / {END} markers")219    head, rest = text.split(BEGIN, 1)220    _, tail = rest.split(END, 1)221    updated = head + BEGIN + "\n" + generated + END + tail222    if "--check" in sys.argv[1:]:223        if updated != text:224            print("formulas.md catalogue is out of date")225            return 1226        print("formulas.md catalogue is up to date")227        return 0228    TARGET.write_text(updated, encoding="utf-8")229    print(f"wrote catalogue: {generated.count(chr(10))} lines")230    return 0231232233if __name__ == "__main__":234    sys.exit(main())235
Files main
Menu

Theme