"""Transactional SQLite persistence outside the installed plugin directory.""" import asyncio import json import sqlite3 import time from contextlib import closing from pathlib import Path def scope_key(platform_id, group_id, user_id): # Group UMO may include the sender when unique-session is enabled. return json.dumps([platform_id, "group" if group_id else "private", group_id or user_id]) class ReminderStore: def __init__(self, path): self.path = Path(path) self._lock = asyncio.Lock() async def _run(self, operation): def run(): with closing(sqlite3.connect(self.path, timeout=10)) as db, db: db.row_factory = sqlite3.Row return operation(db) async with self._lock: # Wait for the worker before releasing the lock even during unload. worker = asyncio.create_task(asyncio.to_thread(run)) try: return await asyncio.shield(worker) except asyncio.CancelledError: await worker raise async def initialize(self): self.path.parent.mkdir(parents=True, exist_ok=True) def create(db): db.executescript(""" PRAGMA journal_mode=WAL; CREATE TABLE IF NOT EXISTS reminders ( id INTEGER PRIMARY KEY AUTOINCREMENT, scope TEXT NOT NULL, platform_id TEXT NOT NULL, platform_name TEXT NOT NULL, destination TEXT NOT NULL, scene TEXT NOT NULL, umo TEXT NOT NULL, creator_id TEXT NOT NULL, creator_name TEXT NOT NULL DEFAULT '', target_id TEXT NOT NULL, target_name TEXT NOT NULL DEFAULT '', content TEXT NOT NULL, remind_at REAL NOT NULL, timezone TEXT NOT NULL, repeat_type TEXT NOT NULL CHECK(repeat_type IN ('none','daily','weekly')), status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','done','cancelled')), failure_count INTEGER NOT NULL DEFAULT 0, last_error TEXT NOT NULL DEFAULT '', retry_at REAL NOT NULL DEFAULT 0, lease_owner TEXT NOT NULL DEFAULT '', lease_until REAL NOT NULL DEFAULT 0, created_at REAL NOT NULL ); CREATE INDEX IF NOT EXISTS reminders_due ON reminders(status, remind_at); CREATE INDEX IF NOT EXISTS reminders_scope ON reminders(scope, creator_id); CREATE TABLE IF NOT EXISTS members ( scope TEXT NOT NULL, user_id TEXT NOT NULL, nickname TEXT NOT NULL DEFAULT '', seen_at REAL NOT NULL, PRIMARY KEY(scope, user_id) ); CREATE TABLE IF NOT EXISTS member_names ( scope TEXT NOT NULL, user_id TEXT NOT NULL, name TEXT NOT NULL, PRIMARY KEY(scope, user_id, name) ); CREATE INDEX IF NOT EXISTS member_names_lookup ON member_names(scope, name); CREATE TABLE IF NOT EXISTS member_aliases ( scope TEXT NOT NULL, user_id TEXT NOT NULL, name TEXT NOT NULL, PRIMARY KEY(scope, user_id), UNIQUE(scope, name) ); INSERT OR IGNORE INTO member_names SELECT scope,user_id,nickname FROM members WHERE nickname!=''; CREATE TABLE IF NOT EXISTS reply_contexts ( platform_id TEXT NOT NULL, scene TEXT NOT NULL, destination TEXT NOT NULL, msg_id TEXT NOT NULL, received_at REAL NOT NULL, used INTEGER NOT NULL DEFAULT 0, blocked INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(platform_id, scene, destination) ); CREATE TABLE IF NOT EXISTS group_permissions ( platform_id TEXT NOT NULL, destination TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'unknown', notified INTEGER NOT NULL DEFAULT 0, notice_until REAL NOT NULL DEFAULT 0, event_at REAL NOT NULL DEFAULT 0, event_id TEXT NOT NULL DEFAULT '', PRIMARY KEY(platform_id, destination) ); """) columns = {row[1] for row in db.execute("PRAGMA table_info(reminders)")} if "waiting_reason" not in columns: db.execute( "ALTER TABLE reminders ADD COLUMN waiting_reason TEXT NOT NULL DEFAULT ''" ) await self._run(create) async def permission(self, platform_id, destination): return await self._run( lambda db: dict( db.execute( "SELECT * FROM group_permissions WHERE platform_id=? AND destination=?", (platform_id, destination), ).fetchone() or {"state": "unknown", "notified": 0} ) ) async def set_permission( self, platform_id, destination, state, event_at=None, event_id="", reset=False ): def write(db): db.execute("BEGIN IMMEDIATE") db.execute( "INSERT OR IGNORE INTO group_permissions(platform_id,destination) VALUES (?,?)", (platform_id, destination), ) row = db.execute( "SELECT * FROM group_permissions WHERE platform_id=? AND destination=?", (platform_id, destination), ).fetchone() if event_at is not None and ( event_at < row["event_at"] or (event_id and event_id == row["event_id"]) ): return False reset_notice = reset and (row["state"] != state or event_at is not None) db.execute( "UPDATE group_permissions SET state=?,event_at=COALESCE(?,event_at)," "event_id=CASE WHEN ?!='' THEN ? ELSE event_id END," "notified=CASE WHEN ? THEN 0 ELSE notified END," "notice_until=CASE WHEN ? THEN 0 ELSE notice_until END " "WHERE platform_id=? AND destination=?", ( state, event_at, event_id, event_id, reset_notice, reset_notice, platform_id, destination, ), ) if state == "granted": db.execute( "UPDATE reminders SET retry_at=0,waiting_reason='' WHERE platform_id=? " "AND destination=? AND scene='group' AND status='active' " "AND waiting_reason LIKE '主动消息权限%'", (platform_id, destination), ) return True return await self._run(write) async def claim_notice(self, platform_id, destination): def write(db): db.execute("BEGIN IMMEDIATE") db.execute( "INSERT OR IGNORE INTO group_permissions(platform_id,destination) VALUES (?,?)", (platform_id, destination), ) return ( db.execute( "UPDATE group_permissions SET notice_until=? WHERE platform_id=? " "AND destination=? AND state!='granted' AND notified=0 AND notice_until<=?", (time.time() + 60, platform_id, destination, time.time()), ).rowcount == 1 ) return await self._run(write) async def finish_notice(self, platform_id, destination, sent): await self._run( lambda db: db.execute( "UPDATE group_permissions SET notified=MAX(notified,?),notice_until=0 WHERE platform_id=? AND destination=?", (int(sent), platform_id, destination), ) ) async def wait_for_permission(self, reminder_id, owner, reason, retry_at): await self._run( lambda db: db.execute( "UPDATE reminders SET waiting_reason=?,retry_at=?,lease_owner='',lease_until=0 " "WHERE id=? AND lease_owner=? AND status='active'", (reason[:500], retry_at, reminder_id, owner), ) ) async def observe(self, scope, user_id, nickname="", names=()): await self.observe_members(scope, [(user_id, nickname, names)]) async def observe_members(self, scope, members): def write(db): for user_id, nickname, names in members: if nickname == user_id: nickname = "" names = list(dict.fromkeys(n for n in (nickname, *names) if n and n != user_id)) db.execute( "INSERT INTO members VALUES (?,?,?,?) ON CONFLICT(scope,user_id) DO UPDATE SET " "nickname=CASE WHEN excluded.nickname!='' THEN excluded.nickname ELSE members.nickname END, " "seen_at=excluded.seen_at", (scope, user_id, nickname or "", time.time()), ) if names: # Keep current names, rather than matching a nickname after a rename. db.execute( "DELETE FROM member_names WHERE scope=? AND user_id=?", (scope, user_id) ) db.executemany( "INSERT INTO member_names VALUES (?,?,?)", [(scope, user_id, name) for name in names], ) await self._run(write) async def member_names(self, scope): return await self._run( lambda db: [ r[0] for r in db.execute( "SELECT name FROM member_names WHERE scope=? UNION " "SELECT name FROM member_aliases WHERE scope=?", (scope, scope), ) ] ) async def member(self, scope, value): def read(db): rows = db.execute( "SELECT DISTINCT m.* FROM members m LEFT JOIN member_names n " "ON m.scope=n.scope AND m.user_id=n.user_id " "LEFT JOIN member_aliases a ON m.scope=a.scope AND m.user_id=a.user_id " "WHERE m.scope=? AND (m.user_id=? OR n.name=? OR a.name=?)", (scope, value, value, value), ).fetchall() exact = [r for r in rows if r["user_id"] == value] if exact: return dict(exact[0]) if len(rows) > 1: raise ValueError( "本群有重名用户,请让成员 /tx name 登记不同提醒昵称,或使用平台能识别的真实 @。" ) return dict(rows[0]) if rows else None return await self._run(read) async def register_name(self, scope, user_id, name): def write(db): db.execute("BEGIN IMMEDIATE") occupied = db.execute( "SELECT user_id FROM member_names WHERE scope=? AND name=? UNION " "SELECT user_id FROM member_aliases WHERE scope=? AND name=? UNION " "SELECT user_id FROM members WHERE scope=? AND user_id=?", (scope, name, scope, name, scope, name), ).fetchall() if any(row[0] != user_id for row in occupied): raise ValueError("该名字已经对应本群其他成员,请选择一个唯一的提醒昵称。") db.execute( "INSERT INTO member_aliases VALUES (?,?,?) " "ON CONFLICT(scope,user_id) DO UPDATE SET name=excluded.name", (scope, user_id, name), ) await self._run(write) async def registered_name(self, scope, user_id): def read(db): row = db.execute( "SELECT name FROM member_aliases WHERE scope=? AND user_id=?", (scope, user_id) ).fetchone() return row["name"] if row else "" return await self._run(read) async def add(self, record, limit): def insert(db): db.execute("BEGIN IMMEDIATE") count = db.execute( "SELECT COUNT(*) FROM reminders WHERE scope=? AND creator_id=? AND status='active'", (record["scope"], record["creator_id"]), ).fetchone()[0] if count >= limit: raise ValueError(f"你在当前会话最多只能设置 {limit} 个提醒,请先取消一些。") columns = ( "scope", "platform_id", "platform_name", "destination", "scene", "umo", "creator_id", "creator_name", "target_id", "target_name", "content", "remind_at", "timezone", "repeat_type", ) cursor = db.execute( f"INSERT INTO reminders ({','.join(columns)},created_at) " f"VALUES ({','.join('?' for _ in range(len(columns) + 1))})", tuple(record[c] for c in columns) + (time.time(),), ) return cursor.lastrowid return await self._run(insert) async def list(self, scope, creator_id=None, history=False): def read(db): sql, params = "SELECT * FROM reminders WHERE scope=?", [scope] if creator_id is not None: sql += " AND creator_id=?" params.append(creator_id) if not history: sql += " AND status='active'" sql += " ORDER BY remind_at,id" return [dict(r) for r in db.execute(sql, params).fetchall()] return await self._run(read) async def get(self, reminder_id, scope): def read(db): row = db.execute( "SELECT * FROM reminders WHERE id=? AND scope=?", (reminder_id, scope) ).fetchone() return dict(row) if row else None return await self._run(read) async def cancel(self, scope, creator_id, reminder_id=None): def write(db): sql = "UPDATE reminders SET status='cancelled', waiting_reason='', lease_owner='', lease_until=0 WHERE scope=? AND status='active'" params = [scope] if creator_id is not None: sql += " AND creator_id=?" params.append(creator_id) if reminder_id is not None: sql += " AND id=?" params.append(reminder_id) return db.execute(sql, params).rowcount return await self._run(write) async def due(self, now): return await self._run( lambda db: [ dict(r) for r in db.execute( "SELECT * FROM reminders WHERE status='active' AND remind_at<=? " "AND retry_at<=? AND lease_until<=? ORDER BY remind_at,id", (now, now, now), ) ] ) async def claim(self, reminder_id, owner, now): # One lease per send prevents overlapping hot reloads from sending twice. return await self._run( lambda db: ( db.execute( "UPDATE reminders SET lease_owner=?,lease_until=? WHERE id=? AND status='active' " "AND lease_until<=? AND remind_at<=? AND retry_at<=?", (owner, now + 120, reminder_id, now, now, now), ).rowcount == 1 ) ) async def success(self, reminder_id, owner, next_time=None): return await self._run( lambda db: ( db.execute( "UPDATE reminders SET status=?,remind_at=COALESCE(?,remind_at),failure_count=0," "last_error='',waiting_reason='',retry_at=0,lease_owner='',lease_until=0 WHERE id=? AND lease_owner=? AND status='active'", ("active" if next_time is not None else "done", next_time, reminder_id, owner), ).rowcount ) ) async def failure(self, reminder_id, owner, error, maximum, retry_at): def write(db): db.execute( "UPDATE reminders SET failure_count=failure_count+1,last_error=?,retry_at=?,waiting_reason=''," "status=CASE WHEN failure_count+1>=? THEN 'cancelled' ELSE 'active' END," "lease_owner='',lease_until=0 WHERE id=? AND lease_owner=? AND status='active'", (str(error)[:500], retry_at, maximum, reminder_id, owner), ) await self._run(write) async def release(self, reminder_id, owner): await self._run( lambda db: ( db.execute( "UPDATE reminders SET lease_owner='',lease_until=0 WHERE id=? AND lease_owner=?", (reminder_id, owner), ).rowcount ) ) async def remember_reply(self, platform_id, scene, destination, msg_id, received_at): # Duplicate/replayed events must not renew expiry or refill reply quota. await self._run( lambda db: ( db.execute( "INSERT INTO reply_contexts(platform_id,scene,destination,msg_id,received_at) VALUES (?,?,?,?,?) " "ON CONFLICT(platform_id,scene,destination) DO UPDATE SET " "msg_id=excluded.msg_id,received_at=excluded.received_at,used=0,blocked=0 " "WHERE reply_contexts.msg_id!=excluded.msg_id AND excluded.received_at>=reply_contexts.received_at", (platform_id, scene, destination, msg_id, received_at), ).rowcount ) ) async def reserve_reply(self, platform_id, scene, destination, now, window): def reserve(db): db.execute("BEGIN IMMEDIATE") row = db.execute( "SELECT * FROM reply_contexts WHERE platform_id=? AND scene=? AND destination=?", (platform_id, scene, destination), ).fetchone() # Reserve two of QQ's five replies for command responses/other plugins. if ( not row or row["blocked"] or row["used"] >= 3 or not 0 <= now - row["received_at"] < window ): return None db.execute( "UPDATE reply_contexts SET used=used+1 WHERE platform_id=? AND scene=? AND destination=?", (platform_id, scene, destination), ) # AstrBot's official event sender uses random sequences in 1..10000. return {"msg_id": row["msg_id"], "msg_seq": 20001 + row["used"]} return await self._run(reserve) async def block_reply(self, platform_id, scene, destination, msg_id): await self._run( lambda db: ( db.execute( "UPDATE reply_contexts SET blocked=1 WHERE platform_id=? AND scene=? AND destination=? AND msg_id=?", (platform_id, scene, destination, msg_id), ).rowcount ) ) async def wait_for_reply(self, reminder_id, owner, reason): await self._run( lambda db: ( db.execute( "UPDATE reminders SET waiting_reason=?,retry_at=0,lease_owner='',lease_until=0 " "WHERE id=? AND lease_owner=? AND status='active'", (reason[:500], reminder_id, owner), ).rowcount ) ) async def resume(self, scope, creator_id, reminder_id, limit): def write(db): db.execute("BEGIN IMMEDIATE") row = db.execute( "SELECT * FROM reminders WHERE scope=? AND id=?", (scope, reminder_id) ).fetchone() if not row or (creator_id is not None and row["creator_id"] != creator_id): raise ValueError("当前会话中找不到该提醒,或你没有操作权限。") if row["status"] == "done" or (row["status"] == "cancelled" and not row["last_error"]): raise ValueError("只能重试投递失败或正在等待的提醒。") if row["lease_until"] > time.time(): raise ValueError("该提醒正在投递,请稍后重试。") count = db.execute( "SELECT COUNT(*) FROM reminders WHERE scope=? AND creator_id=? AND status='active' AND id!=?", (scope, row["creator_id"], reminder_id), ).fetchone()[0] if count >= limit: raise ValueError(f"最多只能设置 {limit} 个活跃提醒,请先取消一些。") db.execute( "UPDATE reminders SET status='active',failure_count=0,last_error='',waiting_reason=''," "retry_at=0,lease_owner='',lease_until=0 WHERE id=?", (reminder_id,), ) await self._run(write)