SQLite-backed state store with persistent connection. Thread-safe via internal lock. The connection is opened once at construction time and reused for all operations, eliminating the per-call open/close overhead.
| 27 | |
| 28 | |
| 29 | class StateStore: |
| 30 | """ |
| 31 | SQLite-backed state store with persistent connection. |
| 32 | |
| 33 | Thread-safe via internal lock. The connection is opened once at |
| 34 | construction time and reused for all operations, eliminating the |
| 35 | per-call open/close overhead. |
| 36 | """ |
| 37 | |
| 38 | def __init__(self, db_path: Path = STATE_DB): |
| 39 | self.db_path = db_path |
| 40 | self._lock = Lock() |
| 41 | self._conn: sqlite3.Connection = sqlite3.connect( |
| 42 | str(self.db_path), |
| 43 | timeout=30.0, |
| 44 | check_same_thread=False, |
| 45 | ) |
| 46 | self._conn.row_factory = sqlite3.Row |
| 47 | # Enable WAL mode for better concurrent read performance |
| 48 | self._conn.execute("PRAGMA journal_mode=WAL") |
| 49 | self._init_schema() |
| 50 | |
| 51 | def close(self): |
| 52 | """Close the persistent connection.""" |
| 53 | with self._lock: |
| 54 | try: |
| 55 | self._conn.close() |
| 56 | except Exception: |
| 57 | pass |
| 58 | |
| 59 | def _init_schema(self): |
| 60 | """Initialize database schema if not exists.""" |
| 61 | with self._lock: |
| 62 | cur = self._conn.cursor() |
| 63 | |
| 64 | # State table (key-value store) |
| 65 | cur.execute(""" |
| 66 | CREATE TABLE IF NOT EXISTS state ( |
| 67 | key TEXT PRIMARY KEY, |
| 68 | value TEXT NOT NULL, |
| 69 | updated_at INTEGER NOT NULL |
| 70 | ) |
| 71 | """) |
| 72 | |
| 73 | # Task queue table |
| 74 | cur.execute(""" |
| 75 | CREATE TABLE IF NOT EXISTS task_queue ( |
| 76 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 77 | description TEXT NOT NULL, |
| 78 | status TEXT NOT NULL, |
| 79 | result TEXT, |
| 80 | created_at INTEGER NOT NULL, |
| 81 | started_at INTEGER, |
| 82 | completed_at INTEGER, |
| 83 | dependencies TEXT DEFAULT '[]', |
| 84 | retry_count INTEGER DEFAULT 0 |
| 85 | ) |
| 86 | """) |