public static DwhTableBuilder[] BaseIsCurrentFinalizer(this DwhTableBuilder[] builders, Action <KeyBasedFinalizerBuilder> customizer)
{
foreach (var tableBuilder in builders)
{
var tempBuilder = new KeyBasedFinalizerBuilder(tableBuilder);
customizer.Invoke(tempBuilder);
if (tempBuilder.MatchColumns == null)
{
throw new NotSupportedException("you must specify the key columns of " + nameof(BaseIsCurrentFinalizer) + " for table " + tableBuilder.ResilientTable.TableName);
}
tableBuilder.AddFinalizerCreator(_ => CreateBaseIsCurrentFinalizer(tempBuilder));
}
return(builders);
}
private static IEnumerable <IJob> CreateBaseIsCurrentFinalizer(KeyBasedFinalizerBuilder builder)
{
var hasHistoryTable = builder.TableBuilder.Table.GetHasHistoryTable();
var mergeIntoBaseColumns = builder.TableBuilder.Table.Columns
.Where(x => !x.GetUsedByEtlRunInfo());
if (builder.TableBuilder.Table.AnyPrimaryKeyColumnIsIdentity)
{
mergeIntoBaseColumns = mergeIntoBaseColumns
.Where(x => !x.IsPrimaryKey);
}
var mergeIntoBaseColumnNames = mergeIntoBaseColumns
.Select(c => c.NameEscaped(builder.TableBuilder.DwhBuilder.ConnectionString))
.ToArray();
var columnNamesToMatch = builder.MatchColumns
.Select(c => c.NameEscaped(builder.TableBuilder.DwhBuilder.ConnectionString))
.ToArray();
var parameters1 = new Dictionary <string, object>();
if (builder.TableBuilder.HasEtlRunInfo)
{
parameters1.Add("EtlRunId", builder.TableBuilder.DwhBuilder.EtlRunId.Value);
}
var columnNamesToUpdate = builder.TableBuilder.Table.Columns
.Where(x => !x.GetUsedByEtlRunInfo() &&
!x.IsPrimaryKey &&
!builder.MatchColumns.Contains(x))
.Select(c => c.NameEscaped(builder.TableBuilder.DwhBuilder.ConnectionString))
.ToArray();
yield return(new CustomMsSqlMergeStatement(builder.TableBuilder.ResilientTable.Scope.Context)
{
Name = "MergeIntoBase",
ConnectionString = builder.TableBuilder.ResilientTable.Scope.ConnectionString,
CommandTimeout = 60 * 60,
SourceTableName = builder.TableBuilder.ResilientTable.TempTableName,
TargetTableName = builder.TableBuilder.ResilientTable.TableName,
SourceTableAlias = "s",
TargetTableAlias = "t",
OnCondition = string.Join(" and ", columnNamesToMatch.Select(x => "((s." + x + "=t." + x + ") or (s." + x + " is null and t." + x + " is null))")),
WhenMatchedAction = columnNamesToUpdate.Length > 0 || builder.TableBuilder.HasEtlRunInfo
? "UPDATE SET "
+ string.Join(", ", columnNamesToUpdate.Select(c => "t." + c + "=s." + c))
+ (builder.TableBuilder.HasEtlRunInfo
? (columnNamesToUpdate.Length > 0 ? ", " : "") + builder.TableBuilder.EtlRunUpdateColumnNameEscaped + "=@EtlRunId"
: "")
: null,
WhenNotMatchedByTargetAction = "INSERT (" + string.Join(", ", mergeIntoBaseColumnNames)
+ (builder.TableBuilder.HasEtlRunInfo
? ", " + builder.TableBuilder.EtlRunInsertColumnNameEscaped + ", " + builder.TableBuilder.EtlRunUpdateColumnNameEscaped + ", " + builder.TableBuilder.EtlRunFromColumnNameEscaped
: "")
+ ") VALUES ("
+ string.Join(", ", mergeIntoBaseColumnNames.Select(c => "s." + c))
+ (builder.TableBuilder.HasEtlRunInfo ? ", @EtlRunId, @EtlRunId, @EtlRunId" : "")
+ ")",
Parameters = parameters1,
});
if (hasHistoryTable)
{
var histTableName = builder.TableBuilder.DwhBuilder.GetEscapedHistTableName(builder.TableBuilder.Table);
var parameters2 = new Dictionary <string, object>();
if (builder.TableBuilder.HasEtlRunInfo)
{
parameters2["EtlRunId"] = builder.TableBuilder.DwhBuilder.EtlRunId.Value;
}
if (builder.TableBuilder.DwhBuilder.Configuration.InfiniteFutureDateTime != null)
{
parameters2["InfiniteFuture"] = builder.TableBuilder.DwhBuilder.Configuration.InfiniteFutureDateTime;
}
yield return(new CustomMsSqlMergeStatement(builder.TableBuilder.ResilientTable.Scope.Context)
{
Name = "CloseOpenEndedHistoryRecords",
ConnectionString = builder.TableBuilder.ResilientTable.Scope.ConnectionString,
CommandTimeout = 60 * 60,
SourceTableName = builder.TableBuilder.ResilientTable.TempTableName,
TargetTableName = histTableName,
SourceTableAlias = "s",
TargetTableAlias = "t",
OnCondition = string.Join(" and ", columnNamesToMatch.Select(x => "((s." + x + "=t." + x + ") or (s." + x + " is null and t." + x + " is null))"))
+ " and t." + builder.TableBuilder.ValidToColumnNameEscaped + (builder.TableBuilder.DwhBuilder.Configuration.InfiniteFutureDateTime == null ? " IS NULL" : "=@InfiniteFuture"),
WhenMatchedAction = "UPDATE SET t."
+ builder.TableBuilder.ValidToColumnNameEscaped + "=s." + builder.TableBuilder.ValidFromColumnNameEscaped
+ (builder.TableBuilder.HasEtlRunInfo
? ", " + builder.TableBuilder.EtlRunUpdateColumnNameEscaped + "=@EtlRunId, " + builder.TableBuilder.EtlRunToColumnNameEscaped + "=@EtlRunId"
: ""),
Parameters = parameters2,
});
var noHistoryColumns = builder.TableBuilder.Table.Columns
.Where(x => x.GetHistoryDisabled() && !x.GetUsedByEtlRunInfo()).ToList();
if (noHistoryColumns.Count > 0)
{
var parameters3 = new Dictionary <string, object>();
if (builder.TableBuilder.HasEtlRunInfo)
{
parameters3["EtlRunId"] = builder.TableBuilder.DwhBuilder.EtlRunId.Value;
}
yield return(new CustomMsSqlMergeStatement(builder.TableBuilder.ResilientTable.Scope.Context)
{
Name = "UpdateNoHistoryColumns",
ConnectionString = builder.TableBuilder.ResilientTable.Scope.ConnectionString,
CommandTimeout = 60 * 60,
SourceTableName = builder.TableBuilder.ResilientTable.TempTableName,
TargetTableName = histTableName,
SourceTableAlias = "s",
TargetTableAlias = "t",
OnCondition = string.Join(" and ", columnNamesToMatch.Select(x => "((s." + x + "=t." + x + ") or (s." + x + " is null and t." + x + " is null))")),
WhenMatchedAction = "UPDATE SET "
+ string.Join(", ", noHistoryColumns.Select(col => "t." + col.NameEscaped(builder.TableBuilder.DwhBuilder.ConnectionString) + " = s." + col.NameEscaped(builder.TableBuilder.DwhBuilder.ConnectionString)))
+ (builder.TableBuilder.HasEtlRunInfo
? ", " + builder.TableBuilder.EtlRunUpdateColumnNameEscaped + "=@EtlRunId"
: ""),
Parameters = parameters3,
});
}
var copyToHistoryColumnNames = builder.TableBuilder.Table.Columns
.Where(x => !x.GetUsedByEtlRunInfo() &&
!string.Equals(x.Name, builder.TableBuilder.ValidToColumnName, StringComparison.InvariantCulture))
.Select(c => c.NameEscaped(builder.TableBuilder.ResilientTable.Scope.ConnectionString))
.ToArray();
var columnDefaults = new Dictionary <string, object>();
if (builder.TableBuilder.ValidToColumnNameEscaped != null)
{
columnDefaults[builder.TableBuilder.ValidToColumnNameEscaped] = builder.TableBuilder.DwhBuilder.Configuration.InfiniteFutureDateTime;
}
if (builder.TableBuilder.HasEtlRunInfo)
{
columnDefaults[builder.TableBuilder.EtlRunInsertColumnNameEscaped] = builder.TableBuilder.DwhBuilder.EtlRunId.Value;
columnDefaults[builder.TableBuilder.EtlRunUpdateColumnNameEscaped] = builder.TableBuilder.DwhBuilder.EtlRunId.Value;
columnDefaults[builder.TableBuilder.EtlRunFromColumnNameEscaped] = builder.TableBuilder.DwhBuilder.EtlRunId.Value;
}
yield return(new CopyTableIntoExistingTable(builder.TableBuilder.ResilientTable.Scope.Context)
{
Name = "CopyToHistory",
ConnectionString = builder.TableBuilder.ResilientTable.Scope.ConnectionString,
Configuration = new TableCopyConfiguration()
{
SourceTableName = builder.TableBuilder.ResilientTable.TempTableName,
TargetTableName = histTableName,
Columns = copyToHistoryColumnNames.ToDictionary(x => x),
},
ColumnDefaults = columnDefaults,
CommandTimeout = 60 * 60,
});
}
}
private static IEnumerable <IJob> CreateBaseIsHistoryFinalizer(KeyBasedFinalizerBuilder builder)
{
var mergeIntoBaseColumns = builder.TableBuilder.Table.Columns
.Where(x => !x.GetUsedByEtlRunInfo());
if (builder.TableBuilder.Table.AnyPrimaryKeyColumnIsIdentity)
{
mergeIntoBaseColumns = mergeIntoBaseColumns
.Where(x => !x.IsPrimaryKey);
}
var mergeIntoBaseColumnNames = mergeIntoBaseColumns
.Select(c => c.NameEscaped(builder.TableBuilder.DwhBuilder.ConnectionString))
.ToArray();
var columnNamesToMatch = builder.MatchColumns
.Select(c => c.NameEscaped(builder.TableBuilder.DwhBuilder.ConnectionString))
.ToArray();
var parameters = new Dictionary <string, object>();
if (builder.TableBuilder.HasEtlRunInfo)
{
parameters.Add("EtlRunId", builder.TableBuilder.DwhBuilder.EtlRunId.Value);
}
if (builder.TableBuilder.DwhBuilder.Configuration.InfiniteFutureDateTime != null)
{
parameters.Add("InfiniteFuture", builder.TableBuilder.DwhBuilder.Configuration.InfiniteFutureDateTime);
}
// todo: support NoHistoryColumnProperty
yield return(new CustomMsSqlMergeStatement(builder.TableBuilder.ResilientTable.Scope.Context)
{
Name = "CloseOpenEndedBaseRecords",
ConnectionString = builder.TableBuilder.ResilientTable.Scope.ConnectionString,
CommandTimeout = 60 * 60,
SourceTableName = builder.TableBuilder.ResilientTable.TempTableName,
TargetTableName = builder.TableBuilder.ResilientTable.TableName,
SourceTableAlias = "s",
TargetTableAlias = "t",
OnCondition = string.Join(" and ", columnNamesToMatch.Select(x => "((s." + x + "=t." + x + ") or (s." + x + " is null and t." + x + " is null))"))
+ " and t." + builder.TableBuilder.ValidToColumnNameEscaped + (builder.TableBuilder.DwhBuilder.Configuration.InfiniteFutureDateTime == null ? " IS NULL" : "=@InfiniteFuture"),
WhenMatchedAction = "UPDATE SET t."
+ builder.TableBuilder.ValidToColumnNameEscaped + "=s." + builder.TableBuilder.ValidFromColumnNameEscaped
+ (builder.TableBuilder.HasEtlRunInfo
? ", " + builder.TableBuilder.EtlRunUpdateColumnNameEscaped + "=@EtlRunId, " + builder.TableBuilder.EtlRunToColumnNameEscaped + "=@EtlRunId"
: ""),
Parameters = parameters,
});
var columnDefaults = new Dictionary <string, object>();
if (builder.TableBuilder.HasEtlRunInfo)
{
columnDefaults[builder.TableBuilder.EtlRunInsertColumnNameEscaped] = builder.TableBuilder.DwhBuilder.EtlRunId.Value;
columnDefaults[builder.TableBuilder.EtlRunUpdateColumnNameEscaped] = builder.TableBuilder.DwhBuilder.EtlRunId.Value;
columnDefaults[builder.TableBuilder.EtlRunFromColumnNameEscaped] = builder.TableBuilder.DwhBuilder.EtlRunId.Value;
}
yield return(new CopyTableIntoExistingTable(builder.TableBuilder.ResilientTable.Scope.Context)
{
Name = "CopyToBase",
ConnectionString = builder.TableBuilder.ResilientTable.Scope.ConnectionString,
Configuration = new TableCopyConfiguration()
{
SourceTableName = builder.TableBuilder.ResilientTable.TempTableName,
TargetTableName = builder.TableBuilder.ResilientTable.TableName,
Columns = mergeIntoBaseColumnNames.ToDictionary(x => x),
},
ColumnDefaults = columnDefaults,
CommandTimeout = 60 * 60,
});
}
