106 lines
3.1 KiB
Python
106 lines
3.1 KiB
Python
import sqlite3
|
|
from pathlib import Path
|
|
from contextlib import contextmanager
|
|
|
|
BASE_DIR = Path(__file__).resolve().parent.parent
|
|
|
|
DATABASE_DIR = BASE_DIR / "data"
|
|
DATABASE_PATH = DATABASE_DIR / "parental-control.db"
|
|
|
|
|
|
def initialize_database():
|
|
DATABASE_DIR.mkdir(parents=True, exist_ok=True)
|
|
|
|
with sqlite3.connect(DATABASE_PATH) as db:
|
|
db.execute("PRAGMA foreign_keys = ON")
|
|
|
|
db.executescript(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS daily_allowances (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
weekday INTEGER NOT NULL,
|
|
allowance_seconds INTEGER NOT NULL DEFAULT 0,
|
|
|
|
UNIQUE(user_id, weekday),
|
|
|
|
FOREIGN KEY(user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS access_windows (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
weekday INTEGER NOT NULL,
|
|
start_minute INTEGER NOT NULL,
|
|
end_minute INTEGER NOT NULL,
|
|
|
|
FOREIGN KEY(user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS usage (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
date TEXT NOT NULL,
|
|
used_seconds INTEGER NOT NULL DEFAULT 0,
|
|
|
|
UNIQUE(user_id, date),
|
|
|
|
FOREIGN KEY(user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS temporary_grants (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
seconds INTEGER NOT NULL,
|
|
remaining_seconds INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at TEXT,
|
|
consumed INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
event_type TEXT NOT NULL,
|
|
details TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY(user_id)
|
|
REFERENCES users(id)
|
|
ON DELETE SET NULL
|
|
);
|
|
"""
|
|
)
|
|
|
|
db.commit()
|
|
|
|
|
|
@contextmanager
|
|
def get_db():
|
|
db = sqlite3.connect(DATABASE_PATH)
|
|
db.row_factory = sqlite3.Row
|
|
db.execute("PRAGMA foreign_keys = ON")
|
|
|
|
try:
|
|
yield db
|
|
db.commit()
|
|
except Exception:
|
|
db.rollback()
|
|
raise
|
|
finally:
|
|
db.close()
|