Spaces:
Sleeping
Sleeping
File size: 6,805 Bytes
822ca51 07ca292 822ca51 5e2a88b 822ca51 5e2a88b 822ca51 | 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 | """Deterministic terminal grader for DB-ER.
Score (0.0 - 1.0) is computed by comparing the agent's final DB against the
hidden golden DB using:
- ``PRAGMA table_info(<table>)`` -- schema structure (column names / types / nullability)
- ``SELECT * FROM <table> ORDER BY rowid`` -- exact row data
For each core table the F1 score between the golden row-set and the agent
row-set is computed. Tables are weighted equally. A schema mismatch caps
the table score at 0.5.
This function is 100% deterministic (no hashing, no LLM, no randomness).
"""
from __future__ import annotations
import sqlite3
from typing import Any, Dict, List, Tuple
from server.fixtures import CORE_TABLES
# Public API
def compute_score(agent_conn: sqlite3.Connection, task_id: int) -> float:
"""Compare *agent_conn* against the golden DB and return a score in [0, 1].
The golden DB is created fresh (read-only in-memory) each call to ensure
it can never be contaminated by agent actions.
"""
from server.fixtures import create_golden_db # local import to avoid circular deps
golden_conn = create_golden_db(task_id)
try:
table_scores = []
for table in CORE_TABLES[task_id]:
score = _score_table(agent_conn, golden_conn, table)
table_scores.append(score)
raw = round(sum(table_scores) / len(table_scores), 4) if table_scores else 0.0
# Phase 2 validation requires scores strictly in (0, 1) — clamp endpoints.
return max(0.01, min(0.99, raw))
finally:
golden_conn.close()
# Per-table scoring
def _score_table(
agent_conn: sqlite3.Connection,
golden_conn: sqlite3.Connection,
table: str,
) -> float:
"""Return [0, 1] score for a single table."""
# 1. Schema check
schema_match = _schemas_match(agent_conn, golden_conn, table)
# 2. Row data - fetch as frozensets of tuples for set comparison
golden_rows = _fetch_rows(golden_conn, table)
agent_rows = _fetch_rows(agent_conn, table)
if not golden_rows and not agent_rows:
return 1.0 # both empty - perfect
if not golden_rows:
return 0.0 # golden has no data but agent does - wrong
golden_set = set(golden_rows)
agent_set = set(agent_rows)
matching = len(golden_set & agent_set)
precision = matching / len(agent_set) if agent_set else 0.0
recall = matching / len(golden_set)
if precision + recall == 0:
data_score = 0.0
else:
data_score = 2 * precision * recall / (precision + recall) # F1
# Schema mismatch: cap the maximum achievable score at 0.5
if not schema_match:
data_score = min(data_score, 0.5)
return data_score
def _fetch_rows(conn: sqlite3.Connection, table: str) -> List[Tuple[Any, ...]]:
"""Fetch all rows ordered by rowid for deterministic comparison."""
try:
cursor = conn.execute(f"SELECT * FROM {table} ORDER BY rowid")
return [tuple(row) for row in cursor.fetchall()]
except sqlite3.OperationalError:
return []
def _schemas_match(
agent_conn: sqlite3.Connection,
golden_conn: sqlite3.Connection,
table: str,
) -> bool:
"""Compare PRAGMA table_info output for the given table."""
golden_info = _table_info(golden_conn, table)
agent_info = _table_info(agent_conn, table)
return golden_info == agent_info
def _table_info(conn: sqlite3.Connection, table: str) -> List[Tuple]:
"""Return (name, type, notnull, pk) tuples from PRAGMA table_info."""
try:
cursor = conn.execute(f"PRAGMA table_info({table})")
# We compare only name, type, notnull, pk (ignore cid and dflt_value for flexibility)
return [(row[1], row[2].upper(), row[3], row[5]) for row in cursor.fetchall()]
except sqlite3.OperationalError:
return []
# Violation counter (used for intermediate rewards)
def compute_violations(conn: sqlite3.Connection, task_id: int) -> int:
"""Return the current violation count for the given task.
Task 1: number of extra (duplicate) email rows.
Task 2: number of FK violations in the purchases table.
Task 3: number of unique employee IDs in employees_old not yet in employees_new.
Task 4: number of columns in inventory+categories with wrong names vs golden schema.
Task 5: number of missing project records that should be present (excluding decommissioned).
"""
try:
if task_id == 1:
row = conn.execute(
"""
SELECT COUNT(*) FROM users
WHERE id NOT IN (
SELECT MIN(id) FROM users GROUP BY email
)
"""
).fetchone()
return row[0]
elif task_id == 2:
conn.execute("PRAGMA foreign_keys = ON")
rows = conn.execute("PRAGMA foreign_key_check(purchases)").fetchall()
return len(rows)
elif task_id == 3:
row = conn.execute(
"""
SELECT COUNT(DISTINCT id) FROM employees_old
WHERE id NOT IN (SELECT id FROM employees_new)
"""
).fetchone()
return row[0]
elif task_id == 4:
# Count columns in inventory and categories that don't match golden names
golden_inv_cols = {"id", "product_name", "category_id", "quantity",
"unit_price", "warehouse", "last_updated"}
golden_cat_cols = {"id", "name", "description", "parent_id", "active"}
violations = 0
try:
inv_cols = {row[1] for row in conn.execute("PRAGMA table_info(inventory)").fetchall()}
violations += len(golden_inv_cols - inv_cols)
except Exception:
violations += len(golden_inv_cols)
try:
cat_cols = {row[1] for row in conn.execute("PRAGMA table_info(categories)").fetchall()}
violations += len(golden_cat_cols - cat_cols)
except Exception:
violations += len(golden_cat_cols)
return violations
elif task_id == 5:
# Count projects that should exist but don't (excluding decommissioned)
# Projects 3 and 7 should be present; also count missing assignments and budgets
violations = 0
# Missing projects (should have IDs 1-4,6-8,10 in golden)
golden_ids = {1, 2, 3, 4, 6, 7, 8, 10}
try:
present = {row[0] for row in conn.execute("SELECT id FROM projects").fetchall()}
violations += len(golden_ids - present)
except Exception:
violations += len(golden_ids)
return violations
except Exception:
return 0
return 0
|