69 lines
1.9 KiB
Python
69 lines
1.9 KiB
Python
"""SQLite access. The schema is shared with patchbayd (schema.sql)."""
|
|
|
|
import os
|
|
import sqlite3
|
|
import time
|
|
|
|
from flask import current_app, g
|
|
|
|
_HERE = os.path.dirname(os.path.abspath(__file__))
|
|
# Installed copy next to the package, else the repository root.
|
|
SCHEMA_PATHS = [os.path.join(_HERE, "schema.sql"), os.path.join(_HERE, "..", "..", "schema.sql")]
|
|
|
|
|
|
def schema_sql():
|
|
for p in SCHEMA_PATHS:
|
|
if os.path.exists(p):
|
|
with open(p, encoding="utf-8") as f:
|
|
return f.read()
|
|
raise FileNotFoundError("schema.sql not found")
|
|
|
|
|
|
def connect(path):
|
|
old = os.umask(0o077) # the DB holds password hashes
|
|
try:
|
|
conn = sqlite3.connect(path, timeout=5, isolation_level=None)
|
|
finally:
|
|
os.umask(old)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA busy_timeout = 5000")
|
|
conn.execute("PRAGMA foreign_keys = ON")
|
|
return conn
|
|
|
|
|
|
def init(path):
|
|
conn = connect(path)
|
|
conn.executescript(schema_sql())
|
|
# Older databases lack nodes.iface / nodes.origin (same migrations as db.c).
|
|
cols = {r["name"] for r in conn.execute("PRAGMA table_info(nodes)")}
|
|
for col in ("iface", "origin"):
|
|
if col not in cols:
|
|
conn.execute(f"ALTER TABLE nodes ADD COLUMN {col} TEXT NOT NULL DEFAULT ''")
|
|
conn.close()
|
|
|
|
|
|
def get():
|
|
if "db" not in g:
|
|
g.db = connect(current_app.config["PB_DB"])
|
|
return g.db
|
|
|
|
|
|
def close(_exc=None):
|
|
conn = g.pop("db", None)
|
|
if conn is not None:
|
|
conn.close()
|
|
|
|
|
|
def setting(key, default=None):
|
|
row = get().execute("SELECT value FROM settings WHERE key = ?", (key,)).fetchone()
|
|
return row["value"] if row else default
|
|
|
|
|
|
def set_setting(key, value):
|
|
get().execute("INSERT INTO settings (key, value) VALUES (?, ?) "
|
|
"ON CONFLICT (key) DO UPDATE SET value = excluded.value", (key, str(value)))
|
|
|
|
|
|
def now():
|
|
return int(time.time())
|