File size: 1,906 Bytes
f1cd642
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""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)