Skip to content

Bulk asynchronous downloader for multiple file hosts

7.7 KB
Raw Blame
from __future__ import annotations

import asyncio
import logging
import sqlite3
import sys
from pathlib import Path
from typing import LiteralString

from cyberdrop_dl import database
from cyberdrop_dl.database import common
from cyberdrop_dl.database.history import apply_fixes
from cyberdrop_dl.database.schema import CURRENT_VERSION, Version
from cyberdrop_dl.filepath import sanitize_filename
from cyberdrop_dl.logs import setup_console_logging
from cyberdrop_dl.utils import dates

logger = logging.getLogger(__name__)


def _get_table_names(conn: sqlite3.Connection, schema: LiteralString = "main") -> set[str]:
    cursor = conn.execute(f"SELECT name FROM {schema}.sqlite_master WHERE type='table'")  # noqa: S608
    return {r[0] for r in cursor.fetchall()}


def _get_column_names(conn: sqlite3.Connection, table: str, schema: str = "main") -> set[str]:
    rows = conn.execute(f"PRAGMA {schema}.table_info({table})").fetchall()
    return {r[1] for r in rows}


def _get_column_info(conn: sqlite3.Connection, table: str, schema: str = "main") -> list[tuple[str, str | None]]:
    """Return `[(col_name, default_value), ...]` for every column in *table*.

    *default_value* is the literal SQL default from the schema, or `None` when no default is declared."""

    # PRAGMA table_info columns: cid | name | type | notnull | dflt_value | pk
    rows = conn.execute(f"PRAGMA {schema}.table_info({table})").fetchall()
    return [(r[1], r[4]) for r in rows]


def _get_applied_versions(conn: sqlite3.Connection) -> list[str]:
    try:
        rows = conn.execute("SELECT version FROM schema_version").fetchall()
        return [r[0] for r in rows]
    except sqlite3.OperationalError:
        return []


def detect_version(conn: sqlite3.Connection) -> Version | None:
    tables = _get_table_names(conn)

    if "schema_version" in tables:
        versions = _get_applied_versions(conn)
        if versions:
            return Version.parse(max(versions))

    if tables == {"media", "downloads_temp", "coomeno"}:
        return Version(4, 2, 231)

    if tables == {"media", "temp"}:
        return Version(5, 3, 31)

    if tables == {"media", "files", "hash"}:
        media_cols = _get_column_names(conn, "media")
        if "duration" in media_cols:
            return Version(6, 10, 1)  # v6.10.1 - v7.5.0
        return Version(6, 5, 0)

    return None


def _transfer_table(new_conn: sqlite3.Connection, table: str) -> None:
    """Transfer rows from `old.<table>` into the new `<table>`.

    The SELECT list is built dynamically:
    - columns that exist in the old table are selected by name;
    - columns that are only in the current schema are filled with their declared schema default or `NULL`."""

    old_tables = _get_table_names(new_conn, schema="old")
    if table not in old_tables:
        logger.debug("_transfer_table: %s absent in old DB, skipping", table)
        return

    old_cols = _get_column_names(new_conn, table, schema="old")
    new_col_info = _get_column_info(new_conn, table, schema="main")

    col_names: list[str] = []
    select_exprs: list[str] = []

    for name, default in new_col_info:
        col_names.append(f'"{name}"')
        if name in old_cols:
            select_exprs.append(f'"{name}"')
        else:
            # Use the declared default value, or NULL when none is specified.
            fallback = default if default is not None else "NULL"
            select_exprs.append(f'{fallback} AS "{name}"')

    cols_sql = ", ".join(col_names)
    select_sql = ", ".join(select_exprs)

    logger.info("_transfer_table: old.%s -> %s", table, table)
    new_conn.execute(f'INSERT OR IGNORE INTO "{table}" ({cols_sql}) SELECT {select_sql} FROM old."{table}"')  # noqa: S608


def _transfer_media_to_files(new_conn: sqlite3.Connection) -> None:
    """Populate the new `files` table from `old.media`."""

    old_tables = _get_table_names(new_conn, schema="old")
    if "media" not in old_tables:
        logger.debug("_transfer_media_to_files: no old media table, skipping")
        return

    old_media_cols = _get_column_names(new_conn, "media", schema="old")
    file_size_expr = '"file_size"' if "file_size" in old_media_cols else "NULL"

    logger.info("_transfer_media_to_files: old.media -> files")
    # download_path is not absolute in older versions, but we can still use it to populate the folder column.
    new_conn.execute(
        f"""
        INSERT OR IGNORE INTO "files"
            ("folder", "download_filename", "original_filename", "file_size", "referer", "date")
        SELECT
            "download_path",
            "download_filename",
            "original_filename",
            {file_size_expr},
            "referer",
            CAST(strftime('%s', "created_at") AS INTEGER)
        FROM old."media"
        WHERE "download_path" IS NOT NULL
        """  # noqa: S608
    )


# TransferManager

# Tables whose rows are transferred column-by-column, in dependency order.
_DIRECT_TRANSFER_TABLES = ["media", "hash", "files"]

_SKIP_TABLES = {"schema_version"}


def run(db_path: Path, *, force: bool = False) -> None:
    if not db_path.exists():
        raise FileNotFoundError(f"Database not found: {db_path}")

    probe = sqlite3.connect(db_path)
    try:
        old_version = detect_version(probe)
    finally:
        probe.close()

    logger.info("Detected schema version: %s", old_version)

    if not force and old_version == CURRENT_VERSION:
        logger.info(
            "Database is already at the latest schema (%s). Use --force to re-run.",
            CURRENT_VERSION,
        )
        return

    now = sanitize_filename(str(dates.now()))
    new_path = db_path.with_name(f"{db_path.stem}_{CURRENT_VERSION}_{now}{db_path.suffix}")

    logger.debug("Creating new database at: %s", new_path)
    _create_new_database(new_path)
    new_conn = sqlite3.connect(new_path)
    try:
        new_conn.execute("PRAGMA foreign_keys=OFF")
        new_conn.execute(f"ATTACH DATABASE '{db_path}' AS old")
        try:
            _transfer(new_conn)
        except Exception:
            new_conn.rollback()
            new_conn.close()
            logger.exception("Transfer failed - original database has NOT been changed")
            new_path.unlink()
            raise
        else:
            new_conn.commit()
    finally:
        new_conn.close()

    _apply_fixes(new_path)
    backup_path = db_path.with_name(f"{db_path.stem}_{old_version}_{now}.backup{db_path.suffix}")
    db_path.rename(backup_path)
    new_path.rename(db_path)
    logger.info("Transfer complete", extra={"color": "green"})
    logger.info("Backup at: '%s'", backup_path)
    logger.info("New schema version: %s", CURRENT_VERSION)


def _create_new_database(path: Path) -> None:
    async def connect() -> None:
        async with database.Database(path, ignore_history=True):
            pass

    asyncio.run(connect())


def _apply_fixes(db_path: Path) -> None:

    async def fix() -> None:
        async with common.connect(db_path) as conn:
            await apply_fixes(conn)

    asyncio.run(fix())


def _transfer(new_conn: sqlite3.Connection) -> None:
    old_tables = _get_table_names(new_conn, schema="old")
    new_tables = _get_table_names(new_conn, schema="main")

    for table in _DIRECT_TRANSFER_TABLES:
        if table not in new_tables or table in _SKIP_TABLES:
            continue

        if table == "files" and "files" not in old_tables:
            _transfer_media_to_files(new_conn)
        else:
            _transfer_table(new_conn, table)

    # Transfer any remaining new tables not listed
    covered = set(_DIRECT_TRANSFER_TABLES) | _SKIP_TABLES
    for table in sorted(new_tables - covered):
        _transfer_table(new_conn, table)


if __name__ == "__main__":
    with setup_console_logging():
        run(Path(sys.argv[1]))