nxxy335
f661862d46
根因：ny_save_config 只写不校验，Db::settingsSet 内部吞异常且返回 void。 data/ 目录不可写时登录页照常显示登录成功，但下一请求读到的账号仍是空的 → 抛未配置卷王登录账号 → 前端 auth 打标 → 跳登录页 → 无限循环。 修复： - Db::settingsSet 改返回 bool 并回读校验；新增 settingsDelete - ny_save_config 双写后均做回读校验，失败写入 ny_config_save_error() - 新增 ny_config_diag()/ny_session_usable()，暴露来源/可写性/是否已存账号 - ny_load_config 把 null 账号强制成空字符串（脏数据不再伪装成未配置） - login.php 存凭据后回读确认，存不住/会话不可写直接报错而不是假成功 - login.php 账号为空时页面直接给出 data 目录写权限提示 - config_save 合并逻辑抽成 ny_apply_config_input：账号/密码不许被改空 - 鉴权失败响应带 extra.diag，前端 401 也算 auth - 前端防死循环：刚登录 120s 内或已跳 2 次则停止自动跳转，弹窗给出真实原因+诊断 测试：新增 tests/test-config-persist.php(29) 与 tools/test-login-loop.js(19)； run-all.sh 纳入两者。全套 148+48=196 项通过。
388 lines
13 KiB
PHP
388 lines
13 KiB
PHP
<?php
|
||
/**
|
||
* 数据库适配层 —— SQLite 单文件后端（零配置、无需额外服务）
|
||
*
|
||
* 设计要点：
|
||
* 1. 数据文件固定为 data/ny.db（SQLite），不需要用户配置任何连接信息。
|
||
* 2. 若环境没有 pdo_sqlite 扩展，整个层自动降级为「不可用」，
|
||
* 上层（Roster / 配置）会回退到 JSON 文件存储 —— 保证老环境不会挂。
|
||
* 3. 所有建表/加列都是幂等的（IF NOT EXISTS / 探测列后 ALTER），可重复执行。
|
||
* 4. 首次启用时自动把已有的 data/roster.json、data/config.json 迁移进来，
|
||
* 老数据不丢（迁移只发生在目标表为空时）。
|
||
*
|
||
* 表结构：
|
||
* ny_settings (k TEXT PK, v TEXT) 全局配置（键 'config' 存整份配置 JSON）
|
||
* ny_roster (sid TEXT PK, name, cls, grp, login)
|
||
* ny_project_config (project_id TEXT PK, config_json TEXT) 每份试卷独立配置（满分/合格率/免考，试卷间隔离）
|
||
*/
|
||
declare(strict_types=1);
|
||
|
||
class Db
|
||
{
|
||
private static ?PDO $pdo = null;
|
||
private static string $err = '';
|
||
private static bool $initTried = false;
|
||
|
||
public static function prefix(): string
|
||
{
|
||
return 'ny_';
|
||
}
|
||
|
||
/** 数据文件路径 */
|
||
public static function dbFile(): string
|
||
{
|
||
return NY_DATA . '/ny.db';
|
||
}
|
||
|
||
/** 是否具备启用条件（有 pdo_sqlite 扩展） */
|
||
public static function isConfigured(): bool
|
||
{
|
||
return extension_loaded('pdo_sqlite');
|
||
}
|
||
|
||
/** 是否已真正连上 */
|
||
public static function isConnected(): bool
|
||
{
|
||
return self::pdo() !== null;
|
||
}
|
||
|
||
public static function driver(): string
|
||
{
|
||
return self::pdo() !== null ? 'sqlite' : '';
|
||
}
|
||
|
||
public static function lastError(): string
|
||
{
|
||
return self::$err;
|
||
}
|
||
|
||
/** 建立/复用连接；失败返回 null 而不抛异常（上层要能优雅降级） */
|
||
public static function connect(array $p = []): PDO
|
||
{
|
||
if (self::$pdo instanceof PDO) {
|
||
return self::$pdo;
|
||
}
|
||
if (!self::isConfigured()) {
|
||
self::$err = 'PHP 未启用 pdo_sqlite 扩展，已回退到文件存储';
|
||
throw new RuntimeException(self::$err);
|
||
}
|
||
$file = self::dbFile();
|
||
$dir = dirname($file);
|
||
if (!is_dir($dir)) {
|
||
@mkdir($dir, 0777, true);
|
||
}
|
||
try {
|
||
$pdo = new PDO('sqlite:' . $file, null, null, [
|
||
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
|
||
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
|
||
PDO::ATTR_EMULATE_PREPARES => false,
|
||
]);
|
||
// WAL + 合理超时，避免多人同时刷新时 "database is locked"
|
||
$pdo->exec('PRAGMA journal_mode=WAL');
|
||
$pdo->exec('PRAGMA synchronous=NORMAL');
|
||
$pdo->exec('PRAGMA busy_timeout=5000');
|
||
$pdo->exec('PRAGMA foreign_keys=ON');
|
||
self::$pdo = $pdo;
|
||
self::$err = '';
|
||
self::migrate();
|
||
return $pdo;
|
||
} catch (Throwable $e) {
|
||
self::$pdo = null;
|
||
self::$err = $e->getMessage();
|
||
throw new RuntimeException('SQLite 连接失败：' . $e->getMessage());
|
||
}
|
||
}
|
||
|
||
/** 获取连接（不可用返回 null） */
|
||
public static function pdo(): ?PDO
|
||
{
|
||
if (self::$pdo instanceof PDO) {
|
||
return self::$pdo;
|
||
}
|
||
if (self::$initTried) {
|
||
return null; // 已经试过且失败，不再反复重试（每次请求最多试一次）
|
||
}
|
||
self::$initTried = true;
|
||
try {
|
||
return self::connect();
|
||
} catch (Throwable $e) {
|
||
return null;
|
||
}
|
||
}
|
||
|
||
/** 建表 + 索引（幂等，可重复调用） */
|
||
public static function migrate(): int
|
||
{
|
||
$pdo = self::$pdo;
|
||
if (!$pdo) {
|
||
return 0;
|
||
}
|
||
$p = self::prefix();
|
||
$pdo->exec("CREATE TABLE IF NOT EXISTS {$p}settings (
|
||
k TEXT PRIMARY KEY,
|
||
v TEXT NOT NULL DEFAULT '',
|
||
updated_at INTEGER NOT NULL DEFAULT 0
|
||
)");
|
||
$pdo->exec("CREATE TABLE IF NOT EXISTS {$p}roster (
|
||
sid TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL DEFAULT '',
|
||
cls TEXT NOT NULL DEFAULT '',
|
||
grp TEXT NOT NULL DEFAULT '',
|
||
login TEXT NOT NULL DEFAULT '',
|
||
sort INTEGER NOT NULL DEFAULT 0,
|
||
updated_at INTEGER NOT NULL DEFAULT 0
|
||
)");
|
||
$pdo->exec("CREATE TABLE IF NOT EXISTS {$p}project_config (
|
||
project_id TEXT PRIMARY KEY,
|
||
config_json TEXT NOT NULL DEFAULT '{}',
|
||
updated_at INTEGER NOT NULL DEFAULT 0
|
||
)");
|
||
$pdo->exec("CREATE INDEX IF NOT EXISTS idx_{$p}roster_cls ON {$p}roster(cls)");
|
||
self::ensureRosterLogin();
|
||
return 3;
|
||
}
|
||
|
||
/** 旧库缺少 login 列时补上（幂等） */
|
||
public static function ensureRosterLogin(): void
|
||
{
|
||
$pdo = self::$pdo;
|
||
if (!$pdo) {
|
||
return;
|
||
}
|
||
$p = self::prefix();
|
||
try {
|
||
$cols = $pdo->query("PRAGMA table_info({$p}roster)")->fetchAll(PDO::FETCH_ASSOC);
|
||
$has = false;
|
||
foreach ($cols as $c) {
|
||
if (($c['name'] ?? '') === 'login') {
|
||
$has = true;
|
||
break;
|
||
}
|
||
}
|
||
if (!$has) {
|
||
$pdo->exec("ALTER TABLE {$p}roster ADD COLUMN login TEXT NOT NULL DEFAULT ''");
|
||
}
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
}
|
||
}
|
||
|
||
// ---------------- 设置 ----------------
|
||
|
||
|
||
/**
|
||
* SQLite 用 ON CONFLICT 做 upsert（其它方言见历史版本）
|
||
*
|
||
* 返回是否真的写成功：以前这里 void + 内部吞异常，导致"配置没落盘但上层以为成功"
|
||
* （典型后果：登录成功→下一请求仍报"未配置账号"→反复跳登录页的死循环）。
|
||
* 因此改为回读校验后返回 bool，并把失败原因放进 self::lastError()。
|
||
*/
|
||
public static function settingsSet(string $k, string $v): bool
|
||
{
|
||
$pdo = self::pdo();
|
||
if (!$pdo) {
|
||
return false;
|
||
}
|
||
try {
|
||
$p = self::prefix();
|
||
$st = $pdo->prepare(
|
||
"INSERT INTO {$p}settings (k, v, updated_at) VALUES (?, ?, ?)
|
||
ON CONFLICT(k) DO UPDATE SET v=excluded.v, updated_at=excluded.updated_at"
|
||
);
|
||
$st->execute([$k, $v, time()]);
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
return false;
|
||
}
|
||
// 回读校验：磁盘满 / 目录不可写 / 只读挂载时 execute 可能不报错但实际未落盘
|
||
try {
|
||
$p = self::prefix();
|
||
$st = $pdo->prepare("SELECT v FROM {$p}settings WHERE k = ?");
|
||
$st->execute([$k]);
|
||
$got = $st->fetchColumn();
|
||
if ($got === false || (string)$got !== $v) {
|
||
self::$err = '写入后回读不一致（可能磁盘已满或数据库只读）';
|
||
return false;
|
||
}
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
return false;
|
||
}
|
||
return true;
|
||
}
|
||
|
||
public static function settingsDelete(string $k): void
|
||
{
|
||
$pdo = self::pdo();
|
||
if (!$pdo) {
|
||
return;
|
||
}
|
||
try {
|
||
$p = self::prefix();
|
||
$st = $pdo->prepare("DELETE FROM {$p}settings WHERE k = ?");
|
||
$st->execute([$k]);
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
}
|
||
}
|
||
|
||
public static function settingsGet(string $k, string $def = ''): string
|
||
{
|
||
$pdo = self::pdo();
|
||
if (!$pdo) {
|
||
return $def;
|
||
}
|
||
try {
|
||
$p = self::prefix();
|
||
$st = $pdo->prepare("SELECT v FROM {$p}settings WHERE k = ?");
|
||
$st->execute([$k]);
|
||
$v = $st->fetchColumn();
|
||
return $v === false ? $def : (string)$v;
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
return $def;
|
||
}
|
||
}
|
||
|
||
// ---------------- 名单 ----------------
|
||
|
||
public static function rosterAll(): array
|
||
{
|
||
$pdo = self::pdo();
|
||
if (!$pdo) {
|
||
return [];
|
||
}
|
||
try {
|
||
$p = self::prefix();
|
||
$st = $pdo->query("SELECT sid, name, cls, grp, login FROM {$p}roster ORDER BY sort ASC, sid ASC");
|
||
$out = [];
|
||
foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $r) {
|
||
$out[] = [
|
||
'sid' => (string)($r['sid'] ?? ''),
|
||
'name' => (string)($r['name'] ?? ''),
|
||
'cls' => (string)($r['cls'] ?? ''),
|
||
'grp' => (string)($r['grp'] ?? ''),
|
||
'login' => (string)($r['login'] ?? ''),
|
||
];
|
||
}
|
||
return $out;
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
return [];
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 全量替换名单（roster_save 语义）
|
||
* @return bool 成功 true；DB 不可用或事务失败 false（失败原因见 lastError）
|
||
*/
|
||
public static function rosterReplace(array $students): bool
|
||
{
|
||
$pdo = self::pdo();
|
||
if (!$pdo) {
|
||
self::$err = '数据库未连接';
|
||
return false;
|
||
}
|
||
try {
|
||
$p = self::prefix();
|
||
$pdo->beginTransaction();
|
||
$pdo->exec("DELETE FROM {$p}roster");
|
||
$st = $pdo->prepare(
|
||
"INSERT INTO {$p}roster (sid, name, cls, grp, login, sort, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)"
|
||
);
|
||
$i = 0;
|
||
foreach ($students as $s) {
|
||
if (!is_array($s)) {
|
||
continue;
|
||
}
|
||
$st->execute([
|
||
(string)($s['sid'] ?? ''),
|
||
(string)($s['name'] ?? ''),
|
||
(string)($s['cls'] ?? ''),
|
||
(string)($s['grp'] ?? ''),
|
||
(string)($s['login'] ?? ''),
|
||
$i++,
|
||
time(),
|
||
]);
|
||
}
|
||
$pdo->commit();
|
||
return true;
|
||
} catch (Throwable $e) {
|
||
if ($pdo->inTransaction()) {
|
||
$pdo->rollBack();
|
||
}
|
||
self::$err = $e->getMessage();
|
||
return false;
|
||
}
|
||
}
|
||
|
||
public static function rosterCount(): int
|
||
{
|
||
$pdo = self::pdo();
|
||
if (!$pdo) {
|
||
return 0;
|
||
}
|
||
try {
|
||
$p = self::prefix();
|
||
return (int)$pdo->query("SELECT COUNT(*) FROM {$p}roster")->fetchColumn();
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
return 0;
|
||
}
|
||
}
|
||
|
||
// ---------------- 每卷独立配置（满分 / 合格率 / 免考名单） ----------------
|
||
|
||
|
||
/** @return array 单卷配置（缺省返回空结构） */
|
||
public static function projectConfigGet(string $projectId): array
|
||
{
|
||
$def = ['full_score' => null, 'pass_rate' => null, 'exempt' => []];
|
||
if ($projectId === '') {
|
||
return $def;
|
||
}
|
||
$pdo = self::pdo();
|
||
if (!$pdo) {
|
||
return $def;
|
||
}
|
||
try {
|
||
$p = self::prefix();
|
||
$st = $pdo->prepare("SELECT config_json FROM {$p}project_config WHERE project_id = ?");
|
||
$st->execute([$projectId]);
|
||
$v = $st->fetchColumn();
|
||
if ($v === false) {
|
||
return $def;
|
||
}
|
||
$j = json_decode((string)$v, true);
|
||
return is_array($j) ? array_merge($def, $j) : $def;
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
return $def;
|
||
}
|
||
}
|
||
|
||
/** 写入单卷配置（整份覆盖；null 表示"用全局默认"） */
|
||
public static function projectConfigSet(string $projectId, array $cfg): void
|
||
{
|
||
if ($projectId === '') {
|
||
return;
|
||
}
|
||
$pdo = self::pdo();
|
||
if (!$pdo) {
|
||
return;
|
||
}
|
||
try {
|
||
$p = self::prefix();
|
||
$st = $pdo->prepare(
|
||
"INSERT INTO {$p}project_config (project_id, config_json, updated_at) VALUES (?, ?, ?)
|
||
ON CONFLICT(project_id) DO UPDATE SET config_json=excluded.config_json, updated_at=excluded.updated_at"
|
||
);
|
||
$st->execute([$projectId, json_encode($cfg, JSON_UNESCAPED_UNICODE), time()]);
|
||
} catch (Throwable $e) {
|
||
self::$err = $e->getMessage();
|
||
}
|
||
}
|
||
|
||
}
|