public static ResilientSqlTableTableFinalizerBuilder SimpleMsSqlMerge(this ResilientSqlTableTableFinalizerBuilder builder, string[] keyColumns, int commandTimeout = 60 * 60)
{
var columnsToUpdate = builder.Table.Columns
.Where(x => !keyColumns.Any(keyColumn => string.Equals(x, keyColumn, StringComparison.InvariantCultureIgnoreCase)))
.ToList();
return(builder.Add(new CustomMsSqlMergeStatement(builder.Table.Scope.Context)
{
Name = "SimpleMergeFinalizer",
ConnectionString = builder.Table.Scope.ConnectionString,
CommandTimeout = commandTimeout,
SourceTableName = builder.Table.TempTableName,
TargetTableName = builder.Table.TableName,
SourceTableAlias = "s",
TargetTableAlias = "t",
OnCondition = string.Join(" and ", keyColumns
.Select(x => builder.Table.Scope.ConnectionString.Escape(x))
.Select(x => "((s." + x + "=t." + x + ") or (s." + x + " is null and t." + x + " is null))")),
WhenMatchedAction = columnsToUpdate.Count > 0
? "update set " + string.Join(",", columnsToUpdate
.Select(x => builder.Table.Scope.ConnectionString.Escape(x))
.Select(x => "t." + x + "=s." + x))
: null,
WhenNotMatchedByTargetAction = "insert ("
+ string.Join(",", builder.Table.Columns
.Select(x => builder.Table.Scope.ConnectionString.Escape(x))) + ") values ("
+ string.Join(",", builder.Table.Columns
.Select(x => "s." + builder.Table.Scope.ConnectionString.Escape(x))) + ")",
}));
}
public static ResilientSqlTableTableFinalizerBuilder SimpleMsSqlMergeUpdateOnly(this ResilientSqlTableTableFinalizerBuilder builder, string[] keyColumns, int commandTimeout = 60 * 60)
{
var columnsToUpdate = builder.Table.Columns
.Where(x => !keyColumns.Contains(x))
.ToList();
return(builder.Add(new CustomMsSqlMergeStatement(builder.Table.Scope.Context)
{
Name = "SimpleMergeUpdateOnlyFinalizer",
ConnectionString = builder.Table.Scope.ConnectionString,
CommandTimeout = commandTimeout,
SourceTableName = builder.Table.TempTableName,
TargetTableName = builder.Table.TableName,
SourceTableAlias = "s",
TargetTableAlias = "t",
OnCondition = string.Join(" and ", keyColumns
.Select(x => builder.Table.Scope.ConnectionString.Escape(x))
.Select(x => "((s." + x + "=t." + x + ") or (s." + x + " is null and t." + x + " is null))")),
WhenMatchedAction = columnsToUpdate.Count > 0
? "update set " + string.Join(",", columnsToUpdate
.Select(x => builder.Table.Scope.ConnectionString.Escape(x))
.Select(x => "t." + x + "=s." + x))
: null,
}));
}
public static ResilientSqlTableTableFinalizerBuilder TruncateTargetTable(this ResilientSqlTableTableFinalizerBuilder builder, int commandTimeout = 60 * 60)
{
return(builder.Add(new TruncateTable(builder.Table.Scope.Context)
{
Name = "TruncateTargetTableFinalizer",
ConnectionString = builder.Table.Scope.ConnectionString,
TableName = builder.Table.TableName,
CommandTimeout = commandTimeout,
}));
}
public static ResilientSqlTableTableFinalizerBuilder DeleteTargetTable(this ResilientSqlTableTableFinalizerBuilder builder, string customWhereClause = null, int commandTimeout = 60 * 60)
{
return(builder.Add(new DeleteTable(builder.Table.Scope.Context)
{
Name = "DeleteTargetTableFinalizer",
ConnectionString = builder.Table.Scope.ConnectionString,
TableName = builder.Table.TableName,
CommandTimeout = commandTimeout,
CustomWhereClause = customWhereClause,
}));
}
public static ResilientSqlTableTableFinalizerBuilder SimpleMergeInsertOnly(this ResilientSqlTableTableFinalizerBuilder builder, string[] keyColumns, int commandTimeout = 60 * 60)
{
return(builder.Add(new CustomMsSqlMergeStatement(builder.Table.Scope.Context)
{
Name = "SimpleMergeInsertOnlyFinalizer",
ConnectionString = builder.Table.Scope.ConnectionString,
CommandTimeout = commandTimeout,
SourceTableName = builder.Table.TempTableName,
TargetTableName = builder.Table.TableName,
SourceTableAlias = "s",
TargetTableAlias = "t",
OnCondition = string.Join(" and ", keyColumns
.Select(x => builder.Table.Scope.ConnectionString.Escape(x))
.Select(x => "((s." + x + "=t." + x + ") or (s." + x + " is null and t." + x + " is null))")),
WhenNotMatchedByTargetAction = "insert ("
+ string.Join(",", builder.Table.Columns
.Select(x => builder.Table.Scope.ConnectionString.Escape(x)))
+ ") values (" + string.Join(",", builder.Table.Columns
.Select(x => "s." + builder.Table.Scope.ConnectionString.Escape(x))) + ")",
}));
}
public static ResilientSqlTableTableFinalizerBuilder CopyTable(this ResilientSqlTableTableFinalizerBuilder builder, int commandTimeout = 60 * 60, bool copyIdentityColumns = false)
{
if (copyIdentityColumns && builder.Table.Columns == null)
{
throw new EtlException(builder.Table.Scope, "identity columns can be copied only if the " + nameof(ResilientTable) + "." + nameof(ResilientTableBase.Columns) + " is specified");
}
return(builder.Add(new CopyTableIntoExistingTable(builder.Table.Scope.Context)
{
Name = "CopyTableFinalizer",
ConnectionString = builder.Table.Scope.ConnectionString,
Configuration = new TableCopyConfiguration()
{
SourceTableName = builder.Table.TempTableName,
TargetTableName = builder.Table.TableName,
Columns = builder.Table.Columns?
.Select(x => builder.Table.Scope.ConnectionString.Escape(x))
.ToDictionary(x => x, x => x),
},
CommandTimeout = commandTimeout,
CopyIdentityColumns = copyIdentityColumns,
}));
}
private void CreateAndExecuteFinalizers()
{
var tablesOrderedTemp = new List <TableWithOrder>();
for (var i = 0; i < Tables.Count; i++)
{
tablesOrderedTemp.Add(new TableWithOrder()
{
Table = Tables[i],
OriginalIndex = i,
});
}
var tablesOrdered = tablesOrderedTemp
.OrderBy(x => x.Table.OrderDuringFinalization)
.ThenBy(x => x.OriginalIndex)
.Select(x => x.Table)
.ToList();
var recordCounts = new int[tablesOrdered.Count];
for (var i = 0; i < tablesOrdered.Count; i++)
{
var table = tablesOrdered[i];
var recordCount = CountTempRecordsIn(table);
if (table.AdditionalTables?.Count > 0)
{
foreach (var additionalTable in table.AdditionalTables)
{
recordCount += CountTempRecordsIn(additionalTable);
}
}
recordCounts[i] = recordCount;
}
Context.Log(LogSeverity.Information, this, "{TableCountWithData} of {TotalTableCount} temp table contains data",
recordCounts.Count(x => x > 0), recordCounts.Length);
for (var i = 0; i < tablesOrdered.Count; i++)
{
Context.Log(LogSeverity.Verbose, this, "temp table {TableName} contains {RecordCount} records",
ConnectionString.Unescape(tablesOrdered[i].TempTableName), recordCounts[i]);
}
for (var i = 0; i < tablesOrdered.Count; i++)
{
var table = tablesOrdered[i];
if (table.SkipFinalizersIfNoTempData && recordCounts[i] == 0)
{
Context.Log(LogSeverity.Debug, this, "no data found for {TableName}, skipping finalizers",
ConnectionString.Unescape(table.TableName));
continue;
}
var creatorScopeKind = table.SuppressTransactionScopeForCreators
? TransactionScopeKind.Suppress
: TransactionScopeKind.None;
var allFinalizers = new Dictionary <string, IJob[]>();
using (var creatorScope = Context.BeginScope(this, creatorScopeKind, LogSeverity.Information))
{
var builder = new ResilientSqlTableTableFinalizerBuilder()
{
Table = table
};
table.Finalizers.Invoke(builder);
var finalizers = builder.Finalizers.Where(x => x != null).ToArray();
allFinalizers[table.TableName] = finalizers;
Context.Log(LogSeverity.Debug, this, "created {FinalizerCount} finalizer(s) for {TableName} (record count: {RecordCount})",
finalizers.Length,
ConnectionString.Unescape(table.TableName),
recordCounts[i]);
if (table.AdditionalTables != null)
{
foreach (var additionalTable in table.AdditionalTables)
{
builder = new ResilientSqlTableTableFinalizerBuilder()
{
Table = additionalTable
};
additionalTable.Finalizers.Invoke(builder);
finalizers = builder.Finalizers.Where(x => x != null).ToArray();
allFinalizers[additionalTable.TableName] = finalizers;
Context.Log(LogSeverity.Debug, this, "created {FinalizerCount} finalizer(s) for {TableName}",
finalizers.Length,
ConnectionString.Unescape(additionalTable.TableName));
}
}
}
foreach (var tableFinalizers in allFinalizers)
{
foreach (var process in tableFinalizers.Value)
{
var preExceptionCount = Context.ExceptionCount;
process.Execute(this);
if (Context.ExceptionCount > preExceptionCount)
{
break;
}
}
}
}
}
