utils.ts•3.65 kB
import fs from 'fs/promises';
import path from 'path';
import { createObjectCsvWriter } from 'csv-writer';
import csvParser from 'csv-parser';
import { createReadStream } from 'fs';
import { ExportOptions, ImportOptions, QueryResult } from './types.js';
/**
* 导出数据到文件
*/
export async function exportData(data: QueryResult, options: ExportOptions): Promise<void> {
const { format, filePath, includeHeaders = true } = options;
// 确保目录存在
const dir = path.dirname(filePath);
await fs.mkdir(dir, { recursive: true });
if (format === 'json') {
await fs.writeFile(filePath, JSON.stringify(data.rows, null, 2), 'utf-8');
} else if (format === 'csv') {
if (data.rows.length === 0) {
await fs.writeFile(filePath, '', 'utf-8');
return;
}
const headers = Object.keys(data.rows[0]).map(key => ({ id: key, title: key }));
const csvWriter = createObjectCsvWriter({
path: filePath,
header: headers,
encoding: 'utf8'
});
await csvWriter.writeRecords(data.rows);
}
}
/**
* 从文件导入数据
*/
export async function importData(options: ImportOptions): Promise<any[]> {
const { format, filePath } = options;
if (format === 'json') {
const content = await fs.readFile(filePath, 'utf-8');
return JSON.parse(content);
} else if (format === 'csv') {
return new Promise((resolve, reject) => {
const results: any[] = [];
createReadStream(filePath)
.pipe(csvParser())
.on('data', (data: any) => results.push(data))
.on('end', () => resolve(results))
.on('error', reject);
});
}
throw new Error(`不支持的格式: ${format}`);
}
/**
* 格式化 SQL 查询结果
*/
export function formatQueryResult(result: QueryResult): string {
if (result.affectedRows !== undefined) {
return `操作完成，影响行数: ${result.affectedRows}${result.insertId ? `，插入ID: ${result.insertId}` : ''}`;
}
if (result.rows.length === 0) {
return '查询结果为空';
}
return `查询成功，返回 ${result.rows.length} 行数据`;
}
/**
* 验证 SQL 语句安全性（基础检查）
*/
export function validateSQL(sql: string): void {
const dangerousPatterns = [
/drop\s+database/i,
/drop\s+schema/i,
/truncate\s+table/i,
/delete\s+from.*where\s*$/i, // DELETE without WHERE
/update.*set.*where\s*$/i, // UPDATE without WHERE
];
for (const pattern of dangerousPatterns) {
if (pattern.test(sql.trim())) {
throw new Error('检测到潜在危险的 SQL 语句，请谨慎操作');
}
}
}
/**
* 生成表创建 SQL
*/
export function generateCreateTableSQL(tableName: string, columns: Array<{
name: string;
type: string;
nullable?: boolean;
defaultValue?: string;
primaryKey?: boolean;
autoIncrement?: boolean;
comment?: string;
}>): string {
const columnDefs = columns.map(col => {
let def = `\`${col.name}\` ${col.type}`;
if (!col.nullable) {
def += ' NOT NULL';
}
if (col.autoIncrement) {
def += ' AUTO_INCREMENT';
}
if (col.defaultValue !== undefined) {
def += ` DEFAULT ${col.defaultValue}`;
}
if (col.comment) {
def += ` COMMENT '${col.comment}'`;
}
return def;
});
const primaryKeys = columns.filter(col => col.primaryKey).map(col => col.name);
if (primaryKeys.length > 0) {
columnDefs.push(`PRIMARY KEY (\`${primaryKeys.join('`, `')}\`)`);
}
return `CREATE TABLE \`${tableName}\` (\n ${columnDefs.join(',\n ')}\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`;
}