This repository has been archived on 2026-11-11. You can view files and clone it. You cannot open issues or pull requests or push a commit.
Files

228 lines
7.1 KiB
Python

import sqlite3
from . import config
SCHEMA = """
CREATE TABLE IF NOT EXISTS videos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url TEXT UNIQUE NOT NULL,
site TEXT,
title TEXT,
description TEXT,
uploader TEXT,
tags TEXT DEFAULT '',
categories TEXT DEFAULT '',
duration INTEGER,
width INTEGER,
height INTEGER,
size_bytes INTEGER,
quality TEXT,
status TEXT DEFAULT 'queued',
error TEXT,
download_path TEXT,
optimized_path TEXT,
poster_path TEXT,
thumb_path TEXT,
preview_path TEXT,
published_at TEXT,
added_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE VIRTUAL TABLE IF NOT EXISTS videos_fts USING fts5(
title, description, uploader, tags, categories,
content='videos', content_rowid='id',
tokenize='porter unicode61'
);
CREATE TRIGGER IF NOT EXISTS videos_ai AFTER INSERT ON videos BEGIN
INSERT INTO videos_fts(rowid, title, description, uploader, tags, categories)
VALUES (new.id, new.title, new.description, new.uploader, new.tags, new.categories);
END;
CREATE TRIGGER IF NOT EXISTS videos_ad AFTER DELETE ON videos BEGIN
INSERT INTO videos_fts(videos_fts, rowid, title, description, uploader, tags, categories)
VALUES ('delete', old.id, old.title, old.description, old.uploader, old.tags, old.categories);
END;
CREATE TRIGGER IF NOT EXISTS videos_au AFTER UPDATE ON videos BEGIN
INSERT INTO videos_fts(videos_fts, rowid, title, description, uploader, tags, categories)
VALUES ('delete', old.id, old.title, old.description, old.uploader, old.tags, old.categories);
INSERT INTO videos_fts(rowid, title, description, uploader, tags, categories)
VALUES (new.id, new.title, new.description, new.uploader, new.tags, new.categories);
END;
CREATE INDEX IF NOT EXISTS idx_videos_status ON videos(status);
CREATE INDEX IF NOT EXISTS idx_videos_site ON videos(site);
CREATE INDEX IF NOT EXISTS idx_videos_updated ON videos(updated_at DESC);
"""
def connect():
conn = sqlite3.connect(config.DB_PATH)
conn.row_factory = sqlite3.Row
conn.execute("PRAGMA foreign_keys=ON")
conn.execute("PRAGMA journal_mode=WAL")
return conn
def init():
conn = connect()
conn.executescript(SCHEMA)
conn.commit()
conn.close()
def add_job(url):
conn = connect()
conn.execute("INSERT OR IGNORE INTO videos(url) VALUES (?)", (url,))
conn.commit()
row = conn.execute("SELECT id FROM videos WHERE url=?", (url,)).fetchone()
conn.close()
return row["id"]
def get(id):
conn = connect()
row = conn.execute("SELECT * FROM videos WHERE id=?", (id,)).fetchone()
conn.close()
return dict(row) if row else None
def set_status(id, status, error=None):
conn = connect()
conn.execute(
"UPDATE videos SET status=?, error=?, updated_at=datetime('now') WHERE id=?",
(status, error, id),
)
conn.commit()
conn.close()
def update_meta(id, **kw):
keys = [k for k in kw if k in SCHEMA_COLUMNS]
if not keys:
return
cols = ", ".join(f"{k}=?" for k in keys)
conn = connect()
conn.execute(
f"UPDATE videos SET {cols}, updated_at=datetime('now') WHERE id=?",
[kw[k] for k in keys] + [id],
)
conn.commit()
conn.close()
SCHEMA_COLUMNS = set(
"""
url site title description uploader tags categories duration width height
size_bytes quality status error download_path optimized_path poster_path
thumb_path preview_path published_at
""".split()
)
def queued(limit=4):
conn = connect()
rows = conn.execute(
"SELECT * FROM videos WHERE status IN ('queued','downloading','downloaded','transcoding') "
"ORDER BY id LIMIT ?",
(limit,),
).fetchall()
conn.close()
return [dict(r) for r in rows]
def list_videos(limit=60, offset=0, status="ready", category=None, tag=None, q=None, order="added_at DESC"):
where, params = ["status=?"], [status]
if category:
like = f"%{category}%"
where.append("categories LIKE ?")
params.append(like)
if tag:
like = f"%{tag}%"
where.append("(tags LIKE ? OR uploader LIKE ? OR title LIKE ?)")
params.extend([like, like, like])
if q:
ids = search_fts(q, 500)
if not ids:
return [], 0
placeholders = ",".join("?" for _ in ids)
where.append(f"id IN ({placeholders})")
params.extend(ids)
conn = connect()
valid_orders = {"added_at DESC", "added_at ASC", "title ASC", "duration DESC", "updated_at DESC"}
order = order if order in valid_orders else "added_at DESC"
sql = (
f"SELECT * FROM videos WHERE {' AND '.join(where)} "
f"ORDER BY {order} LIMIT ? OFFSET ?"
)
cur = conn.execute(sql, params + [limit, offset])
rows = cur.fetchall()
total = conn.execute(
f"SELECT COUNT(*) c FROM videos WHERE {' AND '.join(where)}", params
).fetchone()["c"]
return [dict(r) for r in rows], total
def search_fts(q, limit=200):
conn = connect()
try:
rows = conn.execute(
"SELECT rowid FROM videos_fts WHERE videos_fts MATCH ? ORDER BY rank LIMIT ?",
(q, limit),
).fetchall()
return [r["rowid"] for r in rows]
except sqlite3.OperationalError:
return []
finally:
conn.close()
def stats(conn):
total = conn.execute("SELECT COUNT(*) c FROM videos").fetchone()["c"]
ready = conn.execute("SELECT COUNT(*) c FROM videos WHERE status='ready'").fetchone()["c"]
failed = conn.execute("SELECT COUNT(*) c FROM videos WHERE status='failed'").fetchone()["c"]
q = conn.execute("SELECT COUNT(*) c FROM videos WHERE status!='ready' AND status!='failed'").fetchone()["c"]
recent = conn.execute("SELECT published_at FROM videos WHERE status='ready' ORDER BY updated_at DESC LIMIT 1").fetchone()
return {"total": total, "ready": ready, "failed": failed, "queued": q,
"last": recent["published_at"] if recent else None}
def categories(conn, limit=100):
rows = conn.execute(
"SELECT categories FROM videos WHERE status='ready' AND categories!=''"
).fetchall()
counts = {}
for r in rows:
for c in (r["categories"] or "").split(","):
c = c.strip()
if c:
counts[c] = counts.get(c, 0) + 1
items = sorted(counts.items(), key=lambda kv: -kv[1])
return [{"name": n, "count": c} for n, c in items[:limit]]
def tags_for(conn, limit=120):
rows = conn.execute("SELECT tags FROM videos WHERE status='ready' AND tags!=''").fetchall()
counts = {}
for r in rows:
for t in (r["tags"] or "").split(","):
t = t.strip()
if t:
counts[t] = counts.get(t, 0) + 1
items = sorted(counts.items(), key=lambda kv: -kv[1])
return [{"name": n, "count": c} for n, c in items[:limit]]
def delete_video(id):
conn = connect()
row = conn.execute("SELECT * FROM videos WHERE id=?", (id,)).fetchone()
if not row:
conn.close()
return None
files = [row["download_path"], row["optimized_path"], row["poster_path"],
row["thumb_path"], row["preview_path"]]
conn.execute("DELETE FROM videos WHERE id=?", (id,))
conn.commit()
conn.close()
return files