""" app/executor.py =============== Pure SQLite execution layer for the SQL Query Repair environment. Responsibilities: - Seed a fresh in-memory SQLite database from fixtures.sql - Execute arbitrary SQL safely and return results or structured errors - Compute Jaccard similarity between result rows and expected rows - Classify error messages into human-readable feedback strings This module has NO side effects on the environment state — it is stateless and can be called freely from env.py. """ from __future__ import annotations import os import re import sqlite3 from pathlib import Path from typing import Any, List, Optional, Tuple # Path to the seed SQL file (relative to project root) _FIXTURES_PATH = Path(__file__).parent.parent / "db" / "fixtures.sql" # Hard cap on rows returned to prevent memory abuse _MAX_ROWS = 500 # --------------------------------------------------------------------------- # Public types # --------------------------------------------------------------------------- class ExecutionResult: """ Returned by every execute_* call. Attributes ---------- ok : True if the query ran without a SQLite error. rows : List of tuples representing result rows (empty on error). row_count : len(rows), convenience shortcut. error : SQLite error string if ok=False, else None. feedback : Human-readable one-liner (safe to send to agent). """ __slots__ = ("ok", "rows", "row_count", "error", "feedback") def __init__( self, ok: bool, rows: List[Tuple[Any, ...]], error: Optional[str], feedback: str, ) -> None: self.ok = ok self.rows = rows self.row_count = len(rows) self.error = error self.feedback = feedback def __repr__(self) -> str: # pragma: no cover if self.ok: return f"ExecutionResult(ok=True, rows={self.row_count})" return f"ExecutionResult(ok=False, error={self.error!r})" # --------------------------------------------------------------------------- # Database factory # --------------------------------------------------------------------------- def build_fresh_db() -> sqlite3.Connection: """ Create and return a new in-memory SQLite connection seeded with fixtures.sql. Each episode calls this once inside reset(). Returns ------- sqlite3.Connection A fully seeded, ready-to-use connection. Caller owns it and must call .close() when the episode ends. Raises ------ FileNotFoundError If fixtures.sql cannot be found. sqlite3.Error If the fixture script contains invalid SQL (should never happen in production; would indicate a bug in fixtures.sql). """ if not _FIXTURES_PATH.exists(): raise FileNotFoundError(f"fixtures.sql not found at {_FIXTURES_PATH}") conn = sqlite3.connect(":memory:") conn.row_factory = sqlite3.Row # rows accessible by column name conn.execute("PRAGMA foreign_keys = ON") conn.execute("PRAGMA journal_mode = MEMORY") sql = _FIXTURES_PATH.read_text(encoding="utf-8") conn.executescript(sql) conn.commit() return conn # --------------------------------------------------------------------------- # Query execution helpers # --------------------------------------------------------------------------- def execute_query(conn: sqlite3.Connection, sql: str) -> ExecutionResult: """ Execute *sql* against *conn* and return an ExecutionResult. - SELECT queries: rows are returned as plain tuples (up to _MAX_ROWS). - Non-SELECT DML: rows=[], ok=True if no error. - Any SQLite error: ok=False, error contains the raw message. The agent never receives raw row data — only row counts and error messages — which is enforced at the env.py layer, not here. """ sql = sql.strip() if not sql: return ExecutionResult( ok=False, rows=[], error="empty query", feedback="Query is empty. Please provide a SQL statement.", ) try: cursor = conn.execute(sql) if cursor.description is not None: # SELECT-like query raw_rows = cursor.fetchmany(_MAX_ROWS) rows = [tuple(r) for r in raw_rows] feedback = f"Query ran successfully — {len(rows)} row(s) returned." return ExecutionResult(ok=True, rows=rows, error=None, feedback=feedback) else: # DML (INSERT/UPDATE/DELETE) — valid but unusual in this env conn.rollback() # do not persist side effects return ExecutionResult( ok=True, rows=[], error=None, feedback="Statement executed (no rows returned).", ) except sqlite3.OperationalError as exc: msg = str(exc) return ExecutionResult( ok=False, rows=[], error=msg, feedback=_humanize_error(msg, sql), ) except sqlite3.Error as exc: msg = str(exc) return ExecutionResult( ok=False, rows=[], error=msg, feedback=f"SQL error: {msg}", ) def execute_expected(conn: sqlite3.Connection, correct_sql: str) -> List[Tuple[Any, ...]]: """ Execute the reference correct_query and return its rows as plain tuples. Called once at reset() time to cache expected_rows for the episode. Raises ------ RuntimeError If the reference query itself errors — indicates a bug in the dataset. """ result = execute_query(conn, correct_sql) if not result.ok: raise RuntimeError( f"Reference query failed — dataset bug!\n" f" query: {correct_sql!r}\n" f" error: {result.error}" ) return result.rows # --------------------------------------------------------------------------- # Scoring # --------------------------------------------------------------------------- def jaccard_score( result_rows: List[Tuple[Any, ...]], expected_rows: List[Tuple[Any, ...]], ) -> float: """ Compute Jaccard similarity between two sets of rows. Jaccard = |intersection| / |union| Rows are treated as *sets* (duplicates collapsed) for a fair comparison that isn't sensitive to ordering or duplicate counts. Returns a float in [0.0, 1.0]. Special case: if both sets are empty, returns 1.0 (both correct). """ set_r = set(result_rows) set_e = set(expected_rows) if not set_r and not set_e: return 1.0 intersection = len(set_r & set_e) union = len(set_r | set_e) if union == 0: return 1.0 return round(intersection / union, 4) def score_to_reward(jaccard: float, turns_used: int, max_turns: int) -> float: """ Convert a Jaccard correctness score to a step reward for submit_query. Reward tiers ------------ jaccard == 1.0 → base +1.0 (perfect) jaccard >= 0.8 → base +0.5 (close) jaccard >= 0.5 → base +0.2 (partial) jaccard < 0.5 → base -0.5 (wrong) Turn penalty ------------ penalty = 0.05 * (turns_used - 1) Subtracted from base; encourages solving in fewer turns. Minimum reward is clamped to -1.0. Returns ------- float in [-1.0, 1.0] """ if jaccard == 1.0: base = 1.0 elif jaccard >= 0.8: base = 0.5 elif jaccard >= 0.5: base = 0.2 else: base = -0.5 penalty = 0.05 * max(0, turns_used - 1) reward = base - penalty return round(max(-1.0, min(1.0, reward)), 4) # --------------------------------------------------------------------------- # Clarification matching # --------------------------------------------------------------------------- def match_clarification(question: str, answers: dict[str, str]) -> Optional[str]: """ Fuzzy-match *question* against the task's clarification_answers dict. Matching strategy (case-insensitive, in priority order): 1. Exact key match after lowercasing and stripping punctuation. 2. Key appears as a substring of the question. 3. Any word in the key appears in the question. Returns the answer string on match, or None if no match found. """ if not answers: return None q_clean = _clean(question) # Priority 1 — exact match for key, answer in answers.items(): if _clean(key) == q_clean: return answer # Priority 2 — key is substring of question for key, answer in answers.items(): if _clean(key) in q_clean: return answer # Priority 3 — any word from the key appears in question q_words = set(q_clean.split()) for key, answer in answers.items(): key_words = set(_clean(key).split()) if key_words & q_words: return answer return None # --------------------------------------------------------------------------- # Internal helpers # --------------------------------------------------------------------------- def _clean(text: str) -> str: """Lowercase, strip punctuation, collapse whitespace.""" text = text.lower() text = re.sub(r"[^\w\s]", " ", text) text = re.sub(r"\s+", " ", text).strip() return text def _humanize_error(sqlite_msg: str, sql: str) -> str: """ Convert raw SQLite error messages into concise, agent-friendly hints. """ msg = sqlite_msg.lower() if "syntax error" in msg: # Try to extract the offending token near = re.search(r'near "([^"]+)"', sqlite_msg) if near: return f"Syntax error near '{near.group(1)}'. Check your SQL syntax around that token." return "Syntax error in query. Check for typos, missing keywords, or invalid operators." if "no such table" in msg: tbl = re.search(r"no such table: (\S+)", sqlite_msg) name = tbl.group(1) if tbl else "unknown" return f"Table '{name}' does not exist. Check the schema for correct table names." if "no such column" in msg: col = re.search(r"no such column: (\S+)", sqlite_msg) name = col.group(1) if col else "unknown" return f"Column '{name}' does not exist. Check the schema for correct column names." if "ambiguous column" in msg: col = re.search(r"ambiguous column name: (\S+)", sqlite_msg) name = col.group(1) if col else "unknown" return f"Column '{name}' is ambiguous. Qualify it with a table alias (e.g. t.{name})." if "no such function" in msg: fn = re.search(r"no such function: (\S+)", sqlite_msg) name = fn.group(1) if fn else "unknown" return f"Function '{name}' is not recognised. Check the function name." if "misuse of aggregate" in msg: return "Aggregate function (COUNT/SUM/AVG) used in wrong clause. Use HAVING for aggregates, not WHERE." if "foreign key constraint" in msg: return "Foreign key constraint failed. Ensure referenced IDs exist in the parent table." # Fallback return f"SQL error: {sqlite_msg}"