Coverage for src/copytrading_engine/shared/sqlite.py: 82%
160 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-11-16 16:55 +0000
« prev ^ index » next coverage.py v7.16.2, created at 2026-11-16 16:55 +0000
1"""Small async boundary for one owned SQLite connection.
3The complete database operation, including commit or rollback, runs on a worker
4thread. Cancellation waits for that operation to settle before releasing the lock.
5"""
7import asyncio
8import re
9import sqlite3
10from collections.abc import Callable
11from dataclasses import dataclass
12from pathlib import Path
13from typing import TypeVar
15T = TypeVar("T")
18class SQLiteUnit:
19 def __init__(self, connection: sqlite3.Connection) -> None:
20 self.connection = connection
21 self._lock = asyncio.Lock()
22 self._closing = False
23 self._close_task: asyncio.Task[None] | None = None
24 self._closed = False
25 self._usable = True
27 @classmethod
28 async def open(cls, path: Path) -> SQLiteUnit:
29 def connect() -> sqlite3.Connection:
30 path.parent.mkdir(parents=True, exist_ok=True)
31 db = sqlite3.connect(path, timeout=5, isolation_level=None, check_same_thread=False)
32 try:
33 db.execute("PRAGMA journal_mode=WAL")
34 db.execute("PRAGMA synchronous=FULL")
35 db.execute("PRAGMA foreign_keys=ON")
36 db.execute("PRAGMA busy_timeout=5000")
37 except BaseException:
38 db.close()
39 raise
40 return db
42 task = asyncio.create_task(asyncio.to_thread(connect))
43 try:
44 return cls(await asyncio.shield(task))
45 except asyncio.CancelledError:
46 while not task.done():
47 try:
48 await asyncio.shield(task)
49 except asyncio.CancelledError:
50 pass
51 if not task.cancelled(): 51 ↛ 61line 51 didn't jump to line 61 because the condition on line 51 was always true
52 error = task.exception()
53 if error is None: 53 ↛ 61line 53 didn't jump to line 61 because the condition on line 53 was always true
54 closer = asyncio.create_task(asyncio.to_thread(task.result().close))
55 while not closer.done():
56 try:
57 await asyncio.shield(closer)
58 except asyncio.CancelledError:
59 pass
60 closer.result()
61 raise
63 async def run(self, operation: Callable[[sqlite3.Connection], T], *, write: bool = False) -> T:
64 if self._closing:
65 raise RuntimeError("SQLite session is unusable")
66 async with self._lock:
67 if self._closing or self._closed or not self._usable: 67 ↛ 68line 67 didn't jump to line 68 because the condition on line 67 was never true
68 raise RuntimeError("SQLite session is unusable")
70 def execute() -> T:
71 try:
72 if write:
73 self.connection.execute("BEGIN IMMEDIATE")
74 result = operation(self.connection)
75 if write:
76 try:
77 self.connection.commit()
78 except sqlite3.Error:
79 # The caller cannot know whether COMMIT reached disk.
80 self._usable = False
81 raise
82 return result
83 except BaseException:
84 if write:
85 try:
86 self.connection.rollback()
87 except sqlite3.Error:
88 self._usable = False
89 raise
91 task = asyncio.create_task(asyncio.to_thread(execute))
92 try:
93 return await asyncio.shield(task)
94 except asyncio.CancelledError:
95 # Repeated cancellation must not release the lock while the
96 # worker still owns a transaction or allow close() to race it.
97 while not task.done():
98 try:
99 await asyncio.shield(task)
100 except asyncio.CancelledError:
101 pass
102 if not task.cancelled():
103 task.exception()
104 raise
106 async def _close_owned(self) -> None:
107 async with self._lock:
108 if not self._closed: 108 ↛ exitline 108 didn't jump to the function exit
109 self._closed = True
110 self._usable = False
111 await asyncio.to_thread(self.connection.close)
113 async def close(self) -> None:
114 if self._close_task is None:
115 self._closing = True
116 self._close_task = asyncio.create_task(self._close_owned())
117 task = self._close_task
118 cancelled = False
119 while True:
120 try:
121 await asyncio.shield(task)
122 break
123 except asyncio.CancelledError:
124 cancelled = True
125 if task.done(): 125 ↛ 126line 125 didn't jump to line 126 because the condition on line 125 was never true
126 break
127 except BaseException: # noqa: BLE001 - keep draining until the worker settles
128 break
129 task.result()
130 if cancelled:
131 raise asyncio.CancelledError
134class SchemaMismatch(RuntimeError):
135 """A database component's recorded revision or objects differ from this build."""
138@dataclass(frozen=True, slots=True)
139class SchemaComponent:
140 """One store's named, revisioned set of SQLite objects."""
142 name: str
143 revision: int
144 ddl: str
146 def __post_init__(self) -> None:
147 if not self.name or self.revision < 1: 147 ↛ 148line 147 didn't jump to line 148 because the condition on line 147 was never true
148 raise ValueError("Schema component needs a name and a positive revision")
149 for statement in self.statements():
150 if _object_identity(statement) is None:
151 raise ValueError(f"Schema component {self.name} has an unsupported statement")
153 def statements(self) -> tuple[str, ...]:
154 return tuple(part.strip() for part in self.ddl.split(";") if part.strip())
157_REVISIONS_TABLE = (
158 "CREATE TABLE IF NOT EXISTS copytrading_engine_schema_revisions "
159 "(component TEXT PRIMARY KEY, revision INTEGER NOT NULL)"
160)
163def ensure_schema(db: sqlite3.Connection, component: SchemaComponent) -> None:
164 """Reject incompatible component objects, then create missing ones and record the revision."""
165 db.execute(_REVISIONS_TABLE)
166 recorded = _recorded_revision(db, component)
167 if recorded is not None and recorded != component.revision:
168 raise SchemaMismatch(f"Unsupported {component.name} schema revision: {recorded}")
169 for statement in component.statements():
170 name, existing = _existing_sql(db, statement)
171 if existing is not None and _normalized(existing) != _normalized(statement):
172 raise SchemaMismatch(f"Incompatible {component.name} schema object: {name}")
173 db.execute(statement)
174 db.execute(
175 "INSERT OR IGNORE INTO copytrading_engine_schema_revisions VALUES (?,?)",
176 (component.name, component.revision),
177 )
180def verify_schema(db: sqlite3.Connection, component: SchemaComponent) -> None:
181 """Check a component's recorded revision and every object without writing."""
182 revisions_table = db.execute(
183 "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?",
184 ("copytrading_engine_schema_revisions",),
185 ).fetchone()
186 if revisions_table is None: 186 ↛ 187line 186 didn't jump to line 187 because the condition on line 186 was never true
187 raise SchemaMismatch(f"Missing {component.name} schema revision")
188 if _recorded_revision(db, component) != component.revision:
189 raise SchemaMismatch(f"Unsupported {component.name} schema revision")
190 for statement in component.statements():
191 name, existing = _existing_sql(db, statement)
192 if existing is None or _normalized(existing) != _normalized(statement):
193 raise SchemaMismatch(f"Incompatible {component.name} schema object: {name}")
196def _recorded_revision(db: sqlite3.Connection, component: SchemaComponent) -> int | None:
197 row = db.execute(
198 "SELECT revision FROM copytrading_engine_schema_revisions WHERE component=?",
199 (component.name,),
200 ).fetchone()
201 return None if row is None else row[0]
204def _object_identity(statement: str) -> tuple[str, str] | None:
205 match = re.match(r"CREATE\s+(TABLE|INDEX)\s+IF\s+NOT\s+EXISTS\s+(\w+)", statement, re.I)
206 return None if match is None else (match.group(1).lower(), match.group(2))
209def _existing_sql(db: sqlite3.Connection, statement: str) -> tuple[str, str | None]:
210 identity = _object_identity(statement)
211 if identity is None: 211 ↛ 212line 211 didn't jump to line 212 because the condition on line 211 was never true
212 raise ValueError("Schema contains an unsupported statement")
213 row = db.execute("SELECT sql FROM sqlite_master WHERE type=? AND name=?", identity).fetchone()
214 return identity[1], None if row is None else row[0]
217def _normalized(sql: str) -> str:
218 sql = re.sub(r"\bIF NOT EXISTS\b", "", sql, flags=re.I)
219 return re.sub(r"\s+", " ", sql).strip().rstrip(";").upper()