Spaces:
Running
Running
| """ | |
| SQLite 数据库管理器 —— 线程安全的单例模式，管理录制历史记录表。 | |
| """ | |
| import sqlite3 | |
| import os | |
| import threading | |
| from config.settings import DB_PATH, DATA_DIR | |
| class Database: | |
| """简单的 SQLite 封装，含建表逻辑和线程本地连接。""" | |
| _local = threading.local() | |
| def __init__(self, db_path: str = DB_PATH): | |
| self._db_path = db_path | |
| def conn(self) -> sqlite3.Connection: | |
| """获取当前线程的数据库连接（自动创建）。""" | |
| if not hasattr(self._local, "conn") or self._local.conn is None: | |
| os.makedirs(os.path.dirname(self._db_path), exist_ok=True) | |
| c = sqlite3.connect(self._db_path) | |
| c.row_factory = sqlite3.Row | |
| c.execute("PRAGMA journal_mode=WAL") | |
| c.execute("PRAGMA foreign_keys=ON") | |
| self._local.conn = c | |
| return self._local.conn | |
| def close(self): | |
| """关闭当前线程的数据库连接。""" | |
| if hasattr(self._local, "conn") and self._local.conn: | |
| self._local.conn.close() | |
| self._local.conn = None | |
| # ── 建表 SQL ────────────────────────────────────── | |
| SCHEMA_SQL = """ | |
| CREATE TABLE IF NOT EXISTS recording ( | |
| id TEXT PRIMARY KEY, | |
| camera_index INTEGER NOT NULL, | |
| camera_name TEXT NOT NULL, | |
| file_path TEXT, | |
| episode_index INTEGER DEFAULT 0, -- v1.1.0 池化：任务目录内的全局 episode 序号 | |
| file_size_mb REAL DEFAULT 0, | |
| duration_sec REAL DEFAULT 0, | |
| resolution_w INTEGER DEFAULT 0, | |
| resolution_h INTEGER DEFAULT 0, | |
| status TEXT NOT NULL DEFAULT 'completed', -- completed | uploaded | aborted | deleted | uploaded_deleted | |
| started_at TEXT NOT NULL, | |
| finished_at TEXT NOT NULL | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_recording_camera ON recording(camera_index); | |
| CREATE INDEX IF NOT EXISTS idx_recording_date ON recording(started_at); | |
| CREATE TABLE IF NOT EXISTS upload_task ( | |
| id TEXT PRIMARY KEY, | |
| session_path TEXT NOT NULL, | |
| session_name TEXT NOT NULL, | |
| episode_index INTEGER DEFAULT 0, -- v1.1.0 池化：全局 episode 序号 | |
| status TEXT NOT NULL DEFAULT 'pending', -- pending | uploading | completed | failed | skipped | |
| progress REAL DEFAULT 0.0, | |
| retry_count INTEGER DEFAULT 0, | |
| server_url TEXT NOT NULL, | |
| server_session_id TEXT DEFAULT '', | |
| error_message TEXT DEFAULT '', | |
| created_at TEXT NOT NULL, | |
| updated_at TEXT NOT NULL | |
| ); | |
| CREATE INDEX IF NOT EXISTS idx_upload_status ON upload_task(status); | |
| CREATE INDEX IF NOT EXISTS idx_upload_session ON upload_task(session_path); | |
| """ | |
| def init_schema(self): | |
| """执行建表语句，并做既有库的列迁移（向后兼容）。""" | |
| self.conn.executescript(self.SCHEMA_SQL) | |
| # CREATE TABLE IF NOT EXISTS 不给既有表加列，需 ALTER 迁移 | |
| self._ensure_column("recording", "episode_index", "INTEGER DEFAULT 0") | |
| self._ensure_column("upload_task", "episode_index", "INTEGER DEFAULT 0") | |
| self.conn.commit() | |
| def _ensure_column(self, table: str, column: str, decl: str): | |
| """表缺列时 ALTER TABLE ADD COLUMN（幂等）。""" | |
| cols = {r[1] for r in self.conn.execute(f"PRAGMA table_info({table})")} | |
| if column not in cols: | |
| self.conn.execute( | |
| f"ALTER TABLE {table} ADD COLUMN {column} {decl}") | |
| # 模块级单例 | |
| db = Database() | |