"""Layer 6b: execute SQL against DuckDB - read-only, SELECT-only, row-capped.""" from __future__ import annotations import re import threading import time from dataclasses import dataclass import duckdb import pandas as pd from data.generate import ensure_db MAX_ROWS = 500 _WRITE = re.compile(r"\b(insert|update|delete|drop|create|alter|attach|copy|pragma|install|load|export)\b", re.I) class UnsafeSQL(ValueError): pass @dataclass class Execution: sql: str df: pd.DataFrame row_count: int truncated: bool ms: float class Executor: def __init__(self): self.con = duckdb.connect(str(ensure_db()), read_only=True) self._lock = threading.Lock() def query(self, sql: str) -> pd.DataFrame: with self._lock: return self.con.execute(sql).df() def columns(self, relation: str) -> list[str]: return list(self.query(f"SELECT * FROM {relation} LIMIT 0").columns) def run(self, sql: str) -> Execution: stripped = sql.strip().rstrip(";") if not re.match(r"^\s*(select|with)\b", stripped, re.I) or _WRITE.search(stripped) or ";" in stripped: raise UnsafeSQL("Only a single read-only SELECT statement is allowed.") t0 = time.perf_counter() df = self.query(f"SELECT * FROM ({stripped}) AS q LIMIT {MAX_ROWS + 1}") ms = (time.perf_counter() - t0) * 1000 truncated = len(df) > MAX_ROWS return Execution(sql=stripped, df=df.head(MAX_ROWS), row_count=min(len(df), MAX_ROWS), truncated=truncated, ms=ms) def schema_ddl(self, tables: list[str]) -> str: out = [] for t in tables: cols = self.query(f"DESCRIBE {t}") out.append(f"TABLE {t} (\n" + ",\n".join(f" {r.column_name} {r.column_type}" for r in cols.itertuples()) + "\n)") return "\n".join(out)