Gan, Jimmy
83 lines
3.1 KiB
Python
83 lines
3.1 KiB
Python
import os
|
|
import aiosqlite
|
|
|
|
DB_DIR = os.environ.get("DB_DIR", os.path.dirname(os.path.abspath(__file__)))
|
|
DB_PATH = os.path.join(DB_DIR, "t_youtube.db")
|
|
|
|
async def init_db():
|
|
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
|
|
async with aiosqlite.connect(DB_PATH) as db:
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS videos (
|
|
video_id TEXT PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
channel_name TEXT,
|
|
channel_id TEXT,
|
|
url TEXT NOT NULL,
|
|
thumbnail_url TEXT,
|
|
publish_date TEXT,
|
|
duration INTEGER,
|
|
view_count INTEGER DEFAULT 0,
|
|
transcript TEXT,
|
|
summary TEXT,
|
|
status TEXT DEFAULT 'pending',
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
""")
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS video_notes (
|
|
video_id TEXT PRIMARY KEY,
|
|
note_text TEXT NOT NULL,
|
|
updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (video_id) REFERENCES videos(video_id)
|
|
)
|
|
""")
|
|
await db.execute("""
|
|
CREATE TABLE IF NOT EXISTS agent_queue (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
video_id TEXT NOT NULL,
|
|
user_prompt TEXT NOT NULL,
|
|
status TEXT DEFAULT 'pending',
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
processed_at TEXT,
|
|
FOREIGN KEY (video_id) REFERENCES videos(video_id)
|
|
)
|
|
""")
|
|
# FTS5 virtual table for full-text search on title + summary
|
|
await db.execute("""
|
|
CREATE VIRTUAL TABLE IF NOT EXISTS videos_fts USING fts5(
|
|
title, summary,
|
|
content='videos',
|
|
content_rowid='rowid'
|
|
)
|
|
""")
|
|
# Trigger to keep FTS5 index in sync
|
|
await db.execute("""
|
|
CREATE TRIGGER IF NOT EXISTS videos_fts_insert AFTER INSERT ON videos
|
|
BEGIN
|
|
INSERT INTO videos_fts(rowid, title, summary) VALUES (new.rowid, new.title, new.summary);
|
|
END;
|
|
""")
|
|
await db.execute("""
|
|
CREATE TRIGGER IF NOT EXISTS videos_fts_update AFTER UPDATE ON videos
|
|
BEGIN
|
|
INSERT INTO videos_fts(videos_fts, rowid, title, summary) VALUES ('delete', old.rowid, old.title, old.summary);
|
|
INSERT INTO videos_fts(rowid, title, summary) VALUES (new.rowid, new.title, new.summary);
|
|
END;
|
|
""")
|
|
await db.execute("""
|
|
CREATE TRIGGER IF NOT EXISTS videos_fts_delete AFTER DELETE ON videos
|
|
BEGIN
|
|
INSERT INTO videos_fts(videos_fts, rowid, title, summary) VALUES ('delete', old.rowid, old.title, old.summary);
|
|
END;
|
|
""")
|
|
await db.commit()
|
|
|
|
async def get_db():
|
|
db = await aiosqlite.connect(DB_PATH)
|
|
db.row_factory = aiosqlite.Row
|
|
try:
|
|
yield db
|
|
finally:
|
|
await db.close()
|