import asyncio import os import sqlite3 import time # Append only. Each entry runs once, in order, and the index is stored in # PRAGMA user_version. Never edit an entry that has already shipped. MIGRATIONS = [ """ CREATE TABLE settings ( key TEXT PRIMARY KEY, value TEXT NOT NULL ); CREATE TABLE cases ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, moderator_id INTEGER NOT NULL, action TEXT NOT NULL, reason TEXT, created_at INTEGER NOT NULL, expires_at INTEGER, active INTEGER NOT NULL DEFAULT 1 ); CREATE INDEX idx_cases_user ON cases (user_id); """, """ CREATE TABLE pending_verifications ( user_id INTEGER PRIMARY KEY, joined_at INTEGER NOT NULL, deadline INTEGER NOT NULL, flags TEXT NOT NULL DEFAULT '', in_review INTEGER NOT NULL DEFAULT 0 ); """, """ ALTER TABLE pending_verifications ADD COLUMN rules_ok INTEGER NOT NULL DEFAULT 0; ALTER TABLE pending_verifications ADD COLUMN choices TEXT NOT NULL DEFAULT ''; """, """ CREATE TABLE applications ( id INTEGER PRIMARY KEY AUTOINCREMENT, code_hash TEXT NOT NULL UNIQUE, status TEXT NOT NULL DEFAULT 'pending', invite_url TEXT, created_at INTEGER NOT NULL ); """, ] class Database: def __init__(self, path): self.path = path self.conn = None self.lock = asyncio.Lock() async def connect(self): os.makedirs(os.path.dirname(self.path) or ".", exist_ok=True) self.conn = await asyncio.to_thread(self._open) def _open(self): conn = sqlite3.connect(self.path, check_same_thread=False) conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA foreign_keys=ON") version = conn.execute("PRAGMA user_version").fetchone()[0] for i, sql in enumerate(MIGRATIONS[version:], start=version + 1): conn.executescript(sql) conn.execute(f"PRAGMA user_version={i}") conn.commit() return conn async def close(self): if self.conn: await asyncio.to_thread(self.conn.close) self.conn = None def _run(self, sql, params, fetch): cur = self.conn.execute(sql, params) rows = cur.fetchall() if fetch else None self.conn.commit() return rows, cur.lastrowid async def _exec(self, sql, params=(), fetch=False): async with self.lock: return await asyncio.to_thread(self._run, sql, params, fetch) async def execute(self, sql, *params): """Run a write and return the new row id.""" _, rowid = await self._exec(sql, params) return rowid async def fetchall(self, sql, *params): rows, _ = await self._exec(sql, params, fetch=True) return rows async def fetchone(self, sql, *params): rows = await self.fetchall(sql, *params) return rows[0] if rows else None async def get_setting(self, key, default=None): row = await self.fetchone("SELECT value FROM settings WHERE key = ?", key) return row["value"] if row else default async def set_setting(self, key, value): await self.execute( "INSERT INTO settings (key, value) VALUES (?, ?) " "ON CONFLICT(key) DO UPDATE SET value = excluded.value", key, str(value)) async def add_case(self, user_id, moderator_id, action, reason=None, expires_at=None): return await self.execute( "INSERT INTO cases (user_id, moderator_id, action, reason, " "created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?)", user_id, moderator_id, action, reason, int(time.time()), expires_at) async def get_cases(self, user_id): return await self.fetchall( "SELECT * FROM cases WHERE user_id = ? ORDER BY id DESC", user_id) async def count_active_cases(self, user_id, action): row = await self.fetchone( "SELECT COUNT(*) AS n FROM cases " "WHERE user_id = ? AND action = ? AND active = 1", user_id, action) return row["n"]