sql-repair-env / app /executor.py
ashoka0402
clean commit (removed token)
64ccc23
Raw History Blame Contribute Delete
11.2 kB
"""
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}"