Files
2026-11-16 20:45:22 +08:00

598 lines
25 KiB
Python

from __future__ import annotations
import re
from pathlib import Path
from typing import Any
from pixiv_app.core.auth import SessionStore
from pixiv_app.core.downloader import download_user_works, fetch_user_profile, fetch_user_work_ids
from pixiv_app.core.library_importer import LegacyDownloadImporter
from pixiv_app.core.library import DEFAULT_LIBRARY_DB, PixivLibrary
from pixiv_app.core.paths import app_session_file
from pixiv_app.core.thumbnails import DEFAULT_THUMBNAIL_DIR, ThumbnailCache
class LocalGalleryService:
def __init__(
self,
*,
db_path: str | Path = DEFAULT_LIBRARY_DB,
thumbnail_dir: str | Path = DEFAULT_THUMBNAIL_DIR,
) -> None:
self.db_path = Path(db_path)
library = PixivLibrary(self.db_path)
library.close()
self.thumbnail_cache = ThumbnailCache(db_path=self.db_path, cache_dir=thumbnail_dir)
def list_artworks(
self,
*,
query: str = "",
search_scope: str = "all",
tag: str = "",
artist_id: int | None = None,
artwork_type: str = "",
include_restricted: bool = True,
limit: int = 60,
offset: int = 0,
ensure_thumbnails: bool = True,
) -> dict[str, Any]:
limit = max(1, min(int(limit), 200))
offset = max(0, int(offset))
library = PixivLibrary(self.db_path)
try:
where: list[str] = []
params: list[Any] = []
joins = """
LEFT JOIN artists ar ON ar.artist_id = a.artist_id
LEFT JOIN followed_artists fa ON fa.artist_id = a.artist_id AND fa.enabled = 1
LEFT JOIN thumbnail_cache tc ON tc.artwork_id = a.artwork_id AND tc.page_index = 0
LEFT JOIN artwork_files af ON af.artwork_id = a.artwork_id AND af.page_index = 0
LEFT JOIN artwork_ocr_texts ocr ON ocr.artwork_id = a.artwork_id
"""
if query:
like = f"%{query}%"
if search_scope == "ocr":
where.append("(ocr.status = 'done' AND ocr.ocr_text LIKE ?)")
params.append(like)
elif search_scope == "title":
where.append("(a.title LIKE ? OR a.caption LIKE ? OR ar.name LIKE ?)")
params.extend([like, like, like])
elif search_scope == "tags":
joins += """
LEFT JOIN artwork_tags at_search ON at_search.artwork_id = a.artwork_id
LEFT JOIN tags t_search ON t_search.tag_id = at_search.tag_id
"""
where.append("t_search.name LIKE ?")
params.append(like)
else:
joins += """
LEFT JOIN artwork_tags at_search ON at_search.artwork_id = a.artwork_id
LEFT JOIN tags t_search ON t_search.tag_id = at_search.tag_id
"""
where.append(
"(a.title LIKE ? OR a.caption LIKE ? OR ar.name LIKE ? OR t_search.name LIKE ? "
"OR (ocr.status = 'done' AND ocr.ocr_text LIKE ?))"
)
params.extend([like, like, like, like, like])
if tag:
joins += """
JOIN artwork_tags at_filter ON at_filter.artwork_id = a.artwork_id
JOIN tags t_filter ON t_filter.tag_id = at_filter.tag_id
"""
where.append("t_filter.name = ?")
params.append(tag)
if artist_id is not None:
where.append("a.artist_id = ?")
params.append(int(artist_id))
if artwork_type:
where.append("a.type = ?")
params.append(artwork_type)
else:
where.append("a.type IN ('illust', 'manga')")
if not include_restricted:
where.append("a.x_restrict = 0")
where_sql = "WHERE " + " AND ".join(where) if where else ""
total = library.connection.execute(
f"SELECT COUNT(DISTINCT a.artwork_id) AS total FROM artworks a {joins} {where_sql}",
params,
).fetchone()["total"]
rows = library.connection.execute(
f"""
SELECT
a.artwork_id,
a.type,
a.title,
a.artist_id,
ar.name AS artist_name,
ar.account AS artist_account,
a.page_count,
a.bookmark_count,
a.like_count,
a.view_count,
a.x_restrict,
a.ai_type,
a.thumbnail_url,
CASE WHEN fav.artwork_id IS NULL THEN 0 ELSE 1 END AS is_favorite,
CASE WHEN fa.artist_id IS NULL THEN 0 ELSE 1 END AS is_followed,
COALESCE(tc.thumbnail_path, '') AS thumbnail_path,
COALESCE(af.file_path, '') AS primary_file_path,
CASE
WHEN ocr.status = 'done' AND ocr.ocr_text LIKE ? THEN 'ocr'
ELSE ''
END AS matched_by,
a.updated_at
FROM artworks a
{joins}
LEFT JOIN favorites fav ON fav.artwork_id = a.artwork_id
{where_sql}
GROUP BY a.artwork_id
ORDER BY a.updated_at DESC, a.artwork_id DESC
LIMIT ? OFFSET ?
""",
[f"%{query}%" if query else "", *params, limit, offset],
).fetchall()
items = [dict(row) for row in rows]
if ensure_thumbnails:
self._ensure_list_thumbnails(items)
items = self._refresh_thumbnail_paths(library, items)
return {"total": int(total), "limit": limit, "offset": offset, "items": items}
finally:
library.close()
def get_artwork(self, artwork_id: int, *, ensure_thumbnails: bool = True) -> dict[str, Any] | None:
if ensure_thumbnails:
self.thumbnail_cache.ensure_for_artwork(artwork_id)
library = PixivLibrary(self.db_path)
try:
row = library.connection.execute(
"""
SELECT a.*, ar.name AS artist_name, ar.account AS artist_account
, CASE WHEN fav.artwork_id IS NULL THEN 0 ELSE 1 END AS is_favorite
, CASE WHEN fa.artist_id IS NULL THEN 0 ELSE 1 END AS is_followed
FROM artworks a
LEFT JOIN artists ar ON ar.artist_id = a.artist_id
LEFT JOIN favorites fav ON fav.artwork_id = a.artwork_id
LEFT JOIN followed_artists fa ON fa.artist_id = a.artist_id AND fa.enabled = 1
WHERE a.artwork_id = ?
""",
(artwork_id,),
).fetchone()
if row is None:
return None
files = library.connection.execute(
"""
SELECT f.*, tc.thumbnail_path, tc.width AS thumbnail_width, tc.height AS thumbnail_height
FROM artwork_files f
LEFT JOIN thumbnail_cache tc
ON tc.artwork_id = f.artwork_id AND tc.page_index = f.page_index
WHERE f.artwork_id = ?
ORDER BY f.page_index ASC
""",
(artwork_id,),
).fetchall()
tags = library.connection.execute(
"""
SELECT t.name, t.translated_name
FROM tags t
JOIN artwork_tags at ON at.tag_id = t.tag_id
WHERE at.artwork_id = ?
ORDER BY t.name ASC
""",
(artwork_id,),
).fetchall()
history = library.connection.execute(
"""
SELECT status, downloaded_pages, skipped_pages, failed_pages, message, finished_at
FROM download_history
WHERE artwork_id = ?
ORDER BY finished_at DESC
LIMIT 20
""",
(artwork_id,),
).fetchall()
return {
"artwork": dict(row),
"files": [dict(item) for item in files],
"tags": [dict(item) for item in tags],
"history": [dict(item) for item in history],
}
finally:
library.close()
def list_tags(self, *, query: str = "", limit: int = 100) -> list[dict[str, Any]]:
library = PixivLibrary(self.db_path)
try:
params: list[Any] = []
where = ""
if query:
where = "WHERE t.name LIKE ? OR t.translated_name LIKE ?"
params.extend([f"%{query}%", f"%{query}%"])
rows = library.connection.execute(
f"""
SELECT t.name, t.translated_name, COUNT(at.artwork_id) AS artwork_count
FROM tags t
LEFT JOIN artwork_tags at ON at.tag_id = t.tag_id
{where}
GROUP BY t.tag_id
ORDER BY artwork_count DESC, t.name ASC
LIMIT ?
""",
[*params, max(1, min(int(limit), 500))],
).fetchall()
return [dict(row) for row in rows]
finally:
library.close()
def list_artists(self, *, query: str = "", limit: int = 100) -> list[dict[str, Any]]:
library = PixivLibrary(self.db_path)
try:
params: list[Any] = []
where = ""
if query:
where = "WHERE ar.name LIKE ? OR ar.account LIKE ?"
params.extend([f"%{query}%", f"%{query}%"])
rows = library.connection.execute(
f"""
SELECT ar.artist_id, ar.name, ar.account, COUNT(a.artwork_id) AS artwork_count
FROM artists ar
LEFT JOIN artworks a ON a.artist_id = ar.artist_id
{where}
GROUP BY ar.artist_id
ORDER BY
CASE WHEN COALESCE(NULLIF(ar.name, ''), NULLIF(ar.account, '')) = '' THEN 1 ELSE 0 END,
COALESCE(NULLIF(ar.name, ''), NULLIF(ar.account, ''), CAST(ar.artist_id AS TEXT)) ASC,
artwork_count DESC
LIMIT ?
""",
[*params, max(1, min(int(limit), 500))],
).fetchall()
return [dict(row) for row in rows]
finally:
library.close()
def get_artist(self, artist_id: int, *, limit: int = 60, offset: int = 0) -> dict[str, Any] | None:
library = PixivLibrary(self.db_path)
try:
row = library.connection.execute(
"""
SELECT ar.*, COUNT(a.artwork_id) AS artwork_count,
CASE WHEN fa.enabled = 1 THEN 1 ELSE 0 END AS is_followed,
fa.last_synced_at, fa.last_error
FROM artists ar
LEFT JOIN artworks a ON a.artist_id = ar.artist_id
LEFT JOIN followed_artists fa ON fa.artist_id = ar.artist_id
WHERE ar.artist_id = ?
GROUP BY ar.artist_id
""",
(artist_id,),
).fetchone()
if row is None:
return None
return {
"artist": dict(row),
"artworks": self.list_artworks(artist_id=artist_id, limit=limit, offset=offset),
}
finally:
library.close()
def list_favorites(self, *, limit: int = 60, offset: int = 0) -> dict[str, Any]:
library = PixivLibrary(self.db_path)
try:
total = library.connection.execute(
"""
SELECT COUNT(*) AS total
FROM favorites fav
JOIN artworks a ON a.artwork_id = fav.artwork_id
WHERE a.type IN ('illust', 'manga')
"""
).fetchone()["total"]
rows = library.connection.execute(
"""
SELECT
a.artwork_id, a.type, a.title, a.artist_id, ar.name AS artist_name,
ar.account AS artist_account,
a.page_count, a.bookmark_count, a.like_count, a.view_count,
a.x_restrict, a.ai_type, a.thumbnail_url,
1 AS is_favorite,
CASE WHEN fa.artist_id IS NULL THEN 0 ELSE 1 END AS is_followed,
COALESCE(tc.thumbnail_path, '') AS thumbnail_path,
COALESCE(af.file_path, '') AS primary_file_path,
fav.created_at AS favorite_at
FROM favorites fav
JOIN artworks a ON a.artwork_id = fav.artwork_id
LEFT JOIN artists ar ON ar.artist_id = a.artist_id
LEFT JOIN followed_artists fa ON fa.artist_id = a.artist_id AND fa.enabled = 1
LEFT JOIN thumbnail_cache tc ON tc.artwork_id = a.artwork_id AND tc.page_index = 0
LEFT JOIN artwork_files af ON af.artwork_id = a.artwork_id AND af.page_index = 0
WHERE a.type IN ('illust', 'manga')
ORDER BY fav.created_at DESC
LIMIT ? OFFSET ?
""",
(max(1, min(int(limit), 200)), max(0, int(offset))),
).fetchall()
items = [dict(row) for row in rows]
self._ensure_list_thumbnails(items)
items = self._refresh_thumbnail_paths(library, items)
return {"total": int(total), "limit": limit, "offset": offset, "items": items}
finally:
library.close()
def set_favorite(self, artwork_id: int, favorite: bool, note: str = "") -> dict[str, Any]:
library = PixivLibrary(self.db_path)
try:
library.set_favorite(artwork_id, favorite, note)
return {"artwork_id": artwork_id, "favorite": favorite}
finally:
library.close()
def list_followed_artists(self) -> list[dict[str, Any]]:
library = PixivLibrary(self.db_path)
try:
rows = library.connection.execute(
"""
SELECT fa.artist_id, COALESCE(NULLIF(fa.name, ''), NULLIF(ar.name, ''), '') AS name,
COALESCE(NULLIF(ar.account, ''), '') AS account,
fa.enabled, fa.last_synced_at, fa.last_error,
COUNT(a.artwork_id) AS artwork_count
FROM followed_artists fa
LEFT JOIN artists ar ON ar.artist_id = fa.artist_id
LEFT JOIN artworks a ON a.artist_id = fa.artist_id
WHERE fa.enabled = 1
GROUP BY fa.artist_id
ORDER BY
CASE WHEN COALESCE(NULLIF(fa.name, ''), NULLIF(ar.name, ''), NULLIF(ar.account, '')) = '' THEN 1 ELSE 0 END,
COALESCE(NULLIF(fa.name, ''), NULLIF(ar.name, ''), NULLIF(ar.account, ''), CAST(fa.artist_id AS TEXT)) ASC,
fa.artist_id ASC
"""
).fetchall()
return [dict(row) for row in rows]
finally:
library.close()
def set_followed_artist(self, artist_ref: int | str, followed: bool, name: str = "") -> dict[str, Any]:
artist_id = self._resolve_artist_id(artist_ref)
clean_name = self._clean_name(name)
account = ""
if followed and not clean_name:
try:
profile = fetch_user_profile(
artist_id,
cookie=str(SessionStore(app_session_file()).load().get("cookie", "")).strip(),
)
profile_info = profile.get("profile") if isinstance(profile.get("profile"), dict) else {}
clean_name = self._clean_name(
profile.get("name")
or profile.get("userName")
or profile.get("displayName")
or profile_info.get("name")
)
account = self._clean_name(profile.get("account") or profile.get("userAccount"))
except Exception:
clean_name = ""
library = PixivLibrary(self.db_path)
try:
if clean_name or account:
library.upsert_artist_profile(artist_id, name=clean_name, account=account)
library.set_followed_artist(artist_id, followed, clean_name)
return {
"artist_id": artist_id,
"followed": followed,
"name": clean_name,
"account": account,
"cloud_synced": False,
}
finally:
library.close()
def _resolve_artist_id(self, artist_ref: int | str) -> int:
text = str(artist_ref).strip()
if not text:
raise ValueError("Please enter a Pixiv artist ID, profile URL, or synced artist name.")
if text.lower().startswith("artist_") and text[7:].isdigit():
return int(text[7:])
match = re.search(r"/users/(\d+)", text)
if match:
return int(match.group(1))
if text.isdigit():
return int(text)
lookup = text.lstrip("@")
library = PixivLibrary(self.db_path)
try:
rows = [
dict(row)
for row in library.connection.execute(
"""
SELECT artist_id, name, account
FROM artists
WHERE lower(name) = lower(?) OR lower(account) = lower(?)
ORDER BY
CASE
WHEN lower(account) = lower(?) THEN 0
WHEN lower(name) = lower(?) THEN 1
ELSE 2
END,
artist_id ASC
""",
(lookup, lookup, lookup, lookup),
).fetchall()
]
finally:
library.close()
if len(rows) == 1:
return int(rows[0]["artist_id"])
if not rows:
raise ValueError("Could not resolve Pixiv artist ID from that name. Enter an ID/profile URL or sync the artist first.")
choices = ", ".join(str(int(row["artist_id"])) for row in rows[:5])
raise ValueError(f"Artist name matched multiple Pixiv users; use an ID instead: {choices}")
@staticmethod
def _clean_name(value: Any) -> str:
text = str(value or "").strip()
return "" if text.lower() in {"none", "null", "undefined"} else text
def scan_legacy_downloads(self, *, root: str | Path = "Sakura_Downloads") -> dict[str, Any]:
summary = LegacyDownloadImporter(db_path=self.db_path).scan(root)
self.thumbnail_cache.build_missing(limit=500)
return summary.__dict__
def sync_followed_artists(
self,
*,
cookie: str = "",
max_new_per_artist: int = 20,
download: bool = True,
save_root: str | Path = "Sakura_Downloads",
) -> dict[str, Any]:
if not cookie:
cookie = str(SessionStore(app_session_file()).load().get("cookie", "")).strip()
if not cookie:
raise ValueError("No Pixiv cookie available. Save a session in the GUI first or pass cookie explicitly.")
library = PixivLibrary(self.db_path)
try:
followed = library.connection.execute(
"""
SELECT fa.artist_id,
COALESCE(NULLIF(fa.name, ''), NULLIF(ar.name, ''), NULLIF(ar.account, ''), '') AS name,
COALESCE(NULLIF(ar.account, ''), '') AS account
FROM followed_artists fa
LEFT JOIN artists ar ON ar.artist_id = fa.artist_id
WHERE fa.enabled = 1
ORDER BY fa.artist_id
"""
).fetchall()
finally:
library.close()
results: list[dict[str, Any]] = []
for row in followed:
artist_id = int(row["artist_id"])
artist_name = self._clean_name(row["name"])
artist_account = self._clean_name(row["account"])
library = PixivLibrary(self.db_path)
try:
existing = {
int(item["artwork_id"])
for item in library.connection.execute(
"SELECT artwork_id FROM artworks WHERE artist_id = ? AND type IN ('illust', 'manga')",
(artist_id,),
).fetchall()
}
finally:
library.close()
try:
_, work_ids = fetch_user_work_ids(user_id=artist_id, cookie=cookie)
new_ids = [work_id for work_id in work_ids if work_id not in existing][: max(0, int(max_new_per_artist))]
downloaded = 0
if download and new_ids:
_, download_results = download_user_works(
user_id=artist_id,
cookie=cookie,
max_workers=3,
request_delay=0.2,
save_root=save_root,
work_ids=new_ids,
)
downloaded = len([item for item in download_results if item.ok])
library = PixivLibrary(self.db_path)
try:
library.mark_artist_synced(artist_id)
finally:
library.close()
cloud_synced = False
cloud_alerts = 0
results.append(
{
"artist_id": artist_id,
"remote_count": len(work_ids),
"new_count": len(new_ids),
"downloaded": downloaded,
"cloud_synced": cloud_synced,
"alerts": cloud_alerts,
"error": "",
}
)
except Exception as exc:
library = PixivLibrary(self.db_path)
try:
library.mark_artist_synced(artist_id, error=str(exc))
finally:
library.close()
results.append({"artist_id": artist_id, "remote_count": 0, "new_count": 0, "downloaded": 0, "alerts": 0, "error": str(exc)})
return {"artists": results}
def build_missing_thumbnails(self, *, limit: int = 200) -> list[dict[str, Any]]:
return [item.__dict__ for item in self.thumbnail_cache.build_missing(limit=limit)]
def get_thumbnail_path(self, artwork_id: int, *, page_index: int = 0) -> Path | None:
self.thumbnail_cache.ensure_for_artwork(artwork_id)
library = PixivLibrary(self.db_path)
try:
row = library.connection.execute(
"""
SELECT thumbnail_path
FROM thumbnail_cache
WHERE artwork_id = ? AND page_index = ?
""",
(artwork_id, page_index),
).fetchone()
if row is None:
return None
path = Path(str(row["thumbnail_path"]))
return path if path.exists() else None
finally:
library.close()
def get_source_file_path(self, artwork_id: int, *, page_index: int = 0) -> Path | None:
library = PixivLibrary(self.db_path)
try:
row = library.connection.execute(
"""
SELECT file_path
FROM artwork_files
WHERE artwork_id = ? AND page_index = ?
""",
(artwork_id, page_index),
).fetchone()
if row is None:
return None
path = Path(str(row["file_path"]))
return path if path.exists() else None
finally:
library.close()
def _ensure_list_thumbnails(self, items: list[dict[str, Any]]) -> None:
for item in items:
if item.get("thumbnail_path"):
continue
if not item.get("primary_file_path"):
continue
self.thumbnail_cache.ensure_for_artwork(int(item["artwork_id"]))
def _refresh_thumbnail_paths(self, library: PixivLibrary, items: list[dict[str, Any]]) -> list[dict[str, Any]]:
artwork_ids = [int(item["artwork_id"]) for item in items]
if not artwork_ids:
return items
placeholders = ",".join("?" for _ in artwork_ids)
rows = library.connection.execute(
f"""
SELECT artwork_id, thumbnail_path
FROM thumbnail_cache
WHERE page_index = 0 AND artwork_id IN ({placeholders})
""",
artwork_ids,
).fetchall()
thumbs = {int(row["artwork_id"]): str(row["thumbnail_path"]) for row in rows}
for item in items:
item["thumbnail_path"] = thumbs.get(int(item["artwork_id"]), item.get("thumbnail_path", ""))
return items