Coverage for downtify/db/migrate_sqlite.py: 95%
324 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"""One-shot copy of an existing SQLite install into the PostgreSQL schema.
3``migrate`` reads the six SQLite files under a data directory (read-only,
4they are never modified), copies every table into the same-named table of
5the baseline PostgreSQL schema, fixes the identity sequences, and checks
6each table against its source: row counts and an order-independent digest
7over every column, streamed in batches. ``verify`` runs the same check on
8an already migrated install.
10Tables are loaded parents first, in the order the PostgreSQL foreign keys
11require. The whole load is one transaction: a failure rolls it back and
12leaves the target as it was. A verification mismatch does not raise; it is
13recorded in the returned :class:`Report`, whose ``ok`` is then False.
14"""
16from __future__ import annotations
18import contextlib
19import hashlib
20import sqlite3
21from collections.abc import Iterable, Iterator
22from dataclasses import dataclass, field
23from datetime import datetime, timezone
24from pathlib import Path
25from typing import Any
27import psycopg
28from psycopg import sql
30from downtify.db.config import GROUP_FILES, Dialect
31from downtify.db.core import Database
32from downtify.db.migrations.runner import run_migrations
34MARKER_NAME = 'MIGRATED_TO_POSTGRES'
35BATCH_SIZE = 5000
36_MIGRATION_TABLE = 'schema_migrations'
38# child table -> (parent table, child column, parent column). SQLite never
39# enforced these foreign keys, so old installs may hold orphan child rows;
40# they are skipped (and counted) instead of failing the load.
41_ORPHAN_RULES: dict[str, tuple[str, str, str]] = {
42 'playlist_tracks': ('playlists', 'playlist_name', 'name'),
43}
46class MigrationError(RuntimeError):
47 """The migration cannot start, or the load failed and was rolled back."""
50@dataclass(frozen=True)
51class TableReport:
52 name: str
53 source_file: str
54 source_rows: int
55 skipped: int
56 copied: int
57 target_rows: int
58 checksum_ok: bool
61@dataclass(frozen=True)
62class Report:
63 files_found: list[str]
64 tables: list[TableReport]
65 ok: bool
66 problems: list[str] = field(default_factory=list)
69@dataclass(frozen=True)
70class _Source:
71 file_name: str
72 table: str
73 columns: tuple[str, ...]
74 pk: tuple[str, ...]
75 rowid: bool
78@dataclass(frozen=True)
79class _Plan:
80 source: _Source
81 where: str
82 source_rows: int
83 matched: int
85 @property
86 def skipped(self) -> int:
87 return self.source_rows - self.matched
90@dataclass(frozen=True)
91class _Target:
92 columns: dict[str, set[str]]
93 identity: tuple[tuple[str, str], ...]
94 fk_edges: frozenset[tuple[str, str]]
97def _quote(name: str) -> str:
98 return '"' + name.replace('"', '""') + '"'
101def _found_files(data_dir: Path) -> list[tuple[str, Path]]:
102 found = []
103 for name in GROUP_FILES.values():
104 path = data_dir / name
105 if path.is_file():
106 found.append((name, path))
107 return found
110@contextlib.contextmanager
111def _open_source(path: Path) -> Iterator[sqlite3.Connection]:
112 uri = f'{path.resolve().as_uri()}?mode=ro'
113 conn = sqlite3.connect(uri, uri=True)
114 try:
115 yield conn
116 finally:
117 conn.close()
120def _sqlite_tables(conn: sqlite3.Connection) -> list[str]:
121 rows = conn.execute(
122 "SELECT name FROM sqlite_master WHERE type = 'table' "
123 "AND substr(name, 1, 7) <> 'sqlite_' ORDER BY name"
124 ).fetchall()
125 return [str(row[0]) for row in rows]
128def _sqlite_source(
129 file_name: str, conn: sqlite3.Connection, table: str
130) -> _Source:
131 info = conn.execute(f'PRAGMA table_info({_quote(table)})').fetchall()
132 columns = tuple(str(row[1]) for row in info)
133 pk = tuple(str(r[1]) for r in sorted(info, key=lambda r: r[5]) if r[5] > 0)
134 row = conn.execute(
135 "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?",
136 (table,),
137 ).fetchone()
138 rowid = not (row and row[0] and 'WITHOUT ROWID' in row[0].upper())
139 return _Source(file_name, table, columns, pk, rowid)
142def _introspect(raw: psycopg.Connection) -> _Target:
143 cur = raw.cursor()
144 cur.execute(
145 'SELECT table_name, column_name, is_identity '
146 'FROM information_schema.columns '
147 'WHERE table_schema = current_schema() '
148 'ORDER BY table_name, ordinal_position'
149 )
150 columns: dict[str, set[str]] = {}
151 identity: list[tuple[str, str]] = []
152 for table, column, is_identity in cur.fetchall():
153 if table == _MIGRATION_TABLE:
154 continue
155 columns.setdefault(table, set()).add(column)
156 if is_identity == 'YES':
157 identity.append((table, column))
158 cur.execute(
159 'SELECT parent.relname, child.relname '
160 'FROM pg_constraint k '
161 'JOIN pg_class child ON child.oid = k.conrelid '
162 'JOIN pg_class parent ON parent.oid = k.confrelid '
163 'JOIN pg_namespace n ON n.oid = child.relnamespace '
164 "WHERE k.contype = 'f' AND n.nspname = current_schema()"
165 )
166 edges = frozenset((str(p), str(c)) for p, c in cur.fetchall())
167 return _Target(columns, tuple(identity), edges)
170def _populated_tables(
171 raw: psycopg.Connection, tables: Iterable[str]
172) -> list[str]:
173 cur = raw.cursor()
174 full = []
175 for table in sorted(tables):
176 cur.execute(
177 sql.SQL('SELECT EXISTS (SELECT 1 FROM {})').format(
178 sql.Identifier(table)
179 )
180 )
181 if cur.fetchone()[0]:
182 full.append(table)
183 return full
186def _fk_order(
187 tables: Iterable[str], edges: frozenset[tuple[str, str]]
188) -> list[str]:
189 """Parents before children; ties broken by name, so runs are repeatable."""
190 names = set(tables)
191 parents = {
192 table: {
193 p for p, c in edges if c == table and p in names and p != table
194 }
195 for table in names
196 }
197 remaining = set(names)
198 order: list[str] = []
199 while remaining:
200 ready = sorted(t for t in remaining if not (parents[t] & remaining))
201 if not ready:
202 raise MigrationError(
203 'foreign-key cycle among tables: '
204 + ', '.join(sorted(remaining))
205 )
206 order.extend(ready)
207 remaining.difference_update(ready)
208 return order
211def _plan(
212 sources: list[_Source], handles: dict[str, sqlite3.Connection]
213) -> dict[str, _Plan]:
214 tables_in_file: dict[str, set[str]] = {}
215 for source in sources:
216 tables_in_file.setdefault(source.file_name, set()).add(source.table)
218 plans: dict[str, _Plan] = {}
219 for source in sources:
220 conn = handles[source.file_name]
221 where = ''
222 rule = _ORPHAN_RULES.get(source.table)
223 if (
224 rule is not None
225 and rule[0] in tables_in_file[source.file_name]
226 and rule[1] in source.columns
227 ):
228 parent, child_col, parent_col = rule
229 col = _quote(child_col)
230 where = (
231 f' WHERE {col} IS NULL OR {col} IN '
232 f'(SELECT {_quote(parent_col)} FROM {_quote(parent)})'
233 )
234 table = _quote(source.table)
235 total = conn.execute(f'SELECT COUNT(*) FROM {table}').fetchone()[0]
236 matched = conn.execute(
237 f'SELECT COUNT(*) FROM {table}{where}'
238 ).fetchone()[0]
239 plans[source.table] = _Plan(source, where, int(total), int(matched))
240 return plans
243def _select_cols(columns: tuple[str, ...]) -> str:
244 return ', '.join(_quote(c) for c in columns)
247def _load(
248 raw: psycopg.Connection,
249 plans: dict[str, _Plan],
250 handles: dict[str, sqlite3.Connection],
251 order: list[str],
252 target: _Target,
253 truncate: list[str],
254) -> dict[str, int]:
255 cur = raw.cursor()
256 if truncate:
257 cur.execute(
258 sql.SQL('TRUNCATE {} RESTART IDENTITY CASCADE').format(
259 sql.SQL(', ').join(sql.Identifier(t) for t in truncate)
260 )
261 )
263 copied: dict[str, int] = {}
264 for table in order:
265 plan = plans[table]
266 source = plan.source
267 select = (
268 f'SELECT {_select_cols(source.columns)} '
269 f'FROM {_quote(table)}{plan.where}'
270 )
271 if source.rowid:
272 # Postgres assigns identity ids in this order, keeping them
273 # in the order the rows were inserted.
274 select += ' ORDER BY rowid'
275 copy_sql = sql.SQL('COPY {} ({}) FROM STDIN').format(
276 sql.Identifier(table),
277 sql.SQL(', ').join(sql.Identifier(c) for c in source.columns),
278 )
279 count = 0
280 reader = handles[source.file_name].execute(select)
281 with cur.copy(copy_sql) as copy:
282 while True:
283 batch = reader.fetchmany(BATCH_SIZE)
284 if not batch:
285 break
286 for row in batch:
287 copy.write_row(row)
288 count += 1
289 copied[table] = count
291 for table, column in target.identity:
292 cur.execute(
293 sql.SQL(
294 'SELECT setval(pg_get_serial_sequence({t}, {c}), '
295 'GREATEST(COALESCE(MAX({col}), 0), 1), '
296 'COALESCE(MAX({col}), 0) >= 1) FROM {tbl}'
297 ).format(
298 t=sql.Literal(table),
299 c=sql.Literal(column),
300 col=sql.Identifier(column),
301 tbl=sql.Identifier(table),
302 )
303 )
305 if 'sync_meta' in target.columns and 'library_sync_seq' in _sequences(cur):
306 cur.execute("SELECT value FROM sync_meta WHERE key = 'seq'")
307 row = cur.fetchone()
308 if row is not None:
309 value = int(row[0])
310 cur.execute(
311 "SELECT setval('library_sync_seq', %s, %s)",
312 (max(value, 1), value >= 1),
313 )
314 return copied
317def _sequences(cur: psycopg.Cursor[Any]) -> set[str]:
318 cur.execute(
319 'SELECT sequence_name FROM information_schema.sequences '
320 'WHERE sequence_schema = current_schema()'
321 )
322 return {str(row[0]) for row in cur.fetchall()}
325_MODULUS = 1 << 256
328def _normalize(value: Any) -> Any:
329 if isinstance(value, (bytes, bytearray, memoryview)):
330 return bytes(value)
331 if isinstance(value, bool):
332 return int(value)
333 if isinstance(value, float):
334 return float(value)
335 return value
338def _digest_of(value: Any) -> int:
339 raw = hashlib.sha256(repr(value).encode('utf-8')).digest()
340 return int.from_bytes(raw, 'big')
343@dataclass
344class _Digest:
345 """Row count and the sum of the per-row digests, mod 2**256.
347 A sum is order-independent, so the two sides need not agree on a sort
348 order (SQLite sorts binary, PostgreSQL by locale).
349 """
351 count: int = 0
352 total: int = 0
354 def add(self, row: Iterable[Any]) -> None:
355 self.count += 1
356 normalised = tuple(_normalize(v) for v in row)
357 self.total = (self.total + _digest_of(normalised)) % _MODULUS
360def _fold(rows: Iterable[Iterable[Any]]) -> _Digest:
361 digest = _Digest()
362 for row in rows:
363 digest.add(row)
364 return digest
367def _source_rows(
368 conn: sqlite3.Connection, select: str
369) -> Iterator[tuple[Any, ...]]:
370 cursor = conn.execute(select)
371 while True:
372 batch = cursor.fetchmany(BATCH_SIZE)
373 if not batch:
374 return
375 yield from batch
378def _target_rows(
379 raw: psycopg.Connection, select: str
380) -> Iterator[tuple[Any, ...]]:
381 with raw.transaction(), raw.cursor(name='verify_digest') as cur:
382 cur.execute(select)
383 while True:
384 batch = cur.fetchmany(BATCH_SIZE)
385 if not batch:
386 return
387 yield from batch
390def _column_sums(rows: Iterable[Iterable[Any]], width: int) -> list[int]:
391 sums = [0] * width
392 for row in rows:
393 for i, value in enumerate(row):
394 sums[i] = (sums[i] + _digest_of(_normalize(value))) % _MODULUS
395 return sums
398def _first_bad_column(
399 names: tuple[str, ...],
400 src_rows: Iterable[Iterable[Any]],
401 tgt_rows: Iterable[Iterable[Any]],
402) -> str | None:
403 """Name the first column whose per-column digest sums disagree.
405 Only run after a mismatch with equal row counts, to add detail.
406 """
407 src = _column_sums(src_rows, len(names))
408 tgt = _column_sums(tgt_rows, len(names))
409 for name, left, right in zip(names, src, tgt):
410 if left != right:
411 return name
412 return None
415def _verify(
416 db: Database,
417 plans: dict[str, _Plan],
418 handles: dict[str, sqlite3.Connection],
419 order: list[str],
420 copied: dict[str, int],
421) -> tuple[list[TableReport], list[str]]:
422 reports: list[TableReport] = []
423 problems: list[str] = []
424 with db.connect() as conn:
425 raw = conn.raw
426 for table in order:
427 plan = plans[table]
428 source = plan.source
429 cols = _select_cols(source.columns)
430 src_select = f'SELECT {cols} FROM {_quote(table)}{plan.where}'
431 tgt_select = f'SELECT {cols} FROM {_quote(table)}'
432 conn_src = handles[source.file_name]
433 src = _fold(_source_rows(conn_src, src_select))
434 tgt = _fold(_target_rows(raw, tgt_select))
436 checksum_ok = src == tgt
437 if tgt.count != plan.matched or copied[table] != plan.matched:
438 problems.append(
439 f'{table}: source {plan.matched} rows after skipping '
440 f'{plan.skipped} orphans, target {tgt.count}'
441 )
442 if not checksum_ok:
443 detail = ''
444 if src.count == tgt.count:
445 column = _first_bad_column(
446 source.columns,
447 _source_rows(conn_src, src_select),
448 _target_rows(raw, tgt_select),
449 )
450 if column is not None:
451 detail = f' in column {column}'
452 problems.append(f'{table}: checksum mismatch{detail}')
453 reports.append(
454 TableReport(
455 name=table,
456 source_file=source.file_name,
457 source_rows=plan.source_rows,
458 skipped=plan.skipped,
459 copied=copied[table],
460 target_rows=tgt.count,
461 checksum_ok=checksum_ok,
462 )
463 )
464 return reports, problems
467def _collect_sources(
468 found: list[tuple[str, Path]],
469 handles: dict[str, sqlite3.Connection],
470) -> tuple[list[_Source], dict[str, str]]:
471 sources: list[_Source] = []
472 seen: dict[str, str] = {}
473 for name, _path in found:
474 conn = handles[name]
475 for table in _sqlite_tables(conn):
476 if table in seen:
477 raise MigrationError(
478 f'table {table} is in both {seen[table]} and {name}'
479 )
480 seen[table] = name
481 sources.append(_sqlite_source(name, conn, table))
482 return sources, seen
485def _check_target(sources: list[_Source], target: _Target) -> None:
486 for source in sources:
487 if source.table not in target.columns:
488 raise MigrationError(
489 f'SQLite table {source.table} has no PostgreSQL table'
490 )
491 missing = set(source.columns) - target.columns[source.table]
492 if missing:
493 raise MigrationError(
494 f'{source.table} lacks columns {sorted(missing)}'
495 )
498def verify(data_dir: Path, db: Database) -> Report:
499 """Re-check a migrated install: the SQLite files under *data_dir*
500 against the PostgreSQL *db*. Read-only on both sides; the marker is
501 neither read nor written. ``TableReport.copied`` reports the expected
502 count here, since nothing is copied.
503 """
505 if db.dialect is not Dialect.POSTGRES:
506 raise MigrationError('the migration needs a PostgreSQL target')
507 found = _found_files(data_dir)
508 if not found:
509 raise MigrationError(f'no SQLite database files under {data_dir}')
511 with contextlib.ExitStack() as stack:
512 handles = {
513 name: stack.enter_context(_open_source(path))
514 for name, path in found
515 }
516 sources, seen = _collect_sources(found, handles)
517 with db.connect() as conn:
518 target = _introspect(conn.raw)
519 _check_target(sources, target)
520 order = _fk_order(seen, target.fk_edges)
521 plans = _plan(sources, handles)
522 tables, problems = _verify(
523 db,
524 plans,
525 handles,
526 order,
527 {table: plans[table].matched for table in order},
528 )
529 return Report(
530 files_found=[name for name, _path in found],
531 tables=tables,
532 ok=not problems,
533 problems=problems,
534 )
537def migrate(data_dir: Path, db: Database, *, force: bool = False) -> Report:
538 """Copy the SQLite files under *data_dir* into the PostgreSQL *db*.
540 Raises :class:`MigrationError` when the copy cannot start (already
541 migrated, target not empty, no SQLite files) or when the load fails.
542 With ``force`` the target's tables are truncated first and the copy
543 is redone.
544 """
546 if db.dialect is not Dialect.POSTGRES:
547 raise MigrationError('the migration needs a PostgreSQL target')
548 marker = data_dir / MARKER_NAME
549 if marker.exists() and not force:
550 raise MigrationError(
551 f'{marker} exists: this install was already migrated '
552 '(force=True repeats the copy)'
553 )
554 found = _found_files(data_dir)
555 if not found:
556 raise MigrationError(f'no SQLite database files under {data_dir}')
558 run_migrations(db)
559 with db.connect() as conn:
560 target = _introspect(conn.raw)
561 populated = _populated_tables(conn.raw, target.columns)
562 if populated and not force:
563 raise MigrationError(
564 'target already has rows in: '
565 + ', '.join(populated)
566 + ' (force=True replaces them)'
567 )
569 with contextlib.ExitStack() as stack:
570 handles = {
571 name: stack.enter_context(_open_source(path))
572 for name, path in found
573 }
574 sources, seen = _collect_sources(found, handles)
575 _check_target(sources, target)
577 order = _fk_order(seen, target.fk_edges)
578 plans = _plan(sources, handles)
579 try:
580 with db.transaction() as conn:
581 copied = _load(
582 conn.raw,
583 plans,
584 handles,
585 order,
586 target,
587 sorted(target.columns) if populated else [],
588 )
589 except psycopg.Error as exc:
590 raise MigrationError(
591 f'load failed and was rolled back: {exc}'
592 ) from exc
594 tables, problems = _verify(db, plans, handles, order, copied)
596 report = Report(
597 files_found=[name for name, _path in found],
598 tables=tables,
599 ok=not problems,
600 problems=problems,
601 )
602 if report.ok:
603 marker.write_text(
604 datetime.now(timezone.utc).isoformat() + '\n', encoding='utf-8'
605 )
606 return report
609def format_report(report: Report) -> str:
610 lines = [
611 'Files: ' + (', '.join(report.files_found) or 'none'),
612 f'{"table":<28}{"source":>10}{"skipped":>9}{"target":>10} checksum',
613 ]
614 for t in report.tables:
615 check = 'ok' if t.checksum_ok else 'MISMATCH'
616 lines.append(
617 f'{t.name:<28}{t.source_rows:>10}{t.skipped:>9}'
618 f'{t.target_rows:>10} {check}'
619 )
620 if report.ok:
621 lines.append('Result: OK')
622 else:
623 lines.append('Result: FAILED')
624 lines.extend(f' - {problem}' for problem in report.problems)
625 return '\n'.join(lines)