sql-repair-env / app /env.py
ashoka0402
clean commit (removed token)
64ccc23
Raw History Blame Contribute Delete
15 kB
"""
app/env.py
==========
Core environment class for the Interactive SQL Query Repair environment.
Public interface
----------------
env = SQLRepairEnv()
obs = env.reset(task_id, difficulty)
step_result = env.step(action)
state = env.state()
grader_result = env.grade()
env.close()
Design principles
-----------------
- Fully deterministic: same task_id always produces same episode trajectory.
- No randomness anywhere in this class.
- All SQLite I/O is delegated to executor.py.
- Grading logic lives in grader.py; env.py only accumulates raw stats.
- close() always releases the SQLite connection, even if step() raised.
"""
from __future__ import annotations
import sqlite3
from typing import Dict, List, Optional, Tuple
from app.data_loader import get_task
from app.executor import (
ExecutionResult,
build_fresh_db,
execute_expected,
execute_query,
jaccard_score,
match_clarification,
score_to_reward,
)
from app.models import (
ActionType,
AskClarificationAction,
Difficulty,
EnvState,
GraderResult,
Observation,
RunPartialAction,
StepInfo,
StepResult,
SubmitQueryAction,
Task,
)
# Max turns per difficulty level
_MAX_TURNS: Dict[Difficulty, int] = {
Difficulty.easy: 3,
Difficulty.medium: 5,
Difficulty.hard: 8,
}
# Rewards for non-submit actions
_REWARD_PARTIAL_OK = 0.05 # run_partial succeeded
_REWARD_PARTIAL_ERROR = -0.10 # run_partial had a SQL error
_REWARD_CLARIFY_HIT = 0.00 # clarification answered
_REWARD_CLARIFY_MISS = -0.05 # clarification not answered
class SQLRepairEnv:
"""
Manages one episode of the Interactive SQL Query Repair task.
State machine
-------------
IDLE β†’ reset() β†’ RUNNING β†’ step() (repeat) β†’ DONE
↑_________________________|
(if done=True, must call reset() before step())
"""
def __init__(self) -> None:
self._task: Optional[Task] = None
self._conn: Optional[sqlite3.Connection] = None
self._expected_rows: List[Tuple] = []
self._turn: int = 0
self._max_turns: int = 0
self._done: bool = True # starts in IDLE
self._solved: bool = False
self._total_reward: float = 0.0
self._final_jaccard: float = 0.0
# Per-episode stats for grader
self._clarify_total: int = 0
self._clarify_hits: int = 0
self._partial_total: int = 0
# Last observation cache (for state() between steps)
self._last_obs: Optional[Observation] = None
# ------------------------------------------------------------------
# reset()
# ------------------------------------------------------------------
def reset(self, task_id: str, difficulty: Optional[Difficulty] = None) -> Observation:
"""
Start a new episode for the given task.
Parameters
----------
task_id : ID of the task to load (must exist in the dataset).
difficulty : Optional override; if provided, must match the task's
difficulty field (validation guard against mismatches).
Returns
-------
Observation
Initial observation with turn=0, no error, no clarification.
Raises
------
KeyError
If task_id is not found.
ValueError
If difficulty override does not match the task's difficulty.
RuntimeError
If the reference correct_query fails to execute (dataset bug).
"""
# Release any previous DB connection
self.close()
# Load task
task = get_task(task_id)
if difficulty is not None and task.difficulty != difficulty:
raise ValueError(
f"Task '{task_id}' has difficulty='{task.difficulty}', "
f"but difficulty='{difficulty}' was requested."
)
# Fresh DB
conn = build_fresh_db()
# Pre-compute expected rows from the reference query
expected_rows = execute_expected(conn, task.correct_query)
# Store state
self._task = task
self._conn = conn
self._expected_rows = expected_rows
self._max_turns = _MAX_TURNS[task.difficulty]
self._turn = 0
self._done = False
self._solved = False
self._total_reward = 0.0
self._final_jaccard = 0.0
self._clarify_total = 0
self._clarify_hits = 0
self._partial_total = 0
obs = self._make_observation(
error_message=None,
clarification_response=None,
last_action_type=None,
)
self._last_obs = obs
return obs
# ------------------------------------------------------------------
# step()
# ------------------------------------------------------------------
def step(self, action: SubmitQueryAction | RunPartialAction | AskClarificationAction) -> StepResult:
"""
Advance the environment by one turn.
Parameters
----------
action : One of SubmitQueryAction, RunPartialAction, AskClarificationAction.
Returns
-------
StepResult
Contains the next observation, reward, done flag, and diagnostics.
Raises
------
RuntimeError
If called before reset() or after the episode is already done.
"""
self._assert_running()
self._turn += 1
if action.type == ActionType.submit_query:
return self._handle_submit(action)
elif action.type == ActionType.run_partial:
return self._handle_partial(action)
elif action.type == ActionType.ask_clarification:
return self._handle_clarify(action)
else:
raise ValueError(f"Unknown action type: {action.type}")
# ------------------------------------------------------------------
# state()
# ------------------------------------------------------------------
def state(self) -> EnvState:
"""Return current environment state (safe to call at any time)."""
if self._task is None:
return EnvState(
task_id="",
difficulty=Difficulty.easy,
turn=0,
max_turns=0,
total_reward=0.0,
done=True,
solved=False,
)
return EnvState(
task_id=self._task.id,
difficulty=self._task.difficulty,
turn=self._turn,
max_turns=self._max_turns,
total_reward=round(self._total_reward, 4),
done=self._done,
solved=self._solved,
)
# ------------------------------------------------------------------
# grade()
# ------------------------------------------------------------------
def grade(self) -> GraderResult:
"""
Compute and return the final grader score for the current episode.
Can be called mid-episode (returns partial scores) or after done.
Score formula
-------------
correctness = final_jaccard (0.0 if not yet submitted)
turn_efficiency = 1 - (turns_used / max_turns)
clarification_quality = hits / total (1.0 if no clarifications asked)
final = 0.60 * correctness
+ 0.25 * turn_efficiency
+ 0.15 * clarification_quality
"""
from app.grader import compute_grade # avoid circular import
return compute_grade(
final_jaccard=self._final_jaccard,
turns_used=self._turn,
max_turns=self._max_turns,
clarify_total=self._clarify_total,
clarify_hits=self._clarify_hits,
solved=self._solved,
)
# ------------------------------------------------------------------
# close()
# ------------------------------------------------------------------
def close(self) -> None:
"""Release the SQLite connection. Safe to call multiple times."""
if self._conn is not None:
try:
self._conn.close()
except Exception:
pass
self._conn = None
# ------------------------------------------------------------------
# Private action handlers
# ------------------------------------------------------------------
def _handle_submit(self, action: SubmitQueryAction) -> StepResult:
"""Agent submits its final repaired query."""
result: ExecutionResult = execute_query(self._conn, action.query)
if result.ok:
jaccard = jaccard_score(result.rows, self._expected_rows)
else:
jaccard = 0.0
reward = score_to_reward(jaccard, self._turn, self._max_turns)
self._final_jaccard = jaccard
self._total_reward += reward
solved = jaccard == 1.0
self._solved = solved
self._done = True # submit always ends the episode
if solved:
feedback = f"Correct! Query returned exact expected rows. (jaccard=1.0)"
elif result.ok and jaccard > 0:
feedback = (
f"Partially correct β€” jaccard={jaccard:.2f}. "
f"Got {result.row_count} row(s), expected {len(self._expected_rows)}. "
f"{result.feedback}"
)
elif result.ok:
feedback = (
f"Query ran but returned wrong rows (jaccard=0.0). "
f"Got {result.row_count} row(s), expected {len(self._expected_rows)}."
)
else:
feedback = f"Query failed: {result.feedback}"
obs = self._make_observation(
error_message=result.error if not result.ok else None,
clarification_response=None,
last_action_type=ActionType.submit_query,
)
self._last_obs = obs
return StepResult(
observation=obs,
reward=reward,
done=True,
info=StepInfo(
feedback=feedback,
rows_correct=jaccard,
action_type=ActionType.submit_query,
turn=self._turn,
cumulative_reward=round(self._total_reward, 4),
),
)
def _handle_partial(self, action: RunPartialAction) -> StepResult:
"""Agent tests a query without finalising."""
self._partial_total += 1
result: ExecutionResult = execute_query(self._conn, action.query)
if result.ok:
reward = _REWARD_PARTIAL_OK
feedback = f"Partial run OK β€” {result.row_count} row(s) returned. No result data shown."
error_msg = None
else:
reward = _REWARD_PARTIAL_ERROR
feedback = result.feedback
error_msg = result.error
self._total_reward += reward
# Check turn budget
done = self._turn >= self._max_turns
if done:
self._done = True
obs = self._make_observation(
error_message=error_msg,
clarification_response=None,
last_action_type=ActionType.run_partial,
)
self._last_obs = obs
return StepResult(
observation=obs,
reward=reward,
done=done,
info=StepInfo(
feedback=feedback,
rows_correct=None,
action_type=ActionType.run_partial,
turn=self._turn,
cumulative_reward=round(self._total_reward, 4),
),
)
def _handle_clarify(self, action: AskClarificationAction) -> StepResult:
"""Agent asks a clarifying question about the schema or intent."""
self._clarify_total += 1
answer = match_clarification(
action.question,
self._task.clarification_answers,
)
if answer is not None:
self._clarify_hits += 1
reward = _REWARD_CLARIFY_HIT
response = answer
feedback = f"Clarification answered: {answer}"
else:
reward = _REWARD_CLARIFY_MISS
response = "No information available for that question."
feedback = "Clarification not found. Try rephrasing or asking about table/column names."
self._total_reward += reward
# Check turn budget
done = self._turn >= self._max_turns
if done:
self._done = True
obs = self._make_observation(
error_message=None,
clarification_response=response,
last_action_type=ActionType.ask_clarification,
)
self._last_obs = obs
return StepResult(
observation=obs,
reward=reward,
done=done,
info=StepInfo(
feedback=feedback,
rows_correct=None,
action_type=ActionType.ask_clarification,
turn=self._turn,
cumulative_reward=round(self._total_reward, 4),
),
)
# ------------------------------------------------------------------
# Private helpers
# ------------------------------------------------------------------
def _make_observation(
self,
error_message: Optional[str],
clarification_response: Optional[str],
last_action_type: Optional[ActionType],
) -> Observation:
"""Build an Observation from current episode state."""
assert self._task is not None
schema_hint = " | ".join(self._task.schema.tables)
return Observation(
task_id=self._task.id,
difficulty=self._task.difficulty,
intent=self._task.intent,
schema_hint=schema_hint,
broken_query=self._task.broken_query,
turn=self._turn,
max_turns=self._max_turns,
error_message=error_message,
clarification_response=clarification_response,
last_action_type=last_action_type,
)
def _assert_running(self) -> None:
"""Raise if the env is not in RUNNING state."""
if self._task is None:
raise RuntimeError("Environment not initialised. Call reset() first.")
if self._done:
raise RuntimeError(
"Episode is already done. Call reset() to start a new episode."
)
if self._conn is None:
raise RuntimeError("Database connection lost. Call reset() to reinitialise.")