Bulk asynchronous downloader for multiple file hosts
Python asynciobunkrcyberdropdownloaderdropboxfilestergofilegoogle-drivemediafiremegamega-nzonedrivepatreonpawchivepinterestpixeldraintransfer-ittwitchtwittervk
from __future__ import annotations
import dataclasses
import functools
import logging
import os
from pathlib import Path
from typing import TYPE_CHECKING, cast
from cyberdrop_dl import aio
from .common import Table
from .definitions import CREATE_FILES, CREATE_HASH, CREATE_HASH_INDEX
if TYPE_CHECKING:
import aiosqlite
from cyberdrop_dl.url_objects import AbsoluteHttpURL
logger = logging.getLogger(__name__)
_PATH_EXISTS = "path_exists"
"""Name of the custom SQLite function registered by `HashTable.prune_missing_files`"""
_WHERE_MISSING = f"WHERE {_PATH_EXISTS}(folder, download_filename) = 0"
@dataclasses.dataclass(slots=True)
class PruneStats:
hash_rows: int = 0
file_rows: int = 0
dry_run: bool = False
@dataclasses.dataclass(slots=True)
class HashTable(Table, name="hash"):
cwd: Path = dataclasses.field(init=False, default_factory=lambda: Path.cwd().expanduser().resolve())
async def create(self) -> None:
async with self.db.writer() as db_conn:
for query in (CREATE_FILES, CREATE_HASH, CREATE_HASH_INDEX):
await db_conn.execute(query)
await db_conn.commit()
async def get_file_hash_exists(self, path: Path | str, hash_type: str) -> str | None:
query = "SELECT hash FROM hash WHERE folder= ? AND download_filename= ? AND hash_type= ? AND hash IS NOT NULL LIMIT 1;"
path = self.cwd / path
folder = str(path.parent)
filename = path.name
async with self.db.reader() as db_conn:
cursor = await db_conn.execute(query, (folder, filename, hash_type))
if row := await cursor.fetchone():
return row["hash"]
async def get_files_with_hash_matches(
self,
hash_value: str,
size: int,
hash_algo: str | None = None,
) -> list[aiosqlite.Row]:
if hash_algo:
query = """
SELECT
files.folder,
files.download_filename,
files.date
FROM
hash
JOIN files ON hash.folder = files.folder
AND hash.download_filename = files.download_filename
WHERE
hash.hash = ?
AND files.file_size = ?
AND hash.hash_type = ?;
"""
else:
query = """
SELECT
files.folder,
files.download_filename
FROM
hash
JOIN files ON hash.folder = files.folder
AND hash.download_filename = files.download_filename
WHERE
hash.hash = ?
AND files.file_size = ?
AND hash.hash_type = ?;
"""
async with self.db.reader() as db_conn:
rows = await db_conn.execute_fetchall(query, (hash_value, size, hash_algo))
return cast("list[aiosqlite.Row]", rows)
async def check_hash_exists(self, hash_type: str, hash_value: str) -> bool:
if self.ignore_history:
return False
query = "SELECT 1 FROM hash WHERE hash.hash_type = ? AND hash.hash = ? LIMIT 1;"
async with self.db.reader() as db_conn:
cursor = await db_conn.execute(query, (hash_type, hash_value))
return await cursor.fetchone() is not None
async def prune_missing_files(self, *, dry_run: bool = False) -> PruneStats:
"""Delete every `hash` and `files` row whose file is no longer on disk."""
stats = PruneStats(dry_run=dry_run)
try:
if dry_run:
async with self.db.reader() as db_conn:
await _register_path_exists(db_conn)
stats.hash_rows = await _count_missing(db_conn, "hash")
stats.file_rows = await _count_missing(db_conn, "files")
return stats
# Foreign keys are never enabled at runtime, so deletes do not cascade. `hash` goes first
# so a failure can never leave a hash row pointing at a files row that is already gone
async with self.db.writer() as db_conn:
await _register_path_exists(db_conn)
stats.hash_rows = await _delete_missing(db_conn, "hash")
stats.file_rows = await _delete_missing(db_conn, "files")
await db_conn.commit()
return stats
finally:
# The answers are only good for as long as this one prune. Do not hold on to them, or to
# the memory, once it is over
_path_exists_inner.cache_clear()
async def insert_or_update_hash_db(
self,
hash_value: str,
hash_algo: str,
file: Path | str,
original_filename: str | None,
referer: AbsoluteHttpURL | None,
) -> None:
await self.insert_or_update_hashes(hash_value, hash_algo, file)
await self.insert_or_update_file(original_filename, referer, file)
async def insert_or_update_hashes(self, hash_value: str, hash_type: str, file: Path | str) -> None:
query = """
INSERT INTO hash (
hash, hash_type, folder, download_filename
)
VALUES
(?, ?, ?, ?) ON CONFLICT(
download_filename, folder, hash_type
) DO
UPDATE
SET
hash = ?;
"""
full_path = self.cwd / file
download_filename = full_path.name
folder = str(full_path.parent)
async with self.db.writer() as db_conn:
await db_conn.execute(query, (hash_value, hash_type, folder, download_filename, hash_value))
await db_conn.commit()
async def insert_or_update_file(
self,
original_filename: str | None,
referer: AbsoluteHttpURL | str | None,
file: Path | str,
) -> None:
query = """
INSERT INTO files (
folder, original_filename, download_filename,
file_size, referer, date
)
VALUES
(?, ?, ?, ?, ?, ?) ON CONFLICT(download_filename, folder) DO
UPDATE
SET
original_filename = ?,
file_size = ?,
referer = ?,
date = ?;
"""
referer_ = str(referer) if referer else None
full_path = self.cwd / file
download_filename = full_path.name
folder = str(full_path.parent)
stat = await aio.stat(full_path)
file_size = stat.st_size
file_date = int(stat.st_mtime)
async with self.db.writer() as db_conn:
await db_conn.execute(
query,
(
folder,
original_filename,
download_filename,
file_size,
referer_,
file_date,
original_filename,
file_size,
referer_,
file_date,
),
)
await db_conn.commit()
def _path_exists(folder: str, download_filename: str) -> bool:
# `os.path` instead of `pathlib` because a large library means hundreds of thousands of these,
# and building a Path for each one is not free
return _path_exists_inner(os.path.join(folder, download_filename)) # noqa: PTH118
@functools.lru_cache(maxsize=100_000)
def _path_exists_inner(path: str) -> bool:
# Every hash of a file shares its path, so the same path is looked up once per `hash` row plus
# once for its `files` row. Bounded because a large library can hold millions of rows; the worst
# an eviction costs us is one repeated system call
try:
_ = os.stat(path) # noqa: PTH116
except FileNotFoundError:
return False
except (OSError, ValueError):
# `os.path.exists` reports all of these as "missing". Here they mean we could not find out
# (no permission, an unreachable network drive), which is not a reason to delete the row
return True
return True
async def _register_path_exists(db_conn: aiosqlite.Connection) -> None:
# Deterministic does not memoize calls with different arguments, that is what the cache above is
# for. It lets SQLite treat the call as a pure expression, which is what we want here
await db_conn.create_function(_PATH_EXISTS, 2, _path_exists, deterministic=True)
async def _delete_missing(db_conn: aiosqlite.Connection, table: str) -> int:
cursor = await db_conn.execute(f"DELETE FROM {table} {_WHERE_MISSING};") # noqa: S608
return cursor.rowcount
async def _count_missing(db_conn: aiosqlite.Connection, table: str) -> int:
cursor = await db_conn.execute(f"SELECT COUNT(*) AS count FROM {table} {_WHERE_MISSING};") # noqa: S608
row = await cursor.fetchone()
assert row is not None
return row["count"]