MCP/database

Fork 0

zpc bd1e1201f1 12

2025-12-27 16:21:09 +08:00

32 KiB

Raw Permalink Blame History

v1.0.2 详细实施步骤

本文档提供 v1.0.2 版本开发的详细步骤指导，包括每个阶段的具体任务、代码示例和验收标准。

阶段一：基础架构重构 (Week 1-3)

任务 1.1: 设计数据库驱动接口 (Day 1-2)

目标: 定义 DatabaseDriver 接口，明确所有数据库驱动必须实现的方法。

步骤:

创建驱动接口文件

mkdir -p src/drivers
touch src/drivers/database-driver.ts

定义核心接口

// src/drivers/database-driver.ts

export interface DatabaseDriver {
  // 驱动标识
  readonly type: 'postgres' | 'sqlserver';
  readonly name: string;
  readonly version: string;

  // 连接管理
  createConnectionPool(config: any): any;
  testConnection(pool: any): Promise<boolean>;
  closeConnectionPool(pool: any): Promise<void>;

  // 查询执行
  execute<T>(client: any, sql: string, params?: unknown[]): Promise<QueryResult<T>>;
  buildPaginatedQuery(sql: string, params: unknown[], limit: number, offset: number): { sql: string; params: unknown[] };
  buildExplainQuery(sql: string, analyze?: boolean): string;
  parseExplainResult(result: any): ExplainPlan;

  // SQL 语法工具
  quoteIdentifier(id: string): string;
  buildQualifiedTableName(table: string, schema?: string): string;
  getParameterPlaceholder(index: number): string;

  // 事务管理
  buildBeginStatement(options?: TransactionOptions): string;
  buildCommitStatement(): string;
  buildRollbackStatement(): string;
  buildSavepointStatement(name: string): string;
  buildRollbackToSavepointStatement(name: string): string;

  // Schema 管理
  buildSetSchemaStatement(schemas: string | string[]): string;
  supportsSearchPath(): boolean;

  // 元数据查询
  buildListSchemasQuery(): string;
  buildListTablesQuery(schema?: string): string;
  buildDescribeTableQuery(table: string, schema?: string): string;
  parseTableDefinition(rows: any[]): TableDefinition;
  buildListViewsQuery(schema?: string): string;
  buildListIndexesQuery(schema?: string, table?: string): string;
  buildListConstraintsQuery(schema?: string, table?: string): string;
  buildListFunctionsQuery(schema?: string): string;
  buildListTriggersQuery(schema?: string, table?: string): string;

  // 批量操作
  buildBulkInsertStatement(table: string, schema: string | undefined, columns: string[], rows: Record<string, unknown>[], chunkSize?: number): { sql: string; params: unknown[] }[];
  buildBulkUpsertStatement(table: string, schema: string | undefined, columns: string[], rows: Record<string, unknown>[], conflictColumns: string[], updateColumns: string[]): { sql: string; params: unknown[] };

  // 诊断功能
  buildGetActiveConnectionsQuery(): string;
  parseActiveConnections(rows: any[]): ConnectionInfo[];
  buildGetLocksQuery(): string;
  parseLocks(rows: any[]): LockInfo[];
  supportsDiagnostic(diagnostic: DiagnosticType): boolean;

  // 数据类型映射
  mapToGenericType(nativeType: string): GenericDataType;
  mapFromGenericType(genericType: GenericDataType): string;
}

定义辅助类型

// src/drivers/types.ts

export interface QueryResult<T = any> {
  rows: T[];
  rowCount: number;
  fields?: FieldInfo[];
}

export interface FieldInfo {
  name: string;
  type: string;
  nullable: boolean;
}

export interface TableDefinition {
  schema: string;
  table: string;
  columns: ColumnDefinition[];
  primaryKey?: string[];
  foreignKeys?: ForeignKeyDefinition[];
  indexes?: IndexDefinition[];
  uniqueConstraints?: UniqueConstraintDefinition[];
}

export interface ColumnDefinition {
  name: string;
  type: string;
  nullable: boolean;
  defaultValue?: string;
  maxLength?: number;
  precision?: number;
  scale?: number;
}

// ... 其他类型定义

验收标准:

接口定义完整,覆盖所有核心功能
类型定义清晰,无 TypeScript 编译错误
代码通过 lint 检查

任务 1.2: 创建驱动工厂 (Day 2)

步骤:

创建工厂文件

// src/drivers/driver-factory.ts

import { DatabaseDriver } from './database-driver.js';

export function createDriver(type: 'postgres' | 'sqlserver'): DatabaseDriver {
  switch (type) {
    case 'postgres':
      // 暂时抛出错误,后续实现
      throw new Error('PostgresDriver not implemented yet');
    case 'sqlserver':
      throw new Error('SqlServerDriver not implemented yet');
    default:
      throw new Error(`Unsupported database type: ${type}`);
  }
}

// 驱动注册表
const driverRegistry = new Map<string, DatabaseDriver>();

export function registerDriver(type: string, driver: DatabaseDriver): void {
  driverRegistry.set(type, driver);
}

export function getDriver(type: string): DatabaseDriver {
  const driver = driverRegistry.get(type);
  if (!driver) {
    throw new Error(`Driver not found for type: ${type}`);
  }
  return driver;
}

创建索引导出

// src/drivers/index.ts

export * from './database-driver.js';
export * from './driver-factory.js';
export * from './types.js';

验收标准:

工厂函数编译通过
支持动态驱动注册

任务 1.3: 提取 PostgreSQL 驱动 (Day 3-5)

目标: 将现有 PostgreSQL 代码重构为驱动实现。

步骤:

创建 PostgreSQL 驱动目录

mkdir -p src/drivers/postgres
touch src/drivers/postgres/postgres-driver.ts

实现核心方法

// src/drivers/postgres/postgres-driver.ts

import { Pool, PoolClient, PoolConfig as PgPoolConfig } from 'pg';
import { DatabaseDriver, QueryResult, ExplainPlan, TableDefinition } from '../database-driver.js';

export class PostgresDriver implements DatabaseDriver {
  readonly type = 'postgres' as const;
  readonly name = 'PostgreSQL Driver';
  readonly version = '1.0.0';

  // ========== 连接管理 ==========

  createConnectionPool(config: any): Pool {
    const pgConfig: PgPoolConfig = {
      host: config.host,
      port: config.port,
      database: config.database,
      user: config.user,
      password: config.password,
      ssl: config.ssl?.require ? {
        ca: config.ssl.ca,
        cert: config.ssl.cert,
        key: config.ssl.key,
        rejectUnauthorized: config.ssl.rejectUnauthorized ?? true
      } : false,
      max: config.pool?.max ?? 10,
      idleTimeoutMillis: config.pool?.idleTimeoutMs ?? 30000,
      connectionTimeoutMillis: config.pool?.connectionTimeoutMs ?? 10000
    };
    return new Pool(pgConfig);
  }

  async testConnection(pool: Pool): Promise<boolean> {
    const client = await pool.connect();
    try {
      await client.query('SELECT 1');
      return true;
    } catch (error) {
      return false;
    } finally {
      client.release();
    }
  }

  async closeConnectionPool(pool: Pool): Promise<void> {
    await pool.end();
  }

  // ========== 查询执行 ==========

  async execute<T>(
    client: PoolClient,
    sql: string,
    params?: unknown[]
  ): Promise<QueryResult<T>> {
    const result = await client.query<T>(sql, params);
    return {
      rows: result.rows,
      rowCount: result.rowCount ?? 0,
      fields: result.fields?.map(f => ({
        name: f.name,
        type: f.dataTypeID.toString(),
        nullable: true
      }))
    };
  }

  buildPaginatedQuery(
    sql: string,
    params: unknown[],
    limit: number,
    offset: number
  ): { sql: string; params: unknown[] } {
    const limitIdx = params.length + 1;
    const offsetIdx = limitIdx + 1;
    return {
      sql: `${sql} LIMIT $${limitIdx} OFFSET $${offsetIdx}`,
      params: [...params, limit, offset]
    };
  }

  buildExplainQuery(sql: string, analyze?: boolean): string {
    const prefix = analyze
      ? 'EXPLAIN (ANALYZE, FORMAT JSON)'
      : 'EXPLAIN (FORMAT JSON)';
    return `${prefix} ${sql}`;
  }

  parseExplainResult(result: any): ExplainPlan {
    const plan = result.rows[0]['QUERY PLAN'][0];
    return {
      raw: plan,
      summary: {
        totalCost: plan['Total Cost'],
        estimatedRows: plan['Plan Rows'],
        actualRows: plan['Actual Rows'],
        executionTime: plan['Execution Time']
      }
    };
  }

  // ========== SQL 语法工具 ==========

  quoteIdentifier(id: string): string {
    return `"${id.replace(/"/g, '""')}"`;
  }

  buildQualifiedTableName(table: string, schema?: string): string {
    if (schema) {
      return `${this.quoteIdentifier(schema)}.${this.quoteIdentifier(table)}`;
    }
    return this.quoteIdentifier(table);
  }

  getParameterPlaceholder(index: number): string {
    return `$${index}`;
  }

  // ========== 事务管理 ==========

  buildBeginStatement(options?: any): string {
    let sql = 'BEGIN';
    if (options?.isolationLevel) {
      sql += ` ISOLATION LEVEL ${options.isolationLevel}`;
    }
    if (options?.readOnly) {
      sql += ' READ ONLY';
    }
    if (options?.deferrable) {
      sql += ' DEFERRABLE';
    }
    return sql;
  }

  buildCommitStatement(): string {
    return 'COMMIT';
  }

  buildRollbackStatement(): string {
    return 'ROLLBACK';
  }

  buildSavepointStatement(name: string): string {
    return `SAVEPOINT ${this.quoteIdentifier(name)}`;
  }

  buildRollbackToSavepointStatement(name: string): string {
    return `ROLLBACK TO SAVEPOINT ${this.quoteIdentifier(name)}`;
  }

  // ========== Schema 管理 ==========

  buildSetSchemaStatement(schemas: string | string[]): string {
    const paths = Array.isArray(schemas) ? schemas : [schemas];
    const quoted = paths.map(s => this.quoteIdentifier(s)).join(', ');
    return `SET search_path TO ${quoted}`;
  }

  supportsSearchPath(): boolean {
    return true;
  }

  // ========== 元数据查询 ==========

  buildListSchemasQuery(): string {
    return `
      SELECT schema_name
      FROM information_schema.schemata
      WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
      ORDER BY schema_name
    `;
  }

  buildListTablesQuery(schema?: string): string {
    if (schema) {
      return `
        SELECT tablename as table_name
        FROM pg_tables
        WHERE schemaname = '${schema}'
        ORDER BY tablename
      `;
    }
    return `
      SELECT tablename as table_name, schemaname as schema_name
      FROM pg_tables
      WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
      ORDER BY schemaname, tablename
    `;
  }

  buildDescribeTableQuery(table: string, schema?: string): string {
    const schemaFilter = schema ? `AND table_schema = '${schema}'` : '';
    return `
      SELECT
        column_name as name,
        data_type as type,
        is_nullable as nullable,
        column_default as default_value,
        character_maximum_length as max_length,
        numeric_precision as precision,
        numeric_scale as scale
      FROM information_schema.columns
      WHERE table_name = '${table}' ${schemaFilter}
      ORDER BY ordinal_position
    `;
  }

  parseTableDefinition(rows: any[]): TableDefinition {
    // 简化实现,后续完善
    return {
      schema: rows[0]?.table_schema || 'public',
      table: rows[0]?.table_name || '',
      columns: rows.map(row => ({
        name: row.name,
        type: row.type,
        nullable: row.nullable === 'YES',
        defaultValue: row.default_value,
        maxLength: row.max_length,
        precision: row.precision,
        scale: row.scale
      }))
    };
  }

  // ... 其他方法实现 (先返回空或默认值)
  buildListViewsQuery(schema?: string): string {
    return `SELECT viewname as view_name FROM pg_views WHERE schemaname = '${schema || 'public'}'`;
  }

  buildListIndexesQuery(schema?: string, table?: string): string {
    return `SELECT indexname as index_name FROM pg_indexes WHERE schemaname = '${schema || 'public'}'`;
  }

  buildListConstraintsQuery(schema?: string, table?: string): string {
    return `SELECT conname as constraint_name FROM pg_constraint`;
  }

  buildListFunctionsQuery(schema?: string): string {
    return `SELECT proname as function_name FROM pg_proc`;
  }

  buildListTriggersQuery(schema?: string, table?: string): string {
    return `SELECT tgname as trigger_name FROM pg_trigger`;
  }

  buildBulkInsertStatement(
    table: string,
    schema: string | undefined,
    columns: string[],
    rows: Record<string, unknown>[],
    chunkSize?: number
  ): { sql: string; params: unknown[] }[] {
    // 简化实现
    const chunks: { sql: string; params: unknown[] }[] = [];
    const size = chunkSize ?? 100;

    for (let i = 0; i < rows.length; i += size) {
      const chunk = rows.slice(i, i + size);
      const values = chunk.flatMap(row => columns.map(col => row[col]));
      const valuePlaceholders = chunk.map((_, rowIdx) => {
        const base = rowIdx * columns.length;
        return `(${columns.map((_, colIdx) => `$${base + colIdx + 1}`).join(', ')})`;
      }).join(', ');

      const tableName = this.buildQualifiedTableName(table, schema);
      const columnNames = columns.map(c => this.quoteIdentifier(c)).join(', ');
      const sql = `INSERT INTO ${tableName} (${columnNames}) VALUES ${valuePlaceholders}`;

      chunks.push({ sql, params: values });
    }

    return chunks;
  }

  buildBulkUpsertStatement(
    table: string,
    schema: string | undefined,
    columns: string[],
    rows: Record<string, unknown>[],
    conflictColumns: string[],
    updateColumns: string[]
  ): { sql: string; params: unknown[] } {
    const tableName = this.buildQualifiedTableName(table, schema);
    const columnNames = columns.map(c => this.quoteIdentifier(c)).join(', ');
    const values = rows.flatMap(row => columns.map(col => row[col]));

    const valuePlaceholders = rows.map((_, rowIdx) => {
      const base = rowIdx * columns.length;
      return `(${columns.map((_, colIdx) => `$${base + colIdx + 1}`).join(', ')})`;
    }).join(', ');

    const conflictCols = conflictColumns.map(c => this.quoteIdentifier(c)).join(', ');
    const updates = updateColumns.map(c =>
      `${this.quoteIdentifier(c)} = EXCLUDED.${this.quoteIdentifier(c)}`
    ).join(', ');

    const sql = `
      INSERT INTO ${tableName} (${columnNames})
      VALUES ${valuePlaceholders}
      ON CONFLICT (${conflictCols})
      DO UPDATE SET ${updates}
      RETURNING *
    `;

    return { sql, params: values };
  }

  buildGetActiveConnectionsQuery(): string {
    return `
      SELECT pid, usename, application_name, state, query
      FROM pg_stat_activity
      WHERE state = 'active'
    `;
  }

  parseActiveConnections(rows: any[]): any[] {
    return rows.map(row => ({
      pid: row.pid,
      database: row.datname,
      user: row.usename,
      state: row.state,
      query: row.query
    }));
  }

  buildGetLocksQuery(): string {
    return `
      SELECT locktype, database, relation::regclass, pid, mode, granted
      FROM pg_locks
    `;
  }

  parseLocks(rows: any[]): any[] {
    return rows;
  }

  supportsDiagnostic(diagnostic: string): boolean {
    return true;  // PostgreSQL 支持所有诊断功能
  }

  mapToGenericType(nativeType: string): string {
    const mapping: Record<string, string> = {
      'integer': 'INTEGER',
      'bigint': 'INTEGER',
      'varchar': 'STRING',
      'text': 'TEXT',
      'boolean': 'BOOLEAN',
      'timestamp': 'DATETIME',
      'date': 'DATE',
      'json': 'JSON',
      'jsonb': 'JSON',
      'uuid': 'UUID'
    };
    return mapping[nativeType.toLowerCase()] ?? 'UNKNOWN';
  }

  mapFromGenericType(genericType: string): string {
    const mapping: Record<string, string> = {
      'INTEGER': 'integer',
      'STRING': 'varchar',
      'TEXT': 'text',
      'BOOLEAN': 'boolean',
      'DATETIME': 'timestamp',
      'DATE': 'date',
      'JSON': 'jsonb',
      'UUID': 'uuid'
    };
    return mapping[genericType] ?? 'text';
  }
}

更新工厂函数

// src/drivers/driver-factory.ts

import { PostgresDriver } from './postgres/postgres-driver.js';

export function createDriver(type: 'postgres' | 'sqlserver'): DatabaseDriver {
  switch (type) {
    case 'postgres':
      return new PostgresDriver();
    case 'sqlserver':
      throw new Error('SqlServerDriver not implemented yet');
    default:
      throw new Error(`Unsupported database type: ${type}`);
  }
}

验收标准:

PostgreSQL 驱动编译通过
所有接口方法都已实现
驱动可以成功创建 PostgreSQL 连接池

任务 1.4: 重构 ConnectionManager (Day 6-7)

目标: 修改 ConnectionManager 使用驱动接口。

步骤:

备份现有代码

cp src/core/connection-manager.ts src/core/connection-manager.ts.backup

重构 ConnectionManager

// src/core/connection-manager.ts

import { DatabaseDriver } from '../drivers/database-driver.js';
import { EnvironmentConfig } from '../config/types.js';

export class ConnectionManager {
  private driver: DatabaseDriver;
  private pools = new Map<string, any>();
  private configs: Record<string, EnvironmentConfig>;

  constructor(
    configs: Record<string, EnvironmentConfig>,
    driver: DatabaseDriver
  ) {
    this.configs = configs;
    this.driver = driver;
  }

  getPool(envName: string): any {
    if (!this.configs[envName]) {
      throw new Error(`Environment not found: ${envName}`);
    }

    if (this.pools.has(envName)) {
      return this.pools.get(envName)!;
    }

    const config = this.configs[envName];
    const pool = this.driver.createConnectionPool(config.connection);
    this.pools.set(envName, pool);
    return pool;
  }

  async withClient<T>(
    envName: string,
    schema: string | string[] | undefined,
    callback: (client: any) => Promise<T>,
    options?: any
  ): Promise<T> {
    const pool = this.getPool(envName);
    const client = await pool.connect();

    try {
      // 设置 schema (如果支持)
      if (schema && this.driver.supportsSearchPath()) {
        const schemaSQL = this.driver.buildSetSchemaStatement(schema);
        if (schemaSQL) {
          await client.query(schemaSQL);
        }
      }

      // 设置超时
      const env = this.configs[envName];
      if (env.statementTimeoutMs) {
        const timeoutSQL = `SET statement_timeout = ${env.statementTimeoutMs}`;
        await client.query(timeoutSQL);
      }

      return await callback(client);
    } finally {
      client.release();
    }
  }

  async testConnection(envName: string): Promise<boolean> {
    try {
      const pool = this.getPool(envName);
      return await this.driver.testConnection(pool);
    } catch (error) {
      return false;
    }
  }

  async closeAll(): Promise<void> {
    const closePromises = Array.from(this.pools.entries()).map(async ([name, pool]) => {
      try {
        await this.driver.closeConnectionPool(pool);
        this.pools.delete(name);
      } catch (error) {
        console.error(`Error closing pool for ${name}:`, error);
      }
    });
    await Promise.all(closePromises);
  }
}

更新核心索引

// src/core/index.ts

import { ConnectionManager } from './connection-manager.js';
import { QueryRunner } from './query-runner.js';
import { MetadataBrowser } from './metadata-browser.js';
import { TransactionManager } from './transaction-manager.js';
import { BulkHelpers } from './bulk-helpers.js';
import { Diagnostics } from './diagnostics.js';
import { DatabaseDriver } from '../drivers/database-driver.js';
import { EnvironmentConfig } from '../config/types.js';

export class DatabaseMcp {
  public readonly connections: ConnectionManager;
  public readonly queries: QueryRunner;
  public readonly metadata: MetadataBrowser;
  public readonly transactions: TransactionManager;
  public readonly bulk: BulkHelpers;
  public readonly diagnostics: Diagnostics;

  constructor(
    configs: Record<string, EnvironmentConfig>,
    driver: DatabaseDriver
  ) {
    this.connections = new ConnectionManager(configs, driver);
    this.queries = new QueryRunner(this.connections, driver);
    this.metadata = new MetadataBrowser(this.connections, driver);
    this.transactions = new TransactionManager(this.connections, driver);
    this.bulk = new BulkHelpers(this.connections, driver);
    this.diagnostics = new Diagnostics(this.connections, driver);
  }

  async closeAll(): Promise<void> {
    await this.connections.closeAll();
  }
}

// 保留向后兼容
export class PostgresMcp extends DatabaseMcp {}

验收标准:

ConnectionManager 编译通过
所有现有测试通过
能够创建和关闭连接池

任务 1.5: 重构 QueryRunner (Day 8-9)

步骤:

// src/core/query-runner.ts

import { ConnectionManager } from './connection-manager.js';
import { DatabaseDriver, QueryResult } from '../drivers/database-driver.js';

export class QueryRunner {
  constructor(
    private connections: ConnectionManager,
    private driver: DatabaseDriver
  ) {}

  async execute<T>(
    envName: string,
    sql: string,
    params?: unknown[],
    options?: any
  ): Promise<QueryResult<T>> {
    return this.connections.withClient(
      envName,
      options?.schema,
      async (client) => {
        return await this.driver.execute<T>(client, sql, params);
      },
      options
    );
  }

  async executePaginated<T>(
    envName: string,
    sql: string,
    params: unknown[],
    pagination: { limit: number; offset?: number },
    options?: any
  ): Promise<QueryResult<T>> {
    const { sql: paginatedSQL, params: paginatedParams } =
      this.driver.buildPaginatedQuery(
        sql,
        params,
        pagination.limit,
        pagination.offset ?? 0
      );

    return this.execute<T>(envName, paginatedSQL, paginatedParams, options);
  }

  async explain(
    envName: string,
    sql: string,
    params?: unknown[],
    options?: any & { analyze?: boolean }
  ): Promise<any> {
    const explainSQL = this.driver.buildExplainQuery(sql, options?.analyze);
    const result = await this.execute(envName, explainSQL, params, options);
    return this.driver.parseExplainResult(result);
  }
}

验收标准:

QueryRunner 编译通过
分页查询测试通过
EXPLAIN 查询测试通过

任务 1.6: 重构 MetadataBrowser, BulkHelpers, Diagnostics (Day 10-12)

重构步骤同上,每个类都注入 driver 依赖,使用驱动方法而不是硬编码 SQL。

验收标准:

所有核心层类都重构完成
PostgreSQL 功能完全保留
所有现有测试通过

任务 1.7: 更新 server.ts (Day 13)

步骤:

// src/server.ts

import { createDriver } from './drivers/driver-factory.js';
import { DatabaseMcp, PostgresMcp } from './core/index.js';

// ... 加载配置

// 为每个环境创建对应的驱动
const envDrivers = new Map<string, DatabaseDriver>();
for (const [envName, envConfig] of Object.entries(config.environments)) {
  const driver = createDriver(envConfig.type);
  envDrivers.set(envName, driver);
}

// 如果所有环境都是同一类型,可以使用单一驱动
const firstEnvType = Object.values(config.environments)[0].type;
const allSameType = Object.values(config.environments).every(
  env => env.type === firstEnvType
);

if (allSameType) {
  const driver = createDriver(firstEnvType);
  const dbMcp = new DatabaseMcp(config.environments, driver);
  // ... 注册工具
} else {
  // 处理混合环境的情况 (后续实现)
  throw new Error('Mixed environment types not yet supported');
}

验收标准:

服务器能够启动
PostgreSQL 环境正常工作
所有 MCP 工具可用

任务 1.8: 测试和验证 (Day 14-21)

步骤:

编写单元测试

mkdir -p __tests__/drivers
touch __tests__/drivers/postgres-driver.test.ts

测试驱动方法

// __tests__/drivers/postgres-driver.test.ts

import { PostgresDriver } from '../../src/drivers/postgres/postgres-driver';

describe('PostgresDriver', () => {
  const driver = new PostgresDriver();

  describe('quoteIdentifier', () => {
    it('should quote simple identifier', () => {
      expect(driver.quoteIdentifier('table')).toBe('"table"');
    });

    it('should escape quotes', () => {
      expect(driver.quoteIdentifier('my"table')).toBe('"my""table"');
    });
  });

  describe('buildPaginatedQuery', () => {
    it('should build correct LIMIT OFFSET query', () => {
      const { sql, params } = driver.buildPaginatedQuery(
        'SELECT * FROM users',
        [],
        10,
        20
      );
      expect(sql).toBe('SELECT * FROM users LIMIT $1 OFFSET $2');
      expect(params).toEqual([10, 20]);
    });
  });

  // ... 更多测试
});

集成测试

// __tests__/integration/postgres.test.ts

import { PostgresDriver } from '../../src/drivers/postgres/postgres-driver';
import { ConnectionManager } from '../../src/core/connection-manager';

describe('PostgreSQL Integration', () => {
  let driver: PostgresDriver;
  let connMgr: ConnectionManager;

  beforeAll(() => {
    driver = new PostgresDriver();
    connMgr = new ConnectionManager({
      test: {
        type: 'postgres',
        connection: {
          host: 'localhost',
          port: 5432,
          database: 'test',
          user: 'postgres',
          password: 'postgres'
        }
      }
    }, driver);
  });

  afterAll(async () => {
    await connMgr.closeAll();
  });

  it('should connect and query', async () => {
    const pool = connMgr.getPool('test');
    const client = await pool.connect();
    try {
      const result = await driver.execute(client, 'SELECT 1 as num');
      expect(result.rows).toHaveLength(1);
      expect(result.rows[0].num).toBe(1);
    } finally {
      client.release();
    }
  });
});

验收标准:

所有单元测试通过
集成测试通过
PostgreSQL 现有功能完全正常
代码覆盖率 > 80%

阶段二：SQL Server 基础支持 (Week 4-6)

任务 2.1: 安装 SQL Server 驱动依赖 (Day 1)

步骤:

npm install mssql
npm install --save-dev @types/mssql

更新 package.json:

{
  "dependencies": {
    "mssql": "^10.0.0"
  },
  "devDependencies": {
    "@types/mssql": "^9.1.0"
  }
}

任务 2.2: 实现 SQL Server 驱动 (Day 2-10)

步骤:

创建驱动文件

mkdir -p src/drivers/sqlserver
touch src/drivers/sqlserver/sqlserver-driver.ts

实现核心方法 (参考架构设计文档中的示例)

更新工厂

// src/drivers/driver-factory.ts

import { SqlServerDriver } from './sqlserver/sqlserver-driver.js';

export function createDriver(type: 'postgres' | 'sqlserver'): DatabaseDriver {
  switch (type) {
    case 'postgres':
      return new PostgresDriver();
    case 'sqlserver':
      return new SqlServerDriver();
    default:
      throw new Error(`Unsupported database type: ${type}`);
  }
}

验收标准:

SQL Server 驱动编译通过
所有接口方法都已实现
驱动可以成功创建 SQL Server 连接池

任务 2.3: 扩展配置类型 (Day 11-12)

步骤:

更新配置类型

// src/config/types.ts

export interface EnvironmentConfig {
  type: 'postgres' | 'sqlserver';
  connection: PostgresConnection | SqlServerConnection;
  // ... 其他字段
}

export interface SqlServerConnection {
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
  domain?: string;
  instanceName?: string;
  encrypt?: boolean;
  trustServerCertificate?: boolean;
}

更新配置加载器

// src/config/loader.ts

// 添加 SQL Server 配置验证

验收标准:

配置类型支持 SQL Server
配置验证通过

任务 2.4: 集成测试 (Day 13-15)

步骤:

设置 SQL Server 测试环境

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourStrong!Passw0rd' \
  -p 1433:1433 --name sqlserver \
  -d mcr.microsoft.com/mssql/server:2022-latest

编写集成测试

// __tests__/integration/sqlserver.test.ts

import { SqlServerDriver } from '../../src/drivers/sqlserver/sqlserver-driver';
import * as mssql from 'mssql';

describe('SQL Server Integration', () => {
  let driver: SqlServerDriver;
  let pool: mssql.ConnectionPool;

  beforeAll(async () => {
    driver = new SqlServerDriver();
    pool = await driver.createConnectionPool({
      host: 'localhost',
      port: 1433,
      database: 'master',
      user: 'sa',
      password: 'YourStrong!Passw0rd',
      encrypt: false,
      trustServerCertificate: true
    });
  });

  afterAll(async () => {
    await driver.closeConnectionPool(pool);
  });

  it('should connect and query', async () => {
    const result = await driver.execute(pool, 'SELECT 1 as num');
    expect(result.rows).toHaveLength(1);
    expect(result.rows[0].num).toBe(1);
  });

  it('should build paginated query', () => {
    const { sql, params } = driver.buildPaginatedQuery(
      'SELECT * FROM users',
      [],
      10,
      20
    );
    expect(sql).toContain('OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY');
  });

  // ... 更多测试
});

验收标准:

SQL Server 连接成功
基本 CRUD 操作通过
事务操作通过
元数据查询通过

阶段三：完整功能实现 (Week 7-9)

(继续实现元数据浏览、批量操作、诊断功能等...)

阶段四：测试、优化和文档 (Week 10)

任务 4.1: 完善测试 (Day 1-3)

任务 4.2: 性能测试 (Day 4-5)

任务 4.3: 更新文档 (Day 6-7)

任务 4.4: 发布准备 (Day 8-10)

步骤:

更新 changelog.json

{
  "version": "1.0.2",
  "date": "2025-01-XX",
  "description": "添加 SQL Server 数据库支持",
  "changes": [
    "实现数据库驱动抽象层",
    "添加 SQL Server 驱动",
    "支持混合环境 (PostgreSQL + SQL Server)",
    "重构核心层使用驱动接口",
    "所有 MCP 工具支持 SQL Server",
    "完整的文档和测试"
  ]
}

更新 package.json 版本
```
{
  "version": "1.0.2"
}
```
构建和测试
```
npm run build
npm test
npm start
```

验收标准:

所有测试通过
文档完整更新
changelog 更新
版本号正确

文档维护:

负责人: [待指定]
最后更新: 2024-12-27

32 KiB Raw Permalink Blame History Unescape Escape

v1.0.2 详细实施步骤

阶段一：基础架构重构 (Week 1-3)

任务 1.1: 设计数据库驱动接口 (Day 1-2)

任务 1.2: 创建驱动工厂 (Day 2)

任务 1.3: 提取 PostgreSQL 驱动 (Day 3-5)

任务 1.4: 重构 ConnectionManager (Day 6-7)

任务 1.5: 重构 QueryRunner (Day 8-9)

任务 1.6: 重构 MetadataBrowser, BulkHelpers, Diagnostics (Day 10-12)

任务 1.7: 更新 server.ts (Day 13)

任务 1.8: 测试和验证 (Day 14-21)

阶段二：SQL Server 基础支持 (Week 4-6)

任务 2.1: 安装 SQL Server 驱动依赖 (Day 1)

任务 2.2: 实现 SQL Server 驱动 (Day 2-10)

任务 2.3: 扩展配置类型 (Day 11-12)

任务 2.4: 集成测试 (Day 13-15)

阶段三：完整功能实现 (Week 7-9)

阶段四：测试、优化和文档 (Week 10)

任务 4.1: 完善测试 (Day 1-3)

任务 4.2: 性能测试 (Day 4-5)

任务 4.3: 更新文档 (Day 6-7)

任务 4.4: 发布准备 (Day 8-10)

32 KiB

Raw Permalink Blame History