142 lines
4.8 KiB
Python
142 lines
4.8 KiB
Python
"""
|
|
app/db.py
|
|
=========
|
|
Lightweight SQLite-backed key/value settings store.
|
|
|
|
The database file is created automatically on first use beside the
|
|
application package, or at the path set by the DB_PATH environment
|
|
variable (useful for Docker volume persistence).
|
|
|
|
Public API
|
|
----------
|
|
init_db() — create the table if it doesn't exist (call at startup)
|
|
get_setting(key) — return the stored string value, or None
|
|
save_setting(key, val) — upsert a key/value pair
|
|
get_all_settings() — return all rows as {key: value}
|
|
delete_setting(key) — remove a key (used to clear optional fields)
|
|
"""
|
|
|
|
import os
|
|
import sqlite3
|
|
import threading
|
|
from pathlib import Path
|
|
|
|
from .config import BASE_DIR
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Database location
|
|
# ---------------------------------------------------------------------------
|
|
|
|
# Default: videocompressor/videopress.db — sits beside the app/ package.
|
|
# Override with the DB_PATH env var (e.g. to a Docker-mounted volume path).
|
|
DB_PATH = Path(os.environ.get('DB_PATH', str(BASE_DIR / 'videopress.db')))
|
|
|
|
# SQLite connections are not thread-safe across threads; use a per-thread
|
|
# connection via threading.local() so each worker greenlet/thread gets its own.
|
|
_local = threading.local()
|
|
|
|
_INIT_LOCK = threading.Lock()
|
|
_initialised = False
|
|
|
|
|
|
def _connect() -> sqlite3.Connection:
|
|
"""Return (and cache) a per-thread SQLite connection."""
|
|
if not hasattr(_local, 'conn') or _local.conn is None:
|
|
_local.conn = sqlite3.connect(str(DB_PATH), check_same_thread=False)
|
|
_local.conn.row_factory = sqlite3.Row
|
|
# WAL mode allows concurrent reads alongside a single writer
|
|
_local.conn.execute('PRAGMA journal_mode=WAL')
|
|
_local.conn.execute('PRAGMA foreign_keys=ON')
|
|
return _local.conn
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# Schema
|
|
# ---------------------------------------------------------------------------
|
|
|
|
def init_db() -> None:
|
|
"""
|
|
Create the settings table if it does not already exist.
|
|
Also creates the parent directory of DB_PATH if needed.
|
|
Safe to call multiple times — idempotent.
|
|
"""
|
|
global _initialised
|
|
with _INIT_LOCK:
|
|
if _initialised:
|
|
return
|
|
|
|
# Ensure the directory exists before SQLite tries to create the file.
|
|
# This handles the case where the Docker volume mount creates ./data
|
|
# as root before the container user can write to it.
|
|
db_dir = DB_PATH.parent
|
|
try:
|
|
db_dir.mkdir(parents=True, exist_ok=True)
|
|
except PermissionError:
|
|
raise PermissionError(
|
|
f"Cannot create database directory '{db_dir}'. "
|
|
f"If running in Docker, create the directory on the host first "
|
|
f"and ensure it is writable by UID 1000:\n"
|
|
f" mkdir -p {db_dir} && chown 1000:1000 {db_dir}"
|
|
)
|
|
|
|
# Test that we can actually write to the directory before SQLite tries
|
|
test_file = db_dir / '.write_test'
|
|
try:
|
|
test_file.touch()
|
|
test_file.unlink()
|
|
except PermissionError:
|
|
raise PermissionError(
|
|
f"Database directory '{db_dir}' is not writable by the current user. "
|
|
f"If running in Docker, fix permissions on the host:\n"
|
|
f" chown 1000:1000 {db_dir}"
|
|
)
|
|
|
|
conn = _connect()
|
|
conn.execute("""
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
)
|
|
""")
|
|
conn.commit()
|
|
_initialised = True
|
|
|
|
|
|
# ---------------------------------------------------------------------------
|
|
# CRUD helpers
|
|
# ---------------------------------------------------------------------------
|
|
|
|
def get_setting(key: str) -> str | None:
|
|
"""Return the stored value for *key*, or None if not set."""
|
|
init_db()
|
|
row = _connect().execute(
|
|
'SELECT value FROM settings WHERE key = ?', (key,)
|
|
).fetchone()
|
|
return row['value'] if row else None
|
|
|
|
|
|
def save_setting(key: str, value: str) -> None:
|
|
"""Insert or update *key* with *value*."""
|
|
init_db()
|
|
conn = _connect()
|
|
conn.execute(
|
|
'INSERT INTO settings (key, value) VALUES (?, ?)'
|
|
' ON CONFLICT(key) DO UPDATE SET value = excluded.value',
|
|
(key, value),
|
|
)
|
|
conn.commit()
|
|
|
|
|
|
def delete_setting(key: str) -> None:
|
|
"""Remove *key* from the store (silently succeeds if absent)."""
|
|
init_db()
|
|
conn = _connect()
|
|
conn.execute('DELETE FROM settings WHERE key = ?', (key,))
|
|
conn.commit()
|
|
|
|
|
|
def get_all_settings() -> dict[str, str]:
|
|
"""Return all stored settings as a plain dict."""
|
|
init_db()
|
|
rows = _connect().execute('SELECT key, value FROM settings').fetchall()
|
|
return {row['key']: row['value'] for row in rows}
|