/// <summary>
/// compare table index
/// </summary>
/// <param name="sourceTable"></param>
/// <param name="targetTable"></param>
private void CompareSingleKeys(Table sourceTable, Table targetTable)
{
foreach (var index in sourceTable.Indexes.Values)
{
var sql = $"alter table {Target.Schema}.{targetTable.TableName} ";
if (targetTable.Indexes.ContainsKey(index.IndexName) == false)
{
if (index.IndexName == "PRIMARY")
{
sql += "add primary key ";
}
else
{
if (index.NotUnique == "0")
{
sql += $"add unique {index.IndexName} ";
}
else
{
sql += $"add index {index.IndexName} ";
}
}
sql += $"(`";
foreach (var key in index.Columns)
{
sql += $"{key.Trim()}`,`";
}
sql = sql.Substring(0, sql.Length - 2) + ")";
ChangeSql.Add(sql + ";");
}
}
if (Option.IsDropRedundancy)
{
foreach (var index in targetTable.Indexes.Values)
{
if (sourceTable.Indexes.ContainsKey(index.IndexName) == false)
{
// drop index
var sql = $"alter table {Target.Schema}.{targetTable.TableName} ";
if (index.IndexName == "PRIMARY")
{
sql += "drop primary key ";
}
else
{
sql += $"drop index {index.IndexName} ";
}
ChangeSql.Add(sql + ";");
}
}
}
}
/// <summary>
/// compare all table
/// </summary>
private void CompareTables()
{
foreach (var t in Source.Tables.Values)
{
if (!Target.Tables.ContainsKey(t.TableName))
{
// if target not exist this table,create it!
ChangeSql.Add(t.CreateTable + ";");
continue;
}
// compare column and index
CompareSingleTable(t, Target.Tables[t.TableName]);
}
}
/// <summary>
/// compare table column
/// </summary>
/// <param name="sourceTable"></param>
/// <param name="targetTable"></param>
private void CompareColumns(Table sourceTable, Table targetTable)
{
// 记录最后一个比较的column
string after = null;
foreach (var column in sourceTable.Columns.Values)
{
if (!targetTable.Columns.ContainsKey(column.Name))
{
// 如果对应的target没有这个字段,直接alter
var sql = $"alter table {Target.Schema}.{targetTable.TableName} add COLUMN {column.Name} {column.Type} ";
sql += column.IsNull == "NO" ? "NOT NULL " : "NULL ";
if (!string.IsNullOrWhiteSpace(column.DefaultValue))
{
if (column.Type.Contains("varchar"))
{
sql += $"DEFAULT '{column.DefaultValue}' ";
}
else
{
sql += $"DEFAULT {column.DefaultValue} ";
}
}
if (!string.IsNullOrWhiteSpace(column.Comment))
{
sql += $"COMMENT '{column.Comment}' ";
}
if (after != null)
{
sql += $"after {after}";
}
ChangeSql.Add(sql + ";");
}
else
{
var sql = $"alter table {Target.Schema}.{targetTable.TableName} change {column.Name} ";
// 比较两者字段,如果返回null,表明一致
string sqlExtend = CompareSingleColumn(column, targetTable.Columns[column.Name]);
if (sqlExtend != null)
{
ChangeSql.Add(sql + sqlExtend + ";");
}
}
after = column.Name;
}
if (Option.IsDropRedundancy)
{
// remove the target redundancy columns
foreach (var column in targetTable.Columns.Values)
{
if (!sourceTable.Columns.ContainsKey(column.Name))
{
// redundancy , so drop it
var sql = $"alter table {Target.Schema}.{targetTable.TableName} drop COLUMN {column.Name} ";
ChangeSql.Add(sql + ";");
}
}
}
}
