tkmind_go/ui/h5/db.mjs

import fs from 'node:fs';
import mysql from 'mysql2/promise';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { ensureDefaultSpaces } from './mindspace.mjs';

const __dirname = path.dirname(fileURLToPath(import.meta.url));

export function isDatabaseConfigured() {
  return Boolean(
    process.env.DATABASE_URL ||
      (process.env.MYSQL_HOST && process.env.MYSQL_DATABASE),
  );
}

export function createDbPool() {
  if (!isDatabaseConfigured()) {
    throw new Error('MySQL 未配置，请设置 DATABASE_URL 或 MYSQL_* 环境变量');
  }

  if (process.env.DATABASE_URL) {
    return mysql.createPool(process.env.DATABASE_URL);
  }

  return mysql.createPool({
    host: process.env.MYSQL_HOST ?? 'localhost',
    port: Number(process.env.MYSQL_PORT ?? 3306),
    user: process.env.MYSQL_USER ?? 'boot',
    password: process.env.MYSQL_PASSWORD ?? '',
    database: process.env.MYSQL_DATABASE ?? 'tkmind',
    waitForConnections: true,
    connectionLimit: 10,
  });
}

async function columnExists(pool, table, column) {
  const [rows] = await pool.query(
    `SELECT 1 FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?
     LIMIT 1`,
    [table, column],
  );
  return rows.length > 0;
}

async function indexExists(pool, table, index) {
  const [rows] = await pool.query(
    `SELECT 1 FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND INDEX_NAME = ?
     LIMIT 1`,
    [table, index],
  );
  return rows.length > 0;
}

async function foreignKeyDeleteRule(pool, table, constraint) {
  const [rows] = await pool.query(
    `SELECT DELETE_RULE
     FROM information_schema.REFERENTIAL_CONSTRAINTS
     WHERE CONSTRAINT_SCHEMA = DATABASE()
       AND TABLE_NAME = ?
       AND CONSTRAINT_NAME = ?
     LIMIT 1`,
    [table, constraint],
  );
  return rows[0]?.DELETE_RULE ?? null;
}

async function ensureForeignKeyDeleteRule(
  pool,
  { table, constraint, column, referencedTable, referencedColumn = 'id', deleteRule },
) {
  const currentRule = await foreignKeyDeleteRule(pool, table, constraint);
  if (currentRule === deleteRule) return;
  if (currentRule) {
    await pool.query(`ALTER TABLE \`${table}\` DROP FOREIGN KEY \`${constraint}\``);
  }
  await pool.query(
    `ALTER TABLE \`${table}\`
     ADD CONSTRAINT \`${constraint}\`
       FOREIGN KEY (\`${column}\`) REFERENCES \`${referencedTable}\`(\`${referencedColumn}\`)
       ON DELETE ${deleteRule}`,
  );
}

export async function migrateSchema(pool) {
  const renames = [
    ['h5_user_sessions', 'goose_session_id', 'agent_session_id'],
    ['h5_session_billing_state', 'goose_session_id', 'agent_session_id'],
    ['h5_usage_records', 'goose_session_id', 'agent_session_id'],
  ];
  for (const [table, oldCol, newCol] of renames) {
    if (await columnExists(pool, table, oldCol)) {
      await pool.query(
        `ALTER TABLE \`${table}\` CHANGE \`${oldCol}\` \`${newCol}\` VARCHAR(128) NOT NULL`,
      );
    }
  }

  const userColumns = [
    ['slug', 'VARCHAR(64) NULL AFTER username'],
    ['email', 'VARCHAR(255) NULL AFTER slug'],
    [
      'password_algorithm',
      "VARCHAR(32) NOT NULL DEFAULT 'pbkdf2-sha512' AFTER password_hash",
    ],
    ['plan_type', "VARCHAR(32) NOT NULL DEFAULT 'free' AFTER status"],
  ];
  for (const [column, definition] of userColumns) {
    if (!(await columnExists(pool, 'h5_users', column))) {
      await pool.query(`ALTER TABLE h5_users ADD COLUMN \`${column}\` ${definition}`);
    }
  }

  await pool.query(`UPDATE h5_users SET slug = username WHERE slug IS NULL OR slug = ''`);
  if (!(await indexExists(pool, 'h5_users', 'uq_h5_users_slug'))) {
    await pool.query(`ALTER TABLE h5_users ADD UNIQUE KEY uq_h5_users_slug (slug)`);
  }
  if (!(await indexExists(pool, 'h5_users', 'uq_h5_users_email'))) {
    await pool.query(`ALTER TABLE h5_users ADD UNIQUE KEY uq_h5_users_email (email)`);
  }

  const assetForeignKeys = [
    {
      table: 'h5_assets',
      constraint: 'fk_h5_asset_category',
      column: 'category_id',
      referencedTable: 'h5_space_categories',
      deleteRule: 'CASCADE',
    },
    {
      table: 'h5_assets',
      constraint: 'fk_h5_asset_parent',
      column: 'parent_id',
      referencedTable: 'h5_assets',
      deleteRule: 'SET NULL',
    },
    {
      table: 'h5_asset_versions',
      constraint: 'fk_h5_asset_version_user',
      column: 'created_by',
      referencedTable: 'h5_users',
      deleteRule: 'CASCADE',
    },
    {
      table: 'h5_upload_sessions',
      constraint: 'fk_h5_upload_category',
      column: 'category_id',
      referencedTable: 'h5_space_categories',
      deleteRule: 'CASCADE',
    },
    {
      table: 'h5_page_versions',
      constraint: 'fk_h5_page_version_content',
      column: 'content_asset_id',
      referencedTable: 'h5_assets',
      deleteRule: 'CASCADE',
    },
  ];
  for (const foreignKey of assetForeignKeys) {
    await ensureForeignKeyDeleteRule(pool, foreignKey);
  }

  await pool.query(
    `ALTER TABLE h5_assets
     MODIFY source_type ENUM('upload', 'chat', 'agent', 'template', 'generated', 'workspace')
     NOT NULL DEFAULT 'upload'`,
  );

  const llmColumns = [
    ['provider_kind', "ENUM('builtin','custom') NOT NULL DEFAULT 'builtin' AFTER provider_id"],
    ['api_url', 'VARCHAR(512) NULL AFTER provider_kind'],
    ['base_path', 'VARCHAR(256) NULL AFTER api_url'],
    ['models_json', 'TEXT NULL AFTER base_path'],
    ['goosed_provider_id', 'VARCHAR(64) NULL AFTER models_json'],
    ['engine', "VARCHAR(32) NOT NULL DEFAULT 'openai' AFTER goosed_provider_id"],
    ['relay_provider', 'VARCHAR(64) NULL AFTER engine'],
  ];
  for (const [column, definition] of llmColumns) {
    if (!(await columnExists(pool, 'h5_llm_provider_keys', column))) {
      await pool.query(`ALTER TABLE h5_llm_provider_keys ADD COLUMN \`${column}\` ${definition}`);
    }
  }

  const publishColumns = [
    ['plaza_view_count', 'BIGINT NOT NULL DEFAULT 0'],
    ['plaza_like_count', 'BIGINT NOT NULL DEFAULT 0'],
  ];
  for (const [column, definition] of publishColumns) {
    if (!(await columnExists(pool, 'h5_publish_records', column))) {
      await pool.query(`ALTER TABLE h5_publish_records ADD COLUMN \`${column}\` ${definition}`);
    }
  }

  const plazaUserColumns = [
    ['plaza_post_count', 'INT UNSIGNED NOT NULL DEFAULT 0'],
    ['plaza_follower_count', 'INT UNSIGNED NOT NULL DEFAULT 0'],
    ['plaza_following_count', 'INT UNSIGNED NOT NULL DEFAULT 0'],
    ['plaza_verified', 'TINYINT(1) NOT NULL DEFAULT 0'],
    ['plaza_post_banned', 'TINYINT(1) NOT NULL DEFAULT 0'],
    ['plaza_comment_banned', 'TINYINT(1) NOT NULL DEFAULT 0'],
    [
      'ops_role',
      "ENUM('none','reviewer','editor','ops_admin') NOT NULL DEFAULT 'none'",
    ],
  ];
  for (const [column, definition] of plazaUserColumns) {
    if (!(await columnExists(pool, 'h5_users', column))) {
      await pool.query(`ALTER TABLE h5_users ADD COLUMN \`${column}\` ${definition}`);
    }
  }
}

export async function initSchema(pool) {
  const schemaPath = path.join(__dirname, 'schema.sql');
  const sql = fs.readFileSync(schemaPath, 'utf8');
  for (const statement of sql.split(';')) {
    const trimmed = statement.trim();
    if (trimmed) {
      await pool.query(trimmed);
    }
  }
  await migrateSchema(pool);
  await ensureDefaultSpaces(pool, {
    quotaBytes: Number(process.env.MINDSPACE_FREE_QUOTA_BYTES ?? 5 * 1024 * 1024),
  });
}