Spaces:
Sleeping
Sleeping
Download app/env.py from skillissue04/sql-repair-env: direct link, hf CLI and curl.
- Browser
- Download file 15 kB
-
https://huggingface.co/spaces/skillissue04/sql-repair-env/resolve/main/app/env.py
- Command line
-
hf download hf://spaces/skillissue04/sql-repair-env/app/env.py
-
curl -L -o env.py https://huggingface.co/spaces/skillissue04/sql-repair-env/resolve/main/app/env.py
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.") | |