File size: 3,546 Bytes
215918e | 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 | """Sandboxed SQL execution and result comparison.
run(): read-only, single statement, no ATTACH, 5 s timeout.
match(): multiset by default; ordered when the gold is ordered; column permutation
allowed; floats rounded to 4 dp (and 3.0 == 3). set_match(): BIRD-style set EX.
"""
import itertools
import sqlite3
import time
from collections import Counter
TIMEOUT_S = 5.0
MAX_ROWS = 100_000
class SQLError(Exception):
pass
def _deny_attach(action, *_):
return sqlite3.SQLITE_DENY if action in (sqlite3.SQLITE_ATTACH, sqlite3.SQLITE_DETACH) else sqlite3.SQLITE_OK
def run(db_path: str, sql: str, timeout: float = TIMEOUT_S) -> list[tuple]:
"""Execute one read-only query; raise SQLError on any failure."""
con = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True, check_same_thread=False)
try:
con.execute("PRAGMA query_only = ON")
con.set_authorizer(_deny_attach)
deadline = time.monotonic() + timeout
con.set_progress_handler(lambda: time.monotonic() > deadline, 10_000)
cur = con.execute(sql) # sqlite3 refuses multiple statements here
rows = cur.fetchmany(MAX_ROWS + 1)
if len(rows) > MAX_ROWS:
raise SQLError("too many rows")
return rows
except sqlite3.Error as e:
raise SQLError(str(e)) from e
finally:
con.close()
def _norm(v):
if isinstance(v, float):
v = round(v, 4)
return int(v) if v.is_integer() else v
if isinstance(v, bytes):
return v.hex()
return v
def normalize(rows) -> list[tuple]:
return [tuple(_norm(v) for v in r) for r in rows]
def _key(v):
return (v is None, type(v).__name__, v if v is not None else 0)
def match(pred, gold, ordered: bool = False) -> bool:
"""True if pred equals gold up to column permutation (multiset, or list if ordered)."""
pred, gold = normalize(pred), normalize(gold)
if len(pred) != len(gold):
return False
if not gold:
return True
ncol = len(gold[0])
if any(len(r) != ncol for r in pred):
return False
gcols = [sorted((r[j] for r in gold), key=_key) for j in range(ncol)]
pcols = [sorted((r[j] for r in pred), key=_key) for j in range(ncol)]
# candidate pred columns for each gold column = same sorted values
cands = [[i for i in range(ncol) if pcols[i] == gcols[j]] for j in range(ncol)]
if any(not c for c in cands):
return False
target = gold if ordered else Counter(gold)
for perm in itertools.product(*cands):
if len(set(perm)) != ncol:
continue
mapped = [tuple(r[i] for i in perm) for r in pred]
if (mapped if ordered else Counter(mapped)) == target:
return True
return False
def set_match(pred, gold) -> bool:
"""BIRD/Spider-style execution accuracy: set of rows, column order as written."""
return set(normalize(pred)) == set(normalize(gold))
if __name__ == "__main__":
assert match([(1, "a"), (1, "a")], [(1, "a"), (1, "a")])
assert not match([(1, "a")], [(1, "a"), (1, "a")]) # duplicates matter
assert set_match([(1, "a")], [(1, "a"), (1, "a")]) # ...but not for set EX
assert match([("a", 1)], [(1, "a")]) # column permutation
assert match([(2.00000001,)], [(2,)]) # float rounding
assert not match([(1,), (2,)], [(2,), (1,)], ordered=True)
assert match([(1,), (2,)], [(2,), (1,)])
assert not match([(1, 2)], [(1, 2, 3)])
print("compare.py self-check ok")
|