jiahong
709 lines
27 KiB
TypeScript
709 lines
27 KiB
TypeScript
/**
|
||
* 自然写互动课堂PC端应用软件 V1.0
|
||
* 数据库管理模块 - 基于better-sqlite3实现SQLite本地数据持久化
|
||
*
|
||
* 功能说明：
|
||
* 1. 数据库初始化与版本迁移（Schema Migration）
|
||
* 2. 学生笔迹数据的存储与检索（支持按学生/作业/时间维度查询）
|
||
* 3. 作业批改记录管理（AI批改 + 人工标注）
|
||
* 4. 班级/学生信息本地缓存（减少网络请求）
|
||
* 5. 点阵码映射关系维护（课件页面与点阵码对应）
|
||
* 6. 课件元数据索引（本地课件文件的管理信息）
|
||
* 7. 数据库文件加密（SQLCipher集成，防止本地数据泄露）
|
||
* 8. 自动备份与数据清理策略
|
||
*/
|
||
|
||
import path from 'path';
|
||
import fs from 'fs';
|
||
import { app } from 'electron';
|
||
import crypto from 'crypto';
|
||
|
||
/* ========== 类型定义 ========== */
|
||
|
||
/** 数据库配置接口 */
|
||
interface DatabaseConfig {
|
||
dbPath: string; // 数据库文件路径
|
||
encryptionKey: string; // 加密密钥（SQLCipher）
|
||
maxBackups: number; // 最大备份数量
|
||
autoVacuumInterval: number; // 自动整理间隔（毫秒）
|
||
walMode: boolean; // 是否启用WAL模式
|
||
}
|
||
|
||
/** 学生笔迹记录 */
|
||
interface StrokeRecord {
|
||
id: string;
|
||
studentId: string;
|
||
studentName: string;
|
||
assignmentId: string;
|
||
pageIndex: number;
|
||
strokeData: string; // JSON序列化的笔迹坐标数据
|
||
thumbnailPath: string; // 缩略图文件路径
|
||
collectTime: number; // 采集时间戳
|
||
syncStatus: number; // 同步状态: 0=未同步, 1=已同步, 2=同步失败
|
||
fileSize: number; // 数据大小（字节）
|
||
}
|
||
|
||
/** 批改记录 */
|
||
interface GradeRecord {
|
||
id: string;
|
||
assignmentId: string;
|
||
studentId: string;
|
||
aiScore: number; // AI评分（0-100）
|
||
teacherScore: number; // 教师评分（-1表示未批改）
|
||
aiAnnotation: string; // AI批改标注JSON
|
||
teacherAnnotation: string; // 教师手动标注JSON
|
||
gradeTime: number;
|
||
status: number; // 0=待批改, 1=AI已批, 2=教师已批
|
||
}
|
||
|
||
/** 班级信息 */
|
||
interface ClassInfo {
|
||
classId: string;
|
||
className: string;
|
||
grade: string;
|
||
teacherId: string;
|
||
studentCount: number;
|
||
lastSyncTime: number;
|
||
}
|
||
|
||
/** 学生信息 */
|
||
interface StudentInfo {
|
||
studentId: string;
|
||
studentName: string;
|
||
classId: string;
|
||
seatNumber: number;
|
||
penDeviceId: string; // 绑定的点阵笔设备ID
|
||
avatarPath: string;
|
||
}
|
||
|
||
/** 点阵码映射 */
|
||
interface DotCodeMapping {
|
||
dotCodeId: string; // 点阵码唯一标识
|
||
coursewareId: string; // 课件ID
|
||
pageIndex: number; // 对应页面索引
|
||
regionType: string; // 区域类型: 'answer'/'writing'/'drawing'
|
||
coordinates: string; // 区域坐标JSON
|
||
}
|
||
|
||
/** 课件元数据 */
|
||
interface CoursewareMeta {
|
||
coursewareId: string;
|
||
title: string;
|
||
type: string; // 'ppt'/'pdf'/'custom'
|
||
filePath: string; // 本地文件路径
|
||
pageCount: number;
|
||
fileSize: number;
|
||
createTime: number;
|
||
lastOpenTime: number;
|
||
cloudUrl: string; // 云端地址
|
||
syncStatus: number;
|
||
}
|
||
|
||
/** 迁移脚本定义 */
|
||
interface Migration {
|
||
version: number;
|
||
description: string;
|
||
sql: string;
|
||
}
|
||
|
||
/* ========== 数据库管理器 ========== */
|
||
|
||
// 数据库Schema版本号，每次表结构变更递增
|
||
const CURRENT_SCHEMA_VERSION = 5;
|
||
|
||
/**
|
||
* 数据库管理器 - 统一管理SQLite数据库的生命周期
|
||
* 采用单例模式确保全局唯一数据库连接
|
||
*/
|
||
class DatabaseManager {
|
||
private db: any = null; // better-sqlite3 数据库实例
|
||
private config: DatabaseConfig; // 数据库配置
|
||
private backupTimer: ReturnType<typeof setInterval> | null = null;
|
||
private vacuumTimer: ReturnType<typeof setInterval> | null = null;
|
||
private initialized: boolean = false;
|
||
|
||
constructor() {
|
||
// 默认配置：数据库存储在应用数据目录
|
||
const userDataPath = app.getPath('userData');
|
||
this.config = {
|
||
dbPath: path.join(userDataPath, 'writech_data.db'),
|
||
encryptionKey: this.loadOrCreateEncryptionKey(),
|
||
maxBackups: 5,
|
||
autoVacuumInterval: 24 * 60 * 60 * 1000, // 每24小时整理一次
|
||
walMode: true
|
||
};
|
||
}
|
||
|
||
/**
|
||
* 加载或创建数据库加密密钥
|
||
* 密钥存储在操作系统安全凭据管理器中（通过keytar）
|
||
* 首次运行时生成随机256位密钥
|
||
*/
|
||
private loadOrCreateEncryptionKey(): string {
|
||
const keyFilePath = path.join(app.getPath('userData'), '.db_key');
|
||
try {
|
||
if (fs.existsSync(keyFilePath)) {
|
||
return fs.readFileSync(keyFilePath, 'utf-8').trim();
|
||
}
|
||
// 生成256位随机密钥并保存
|
||
const newKey = crypto.randomBytes(32).toString('hex');
|
||
fs.writeFileSync(keyFilePath, newKey, { mode: 0o600 });
|
||
console.log('[DatabaseManager] 已生成新的数据库加密密钥');
|
||
return newKey;
|
||
} catch (error) {
|
||
console.error('[DatabaseManager] 密钥管理失败，使用默认密钥:', error);
|
||
return 'writech_default_key_2024';
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 初始化数据库连接并执行迁移
|
||
* 启用WAL模式提高并发读写性能
|
||
* 设置SQLCipher加密密钥
|
||
*/
|
||
async initialize(): Promise<void> {
|
||
if (this.initialized) return;
|
||
|
||
try {
|
||
const Database = require('better-sqlite3');
|
||
const dbDir = path.dirname(this.config.dbPath);
|
||
if (!fs.existsSync(dbDir)) {
|
||
fs.mkdirSync(dbDir, { recursive: true });
|
||
}
|
||
|
||
// 创建数据库连接（启用verbose日志用于调试）
|
||
this.db = new Database(this.config.dbPath, { verbose: undefined });
|
||
|
||
// 设置SQLCipher加密密钥
|
||
this.db.pragma(`key='${this.config.encryptionKey}'`);
|
||
|
||
// 启用WAL模式提高并发性能
|
||
if (this.config.walMode) {
|
||
this.db.pragma('journal_mode=WAL');
|
||
this.db.pragma('synchronous=NORMAL');
|
||
}
|
||
|
||
// 启用外键约束
|
||
this.db.pragma('foreign_keys=ON');
|
||
|
||
// 执行数据库迁移
|
||
this.runMigrations();
|
||
|
||
// 启动定时任务（备份 + 整理）
|
||
this.startAutoBackup();
|
||
this.startAutoVacuum();
|
||
|
||
this.initialized = true;
|
||
console.log('[DatabaseManager] 数据库初始化完成，版本:', CURRENT_SCHEMA_VERSION);
|
||
} catch (error) {
|
||
console.error('[DatabaseManager] 数据库初始化失败:', error);
|
||
throw error;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* 获取所有迁移脚本列表
|
||
* 每个版本对应一个迁移脚本，按版本号顺序执行
|
||
*/
|
||
private getMigrations(): Migration[] {
|
||
return [
|
||
{
|
||
version: 1,
|
||
description: '创建基础表结构',
|
||
sql: `
|
||
-- 学生笔迹数据表
|
||
CREATE TABLE IF NOT EXISTS stroke_records (
|
||
id TEXT PRIMARY KEY,
|
||
student_id TEXT NOT NULL,
|
||
student_name TEXT NOT NULL,
|
||
assignment_id TEXT NOT NULL,
|
||
page_index INTEGER DEFAULT 0,
|
||
stroke_data TEXT NOT NULL,
|
||
thumbnail_path TEXT DEFAULT '',
|
||
collect_time INTEGER NOT NULL,
|
||
sync_status INTEGER DEFAULT 0,
|
||
file_size INTEGER DEFAULT 0,
|
||
created_at INTEGER DEFAULT (strftime('%s','now'))
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_stroke_student ON stroke_records(student_id);
|
||
CREATE INDEX IF NOT EXISTS idx_stroke_assignment ON stroke_records(assignment_id);
|
||
CREATE INDEX IF NOT EXISTS idx_stroke_time ON stroke_records(collect_time);
|
||
|
||
-- 批改记录表
|
||
CREATE TABLE IF NOT EXISTS grade_records (
|
||
id TEXT PRIMARY KEY,
|
||
assignment_id TEXT NOT NULL,
|
||
student_id TEXT NOT NULL,
|
||
ai_score REAL DEFAULT -1,
|
||
teacher_score REAL DEFAULT -1,
|
||
ai_annotation TEXT DEFAULT '{}',
|
||
teacher_annotation TEXT DEFAULT '{}',
|
||
grade_time INTEGER NOT NULL,
|
||
status INTEGER DEFAULT 0,
|
||
created_at INTEGER DEFAULT (strftime('%s','now'))
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_grade_assignment ON grade_records(assignment_id);
|
||
CREATE INDEX IF NOT EXISTS idx_grade_student ON grade_records(student_id);
|
||
`
|
||
},
|
||
{
|
||
version: 2,
|
||
description: '添加班级和学生信息表',
|
||
sql: `
|
||
-- 班级信息缓存表
|
||
CREATE TABLE IF NOT EXISTS class_info (
|
||
class_id TEXT PRIMARY KEY,
|
||
class_name TEXT NOT NULL,
|
||
grade TEXT DEFAULT '',
|
||
teacher_id TEXT NOT NULL,
|
||
student_count INTEGER DEFAULT 0,
|
||
last_sync_time INTEGER DEFAULT 0
|
||
);
|
||
|
||
-- 学生信息缓存表
|
||
CREATE TABLE IF NOT EXISTS student_info (
|
||
student_id TEXT PRIMARY KEY,
|
||
student_name TEXT NOT NULL,
|
||
class_id TEXT NOT NULL,
|
||
seat_number INTEGER DEFAULT 0,
|
||
pen_device_id TEXT DEFAULT '',
|
||
avatar_path TEXT DEFAULT '',
|
||
FOREIGN KEY (class_id) REFERENCES class_info(class_id)
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_student_class ON student_info(class_id);
|
||
CREATE INDEX IF NOT EXISTS idx_student_pen ON student_info(pen_device_id);
|
||
`
|
||
},
|
||
{
|
||
version: 3,
|
||
description: '添加点阵码映射表',
|
||
sql: `
|
||
-- 点阵码映射关系表（课件页面与点阵码ID对应）
|
||
CREATE TABLE IF NOT EXISTS dot_code_mapping (
|
||
dot_code_id TEXT PRIMARY KEY,
|
||
courseware_id TEXT NOT NULL,
|
||
page_index INTEGER NOT NULL,
|
||
region_type TEXT DEFAULT 'answer',
|
||
coordinates TEXT DEFAULT '{}',
|
||
created_at INTEGER DEFAULT (strftime('%s','now'))
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_dotcode_courseware ON dot_code_mapping(courseware_id);
|
||
`
|
||
},
|
||
{
|
||
version: 4,
|
||
description: '添加课件元数据表',
|
||
sql: `
|
||
-- 课件元数据索引表
|
||
CREATE TABLE IF NOT EXISTS courseware_meta (
|
||
courseware_id TEXT PRIMARY KEY,
|
||
title TEXT NOT NULL,
|
||
type TEXT DEFAULT 'custom',
|
||
file_path TEXT NOT NULL,
|
||
page_count INTEGER DEFAULT 0,
|
||
file_size INTEGER DEFAULT 0,
|
||
create_time INTEGER NOT NULL,
|
||
last_open_time INTEGER DEFAULT 0,
|
||
cloud_url TEXT DEFAULT '',
|
||
sync_status INTEGER DEFAULT 0
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_courseware_type ON courseware_meta(type);
|
||
CREATE INDEX IF NOT EXISTS idx_courseware_time ON courseware_meta(last_open_time);
|
||
`
|
||
},
|
||
{
|
||
version: 5,
|
||
description: '添加同步日志表用于离线数据追踪',
|
||
sql: `
|
||
-- 数据同步日志表（记录所有待同步操作）
|
||
CREATE TABLE IF NOT EXISTS sync_log (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
table_name TEXT NOT NULL,
|
||
record_id TEXT NOT NULL,
|
||
operation TEXT NOT NULL,
|
||
payload TEXT DEFAULT '{}',
|
||
sync_status INTEGER DEFAULT 0,
|
||
retry_count INTEGER DEFAULT 0,
|
||
created_at INTEGER DEFAULT (strftime('%s','now')),
|
||
synced_at INTEGER DEFAULT 0
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_sync_status ON sync_log(sync_status);
|
||
`
|
||
}
|
||
];
|
||
}
|
||
|
||
/**
|
||
* 执行数据库迁移
|
||
* 检查当前版本号，依次执行未执行的迁移脚本
|
||
* 使用事务确保迁移的原子性
|
||
*/
|
||
private runMigrations(): void {
|
||
// 创建版本跟踪表
|
||
this.db.exec(`
|
||
CREATE TABLE IF NOT EXISTS schema_version (
|
||
version INTEGER PRIMARY KEY,
|
||
description TEXT,
|
||
applied_at INTEGER DEFAULT (strftime('%s','now'))
|
||
);
|
||
`);
|
||
|
||
// 获取当前数据库版本
|
||
const row = this.db.prepare('SELECT MAX(version) as ver FROM schema_version').get();
|
||
const currentVersion = row?.ver || 0;
|
||
|
||
if (currentVersion >= CURRENT_SCHEMA_VERSION) {
|
||
console.log('[DatabaseManager] 数据库已是最新版本:', currentVersion);
|
||
return;
|
||
}
|
||
|
||
// 获取待执行的迁移脚本并按版本排序执行
|
||
const migrations = this.getMigrations().filter(m => m.version > currentVersion);
|
||
const runAll = this.db.transaction(() => {
|
||
for (const migration of migrations) {
|
||
console.log(`[DatabaseManager] 执行迁移 v${migration.version}: ${migration.description}`);
|
||
this.db.exec(migration.sql);
|
||
this.db.prepare('INSERT INTO schema_version (version, description) VALUES (?, ?)')
|
||
.run(migration.version, migration.description);
|
||
}
|
||
});
|
||
|
||
runAll();
|
||
console.log(`[DatabaseManager] 迁移完成: v${currentVersion} -> v${CURRENT_SCHEMA_VERSION}`);
|
||
}
|
||
|
||
/* ========== 笔迹数据操作 ========== */
|
||
|
||
/** 保存学生笔迹记录（批量插入，提高写入性能） */
|
||
saveStrokeRecords(records: StrokeRecord[]): number {
|
||
const insertStmt = this.db.prepare(`
|
||
INSERT OR REPLACE INTO stroke_records
|
||
(id, student_id, student_name, assignment_id, page_index,
|
||
stroke_data, thumbnail_path, collect_time, sync_status, file_size)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`);
|
||
|
||
// 使用事务批量插入，避免逐条写入导致的性能问题
|
||
const insertMany = this.db.transaction((items: StrokeRecord[]) => {
|
||
let count = 0;
|
||
for (const r of items) {
|
||
insertStmt.run(
|
||
r.id, r.studentId, r.studentName, r.assignmentId,
|
||
r.pageIndex, r.strokeData, r.thumbnailPath,
|
||
r.collectTime, r.syncStatus, r.fileSize
|
||
);
|
||
count++;
|
||
}
|
||
// 同时记录同步日志
|
||
const logStmt = this.db.prepare(`
|
||
INSERT INTO sync_log (table_name, record_id, operation, payload)
|
||
VALUES ('stroke_records', ?, 'INSERT', ?)
|
||
`);
|
||
for (const r of items) {
|
||
logStmt.run(r.id, JSON.stringify({ assignmentId: r.assignmentId }));
|
||
}
|
||
return count;
|
||
});
|
||
|
||
return insertMany(records);
|
||
}
|
||
|
||
/** 按作业ID查询笔迹（支持分页） */
|
||
getStrokesByAssignment(assignmentId: string, page: number = 0, pageSize: number = 50): StrokeRecord[] {
|
||
const offset = page * pageSize;
|
||
return this.db.prepare(`
|
||
SELECT id, student_id as studentId, student_name as studentName,
|
||
assignment_id as assignmentId, page_index as pageIndex,
|
||
stroke_data as strokeData, thumbnail_path as thumbnailPath,
|
||
collect_time as collectTime, sync_status as syncStatus,
|
||
file_size as fileSize
|
||
FROM stroke_records
|
||
WHERE assignment_id = ?
|
||
ORDER BY collect_time DESC
|
||
LIMIT ? OFFSET ?
|
||
`).all(assignmentId, pageSize, offset);
|
||
}
|
||
|
||
/** 查询某学生的所有笔迹（用于学情分析） */
|
||
getStrokesByStudent(studentId: string, startTime?: number, endTime?: number): StrokeRecord[] {
|
||
let sql = `SELECT * FROM stroke_records WHERE student_id = ?`;
|
||
const params: any[] = [studentId];
|
||
if (startTime) {
|
||
sql += ' AND collect_time >= ?';
|
||
params.push(startTime);
|
||
}
|
||
if (endTime) {
|
||
sql += ' AND collect_time <= ?';
|
||
params.push(endTime);
|
||
}
|
||
sql += ' ORDER BY collect_time DESC';
|
||
return this.db.prepare(sql).all(...params);
|
||
}
|
||
|
||
/** 获取未同步的笔迹记录（用于断网重连后批量上传） */
|
||
getUnsyncedStrokes(limit: number = 100): StrokeRecord[] {
|
||
return this.db.prepare(`
|
||
SELECT * FROM stroke_records
|
||
WHERE sync_status = 0
|
||
ORDER BY collect_time ASC
|
||
LIMIT ?
|
||
`).all(limit);
|
||
}
|
||
|
||
/** 批量更新笔迹同步状态 */
|
||
updateStrokeSyncStatus(ids: string[], status: number): void {
|
||
const placeholders = ids.map(() => '?').join(',');
|
||
this.db.prepare(`
|
||
UPDATE stroke_records SET sync_status = ?
|
||
WHERE id IN (${placeholders})
|
||
`).run(status, ...ids);
|
||
}
|
||
|
||
/* ========== 批改记录操作 ========== */
|
||
|
||
/** 保存或更新批改记录 */
|
||
saveGradeRecord(record: GradeRecord): void {
|
||
this.db.prepare(`
|
||
INSERT OR REPLACE INTO grade_records
|
||
(id, assignment_id, student_id, ai_score, teacher_score,
|
||
ai_annotation, teacher_annotation, grade_time, status)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`).run(
|
||
record.id, record.assignmentId, record.studentId,
|
||
record.aiScore, record.teacherScore,
|
||
record.aiAnnotation, record.teacherAnnotation,
|
||
record.gradeTime, record.status
|
||
);
|
||
}
|
||
|
||
/** 查询作业的批改结果列表 */
|
||
getGradesByAssignment(assignmentId: string): GradeRecord[] {
|
||
return this.db.prepare(`
|
||
SELECT g.*, s.student_name as studentName
|
||
FROM grade_records g
|
||
LEFT JOIN student_info s ON g.student_id = s.student_id
|
||
WHERE g.assignment_id = ?
|
||
ORDER BY g.grade_time DESC
|
||
`).all(assignmentId);
|
||
}
|
||
|
||
/** 获取待教师批改的记录数 */
|
||
getPendingGradeCount(): number {
|
||
const row = this.db.prepare(`
|
||
SELECT COUNT(*) as cnt FROM grade_records WHERE status < 2
|
||
`).get();
|
||
return row?.cnt || 0;
|
||
}
|
||
|
||
/* ========== 班级/学生信息操作 ========== */
|
||
|
||
/** 批量同步班级信息（从云端拉取后缓存到本地） */
|
||
syncClassInfo(classes: ClassInfo[]): void {
|
||
const upsert = this.db.prepare(`
|
||
INSERT OR REPLACE INTO class_info
|
||
(class_id, class_name, grade, teacher_id, student_count, last_sync_time)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
`);
|
||
const syncAll = this.db.transaction((items: ClassInfo[]) => {
|
||
for (const c of items) {
|
||
upsert.run(c.classId, c.className, c.grade, c.teacherId, c.studentCount, Date.now());
|
||
}
|
||
});
|
||
syncAll(classes);
|
||
}
|
||
|
||
/** 批量同步学生信息 */
|
||
syncStudentInfo(students: StudentInfo[]): void {
|
||
const upsert = this.db.prepare(`
|
||
INSERT OR REPLACE INTO student_info
|
||
(student_id, student_name, class_id, seat_number, pen_device_id, avatar_path)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
`);
|
||
const syncAll = this.db.transaction((items: StudentInfo[]) => {
|
||
for (const s of items) {
|
||
upsert.run(s.studentId, s.studentName, s.classId, s.seatNumber, s.penDeviceId, s.avatarPath);
|
||
}
|
||
});
|
||
syncAll(students);
|
||
}
|
||
|
||
/** 按班级查询学生列表 */
|
||
getStudentsByClass(classId: string): StudentInfo[] {
|
||
return this.db.prepare(`
|
||
SELECT * FROM student_info WHERE class_id = ? ORDER BY seat_number
|
||
`).all(classId);
|
||
}
|
||
|
||
/** 通过点阵笔设备ID查找学生（用于实时笔迹识别） */
|
||
findStudentByPenDevice(penDeviceId: string): StudentInfo | undefined {
|
||
return this.db.prepare(`
|
||
SELECT * FROM student_info WHERE pen_device_id = ?
|
||
`).get(penDeviceId);
|
||
}
|
||
|
||
/* ========== 点阵码映射操作 ========== */
|
||
|
||
/** 保存点阵码映射关系 */
|
||
saveDotCodeMappings(mappings: DotCodeMapping[]): void {
|
||
const upsert = this.db.prepare(`
|
||
INSERT OR REPLACE INTO dot_code_mapping
|
||
(dot_code_id, courseware_id, page_index, region_type, coordinates)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
`);
|
||
const saveAll = this.db.transaction((items: DotCodeMapping[]) => {
|
||
for (const m of items) {
|
||
upsert.run(m.dotCodeId, m.coursewareId, m.pageIndex, m.regionType, m.coordinates);
|
||
}
|
||
});
|
||
saveAll(mappings);
|
||
}
|
||
|
||
/** 根据点阵码ID查找对应的课件页面（笔迹数据落点定位） */
|
||
findPageByDotCode(dotCodeId: string): DotCodeMapping | undefined {
|
||
return this.db.prepare(`
|
||
SELECT * FROM dot_code_mapping WHERE dot_code_id = ?
|
||
`).get(dotCodeId);
|
||
}
|
||
|
||
/* ========== 课件元数据操作 ========== */
|
||
|
||
/** 保存课件元数据 */
|
||
saveCoursewareMeta(meta: CoursewareMeta): void {
|
||
this.db.prepare(`
|
||
INSERT OR REPLACE INTO courseware_meta
|
||
(courseware_id, title, type, file_path, page_count, file_size,
|
||
create_time, last_open_time, cloud_url, sync_status)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
`).run(
|
||
meta.coursewareId, meta.title, meta.type, meta.filePath,
|
||
meta.pageCount, meta.fileSize, meta.createTime,
|
||
meta.lastOpenTime, meta.cloudUrl, meta.syncStatus
|
||
);
|
||
}
|
||
|
||
/** 获取最近打开的课件列表 */
|
||
getRecentCoursewares(limit: number = 20): CoursewareMeta[] {
|
||
return this.db.prepare(`
|
||
SELECT * FROM courseware_meta ORDER BY last_open_time DESC LIMIT ?
|
||
`).all(limit);
|
||
}
|
||
|
||
/* ========== 数据库维护操作 ========== */
|
||
|
||
/** 启动自动备份定时器（每6小时备份一次） */
|
||
private startAutoBackup(): void {
|
||
const BACKUP_INTERVAL = 6 * 60 * 60 * 1000; // 6小时
|
||
this.backupTimer = setInterval(() => {
|
||
this.createBackup();
|
||
}, BACKUP_INTERVAL);
|
||
}
|
||
|
||
/** 创建数据库备份文件 */
|
||
createBackup(): string {
|
||
const backupDir = path.join(path.dirname(this.config.dbPath), 'backups');
|
||
if (!fs.existsSync(backupDir)) {
|
||
fs.mkdirSync(backupDir, { recursive: true });
|
||
}
|
||
|
||
// 生成备份文件名（包含时间戳）
|
||
const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
|
||
const backupPath = path.join(backupDir, `writech_backup_${timestamp}.db`);
|
||
|
||
// 使用SQLite的backup API执行在线备份（不阻塞读写）
|
||
this.db.backup(backupPath);
|
||
console.log('[DatabaseManager] 数据库备份完成:', backupPath);
|
||
|
||
// 清理过期备份（保留最近N个）
|
||
this.cleanOldBackups(backupDir);
|
||
return backupPath;
|
||
}
|
||
|
||
/** 清理过期的备份文件 */
|
||
private cleanOldBackups(backupDir: string): void {
|
||
const files = fs.readdirSync(backupDir)
|
||
.filter(f => f.startsWith('writech_backup_'))
|
||
.sort()
|
||
.reverse();
|
||
|
||
// 删除超出最大数量的旧备份
|
||
for (let i = this.config.maxBackups; i < files.length; i++) {
|
||
const filePath = path.join(backupDir, files[i]);
|
||
fs.unlinkSync(filePath);
|
||
console.log('[DatabaseManager] 已清理过期备份:', files[i]);
|
||
}
|
||
}
|
||
|
||
/** 启动自动数据库整理（VACUUM） */
|
||
private startAutoVacuum(): void {
|
||
this.vacuumTimer = setInterval(() => {
|
||
try {
|
||
// 清理30天前已同步的笔迹原始数据（缩略图保留）
|
||
const threshold = Date.now() - 30 * 24 * 60 * 60 * 1000;
|
||
const result = this.db.prepare(`
|
||
DELETE FROM stroke_records
|
||
WHERE sync_status = 1 AND collect_time < ?
|
||
`).run(threshold);
|
||
if (result.changes > 0) {
|
||
console.log(`[DatabaseManager] 清理过期笔迹记录: ${result.changes}条`);
|
||
}
|
||
|
||
// 清理已同步的同步日志
|
||
this.db.prepare(`
|
||
DELETE FROM sync_log WHERE sync_status = 1 AND synced_at < ?
|
||
`).run(threshold);
|
||
|
||
// 执行VACUUM整理磁盘空间
|
||
this.db.exec('VACUUM');
|
||
console.log('[DatabaseManager] 数据库整理完成');
|
||
} catch (error) {
|
||
console.error('[DatabaseManager] 数据库整理失败:', error);
|
||
}
|
||
}, this.config.autoVacuumInterval);
|
||
}
|
||
|
||
/** 获取数据库统计信息（用于状态显示） */
|
||
getStatistics(): Record<string, number> {
|
||
const stats: Record<string, number> = {};
|
||
stats.strokeCount = this.db.prepare('SELECT COUNT(*) as c FROM stroke_records').get().c;
|
||
stats.gradeCount = this.db.prepare('SELECT COUNT(*) as c FROM grade_records').get().c;
|
||
stats.studentCount = this.db.prepare('SELECT COUNT(*) as c FROM student_info').get().c;
|
||
stats.coursewareCount = this.db.prepare('SELECT COUNT(*) as c FROM courseware_meta').get().c;
|
||
stats.unsyncedCount = this.db.prepare('SELECT COUNT(*) as c FROM sync_log WHERE sync_status=0').get().c;
|
||
|
||
// 计算数据库文件大小
|
||
try {
|
||
const stat = fs.statSync(this.config.dbPath);
|
||
stats.dbSizeBytes = stat.size;
|
||
} catch {
|
||
stats.dbSizeBytes = 0;
|
||
}
|
||
return stats;
|
||
}
|
||
|
||
/** 关闭数据库连接并清理资源 */
|
||
close(): void {
|
||
if (this.backupTimer) {
|
||
clearInterval(this.backupTimer);
|
||
this.backupTimer = null;
|
||
}
|
||
if (this.vacuumTimer) {
|
||
clearInterval(this.vacuumTimer);
|
||
this.vacuumTimer = null;
|
||
}
|
||
if (this.db) {
|
||
// 关闭前执行一次checkpoint确保WAL数据写入
|
||
try { this.db.pragma('wal_checkpoint(TRUNCATE)'); } catch {}
|
||
this.db.close();
|
||
this.db = null;
|
||
}
|
||
this.initialized = false;
|
||
console.log('[DatabaseManager] 数据库连接已关闭');
|
||
}
|
||
}
|
||
|
||
/* ========== 单例导出 ========== */
|
||
|
||
/** 全局数据库管理器实例 */
|
||
const dbManager = new DatabaseManager();
|
||
export default dbManager;
|