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