private static string CompareData(CompareSideType sidetype, TableSchema schema1, TableName tname1, TableSchema schema2, TableName tname2, IDictionary <string, string[]> pk, string[] exceptColumns)
{
string sql;
bool hasPk = schema1.PrimaryKeys.Length > 0;
sql = Compare.TableDifference(sidetype, schema1, schema2, schema1.PrimaryKeys.Keys, exceptColumns);
if (!hasPk)
{
cout.WriteLine("warning: no primary key found : {0}", tname1);
string key = tname1.Name.ToUpper();
if (pk.ContainsKey(key))
{
cout.WriteLine("use predefine keys defined in ini file: {0}", tname1);
sql = Compare.TableDifference(sidetype, schema1, schema2, pk[key], exceptColumns);
}
else
{
cout.WriteLine("use entire row as primary keys:{0}", tname1);
var keys = schema1.Columns.Select(row => row.ColumnName).ToArray();
sql = Compare.TableDifference(sidetype, schema1, schema2, keys, exceptColumns);
}
}
cout.WriteLine("completed to {0} table data {1} => {2}", sidetype, tname1, tname2);
return(sql);
}
public static string DatabaseSchemaDifference(CompareSideType sideType, DatabaseName dname1, DatabaseName dname2)
{
TableName[] names = dname1.GetDependencyTableNames();
StringBuilder builder = new StringBuilder();
foreach (TableName tableName in names)
{
#if DEBUG
cout.WriteLine(tableName.ShortName);
#endif
try
{
string sql = TableSchemaDifference(sideType, tableName, new TableName(dname2, tableName.SchemaName, tableName.Name));
builder.Append(sql);
#if DEBUG
if (sql != string.Empty)
{
cout.WriteLine(sql);
}
#endif
}
catch (Exception ex)
{
cerr.WriteLine("error:" + ex.Message);
}
}
return(builder.ToString());
}
public static string DatabaseSchemaDifference(CompareSideType sideType, DatabaseName dname1, DatabaseName dname2)
{
TableName[] names = dname1.GetDependencyTableNames();
StringBuilder builder = new StringBuilder();
foreach (TableName tableName in names)
{
#if DEBUG
Console.WriteLine(tableName.ShortName);
#endif
try
{
string sql = TableSchemaDifference(sideType, tableName, new TableName(dname2, tableName.SchemaName, tableName.Name));
builder.Append(sql);
#if DEBUG
if (sql != string.Empty)
Console.WriteLine(sql);
#endif
}
catch (Exception ex)
{
Console.WriteLine("error:" + ex.Message);
}
}
return builder.ToString();
}
private string CompareDatabaseData(CompareSideType sideType, DatabaseName db1, DatabaseName db2, string[] excludedtables)
{
cout.WriteLine("compare database data {0} => {1}", db1.Name, db2.Name);
if (excludedtables != null && excludedtables.Length > 0)
{
cout.WriteLine("ignore tables: {0}", string.Join(",", excludedtables));
}
return(Compare.DatabaseDifference(sideType, db1, db2, excludedtables));
}
public string CompareTable(ActionType actiontype, CompareSideType sidetype, TableName tname1, TableName tname2, Dictionary<string, string[]> pk, string[] exceptColumns)
{
TableSchema schema1 = new TableSchema(tname1);
TableSchema schema2 = new TableSchema(tname2);
if (!Exists(tname1))
return string.Empty;
string sql = string.Empty;
if (actiontype == ActionType.CompareSchema)
{
sql = Compare.TableSchemaDifference(sidetype, tname1, tname2);
stdio.WriteLine("completed to {0} table schema {1} => {2}", sidetype, tname1, tname2);
}
else if (actiontype == ActionType.CompareData)
{
if (!Exists(tname2))
{
return string.Empty;
}
if (Compare.TableSchemaDifference(sidetype, tname1, tname2) != string.Empty)
{
stdio.WriteLine("failed to {0} becuase of different table schemas", sidetype);
return string.Empty;
}
bool hasPk = schema1.PrimaryKeys.Length > 0;
sql = Compare.TableDifference(sidetype, schema1, schema2, schema1.PrimaryKeys.Keys, exceptColumns);
if (!hasPk)
{
stdio.WriteLine("warning: no primary key found : {0}", tname1);
string key = tname1.Name.ToUpper();
if (pk.ContainsKey(key))
{
stdio.WriteLine("use predefine keys defined in ini file: {0}", tname1);
sql = Compare.TableDifference(sidetype, schema1, schema2, pk[key], exceptColumns);
}
else
{
stdio.WriteLine("use entire row as primary keys:{0}", tname1);
var keys = schema1.Columns.Select(row => row.ColumnName).ToArray();
sql = Compare.TableDifference(sidetype, schema1, schema2, keys, exceptColumns);
}
}
stdio.WriteLine("completed to {0} table data {1} => {2}", sidetype, tname1, tname2);
}
if (sql != string.Empty && sidetype == CompareSideType.compare)
stdio.WriteLine(sql);
return sql;
}
public string CompareTable(ActionType actiontype, CompareSideType sidetype, TableName tname1, TableName tname2, IDictionary <string, string[]> pk, string[] exceptColumns)
{
if (!Exists(tname1))
{
return(string.Empty);
}
if (actiontype == ActionType.CompareRowCount)
{
return(CompareRowCount(tname1, tname2));
}
TableSchema schema1 = new TableSchema(tname1);
TableSchema schema2 = new TableSchema(tname2);
string sql = string.Empty;
if (actiontype == ActionType.CompareSchema)
{
sql = Compare.TableSchemaDifference(sidetype, tname1, tname2);
cout.WriteLine("completed to {0} table schema {1} => {2}", sidetype, tname1, tname2);
}
else if (actiontype == ActionType.CompareData)
{
if (!Exists(tname2))
{
return(string.Empty);
}
if (Compare.TableSchemaDifference(sidetype, tname1, tname2) != string.Empty)
{
cout.WriteLine("failed to {0} becuase of different table schemas", sidetype);
return(string.Empty);
}
sql = CompareData(sidetype, schema1, tname1, schema2, tname2, pk, exceptColumns);
}
if (sql != string.Empty && sidetype == CompareSideType.compare)
{
cout.WriteLine(sql);
}
return(sql);
}
public static string TableSchemaDifference(CompareSideType sideType, TableName tableName1, TableName tableName2)
{
string sql;
if (tableName2.Exists())
{
TableSchemaCompare compare = new TableSchemaCompare(tableName1, tableName2)
{
SideType = sideType
};
sql = compare.Compare();
}
else
{
sql = tableName1.GenerateCreateTableClause(appendGO: true);
}
return(sql);
}
public static string DatabaseDifference(CompareSideType sideType, DatabaseName dname1, DatabaseName dname2, string[] excludedTables)
{
TableName[] names = dname1.GetDependencyTableNames();
excludedTables = excludedTables.Select(row => row.ToUpper()).ToArray();
StringBuilder builder = new StringBuilder();
foreach (TableName tableName in names)
{
TableName tname1 = tableName;
TableName tname2 = new TableName(dname2, tableName.SchemaName, tableName.Name);
TableSchema schema1 = new TableSchema(tname1);
TableSchema schema2 = new TableSchema(tname2);
cout.WriteLine(tname1.ShortName);
if (excludedTables.Contains(tableName.ShortName.ToUpper()))
{
cout.WriteLine("skip to compare data on excluded table {0}", tableName.ShortName);
continue;
}
if (schema1.PrimaryKeys.Length == 0)
{
cout.WriteLine("undefined primary key");
continue;
}
if (tname2.Exists())
{
builder.Append(TableDifference(sideType, schema1, schema2, schema1.PrimaryKeys.Keys, new string[] { }));
}
else
{
builder.Append(Compare.GenerateRows(schema1, new TableReader(tname1)));
}
builder.AppendLine();
}
return(builder.ToString());
}
public static string DatabaseDifference(CompareSideType sideType, DatabaseName dname1, DatabaseName dname2, string[] excludedTables)
{
TableName[] names = dname1.GetDependencyTableNames();
excludedTables = excludedTables.Select(row => row.ToUpper()).ToArray();
StringBuilder builder = new StringBuilder();
foreach (TableName tableName in names)
{
TableName tname1 = tableName;
TableName tname2 = new TableName(dname2, tableName.SchemaName, tableName.Name);
TableSchema schema1 = new TableSchema(tname1);
TableSchema schema2 = new TableSchema(tname2);
Console.WriteLine(tname1.ShortName);
if (excludedTables.Contains(tableName.ShortName.ToUpper()))
{
Console.WriteLine("skip to compare data on excluded table {0}", tableName.ShortName);
continue;
}
if (schema1.PrimaryKeys.Length == 0)
{
Console.WriteLine("undefined primary key");
continue;
}
if (tname2.Exists())
{
builder.Append(TableDifference(sideType, schema1, schema2, schema1.PrimaryKeys.Keys, new string[] { }));
}
else
{
builder.Append(Compare.GenerateRows(schema1, new TableReader(tname1)));
}
builder.AppendLine();
}
return builder.ToString();
}
public static string TableDifference(CompareSideType sideType, ITableSchema schema1, ITableSchema schema2, string[] primaryKeys, string[] exceptColumns)
{
//don't compare identity column or computed column
exceptColumns = schema1.Columns
.Where(column => column.IsComputed || (column.IsIdentity && !column.IsPrimary))
.Select(column => column.ColumnName)
.Union(exceptColumns)
.Distinct()
.ToArray();
TableCompare compare = new TableCompare(schema1, schema2)
{
SideType = sideType,
ExceptColumns = exceptColumns
};
IPrimaryKeys keys = new PrimaryKeys(primaryKeys);
return(compare.Compare(keys));
}
private string CompareDatabaseSchema(CompareSideType sideType, DatabaseName db1, DatabaseName db2)
{
cout.WriteLine("{0} database schema {1} => {2}", sideType, db1.Name, db2.Name);
return(Compare.DatabaseSchemaDifference(sideType, db1, db2));
}
private string CompareDatabaseSchema(CompareSideType sideType, DatabaseName db1, DatabaseName db2)
{
stdio.WriteLine("{0} database schema {1} => {2}", sideType, db1.Name, db2.Name);
return Compare.DatabaseSchemaDifference(sideType, db1, db2);
}
private string CompareDatabaseData(CompareSideType sideType, DatabaseName db1, DatabaseName db2, string[] excludedtables)
{
stdio.WriteLine("compare database data {0} => {1}", db1.Name, db2.Name);
if (excludedtables != null && excludedtables.Length > 0)
stdio.WriteLine("ignore tables: {0}", string.Join(",", excludedtables));
return Compare.DatabaseDifference(sideType, db1, db2, excludedtables);
}
public static string TableSchemaDifference(CompareSideType sideType, TableName tableName1, TableName tableName2)
{
string sql;
if (tableName2.Exists())
{
TableSchemaCompare compare = new TableSchemaCompare(tableName1, tableName2) { SideType = sideType };
sql = compare.Compare();
}
else
{
sql = tableName1.GenerateCluase();
}
return sql;
}
public static string TableDifference(CompareSideType sideType, TableSchema schema1, TableSchema schema2, string[] primaryKeys, string[] exceptColumns)
{
TableCompare compare = new TableCompare(schema1, schema2) { SideType = sideType, ExceptColumns = exceptColumns };
IPrimaryKeys keys = new PrimaryKeys(primaryKeys);
return compare.Compare(keys);
}
public void copy(Command cmd, CompareSideType sideType)
{
if (cmd.HasHelp)
{
if (sideType == CompareSideType.copy)
{
stdio.WriteLine("copy schema or records from table1 to table2, support table name wildcards");
stdio.WriteLine("copy table1 [table2] [/s]");
}
else if (sideType == CompareSideType.sync)
{
stdio.WriteLine("synchronize schema or records from table1 to table2");
stdio.WriteLine("sync table1 [table2] [/s] : sync table1' records to table2");
}
else if (sideType == CompareSideType.compare)
{
stdio.WriteLine("compare schema or records from table1 to table2");
stdio.WriteLine("comp table1 [table2] [/s] : sync table1' records to table2");
}
stdio.WriteLine("support table name wildcards");
stdio.WriteLine("[/s] : table schema, default table records");
return;
}
CancelableWork.CanCancel(cts =>
{
PathBothSide both = new PathBothSide(mgr, cmd);
var dname2 = mgr.GetPathFrom<DatabaseName>(both.ps2.Node);
if (both.ps1.MatchedTables == null)
return;
foreach (var tname1 in both.ps1.MatchedTables)
{
if (cts.IsCancellationRequested)
return;
TableName tname2 = mgr.GetPathFrom<TableName>(both.ps2.Node);
if (tname2 == null)
{
tname2 = new TableName(dname2, tname1.SchemaName, tname1.ShortName);
}
var adapter = new CompareAdapter(both.ps1.side, both.ps2.side);
//stdio.WriteLine("start to {0} from {1} to {2}", sideType, tname1, tname2);
var sql = adapter.CompareTable(cmd.IsSchema ? ActionType.CompareSchema : ActionType.CompareData,
sideType, tname1, tname2, mgr.Configuration.PK, cmd.Columns);
if (sideType == CompareSideType.compare)
{
if (sql == string.Empty)
{
stdio.WriteLine("source {0} and destination {1} are identical", tname1, tname2);
}
continue;
}
if (sql == string.Empty)
{
stdio.WriteLine("nothing changes made on destination {0}", tname2);
}
else
{
bool exists = tname2.Exists();
try
{
var sqlcmd = new SqlCmd(both.ps2.side.Provider, sql);
int count = sqlcmd.ExecuteNonQueryTransaction();
if (exists)
{
if (count >= 0)
stdio.WriteLine("{0} row(s) changed at destination {1}", count, tname2);
else
stdio.WriteLine("command(s) completed successfully at destination {1}", count, tname2);
}
else
stdio.WriteLine("table {0} created at destination", tname2);
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
return;
}
}
} // loop for
return;
});
}
