| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153 |
- """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()
|