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; }