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}"