Coverage for downtify/db/core.py: 98%

205 statements  

« prev     ^ index     » next       coverage.py v7.17.4, created at 2026-11-21 22:18 +0000

1"""Backend-neutral database access for SQLite and PostgreSQL. 

2 

3``Database`` hands out ``Connection`` objects. Each connection exposes the 

4same small API on both backends: ``?`` placeholders (translated for 

5PostgreSQL), rows that support both index and column-name access, and 

6errors translated to the classes in ``downtify.db.errors``. 

7""" 

8 

9from __future__ import annotations 

10 

11import contextlib 

12import random 

13import threading 

14import time 

15from collections.abc import Callable, Iterable, Iterator, Sequence 

16from pathlib import Path 

17from typing import Any, TypeVar 

18 

19from psycopg_pool import ConnectionPool 

20 

21from downtify.db.config import DbConfig, Dialect 

22from downtify.db.errors import DatabaseError, SerializationRetry, wrap_errors 

23from downtify.db.rows import pg_row_factory 

24from downtify.db.sql import to_pyformat 

25from downtify.sqlite_utils import connect_sqlite 

26 

27T = TypeVar('T') 

28 

29_POOLS: dict[tuple[str, str | None], ConnectionPool] = {} 

30_POOLS_LOCK = threading.Lock() 

31 

32 

33class Cursor: 

34 """Thin wrapper over a driver cursor with translated errors.""" 

35 

36 def __init__(self, raw: Any, dialect: Dialect) -> None: 

37 self._raw = raw 

38 self._dialect = dialect 

39 

40 @property 

41 def rowcount(self) -> int: 

42 return self._raw.rowcount 

43 

44 @property 

45 def lastrowid(self) -> int | None: 

46 """SQLite's last inserted row id; always None on PostgreSQL.""" 

47 if self._dialect is Dialect.SQLITE: 

48 return self._raw.lastrowid 

49 return None 

50 

51 @property 

52 def description(self) -> Any: 

53 return self._raw.description 

54 

55 def fetchone(self) -> Any: 

56 with wrap_errors(): 

57 return self._raw.fetchone() 

58 

59 def fetchall(self) -> list[Any]: 

60 with wrap_errors(): 

61 return self._raw.fetchall() 

62 

63 def fetchmany(self, size: int) -> list[Any]: 

64 with wrap_errors(): 

65 return self._raw.fetchmany(size) 

66 

67 def __iter__(self) -> Iterator[Any]: 

68 while True: 

69 row = self.fetchone() 

70 if row is None: 

71 return 

72 yield row 

73 

74 def close(self) -> None: 

75 with wrap_errors(): 

76 self._raw.close() 

77 

78 

79class Connection: 

80 """A single database connection, usable on either backend.""" 

81 

82 def __init__(self, dialect: Dialect, raw: Any) -> None: 

83 self.dialect = dialect 

84 self.raw = raw 

85 self._postgres_changes = 0 

86 

87 @property 

88 def total_changes(self) -> int: 

89 if self.dialect is Dialect.SQLITE: 

90 return self.raw.total_changes 

91 return self._postgres_changes 

92 

93 def _count(self, sql: str, raw_cursor: Any) -> None: 

94 # Only meaningful on PostgreSQL; SQLite reports its own total, which 

95 # counts rows changed by INSERT/UPDATE/DELETE only (not SELECT). 

96 if self.dialect is not Dialect.POSTGRES or raw_cursor.rowcount <= 0: 

97 return 

98 if sql.lstrip()[:6].upper() in {'INSERT', 'UPDATE', 'DELETE'}: 

99 self._postgres_changes += raw_cursor.rowcount 

100 

101 def execute(self, sql: str, params: Iterable[Any] = ()) -> Cursor: 

102 values = tuple(params) 

103 with wrap_errors(): 

104 if self.dialect is Dialect.POSTGRES: 

105 raw_cursor = self.raw.execute(to_pyformat(sql), values) 

106 else: 

107 raw_cursor = self.raw.execute(sql, values) 

108 self._count(sql, raw_cursor) 

109 return Cursor(raw_cursor, self.dialect) 

110 

111 def executemany(self, sql: str, seq: Iterable[Sequence[Any]]) -> Cursor: 

112 with wrap_errors(): 

113 if self.dialect is Dialect.POSTGRES: 

114 raw_cursor = self.raw.cursor() 

115 raw_cursor.executemany( 

116 to_pyformat(sql), [tuple(row) for row in seq] 

117 ) 

118 else: 

119 raw_cursor = self.raw.executemany(sql, seq) 

120 self._count(sql, raw_cursor) 

121 return Cursor(raw_cursor, self.dialect) 

122 

123 def executescript(self, sql: str) -> None: 

124 # No parameters: the SQL must not be pyformat-translated. 

125 with wrap_errors(): 

126 if self.dialect is Dialect.POSTGRES: 

127 self.raw.execute(sql) 

128 else: 

129 self.raw.executescript(sql) 

130 

131 def commit(self) -> None: 

132 with wrap_errors(): 

133 self.raw.commit() 

134 

135 def rollback(self) -> None: 

136 with wrap_errors(): 

137 self.raw.rollback() 

138 

139 

140class _ThreadState(threading.local): 

141 """Per-thread bookkeeping for one ``Database``'s connect() nesting.""" 

142 

143 depth: int = 0 

144 conn: Connection | None = None 

145 failed: bool = False 

146 

147 

148class Database: 

149 """Opens connections to one SQLite file or one PostgreSQL pool.""" 

150 

151 def __init__( 

152 self, 

153 dialect: Dialect, 

154 *, 

155 path: Path | None = None, 

156 pool: ConnectionPool | None = None, 

157 pool_key: tuple[str, str | None] | None = None, 

158 ) -> None: 

159 self.dialect = dialect 

160 self.path = path 

161 self._pool = pool 

162 self._pool_key = pool_key 

163 self._local = _ThreadState() 

164 

165 @classmethod 

166 def sqlite(cls, path: str | Path) -> Database: 

167 file_path = Path(path) 

168 file_path.parent.mkdir(parents=True, exist_ok=True) 

169 return cls(Dialect.SQLITE, path=file_path) 

170 

171 @classmethod 

172 def postgres( 

173 cls, 

174 url: str, 

175 *, 

176 min_size: int = 2, 

177 max_size: int = 20, 

178 timeout: float = 10.0, 

179 search_path: str | None = None, 

180 ) -> Database: 

181 key = (url, search_path) 

182 with _POOLS_LOCK: 

183 pool = _POOLS.get(key) 

184 if pool is None: 

185 kwargs: dict[str, Any] = {'row_factory': pg_row_factory} 

186 # UTC session time zone: naive ISO timestamps then compare 

187 # like SQLite's (UTC) julianday(). 

188 options = '-c timezone=UTC' 

189 if search_path: 

190 options += f' -c search_path={search_path}' 

191 kwargs['options'] = options 

192 pool = ConnectionPool( 

193 url, 

194 min_size=min_size, 

195 max_size=max_size, 

196 timeout=timeout, 

197 kwargs=kwargs, 

198 open=True, 

199 ) 

200 _POOLS[key] = pool 

201 return cls(Dialect.POSTGRES, pool=pool, pool_key=key) 

202 

203 @classmethod 

204 def for_group(cls, cfg: DbConfig, group: str) -> Database: 

205 if cfg.dialect is Dialect.SQLITE: 

206 return cls.sqlite(cfg.sqlite_path(group)) 

207 assert cfg.url is not None 

208 return cls.postgres( 

209 cfg.url, 

210 min_size=cfg.pool_min, 

211 max_size=cfg.pool_max, 

212 timeout=cfg.pool_timeout, 

213 search_path=cfg.search_path, 

214 ) 

215 

216 def _acquire(self) -> Connection: 

217 if self.dialect is Dialect.SQLITE: 

218 with wrap_errors(): 

219 raw = connect_sqlite(str(self.path), row_factory=True) 

220 else: 

221 assert self._pool is not None 

222 with wrap_errors(): 

223 raw = self._pool.getconn() 

224 return Connection(self.dialect, raw) 

225 

226 def _release(self, conn: Connection) -> None: 

227 if self.dialect is Dialect.SQLITE: 

228 conn.raw.close() 

229 else: 

230 assert self._pool is not None 

231 self._pool.putconn(conn.raw) 

232 

233 @contextlib.contextmanager 

234 def _session(self, *, lock: bool) -> Iterator[Connection]: 

235 state = self._local 

236 if state.depth: 

237 # Nested on this thread: reuse the open connection. 

238 assert state.conn is not None 

239 state.depth += 1 

240 try: 

241 yield state.conn 

242 except BaseException: 

243 state.failed = True 

244 raise 

245 finally: 

246 state.depth -= 1 

247 return 

248 

249 conn = self._acquire() 

250 state.conn, state.depth, state.failed = conn, 1, False 

251 commit = False 

252 try: 

253 if lock and self.dialect is Dialect.SQLITE: 

254 conn.execute('BEGIN IMMEDIATE') 

255 yield conn 

256 commit = not state.failed 

257 finally: 

258 state.conn, state.depth, state.failed = None, 0, False 

259 try: 

260 if commit: 

261 conn.commit() 

262 else: 

263 # Keep the original exception if rollback also fails. 

264 with contextlib.suppress(DatabaseError): 

265 conn.rollback() 

266 finally: 

267 self._release(conn) 

268 

269 def connect(self) -> contextlib.AbstractContextManager[Connection]: 

270 """Yield a connection; re-entrant per thread. 

271 

272 Only the outermost block commits or rolls back and releases the 

273 connection. 

274 """ 

275 return self._session(lock=False) 

276 

277 def transaction( 

278 self, *, lock: bool = False 

279 ) -> contextlib.AbstractContextManager[Connection]: 

280 """Like ``connect()``; on SQLite, ``lock=True`` takes a write lock 

281 up front with ``BEGIN IMMEDIATE`` when opening a new connection. 

282 """ 

283 return self._session(lock=lock) 

284 

285 def run( 

286 self, 

287 fn: Callable[[Connection], T], 

288 *, 

289 lock: bool = False, 

290 retries: int = 3, 

291 ) -> T: 

292 """Run ``fn(conn)`` in a transaction, retrying on serialization 

293 failures. Inside an open ``connect()`` it runs once without retry. 

294 """ 

295 if self._local.depth: 

296 assert self._local.conn is not None 

297 return fn(self._local.conn) 

298 attempt = 0 

299 while True: 

300 try: 

301 with self.transaction(lock=lock) as conn: 

302 return fn(conn) 

303 except SerializationRetry: 

304 if attempt >= retries: 

305 raise 

306 time.sleep(0.05 * 2**attempt + random.uniform(0, 0.05)) 

307 attempt += 1 

308 

309 def healthcheck(self) -> bool: 

310 try: 

311 with self.connect() as conn: 

312 conn.execute('select 1').fetchone() 

313 except Exception: 

314 return False 

315 return True 

316 

317 def close(self) -> None: 

318 if self._pool is None or self._pool_key is None: 

319 return 

320 with _POOLS_LOCK: 

321 if _POOLS.get(self._pool_key) is self._pool: 

322 del _POOLS[self._pool_key] 

323 self._pool.close() 

324 

325 

326def as_database(obj: Database | str | Path, group: str) -> Database: 

327 """Return ``obj`` if it is a Database, else open it as a SQLite path. 

328 

329 ``group`` is accepted for symmetry with ``Database.for_group``. 

330 """ 

331 if isinstance(obj, Database): 

332 return obj 

333 return Database.sqlite(obj)