349 lines
10 KiB
TypeScript
349 lines
10 KiB
TypeScript
|
export interface DBSchema {
|
|||
|
|
dbName: string;
|
|||
|
|
dbPath: string;
|
|||
|
|
tableName: string;
|
|||
|
|
columns: string; // 建表 SQL
|
|||
|
|
indexes?: string[]; // 索引
|
|||
|
|
jsonFields?: string[]; // 需要自动 JSON 序列化的字段名
|
|||
|
|
primaryKey: string; // 主键
|
|||
|
|
immutableFields?: string[]; // 只插入不更新的值
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 导入输入下面这个👇
|
|||
|
|
// import { DBManager , DBSchema} from '@/src/service/DBManager/DBManager'
|
|||
|
|
|
|||
|
|
export class DBManager {
|
|||
|
|
private tableColumns!: string[];
|
|||
|
|
constructor(private config: DBSchema) {}
|
|||
|
|
|
|||
|
|
// 外部调用的开启数据库的接口
|
|||
|
|
async open(): Promise<void> {
|
|||
|
|
this.compileColumns();// 建立Scheme规范
|
|||
|
|
await this.connectDB(); // 先链接
|
|||
|
|
const sqlList = this.generateInitSqls(); // 后建表
|
|||
|
|
if (sqlList.length > 0) { await this.executeSql(sqlList);} // 再建索引
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/* ================================= 基础 CRUD 封装 ================================= */
|
|||
|
|
|
|||
|
|
async Upsert(data: any): Promise<void> {
|
|||
|
|
const row = this.serialize(data);
|
|||
|
|
const keys = Object.keys(row);
|
|||
|
|
if (keys.length === 0) return;
|
|||
|
|
const values = keys.map(k => this.formatValue(row[k]));
|
|||
|
|
const insertSql =
|
|||
|
|
`INSERT INTO ${this.config.tableName} (${keys.join(',')})
|
|||
|
|
VALUES (${values.join(',')})`;
|
|||
|
|
|
|||
|
|
const updateCols = keys.filter(
|
|||
|
|
k =>
|
|||
|
|
k !== this.config.primaryKey &&
|
|||
|
|
!this.config.immutableFields?.includes(k)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
if (updateCols.length === 0) {
|
|||
|
|
// 只有主键或不可变字段，直接插
|
|||
|
|
return this.executeSql(insertSql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const updateSet = updateCols
|
|||
|
|
.map(k => `${k} = excluded.${k}`)
|
|||
|
|
.join(',');
|
|||
|
|
|
|||
|
|
const sql = `
|
|||
|
|
${insertSql}
|
|||
|
|
ON CONFLICT(${this.config.primaryKey})
|
|||
|
|
DO UPDATE SET ${updateSet}
|
|||
|
|
`;
|
|||
|
|
return this.executeSql(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
async UpsertOnlyChanged(data: any): Promise<void> {
|
|||
|
|
const row = this.serialize(data);
|
|||
|
|
const keys = Object.keys(row);
|
|||
|
|
if (keys.length === 0) return;
|
|||
|
|
|
|||
|
|
const pk = this.config.primaryKey;
|
|||
|
|
if (!pk || !keys.includes(pk)) {
|
|||
|
|
throw new Error('[DB] UpsertOnlyChanged 需要 primaryKey');
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
const values = keys.map(k => this.formatValue(row[k]));
|
|||
|
|
|
|||
|
|
const insertSql =
|
|||
|
|
`INSERT INTO ${this.config.tableName} (${keys.join(',')})
|
|||
|
|
VALUES (${values.join(',')})`;
|
|||
|
|
|
|||
|
|
const updateCols = keys.filter(
|
|||
|
|
k =>
|
|||
|
|
k !== pk &&
|
|||
|
|
!this.config.immutableFields?.includes(k)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
if (updateCols.length === 0) {
|
|||
|
|
return this.executeSql(insertSql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// SET 子句
|
|||
|
|
const setClause = updateCols
|
|||
|
|
.map(k => `${k} = excluded.${k}`)
|
|||
|
|
.join(', ');
|
|||
|
|
|
|||
|
|
// 变化检测（核心）
|
|||
|
|
const diffWhere = updateCols
|
|||
|
|
.map(k => `${k} IS NOT excluded.${k}`)
|
|||
|
|
.join(' OR ');
|
|||
|
|
|
|||
|
|
const sql = `
|
|||
|
|
${insertSql}
|
|||
|
|
ON CONFLICT(${pk})
|
|||
|
|
DO UPDATE SET ${setClause}
|
|||
|
|
WHERE ${diffWhere}
|
|||
|
|
`;
|
|||
|
|
return this.executeSql(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
/** 批量保存 */
|
|||
|
|
async saveBatch(list: any[]): Promise<void> {
|
|||
|
|
if (!list || list.length === 0) return;
|
|||
|
|
await this.executeSql("BEGIN TRANSACTION;");
|
|||
|
|
try {
|
|||
|
|
for (const item of list) { await this.Upsert(item);}
|
|||
|
|
|
|||
|
|
await this.executeSql("COMMIT;");
|
|||
|
|
} catch (e) {
|
|||
|
|
await this.executeSql("ROLLBACK;");
|
|||
|
|
throw e;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 批量查询多个 target 的最新 N 条记录
|
|||
|
|
* @param targetField 目标字段名 (如 'target_guid')
|
|||
|
|
* @param targetIds 目标ID数组
|
|||
|
|
* @param limit 每个 target 取多少条
|
|||
|
|
* @param orderByField 排序字段 (默认按此字段倒序)
|
|||
|
|
* @returns 所有结果的数组
|
|||
|
|
*/
|
|||
|
|
async findBatchLatest<T>(
|
|||
|
|
targetField: string,
|
|||
|
|
targetIds: string[],
|
|||
|
|
limit: number = 50,
|
|||
|
|
orderByField: string = 'timestamp'
|
|||
|
|
): Promise<T[]> {
|
|||
|
|
if (!targetIds || targetIds.length === 0) return [];
|
|||
|
|
|
|||
|
|
// 过滤掉无效的 ID（undefined、null、空字符串）
|
|||
|
|
const validIds = targetIds.filter(id => id != null && id !== '');
|
|||
|
|
if (validIds.length === 0) return [];
|
|||
|
|
|
|||
|
|
// 使用子查询 + UNION ALL，每个子查询需要用括号包起来
|
|||
|
|
const subQueries = validIds.map(id => {
|
|||
|
|
const safeId = id.replace(/'/g, "''");
|
|||
|
|
return `SELECT * FROM (
|
|||
|
|
SELECT * FROM ${this.config.tableName}
|
|||
|
|
WHERE ${targetField} = '${safeId}'
|
|||
|
|
ORDER BY ${orderByField} DESC
|
|||
|
|
LIMIT ${limit}
|
|||
|
|
)`;
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
const sql = subQueries.join(' UNION ALL ');
|
|||
|
|
const res = await this.selectRaw(sql);
|
|||
|
|
return res.map(row => this.deserialize(row)) as T[];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 通用条件查询 */
|
|||
|
|
async find<T>(options: {
|
|||
|
|
where?: Record<string, any>,
|
|||
|
|
columns?: string[], // 支持只查特定列，如 ['key', 'local_path']
|
|||
|
|
orderBy?: string,
|
|||
|
|
limit?: number,
|
|||
|
|
offset?: number
|
|||
|
|
}): Promise<T[]> {
|
|||
|
|
const colStr = options.columns && options.columns.length > 0
|
|||
|
|
? options.columns.join(',') : '*';
|
|||
|
|
|
|||
|
|
let sql = `SELECT ${colStr} FROM ${this.config.tableName}`;
|
|||
|
|
|
|||
|
|
const whereClause = this.buildWhere(options.where);
|
|||
|
|
if (whereClause) sql += ` WHERE ${whereClause}`;
|
|||
|
|
|
|||
|
|
if (options.orderBy) sql += ` ORDER BY ${options.orderBy}`;
|
|||
|
|
if (options.limit) sql += ` LIMIT ${options.limit}`;
|
|||
|
|
if (options.offset) sql += ` OFFSET ${options.offset}`;
|
|||
|
|
|
|||
|
|
const res = await this.selectRaw(sql);
|
|||
|
|
return res.map(row => this.deserialize(row)) as T[];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 通用删除 */
|
|||
|
|
async delete(whereClause: string): Promise<void> {
|
|||
|
|
const sql = `DELETE FROM ${this.config.tableName} WHERE ${whereClause}`;
|
|||
|
|
return this.executeSql(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 执行原生SQL查询 */
|
|||
|
|
async queryRaw<T>(sql: string): Promise<T[]> {
|
|||
|
|
const res = await this.selectRaw(sql);
|
|||
|
|
return res.map(row => this.deserialize(row)) as T[];
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 统计数量 */
|
|||
|
|
async count(where?: Record<string, any>): Promise<number> {
|
|||
|
|
let sql = `SELECT COUNT(*) as total FROM ${this.config.tableName}`;
|
|||
|
|
const whereClause = this.buildWhere(where);
|
|||
|
|
if (whereClause) sql += ` WHERE ${whereClause}`;
|
|||
|
|
|
|||
|
|
const res = await this.selectRaw(sql);
|
|||
|
|
return res.length > 0 ? res[0].total : 0;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/**
|
|||
|
|
* 替代手动写 "DELETE FROM table WHERE key='...'"
|
|||
|
|
*/
|
|||
|
|
async deleteBy(where: Record<string, any>): Promise<void> {
|
|||
|
|
const whereClause = this.buildWhere(where);
|
|||
|
|
if (!whereClause) {
|
|||
|
|
console.warn('[DB] deleteBy 被调用但没有条件，操作被阻止以防止清空全表');
|
|||
|
|
return;
|
|||
|
|
}
|
|||
|
|
const sql = `DELETE FROM ${this.config.tableName} WHERE ${whereClause}`;
|
|||
|
|
return this.executeSql(sql);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/* ================================= 内部工具 ================================= */
|
|||
|
|
|
|||
|
|
private buildWhere(where?: Record<string, any>): string {
|
|||
|
|
if (!where) return '';
|
|||
|
|
const clauses: string[] = [];
|
|||
|
|
for (const key in where) {
|
|||
|
|
const val = where[key];
|
|||
|
|
if (val === undefined) continue;
|
|||
|
|
// 处理特殊符号，如 "age >": 18
|
|||
|
|
if (key.includes(' ') || key.includes('<') || key.includes('>') || key.includes('LIKE'))
|
|||
|
|
clauses.push(`${key} ${this.formatValue(val)}`);
|
|||
|
|
else
|
|||
|
|
clauses.push(`${key} = ${this.formatValue(val)}`);
|
|||
|
|
}
|
|||
|
|
return clauses.join(' AND ');
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 执行SQL语句
|
|||
|
|
public executeSql(sql: string | string[]): Promise<void> {
|
|||
|
|
return new Promise((resolve, reject) => {
|
|||
|
|
plus.sqlite.executeSql({
|
|||
|
|
name: this.config.dbName,
|
|||
|
|
sql: sql,
|
|||
|
|
success: () => resolve(),
|
|||
|
|
fail: (e) => {
|
|||
|
|
console.error(`[DB] SQL Error:`, e);
|
|||
|
|
reject(e);
|
|||
|
|
}
|
|||
|
|
});
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private selectRaw(sql: string): Promise<any[]> {
|
|||
|
|
return new Promise((resolve, reject) => {
|
|||
|
|
plus.sqlite.selectSql({
|
|||
|
|
name: this.config.dbName,
|
|||
|
|
sql,
|
|||
|
|
success: (res) => resolve(res as any[]),
|
|||
|
|
fail: (e) => {
|
|||
|
|
console.error("SQL查询失败:", sql, e);
|
|||
|
|
reject(e);
|
|||
|
|
}
|
|||
|
|
});
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 序列化数值
|
|||
|
|
private formatValue(val: any): string {
|
|||
|
|
if (typeof val === 'string') return `'${val.replace(/'/g, "''")}'`;
|
|||
|
|
if (typeof val === 'boolean') return val ? '1' : '0';
|
|||
|
|
if (val === null || val === undefined) return 'NULL';
|
|||
|
|
return String(val);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/** 整体序列化*/
|
|||
|
|
private serialize(data: any): Record<string, any> {
|
|||
|
|
const row: any = {};
|
|||
|
|
for (const col of this.tableColumns) {
|
|||
|
|
let val = data[col];
|
|||
|
|
if (val === undefined) continue;
|
|||
|
|
// JSON 字段处理
|
|||
|
|
if (this.config.jsonFields?.includes(col)) {
|
|||
|
|
if (val !== null && typeof val === 'object') { val = JSON.stringify(val);}
|
|||
|
|
}
|
|||
|
|
// boolean 统一
|
|||
|
|
if (typeof val === 'boolean')
|
|||
|
|
val = val ? 1 : 0;
|
|||
|
|
row[col] = val;
|
|||
|
|
}
|
|||
|
|
return row;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
|
|||
|
|
/** 整体反序列化 */
|
|||
|
|
private deserialize(row: any) {
|
|||
|
|
if (!row) return row;
|
|||
|
|
this.config.jsonFields?.forEach(f => {
|
|||
|
|
if (row[f] && typeof row[f] === 'string') {
|
|||
|
|
try {
|
|||
|
|
// 判断是否像 json 只有 {, [ 开头的才解析
|
|||
|
|
if (row[f].startsWith('{') || row[f].startsWith('[')) {
|
|||
|
|
row[f] = JSON.parse(row[f]);
|
|||
|
|
}
|
|||
|
|
} catch (e) {
|
|||
|
|
console.warn(`[DB] JSON 解析错误 ${f}:`, e);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
});
|
|||
|
|
return row;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 初始化数据库SQL样式
|
|||
|
|
private generateInitSqls(): string[] {
|
|||
|
|
const sqls: string[] = [];
|
|||
|
|
const { tableName, columns, indexes } = this.config;
|
|||
|
|
sqls.push(`CREATE TABLE IF NOT EXISTS ${tableName} (${columns})`);
|
|||
|
|
if (indexes && indexes.length > 0) {
|
|||
|
|
indexes.forEach((fieldStr) => {
|
|||
|
|
const safeSuffix = fieldStr.replace(/[, ]+/g, '_');
|
|||
|
|
const indexName = `idx_${tableName}_${safeSuffix}`;
|
|||
|
|
sqls.push(`CREATE INDEX IF NOT EXISTS ${indexName} ON ${tableName} (${fieldStr})`);
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
return sqls;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private get realDbPath(): string {
|
|||
|
|
const p = this.config.dbPath;
|
|||
|
|
// 如果本来就以 .db/.sqlite/.sqlite3 结尾，就不再拼
|
|||
|
|
return /(\.db|\.sqlite3?)$/i.test(p) ? p : `${p}.db`;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private compileColumns() {
|
|||
|
|
this.tableColumns = this.config.columns
|
|||
|
|
.split(',')
|
|||
|
|
.map(c => c.trim().split(/\s+/)[0]);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
private connectDB(): Promise<void> {
|
|||
|
|
return new Promise((resolve, reject) => {
|
|||
|
|
if (plus.sqlite.isOpenDatabase({ name: this.config.dbName, path: this.realDbPath })) {
|
|||
|
|
return resolve();
|
|||
|
|
}
|
|||
|
|
plus.sqlite.openDatabase({
|
|||
|
|
name: this.config.dbName,
|
|||
|
|
path: this.realDbPath,
|
|||
|
|
success: () => resolve(),
|
|||
|
|
fail: (e) => reject(e)
|
|||
|
|
});
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|