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

1"""Small async boundary for one owned SQLite connection. 

2 

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""" 

6 

7import asyncio 

8import re 

9import sqlite3 

10from collections.abc import Callable 

11from dataclasses import dataclass 

12from pathlib import Path 

13from typing import TypeVar 

14 

15T = TypeVar("T") 

16 

17 

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 

26 

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 

41 

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 

62 

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

69 

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 

90 

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 

105 

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) 

112 

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 

132 

133 

134class SchemaMismatch(RuntimeError): 

135 """A database component's recorded revision or objects differ from this build.""" 

136 

137 

138@dataclass(frozen=True, slots=True) 

139class SchemaComponent: 

140 """One store's named, revisioned set of SQLite objects.""" 

141 

142 name: str 

143 revision: int 

144 ddl: str 

145 

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

152 

153 def statements(self) -> tuple[str, ...]: 

154 return tuple(part.strip() for part in self.ddl.split(";") if part.strip()) 

155 

156 

157_REVISIONS_TABLE = ( 

158 "CREATE TABLE IF NOT EXISTS copytrading_engine_schema_revisions " 

159 "(component TEXT PRIMARY KEY, revision INTEGER NOT NULL)" 

160) 

161 

162 

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 ) 

178 

179 

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}") 

194 

195 

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] 

202 

203 

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)) 

207 

208 

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] 

215 

216 

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