sql-repair-env / README.md
Savita Srivastava
readme harshit ne hi udaya tha
19e8836
|
Raw History Blame Contribute Delete
7.62 kB
---
title: SQL Repair Env
colorFrom: blue
colorTo: green
sdk: docker
app_port: 7860
---
# Interactive SQL Query Repair Environment
An OpenEnv-compatible reinforcement learning environment where an agent
learns to fix broken SQL queries through multi-turn interaction.
---
## Overview
The agent receives a **broken SQL query**, a **plain-English intent**, and a
**compact schema hint**. Over multiple turns it can:
- **Run partial queries** β€” test a fix and see whether SQLite errors
- **Ask clarification questions** β€” probe the schema or intent for hints
- **Submit a final query** β€” evaluated against ground-truth SQLite execution
Grading is fully objective: does the repaired query return the correct rows?
---
## Environment Details
### Action Space
| Action | Fields | Effect |
| ------------------- | --------------- | --------------------------------------------------- |
| `submit_query` | `query: str` | Finalises the episode; scored by Jaccard similarity |
| `run_partial` | `query: str` | Tests a query; returns error message or `OK` |
| `ask_clarification` | `question: str` | Returns a schema/intent hint if available |
### Observation Space
| Field | Type | Description |
| ------------------------ | ------------------ | -------------------------------------------------- |
| `task_id` | str | Unique task identifier |
| `difficulty` | easy\|medium\|hard | Difficulty level |
| `intent` | str | Plain-English goal for the query |
| `schema_hint` | str | Compact table signatures |
| `broken_query` | str | The query to fix (unchanged across turns) |
| `turn` | int | Current turn number |
| `max_turns` | int | Maximum turns for this difficulty |
| `error_message` | str\|null | SQLite error from last run_partial / failed submit |
| `clarification_response` | str\|null | Answer to last ask_clarification |
| `last_action_type` | str\|null | Previous action type |
### Reward Function
```
submit_query:
jaccard == 1.0 β†’ +1.0 (perfect)
jaccard >= 0.8 β†’ +0.5 (close)
jaccard >= 0.5 β†’ +0.2 (partial)
jaccard < 0.5 β†’ -0.5 (wrong)
minus turn_penalty = 0.05 Γ— (turns_used - 1)
clamped to [-1.0, 1.0]
run_partial:
no SQL error β†’ +0.05
SQL error β†’ -0.10
ask_clarification:
answer found β†’ 0.00
no answer β†’ -0.05
```
### Grader Formula
```
score = 0.60 Γ— correctness
+ 0.25 Γ— turn_efficiency
+ 0.15 Γ— clarification_quality
correctness = final Jaccard score
turn_efficiency = 1 - (turns_used / max_turns)
clarification_quality = useful_hits / total_asks (1.0 if no asks)
```
All scores are deterministic and in `[0.0, 1.0]`.
---
## Tasks
### Easy (15 tasks, max 3 turns)
- Single table (`users`)
- Error types: syntax errors, wrong operators, missing keywords
- Examples: `COUNTS(*)` β†’ `COUNT(*)`, `ORDER age` β†’ `ORDER BY age`
### Medium (15 tasks, max 5 turns)
- Two tables (`users + orders` or `orders + products`)
- Error types: wrong join keys, `WHERE` vs `HAVING`, missing `LEFT JOIN`
- Examples: join on `orders.id` instead of `orders.user_id`
### Hard (10 tasks, max 8 turns)
- Three to five tables with CTEs and subqueries
- Error types: nested aggregates, correlated subqueries, multi-table joins
- Designed so naive first-attempt strategies score ≀ 0.40
- Examples: `AVG(SUM(...))` requires CTE, tag intersection requires self-join
---
## API Reference
| Endpoint | Method | Description |
| --------- | ------ | ------------------------------- |
| `/health` | GET | Liveness probe |
| `/tasks` | GET | List all 40 tasks |
| `/reset` | POST | Start a new episode |
| `/step` | POST | Take one action |
| `/state` | GET | Current episode state |
| `/grader` | GET | Final score for current episode |
### POST /reset
```json
{
"task_id": "medium_001",
"difficulty": "medium"
}
```
### POST /step β€” submit_query
```json
{
"type": "submit_query",
"query": "SELECT DISTINCT u.name FROM users u JOIN orders o ON u.id = o.user_id"
}
```
### POST /step β€” run_partial
```json
{
"type": "run_partial",
"query": "SELECT * FROM users WHERE age > 30"
}
```
### POST /step β€” ask_clarification
```json
{
"type": "ask_clarification",
"question": "what column in orders links to users?"
}
```
---
## Setup & Running
### Local (Python)
```bash
pip install -r requirements.txt
uvicorn server.app:app --host 0.0.0.0 --port 7860
```
### Docker
```bash
docker build -t sql-repair-env .
docker run -p 8000:8000 sql-repair-env
```
Test it:
```bash
curl http://localhost:7860/health
curl http://localhost:7860/tasks
curl -X POST http://localhost:7860/reset \
-H "Content-Type: application/json" \
-d '{"task_id": "easy_001", "difficulty": "easy"}'
```
---
## Running the Baseline
```bash
export API_BASE_URL="https://router.huggingface.co/v1"
export MODEL_NAME="Qwen/Qwen2.5-72B-Instruct"
export HF_TOKEN="your-token-here"
export ENV_SERVER_URL="http://localhost:7860"
python inference.py
```
Expected baseline scores (approximate):
| Difficulty | Score range |
| ---------- | ----------- |
| Easy | 0.55 – 0.80 |
| Medium | 0.30 – 0.55 |
| Hard | 0.10 – 0.30 |
---
## Project Structure
```
sql-repair-env/
β”œβ”€β”€ app/
β”‚ β”œβ”€β”€ models.py # All Pydantic types
β”‚ β”œβ”€β”€ env.py # Core environment logic
β”‚ β”œβ”€β”€ executor.py # SQLite execution layer
β”‚ β”œβ”€β”€ grader.py # Deterministic scoring
β”‚ └── data_loader.py # Dataset loader (module singleton)
β”œβ”€β”€ data/
β”‚ β”œβ”€β”€ easy.json # 15 easy tasks
β”‚ β”œβ”€β”€ medium.json # 15 medium tasks
β”‚ └── hard.json # 10 hard tasks
β”œβ”€β”€ db/
β”‚ └── fixtures.sql # SQLite seed data
β”œβ”€β”€ server/
β”‚ └── app.py # FastAPI server
β”œβ”€β”€ inference.py # Baseline inference script
β”œβ”€β”€ openenv.yaml # OpenEnv spec
β”œβ”€β”€ Dockerfile
β”œβ”€β”€ requirements.txt
└── README.md
```
---
## Design Decisions
**Why SQLite in-process?**
Zero external infrastructure β€” the DB seeds from `fixtures.sql` on every
`reset()`, guaranteeing a clean deterministic state per episode.
**Why Jaccard similarity?**
Row order is undefined in SQL; Jaccard treats results as sets, making grading
robust to `ORDER BY` differences between the agent's query and the reference.
**Why deny actual row data to the agent?**
Showing only error messages forces the agent to reason about the query
structure, not memorise the dataset. This better simulates real-world
debugging where production data isn't available.
**Why three action types?**
`run_partial` + `ask_clarification` before `submit_query` models real
developer behaviour: check syntax, check schema, then commit. The turn
penalty in the reward function ensures the agent learns to use these
efficiently rather than exhausting the budget.