public List <KeyValuePair <string, string> > GetListOfInsertColumnFields(MigrationColumnSet columnsToMigrate, int dataLoadRunID)
{
var inserts = new List <KeyValuePair <string, string> >();
columnsToMigrate.FieldsToUpdate.ToList().ForEach(column =>
inserts.Add(new KeyValuePair <string, string>("[" + column + "]", "source.[" + column + "]")));
inserts.Add(new KeyValuePair <string, string>(SpecialFieldNames.DataLoadRunID, dataLoadRunID.ToString()));
return(inserts);
}
public override void MigrateTable(IDataLoadJob job, MigrationColumnSet columnsToMigrate, int dataLoadInfoID, GracefulCancellationToken cancellationToken, ref int inserts, ref int updates)
{
var server = columnsToMigrate.DestinationTable.Database.Server;
//see CrossDatabaseMergeCommandTest
/* ------------MIGRATE NEW RECORDS (novel by primary key)--------
*
*
* INSERT INTO CrossDatabaseMergeCommandTo..ToTable (Name,Age,Postcode,hic_dataLoadRunID)
* SELECT
* [CrossDatabaseMergeCommandFrom]..CrossDatabaseMergeCommandTo_ToTable_STAGING.Name,
* [CrossDatabaseMergeCommandFrom]..CrossDatabaseMergeCommandTo_ToTable_STAGING.Age,
* [CrossDatabaseMergeCommandFrom]..CrossDatabaseMergeCommandTo_ToTable_STAGING.Postcode,
* 1
* FROM
* [CrossDatabaseMergeCommandFrom]..CrossDatabaseMergeCommandTo_ToTable_STAGING
* left join
* CrossDatabaseMergeCommandTo..ToTable
* on
* [CrossDatabaseMergeCommandFrom]..CrossDatabaseMergeCommandTo_ToTable_STAGING.Age = CrossDatabaseMergeCommandTo..ToTable.Age
* AND
* [CrossDatabaseMergeCommandFrom]..CrossDatabaseMergeCommandTo_ToTable_STAGING.Name = CrossDatabaseMergeCommandTo..ToTable.Name
* WHERE
* CrossDatabaseMergeCommandTo..ToTable.Age is null
*/
StringBuilder sbInsert = new StringBuilder();
var syntax = server.GetQuerySyntaxHelper();
sbInsert.AppendLine(string.Format("INSERT INTO {0} ({1}",
columnsToMigrate.DestinationTable.GetFullyQualifiedName(),
string.Join(",", columnsToMigrate.FieldsToUpdate.Select(c => syntax.EnsureWrapped(c.GetRuntimeName())))));
//if we are not ignoring the trigger then we should record the data load run ID
if (!job.LoadMetadata.IgnoreTrigger)
{
sbInsert.AppendLine($",{syntax.EnsureWrapped(SpecialFieldNames.DataLoadRunID)}");
}
sbInsert.AppendLine(")");
sbInsert.AppendLine("SELECT");
// Add the columns we are migrating
sbInsert.AppendLine(string.Join("," + Environment.NewLine, columnsToMigrate.FieldsToUpdate.Select(c => c.GetFullyQualifiedName())));
// If we are using trigger also add the run ID e.g. ",50"
if (!job.LoadMetadata.IgnoreTrigger)
{
sbInsert.AppendLine("," + dataLoadInfoID.ToString());
}
sbInsert.AppendLine("FROM");
sbInsert.AppendLine(columnsToMigrate.SourceTable.GetFullyQualifiedName());
sbInsert.AppendLine("LEFT JOIN");
sbInsert.AppendLine(columnsToMigrate.DestinationTable.GetFullyQualifiedName());
sbInsert.AppendLine("ON");
sbInsert.AppendLine(
string.Join(" AND " + Environment.NewLine,
columnsToMigrate.PrimaryKeys.Select(
pk =>
string.Format("{0}.{1}={2}.{1}", columnsToMigrate.SourceTable.GetFullyQualifiedName(),
syntax.EnsureWrapped(pk.GetRuntimeName()), columnsToMigrate.DestinationTable.GetFullyQualifiedName()))));
sbInsert.AppendLine("WHERE");
sbInsert.AppendLine(string.Format("{0}.{1} IS NULL",
columnsToMigrate.DestinationTable.GetFullyQualifiedName(),
syntax.EnsureWrapped(columnsToMigrate.PrimaryKeys.First().GetRuntimeName())));
//right at the end of the SELECT
if (columnsToMigrate.DestinationTable.Database.Server.DatabaseType == DatabaseType.MySql)
{
sbInsert.Append(" FOR UPDATE");
}
string insertSql = sbInsert.ToString();
var cmd = server.GetCommand(insertSql, _managedConnection);
cmd.CommandTimeout = Timeout;
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "INSERT query: " + Environment.NewLine + insertSql));
cancellationToken.ThrowIfCancellationRequested();
try
{
inserts = cmd.ExecuteNonQuery();
List <CustomLine> sqlLines = new List <CustomLine>();
var toSet = columnsToMigrate.FieldsToUpdate.Where(c => !c.IsPrimaryKey).Select(c => string.Format("t1.{0} = t2.{0}", syntax.EnsureWrapped(c.GetRuntimeName()))).ToArray();
if (!toSet.Any())
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Table " + columnsToMigrate.DestinationTable + " is entirely composed of PrimaryKey columns or hic_ columns so UPDATE will NOT take place"));
return;
}
var toDiff = columnsToMigrate.FieldsToDiff.Where(c => !c.IsPrimaryKey).ToArray();
if (!toDiff.Any())
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Table " + columnsToMigrate.DestinationTable + " is entirely composed of PrimaryKey columns or hic_ columns/ other non DIFF columns that will not result in an UPDATE will NOT take place"));
return;
}
//t1.Name = t2.Name, t1.Age=T2.Age etc
sqlLines.Add(new CustomLine(string.Join(",", toSet), QueryComponent.SET));
//also update the hic_dataLoadRunID field
if (!job.LoadMetadata.IgnoreTrigger)
{
sqlLines.Add(new CustomLine(string.Format("t1.{0}={1}",
syntax.EnsureWrapped(SpecialFieldNames.DataLoadRunID)
, dataLoadInfoID), QueryComponent.SET));
}
//t1.Name <> t2.Name AND t1.Age <> t2.Age etc
sqlLines.Add(new CustomLine(string.Join(" OR ", toDiff.Select(c => GetORLine(c, syntax))), QueryComponent.WHERE));
//the join
sqlLines.AddRange(columnsToMigrate.PrimaryKeys.Select(p => new CustomLine(string.Format("t1.{0} = t2.{0}", syntax.EnsureWrapped(p.GetRuntimeName())), QueryComponent.JoinInfoJoin)));
var updateHelper = columnsToMigrate.DestinationTable.Database.Server.GetQuerySyntaxHelper().UpdateHelper;
var updateQuery = updateHelper.BuildUpdate(
columnsToMigrate.DestinationTable,
columnsToMigrate.SourceTable,
sqlLines);
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Update query:" + Environment.NewLine + updateQuery));
var updateCmd = server.GetCommand(updateQuery, _managedConnection);
updateCmd.CommandTimeout = Timeout;
cancellationToken.ThrowIfCancellationRequested();
try
{
updates = updateCmd.ExecuteNonQuery();
}
catch (Exception e)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Did not successfully perform the update queries: " + updateQuery, e));
throw new Exception("Did not successfully perform the update queries: " + updateQuery + " - " + e);
}
}
catch (OperationCanceledException)
{
throw; // have to catch and rethrow this because of the catch-all below
}
catch (Exception e)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Failed to migrate " + columnsToMigrate.SourceTable + " to " + columnsToMigrate.DestinationTable, e));
throw new Exception("Failed to migrate " + columnsToMigrate.SourceTable + " to " + columnsToMigrate.DestinationTable + ": " + e);
}
}
public LiveMigrationQueryHelper(MigrationColumnSet columnsToMigrate, int dataLoadRunID) : base(columnsToMigrate)
{
_dataLoadRunID = dataLoadRunID;
}
public MigrationColumnSetQueryHelper(MigrationColumnSet migrationColumnSet)
{
_migrationColumnSet = migrationColumnSet;
}
protected MigrationQueryHelper(MigrationColumnSet columnsToMigrate)
{
ColumnsToMigrate = columnsToMigrate;
McsQueryHelper = new MigrationColumnSetQueryHelper(ColumnsToMigrate);
}
public ReverseMigrationQueryHelper(MigrationColumnSet columnsToMigrate) : base(columnsToMigrate)
{
}
