import { DatabaseService } from './database-service.js';
import { TableInfo, ColumnInfo } from '../types.js';
export interface SchemaDifference {
missingTables: TableInfo[];
extraTables: TableInfo[];
tableDifferences: {
tableName: string;
missingColumns: ColumnInfo[];
extraColumns: ColumnInfo[];
typeMismatches: { column: string; sourceType: string; targetType: string; }[];
}[];
}
export class SchemaCompareService {
constructor(
private source: DatabaseService,
private target: DatabaseService
) {}
async compare(): Promise<SchemaDifference> {
const sourceTables = await this.source.listTables();
const targetTables = await this.target.listTables();
const sourceTableNames = new Set(sourceTables.map(t => t.name));
const targetTableNames = new Set(targetTables.map(t => t.name));
const missingTables = sourceTables.filter(t => !targetTableNames.has(t.name));
const extraTables = targetTables.filter(t => !sourceTableNames.has(t.name));
const commonTables = sourceTables.filter(t => targetTableNames.has(t.name));
const tableDifferences = [];
for (const table of commonTables) {
try {
const sourceCols = await this.source.describeTable(table.name);
const targetCols = await this.target.describeTable(table.name);
const diff = this.compareColumns(table.name, sourceCols, targetCols);
if (diff) {
tableDifferences.push(diff);
}
} catch (e) {
console.error(`Error comparing table ${table.name}`, e);
}
}
return {
missingTables,
extraTables,
tableDifferences
};
}
private compareColumns(tableName: string, source: ColumnInfo[], target: ColumnInfo[]) {
const sourceMap = new Map(source.map(c => [c.name, c]));
const targetMap = new Map(target.map(c => [c.name, c]));
const missingColumns = source.filter(c => !targetMap.has(c.name));
const extraColumns = target.filter(c => !sourceMap.has(c.name));
const typeMismatches = [];
for (const srcCol of source) {
const tgtCol = targetMap.get(srcCol.name);
if (tgtCol) {
if (srcCol.dataType !== tgtCol.dataType) {
typeMismatches.push({
column: srcCol.name,
sourceType: srcCol.dataType,
targetType: tgtCol.dataType
});
}
}
}
if (missingColumns.length === 0 && extraColumns.length === 0 && typeMismatches.length === 0) {
return null;
}
return {
tableName,
missingColumns,
extraColumns,
typeMismatches
};
}
async generateDDL(diff: SchemaDifference): Promise<string> {
let ddl = `-- Migration Script Generated by DBA MCP\n-- Source -> Target\n\n`;
// 1. Create missing tables
for (const table of diff.missingTables) {
try {
const columns = await this.source.describeTable(table.name);
const pks = await this.source.listPrimaryKeys(table.name);
ddl += this.generateCreateTable(table.name, columns, pks) + ';\n\n';
} catch(e) {
ddl += `-- Error generating DDL for ${table.name}: ${e}\n\n`;
}
}
// 2. Alter tables
for (const td of diff.tableDifferences) {
if (td.missingColumns.length > 0) {
ddl += `-- Table: ${td.tableName} - Adding columns\n`;
for (const col of td.missingColumns) {
ddl += `ALTER TABLE ${td.tableName} ADD ${col.name} ${this.mapType(col.dataType)};\n`;
}
ddl += '\n';
}
if (td.typeMismatches.length > 0) {
ddl += `-- Table: ${td.tableName} - Type mismatches\n`;
for (const mm of td.typeMismatches) {
ddl += `-- Warn: ${mm.column} Source=${mm.sourceType} Target=${mm.targetType}\n`;
}
ddl += '\n';
}
}
return ddl;
}
public generateCreateTable(tableName: string, columns: ColumnInfo[], pks: string[]): string {
const colDefs = columns.map(c => {
return ` ${c.name} ${this.mapType(c.dataType)}${c.nullable ? '' : ' NOT NULL'}`;
});
if (pks.length > 0) {
colDefs.push(` CONSTRAINT pk_${tableName} PRIMARY KEY (${pks.join(', ')})`);
}
return `CREATE TABLE ${tableName} (\n${colDefs.join(',\n')}\n)`;
}
private mapType(type: string): string {
const t = type.toUpperCase();
// Basic normalization for common types
if (t.includes('CHAR') || t.includes('TEXT') || t.includes('CLOB')) return 'VARCHAR(255)'; // Default safe-ish
if (t.includes('INT') || t.includes('NUMBER') || t.includes('DECIMAL')) return 'INTEGER';
if (t.includes('DATE') || t.includes('TIME')) return 'TIMESTAMP';
return type;
}
}
