File size: 2,534 Bytes
dece8c2 8552e6a dece8c2 8552e6a dece8c2 8552e6a dece8c2 8552e6a | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | import aiosqlite
import os
DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data.db")
async def init_db():
async with aiosqlite.connect(DB_PATH) as db:
await db.execute("CREATE TABLE IF NOT EXISTS sources (id TEXT PRIMARY KEY, name TEXT, type TEXT, url TEXT, api_key TEXT)")
await db.execute("CREATE TABLE IF NOT EXISTS recordings (id TEXT PRIMARY KEY, source_id TEXT, dataset_repo TEXT, original_name TEXT, md5_name TEXT, status TEXT, start_time REAL, end_time REAL, file_path TEXT, remote_url TEXT, remote_id TEXT)")
await db.execute("CREATE TABLE IF NOT EXISTS epg_cache (id TEXT PRIMARY KEY, source_id TEXT, data TEXT, cached_at REAL)")
await db.commit()
async def sync_sources(sources):
async with aiosqlite.connect(DB_PATH) as db:
await db.execute("DELETE FROM sources")
await db.executemany("INSERT OR IGNORE INTO sources VALUES (?,?,?,?,?)", [(s["id"], s["name"], s["type"], s["url"], s.get("api_key","")) for s in sources])
await db.commit()
async def get_sources():
async with aiosqlite.connect(DB_PATH) as db:
db.row_factory = aiosqlite.Row
async with db.execute("SELECT * FROM sources") as cursor:
return [dict(r) for r in await cursor.fetchall()]
async def upsert_recording(data):
async with aiosqlite.connect(DB_PATH) as db:
await db.execute("INSERT OR REPLACE INTO recordings VALUES (?,?,?,?,?,?,?,?,?,?,?)", (data["id"], data["source_id"], data.get("dataset_repo",""), data["original_name"], data["md5_name"], data["status"], data["start_time"], data.get("end_time",0), data.get("file_path",""), data.get("remote_url",""), data.get("remote_id","")))
await db.commit()
async def get_recording(rec_id):
async with aiosqlite.connect(DB_PATH) as db:
db.row_factory = aiosqlite.Row
async with db.execute("SELECT * FROM recordings WHERE id=?", (rec_id,)) as cursor:
row = await cursor.fetchone()
return dict(row) if row else None
async def list_recordings():
async with aiosqlite.connect(DB_PATH) as db:
db.row_factory = aiosqlite.Row
async with db.execute("SELECT * FROM recordings ORDER BY start_time DESC") as cursor:
return [dict(r) for r in await cursor.fetchall()]
async def cache_epg(source_id, data, timestamp):
async with aiosqlite.connect(DB_PATH) as db:
await db.execute("INSERT OR REPLACE INTO epg_cache VALUES (?,?,?,?)", (source_id, source_id, data, timestamp))
await db.commit() |