"""SQLite 访问层:对象库、行级血缘、观测台账都在同一个库文件里,便于整体备份。""" from __future__ import annotations import sqlite3 import threading from collections.abc import Iterator from contextlib import contextmanager from pathlib import Path from .config import settings SCHEMA = """ CREATE TABLE IF NOT EXISTS workspaces ( id TEXT PRIMARY KEY, name TEXT NOT NULL, description TEXT DEFAULT '', created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS objects ( id TEXT PRIMARY KEY, workspace_id TEXT NOT NULL, name TEXT NOT NULL, kind TEXT NOT NULL, -- raw / stat media_type TEXT DEFAULT '', path TEXT NOT NULL, sha256 TEXT NOT NULL, bytes INTEGER NOT NULL DEFAULT 0, rows INTEGER DEFAULT 0, columns_json TEXT DEFAULT '[]', producer TEXT DEFAULT '', parents_json TEXT DEFAULT '[]', meta_json TEXT DEFAULT '{}', created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_objects_ws ON objects(workspace_id, created_at DESC); CREATE TABLE IF NOT EXISTS tables ( object_id TEXT NOT NULL, name TEXT NOT NULL, -- 逻辑表名(工作表名 / raw) sqlite_name TEXT NOT NULL, rows INTEGER DEFAULT 0, columns_json TEXT DEFAULT '[]', PRIMARY KEY (object_id, name) ); CREATE TABLE IF NOT EXISTS row_lineage ( object_id TEXT NOT NULL, table_name TEXT NOT NULL, row_index INTEGER NOT NULL, row_hash TEXT NOT NULL, source_object_id TEXT, source_rows TEXT, PRIMARY KEY (object_id, table_name, row_index) ); CREATE TABLE IF NOT EXISTS calls ( call_id TEXT PRIMARY KEY, tool TEXT NOT NULL, args_hash TEXT NOT NULL, args_json TEXT NOT NULL, result_hash TEXT NOT NULL, result_json TEXT NOT NULL, object_ids TEXT DEFAULT '[]', verdict_hint TEXT DEFAULT '', prev_hash TEXT DEFAULT '', hash TEXT DEFAULT '', session_id TEXT DEFAULT '', created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_calls_session ON calls(session_id, created_at); CREATE TABLE IF NOT EXISTS runs ( id TEXT PRIMARY KEY, object_id TEXT, input_object TEXT, status TEXT NOT NULL, operation TEXT NOT NULL, params_json TEXT NOT NULL, progress REAL DEFAULT 0, message TEXT DEFAULT '', error TEXT DEFAULT '', started_at TEXT NOT NULL, finished_at TEXT ); -- 台账索引:把工具返回过的每个字段值落成一行,核验层用它做 (call_id,row_index,field,value) 反查 CREATE TABLE IF NOT EXISTS value_index ( call_id TEXT NOT NULL, session_id TEXT DEFAULT '', object_id TEXT DEFAULT '', table_name TEXT DEFAULT '', row_index INTEGER DEFAULT -1, field TEXT NOT NULL, value_text TEXT NOT NULL, value_num REAL ); CREATE INDEX IF NOT EXISTS idx_value_num ON value_index(session_id, value_num); CREATE INDEX IF NOT EXISTS idx_value_text ON value_index(session_id, value_text); CREATE INDEX IF NOT EXISTS idx_value_call ON value_index(call_id); """ class Database: """极简连接管理:每线程一个连接 + WAL,写操作走短事务。""" def __init__(self, path: str | Path | None = None) -> None: self.path = Path(path) if path else settings.db_path self.path.parent.mkdir(parents=True, exist_ok=True) self._local = threading.local() with self.connect() as connection: connection.executescript(SCHEMA) def _new_connection(self) -> sqlite3.Connection: connection = sqlite3.connect(str(self.path), timeout=30, isolation_level=None) connection.row_factory = sqlite3.Row connection.execute("PRAGMA journal_mode=WAL") connection.execute("PRAGMA foreign_keys=ON") connection.execute("PRAGMA busy_timeout=15000") return connection @contextmanager def connect(self) -> Iterator[sqlite3.Connection]: connection = getattr(self._local, "connection", None) if connection is None: connection = self._new_connection() self._local.connection = connection try: yield connection finally: pass @contextmanager def transaction(self) -> Iterator[sqlite3.Connection]: with self.connect() as connection: connection.execute("BEGIN IMMEDIATE") try: yield connection except Exception: connection.execute("ROLLBACK") raise else: connection.execute("COMMIT") def close(self) -> None: connection = getattr(self._local, "connection", None) if connection is not None: connection.close() self._local.connection = None database = Database()