#!/usr/bin/env python3 """Проверка целостности SQLite в режиме только для чтения.""" from __future__ import annotations import argparse import json import sqlite3 from dataclasses import asdict, dataclass from pathlib import Path from urllib.parse import quote MAGIC = b"SQLite format 3\x00" SUFFIXES = {".db", ".sqlite", ".sqlite3", ".db3"} @dataclass class DatabaseReport: path: str size: int tables: int indexes: int page_size: int page_count: int freelist: int journal_mode: str user_version: int status: str detail: str wal_size: int def is_sqlite(path: Path) -> bool: try: with path.open("rb") as handle: return handle.read(16) == MAGIC except OSError: return False def collect(paths: list[str], recursive: bool) -> tuple[list[Path], list[str]]: files: list[Path] = [] missing: list[str] = [] for raw in paths: path = Path(raw).expanduser() if path.is_dir(): iterator = path.rglob("*") if recursive else path.iterdir() for item in iterator: if item.is_file() and item.suffix.lower() in SUFFIXES: files.append(item) elif path.is_file(): files.append(path) else: missing.append(str(path)) return sorted(set(files)), missing def check(path: Path, quick: bool, foreign_keys: bool) -> DatabaseReport: uri = f"file:{quote(str(path.resolve()))}?mode=ro" size = path.stat().st_size wal = path.with_name(path.name + "-wal") wal_size = wal.stat().st_size if wal.exists() else 0 try: connection = sqlite3.connect(uri, uri=True, timeout=5.0) except sqlite3.Error as error: return DatabaseReport(str(path), size, 0, 0, 0, 0, 0, "-", 0, "error", str(error), wal_size) try: connection.execute("PRAGMA query_only = ON") pragma = "quick_check" if quick else "integrity_check" rows = [str(row[0]) for row in connection.execute(f"PRAGMA {pragma}")] status = "ok" if rows == ["ok"] else "corrupt" detail = "; ".join(rows[:3]) page_size = int(connection.execute("PRAGMA page_size").fetchone()[0]) page_count = int(connection.execute("PRAGMA page_count").fetchone()[0]) freelist = int(connection.execute("PRAGMA freelist_count").fetchone()[0]) journal = str(connection.execute("PRAGMA journal_mode").fetchone()[0]) user_version = int(connection.execute("PRAGMA user_version").fetchone()[0]) tables = int(connection.execute("SELECT count(*) FROM sqlite_master WHERE type = 'table'").fetchone()[0]) indexes = int(connection.execute("SELECT count(*) FROM sqlite_master WHERE type = 'index'").fetchone()[0]) if foreign_keys: violations = connection.execute("PRAGMA foreign_key_check").fetchall() if violations: status = "foreign_keys" detail = f"нарушений внешних ключей: {len(violations)}" return DatabaseReport( path=str(path), size=size, tables=tables, indexes=indexes, page_size=page_size, page_count=page_count, freelist=freelist, journal_mode=journal, user_version=user_version, status=status, detail=detail, wal_size=wal_size, ) except sqlite3.Error as error: return DatabaseReport(str(path), size, 0, 0, 0, 0, 0, "-", 0, "error", str(error), wal_size) finally: connection.close() def human(value: int) -> str: size = float(value) for unit in ("B", "KiB", "MiB", "GiB"): if size < 1024 or unit == "GiB": return f"{size:.1f} {unit}" size /= 1024 return f"{value} B" def main() -> int: parser = argparse.ArgumentParser(description="Проверка SQLite") parser.add_argument("paths", nargs="+", help="Файлы или каталоги с базами") parser.add_argument("--recursive", action="store_true", help="Обходить каталоги рекурсивно") parser.add_argument("--quick", action="store_true", help="Использовать quick_check") parser.add_argument("--foreign-keys", action="store_true", help="Проверить внешние ключи") parser.add_argument("--json", type=Path, help="Сохранить отчёт в JSON") args = parser.parse_args() files, missing = collect(args.paths, args.recursive) reports: list[DatabaseReport] = [] skipped: list[str] = [] for path in files: if not is_sqlite(path): skipped.append(str(path)) continue reports.append(check(path, args.quick, args.foreign_keys)) print(f"{'ФАЙЛ':<48} {'РАЗМЕР':>9} {'ТАБЛИЦ':>7} {'ИНДЕКС':>7} {'FREE%':>6} {'JOURNAL':<9} СТАТУС") print("-" * 110) problems = 0 for report in reports: free_percent = (report.freelist / report.page_count * 100) if report.page_count else 0.0 print( f"{report.path:<48} {human(report.size):>9} {report.tables:>7} {report.indexes:>7} " f"{free_percent:>5.1f}% {report.journal_mode:<9} {report.status}" ) if report.detail and report.status != "ok": print(f" {report.detail}") if report.status != "ok": problems += 1 if report.wal_size: print(f" WAL: {human(report.wal_size)}") if skipped: print("\nПропущены файлы без заголовка SQLite:") for path in skipped: print(f" - {path}") if missing: print("\nНе найдены:") for path in missing: print(f" - {path}") print(f"\nПроверено баз: {len(reports)}, проблемных: {problems}") if args.json: args.json.write_text( json.dumps({"databases": [asdict(item) for item in reports], "skipped": skipped, "missing": missing}, ensure_ascii=False, indent=2) + "\n", encoding="utf-8", ) print(f"JSON сохранён: {args.json}") return 1 if problems or missing else 0 if __name__ == "__main__": raise SystemExit(main())