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

1"""One-shot copy of an existing SQLite install into the PostgreSQL schema. 

2 

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. 

9 

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

15 

16from __future__ import annotations 

17 

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 

26 

27import psycopg 

28from psycopg import sql 

29 

30from downtify.db.config import GROUP_FILES, Dialect 

31from downtify.db.core import Database 

32from downtify.db.migrations.runner import run_migrations 

33 

34MARKER_NAME = 'MIGRATED_TO_POSTGRES' 

35BATCH_SIZE = 5000 

36_MIGRATION_TABLE = 'schema_migrations' 

37 

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} 

44 

45 

46class MigrationError(RuntimeError): 

47 """The migration cannot start, or the load failed and was rolled back.""" 

48 

49 

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 

59 

60 

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) 

67 

68 

69@dataclass(frozen=True) 

70class _Source: 

71 file_name: str 

72 table: str 

73 columns: tuple[str, ...] 

74 pk: tuple[str, ...] 

75 rowid: bool 

76 

77 

78@dataclass(frozen=True) 

79class _Plan: 

80 source: _Source 

81 where: str 

82 source_rows: int 

83 matched: int 

84 

85 @property 

86 def skipped(self) -> int: 

87 return self.source_rows - self.matched 

88 

89 

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

95 

96 

97def _quote(name: str) -> str: 

98 return '"' + name.replace('"', '""') + '"' 

99 

100 

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 

108 

109 

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

118 

119 

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] 

126 

127 

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) 

140 

141 

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) 

168 

169 

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 

184 

185 

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 

209 

210 

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) 

217 

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 

241 

242 

243def _select_cols(columns: tuple[str, ...]) -> str: 

244 return ', '.join(_quote(c) for c in columns) 

245 

246 

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 ) 

262 

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 

290 

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 ) 

304 

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 

315 

316 

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

323 

324 

325_MODULUS = 1 << 256 

326 

327 

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 

336 

337 

338def _digest_of(value: Any) -> int: 

339 raw = hashlib.sha256(repr(value).encode('utf-8')).digest() 

340 return int.from_bytes(raw, 'big') 

341 

342 

343@dataclass 

344class _Digest: 

345 """Row count and the sum of the per-row digests, mod 2**256. 

346 

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

350 

351 count: int = 0 

352 total: int = 0 

353 

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 

358 

359 

360def _fold(rows: Iterable[Iterable[Any]]) -> _Digest: 

361 digest = _Digest() 

362 for row in rows: 

363 digest.add(row) 

364 return digest 

365 

366 

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 

376 

377 

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 

388 

389 

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 

396 

397 

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. 

404 

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 

413 

414 

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

435 

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 

465 

466 

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 

483 

484 

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 ) 

496 

497 

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

504 

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

510 

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 ) 

535 

536 

537def migrate(data_dir: Path, db: Database, *, force: bool = False) -> Report: 

538 """Copy the SQLite files under *data_dir* into the PostgreSQL *db*. 

539 

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

545 

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

557 

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 ) 

568 

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) 

576 

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 

593 

594 tables, problems = _verify(db, plans, handles, order, copied) 

595 

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 

607 

608 

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)