第八章：数据操作功能

本章介绍 VSDB 的数据操作功能，包括查询执行、导入导出、数据编辑等。

8.1 查询执行流程

执行流程概览

sequenceDiagram
    participant User as 用户
    participant Editor as SqlEditor
    participant Panel as SqlEditorPanel
    participant IPC as IpcManager
    participant Worker as Worker Process
    participant Driver as DatabaseDriver
    participant DB as Database
    
    User->>Editor: 输入 SQL + Ctrl+Enter
    Editor->>Panel: postMessage(executeQuery)
    Panel->>Panel: 记录查询历史
    Panel->>IPC: sendRequest(type=query)
    IPC->>Worker: WorkerRequest
    Worker->>Driver: driver.query(sql)
    Driver->>DB: 执行 SQL
    DB-->>Driver: 结果
    Driver-->>Worker: QueryResult
    Worker-->>IPC: WorkerResponse
    IPC-->>Panel: WorkerResponse
    Panel-->>Editor: postMessage(queryResult)
    Editor-->>User: 渲染结果表格

查询历史记录

// HistoryManager.ts
export class HistoryManager {
  private maxItems = 100;
  
  constructor(private globalState: vscode.Memento) {}
  
  add(item: QueryHistoryItem): void {
    const history = this.getList();
    
    // 添加新项
    history.unshift(item);
    
    // 清理超出上限的未固定项
    while (history.length > this.maxItems) {
      const unpinnedIndex = history.findIndex(h => !h.pinned);
      if (un pinnedIndex >= 0) {
        history.splice(unpinnedIndex, 1);
      } else {
        break;  // 全部固定，不再清理
      }
    }
    
    this.globalState.update('queryHistory', history);
  }
  
  getList(limit?: number): QueryHistoryItem[] {
    const history = this.globalState.get<QueryHistoryItem[]>('queryHistory', []);
    return limit ? history.slice(0, limit) : history;
  }
  
  pin(id: string): void {
    const history = this.getList();
    const item = history.find(h => h.id === id);
    if (item) {
      item.pinned = true;
      this.globalState.update('queryHistory', history);
    }
  }
  
  remove(id: string): void {
    const history = this.getList().filter(h => h.id !== id);
    this.globalState.update('queryHistory', history);
  }
  
  clear(): void {
    const history = this.getList().filter(h => h.pinned);
    this.globalState.update('queryHistory', history);
  }
}

执行时间统计

// Driver 端计时
async query(sql: string, params?: any[]): Promise<QueryResult> {
  const startTime = Date.now();
  const result = await this.connection.execute(sql, params);
  const executionTime = Date.now() - startTime;
  
  return {
    ...result,
    executionTime,
  };
}

// 历史记录包含执行时间
const historyItem: QueryHistoryItem = {
  id: uuid(),
  sql,
  connectionId,
  connectionName: conn.name,
  executedAt: new Date(),
  rowCount: result.rowCount,
  executionTime: result.executionTime,
};

8.2 导入导出功能

导出管理器

export class ExportManager {
  async exportToCsv(
    result: QueryResult,
    filePath: string
  ): Promise<void> {
    const lines: string[] = [];
    
    // 头行：列名
    lines.push(result.columns.join(','));
    
    // 数据行
    for (const row of result.rows) {
      const values = result.columns.map(col => {
        const val = row[col];
        // 处理特殊字符
        if (val === null || val === undefined) return '';
        if (typeof val === 'string' && (val.includes(',') || val.includes('"'))) {
          return `"${val.replace(/"/g, '""')}"`;
        }
        return String(val);
      });
      lines.push(values.join(','));
    }
    
    fs.writeFileSync(filePath, lines.join('\n'), 'utf-8');
  }
  
  async exportToJson(
    result: QueryResult,
    filePath: string
  ): Promise<void> {
    const data = {
      columns: result.columns,
      rows: result.rows,
      rowCount: result.rowCount,
      exportedAt: new Date().toISOString(),
    };
    
    fs.writeFileSync(filePath, JSON.stringify(data, null, 2), 'utf-8');
  }
  
  async exportToSql(
    result: QueryResult,
    filePath: string,
    tableName: string
  ): Promise<void> {
    const lines: string[] = [];
    
    for (const row of result.rows) {
      const columns = result.columns.map(c => `\`${c}\``).join(', ');
      const values = result.columns.map(c => {
        const val = row[c];
        if (val === null) return 'NULL';
        if (typeof val === 'string') return `'${val.replace(/'/g, "''")}'`;
        if (typeof val === 'number') return String(val);
        return `'${String(val)}'`;
      }).join(', ');
      
      lines.push(`INSERT INTO \`${tableName}\` (${columns}) VALUES (${values});`);
    }
    
    fs.writeFileSync(filePath, lines.join('\n'), 'utf-8');
  }
}

导入管理器

export class ImportManager {
  async importFromCsv(
    filePath: string,
    connectionId: string,
    tableName: string
  ): Promise<{ imported: number; errors: string[] }> {
    const content = fs.readFileSync(filePath, 'utf-8');
    const lines = content.split('\n');
    
    if (lines.length < 2) {
      throw new Error('CSV file is empty or has no data');
    }
    
    // 第一行是列名
    const columns = this.parseCsvLine(lines[0]);
    const errors: string[] = [];
    let imported = 0;
    
    for (let i = 1; i < lines.length; i++) {
      try {
        const values = this.parseCsvLine(lines[i]);
        const sql = this.buildInsertSql(tableName, columns, values);
        
        await ipcManager.sendRequest({
          type: 'query',
          connectionId,
          payload: { sql },
        });
        
        imported++;
      } catch (err: any) {
        errors.push(`Row ${i}: ${err.message}`);
      }
    }
    
    return { imported, errors };
  }
  
  async importFromJson(
    filePath: string,
    connectionId: string,
    tableName: string
  ): Promise<{ imported: number; errors: string[] }> {
    const content = fs.readFileSync(filePath, 'utf-8');
    const data = JSON.parse(content);
    
    const rows = Array.isArray(data.rows) ? data.rows : 
                 Array.isArray(data) ? data : [data];
    
    const errors: string[] = [];
    let imported = 0;
    
    for (const row of rows) {
      try {
        const columns = Object.keys(row);
        const values = columns.map(c => row[c]);
        const sql = this.buildInsertSql(tableName, columns, values);
        
        await ipcManager.sendRequest({
          type: 'query',
          connectionId,
          payload: { sql },
        });
        
        imported++;
      } catch (err: any) {
        errors.push(`Row ${imported + errors.length + 1}: ${err.message}`);
      }
    }
    
    return { imported, errors };
  }
}

CSV 解析

private parseCsvLine(line: string): string[] {
  const values: string[] = [];
  let current = '';
  let inQuotes = false;
  
  for (let i = 0; i < line.length; i++) {
    const char = line[i];
    
    if (inQuotes) {
      if (char === '"') {
        if (line[i + 1] === '"') {
          current += '"';
          i++;
        } else {
          inQuotes = false;
        }
      } else {
        current += char;
      }
    } else {
      if (char === '"') {
        inQuotes = true;
      } else if (char === ',') {
        values.push(current);
        current = '';
      } else {
        current += char;
      }
    }
  }
  
  values.push(current);
  return values;
}

8.3 数据编辑

内联编辑模式

graph TD
    A["查看数据"] --> B["启用编辑模式"]
    B --> C["点击单元格"]
    C --> D["输入新值"]
    D --> E{"确认修改"}
    E -->|"Enter"| F["标记为已修改"]
    E -->|"Esc"| G["取消修改"]
    
    F --> H["继续编辑或提交"]
    H --> I["提交更改"]
    I --> J["生成 UPDATE SQL"]
    J --> K["执行 SQL"]
    K --> L["刷新数据"]

EditManager 实现

class EditManager {
  private edits = new Map<string, { original: any; modified: any }>();
  private editMode: EditMode = 'readonly';
  
  startEdit(rowIndex: number, column: string, originalValue: any): void {
    const key = `${rowIndex}:${column}`;
    this.edits.set(key, { original: originalValue, modified: originalValue });
    this.editMode = 'inline';
  }
  
  updateValue(rowIndex: number, column: string, newValue: any): void {
    const key = `${rowIndex}:${column}`;
    const edit = this.edits.get(key);
    if (edit) {
      edit.modified = newValue;
    }
  }
  
  cancelEdit(rowIndex: number, column: string): void {
    const key = `${rowIndex}:${column}`;
    this.edits.delete(key);
  }
  
  hasEdits(): boolean {
    return this.edits.size > 0;
  }
  
  getEdits(): Array<{ rowIndex: number; column: string; original: any; modified: any }> {
    return Array.from(this.edits.entries())
      .filter(([_, edit]) => edit.original !== edit.modified)
      .map(([key, edit]) => {
        const [rowIndex, column] = key.split(':');
        return {
          rowIndex: parseInt(rowIndex),
          column,
          original: edit.original,
          modified: edit.modified,
        };
      });
  }
  
  buildUpdateSql(
    tableName: string,
    primaryKeyColumns: string[],
    rows: any[]
  ): string[] {
    const sqls: string[] = [];
    
    for (const edit of this.getEdits()) {
      const row = rows[edit.rowIndex];
      const whereClause = primaryKeyColumns
        .map(pk => `${pk} = ${this.formatValue(row[pk])}`)
        .join(' AND ');
      
      sqls.push(
        `UPDATE ${tableName} SET ${edit.column} = ${this.formatValue(edit.modified)} WHERE ${whereClause}`
      );
    }
    
    return sqls;
  }
  
  private formatValue(value: any): string {
    if (value === null) return 'NULL';
    if (typeof value === 'number') return String(value);
    return `'${String(value).replace(/'/g, "''")}'`;
  }
}

批量编辑提交

const commitEdits = async () => {
  if (!editManager.hasEdits()) return;
  
  const edits = editManager.getEdits();
  
  // 确认对话框
  const confirm = await vscode.window.showWarningMessage(
    `Apply ${edits.length} changes to ${tableName}?`,
    { modal: true },
    'Apply',
    'Cancel'
  );
  
  if (confirm !== 'Apply') return;
  
  // 生成并执行 SQL
  const sqls = editManager.buildUpdateSql(tableName, primaryKeyColumns, rows);
  
  for (const sql of sqls) {
    try {
      await ipcManager.sendRequest({
        type: 'query',
        connectionId,
        payload: { sql },
      });
    } catch (err) {
      // 错误处理
    }
  }
  
  // 刷新数据
  editManager.clearEdits();
  refreshData();
};

8.4 数据搜索

SearchEngine 实现

export class SearchEngine {
  private schemaCache = new Map<string, SchemaCacheEntry>();
  
  constructor(
    private ipcManager: IpcManager,
    private connectionManager: ConnectionManager,
  ) {}
  
  async searchAll(
    keyword: string,
    options: SearchOptions = {}
  ): Promise<SearchResult[]> {
    const results: SearchResult[] = [];
    
    const connectionIds = options.connectionIds || 
      await this.getAllConnectionIds();
    
    for (const connectionId of connectionIds) {
      if (options.searchTables) {
        const tables = await this.searchTables(connectionId, keyword);
        results.push(...tables);
      }
      
      if (options.searchColumns) {
        const columns = await this.searchColumns(connectionId, keyword);
        results.push(...columns);
      }
      
      if (options.searchData) {
        const data = await this.searchData(connectionId, keyword, options.tables);
        results.push(...data);
      }
    }
    
    return results;
  }
  
  async searchTables(connectionId: string, keyword: string): Promise<SearchResult[]> {
    const schema = await this.getSchema(connectionId);
    const results: SearchResult[] = [];
    
    for (const table of schema.tables) {
      if (this.matches(table.name, keyword)) {
        results.push({
          type: 'table',
          connectionId,
          connectionName: await this.getConnectionName(connectionId),
          database: table.schema || '',
          name: table.name,
          detail: `Table (${table.rowCount || '?'} rows)`,
        });
      }
    }
    
    return results;
  }
  
  async searchColumns(connectionId: string, keyword: string): Promise<SearchResult[]> {
    const schema = await this.getSchema(connectionId);
    const results: SearchResult[] = [];
    
    for (const column of schema.columns) {
      if (this.matches(column.name, keyword)) {
        results.push({
          type: 'column',
          connectionId,
          connectionName: await this.getConnectionName(connectionId),
          database: '',  // 需要关联表获取
          name: column.name,
          detail: `${column.type} in ${column.tableName}`,
        });
      }
    }
    
    return results;
  }
  
  private matches(value: string, keyword: string, caseSensitive = false): boolean {
    if (!caseSensitive) {
      return value.toLowerCase().includes(keyword.toLowerCase());
    }
    return value.includes(keyword);
  }
  
  private async getSchema(connectionId: string): Promise<SchemaCacheEntry> {
    const cached = this.schemaCache.get(connectionId);
    
    if (cached && Date.now() - cached.cachedAt.getTime() < 60000) {
      return cached;  // 1 分钟缓存
    }
    
    // 查询 Schema
    const tablesResponse = await this.ipcManager.sendRequest({
      type: 'schema',
      connectionId,
      payload: { schemaType: 'tables' },
    });
    
    const columnsResponse = await this.ipcManager.sendRequest({
      type: 'schema',
      connectionId,
      payload: { schemaType: 'columns' },
    });
    
    const entry: SchemaCacheEntry = {
      tables: tablesResponse.data?.data || [],
      columns: columnsResponse.data?.data || [],
      cachedAt: new Date(),
    };
    
    this.schemaCache.set(connectionId, entry);
    return entry;
  }
  
  clearCache(): void {
    this.schemaCache.clear();
  }
}

8.5 小结

本章介绍了 VSDB 的数据操作功能：

关键设计：

• 查询历史：上限 100 条，支持固定防清理
• 导出格式：CSV（逗号分隔）、JSON（结构化）、SQL（INSERT 语句）
• 内联编辑：标记修改、批量提交、主键定位
• 搜索缓存：Schema 缓存 1 分钟，提升搜索性能

下一章将介绍辅助功能系统。