public void AddColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { DataTable dt = testData.Tables[t.FullName, GetTableSpace(dbName)]; Type type; //since this is just for unit testing we only need to support a subset of data types switch (dataType.ToString()) { case "int": type = typeof(Int32); break; case "bigint": type = typeof(Int64); break; case "datetime": type = typeof(DateTime); break; default: throw new NotImplementedException("Data type " + dataType + " not supported for testing"); } dt.Columns.Add(columnName, type); }
/// <summary> /// Build the apply command /// </summary> /// <param name="table">Table to apply changes to</param> /// <param name="schema">Vertica schema to apply changes to</param> /// <param name="CTDBName">CT database name, which is actually Vertica CT schema name</param> /// <param name="CTID">Change tracking ID</param> /// <returns>InsertDelete object representing the apply command</returns> private InsertDelete BuildApplyCommand(TableConf table, string schema, string CTDBName, long CTID) { // NOTE: Vertica does not like the first alias P in the following command: // DELETE FROM a.b P WHERE EXISTS (SELECT 1 FROM c.d CT WHERE P.id = CT.id) // instead, the first alias has to be removed, thus: // DELETE FROM a.b WHERE EXISTS (SELECT 1 FROM c.d CT WHERE a.b.id = CT.id) // and in the case of multi-column primary key: // DELETE FROM a.b WHERE EXISTS (SELECT 1 FROM c.d CT WHERE a.b.id1 = CT.id1 AND a.b.id2 = CT.id2) string verticaTableName = string.Format("{0}.{1}", schema, table.Name); string delete = string.Format( @"DELETE FROM {0} WHERE EXISTS (SELECT 1 FROM {1}.{2} CT WHERE {3});", verticaTableName, CTDBName, table.ToCTName(CTID), table.getNoAliasPkList(verticaTableName)); // since Vertica does not have the reserved words issue // we are using table.SimpleColumnList string insert = string.Format( @"INSERT INTO {0} ({1}) SELECT {1} FROM {2}.{3} CT WHERE NOT EXISTS (SELECT 1 FROM {0} P WHERE {4}) AND CT.sys_change_operation IN ( 'I', 'U' );", verticaTableName, table.SimpleColumnList, CTDBName, table.ToCTName(CTID), table.PkList); var deleteCmd = new VerticaCommand(delete); var insertCmd = new VerticaCommand(insert); return(new InsertDelete(insertCmd, deleteCmd)); }
/// <summary> /// Copies change tables from the master to the relay server /// </summary> /// <param name="tables">Array of table config objects</param> /// <param name="sourceServer">Source server identifer</param> /// <param name="sourceCTDB">Source CT database</param> /// <param name="destCTDB">Dest CT database</param> /// <param name="CTID">CT batch ID this is for</param> protected void PublishChangeTables(string sourceCTDB, string destCTDB, Int64 CTID, IDictionary <string, Int64> changesCaptured) { if (Config.Master != null && Config.Master == Config.RelayServer && sourceCTDB == destCTDB) { logger.Log("Skipping publish because master is equal to relay.", LogLevel.Debug); return; } var actions = new List <Action>(); foreach (TableConf t in Config.Tables) { if (changesCaptured[t.SchemaName + "." + t.Name] > 0) { //we need to define a local variable in this scope for it to be appropriately evaluated in the action TableConf localT = t; Action act = () => PublishChangeTable(localT, sourceCTDB, destCTDB, CTID); actions.Add(act); } } logger.Log("Parallel invocation of " + actions.Count + " changetable publishes", LogLevel.Trace); var options = new ParallelOptions(); options.MaxDegreeOfParallelism = Config.MaxThreads; Parallel.Invoke(options, actions.ToArray()); }
/// <summary> /// Loops through passed in array of table objects, creates changedata tables for each one on the CT DB /// </summary> /// <param name="tables">Array of table config objects to create CT tables for</param> /// <param name="sourceDB">Database the source data lives in</param> /// <param name="sourceCTDB">Database the changetables should go to</param> /// <param name="startVersion">Change tracking version to start with</param> /// <param name="stopVersion">Change tracking version to stop at</param> /// <param name="CTID">CT batch ID this is being run for</param> protected IDictionary <string, Int64> CreateChangeTables(string sourceDB, string sourceCTDB, ChangeTrackingBatch batch) { var changesCaptured = new ConcurrentDictionary <string, Int64>(); var actions = new List <Action>(); foreach (TableConf t in Config.Tables) { //local variables inside the loop required for the action to bind properly TableConf table = t; long rowsAffected; Action act = () => { logger.Log("Creating changetable for " + table.SchemaName + "." + table.Name, LogLevel.Debug); rowsAffected = CreateChangeTable(table, sourceDB, sourceCTDB, batch); changesCaptured.TryAdd(table.SchemaName + "." + table.Name, rowsAffected); logger.Log(rowsAffected + " changes captured for table " + table.SchemaName + "." + table.Name, LogLevel.Trace); }; actions.Add(act); } var options = new ParallelOptions(); options.MaxDegreeOfParallelism = Config.MaxThreads; logger.Log("Parallel invocation of " + actions.Count + " change captures", LogLevel.Trace); Parallel.Invoke(options, actions.ToArray()); return(changesCaptured); }
public void AddColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { // NOTE: We changed the signature of this method to use "DataType dataType" instead of "string dataType" // but currently, we are still using the generic DataType.MapDataType(), passing in dataType.ToString(). // The generic DataType.MapDataType() method is likely to have problem mapping source column data types to // destination (in this case, Netezza) column data types. We should implement a MapColumnTypeName() method // within this DataUtil class to handle the specific column data type mapping from different sources to Netezza. // Check out VerticaDataUtil for an example. columnName = MapReservedWord(columnName); if (!CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { //The "max" string doesn't exist on netezza, we can just replace it with the NetezzaStringLength after mapping it. //In practice this only impacts varchar and nvarchar, since other data types would be mapped to something else by the MapDataType //function (i.e. varbinary). This is the only place we do this special string-based handling because we wanted to keep Netezza specific logic //out of the DataType class. Outside of this case, the "max" is handled appropriately in the netezza data copy class. string destDataType = DataType.MapDataType(Config.RelayType, SqlFlavor.Netezza, dataType.ToString()).Replace("max", Config.NetezzaStringLength.ToString()); string sql = string.Format("ALTER TABLE {0} ADD {1} {2}; GROOM TABLE {0} VERSIONS;", t.Name, columnName, destDataType); var cmd = new OleDbCommand(sql); SqlNonQuery(dbName, cmd); RefreshViews(dbName, t.Name); } if (t.RecordHistoryTable && CheckTableExists(historyDB, t.HistoryName, t.SchemaName) && !CheckColumnExists(historyDB, t.SchemaName, t.HistoryName, columnName)) { string sql = string.Format("ALTER TABLE {0} ADD {1} {2}; GROOM TABLE {0} VERSIONS;", t.HistoryName, columnName, dataType.ToString()); var cmd = new OleDbCommand(sql); logger.Log("Altering history table column with command: " + cmd.CommandText, LogLevel.Debug); SqlNonQuery(historyDB, cmd); RefreshViews(historyDB, t.HistoryName); } }
public void TestNoOverrideNonZeroStartVersion() { var table = new TableConf(); table.Name = "tableName"; table.SchemaName = "schmea"; string db = "db"; string ctdb = "ctdb"; var batch = new ChangeTrackingBatch(1, 1, 10, 0); var sourceUtils = new Mock <IDataUtils>(); this.sourceDataUtils = sourceUtils.Object; sourceUtils.Setup((ut) => ut.GetMinValidVersion(db, table.Name, table.SchemaName)) .Returns(5); sourceUtils.Setup((ut) => ut.CheckTableExists(db, table.Name, It.IsAny <string>())) .Returns(true); sourceUtils.Setup((ut) => ut.IsChangeTrackingEnabled(db, table.Name, table.SchemaName)) .Returns(true); sourceUtils.Setup((ut) => ut.IsBeingInitialized(ctdb, It.IsAny <TableConf>())) .Returns(false); sourceUtils.Setup((ut) => ut.GetInitializeStartVersion(ctdb, It.IsAny <TableConf>())) .Returns(new long?()); CreateChangeTable(table, db, ctdb, batch); //sourceUtils.Verify( // (ut) => ut.SelectIntoCTTable(It.IsAny<string>(), It.IsAny<TableConf>(), It.IsAny<string>( sourceUtils.Verify( (ut) => ut.SelectIntoCTTable(ctdb, It.IsAny <TableConf>(), db, It.IsAny <ChangeTrackingBatch>(), It.IsAny <int>(), batch.SyncStartVersion), Times.Never(), "Should not select into CT table when the start version is less than minValidVersion"); }
public ApplySchemaChangeTestData() { tables = new TableConf[2]; //first table has no column list tables[0] = new TableConf(); tables[0].SchemaName = "dbo"; tables[0].Name = "test1"; //second one has column list tables[1] = new TableConf(); tables[1].SchemaName = "dbo"; tables[1].Name = "test2"; tables[1].ColumnList = new string[] { "column1", "column2" }; sourceDataUtils = new TestDataUtils(TServer.RELAY); destDataUtils = new TestDataUtils(TServer.SLAVE); testData = new DataSet(); sourceDataUtils.testData = new DataSet(); destDataUtils.testData = new DataSet(); //this method, conveniently, sets up the datatable schema we need sourceDataUtils.CreateSchemaChangeTable("CT_testdb", 1); Config.Tables = tables.ToList(); Config.RelayDB = "CT_testdb"; var logger = new Logger(null, null, null, ""); slave = new Slave(sourceDataUtils, destDataUtils, logger); }
public void AddColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { // NOTE: We changed the signature of this method to use "DataType dataType" instead of "string dataType" // but currently, we are still using the generic DataType.MapDataType(), passing in dataType.ToString(). // The generic DataType.MapDataType() method is likely to have problem mapping source column data types to // destination (in this case, Netezza) column data types. We should implement a MapColumnTypeName() method // within this DataUtil class to handle the specific column data type mapping from different sources to Netezza. // Check out VerticaDataUtil for an example. columnName = MapReservedWord(columnName); if (!CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { //The "max" string doesn't exist on netezza, we can just replace it with the NetezzaStringLength after mapping it. //In practice this only impacts varchar and nvarchar, since other data types would be mapped to something else by the MapDataType //function (i.e. varbinary). This is the only place we do this special string-based handling because we wanted to keep Netezza specific logic //out of the DataType class. Outside of this case, the "max" is handled appropriately in the netezza data copy class. string destDataType = DataType.MapDataType(Config.RelayType, SqlFlavor.Netezza, dataType.ToString()).Replace("max", Config.NetezzaStringLength.ToString()); string sql = string.Format("ALTER TABLE {0} ADD {1} {2}; GROOM TABLE {0} VERSIONS;", t.Name, columnName, destDataType); var cmd = new OleDbCommand(sql); SqlNonQuery(dbName, cmd); RefreshViews(dbName, t.Name); } if (t.RecordHistoryTable && CheckTableExists(historyDB, t.HistoryName, t.SchemaName) && !CheckColumnExists(historyDB, t.SchemaName, t.HistoryName, columnName)) { string sql = string.Format("ALTER TABLE {0} ADD {1} {2}; GROOM TABLE {0} VERSIONS;", t.HistoryName, columnName, dataType.ToString()); var cmd = new OleDbCommand(sql); logger.Log("Altering history table column with command: " + cmd.CommandText, LogLevel.Debug); SqlNonQuery(historyDB, cmd); RefreshViews(historyDB, t.HistoryName); } }
public void TestValidTablesAndArchives() { int CTID = 1; string schema = "sch"; string slaveName = "slave"; var tableConf = new List <TableConf>(); var tables = new HashSet <ChangeTable>(); var table = new TableConf(); table.Name = "tblName"; var aTable = new TableConf(); aTable.Name = "tblNameArchive"; tableConf.Add(aTable); tableConf.Add(table); Config.Tables = tableConf; tables.Add(new ChangeTable(aTable.Name, CTID, schema, slaveName)); tables.Add(new ChangeTable(table.Name, CTID, schema, slaveName)); var s = ValidTablesAndArchives(tables, CTID); Assert.True(s.ContainsKey(table)); Assert.True(s[table] != null); Assert.True(s[table] == aTable); //double checking that order of table objects doesn't matter tableConf.Clear(); tableConf.Add(table); tableConf.Add(aTable); s = ValidTablesAndArchives(tables, CTID); Assert.True(s.ContainsKey(table)); Assert.True(s[table] != null); Assert.True(s[table] == aTable); }
private void ApplySchemaChange(string destDB, TableConf table, SchemaChange schemaChange) { switch (schemaChange.EventType) { case SchemaChangeType.Rename: logger.Log("Renaming column " + schemaChange.ColumnName + " to " + schemaChange.NewColumnName, LogLevel.Info); destDataUtils.RenameColumn(table, destDB, schemaChange.ColumnName, schemaChange.NewColumnName, Config.SlaveCTDB); break; case SchemaChangeType.Modify: logger.Log("Changing data type on column " + schemaChange.ColumnName, LogLevel.Info); destDataUtils.ModifyColumn(table, destDB, schemaChange.ColumnName, schemaChange.DataType, Config.SlaveCTDB); break; case SchemaChangeType.Add: logger.Log("Adding column " + schemaChange.ColumnName, LogLevel.Info); destDataUtils.AddColumn(table, destDB, schemaChange.ColumnName, schemaChange.DataType, Config.SlaveCTDB); break; case SchemaChangeType.Drop: logger.Log("Dropping column " + schemaChange.ColumnName, LogLevel.Info); destDataUtils.DropColumn(table, destDB, schemaChange.ColumnName, Config.SlaveCTDB); break; } }
public void ModifyColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { //can't change the datatype of a column in a datatable but since this is just for unit testing, we can just drop and recreate it //instead since there is no data to worry about losing DropColumn(t, dbName, columnName, historyDB); AddColumn(t, dbName, columnName, dataType, historyDB); }
public RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated) { var cmds = new List<InsertDelete>(); cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID)); if (archiveTable != null) { cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID)); } var connStr = buildConnString(dbName); var rowCounts = new RowCounts(0, 0); using (var conn = new OleDbConnection(connStr)) { conn.Open(); var trans = conn.BeginTransaction(); foreach (var id in cmds) { id.delete.Transaction = trans; id.delete.Connection = conn; id.delete.CommandTimeout = Config.QueryTimeout; logger.Log(id.delete.CommandText, LogLevel.Trace); int deleted = id.delete.ExecuteNonQuery(); logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info); id.insert.Transaction = trans; id.insert.Connection = conn; id.insert.CommandTimeout = Config.QueryTimeout; logger.Log(id.insert.CommandText, LogLevel.Trace); int inserted = id.insert.ExecuteNonQuery(); logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info); rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted); } trans.Commit(); } return rowCounts; }
public RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated) { var cmds = new List <InsertDelete>(); cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID)); if (archiveTable != null) { cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID)); } var connStr = buildConnString(dbName); var rowCounts = new RowCounts(0, 0); using (var conn = new OleDbConnection(connStr)) { conn.Open(); var trans = conn.BeginTransaction(); foreach (var id in cmds) { id.delete.Transaction = trans; id.delete.Connection = conn; id.delete.CommandTimeout = Config.QueryTimeout; logger.Log(id.delete.CommandText, LogLevel.Trace); int deleted = id.delete.ExecuteNonQuery(); logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info); id.insert.Transaction = trans; id.insert.Connection = conn; id.insert.CommandTimeout = Config.QueryTimeout; logger.Log(id.insert.CommandText, LogLevel.Trace); int inserted = id.insert.ExecuteNonQuery(); logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info); rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted); } trans.Commit(); } return(rowCounts); }
public void TestOverrideZeroStartVersion() { var table = new TableConf(); table.Name = "tableName"; table.SchemaName = "schema"; string db = "db"; string ctdb = "ctdb"; long minValidVersion = 5; var batch = new ChangeTrackingBatch(1, 0, 10, 0); var sourceUtils = new Mock <IDataUtils>(); this.sourceDataUtils = sourceUtils.Object; sourceUtils.Setup((ut) => ut.GetMinValidVersion(db, table.Name, table.SchemaName)) .Returns(minValidVersion).Verifiable(); sourceUtils.Setup((ut) => ut.CheckTableExists(db, table.Name, It.IsAny <string>())) .Returns(true); sourceUtils.Setup((ut) => ut.IsChangeTrackingEnabled(db, table.Name, table.SchemaName)) .Returns(true); CreateChangeTable(table, db, ctdb, batch); sourceUtils.Verify( (ut) => ut.SelectIntoCTTable(ctdb, It.IsAny <TableConf>(), db, It.IsAny <ChangeTrackingBatch>(), It.IsAny <int>(), minValidVersion) ); }
/// <summary> /// Set several field lists on a TableConf object using its config and an smo table object. /// </summary> /// <param name="t">A table configuration object</param> /// <param name="fields">Dictionary of field names with a bool for whether they are part of the primary key</param> public void SetFieldList(TableConf t, IEnumerable<TColumn> fields) { Stopwatch st = new Stopwatch(); st.Start(); t.columns.Clear(); t.columns = fields.Where(c => t.ColumnList == null || t.ColumnList.Contains(c.name, StringComparer.OrdinalIgnoreCase)).ToList(); st.Stop(); logger.Log(new { message = "SetFieldList Elapsed time : " + st.ElapsedMilliseconds, Table = t.FullName }, LogLevel.Trace); }
protected void HandleException(Exception e, TableConf table, string message = "") { message = "Table: " + table.FullName + "; StopOnError: " + table.StopOnError + (message.Length > 0 ? "\r\n" + message : ""); logger.Log(e, message); if (table.StopOnError) { throw e; } }
/// <summary> /// Set several field lists on a TableConf object using its config and an smo table object. /// </summary> /// <param name="t">A table configuration object</param> /// <param name="fields">Dictionary of field names with a bool for whether they are part of the primary key</param> public void SetFieldList(TableConf t, IEnumerable <TColumn> fields) { Stopwatch st = new Stopwatch(); st.Start(); t.columns.Clear(); t.columns = fields.Where(c => t.ColumnList == null || t.ColumnList.Contains(c.name, StringComparer.OrdinalIgnoreCase)).ToList(); st.Stop(); logger.Log(new { message = "SetFieldList Elapsed time : " + st.ElapsedMilliseconds, Table = t.FullName }, LogLevel.Trace); }
public void PublishTableInfo(string dbName, TableConf t, long CTID, long expectedRows) { DataTable table = testData.Tables["dbo.tblCTTableInfo_" + Convert.ToString(CTID), GetTableSpace(dbName)]; DataRow row = table.NewRow(); row["CtiTableName"] = t.Name; row["CtiSchemaName"] = t.SchemaName; row["CtiPKList"] = string.Join(",", t.columns.Where(c => c.isPk)); row["CtiExpectedRows"] = expectedRows; table.Rows.Add(row); }
private void SetFieldList(TableConf table, string database, ChangeTrackingBatch batch) { var cols = sourceDataUtils.GetFieldList(database, table.ToCTName(batch.CTID), table.SchemaName); var pks = sourceDataUtils.GetPrimaryKeysFromInfoTable(table, batch.CTID, database); foreach (var pk in pks) { cols.First((c => c.name == pk)).isPk = true; } SetFieldList(table, cols); }
/// <summary> /// Copies change tables from the relay to the slave server /// </summary> /// <param name="tables">Array of table config objects</param> /// <param name="sourceCTDB">Source CT database</param> /// <param name="destCTDB">Dest CT database</param> /// <param name="CTID">CT batch ID this is for</param> /// <param name="existingCTTables">List of existing CT tables</param> /// <param name="isConsolidated">Whether source CT table is consolidated</param> private void CopyChangeTables(IEnumerable <TableConf> tables, string sourceCTDB, string destCTDB, Int64 CTID, List <ChangeTable> existingCTTables, bool isConsolidated = false) { if (Config.Slave == Config.RelayServer && sourceCTDB == destCTDB) { logger.Log("Skipping download because slave is equal to relay.", LogLevel.Debug); return; } var actions = new List <Action>(); foreach (TableConf t in tables) { if (Config.SlaveType == SqlFlavor.Vertica) { // NOTE: bug fix for ticket # 1699344 // Currently we are testing this fix for Vertica only ChangeTable changeTable = existingCTTables.Where(ctbl => String.Compare(ctbl.name, t.Name, StringComparison.OrdinalIgnoreCase) == 0).OrderBy(ctbl => ctbl.CTID).LastOrDefault(); if (changeTable == null) { continue; } } IDataCopy dataCopy = DataCopyFactory.GetInstance(Config.RelayType, Config.SlaveType, sourceDataUtils, destDataUtils, logger); var ct = new ChangeTable(t.Name, CTID, t.SchemaName, Config.Slave); string sourceCTTable = isConsolidated ? ct.consolidatedName : ct.ctName; string destCTTable = ct.ctName; TableConf tLocal = t; Action act = () => { try { //hard coding timeout at 1 hour for bulk copy logger.Log(new { message = "Copying table to slave", Table = tLocal.SchemaName + "." + sourceCTTable }, LogLevel.Trace); var sw = Stopwatch.StartNew(); dataCopy.CopyTable(sourceCTDB, sourceCTTable, tLocal.SchemaName, destCTDB, Config.DataCopyTimeout, destCTTable, tLocal.Name); logger.Log(new { message = "CopyTable: " + sw.Elapsed, Table = tLocal.SchemaName + "." + sourceCTTable }, LogLevel.Trace); } catch (DoesNotExistException) { //this is a totally normal and expected case since we only publish changetables when data actually changed logger.Log("No changes to pull for table " + tLocal.SchemaName + "." + sourceCTTable + " because it does not exist ", LogLevel.Debug); } catch (Exception e) { HandleException(e, tLocal); } }; actions.Add(act); } logger.Log("Parallel invocation of " + actions.Count + " changetable downloads", LogLevel.Trace); var options = new ParallelOptions(); options.MaxDegreeOfParallelism = Config.MaxThreads; Parallel.Invoke(options, actions.ToArray()); return; }
private InsertDelete BuildApplyCommand(TableConf table, string dbName, string CTDBName, long CTID) { string delete = string.Format(@"DELETE FROM {0} P WHERE EXISTS (SELECT 1 FROM {1}..{2} CT WHERE {3});", table.Name, CTDBName, table.ToCTName(CTID), table.PkList); string insert = string.Format(@"INSERT INTO {0} ({1}) SELECT {1} FROM {2}..{3} CT WHERE NOT EXISTS (SELECT 1 FROM {0} P WHERE {4}) AND CT.sys_change_operation IN ( 'I', 'U' );", table.Name, table.NetezzaColumnList, CTDBName, table.ToCTName(CTID), table.PkList); var deleteCmd = new OleDbCommand(delete); var insertCmd = new OleDbCommand(insert); return(new InsertDelete(insertCmd, deleteCmd)); }
public DDLEventTestData() { tables = new TableConf[2]; //first table has no column list tables[0] = new TableConf(); tables[0].Name = "test1"; //second one has column list tables[1] = new TableConf(); tables[1].Name = "test2"; tables[1].ColumnList = new string[] { "column1", "column2" }; dataUtils = new TestDataUtils(TServer.MASTER); dataUtils.ReloadData("test1"); }
/// <summary> /// Creates changetable for an individual table /// </summary> /// <param name="table">Config table object to create changes for</param> /// <param name="sourceDB">Database the source data lives in</param> /// <param name="sourceCTDB">Database the changetables should go to</param> /// <param name="batch">Batch to work on</param> protected long CreateChangeTable(TableConf table, string sourceDB, string sourceCTDB, ChangeTrackingBatch batch) { string ctTableName = table.ToCTName(batch.CTID); string reason; long tableStartVersion = batch.SyncStartVersion; long minValidVersion = sourceDataUtils.GetMinValidVersion(sourceDB, table.Name, table.SchemaName); if (batch.SyncStartVersion == 0) { tableStartVersion = minValidVersion; } if (sourceDataUtils.IsBeingInitialized(sourceCTDB, table)) { return(0); } long?initializeVersion = sourceDataUtils.GetInitializeStartVersion(sourceCTDB, table); if (initializeVersion.HasValue) { tableStartVersion = initializeVersion.Value; } if (!ValidateSourceTable(sourceDB, table.Name, table.SchemaName, tableStartVersion, minValidVersion, out reason)) { string message = "Change table creation impossible because : " + reason; if (table.StopOnError) { throw new Exception(message); } else { logger.Log(message, LogLevel.Error); return(0); } } logger.Log("Dropping table " + ctTableName + " if it exists", LogLevel.Trace); sourceDataUtils.DropTableIfExists(sourceCTDB, ctTableName, table.SchemaName); logger.Log("Calling SelectIntoCTTable to create CT table", LogLevel.Trace); Int64 rowsAffected = sourceDataUtils.SelectIntoCTTable(sourceCTDB, table, sourceDB, batch, Config.QueryTimeout, tableStartVersion); logger.Log("Rows affected for table " + table.SchemaName + "." + table.Name + ": " + Convert.ToString(rowsAffected), LogLevel.Debug); return(rowsAffected); }
public void AddColumn(TableConf t, string dbName, string schema, string table, string columnName, string dataType) { columnName = MapReservedWord(columnName); if (!CheckColumnExists(dbName, schema, table, columnName)) { dataType = DataType.MapDataType(Config.RelayType, SqlFlavor.Netezza, dataType); string sql = string.Format("ALTER TABLE {0} ADD {1} {2}; GROOM TABLE {0} VERSIONS;", table, columnName, dataType); var cmd = new OleDbCommand(sql); SqlNonQuery(dbName, cmd); RefreshViews(dbName, table); } if (t.RecordHistoryTable && CheckTableExists(dbName, schema, table + "_History") && !CheckColumnExists(dbName, schema, table + "_History", columnName)) { string sql = string.Format("ALTER TABLE {0} ADD {1} {2}; GROOM TABLE {0} VERSIONS;", table + "_History", columnName, dataType); var cmd = new OleDbCommand(sql); logger.Log("Altering history table column with command: " + cmd.CommandText, LogLevel.Debug); SqlNonQuery(dbName, cmd); RefreshViews(dbName, table + "_History"); } }
protected void PublishChangeTable(TableConf table, string sourceCTDB, string destCTDB, Int64 CTID) { IDataCopy dataCopy = DataCopyFactory.GetInstance((SqlFlavor)Config.MasterType, (SqlFlavor)Config.RelayType, sourceDataUtils, destDataUtils, logger); logger.Log("Publishing changes for table " + table.SchemaName + "." + table.Name, LogLevel.Trace); try { dataCopy.CopyTable(sourceCTDB, table.ToCTName(CTID), table.SchemaName, destCTDB, Config.DataCopyTimeout, originalTableName: table.Name); logger.Log("Publishing changes succeeded for " + table.SchemaName + "." + table.Name, LogLevel.Trace); } catch (Exception e) { if (table.StopOnError) { throw; } else { logger.Log("Copying change data for table " + table.SchemaName + "." + table.Name + " failed with error: " + e.Message, LogLevel.Error); } } }
public void RenameColumn(TableConf t, string dbName, string columnName, string newColumnName, string historyDB) { // rename the column if it exists if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string sql = string.Format( @"ALTER TABLE {0}.{1} RENAME COLUMN {2} TO {3};", dbName, t.Name, columnName, newColumnName); var cmd = new VerticaCommand(sql); SqlNonQuery(cmd); if (ShallRefreshViews(t, dbName, newColumnName, action: "RenameColumn", columnShallExist: true)) { RefreshViews(dbName, t.Name); } } }
public void DropColumn(TableConf t, string dbName, string columnName, string historyDB) { // NOTE: Reserved word should not be a problem for Vertica // in case we found it is some point in the future, enable mapping // columnName = MapReservedWord(columnName); if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string sql = string.Format( @"ALTER TABLE {0}.{1} DROP COLUMN {2} RESTRICT;", dbName, t.Name, columnName); var cmd = new VerticaCommand(sql); SqlNonQuery(cmd); if (ShallRefreshViews(t, dbName, columnName, action: "DropColumn", columnShallExist: false)) { RefreshViews(dbName, t.Name); } } }
public void DropColumn(TableConf t, string dbName, string columnName, string historyDB) { columnName = MapReservedWord(columnName); if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1} RESTRICT; GROOM TABLE {0} VERSIONS;", t.Name, columnName); var cmd = new OleDbCommand(sql); SqlNonQuery(dbName, cmd); RefreshViews(dbName, t.Name); } if (t.RecordHistoryTable && CheckTableExists(dbName, t.HistoryName, t.SchemaName) && CheckColumnExists(dbName, t.SchemaName, t.HistoryName, columnName)) { string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1} RESTRICT; GROOM TABLE {0} VERSIONS;", t.HistoryName, columnName); var cmd = new OleDbCommand(sql); logger.Log("Altering history table column with command: " + cmd.CommandText, LogLevel.Debug); SqlNonQuery(historyDB, cmd); RefreshViews(historyDB, t.HistoryName); } }
public void ApplySchemaChanges(string sourceDB, string destDB, Int64 CTID) { //get list of schema changes from tblCTSChemaChange_ctid on the relay server/db DataTable result = sourceDataUtils.GetSchemaChanges(sourceDB, CTID); if (result == null) { return; } foreach (DataRow row in result.Rows) { var schemaChange = new SchemaChange(row); //String.Compare method returns 0 if the strings are equal TableConf table = Config.Tables.SingleOrDefault(item => String.Compare(item.Name, schemaChange.TableName, ignoreCase: true) == 0); if (table == null) { logger.Log(new { message = "Ignoring schema change for untracked table", Table = schemaChange.TableName }, LogLevel.Debug); continue; } logger.Log("Processing schema change (CscID: " + row.Field <int>("CscID") + ") of type " + schemaChange.EventType + " for table " + table.Name, LogLevel.Info); if (table.ColumnList == null || table.ColumnList.Contains(schemaChange.ColumnName, StringComparer.OrdinalIgnoreCase)) { logger.Log("Schema change applies to a valid column, so we will apply it", LogLevel.Info); try { ApplySchemaChange(destDB, table, schemaChange); } catch (Exception e) { var wrappedExc = new Exception(schemaChange.ToString(), e); HandleException(wrappedExc, table); } } else { logger.Log("Skipped schema change because the column it impacts is not in our list", LogLevel.Info); } } }
public void AddColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { // NOTE: Reserved word should not be a problem for Vertica // in case we found it is some point in the future, enable mapping // columnName = MapReservedWord(columnName); if (!CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string destDataType = MapColumnTypeName(Config.RelayType, dataType, t.getColumnModifier(columnName)); string sql = string.Format( @"ALTER TABLE {0}.{1} ADD {2} {3};", dbName, t.Name, columnName, destDataType); var cmd = new VerticaCommand(sql); SqlNonQuery(cmd); if (ShallRefreshViews(t, dbName, columnName, action: "AddColumn", columnShallExist: true)) { RefreshViews(dbName, t.Name); } } }
public void TestSetFieldList() { TableConf t = new TableConf(); t.ColumnList = new string[] { "col1", "col2", "col3", "col4" }; var fields = new List<TColumn>{ {new TColumn("col1", true, null, true)}, {new TColumn("col2", true, null, true)}, {new TColumn("col3", true, null, true)}, {new TColumn("col4", true, null, true)} }; var cm = new ColumnModifier(); cm.type = "ShortenField"; cm.length = 100; cm.columnName = "col1"; t.ColumnModifiers = new ColumnModifier[] { cm }; SetFieldList(t, fields); Assert.Equal("LEFT(CAST(P.[col1] AS NVARCHAR(MAX)),100) as 'col1',P.col2,P.col3,CT.col4", t.ModifiedMasterColumnList); Assert.Equal("col1,col2,col3,col4", t.SlaveColumnList); Assert.Equal("P.col1 = CT.col1 AND P.col4 = CT.col4", t.PkList); Assert.Equal("P.col1 IS NOT NULL AND P.col4 IS NOT NULL", t.NotNullPKList); Assert.Equal("P.col2=CT.col2,P.col3=CT.col3", t.MergeUpdateList); }
public void ModifyColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { // modify the column if it exists if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string destDataType = MapColumnTypeName(Config.RelayType, dataType, t.getColumnModifier(columnName)); if (!ColumnDatatypeMatches(dbName, t.SchemaName, t.Name, columnName, destDataType)) { // do not modify if the destination column already has the right data type string sql = string.Format( @"ALTER TABLE {0}.{1} ALTER COLUMN {2} SET DATA TYPE {3};", dbName, t.Name, columnName, destDataType); var cmd = new VerticaCommand(sql); SqlNonQuery(cmd); if (ShallRefreshViews(t, dbName, columnName, action: "ModifyColumn", columnShallExist: true, dataType: destDataType)) { RefreshViews(dbName, t.Name); } } } }
public MasterTestFixture() { var tables = new TableConf[2]; tables[0] = new TableConf(); tables[0].Name = "test1"; tables[0].SchemaName = "dbo"; tables[1] = new TableConf(); tables[1].Name = "test2"; tables[1].SchemaName = "dbo"; sourceDataUtils = new TestDataUtils(TServer.MASTER); sourceDataUtils.testData = new DataSet(); destDataUtils = new TestDataUtils(TServer.RELAY); destDataUtils.testData = new DataSet(); Config.MasterDB = "testdb"; Config.MasterCTDB = "CT_testdb"; Config.RelayDB = "CT_testdb"; Config.Tables = tables.ToList(); Config.MasterType = SqlFlavor.MSSQL; Config.RelayType = SqlFlavor.MSSQL; }
public void TestSetFieldList() { TableConf t = new TableConf(); t.ColumnList = new string[] { "col1", "col2", "col3", "col4" }; var fields = new List <TColumn> { { new TColumn("col1", true, null, true) }, { new TColumn("col2", true, null, true) }, { new TColumn("col3", true, null, true) }, { new TColumn("col4", true, null, true) } }; var cm = new ColumnModifier(); cm.type = "ShortenField"; cm.length = 100; cm.columnName = "col1"; t.ColumnModifiers = new ColumnModifier[] { cm }; SetFieldList(t, fields); Assert.Equal("LEFT(CAST(P.[col1] AS NVARCHAR(MAX)),100) as 'col1',P.col2,P.col3,CT.col4", t.ModifiedMasterColumnList); Assert.Equal("col1,col2,col3,col4", t.SlaveColumnList); Assert.Equal("P.col1 = CT.col1 AND P.col4 = CT.col4", t.PkList); Assert.Equal("P.col1 IS NOT NULL AND P.col4 IS NOT NULL", t.NotNullPKList); Assert.Equal("P.col2=CT.col2,P.col3=CT.col3", t.MergeUpdateList); }
public int SelectIntoCTTable(string sourceCTDB, TableConf table, string sourceDB, ChangeTrackingBatch ctb, int timeout, long? overrideStartVersion) { //no good way to fake this with DataTables so just return and make sure we are also unit testing the //methods that generate these sfield lists return testData.Tables[table.SchemaName + "." + table.ToCTName(ctb.CTID), GetTableSpace(sourceCTDB)].Rows.Count; }
protected void HandleException(Exception e, TableConf table, string message = "") { message = "Table: " + table.FullName + "; StopOnError: " + table.StopOnError + (message.Length > 0 ? "\r\n" + message : ""); logger.Log(e, message); if (table.StopOnError) { throw e; } }
public void RemoveDuplicatePrimaryKeyChangeRows(TableConf table, string consolidatedTableName, string dbName) { throw new NotImplementedException("Netezza is only supported as a slave!"); }
public void DropColumn(TableConf t, string dbName, string columnName, string historyDB) { DataTable dt = testData.Tables[t.FullName, GetTableSpace(dbName)]; dt.Columns.Remove(columnName); }
public bool IsBeingInitialized(string sourceCTDB, TableConf table) { return false; }
public void PublishTableInfo(string dbName, TableConf t, long CTID, long expectedRows) { DataTable table = testData.Tables["dbo.tblCTTableInfo_" + Convert.ToString(CTID), GetTableSpace(dbName)]; DataRow row = table.NewRow(); row["CtiTableName"] = t.Name; row["CtiSchemaName"] = t.SchemaName; row["CtiPKList"] = string.Join(",", t.columns.Where(c => c.isPk)); row["CtiExpectedRows"] = expectedRows; table.Rows.Add(row); }
private void MergeTable(ChangeTrackingBatch batch, Dictionary<string, List<TColumn>> dbColumns, TableConf table, string firstDB) { logger.Log(new { message = "Merging table", Table = table.Name }, LogLevel.Debug); var dc = DataCopyFactory.GetInstance(Config.RelayType, Config.RelayType, sourceDataUtils, sourceDataUtils, logger); dc.CopyTableDefinition(firstDB, table.ToCTName(batch.CTID), table.SchemaName, Config.RelayDB, table.ToCTName(batch.CTID)); foreach (var dbNameFields in dbColumns) { var dbName = dbNameFields.Key; var columns = dbNameFields.Value; if (columns.Count == 0) { //no changes in this DB for this table continue; } sourceDataUtils.MergeCTTable(table, Config.RelayDB, dbName, batch.CTID); } }
public bool IsBeingInitialized(string sourceCTDB, TableConf table) { throw new NotImplementedException(); }
public void MergeCTTable(TableConf table, string destDB, string sourceDB, long CTID) { throw new NotImplementedException(); }
public void ModifyColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { logger.Log("Unable to apply modify of column " + columnName + " to type " + dataType.ToString() + " on " + dbName + "." + t.FullName + " for slave " + Config.Slave, LogLevel.Error); }
protected void PublishChangeTable(TableConf table, string sourceCTDB, string destCTDB, Int64 CTID) { IDataCopy dataCopy = DataCopyFactory.GetInstance((SqlFlavor)Config.MasterType, (SqlFlavor)Config.RelayType, sourceDataUtils, destDataUtils, logger); logger.Log("Publishing changes for table " + table.SchemaName + "." + table.Name, LogLevel.Trace); try { dataCopy.CopyTable(sourceCTDB, table.ToCTName(CTID), table.SchemaName, destCTDB, Config.DataCopyTimeout, originalTableName: table.Name); logger.Log("Publishing changes succeeded for " + table.SchemaName + "." + table.Name, LogLevel.Trace); } catch (Exception e) { if (table.StopOnError) { throw; } else { logger.Log("Copying change data for table " + table.SchemaName + "." + table.Name + " failed with error: " + e.Message, LogLevel.Error); } } }
/// <summary> /// Creates changetable for an individual table /// </summary> /// <param name="table">Config table object to create changes for</param> /// <param name="sourceDB">Database the source data lives in</param> /// <param name="sourceCTDB">Database the changetables should go to</param> /// <param name="batch">Batch to work on</param> protected long CreateChangeTable(TableConf table, string sourceDB, string sourceCTDB, ChangeTrackingBatch batch) { string ctTableName = table.ToCTName(batch.CTID); string reason; long tableStartVersion = batch.SyncStartVersion; long minValidVersion = sourceDataUtils.GetMinValidVersion(sourceDB, table.Name, table.SchemaName); if (batch.SyncStartVersion == 0) { tableStartVersion = minValidVersion; } if (sourceDataUtils.IsBeingInitialized(sourceCTDB, table)) { return 0; } long? initializeVersion = sourceDataUtils.GetInitializeStartVersion(sourceCTDB, table); if (initializeVersion.HasValue) { tableStartVersion = initializeVersion.Value; } if (!ValidateSourceTable(sourceDB, table.Name, table.SchemaName, tableStartVersion, minValidVersion, out reason)) { string message = "Change table creation impossible because : " + reason; if (table.StopOnError) { throw new Exception(message); } else { logger.Log(message, LogLevel.Error); return 0; } } logger.Log("Dropping table " + ctTableName + " if it exists", LogLevel.Trace); sourceDataUtils.DropTableIfExists(sourceCTDB, ctTableName, table.SchemaName); logger.Log("Calling SelectIntoCTTable to create CT table", LogLevel.Trace); Int64 rowsAffected = sourceDataUtils.SelectIntoCTTable(sourceCTDB, table, sourceDB, batch, Config.QueryTimeout, tableStartVersion); logger.Log("Rows affected for table " + table.SchemaName + "." + table.Name + ": " + Convert.ToString(rowsAffected), LogLevel.Debug); return rowsAffected; }
public void PublishTableInfo(string dbName, TableConf t, long CTID, long expectedRows) { throw new NotImplementedException("Netezza is only supported as a slave!"); }
public void RenameColumn(TableConf t, string dbName, string columnName, string newColumnName, string historyDB) { DataTable dt = testData.Tables[t.FullName, GetTableSpace(dbName)]; dt.Columns[columnName].ColumnName = newColumnName; }
public void RemoveDuplicatePrimaryKeyChangeRows(TableConf table, string consolidatedTableName, string dbName) { throw new NotImplementedException(); }
public IEnumerable<string> GetPrimaryKeysFromInfoTable(TableConf table, long CTID, string database) { throw new NotImplementedException(); }
private void SetFieldList(TableConf table, string database, ChangeTrackingBatch batch) { var cols = sourceDataUtils.GetFieldList(database, table.ToCTName(batch.CTID), table.SchemaName); var pks = sourceDataUtils.GetPrimaryKeysFromInfoTable(table, batch.CTID, database); foreach (var pk in pks) { cols.First((c => c.name == pk)).isPk = true; } SetFieldList(table, cols); }
public long? GetInitializeStartVersion(string sourceCTDB, TableConf table) { throw new NotImplementedException(); }
public void ModifyColumn(TableConf t, string dbName, string columnName, DataType dataType, string historyDB) { //can't change the datatype of a column in a datatable but since this is just for unit testing, we can just drop and recreate it //instead since there is no data to worry about losing DropColumn(t, dbName, columnName, historyDB); AddColumn(t, dbName, columnName, dataType, historyDB); }
public void DropColumn(TableConf t, string dbName, string columnName, string historyDB) { columnName = MapReservedWord(columnName); if (CheckColumnExists(dbName, t.SchemaName, t.Name, columnName)) { string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1} RESTRICT; GROOM TABLE {0} VERSIONS;", t.Name, columnName); var cmd = new OleDbCommand(sql); SqlNonQuery(dbName, cmd); RefreshViews(dbName, t.Name); } if (t.RecordHistoryTable && CheckTableExists(dbName, t.HistoryName, t.SchemaName) && CheckColumnExists(dbName, t.SchemaName, t.HistoryName, columnName)) { string sql = string.Format("ALTER TABLE {0} DROP COLUMN {1} RESTRICT; GROOM TABLE {0} VERSIONS;", t.HistoryName, columnName); var cmd = new OleDbCommand(sql); logger.Log("Altering history table column with command: " + cmd.CommandText, LogLevel.Debug); SqlNonQuery(historyDB, cmd); RefreshViews(historyDB, t.HistoryName); } }
public void RenameColumn(TableConf t, string dbName, string schema, string table, string columnName, string newColumnName) { logger.Log("Please check pending schema changes to be applied on Netezza for " + dbName + "." + schema + "." + table + " on " + Config.Slave, LogLevel.Error); }
private void ApplySchemaChange(string destDB, TableConf table, SchemaChange schemaChange) { switch (schemaChange.EventType) { case SchemaChangeType.Rename: logger.Log("Renaming column " + schemaChange.ColumnName + " to " + schemaChange.NewColumnName, LogLevel.Info); destDataUtils.RenameColumn(table, destDB, schemaChange.SchemaName, schemaChange.TableName, schemaChange.ColumnName, schemaChange.NewColumnName); break; case SchemaChangeType.Modify: logger.Log("Changing data type on column " + schemaChange.ColumnName, LogLevel.Info); destDataUtils.ModifyColumn(table, destDB, schemaChange.SchemaName, schemaChange.TableName, schemaChange.ColumnName, schemaChange.DataType.ToString()); break; case SchemaChangeType.Add: logger.Log("Adding column " + schemaChange.ColumnName, LogLevel.Info); destDataUtils.AddColumn(table, destDB, schemaChange.SchemaName, schemaChange.TableName, schemaChange.ColumnName, schemaChange.DataType.ToString()); break; case SchemaChangeType.Drop: logger.Log("Dropping column " + schemaChange.ColumnName, LogLevel.Info); destDataUtils.DropColumn(table, destDB, schemaChange.SchemaName, schemaChange.TableName, schemaChange.ColumnName); break; } }
public void RenameColumn(TableConf t, string dbName, string columnName, string newColumnName, string historyDB) { logger.Log("Unable to apply rename of column " + columnName + " to " + newColumnName + " on " + dbName + "." + t.FullName + " for slave " + Config.Slave, LogLevel.Error); }
private InsertDelete BuildApplyCommand(TableConf table, string dbName, string CTDBName, long CTID) { string delete = string.Format(@"DELETE FROM {0} P WHERE EXISTS (SELECT 1 FROM {1}..{2} CT WHERE {3});", table.Name, CTDBName, table.ToCTName(CTID), table.PkList); string insert = string.Format(@"INSERT INTO {0} ({1}) SELECT {1} FROM {2}..{3} CT WHERE NOT EXISTS (SELECT 1 FROM {0} P WHERE {4}) AND CT.sys_change_operation IN ( 'I', 'U' );", table.Name, table.NetezzaColumnList, CTDBName, table.ToCTName(CTID), table.PkList); var deleteCmd = new OleDbCommand(delete); var insertCmd = new OleDbCommand(insert); return new InsertDelete(insertCmd, deleteCmd); }
public long?GetInitializeStartVersion(string sourceCTDB, TableConf table) { throw new NotImplementedException(); }
public int SelectIntoCTTable(string sourceCTDB, TableConf table, string sourceDB, ChangeTrackingBatch batch, int timeout, long? startVersionOverride) { throw new NotImplementedException("Netezza is only supported as a slave!"); }