129 lines
4.6 KiB
Python
129 lines
4.6 KiB
Python
"""SQLite persistence outside the plugin checkout; every connection stays in a worker thread."""
|
|
|
|
import asyncio
|
|
import sqlite3
|
|
import time
|
|
from pathlib import Path
|
|
|
|
|
|
class MotdStore:
|
|
def __init__(self, path: Path):
|
|
self.path = Path(path)
|
|
|
|
def _run(self, operation):
|
|
self.path.parent.mkdir(parents=True, exist_ok=True)
|
|
with sqlite3.connect(self.path, timeout=15) as db:
|
|
db.row_factory = sqlite3.Row
|
|
return operation(db)
|
|
|
|
async def initialize(self):
|
|
def create(db):
|
|
db.execute("PRAGMA journal_mode=WAL")
|
|
db.executescript("""
|
|
CREATE TABLE IF NOT EXISTS bindings (
|
|
scope TEXT PRIMARY KEY, address TEXT NOT NULL,
|
|
updated_by TEXT NOT NULL, updated_at INTEGER NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS tracked (
|
|
address TEXT PRIMARY KEY, touched_at INTEGER NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS samples (
|
|
address TEXT NOT NULL, timestamp INTEGER NOT NULL,
|
|
online INTEGER, players INTEGER, source TEXT NOT NULL,
|
|
PRIMARY KEY (address, timestamp)
|
|
);
|
|
""")
|
|
|
|
await asyncio.to_thread(self._run, create)
|
|
|
|
async def get_binding(self, scope: str) -> str | None:
|
|
def query(db):
|
|
row = db.execute("SELECT address FROM bindings WHERE scope=?", (scope,)).fetchone()
|
|
return row[0] if row else None
|
|
|
|
return await asyncio.to_thread(self._run, query)
|
|
|
|
async def set_binding(self, scope: str, address: str, user: str):
|
|
await asyncio.to_thread(
|
|
self._run,
|
|
lambda db: (
|
|
db.execute(
|
|
"INSERT OR REPLACE INTO bindings VALUES (?, ?, ?, ?)",
|
|
(scope, address, user, int(time.time())),
|
|
).rowcount
|
|
),
|
|
)
|
|
|
|
async def clear_binding(self, scope: str):
|
|
await asyncio.to_thread(
|
|
self._run,
|
|
lambda db: (
|
|
db.execute(
|
|
"DELETE FROM bindings WHERE scope=?",
|
|
(scope,),
|
|
).rowcount
|
|
),
|
|
)
|
|
|
|
async def track(self, address: str, limit: int = 128):
|
|
def insert(db):
|
|
db.execute("INSERT OR REPLACE INTO tracked VALUES (?, ?)", (address, int(time.time())))
|
|
db.execute(
|
|
"""DELETE FROM tracked WHERE address IN (
|
|
SELECT address FROM tracked ORDER BY touched_at DESC, address LIMIT -1 OFFSET ?
|
|
)""",
|
|
(limit,),
|
|
)
|
|
|
|
await asyncio.to_thread(self._run, insert)
|
|
|
|
async def monitored_addresses(self, now: int | None = None) -> list[str]:
|
|
now = int(time.time()) if now is None else now
|
|
return await asyncio.to_thread(
|
|
self._run,
|
|
lambda db: [
|
|
r[0]
|
|
for r in db.execute(
|
|
"SELECT address FROM bindings UNION SELECT address FROM tracked WHERE touched_at>=?",
|
|
(now - 86400,),
|
|
)
|
|
],
|
|
)
|
|
|
|
async def record(self, address: str, data: dict | None, timestamp: int | None = None):
|
|
timestamp = int(time.time()) if timestamp is None else timestamp
|
|
online = int(bool(data["online"])) if data is not None else None
|
|
players = int(data.get("players_online", 0)) if online else None
|
|
source = data.get("source", "api") if data else "error"
|
|
await asyncio.to_thread(
|
|
self._run,
|
|
lambda db: (
|
|
db.execute(
|
|
"INSERT OR REPLACE INTO samples VALUES (?, ?, ?, ?, ?)",
|
|
(address, timestamp, online, players, source),
|
|
).rowcount
|
|
),
|
|
)
|
|
|
|
async def history(self, address: str, now: int | None = None) -> list[dict]:
|
|
now = int(time.time()) if now is None else now
|
|
return await asyncio.to_thread(
|
|
self._run,
|
|
lambda db: [
|
|
dict(r)
|
|
for r in db.execute(
|
|
"SELECT * FROM samples WHERE address=? AND timestamp BETWEEN ? AND ? ORDER BY timestamp",
|
|
(address, now - 86400, now),
|
|
)
|
|
],
|
|
)
|
|
|
|
async def prune(self, retention_hours: int = 48):
|
|
now = int(time.time())
|
|
|
|
def clean(db):
|
|
db.execute("DELETE FROM samples WHERE timestamp<?", (now - retention_hours * 3600,))
|
|
db.execute("DELETE FROM tracked WHERE touched_at<?", (now - 86400,))
|
|
|
|
await asyncio.to_thread(self._run, clean)
|