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
« 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.
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"""
9from __future__ import annotations
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
19from psycopg_pool import ConnectionPool
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
27T = TypeVar('T')
29_POOLS: dict[tuple[str, str | None], ConnectionPool] = {}
30_POOLS_LOCK = threading.Lock()
33class Cursor:
34 """Thin wrapper over a driver cursor with translated errors."""
36 def __init__(self, raw: Any, dialect: Dialect) -> None:
37 self._raw = raw
38 self._dialect = dialect
40 @property
41 def rowcount(self) -> int:
42 return self._raw.rowcount
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
51 @property
52 def description(self) -> Any:
53 return self._raw.description
55 def fetchone(self) -> Any:
56 with wrap_errors():
57 return self._raw.fetchone()
59 def fetchall(self) -> list[Any]:
60 with wrap_errors():
61 return self._raw.fetchall()
63 def fetchmany(self, size: int) -> list[Any]:
64 with wrap_errors():
65 return self._raw.fetchmany(size)
67 def __iter__(self) -> Iterator[Any]:
68 while True:
69 row = self.fetchone()
70 if row is None:
71 return
72 yield row
74 def close(self) -> None:
75 with wrap_errors():
76 self._raw.close()
79class Connection:
80 """A single database connection, usable on either backend."""
82 def __init__(self, dialect: Dialect, raw: Any) -> None:
83 self.dialect = dialect
84 self.raw = raw
85 self._postgres_changes = 0
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
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
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)
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)
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)
131 def commit(self) -> None:
132 with wrap_errors():
133 self.raw.commit()
135 def rollback(self) -> None:
136 with wrap_errors():
137 self.raw.rollback()
140class _ThreadState(threading.local):
141 """Per-thread bookkeeping for one ``Database``'s connect() nesting."""
143 depth: int = 0
144 conn: Connection | None = None
145 failed: bool = False
148class Database:
149 """Opens connections to one SQLite file or one PostgreSQL pool."""
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()
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)
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)
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 )
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)
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)
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
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)
269 def connect(self) -> contextlib.AbstractContextManager[Connection]:
270 """Yield a connection; re-entrant per thread.
272 Only the outermost block commits or rolls back and releases the
273 connection.
274 """
275 return self._session(lock=False)
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)
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
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
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()
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.
329 ``group`` is accepted for symmetry with ``Database.for_group``.
330 """
331 if isinstance(obj, Database):
332 return obj
333 return Database.sqlite(obj)