Spaces:
Sleeping
Sleeping
File size: 11,206 Bytes
64ccc23 | 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 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 | """
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}"
|