Archived
228 lines
7.1 KiB
Python
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 |