import sqlite3 import os DB_PATH = os.path.join(os.path.dirname(__file__), 'kiosk.db') def get_conn(): conn = sqlite3.connect(DB_PATH) conn.row_factory = sqlite3.Row return conn def init_db(): with get_conn() as conn: conn.executescript(""" CREATE TABLE IF NOT EXISTS jobs ( id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT NOT NULL, duration REAL NOT NULL, type TEXT NOT NULL, source TEXT NOT NULL, enabled INTEGER DEFAULT 1, position INTEGER, shuffle INTEGER DEFAULT 0, show_chat INTEGER DEFAULT 0, transition_type TEXT DEFAULT '', transition_duration INTEGER DEFAULT 0 ); CREATE TABLE IF NOT EXISTS state ( key TEXT PRIMARY KEY, value TEXT ); """) # Migrering: time → duration cols = {r[1]: r[2] for r in conn.execute("PRAGMA table_info(jobs)").fetchall()} if 'time' in cols and 'duration' not in cols: conn.executescript(""" ALTER TABLE jobs ADD COLUMN duration REAL NOT NULL DEFAULT 5; UPDATE jobs SET duration = 5; """) # Migrering: add shuffle + show_chat columns if missing if 'shuffle' not in cols: conn.execute("ALTER TABLE jobs ADD COLUMN shuffle INTEGER DEFAULT 0") if 'show_chat' not in cols: conn.execute("ALTER TABLE jobs ADD COLUMN show_chat INTEGER DEFAULT 0") if 'transition_type' not in cols: conn.execute("ALTER TABLE jobs ADD COLUMN transition_type TEXT DEFAULT ''") if 'transition_duration' not in cols: conn.execute("ALTER TABLE jobs ADD COLUMN transition_duration INTEGER DEFAULT 0") # Migrering: add position column if missing if 'position' not in cols: conn.executescript(""" ALTER TABLE jobs RENAME TO jobs_old; CREATE TABLE jobs ( id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT NOT NULL, duration REAL NOT NULL, type TEXT NOT NULL, source TEXT NOT NULL, enabled INTEGER DEFAULT 1, position INTEGER ); INSERT INTO jobs (id, label, duration, type, source, enabled) SELECT id, label, duration, type, source, enabled FROM jobs_old; DROP TABLE jobs_old; """) # initialize positions to id conn.execute("UPDATE jobs SET position = id;") # Migrering: INTEGER → REAL elif cols.get('duration') == 'INTEGER': conn.executescript(""" ALTER TABLE jobs RENAME TO jobs_old; CREATE TABLE jobs ( id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT NOT NULL, duration REAL NOT NULL, type TEXT NOT NULL, source TEXT NOT NULL, enabled INTEGER DEFAULT 1 ); INSERT INTO jobs SELECT * FROM jobs_old; DROP TABLE jobs_old; """) # --- Jobs --- def get_all_jobs(): with get_conn() as conn: return [dict(r) for r in conn.execute("SELECT * FROM jobs ORDER BY COALESCE(position, id), id").fetchall()] def get_enabled_jobs(): with get_conn() as conn: return [dict(r) for r in conn.execute( "SELECT * FROM jobs WHERE enabled = 1 ORDER BY COALESCE(position, id), id" ).fetchall()] def add_job(label, duration, type_, source, shuffle=False, show_chat=False, transition_type='', transition_duration=0): with get_conn() as conn: curpos = conn.execute("SELECT MAX(position) AS m FROM jobs").fetchone() nextpos = (curpos['m'] or 0) + 1 cur = conn.execute( "INSERT INTO jobs (label, duration, type, source, position, shuffle, show_chat, " "transition_type, transition_duration) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", (label, duration, type_, source, nextpos, 1 if shuffle else 0, 1 if show_chat else 0, transition_type or '', int(transition_duration or 0)) ) return cur.lastrowid def delete_job(job_id): with get_conn() as conn: conn.execute("DELETE FROM jobs WHERE id = ?", (job_id,)) def toggle_job(job_id): with get_conn() as conn: conn.execute( "UPDATE jobs SET enabled = CASE WHEN enabled = 1 THEN 0 ELSE 1 END WHERE id = ?", (job_id,) ) row = conn.execute("SELECT enabled FROM jobs WHERE id = ?", (job_id,)).fetchone() return dict(row) if row else None def update_job(job_id, **fields): if not fields: return keys = [] vals = [] for k, v in fields.items(): if k in ('label', 'duration', 'type', 'source', 'enabled', 'shuffle', 'show_chat', 'transition_type', 'transition_duration'): keys.append(f"{k} = ?") vals.append(v) if not keys: return vals.append(job_id) with get_conn() as conn: conn.execute(f"UPDATE jobs SET {', '.join(keys)} WHERE id = ?", tuple(vals)) def reorder_jobs(id_list): with get_conn() as conn: for idx, jid in enumerate(id_list, start=1): conn.execute("UPDATE jobs SET position = ? WHERE id = ?", (idx, jid)) # --- State --- def set_state(key, value): with get_conn() as conn: conn.execute( "INSERT OR REPLACE INTO state (key, value) VALUES (?, ?)", (key, value) ) def set_current(type_, source): with get_conn() as conn: conn.execute( "INSERT OR REPLACE INTO state (key, value) VALUES ('current_type', ?)", (type_,) ) conn.execute( "INSERT OR REPLACE INTO state (key, value) VALUES ('current_source', ?)", (source,) ) def get_state(): with get_conn() as conn: rows = conn.execute("SELECT key, value FROM state").fetchall() return {r['key']: r['value'] for r in rows} class AddonDB: """ Scoped DB-access för addons. Alla nycklar prefixas automatiskt med addon-prefixet så att addons inte kan råka skriva utanför sin namnrymd. Exempel: _db = AddonDB('ticker_') _db.set('enabled', '1') # sparar 'ticker_enabled' _db.get('enabled') # läser 'ticker_enabled' """ def __init__(self, prefix: str): self._prefix = prefix def get(self, key: str, default=None): return get_state().get(self._prefix + key, default) def set(self, key: str, value): set_state(self._prefix + key, str(value)) def get_all(self) -> dict: """Returnerar alla värden för detta prefix, utan prefixet i nyckelnamnen.""" state = get_state() pfx = self._prefix return {k[len(pfx):]: v for k, v in state.items() if k.startswith(pfx)}