/// <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 + ";"); } } } }