Spaces:
Sleeping
Sleeping
| # Adaptive SQL Trainer — Domain Randomized with OpenAI (Gradio + SQLite) | |
| # - Randomizes a relational domain via OpenAI (bookstore, retail sales, wholesaler, | |
| # sales tax, oil & gas wells, marketing) OR falls back to a built-in dataset. | |
| # - Builds 3–4 related tables (schema + seed rows) in SQLite. | |
| # - Generates 8–12 randomized SQL questions with varied phrasings. | |
| # - Validates answers by executing canonical SQL and comparing result sets. | |
| # - Provides tailored feedback (SQLite dialect, cartesian products, aggregates, aliases). | |
| # - Shows data results at the bottom pane for every run (SELECT or preview for VIEW/CTAS). | |
| # | |
| # Hugging Face Spaces: set OPENAI_API_KEY as a secret to enable LLM randomization. | |
| import os | |
| import re | |
| import json | |
| import time | |
| import random | |
| import sqlite3 | |
| from dataclasses import dataclass, asdict | |
| from datetime import datetime, timezone | |
| from typing import List, Dict, Any, Tuple, Optional | |
| import gradio as gr | |
| import pandas as pd | |
| import numpy as np | |
| # Matplotlib for ERD drawing (headless) | |
| import matplotlib | |
| matplotlib.use("Agg") | |
| import matplotlib.pyplot as plt | |
| from io import BytesIO | |
| from PIL import Image | |
| # -------------------- OpenAI (optional) -------------------- | |
| USE_RESPONSES_API = True | |
| OPENAI_AVAILABLE = True | |
| MODEL_ID = os.getenv("OPENAI_MODEL", "gpt-4.1-mini") | |
| try: | |
| from openai import OpenAI | |
| _client = OpenAI() # requires OPENAI_API_KEY | |
| except Exception: | |
| OPENAI_AVAILABLE = False | |
| _client = None | |
| # -------------------- Global settings -------------------- | |
| DB_DIR = "/data" if os.path.exists("/data") else "." | |
| DB_PATH = os.path.join(DB_DIR, "sql_trainer_dynamic.db") | |
| EXPORT_DIR = "." | |
| ADMIN_KEY = os.getenv("ADMIN_KEY", "demo") | |
| RANDOM_SEED = int(os.getenv("RANDOM_SEED", "7")) | |
| random.seed(RANDOM_SEED) | |
| SYS_RAND = random.SystemRandom() | |
| PLOT_FIGSIZE = (6.8, 3.4) | |
| PLOT_DPI = 110 | |
| PLOT_HEIGHT = 300 | |
| # -------------------- ERD helpers -------------------- | |
| def _to_pil(fig) -> Image.Image: | |
| buf = BytesIO() | |
| fig.tight_layout() | |
| fig.savefig(buf, format="png", dpi=PLOT_DPI, bbox_inches="tight") | |
| plt.close(fig) | |
| buf.seek(0) | |
| return Image.open(buf) | |
| def draw_dynamic_erd(schema: Dict[str, Any]) -> Image.Image: | |
| """ | |
| schema = { | |
| "domain": "bookstore", | |
| "tables": [ | |
| {"name":"authors","columns":[{"name":"author_id","type":"INTEGER",...}, ...], | |
| "pk":["author_id"], "fks":[{"columns":["author_id"],"ref_table":"...","ref_columns":["..."]}], | |
| "rows":[{...}, {...}]} | |
| ] | |
| } | |
| """ | |
| fig, ax = plt.subplots(figsize=PLOT_FIGSIZE) | |
| ax.axis("off") | |
| tables = schema.get("tables", []) | |
| n = max(1, len(tables)) | |
| # Lay out boxes horizontally | |
| margin = 0.03 | |
| width = (1 - margin*(n+1)) / n | |
| height = 0.65 | |
| y = 0.25 | |
| boxes = {} | |
| for i, t in enumerate(tables): | |
| x = margin + i*(width + margin) | |
| boxes[t["name"]] = (x, y, width, height) | |
| ax.add_patch(plt.Rectangle((x, y), width, height, fill=False)) | |
| ax.text(x + 0.01, y + height - 0.05, f"**{t['name']}**", fontsize=10, ha="left", va="top") | |
| yy = y + height - 0.10 | |
| pk = set(t.get("pk", [])) | |
| cols = t.get("columns", []) | |
| for col in cols: | |
| nm = col["name"] | |
| mark = " (PK)" if nm in pk else "" | |
| ax.text(x + 0.02, yy, f"{nm}{mark}", fontsize=9, ha="left", va="top") | |
| yy -= 0.06 | |
| # Draw FK arrows | |
| for t in tables: | |
| for fk in t.get("fks", []): | |
| src_tbl = t["name"] | |
| dst_tbl = fk.get("ref_table") | |
| if src_tbl in boxes and dst_tbl in boxes: | |
| (x1, y1, w1, h1) = boxes[src_tbl] | |
| (x2, y2, w2, h2) = boxes[dst_tbl] | |
| ax.annotate("", xy=(x2 + w2/2, y2 + h2), xytext=(x1 + w1/2, y1), | |
| arrowprops=dict(arrowstyle="->", lw=1.1)) | |
| ax.text(0.5, 0.06, f"Domain: {schema.get('domain','unknown')}", fontsize=9, ha="center") | |
| return _to_pil(fig) | |
| # -------------------- SQLite helpers -------------------- | |
| def connect_db(): | |
| con = sqlite3.connect(DB_PATH) | |
| con.execute("PRAGMA foreign_keys = ON;") | |
| return con | |
| CONN = connect_db() | |
| def init_progress_tables(con: sqlite3.Connection): | |
| cur = con.cursor() | |
| cur.execute(""" | |
| CREATE TABLE IF NOT EXISTS users ( | |
| user_id TEXT PRIMARY KEY, | |
| name TEXT, | |
| created_at TEXT | |
| ) | |
| """) | |
| cur.execute(""" | |
| CREATE TABLE IF NOT EXISTS attempts ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| user_id TEXT, | |
| question_id TEXT, | |
| category TEXT, | |
| correct INTEGER, | |
| sql_text TEXT, | |
| timestamp TEXT, | |
| time_taken REAL, | |
| difficulty INTEGER, | |
| source TEXT, | |
| notes TEXT | |
| ) | |
| """) | |
| cur.execute(""" | |
| CREATE TABLE IF NOT EXISTS session_meta ( | |
| id INTEGER PRIMARY KEY CHECK (id=1), | |
| domain TEXT, | |
| schema_json TEXT | |
| ) | |
| """) | |
| con.commit() | |
| init_progress_tables(CONN) | |
| # -------------------- Fallback dataset (if no OpenAI) -------------------- | |
| FALLBACK_SCHEMA = { | |
| "domain": "bookstore", | |
| "tables": [ | |
| { | |
| "name": "authors", | |
| "pk": ["author_id"], | |
| "columns": [ | |
| {"name":"author_id","type":"INTEGER"}, | |
| {"name":"name","type":"TEXT"}, | |
| {"name":"country","type":"TEXT"}, | |
| {"name":"birth_year","type":"INTEGER"}, | |
| ], | |
| "fks": [], | |
| "rows": [ | |
| {"author_id":1,"name":"Isaac Asimov","country":"USA","birth_year":1920}, | |
| {"author_id":2,"name":"Ursula K. Le Guin","country":"USA","birth_year":1929}, | |
| {"author_id":3,"name":"Haruki Murakami","country":"Japan","birth_year":1949}, | |
| {"author_id":4,"name":"Chinua Achebe","country":"Nigeria","birth_year":1930}, | |
| {"author_id":5,"name":"Jane Austen","country":"UK","birth_year":1775}, | |
| {"author_id":6,"name":"J.K. Rowling","country":"UK","birth_year":1965}, | |
| {"author_id":7,"name":"Yuval Noah Harari","country":"Israel","birth_year":1976}, | |
| {"author_id":8,"name":"New Author","country":"Nowhere","birth_year":1990}, | |
| ], | |
| }, | |
| { | |
| "name": "bookstores", | |
| "pk": ["store_id"], | |
| "columns": [ | |
| {"name":"store_id","type":"INTEGER"}, | |
| {"name":"name","type":"TEXT"}, | |
| {"name":"city","type":"TEXT"}, | |
| {"name":"state","type":"TEXT"}, | |
| ], | |
| "fks": [], | |
| "rows": [ | |
| {"store_id":1,"name":"Downtown Books","city":"Oklahoma City","state":"OK"}, | |
| {"store_id":2,"name":"Harbor Books","city":"Seattle","state":"WA"}, | |
| {"store_id":3,"name":"Desert Pages","city":"Phoenix","state":"AZ"}, | |
| ], | |
| }, | |
| { | |
| "name": "books", | |
| "pk": ["book_id"], | |
| "columns": [ | |
| {"name":"book_id","type":"INTEGER"}, | |
| {"name":"title","type":"TEXT"}, | |
| {"name":"author_id","type":"INTEGER"}, | |
| {"name":"store_id","type":"INTEGER"}, | |
| {"name":"category","type":"TEXT"}, | |
| {"name":"price","type":"REAL"}, | |
| {"name":"published_year","type":"INTEGER"}, | |
| ], | |
| "fks": [ | |
| {"columns":["author_id"],"ref_table":"authors","ref_columns":["author_id"]}, | |
| {"columns":["store_id"],"ref_table":"bookstores","ref_columns":["store_id"]}, | |
| ], | |
| "rows": [ | |
| {"book_id":101,"title":"Foundation","author_id":1,"store_id":1,"category":"Sci-Fi","price":14.99,"published_year":1951}, | |
| {"book_id":102,"title":"I, Robot","author_id":1,"store_id":1,"category":"Sci-Fi","price":12.50,"published_year":1950}, | |
| {"book_id":103,"title":"The Left Hand of Darkness","author_id":2,"store_id":2,"category":"Sci-Fi","price":16.00,"published_year":1969}, | |
| {"book_id":104,"title":"A Wizard of Earthsea","author_id":2,"store_id":2,"category":"Fantasy","price":11.50,"published_year":1968}, | |
| {"book_id":105,"title":"Norwegian Wood","author_id":3,"store_id":3,"category":"Fiction","price":18.00,"published_year":1987}, | |
| {"book_id":106,"title":"Kafka on the Shore","author_id":3,"store_id":1,"category":"Fiction","price":21.00,"published_year":2002}, | |
| {"book_id":107,"title":"Things Fall Apart","author_id":4,"store_id":1,"category":"Fiction","price":10.00,"published_year":1958}, | |
| {"book_id":108,"title":"Pride and Prejudice","author_id":5,"store_id":2,"category":"Fiction","price":9.00,"published_year":1813}, | |
| {"book_id":109,"title":"Harry Potter and the Sorcerer's Stone","author_id":6,"store_id":3,"category":"Children","price":22.00,"published_year":1997}, | |
| {"book_id":110,"title":"Harry Potter and the Chamber of Secrets","author_id":6,"store_id":3,"category":"Children","price":23.00,"published_year":1998}, | |
| {"book_id":111,"title":"Sapiens","author_id":7,"store_id":1,"category":"History","price":26.00,"published_year":2011}, | |
| {"book_id":112,"title":"Homo Deus","author_id":7,"store_id":2,"category":"History","price":28.00,"published_year":2015}, | |
| ], | |
| }, | |
| ] | |
| } | |
| FALLBACK_QUESTIONS = [ | |
| { | |
| "id":"Q01","category":"SELECT *","difficulty":1, | |
| "prompt_md":"Select all rows and columns from `authors`.", | |
| "answer_sql":["SELECT * FROM authors;"], | |
| "requires_aliases":False,"required_aliases":[] | |
| }, | |
| { | |
| "id":"Q02","category":"SELECT columns","difficulty":1, | |
| "prompt_md":"Show `title` and `price` from `books`.", | |
| "answer_sql":["SELECT title, price FROM books;"], | |
| "requires_aliases":False,"required_aliases":[] | |
| }, | |
| { | |
| "id":"Q03","category":"WHERE","difficulty":1, | |
| "prompt_md":"List Sci‑Fi books under $15 (show title, price).", | |
| "answer_sql":["SELECT title, price FROM books WHERE category='Sci-Fi' AND price < 15;"], | |
| "requires_aliases":False,"required_aliases":[] | |
| }, | |
| { | |
| "id":"Q04","category":"Aliases","difficulty":1, | |
| "prompt_md":"Using aliases `b` and `a`, join `books` to `authors` and show `b.title` and `a.name` as `author_name`.", | |
| "answer_sql":["SELECT b.title, a.name AS author_name FROM books b JOIN authors a ON b.author_id=a.author_id;"], | |
| "requires_aliases":True,"required_aliases":["a","b"] | |
| }, | |
| { | |
| "id":"Q05","category":"JOIN (INNER)","difficulty":2, | |
| "prompt_md":"Inner join `books` and `bookstores`. Return `title`, `name` as `store`.", | |
| "answer_sql":[ | |
| "SELECT b.title, s.name AS store FROM books b INNER JOIN bookstores s ON b.store_id=s.store_id;" | |
| ], | |
| "requires_aliases":False,"required_aliases":[] | |
| }, | |
| { | |
| "id":"Q06","category":"JOIN (LEFT)","difficulty":2, | |
| "prompt_md":"List each author and their number of books (include authors with zero): columns `name`, `book_count`.", | |
| "answer_sql":[ | |
| "SELECT a.name, COUNT(b.book_id) AS book_count FROM authors a LEFT JOIN books b ON a.author_id=b.author_id GROUP BY a.name;" | |
| ], | |
| "requires_aliases":False,"required_aliases":[] | |
| }, | |
| { | |
| "id":"Q07","category":"VIEW","difficulty":2, | |
| "prompt_md":"Create a view `vw_pricy` with `title`, `price` for books priced > 25.", | |
| "answer_sql":[ | |
| "CREATE VIEW vw_pricy AS SELECT title, price FROM books WHERE price > 25;" | |
| ], | |
| "requires_aliases":False,"required_aliases":[] | |
| }, | |
| { | |
| "id":"Q08","category":"CTAS / SELECT INTO","difficulty":2, | |
| "prompt_md":"Create a table `cheap_books` containing books priced < 12. Use CTAS or SELECT INTO.", | |
| "answer_sql":[ | |
| "CREATE TABLE cheap_books AS SELECT * FROM books WHERE price < 12;", | |
| "SELECT * INTO cheap_books FROM books WHERE price < 12;" | |
| ], | |
| "requires_aliases":False,"required_aliases":[] | |
| }, | |
| ] | |
| # -------------------- OpenAI prompts -------------------- | |
| DOMAIN_AND_QUESTIONS_SCHEMA = { | |
| "name": "DomainSQLPack", | |
| "schema": { | |
| "type": "object", | |
| "additionalProperties": False, | |
| "properties": { | |
| "domain": {"type":"string"}, | |
| "tables": { | |
| "type":"array", | |
| "items": { | |
| "type":"object", | |
| "additionalProperties": False, | |
| "properties": { | |
| "name": {"type":"string"}, | |
| "pk": {"type":"array","items":{"type":"string"}}, | |
| "columns": { | |
| "type":"array", | |
| "items": { | |
| "type":"object", | |
| "additionalProperties": False, | |
| "properties": { | |
| "name":{"type":"string"}, | |
| "type":{"type":"string"} | |
| }, | |
| "required":["name","type"] | |
| } | |
| }, | |
| "fks": { | |
| "type":"array", | |
| "items": { | |
| "type":"object", | |
| "additionalProperties": False, | |
| "properties": { | |
| "columns":{"type":"array","items":{"type":"string"}}, | |
| "ref_table":{"type":"string"}, | |
| "ref_columns":{"type":"array","items":{"type":"string"}} | |
| }, | |
| "required":["columns","ref_table","ref_columns"] | |
| } | |
| }, | |
| "rows": {"type":"array","items":{"type":["object","array"]}} | |
| }, | |
| "required":["name","pk","columns","fks","rows"] | |
| }, | |
| "minItems":3,"maxItems":4 | |
| }, | |
| "questions": { | |
| "type":"array", | |
| "items": { | |
| "type":"object", | |
| "additionalProperties": False, | |
| "properties": { | |
| "id":{"type":"string"}, | |
| "category":{"type":"string"}, | |
| "difficulty":{"type":"integer"}, | |
| "prompt_md":{"type":"string"}, | |
| "answer_sql":{"type":"array","items":{"type":"string"}}, | |
| "requires_aliases":{"type":"boolean"}, | |
| "required_aliases":{"type":"array","items":{"type":"string"}} | |
| }, | |
| "required":["id","category","difficulty","prompt_md","answer_sql"] | |
| }, | |
| "minItems":8,"maxItems":12 | |
| } | |
| }, | |
| "required":["domain","tables","questions"] | |
| }, | |
| "strict": True | |
| } | |
| DOMAIN_AND_QUESTIONS_PROMPT = """ | |
| You are designing a small relational dataset and training questions for SQL basics. | |
| 1) Choose ONE domain at random from: | |
| - bookstore, retail sales, wholesaler, sales tax, oil and gas wells, marketing. | |
| 2) Produce exactly 3–4 tables that fit together (SQLite-friendly): | |
| - Use snake_case, avoid reserved words. | |
| - Types: INTEGER, REAL, TEXT, NUMERIC, DATE (but no advanced features). | |
| - Primary keys (pk) and foreign keys (fks) must align. | |
| - Provide 8–15 small, realistic seed rows per table (not huge). | |
| 3) Generate 8–12 SQL questions covering basics with varied, natural language: | |
| - Categories from: "SELECT *", "SELECT columns", "WHERE", "Aliases", | |
| "JOIN (INNER)", "JOIN (LEFT)", "Aggregation", "VIEW", "CTAS / SELECT INTO". | |
| - Include a few joins and at least one LEFT JOIN. | |
| - Include one view creation. | |
| - Include one table creation from SELECT (either CTAS or SELECT INTO). | |
| - Prefer SQLite-compatible SQL. DO NOT use RIGHT/FULL OUTER JOIN. | |
| - Offer 1–3 acceptable answer_sql variants per question. | |
| - For 1–2 questions, require table aliases (set requires_aliases=true and list required_aliases). | |
| Return JSON only. | |
| """ | |
| def llm_generate_domain_and_questions() -> Optional[Dict[str,Any]]: | |
| if not OPENAI_AVAILABLE: | |
| return None | |
| try: | |
| if USE_RESPONSES_API: | |
| resp = _client.responses.create( | |
| model=MODEL_ID, | |
| response_format={"type":"json_schema","json_schema":DOMAIN_AND_QUESTIONS_SCHEMA}, | |
| input=[{"role":"user","content": DOMAIN_AND_QUESTIONS_PROMPT}], | |
| temperature=0.6, | |
| ) | |
| data_text = getattr(resp, "output_text", None) | |
| else: | |
| chat = _client.chat.completions.create( | |
| model=MODEL_ID, | |
| messages=[{"role":"user","content": DOMAIN_AND_QUESTIONS_PROMPT}], | |
| temperature=0.6 | |
| ) | |
| data_text = chat.choices[0].message.content | |
| obj = json.loads(data_text) if data_text else None | |
| return obj | |
| except Exception: | |
| return None | |
| # -------------------- Schema install & question handling -------------------- | |
| def drop_existing_domain_tables(con: sqlite3.Connection, keep_internal=True): | |
| cur = con.cursor() | |
| cur.execute("SELECT name, type FROM sqlite_master WHERE type IN ('table','view')") | |
| items = cur.fetchall() | |
| for name, typ in items: | |
| if keep_internal and name in ("users","attempts","session_meta"): | |
| continue | |
| try: | |
| cur.execute(f"DROP {typ.upper()} IF EXISTS {name}") | |
| except Exception: | |
| pass | |
| con.commit() | |
| def install_schema(con: sqlite3.Connection, schema: Dict[str,Any]): | |
| drop_existing_domain_tables(con, keep_internal=True) | |
| cur = con.cursor() | |
| # Create tables first | |
| for t in schema.get("tables", []): | |
| cols_sql = [] | |
| pk = t.get("pk", []) | |
| for c in t.get("columns", []): | |
| cname = c["name"] | |
| ctype = c.get("type","TEXT") | |
| cols_sql.append(f"{cname} {ctype}") | |
| if pk: | |
| cols_sql.append(f"PRIMARY KEY ({', '.join(pk)})") | |
| create_sql = f"CREATE TABLE {t['name']} ({', '.join(cols_sql)})" | |
| cur.execute(create_sql) | |
| # Insert rows | |
| for t in schema.get("tables", []): | |
| if not t.get("rows"): | |
| continue | |
| cols = [c["name"] for c in t.get("columns", [])] | |
| qmarks = ",".join(["?"]*len(cols)) | |
| insert_sql = f"INSERT INTO {t['name']} ({', '.join(cols)}) VALUES ({qmarks})" | |
| # rows can be objects or arrays | |
| for r in t["rows"]: | |
| if isinstance(r, dict): | |
| vals = [r.get(col, None) for col in cols] | |
| elif isinstance(r, list) or isinstance(r, tuple): | |
| vals = list(r) + [None]*(len(cols)-len(r)) | |
| vals = vals[:len(cols)] | |
| else: | |
| continue | |
| cur.execute(insert_sql, vals) | |
| con.commit() | |
| # Persist schema JSON | |
| cur.execute("INSERT OR REPLACE INTO session_meta(id, domain, schema_json) VALUES (1, ?, ?)", | |
| (schema.get("domain","unknown"), json.dumps(schema))) | |
| con.commit() | |
| def run_df(con: sqlite3.Connection, sql: str) -> pd.DataFrame: | |
| return pd.read_sql_query(sql, con) | |
| def rewrite_select_into(sql: str) -> Tuple[str, Optional[str]]: | |
| s = sql.strip().strip(";") | |
| if re.search(r"\bselect\b.+\binto\b.+\bfrom\b", s, flags=re.IGNORECASE|re.DOTALL): | |
| m = re.match(r"(?is)^\s*select\s+(.*?)\s+into\s+([A-Za-z_][A-Za-z0-9_]*)\s+from\s+(.*)$", s) | |
| if m: | |
| cols, tbl, rest = m.groups() | |
| return f"CREATE TABLE {tbl} AS SELECT {cols} FROM {rest}", tbl | |
| return sql, None | |
| def detect_unsupported_joins(sql: str) -> Optional[str]: | |
| low = sql.lower() | |
| if " right join " in low: | |
| return "SQLite does not support RIGHT JOIN. Use LEFT JOIN in the opposite direction." | |
| if " full join " in low or " full outer join " in low: | |
| return "SQLite does not support FULL OUTER JOIN. Use LEFT JOIN plus UNION for the other side." | |
| if " ilike " in low: | |
| return "SQLite has no ILIKE. Use `LOWER(col) LIKE LOWER('%pattern%')`." | |
| return None | |
| def detect_cartesian(con: sqlite3.Connection, sql: str, df_result: pd.DataFrame) -> Optional[str]: | |
| low = sql.lower() | |
| if " cross join " in low: | |
| return "Query uses CROSS JOIN (cartesian product). Ensure this is intended." | |
| comma_from = re.search(r"\bfrom\b\s+([a-z_]\w*)\s*,\s*([a-z_]\w*)", low) | |
| missing_on = (" join " in low) and (" on " not in low) and (" using " not in low) and (" natural " not in low) | |
| if comma_from or missing_on: | |
| try: | |
| cur = con.cursor() | |
| if comma_from: | |
| t1, t2 = comma_from.groups() | |
| else: | |
| m = re.search(r"\bfrom\b\s+([a-z_]\w*)", low) | |
| j = re.search(r"\bjoin\b\s+([a-z_]\w*)", low) | |
| if not m or not j: | |
| return "Possible cartesian product: no join condition detected." | |
| t1, t2 = m.group(1), j.group(1) | |
| cur.execute(f"SELECT COUNT(*) FROM {t1}") | |
| n1 = cur.fetchone()[0] | |
| cur.execute(f"SELECT COUNT(*) FROM {t2}") | |
| n2 = cur.fetchone()[0] | |
| prod = n1 * n2 | |
| if len(df_result) == prod and prod > 0: | |
| return f"Result row count equals {n1}×{n2}={prod}. Likely cartesian product (missing join)." | |
| except Exception: | |
| return "Possible cartesian product: no join condition detected." | |
| return None | |
| def results_equal(df_a: pd.DataFrame, df_b: pd.DataFrame) -> bool: | |
| if df_a.shape != df_b.shape: | |
| return False | |
| a = df_a.copy() | |
| b = df_b.copy() | |
| a.columns = [c.lower() for c in a.columns] | |
| b.columns = [c.lower() for c in b.columns] | |
| a = a.sort_values(list(a.columns)).reset_index(drop=True) | |
| b = b.sort_values(list(b.columns)).reset_index(drop=True) | |
| return a.equals(b) | |
| def aliases_present(sql: str, required_aliases: List[str]) -> bool: | |
| low = re.sub(r"\s+", " ", sql.lower()) | |
| for al in required_aliases: | |
| if f" {al}." not in low and f" as {al} " not in low: | |
| return False | |
| return True | |
| # -------------------- Question model -------------------- | |
| class SQLQuestion: | |
| id: str | |
| category: str | |
| difficulty: int | |
| prompt_md: str | |
| answer_sql: List[str] | |
| requires_aliases: bool = False | |
| required_aliases: List[str] = None | |
| def to_question_dict(q) -> Dict[str,Any]: | |
| d = dict(q) | |
| d.setdefault("requires_aliases", False) | |
| d.setdefault("required_aliases", []) | |
| return d | |
| def load_questions(obj_list: List[Dict[str,Any]]) -> List[Dict[str,Any]]: | |
| out = [] | |
| for o in obj_list: | |
| out.append(to_question_dict(o)) | |
| return out | |
| # -------------------- Domain bootstrap -------------------- | |
| def bootstrap_domain_with_llm_or_fallback() -> Tuple[Dict[str,Any], List[Dict[str,Any]]]: | |
| obj = llm_generate_domain_and_questions() | |
| if obj is None: | |
| return FALLBACK_SCHEMA, FALLBACK_QUESTIONS | |
| # Guardrails: strip RIGHT/FULL joins from answers | |
| clean_qs = [] | |
| for q in obj.get("questions", []): | |
| answers = [a for a in q.get("answer_sql", []) if " right join " not in a.lower() and " full " not in a.lower()] | |
| if not answers: | |
| continue | |
| q["answer_sql"] = answers | |
| q.setdefault("requires_aliases", False) | |
| q.setdefault("required_aliases", []) | |
| clean_qs.append(q) | |
| obj["questions"] = clean_qs | |
| return obj, clean_qs | |
| def install_new_domain(): | |
| schema, questions = bootstrap_domain_with_llm_or_fallback() | |
| install_schema(CONN, schema) | |
| return schema, questions | |
| # -------------------- Session state -------------------- | |
| CURRENT_SCHEMA, CURRENT_QS = install_new_domain() | |
| # -------------------- Progress + mastery -------------------- | |
| def upsert_user(con: sqlite3.Connection, user_id: str, name: str): | |
| cur = con.cursor() | |
| cur.execute("SELECT user_id FROM users WHERE user_id = ?", (user_id,)) | |
| if cur.fetchone() is None: | |
| cur.execute("INSERT INTO users (user_id, name, created_at) VALUES (?, ?, ?)", | |
| (user_id, name, datetime.now(timezone.utc).isoformat())) | |
| else: | |
| cur.execute("UPDATE users SET name=? WHERE user_id=?", (name, user_id)) | |
| con.commit() | |
| CATEGORIES_ORDER = [ | |
| "SELECT *", "SELECT columns", "WHERE", "Aliases", | |
| "JOIN (INNER)", "JOIN (LEFT)", "Aggregation", "VIEW", "CTAS / SELECT INTO" | |
| ] | |
| def topic_stats(df_attempts: pd.DataFrame) -> pd.DataFrame: | |
| rows = [] | |
| for cat in CATEGORIES_ORDER: | |
| sub = df_attempts[df_attempts["category"] == cat] if not df_attempts.empty else pd.DataFrame() | |
| att = int(sub.shape[0]) if not sub.empty else 0 | |
| cor = int(sub["correct"].sum()) if not sub.empty else 0 | |
| acc = float(cor / max(att, 1)) | |
| rows.append({"category":cat,"attempts":att,"correct":cor,"accuracy":acc}) | |
| return pd.DataFrame(rows) | |
| def fetch_attempts(con: sqlite3.Connection, user_id: str) -> pd.DataFrame: | |
| return pd.read_sql_query("SELECT * FROM attempts WHERE user_id=? ORDER BY id DESC", con, params=(user_id,)) | |
| def pick_next_question(user_id: str) -> Dict[str,Any]: | |
| df = fetch_attempts(CONN, user_id) | |
| stats = topic_stats(df) | |
| stats = stats.sort_values(by=["accuracy","attempts"], ascending=[True, True]) | |
| weakest = stats.iloc[0]["category"] if not stats.empty else CATEGORIES_ORDER[0] | |
| cands = [q for q in CURRENT_QS if q["category"] == weakest] or CURRENT_QS | |
| return dict(random.choice(cands)) | |
| # -------------------- Execution & feedback -------------------- | |
| def exec_student_sql(sql_text: str) -> Tuple[Optional[pd.DataFrame], Optional[str], Optional[str], Optional[str]]: | |
| if not sql_text or not sql_text.strip(): | |
| return None, "Enter a SQL statement.", None, None | |
| sql_raw = sql_text.strip().rstrip(";") | |
| sql_rew, created_tbl = rewrite_select_into(sql_raw) | |
| note = None | |
| if sql_rew != sql_raw: | |
| note = "Rewrote `SELECT ... INTO` to `CREATE TABLE ... AS SELECT ...` for SQLite." | |
| unsup = detect_unsupported_joins(sql_rew) | |
| if unsup: | |
| return None, unsup, None, note | |
| try: | |
| low = sql_rew.lower() | |
| if low.startswith("select"): | |
| df = run_df(CONN, sql_rew) | |
| warn = detect_cartesian(CONN, sql_rew, df) | |
| return df, None, warn, note | |
| else: | |
| cur = CONN.cursor() | |
| cur.execute(sql_rew) | |
| CONN.commit() | |
| # Preview newly created objects | |
| if low.startswith("create view"): | |
| m = re.match(r"(?is)^\s*create\s+view\s+(if\s+not\s+exists\s+)?([a-z_]\w*)\s+as\s+(select.*)$", low) | |
| name = m.group(2) if m else None | |
| if name: | |
| try: | |
| df = run_df(CONN, f"SELECT * FROM {name}") | |
| return df, None, None, note | |
| except Exception: | |
| return None, "View created but could not be queried.", None, note | |
| if low.startswith("create table"): | |
| tbl = created_tbl | |
| if not tbl: | |
| m = re.match(r"(?is)^\s*create\s+table\s+(if\s+not\s+exists\s+)?([a-z_]\w*)\s+as\s+select.*$", low) | |
| tbl = m.group(2) if m else None | |
| if tbl: | |
| try: | |
| df = run_df(CONN, f"SELECT * FROM {tbl}") | |
| return df, None, None, note | |
| except Exception: | |
| return None, "Table created but could not be queried.", None, note | |
| return pd.DataFrame(), None, None, note | |
| except Exception as e: | |
| # Tailored messages | |
| msg = str(e) | |
| if "no such table" in msg.lower(): | |
| return None, f"{msg}. Check table names for this randomized domain.", None, note | |
| if "no such column" in msg.lower(): | |
| return None, f"{msg}. Use correct column names or prefixes (alias.column).", None, note | |
| if "ambiguous column name" in msg.lower(): | |
| return None, f"{msg}. Qualify the column with a table alias.", None, note | |
| if "misuse of aggregate" in msg.lower() or "aggregate functions are not allowed in" in msg.lower(): | |
| return None, f"{msg}. You might need a GROUP BY for non-aggregated columns.", None, note | |
| if "near \"into\"" in msg.lower() and "syntax error" in msg.lower(): | |
| return None, "SQLite doesn’t support `SELECT ... INTO`. I can rewrite it automatically—try again.", None, note | |
| if "syntax error" in msg.lower(): | |
| return None, f"Syntax error. Check commas, keywords, and parentheses. Raw error: {msg}", None, note | |
| return None, f"SQL error: {msg}", None, note | |
| def answer_df(answer_sql: List[str]) -> Optional[pd.DataFrame]: | |
| for sql in answer_sql: | |
| try: | |
| low = sql.strip().lower() | |
| if low.startswith("select"): | |
| return run_df(CONN, sql) | |
| if low.startswith("create view"): | |
| # temp preview | |
| m = re.match(r"(?is)^\s*create\s+view\s+(if\s+not\s+exists\s+)?([a-z_]\w*)\s+as\s+select.*$", low) | |
| view_name = m.group(2) if m else "vw_tmp" | |
| cur = CONN.cursor() | |
| cur.execute(f"DROP VIEW IF EXISTS {view_name}") | |
| cur.execute(sql) | |
| CONN.commit() | |
| return run_df(CONN, f"SELECT * FROM {view_name}") | |
| if low.startswith("create table"): | |
| m = re.match(r"(?is)^\s*create\s+table\s+(if\s+not\s+exists\s+)?([a-z_]\w*)\s+as\s+select.*$", low) | |
| tbl = m.group(2) if m else None | |
| cur = CONN.cursor() | |
| if tbl: | |
| cur.execute(f"DROP TABLE IF EXISTS {tbl}") | |
| cur.execute(sql) | |
| CONN.commit() | |
| if tbl: | |
| return run_df(CONN, f"SELECT * FROM {tbl}") | |
| except Exception: | |
| continue | |
| return None | |
| def validate_answer(q: Dict[str,Any], student_sql: str, df_student: Optional[pd.DataFrame]) -> Tuple[bool, str]: | |
| df_expected = answer_df(q["answer_sql"]) | |
| # If we can't build a canonical DF (e.g., DDL side effect), we accept any successful execution as correct | |
| if df_expected is None: | |
| return (df_student is not None), f"**Explanation:** Your statement executed successfully for this task." | |
| if df_student is None: | |
| return False, f"**Explanation:** Expected data result differs." | |
| return results_equal(df_student, df_expected), f"**Explanation:** Compare your result to a canonical solution." | |
| def log_attempt(user_id: str, qid: str, category: str, correct: bool, sql_text: str, | |
| time_taken: float, difficulty: int, source: str, notes: str): | |
| cur = CONN.cursor() | |
| cur.execute(""" | |
| INSERT INTO attempts (user_id, question_id, category, correct, sql_text, timestamp, time_taken, difficulty, source, notes) | |
| VALUES (?,?,?,?,?,?,?,?,?,?) | |
| """, (user_id, qid, category, int(correct), sql_text, datetime.now(timezone.utc).isoformat(), | |
| time_taken, difficulty, source, notes)) | |
| CONN.commit() | |
| # -------------------- UI callbacks -------------------- | |
| def start_session(name: str, session: dict): | |
| name = (name or "").strip() | |
| if not name: | |
| return (session, | |
| gr.update(value="Please enter your name to begin.", visible=True), | |
| gr.update(visible=False), | |
| gr.update(visible=False), | |
| None, | |
| gr.update(visible=False), | |
| pd.DataFrame(), | |
| pd.DataFrame()) | |
| slug = "-".join(name.lower().split()) | |
| user_id = slug[:64] if slug else f"user-{int(time.time())}" | |
| upsert_user(CONN, user_id, name) | |
| q = pick_next_question(user_id) | |
| session = {"user_id": user_id, "name": name, "qid": q["id"], "start_ts": time.time(), "q": q} | |
| prompt = q["prompt_md"] | |
| stats = topic_stats(fetch_attempts(CONN, user_id)) | |
| erd = draw_dynamic_erd(CURRENT_SCHEMA) | |
| return (session, | |
| gr.update(value=f"**Question {q['id']}**\n\n{prompt}", visible=True), | |
| gr.update(visible=True), # show SQL input | |
| gr.update(value="", visible=True), # preview block | |
| erd, | |
| gr.update(visible=False), # next btn hidden until submit | |
| stats, | |
| pd.DataFrame()) | |
| def render_preview_and_erd(sql_text: str, session: dict): | |
| if not session or "q" not in session: | |
| return gr.update(value="", visible=False), draw_dynamic_erd(CURRENT_SCHEMA) | |
| s = (sql_text or "").strip() | |
| if not s: | |
| return gr.update(value="", visible=False), draw_dynamic_erd(CURRENT_SCHEMA) | |
| return gr.update(value=f"**Preview:**\n\n```sql\n{s}\n```", visible=True), draw_dynamic_erd(CURRENT_SCHEMA) | |
| def submit_answer(sql_text: str, session: dict): | |
| if not session or "user_id" not in session or "q" not in session: | |
| return gr.update(value="Start a session first.", visible=True), pd.DataFrame(), gr.update(visible=False), pd.DataFrame() | |
| user_id = session["user_id"] | |
| q = session["q"] | |
| elapsed = max(0.0, time.time() - session.get("start_ts", time.time())) | |
| df, err, warn, note = exec_student_sql(sql_text) | |
| details = [] | |
| if note: details.append(f"ℹ️ {note}") | |
| if err: | |
| fb = f"❌ **Did not run**\n\n{err}" | |
| if details: fb += "\n\n" + "\n".join(details) | |
| log_attempt(user_id, q["id"], q["category"], False, sql_text, elapsed, int(q["difficulty"]), "bank", " | ".join([err] + details)) | |
| stats = topic_stats(fetch_attempts(CONN, user_id)) | |
| return gr.update(value=fb, visible=True), pd.DataFrame(), gr.update(visible=True), stats | |
| # Validate correctness | |
| alias_msg = None | |
| if q.get("requires_aliases"): | |
| if not aliases_present(sql_text, q.get("required_aliases", [])): | |
| alias_msg = f"⚠️ This task asked for aliases {q.get('required_aliases', [])}. I didn’t detect them." | |
| is_correct, explanation = validate_answer(q, sql_text, df) | |
| if warn: details.append(f"⚠️ {warn}") | |
| if alias_msg: details.append(alias_msg) | |
| prefix = "✅ **Correct!**" if is_correct else "❌ **Not quite.**" | |
| feedback = prefix | |
| if details: | |
| feedback += "\n\n" + "\n".join(details) | |
| feedback += "\n\n" + explanation + "\n\n**One acceptable solution:**\n```sql\n" + q["answer_sql"][0].rstrip(";") + ";\n```" | |
| log_attempt(user_id, q["id"], q["category"], bool(is_correct), sql_text, elapsed, int(q["difficulty"]), "bank", " | ".join(details)) | |
| stats = topic_stats(fetch_attempts(CONN, user_id)) | |
| return gr.update(value=feedback, visible=True), (df if df is not None else pd.DataFrame()), gr.update(visible=True), stats | |
| def next_question(session: dict): | |
| if not session or "user_id" not in session: | |
| return session, gr.update(value="Start a session first.", visible=True), gr.update(visible=False), draw_dynamic_erd(CURRENT_SCHEMA), gr.update(visible=False) | |
| user_id = session["user_id"] | |
| q = pick_next_question(user_id) | |
| session["qid"] = q["id"] | |
| session["q"] = q | |
| session["start_ts"] = time.time() | |
| return session, gr.update(value=f"**Question {q['id']}**\n\n{q['prompt_md']}", visible=True), gr.update(value="", visible=True), draw_dynamic_erd(CURRENT_SCHEMA), gr.update(visible=False) | |
| def show_hint(session: dict): | |
| if not session or "q" not in session: | |
| return gr.update(value="Start a session first.", visible=True) | |
| # Lightweight hint policy: category-specific guidance | |
| cat = session["q"]["category"] | |
| hint = { | |
| "SELECT *": "Use `SELECT * FROM table_name`.", | |
| "SELECT columns": "List columns: `SELECT col1, col2 FROM table_name`.", | |
| "WHERE": "Filter with `WHERE` and combine conditions using AND/OR.", | |
| "Aliases": "Use `table_name t` and qualify: `t.col`.", | |
| "JOIN (INNER)": "Join with `... INNER JOIN ... ON left.key = right.key`.", | |
| "JOIN (LEFT)": "LEFT JOIN keeps all rows from the left table.", | |
| "Aggregation": "Use aggregate functions and `GROUP BY` non-aggregated columns.", | |
| "VIEW": "`CREATE VIEW view_name AS SELECT ...`.", | |
| "CTAS / SELECT INTO": "SQLite uses `CREATE TABLE name AS SELECT ...`." | |
| }.get(cat, "Read the ER diagram and identify keys to join on.") | |
| return gr.update(value=f"**Hint:** {hint}", visible=True) | |
| def export_progress(user_name: str): | |
| slug = "-".join((user_name or "").lower().split()) | |
| if not slug: | |
| return None | |
| user_id = slug[:64] | |
| df = fetch_attempts(CONN, user_id) | |
| os.makedirs(EXPORT_DIR, exist_ok=True) | |
| path = os.path.abspath(os.path.join(EXPORT_DIR, f"{user_id}_progress.csv")) | |
| (pd.DataFrame([{"info":"No attempts yet."}]) if df.empty else df).to_csv(path, index=False) | |
| return path | |
| def regenerate_domain(): | |
| global CURRENT_SCHEMA, CURRENT_QS | |
| CURRENT_SCHEMA, CURRENT_QS = install_new_domain() | |
| erd = draw_dynamic_erd(CURRENT_SCHEMA) | |
| return gr.update(value="✅ Domain regenerated.", visible=True), erd | |
| def preview_table(tbl: str): | |
| try: | |
| return run_df(CONN, f"SELECT * FROM {tbl} LIMIT 20") | |
| except Exception as e: | |
| return pd.DataFrame([{"error": str(e)}]) | |
| def list_tables_for_preview(): | |
| df = run_df(CONN, "SELECT name, type FROM sqlite_master WHERE type in ('table','view') AND name NOT IN ('users','attempts','session_meta') ORDER BY type, name") | |
| if df.empty: | |
| return ["(no tables)"] | |
| return df["name"].tolist() | |
| # -------------------- UI -------------------- | |
| with gr.Blocks(title="Adaptive SQL Trainer — Randomized Domains") as demo: | |
| gr.Markdown( | |
| """ | |
| # 🧪 Adaptive SQL Trainer — Randomized Domains (SQLite) | |
| - Uses **OpenAI** (if configured) to randomize a domain (bookstore, retail sales, wholesaler, | |
| sales tax, oil & gas wells, marketing), generate **3–4 tables** and **8–12** questions. | |
| - Practice `SELECT`, `WHERE`, `JOIN` (INNER/LEFT), **aliases**, **views**, and **CTAS / SELECT INTO**. | |
| - The app explains **SQLite quirks** (no RIGHT/FULL JOIN) and flags likely **cartesian products**. | |
| > Set your `OPENAI_API_KEY` in the Space secrets to enable randomization. | |
| """ | |
| ) | |
| with gr.Row(): | |
| # -------- Left column: controls + quick preview ---------- | |
| with gr.Column(scale=1): | |
| name_box = gr.Textbox(label="Your Name", placeholder="e.g., Jordan Alvarez") | |
| start_btn = gr.Button("Start / Resume Session", variant="primary") | |
| session_state = gr.State({"user_id": None, "name": None, "qid": None, "start_ts": None, "q": None}) | |
| gr.Markdown("---") | |
| gr.Markdown("### Dataset Controls") | |
| regen_btn = gr.Button("🔀 Randomize Dataset (OpenAI)") | |
| regen_fb = gr.Markdown(visible=False) | |
| gr.Markdown("---") | |
| gr.Markdown("### Instructor Tools") | |
| export_name = gr.Textbox(label="Export a student's progress (enter name)") | |
| export_btn = gr.Button("Export CSV") | |
| export_file = gr.File(label="Download progress") | |
| gr.Markdown("---") | |
| gr.Markdown("### Quick Table/View Preview (top 20 rows)") | |
| tbl_dd = gr.Dropdown(choices=list_tables_for_preview(), label="Pick table/view", interactive=True) | |
| tbl_btn = gr.Button("Preview") | |
| preview_df = gr.Dataframe(value=pd.DataFrame(), interactive=False) | |
| # -------- Right column: task + feedback + mastery + results ---------- | |
| with gr.Column(scale=2): | |
| prompt_md = gr.Markdown(visible=False) | |
| sql_input = gr.Textbox(label="Your SQL", placeholder="Type SQL here (end ; optional).", lines=6, visible=False) | |
| preview_md = gr.Markdown(visible=False) | |
| er_image = gr.Image(label="Entity Diagram", value=draw_dynamic_erd(CURRENT_SCHEMA), height=PLOT_HEIGHT) | |
| with gr.Row(): | |
| submit_btn = gr.Button("Run & Submit", variant="primary") | |
| hint_btn = gr.Button("Hint") | |
| next_btn = gr.Button("Next Question ▶", visible=False) | |
| feedback_md = gr.Markdown("") | |
| gr.Markdown("---") | |
| gr.Markdown("### Your Progress by Category") | |
| mastery_df = gr.Dataframe( | |
| headers=["category","attempts","correct","accuracy"], | |
| col_count=(4, "dynamic"), | |
| row_count=(0, "dynamic"), | |
| interactive=False | |
| ) | |
| gr.Markdown("---") | |
| gr.Markdown("### Result Preview") | |
| result_df = gr.Dataframe(value=pd.DataFrame(), interactive=False) | |
| # Wire events | |
| start_btn.click( | |
| start_session, | |
| inputs=[name_box, session_state], | |
| outputs=[session_state, prompt_md, sql_input, preview_md, er_image, next_btn, mastery_df, result_df], | |
| ) | |
| sql_input.change( | |
| render_preview_and_erd, | |
| inputs=[sql_input, session_state], | |
| outputs=[preview_md, er_image], | |
| ) | |
| submit_btn.click( | |
| submit_answer, | |
| inputs=[sql_input, session_state], | |
| outputs=[feedback_md, result_df, next_btn, mastery_df], | |
| ) | |
| next_btn.click( | |
| next_question, | |
| inputs=[session_state], | |
| outputs=[session_state, prompt_md, sql_input, er_image, next_btn], | |
| ) | |
| hint_btn.click( | |
| show_hint, | |
| inputs=[session_state], | |
| outputs=[feedback_md], | |
| ) | |
| export_btn.click( | |
| export_progress, | |
| inputs=[export_name], | |
| outputs=[export_file], | |
| ) | |
| regen_btn.click( | |
| regenerate_domain, | |
| inputs=[], | |
| outputs=[regen_fb, er_image], | |
| ) | |
| tbl_btn.click( | |
| lambda name: preview_table(name), | |
| inputs=[tbl_dd], | |
| outputs=[preview_df] | |
| ) | |
| # Keep dropdown fresh after regeneration | |
| regen_btn.click( | |
| lambda: gr.update(choices=list_tables_for_preview()), | |
| inputs=[], | |
| outputs=[tbl_dd] | |
| ) | |
| if __name__ == "__main__": | |
| demo.launch() | |