Spaces:
Sleeping
Sleeping
File size: 3,568 Bytes
7e0eb7b bee1eed 7e0eb7b bee1eed 7e0eb7b 6690949 7e0eb7b 6690949 7e0eb7b 6690949 7e0eb7b 6690949 7e0eb7b 6690949 7e0eb7b | 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 | """
database.py — SQLite schema + helpers for Teacher AI progress tracking.
Tables: students, sessions, attempts, topic_mastery, error_log
"""
import sqlite3
import os
from contextlib import contextmanager
from pathlib import Path
# Store DB inside the app folder — persists between restarts
# Path: /code/app/data/teacher_ai.db
BASE_DIR = Path(__file__).parent
DATA_DIR = BASE_DIR / "data"
DATA_DIR.mkdir(exist_ok=True) # create /code/app/data/ if it doesn't exist
DB_PATH = str(DATA_DIR / "teacher_ai.db")
def get_conn() -> sqlite3.Connection:
conn = sqlite3.connect(DB_PATH, check_same_thread=False)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA journal_mode=WAL")
conn.execute("PRAGMA foreign_keys=ON")
return conn
@contextmanager
def db():
conn = get_conn()
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
def init_db():
with db() as conn:
conn.executescript("""
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
role TEXT NOT NULL DEFAULT 'student',
level TEXT NOT NULL DEFAULT 'high_school',
streak INTEGER NOT NULL DEFAULT 0,
last_active TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS attempts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER NOT NULL REFERENCES students(id),
question TEXT NOT NULL,
topic TEXT NOT NULL,
error_type TEXT,
response TEXT,
difficulty INTEGER NOT NULL DEFAULT 5,
mode TEXT NOT NULL DEFAULT 'text',
explain_level TEXT NOT NULL DEFAULT 'normal',
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS topic_mastery (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER NOT NULL REFERENCES students(id),
topic TEXT NOT NULL,
attempts INTEGER NOT NULL DEFAULT 0,
correct INTEGER NOT NULL DEFAULT 0,
mastery_score REAL NOT NULL DEFAULT 0.0,
last_updated TEXT NOT NULL DEFAULT (datetime('now')),
UNIQUE(student_id, topic)
);
CREATE TABLE IF NOT EXISTS error_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id INTEGER NOT NULL REFERENCES students(id),
topic TEXT NOT NULL,
error_type TEXT NOT NULL,
count INTEGER NOT NULL DEFAULT 1,
last_seen TEXT NOT NULL DEFAULT (datetime('now')),
UNIQUE(student_id, topic, error_type)
);
CREATE INDEX IF NOT EXISTS idx_attempts_student ON attempts(student_id);
CREATE INDEX IF NOT EXISTS idx_attempts_topic ON attempts(topic);
CREATE INDEX IF NOT EXISTS idx_mastery_student ON topic_mastery(student_id);
""")
# Always ensure the default Guest student (id=1) exists.
# All unauthenticated requests use student_id=1.
conn.execute("""
INSERT OR IGNORE INTO students (id, name, role, level)
VALUES (1, 'Guest', 'student', 'high_school')
""")
print(f"[DB] Initialised at {DB_PATH}")
|