Spaces:
Sleeping
Sleeping
File size: 9,570 Bytes
d075fca | 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 | """
Core environment logic for the SQL query environment.
Each episode creates a fresh in-memory SQLite database for the chosen
domain so there's no state leaking between episodes. SQLite in-memory
databases are cheap to spin up so this doesn't hurt performance.
TODO(team): might want to add a "curriculum mode" that auto-selects
tasks of increasing difficulty within a session. For now we just let
the caller pick or we cycle through randomly.
"""
import random
import sqlite3
from typing import Dict, List, Optional
from uuid import uuid4
from openenv.core.env_server.interfaces import Environment
from openenv.core.env_server.types import State
# Handle both import paths — running via `uv run server` uses a different
# package layout than `python -m sql_query_env.server.app`
try:
from ..models import SqlQueryAction, SqlQueryObservation
except ImportError:
from models import SqlQueryAction, SqlQueryObservation
try:
from .tasks import (
ALL_TASKS,
ALL_TASKS_LIST,
SCHEMAS,
TASK_MAP,
Task,
create_database,
get_expected_result,
)
from .graders import grade_query, GradeResult
except ImportError:
from server.tasks import (
ALL_TASKS,
ALL_TASKS_LIST,
SCHEMAS,
TASK_MAP,
Task,
create_database,
get_expected_result,
)
from server.graders import grade_query, GradeResult
class SqlQueryEnvironment(Environment):
"""Main environment class - presents schema + question, grades SQL.
Steps are independent in terms of DB state (no writes accumulate).
We track query history and best score across steps for penalty logic
and progressive hints.
"""
SUPPORTS_CONCURRENT_SESSIONS: bool = True
def __init__(self):
self._state = State(episode_id=str(uuid4()), step_count=0)
self._db: Optional[sqlite3.Connection] = None
self._current_task: Optional[Task] = None
self._current_schema_id: Optional[str] = None
self._previous_queries: List[str] = []
self._best_score: float = 0.0
self._step_rewards: List[float] = []
self._task_queue: List[str] = []
self._current_task_index: int = 0
def _get_task_list(self) -> List[str]:
"""Shuffle task IDs for random ordering."""
all_ids = [t.id for t in ALL_TASKS_LIST]
random.shuffle(all_ids)
return all_ids
def _select_task(self, task_id: Optional[str] = None) -> Task:
"""Pick a specific task or grab the next one from the queue."""
if task_id and task_id in TASK_MAP:
return TASK_MAP[task_id]
# lazy-init the queue
if not self._task_queue:
self._task_queue = self._get_task_list()
self._current_task_index = 0
if self._current_task_index >= len(self._task_queue):
self._current_task_index = 0
task = TASK_MAP[self._task_queue[self._current_task_index]]
self._current_task_index += 1
return task
def reset(self, task_id: Optional[str] = None) -> SqlQueryObservation:
"""Start a new episode.
Creates a fresh DB for the task's domain and returns the initial
observation with schema, question, and hints.
"""
self._state = State(episode_id=str(uuid4()), step_count=0)
self._previous_queries = []
self._best_score = 0.0
self._step_rewards = []
self._current_task = self._select_task(task_id)
self._current_schema_id = self._current_task.schema_id
# close old connection if any
if self._db:
try:
self._db.close()
except Exception:
pass
self._db = create_database(self._current_schema_id)
schema = SCHEMAS[self._current_schema_id]
expected_rows = get_expected_result(self._db, self._current_task.ground_truth_query)
return SqlQueryObservation(
task_id=self._current_task.id,
difficulty=self._current_task.difficulty,
database_domain=schema.name,
question=self._current_task.question,
schema_description=schema.description,
query_result="",
query_error=None,
feedback=(
f"Task: {self._current_task.description}\n"
f"Database: {schema.name}\n"
f"Difficulty: {self._current_task.difficulty}\n"
f"Attempts available: {self._current_task.max_steps}\n"
f"\nHints:\n" + "\n".join(f" - {h}" for h in self._current_task.hints[:1])
),
diagnostics=[],
efficiency_notes=[],
expected_row_count=len(expected_rows),
expected_columns=self._current_task.expected_columns,
steps_remaining=self._current_task.max_steps,
current_score=0.0,
history=[],
done=False,
reward=0.0,
)
def _progressive_hints(self, step: int) -> str:
"""Reveal more hints as the agent struggles.
Step 1: just the first hint
Step 2-3: a few more
Step 4+: everything we've got
"""
task = self._current_task
hints = task.hints
if step <= 1:
shown = hints[:1] if hints else []
elif step <= 3:
shown = hints[:min(step + 1, len(hints))]
else:
shown = hints
if not shown:
return ""
return "\n".join(f" - {h}" for h in shown)
def step(self, action: SqlQueryAction) -> SqlQueryObservation:
"""Evaluate a submitted SQL query and return graded feedback."""
if self._current_task is None or self._db is None:
return SqlQueryObservation(
feedback="Environment not initialized. Call reset() first.",
done=True,
reward=0.0,
)
self._state.step_count += 1
step = self._state.step_count
steps_remaining = self._current_task.max_steps - step
# grade it
grade_result = grade_query(
conn=self._db,
query=action.query,
task=self._current_task,
previous_queries=self._previous_queries,
)
self._previous_queries.append(action.query)
self._step_rewards.append(grade_result.total_score)
self._best_score = max(self._best_score, grade_result.total_score)
# done if near-perfect or out of steps
is_done = grade_result.total_score >= 0.95 or steps_remaining <= 0
# build history for the observation
history = [
{
"step": i + 1,
"query": q[:100] + "..." if len(q) > 100 else q,
"score": round(s, 2),
}
for i, (q, s) in enumerate(
zip(self._previous_queries, self._step_rewards)
)
]
feedback = grade_result.feedback
# add hints if the agent is struggling
if not is_done and grade_result.total_score < 0.95:
hint_text = self._progressive_hints(step)
if hint_text:
feedback += f"\n\nHints (attempt {step}):\n{hint_text}"
if is_done:
if grade_result.total_score >= 0.95:
feedback += "\n\nNear-perfect score achieved."
else:
feedback += f"\n\nEpisode finished. Best score: {self._best_score:.2f}"
feedback += f"\nScore progression: {' -> '.join(f'{s:.2f}' for s in self._step_rewards)}"
expected_rows = get_expected_result(self._db, self._current_task.ground_truth_query)
schema = SCHEMAS[self._current_schema_id]
return SqlQueryObservation(
task_id=self._current_task.id,
difficulty=self._current_task.difficulty,
database_domain=schema.name,
question=self._current_task.question,
schema_description=schema.description,
query_result=grade_result.query_result or "",
query_error=grade_result.error,
feedback=feedback,
diagnostics=[d.to_dict() for d in grade_result.diagnostics],
efficiency_notes=grade_result.efficiency_notes,
expected_row_count=len(expected_rows),
expected_columns=self._current_task.expected_columns,
steps_remaining=max(0, steps_remaining),
current_score=self._best_score,
history=history,
done=is_done,
reward=grade_result.total_score,
metadata={
"task_id": self._current_task.id,
"difficulty": self._current_task.difficulty,
"database_domain": self._current_schema_id,
"step": step,
"best_score": self._best_score,
"syntax_score": grade_result.syntax_score,
"table_score": grade_result.table_score,
"column_score": grade_result.column_score,
"result_score": grade_result.result_score,
"efficiency_score": grade_result.efficiency_score,
"penalty": grade_result.penalty,
"challenge_type": self._current_task.challenge_type,
},
)
@property
def state(self) -> State:
return self._state
def close(self):
if self._db:
try:
self._db.close()
except Exception:
pass
self._db = None
|