export class SchemaCompareService {
source;
target;
constructor(source, target) {
this.source = source;
this.target = target;
}
async compare() {
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
};
}
compareColumns(tableName, source, target) {
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) {
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;
}
generateCreateTable(tableName, columns, pks) {
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)`;
}
mapType(type) {
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;
}
}
