db.py 4.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153
  1. """SQLite 访问层:对象库、行级血缘、观测台账都在同一个库文件里,便于整体备份。"""
  2. from __future__ import annotations
  3. import sqlite3
  4. import threading
  5. from collections.abc import Iterator
  6. from contextlib import contextmanager
  7. from pathlib import Path
  8. from .config import settings
  9. SCHEMA = """
  10. CREATE TABLE IF NOT EXISTS workspaces (
  11. id TEXT PRIMARY KEY,
  12. name TEXT NOT NULL,
  13. description TEXT DEFAULT '',
  14. created_at TEXT NOT NULL
  15. );
  16. CREATE TABLE IF NOT EXISTS objects (
  17. id TEXT PRIMARY KEY,
  18. workspace_id TEXT NOT NULL,
  19. name TEXT NOT NULL,
  20. kind TEXT NOT NULL, -- raw / stat
  21. media_type TEXT DEFAULT '',
  22. path TEXT NOT NULL,
  23. sha256 TEXT NOT NULL,
  24. bytes INTEGER NOT NULL DEFAULT 0,
  25. rows INTEGER DEFAULT 0,
  26. columns_json TEXT DEFAULT '[]',
  27. producer TEXT DEFAULT '',
  28. parents_json TEXT DEFAULT '[]',
  29. meta_json TEXT DEFAULT '{}',
  30. created_at TEXT NOT NULL
  31. );
  32. CREATE INDEX IF NOT EXISTS idx_objects_ws ON objects(workspace_id, created_at DESC);
  33. CREATE TABLE IF NOT EXISTS tables (
  34. object_id TEXT NOT NULL,
  35. name TEXT NOT NULL, -- 逻辑表名(工作表名 / raw)
  36. sqlite_name TEXT NOT NULL,
  37. rows INTEGER DEFAULT 0,
  38. columns_json TEXT DEFAULT '[]',
  39. PRIMARY KEY (object_id, name)
  40. );
  41. CREATE TABLE IF NOT EXISTS row_lineage (
  42. object_id TEXT NOT NULL,
  43. table_name TEXT NOT NULL,
  44. row_index INTEGER NOT NULL,
  45. row_hash TEXT NOT NULL,
  46. source_object_id TEXT,
  47. source_rows TEXT,
  48. PRIMARY KEY (object_id, table_name, row_index)
  49. );
  50. CREATE TABLE IF NOT EXISTS calls (
  51. call_id TEXT PRIMARY KEY,
  52. tool TEXT NOT NULL,
  53. args_hash TEXT NOT NULL,
  54. args_json TEXT NOT NULL,
  55. result_hash TEXT NOT NULL,
  56. result_json TEXT NOT NULL,
  57. object_ids TEXT DEFAULT '[]',
  58. verdict_hint TEXT DEFAULT '',
  59. prev_hash TEXT DEFAULT '',
  60. hash TEXT DEFAULT '',
  61. session_id TEXT DEFAULT '',
  62. created_at TEXT NOT NULL
  63. );
  64. CREATE INDEX IF NOT EXISTS idx_calls_session ON calls(session_id, created_at);
  65. CREATE TABLE IF NOT EXISTS runs (
  66. id TEXT PRIMARY KEY,
  67. object_id TEXT,
  68. input_object TEXT,
  69. status TEXT NOT NULL,
  70. operation TEXT NOT NULL,
  71. params_json TEXT NOT NULL,
  72. progress REAL DEFAULT 0,
  73. message TEXT DEFAULT '',
  74. error TEXT DEFAULT '',
  75. started_at TEXT NOT NULL,
  76. finished_at TEXT
  77. );
  78. -- 台账索引:把工具返回过的每个字段值落成一行,核验层用它做 (call_id,row_index,field,value) 反查
  79. CREATE TABLE IF NOT EXISTS value_index (
  80. call_id TEXT NOT NULL,
  81. session_id TEXT DEFAULT '',
  82. object_id TEXT DEFAULT '',
  83. table_name TEXT DEFAULT '',
  84. row_index INTEGER DEFAULT -1,
  85. field TEXT NOT NULL,
  86. value_text TEXT NOT NULL,
  87. value_num REAL
  88. );
  89. CREATE INDEX IF NOT EXISTS idx_value_num ON value_index(session_id, value_num);
  90. CREATE INDEX IF NOT EXISTS idx_value_text ON value_index(session_id, value_text);
  91. CREATE INDEX IF NOT EXISTS idx_value_call ON value_index(call_id);
  92. """
  93. class Database:
  94. """极简连接管理:每线程一个连接 + WAL,写操作走短事务。"""
  95. def __init__(self, path: str | Path | None = None) -> None:
  96. self.path = Path(path) if path else settings.db_path
  97. self.path.parent.mkdir(parents=True, exist_ok=True)
  98. self._local = threading.local()
  99. with self.connect() as connection:
  100. connection.executescript(SCHEMA)
  101. def _new_connection(self) -> sqlite3.Connection:
  102. connection = sqlite3.connect(str(self.path), timeout=30, isolation_level=None)
  103. connection.row_factory = sqlite3.Row
  104. connection.execute("PRAGMA journal_mode=WAL")
  105. connection.execute("PRAGMA foreign_keys=ON")
  106. connection.execute("PRAGMA busy_timeout=15000")
  107. return connection
  108. @contextmanager
  109. def connect(self) -> Iterator[sqlite3.Connection]:
  110. connection = getattr(self._local, "connection", None)
  111. if connection is None:
  112. connection = self._new_connection()
  113. self._local.connection = connection
  114. try:
  115. yield connection
  116. finally:
  117. pass
  118. @contextmanager
  119. def transaction(self) -> Iterator[sqlite3.Connection]:
  120. with self.connect() as connection:
  121. connection.execute("BEGIN IMMEDIATE")
  122. try:
  123. yield connection
  124. except Exception:
  125. connection.execute("ROLLBACK")
  126. raise
  127. else:
  128. connection.execute("COMMIT")
  129. def close(self) -> None:
  130. connection = getattr(self._local, "connection", None)
  131. if connection is not None:
  132. connection.close()
  133. self._local.connection = None
  134. database = Database()