openclaw-voc-skill/services/qiwei-relay/lib/db.ts

/**
 * Relay 数据库封装 — 轻量版
 *
 * 与主项目 lib/db.ts 类似，但独立维护，避免跨服务依赖。
 */

import Database from 'better-sqlite3';
import type { Database as DatabaseType } from 'better-sqlite3';
import path from 'path';
import fs from 'fs';
import { randomUUID } from 'crypto';

export interface DbRow {
  objectId: string;
  createdAt: string;
  updatedAt: string;
  [key: string]: unknown;
}

export interface ColumnDef {
  name: string;
  type: 'TEXT' | 'INTEGER' | 'REAL' | 'BOOLEAN';
  required?: boolean;
  defaultValue?: unknown;
}

let db: DatabaseType;

export function initDb(dbPath: string): void {
  fs.mkdirSync(path.dirname(dbPath), { recursive: true });
  db = new Database(dbPath);
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
}

export function getDb(): DatabaseType {
  if (!db) throw new Error('数据库未初始化，请先调用 initDb()');
  return db;
}

export function createTable(tableName: string, columns: ColumnDef[]): void {
  const systemColumns = `
    objectId TEXT PRIMARY KEY,
    createdAt TEXT NOT NULL DEFAULT (datetime('now')),
    updatedAt TEXT NOT NULL DEFAULT (datetime('now'))
  `;

  const businessColumns = columns
    .map((col) => {
      let sql = `"${col.name}" ${col.type}`;
      if (col.required) sql += ' NOT NULL';
      if (col.defaultValue !== undefined) {
        const dv =
          typeof col.defaultValue === 'string'
            ? `'${col.defaultValue}'`
            : col.defaultValue;
        sql += ` DEFAULT ${dv}`;
      }
      return sql;
    })
    .join(',\n    ');

  const sql = `CREATE TABLE IF NOT EXISTS "${tableName}" (\n    ${systemColumns}${businessColumns ? ',\n    ' + businessColumns : ''}\n  )`;

  db.exec(sql);
}

export class Query<T extends DbRow = DbRow> {
  private tableName: string;
  private conditions: string[] = [];
  private params: unknown[] = [];
  private _limit = 100;
  private _skip = 0;
  private _orderBy = 'createdAt';
  private _orderDirection: 'asc' | 'desc' = 'desc';

  constructor(tableName: string) {
    this.tableName = tableName;
  }

  equalTo(field: string, value: unknown): this {
    if (value === null || value === undefined) {
      this.conditions.push(`"${field}" IS NULL`);
    } else {
      this.conditions.push(`"${field}" = ?`);
      this.params.push(value);
    }
    return this;
  }

  notEqualTo(field: string, value: unknown): this {
    if (value === null || value === undefined) {
      this.conditions.push(`"${field}" IS NOT NULL`);
    } else {
      this.conditions.push(`"${field}" != ?`);
      this.params.push(value);
    }
    return this;
  }

  containedIn(field: string, values: unknown[]): this {
    if (values.length === 0) {
      this.conditions.push('1 = 0');
      return this;
    }
    const placeholders = values.map(() => '?').join(',');
    this.conditions.push(`"${field}" IN (${placeholders})`);
    this.params.push(...values);
    return this;
  }

  greaterThan(field: string, value: number | string): this {
    this.conditions.push(`"${field}" > ?`);
    this.params.push(value);
    return this;
  }

  lessThan(field: string, value: number | string): this {
    this.conditions.push(`"${field}" < ?`);
    this.params.push(value);
    return this;
  }

  descending(field: string): this {
    this._orderBy = field;
    this._orderDirection = 'desc';
    return this;
  }

  ascending(field: string): this {
    this._orderBy = field;
    this._orderDirection = 'asc';
    return this;
  }

  limit(n: number): this {
    this._limit = n;
    return this;
  }

  skip(n: number): this {
    this._skip = n;
    return this;
  }

  private buildWhere(): string {
    return this.conditions.length > 0
      ? `WHERE ${this.conditions.join(' AND ')}`
      : '';
  }

  find(): T[] {
    const where = this.buildWhere();
    const sql = `SELECT * FROM "${this.tableName}" ${where} ORDER BY "${this._orderBy}" ${this._orderDirection} LIMIT ? OFFSET ?`;
    return db.prepare(sql).all(...this.params, this._limit, this._skip) as T[];
  }

  first(): T | null {
    const where = this.buildWhere();
    const sql = `SELECT * FROM "${this.tableName}" ${where} ORDER BY "${this._orderBy}" ${this._orderDirection} LIMIT 1`;
    return (db.prepare(sql).get(...this.params) as T | undefined) ?? null;
  }

  count(): number {
    const where = this.buildWhere();
    const sql = `SELECT COUNT(*) as count FROM "${this.tableName}" ${where}`;
    const result = db.prepare(sql).get(...this.params) as { count: number };
    return result.count;
  }
}

export function createObject(tableName: string, data: Record<string, unknown>): DbRow {
  const now = new Date().toISOString();
  const objectId = randomUUID();

  const columns: string[] = ['objectId', 'createdAt', 'updatedAt'];
  const values: unknown[] = [objectId, now, now];
  const placeholders: string[] = ['?', '?', '?'];

  for (const [key, value] of Object.entries(data)) {
    columns.push(`"${key}"`);
    values.push(value);
    placeholders.push('?');
  }

  const sql = `INSERT INTO "${tableName}" (${columns.join(', ')}) VALUES (${placeholders.join(', ')})`;
  db.prepare(sql).run(...values);

  return { objectId, createdAt: now, updatedAt: now, ...data } as DbRow;
}

export function updateObject(tableName: string, objectId: string, data: Record<string, unknown>): boolean {
  const now = new Date().toISOString();
  const sets: string[] = ['updatedAt = ?'];
  const values: unknown[] = [now];

  for (const [key, value] of Object.entries(data)) {
    sets.push(`"${key}" = ?`);
    values.push(value);
  }

  values.push(objectId);
  const sql = `UPDATE "${tableName}" SET ${sets.join(', ')} WHERE objectId = ?`;
  const result = db.prepare(sql).run(...values);
  return result.changes > 0;
}

export function getObject<T extends DbRow = DbRow>(tableName: string, objectId: string): T | null {
  const sql = `SELECT * FROM "${tableName}" WHERE objectId = ?`;
  return (db.prepare(sql).get(objectId) as T) ?? null;
}