105 lines
3.6 KiB
Python
105 lines
3.6 KiB
Python
"""SQLite 数据层：连接短生命周期（每次操作新建），线程安全。"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import sqlite3
|
|
from pathlib import Path
|
|
|
|
DATA_DIR = Path(__file__).resolve().parent.parent / "data"
|
|
DB_PATH = DATA_DIR / "monitor.db"
|
|
|
|
SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS platforms (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
provider_id TEXT NOT NULL DEFAULT '',
|
|
name TEXT NOT NULL UNIQUE,
|
|
currency TEXT NOT NULL DEFAULT 'USD',
|
|
icon TEXT NOT NULL DEFAULT '',
|
|
interval_seconds INTEGER,
|
|
retry_count INTEGER,
|
|
timeout_seconds INTEGER,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
note TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS accounts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
platform_id INTEGER NOT NULL REFERENCES platforms(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
api_key TEXT NOT NULL,
|
|
threshold REAL NOT NULL DEFAULT 0,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
alert_armed INTEGER NOT NULL DEFAULT 1,
|
|
last_balance REAL,
|
|
last_status TEXT NOT NULL DEFAULT 'pending',
|
|
last_error TEXT NOT NULL DEFAULT '',
|
|
last_check_at TEXT,
|
|
note TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS balance_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
balance REAL NOT NULL,
|
|
checked_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS alert_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account_id INTEGER NOT NULL,
|
|
type TEXT NOT NULL,
|
|
message TEXT NOT NULL,
|
|
sent_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_accounts_platform ON accounts(platform_id);
|
|
CREATE INDEX IF NOT EXISTS idx_history_account ON balance_history(account_id, id);
|
|
CREATE INDEX IF NOT EXISTS idx_alert_account ON alert_log(account_id);
|
|
"""
|
|
|
|
|
|
def get_conn() -> sqlite3.Connection:
|
|
conn = sqlite3.connect(DB_PATH, timeout=30)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA foreign_keys = ON")
|
|
return conn
|
|
|
|
|
|
def init_db() -> None:
|
|
DATA_DIR.mkdir(parents=True, exist_ok=True)
|
|
with get_conn() as conn:
|
|
conn.executescript(SCHEMA)
|
|
|
|
|
|
def sync_platforms() -> None:
|
|
"""把代码内置的 provider 自动同步为平台记录（缺失时创建）。
|
|
|
|
平台 = 代码注册表，无需用户手动添加；删除也会在下一次启动时重建。
|
|
"""
|
|
from app.providers import list_providers
|
|
|
|
with get_conn() as conn:
|
|
existing = {
|
|
r["provider_id"]
|
|
for r in conn.execute(
|
|
"SELECT provider_id FROM platforms WHERE provider_id != ''"
|
|
).fetchall()
|
|
}
|
|
for p in list_providers():
|
|
if p["id"] in existing:
|
|
continue
|
|
conn.execute(
|
|
"INSERT INTO platforms (provider_id, name, currency, icon, enabled) "
|
|
"VALUES (?, ?, ?, ?, 1)",
|
|
(p["id"], p["name"], p["currency"], p["icon"]),
|
|
)
|
|
existing.add(p["id"])
|
|
|
|
|
|
def rows_to_dicts(rows: list[sqlite3.Row]) -> list[dict]:
|
|
return [dict(r) for r in rows]
|