""" 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.")