private void DbDataSync()
{
if (config.type != 4)
{
if (config.type == 3)
{
//Remove target Foreign Keys
if (target.tables.Count > 0)
{
foreach (Table table in target.tables.Values)
{
tempTableSchema = table.schema;
break;
}
foreignKeyRefresh = new ForeignKeyRefresh(_target, tempTableSchema);
foreignKeyRefresh.load();
foreignKeyRefresh.dropForeignKey();
}
}
log.phaseUpdate("Data Sync");
log.statusUpdate("Tables");
log.log(Logger.LogLevel.progress, "");
log.log(Logger.LogLevel.progress, "SYNCHRONIZING DATA...");
log.indent();
string sourceKey;
string targetKey;
Table targetTable;
int rowCount;
double hundredthDivisor;
int progressValue;
int hundredthValue;
log.progressSet(0, source.tables.Count);
foreach (Table table in source.tables.Values)
{
//sourceKey = table.schema + "." + table.name;
sourceKey = table.name;
// TODO: May need to translate the target schema here!
//targetKey = table.schema + "." + table.name;
targetKey = table.name;
if (!ProcessData(table.name))
{
continue;
}
log.log(Logger.LogLevel.progress, "");
log.log(Logger.LogLevel.change, "Transfering data for tables: " + sourceKey + " -> " + targetKey);
log.log(Logger.LogLevel.progress, "");
log.indent();
targetTable = target.tables[targetKey];
log.statusUpdate(sourceKey);
// TODO: Status updates here
try
{
OdbcCommand countCommand = new OdbcCommand();
countCommand.Connection = source.connection;
countCommand.CommandType = CommandType.Text;
countCommand.CommandText = "select count(*) from " + sourceKey;
if (config.filterTables.ContainsKey(sourceKey))
{
TableFilter filter = (TableFilter)config.filterTables[sourceKey];
if (filter.active)
{
log.log(Logger.LogLevel.warning, "Data for table " + sourceKey + " is being filtered. All data may not be transferred.");
countCommand.CommandText += " " + filter.filter;
}
}
rowCount = (int)countCommand.ExecuteScalar();
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to get row count from " + sourceKey + ".");
log.log(Logger.LogLevel.error, "Data synchronization for " + sourceKey + " skipped.");
log.log(Logger.LogLevel.error, ex.Message);
continue;
}
if (config.transferMaxRows > 0 && rowCount > config.transferMaxRows)
{
log.log(Logger.LogLevel.error, "Table " + sourceKey + " contains " + rowCount + " rows which exceeds the maximum rows of " + config.transferMaxRows + " in configuration " + config.title + ". NO DATA WILL BE TRANSFERRED.");
continue;
}
hundredthDivisor = (double)rowCount / 100.0;
log.progressSet(1);
log.log(Logger.LogLevel.change, "Transfering " + rowCount + " rows for table " + targetKey + ".");
try
{
OdbcCommand deleteCommand = new OdbcCommand();
deleteCommand.Connection = target.connection;
deleteCommand.CommandType = CommandType.Text;
//deleteCommand.CommandText = "delete from " + targetKey;
if (target.dialect == Ddl.Dialect.db2)
deleteCommand.CommandText = "TRUNCATE TABLE " + targetKey + " IMMEDIATE";
else
deleteCommand.CommandText = "TRUNCATE TABLE " + targetKey;
deleteCommand.CommandTimeout = 0;
//if (config.type == 3 && config.filterTables.ContainsKey(sourceKey))
if (config.filterTables.ContainsKey(sourceKey))
{
TableFilter filter = (TableFilter)config.filterTables[sourceKey];
if (filter.active)
{
log.log(Logger.LogLevel.warning, "Data for table " + sourceKey + " is being filtered on a data transer only deployment. All data may not be deleted.");
deleteCommand.CommandText = "delete from " + targetKey;
deleteCommand.CommandText += " " + filter.filter;
}
log.log(Logger.LogLevel.change, "Successfully deleted " + deleteCommand.ExecuteNonQuery().ToString() + " from " + targetKey + ".");
}
else
{
deleteCommand.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Successfully deleted all records from " + targetKey + ".");
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to delete all records from " + targetKey + ".");
log.log(Logger.LogLevel.error, "Data synchronization for " + targetKey + " skipped.");
log.log(Logger.LogLevel.error, ex.Message);
continue;
}
OdbcCommand identityCommand = new OdbcCommand();
try
{
// TODO: Status updates here
identityCommand.Connection = target.connection;
identityCommand.CommandType = CommandType.Text;
// TODO: Status updates here
if (table.columns.identity != null)
{
switch (target.dialect)
{
case Ddl.Dialect.db2:
if (table.columns.identity.identity.generated.ToUpper() == "A")
{
identityCommand.CommandText = "ALTER TABLE " + targetKey + " " +
"ALTER COLUMN " + table.columns.identity.name + " " +
"SET GENERATED BY DEFAULT";
identityCommand.CommandTimeout = 0;
identityCommand.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Succussfully set generated by default for table " + targetKey + ".");
}
break;
case Ddl.Dialect.sqlServer:
identityCommand.CommandText = "set identity_insert " + targetKey + " on";
identityCommand.CommandTimeout = 0;
identityCommand.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Succussfully set identity insert on for table " + targetKey + ".");
break;
default:
log.log(Logger.LogLevel.error, "Unknown database dialect detected in DbSync - identity 1.");
log.log(Logger.LogLevel.error, "Data synchronization for " + targetKey + " skipped.");
continue;
}
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to setup identity column for data transfer for table " + targetKey + ".");
log.log(Logger.LogLevel.error, "Data synchronization for " + targetKey + " skipped.");
log.log(Logger.LogLevel.error, ex.Message);
continue;
}
int Count = 0;
OdbcDataReader reader = null;
try
{
// TODO: Status updates here
OdbcCommand readCommand = new OdbcCommand();
readCommand.Connection = source.connection;
readCommand.CommandType = CommandType.Text;
readCommand.CommandText = "select " + sourceKey + ".* from " + sourceKey;
if (config.filterTables.ContainsKey(sourceKey))
{
TableFilter filter = (TableFilter)config.filterTables[sourceKey];
if (filter.active)
readCommand.CommandText += " " + filter.filter;
}
if (target.dialect == Ddl.Dialect.db2)
{
//string PrimaryColumnName = findPrimaryKeyColumnName(table);
string PrimaryColumnName = findPrimaryKeyColumnName(targetTable);
if (PrimaryColumnName != "")
{
readCommand.CommandText += " Order by " + PrimaryColumnName;
}
}
reader = readCommand.ExecuteReader();
progressValue = 0;
int field;
// This is a code optimization for special data types (CLOBs)
if (table.columns.hasSpecialTypes)
{
// Same loop as the one below with special calls to make special data types work
while (reader.Read())
{
for (field = 0; field < reader.FieldCount; field++)
{
//logText.AppendText(reader[field].ToString());
////if (reader.IsDBNull(field)) MessageBox.Show("tesT");
//logText.AppendText(" - ");
//if (reader.IsDBNull(field))
// targetTable.insert.Parameters["@" + reader.GetName(field)].Value = DBNull.Value;
//else
if (reader.GetDataTypeName(field) == "CLOB")
reader.GetString(field);
targetTable.insert.Parameters["@" + reader.GetName(field)].Value = reader[field];
}
if (targetTable.insert.ExecuteNonQuery() != 1)
log.log(Logger.LogLevel.error, "Insert error occurred at record " + (Count + 1) + ".");
hundredthValue = (int)((float)(++Count) / hundredthDivisor);
if (progressValue != hundredthValue)
{
progressValue = hundredthValue;
log.progressUpdate(1, progressValue);
//log.log("Insert record count: " + Count);
}
//log.progressIncrement();
if ((Count % 500) == 0)
log.log(Logger.LogLevel.progress, "Insert record count: " + Count + " at " + DateTime.Now.ToLongTimeString());
}
}
else
{
while (reader.Read())
{
for (field = 0; field < reader.FieldCount; field++)
targetTable.insert.Parameters["@" + reader.GetName(field)].Value = reader[field];
if (targetTable.insert.ExecuteNonQuery() != 1)
log.log(Logger.LogLevel.error, "Insert error occurred at record " + (Count + 1) + ".");
hundredthValue = (int)((float)(++Count) / hundredthDivisor);
if (progressValue != hundredthValue)
{
progressValue = hundredthValue;
log.progressUpdate(1, progressValue);
}
if ((Count % 500) == 0)
log.log(Logger.LogLevel.progress, "Insert record count: " + Count + " at " + DateTime.Now.ToLongTimeString());
}
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while transfering data to " + targetKey + ".");
log.log(Logger.LogLevel.error, "Data synchronization for " + targetKey + " stopped.");
log.log(Logger.LogLevel.error, ex.Message);
continue;
}
finally
{
try { reader.Close(); }
catch { /*Do nothing*/ }
}
if (Count == rowCount)
log.log(Logger.LogLevel.change, "Successfully transfered " + Count + " of " + rowCount + " row(s) for table " + targetKey + ".");
else
log.log(Logger.LogLevel.error, "Only " + Count + " of " + rowCount + " row(s) were transfered for table " + targetKey + ".");
try
{
// TODO: Status updates here
if (table.columns.identity != null)
{
switch (target.dialect)
{
case Ddl.Dialect.db2:
if (table.columns.identity.identity.generated.ToUpper() == "A")
{
identityCommand.CommandText = "ALTER TABLE " + targetKey + " " +
"ALTER COLUMN " + table.columns.identity.name + " " +
"SET GENERATED BY DEFAULT";
identityCommand.CommandTimeout = 0;
identityCommand.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Successfully set generated always for table " + targetKey + ".");
}
//long maxIdentity = maxValue(table.columns.identity);
long maxIdentity = maxValue(table.columns.identity, "");
// RESET COUNTER
identityCommand.CommandText = "ALTER TABLE " + targetKey + " " +
"ALTER COLUMN " + table.columns.identity.name + " " +
"RESTART WITH " + (maxIdentity + 1);
identityCommand.CommandTimeout = 0;
identityCommand.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Successfully reset identity counter to " + (maxIdentity + 1) + ".");
break;
case Ddl.Dialect.sqlServer:
identityCommand.CommandText = "set identity_insert " + targetKey + " off";
identityCommand.CommandTimeout = 0;
identityCommand.ExecuteNonQuery();
log.log(Logger.LogLevel.change, "Successfully set identity insert off for table " + targetKey + ".");
break;
default:
log.log(Logger.LogLevel.error, "Unknown database dialect detected in DbSync - identity 1.");
log.log(Logger.LogLevel.error, "IDENTITY COLUMN FOR " + targetKey + " LEFT IN INAPPROPRIATE STATE. PLEASE FIX MANUALLY.");
break;
}
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to reset identity insert for table " + targetKey + ".");
log.log(Logger.LogLevel.error, "IDENTITY COLUMN FOR " + targetKey + " LEFT IN INAPPROPRIATE STATE. PLEASE FIX MANUALLY.");
log.log(Logger.LogLevel.error, ex.Message);
continue;
}
// Check to see if a sequence is associated with this table and if so update it.
//if (table.name == "E_PPL_PEOPLE")
//{
// bool testb = true;
//}
string sequenceColumnName = table.name;
/*
if (sequenceColumnName.ToUpper().EndsWith("_STATIC"))
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 8);
else
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 1);
*/
if (sequenceColumnName.ToUpper().EndsWith("_STATIC"))
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 8);
else if (sequenceColumnName.ToUpper().EndsWith("_PEOPLE"))
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 7) + "_PERSON";
else if (sequenceColumnName.ToUpper().EndsWith("_STATUSES"))
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 2);
else if (sequenceColumnName.ToUpper().EndsWith("_ENTRIES"))
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 3) + "Y";
else if (sequenceColumnName.ToUpper().EndsWith("_CLASSES"))
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 2);
else if (sequenceColumnName.ToUpper().EndsWith("_CATEGORIES"))
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 3) + "Y";
if (sequenceColumnName.ToUpper().EndsWith("_CONFIG"))
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 8);
else
sequenceColumnName = sequenceColumnName.Substring(0, sequenceColumnName.Length - 1);
string sequenceName = "SEQ_" + sequenceColumnName;
if (sequenceColumnName == "E_HIR_NODE")
{
sequencesDifferent.Add("SEQ_E_HIR_NODE_STATIC");
}
if (!sequencesDifferent.Contains(sequenceName) && source.sequences.ContainsKey(sequenceName))
{
// Add the sequence to the different list to make sure its value gets reset.
sequencesDifferent.Add(sequenceName);
}
log.unindent();
log.progressIncrement(0);
}
log.progressHide(1);
log.progressHide(0);
log.statusUpdate("Completed");
log.unindent();
log.log(Logger.LogLevel.progress, "");
log.log(Logger.LogLevel.progress, "DATA SYNCHRONIZED.");
log.log(Logger.LogLevel.change, "");
if (source.dialect == Ddl.Dialect.db2 && target.dialect == Ddl.Dialect.db2)
MQTRefresh(2);
}
if (config.FKCheckSync == "Y")
{
log.log(Logger.LogLevel.progress, "");
log.log(Logger.LogLevel.progress, "");
log.log(Logger.LogLevel.progress, "SYNCHRONIZING FOREIGN KEYS...");
log.log(Logger.LogLevel.progress, "");
log.indent();
//Add target foreign keys from source
foreach (Table table in source.tables.Values)
{
tempTableSchema = table.schema;
break;
}
foreignKeyRefresh = new ForeignKeyRefresh(_source, tempTableSchema);
foreignKeyRefresh.load();
string tempFKvalues = foreignKeyRefresh.sqlFKAdd;
OdbcCommand alter = new OdbcCommand();
alter.CommandType = CommandType.Text;
alter.Connection = target.connection;
string[] FKString = new string[1000];
string[] FKKeyValue = new string[10];
if (tempFKvalues != "")
{
try
{
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, "-- Foreign key constraints add commands --");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, tempFKvalues);
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
else
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, "-- Foreign key constraints add commands --");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
FKString = tempFKvalues.Split(';');
for (int count = 0; count < FKString.Length; count = count + 1)
{
try
{
if (FKString[count].Trim().Length > 0)
{
alter.CommandText = FKString[count].ToString();
alter.CommandTimeout = 0;
alter.ExecuteNonQuery();
FKKeyValue = FKString[count].Split(' ');
if (target.dialect == Ddl.Dialect.db2)
{
log.log(Logger.LogLevel.change, "Foreign Key " + FKKeyValue[5].ToString() + " created successfully.");
log.log(Logger.LogLevel.ddlChange, FKString[count].ToString() + ";");
}
else if (target.dialect == Ddl.Dialect.sqlServer)
{
log.log(Logger.LogLevel.change, "Foreign Key " + FKKeyValue[6].ToString() + " created successfully.");
log.log(Logger.LogLevel.ddlChange, FKString[count].ToString() + ";");
}
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, ex.Message);
}
}
log.log(Logger.LogLevel.progress, "");
log.log(Logger.LogLevel.progress, "FOREIGN KEYS SYNCHRONIZED...");
log.log(Logger.LogLevel.progress, "");
log.unindent();
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to add foreign key constraint " + ".");
log.log(Logger.LogLevel.error, ex.Message);
}
}
}
}
private int DbSchemaSync()
{
log.phaseUpdate("Schema Sync");
log.statusUpdate("");
log.log(Logger.LogLevel.progress, "");
log.log(Logger.LogLevel.progress, "SYNCHRONIZING SCHEMAS...");
log.indent();
if (target.dialect == Ddl.Dialect.sqlServer && config.type != 4)
{
if (BackupDatabase() > 0)
return 1;
}
if (config.FKCheckSync == "Y")
{
//Remove target Foreign Keys
//if (target.tables.Count > 0 && config.type !=4)
if (target.tables.Count > 0)
{
foreach (Table table in target.tables.Values)
{
tempTableSchema = table.schema;
break;
}
foreignKeyRefresh = new ForeignKeyRefresh(_target, tempTableSchema);
foreignKeyRefresh.load();
//foreignKeyRefresh.dropForeignKey();
string tempFKvalues = foreignKeyRefresh.sqlFKRemove;
OdbcCommand alter = new OdbcCommand();
alter.CommandType = CommandType.Text;
alter.Connection = target.connection;
if (tempFKvalues != "")
{
try
{
if (config.type == 4)
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, "-- Foreign key constraints drop commands --");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, tempFKvalues);
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
}
else
{
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, "-- Foreign key constraints drop commands --");
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.log(Logger.LogLevel.ddlChange, tempFKvalues);
log.log(Logger.LogLevel.ddl, string.Concat(System.Collections.ArrayList.Repeat('-', 75).ToArray()));
log.statusUpdate("Dropping foreign key constraints in target database");
alter.CommandText = tempFKvalues;
alter.CommandTimeout = 0;
alter.ExecuteNonQuery();
log.log(Logger.LogLevel.change, " ");
log.log(Logger.LogLevel.change, "Dropped foreign key constraints in target database. ");
log.log(Logger.LogLevel.change, " ");
log.statusUpdate("");
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to drop foreign key constraint " + ".");
log.log(Logger.LogLevel.error, ex.Message);
}
}
}
}
int changeCount = DbSchemaTableSync();
if (changeCount == 0)
{
DbSchemaIndexSync();
//DbSchemaViewSync();
if (source.dialect == Ddl.Dialect.db2 && target.dialect == Ddl.Dialect.db2)
{
DbSchemaMQTSync();
MQTRefresh(1);
DbSchemaMQTIndexSync();
DbSchemaViewSync();
DbSchemaStoredProceduresSync();
DbSchemaFunctionsSync();
DbSchemaModulesSync();
//MQTRefresh(2);
DbSchemaSequenceSync();
}
if (source.dialect != Ddl.Dialect.db2 && target.dialect != Ddl.Dialect.db2)
{
DbSchemaViewSync();
DbSchemaStoredProceduresSync();
DbSchemaFunctionsSync();
DbSchemaTriggersSync();
}
//log.unindent();
log.log(Logger.LogLevel.progress, "");
log.log(Logger.LogLevel.progress, "SCHEMAS SYNCHRONIZED.");
}
return changeCount;
}
