1#************************************************************** 2# 3# Licensed to the Apache Software Foundation (ASF) under one 4# or more contributor license agreements. See the NOTICE file 5# distributed with this work for additional information 6# regarding copyright ownership. The ASF licenses this file 7# to you under the Apache License, Version 2.0 (the 8# "License"); you may not use this file except in compliance 9# with the License. You may obtain a copy of the License at 10# 11# http://www.apache.org/licenses/LICENSE-2.0 12# 13# Unless required by applicable law or agreed to in writing, 14# software distributed under the License is distributed on an 15# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16# KIND, either express or implied. See the License for the 17# specific language governing permissions and limitations 18# under the License. 19# 20#************************************************************** 21# usage: runpy.sh calcval.py <port> <cases.tsv> <out.json> 22# python3 -I calcval.py --compare old.json new.json 23# 24# Evaluates the formulas of a cases TSV (see calcval-cases.tsv for the columns 25# and the data area) in a new Calc document and records, per case, the compile 26# and evaluation result of the current build, and whether it equals the value 27# that ODF 1.3 Part 4 requires. --compare lists the cases whose result changed. 28 29import json, math, re, sys 30 31 32def parse_mode(mode): 33 m = re.fullmatch(r"cell(?:@(\d+))?", mode) 34 if m: 35 return "cell", 1, 1, int(m.group(1) or 1) 36 m = re.fullmatch(r"array(?::(\d+)x(\d+))?", mode) 37 if m: 38 return "array", int(m.group(1) or 1), int(m.group(2) or 1), 1 39 raise ValueError("bad mode %r" % mode) 40 41 42def read_cases(path): 43 cases = [] 44 for ln, line in enumerate(open(path, encoding="utf-8"), 1): 45 line = line.rstrip("\n") 46 if not line or line.startswith("#"): 47 continue 48 f = line.split("\t") 49 if len(f) != 5: 50 raise SystemExit("%s:%d: expected 5 tab separated columns, got %d" % (path, ln, len(f))) 51 cases.append(dict(zip(("id", "formula", "mode", "expected", "note"), f))) 52 ids = [c["id"] for c in cases] 53 if len(set(ids)) != len(ids): 54 raise SystemExit("duplicate case ids in " + path) 55 return cases 56 57 58# Analysis add-in functions are not known by their English names to setFormula; 59# the case formulas keep the ODF names and are rewritten to the programmatic names. 60ADDIN = {"RECEIVED": "getReceived", "BESSELK": "getBesselk", "BESSELY": "getBessely"} 61 62 63def api_formula(f): 64 for name, prog in ADDIN.items(): 65 f = re.sub(r"(?<![A-Za-z0-9_.])%s\(" % name, "com.sun.star.sheet.addin.Analysis.%s(" % prog, f) 66 return f 67 68 69NOT_AVAILABLE = 32767 70 71 72def elem_match(exp, cell): 73 # cell: dict with rt (CellContentType name of the formula result; errors report VALUE), err, str, val 74 if exp == "#N/A": 75 return cell["err"] == NOT_AVAILABLE 76 if exp == "#ERR": 77 return cell["err"] != 0 78 if cell["err"] != 0: 79 return False 80 if exp.startswith("s:"): 81 return cell["rt"] == "TEXT" and cell["str"] == exp[2:] 82 tol = 1e-9 83 if exp.startswith("~"): 84 exp, tol = exp[1:], 1e-6 85 e = float(exp) 86 return cell["rt"] == "VALUE" and isinstance(cell["val"], float) and abs(cell["val"] - e) <= tol * max(1.0, abs(e)) 87 88 89def judge(expected, cells): 90 if expected == "-": 91 return "record" 92 exps = expected.split("|") 93 if len(exps) != len(cells): 94 return "mismatch" 95 return "match" if all(elem_match(e, c) for e, c in zip(exps, cells)) else "mismatch" 96 97 98def num(v): 99 return v if math.isfinite(v) else repr(v) 100 101 102def cell_result(cell): 103 try: 104 rt = cell.getPropertyValue("FormulaResultType").value 105 except Exception: 106 rt = None 107 return {"rt": rt, "err": cell.getError(), "str": cell.getString(), "val": num(float(cell.getValue()))} 108 109 110def setup_data(doc): 111 sheets = doc.getSheets() 112 sheets.insertNewByName("Other", 1) 113 sh = sheets.getByIndex(0) 114 115 def put(addr, vals): 116 # vals: list of rows; None leaves the cell empty; str is text 117 a0 = sh.getCellRangeByName(addr).getRangeAddress() 118 for r, row in enumerate(vals): 119 for c, v in enumerate(row): 120 if v is None: 121 continue 122 cell = sh.getCellByPosition(a0.StartColumn + c, a0.StartRow + r) 123 if isinstance(v, str): 124 cell.setString(v) 125 else: 126 cell.setValue(v) 127 128 put("A1", [[v] for v in "yxxyxyxxy"]) 129 put("B1", [[v] for v in "yxyyyyxyx"]) 130 put("D1", [[10 * i, 11 * i, 101 * i] for i in range(1, 6)]) 131 put("H1", [[10 * i for i in range(1, 6)], [11 * i for i in range(1, 6)], [101 * i for i in range(1, 6)]]) 132 put("N1", [[v] for v in (3, 5, 8, 9, 13, 14)]) 133 put("O1", [[v] for v in (1, 2, 3, 4, 5, 6)]) 134 put("P1", [[v] for v in (2, 4, 7, 15, 30, 58)]) 135 put("R1", [["Name", "Age", "Score"], ["A", 20, 5], ["B", 30, "n/a"], ["C", 40, 15], ["D", 50, None], ["E", 60, 25]]) 136 put("V1", [["Age"], [">=30"]]) 137 put("W1", [["Age"], [None]]) 138 put("Y1", [["Name"], ["A"]]) 139 put("AH1", [[v] for v in (6, 6, 6, 6, 3, 2, 1, 2, 6, 4, 2)]) 140 put("AI1", [[v] for v in (1, 3, 5)]) 141 put("AJ1", [[v] for v in (2, 4, 7, 9)]) 142 put("AK1", [[v] for v in (2, 4, 7, 9)]) 143 return sh 144 145 146def office_version(ctx): 147 try: 148 from unohelp import pv 149 cp = ctx.ServiceManager.createInstanceWithContext("com.sun.star.configuration.ConfigurationProvider", ctx) 150 ca = cp.createInstanceWithArguments("com.sun.star.configuration.ConfigurationAccess", 151 (pv("nodepath", "/org.openoffice.Setup/Product"),)) 152 return "%s %s" % (ca.getByName("ooName"), ca.getByName("ooSetupVersion")) 153 except Exception as e: 154 return "unknown (%s)" % e 155 156 157def run(port, cases_path, out_path): 158 from unohelp import connect, desktop, pv 159 cases = read_cases(cases_path) 160 ctx = connect(port) 161 desk = desktop(ctx) 162 doc = desk.loadComponentFromURL("private:factory/scalc", "_blank", 0, (pv("Hidden", True),)) 163 if doc is None: 164 raise SystemExit("could not create a Calc document") 165 results = [] 166 aborted = None 167 try: 168 sh = setup_data(doc) 169 scratch = sh.getCellRangeByName("AA1:AF12") 170 for case in cases: 171 kind, cols, rows, row = parse_mode(case["mode"]) 172 rec = dict(case) 173 try: 174 formula = api_formula(case["formula"]) 175 if kind == "cell": 176 cell = sh.getCellByPosition(26, row - 1) 177 cell.setFormula(formula) 178 cells = [cell] 179 else: 180 rng = sh.getCellRangeByPosition(26, 0, 26 + cols - 1, rows - 1) 181 rng.setArrayFormula(formula) 182 cells = [sh.getCellByPosition(26 + c, r) for r in range(rows) for c in range(cols)] 183 doc.calculateAll() 184 rec["formula_used"] = formula 185 res = [cell_result(c) for c in cells] 186 rec["readback"] = cells[0].getFormula() 187 rec["result"] = res 188 rec["status"] = judge(case["expected"], res) 189 except Exception as e: 190 rec["exception"] = "%s: %s" % (type(e).__name__, e) 191 rec["status"] = "exception" 192 if "Disposed" in type(e).__name__ or "connect" in str(e).lower(): 193 aborted = case["id"] 194 results.append(rec) 195 try: 196 scratch.clearContents(1 | 2 | 4 | 8 | 16 | 32) 197 except Exception as e: 198 aborted = aborted or case["id"] 199 rec["exception"] = rec.get("exception", "") + " clear: %s" % e 200 if aborted: 201 break 202 finally: 203 try: 204 doc.close(True) 205 except Exception: 206 pass 207 out = {"office": office_version(ctx) if not aborted else "unknown", "cases_file": cases_path, 208 "aborted_at": aborted, "cases": results} 209 with open(out_path, "w", encoding="utf-8") as f: 210 json.dump(out, f, indent=1, ensure_ascii=False) 211 f.write("\n") 212 n = len(results) 213 st = {} 214 for r in results: 215 st[r["status"]] = st.get(r["status"], 0) + 1 216 nerr = sum(1 for r in results if r["status"] != "exception" and any(c["err"] for c in r["result"])) 217 print("cases run %d of %d: %s; cases with an error value in the result: %d%s" % ( 218 n, len(cases), ", ".join("%s=%d" % kv for kv in sorted(st.items())), nerr, 219 "; ABORTED at " + aborted if aborted else "")) 220 for r in results: 221 if r["status"] == "mismatch": 222 print("MISMATCH %-28s %-12s expected %s got %s" % (r["id"], r["mode"], r["expected"], summary(r))) 223 return 1 if aborted else 0 224 225 226def summary(rec): 227 if "result" not in rec: 228 return rec.get("exception", "?") 229 return " | ".join("err=%d" % c["err"] if c["err"] else 230 ("%r" % c["val"] if c["rt"] == "VALUE" else "%r" % c["str"]) for c in rec["result"]) 231 232 233def same(a, b): 234 if "result" not in a or "result" not in b: 235 return a.get("exception") == b.get("exception") and ("result" in a) == ("result" in b) 236 if a["readback"] != b["readback"] or len(a["result"]) != len(b["result"]): 237 return False 238 for x, y in zip(a["result"], b["result"]): 239 if (x["rt"], x["err"], x["str"]) != (y["rt"], y["err"], y["str"]): 240 return False 241 vx, vy = x["val"], y["val"] 242 if isinstance(vx, float) and isinstance(vy, float): 243 if abs(vx - vy) > 1e-12 * max(1.0, abs(vx)): 244 return False 245 elif vx != vy: 246 return False 247 return True 248 249 250def compare(old_path, new_path): 251 old = {r["id"]: r for r in json.load(open(old_path, encoding="utf-8"))["cases"]} 252 new = {r["id"]: r for r in json.load(open(new_path, encoding="utf-8"))["cases"]} 253 changed = 0 254 for i in old: 255 if i not in new: 256 print("REMOVED %s" % i); changed += 1 257 elif not same(old[i], new[i]): 258 changed += 1 259 print("CHANGED %-28s %-12s %s\n old: %s [%s]\n new: %s [%s]" % ( 260 i, new[i]["mode"], new[i]["formula"], summary(old[i]), old[i]["status"], 261 summary(new[i]), new[i]["status"])) 262 for i in new: 263 if i not in old: 264 print("ADDED %s" % i); changed += 1 265 print("%d cases compared, %d changed" % (len(set(old) & set(new)), changed)) 266 return 1 if changed else 0 267 268 269def main(argv): 270 if len(argv) == 4 and argv[1] == "--compare": 271 return compare(argv[2], argv[3]) 272 if len(argv) == 4: 273 return run(int(argv[1]), argv[2], argv[3]) 274 print("usage: runpy.sh calcval.py <port> <cases.tsv> <out.json>\n" 275 " python3 -I calcval.py --compare old.json new.json", file=sys.stderr) 276 return 2 277 278 279sys.exit(main(sys.argv)) 280