public static void EnsureExist(string connectionString, int timeoutSecs)
{
_log.DebugFormat("Checking existence of history tables in schema: {0}", HistorySchema.HistorySchemaName);
if (!DatabaseUtils.SchemaExists(connectionString, timeoutSecs, HistorySchema.HistorySchemaName))
{
DatabaseUtils.CreateSchema(connectionString, timeoutSecs, HistorySchema.HistorySchemaName);
}
var b = HistoryTablesBuilder.Get();
var tables = b.GetTables();
var tablesInHistorySchema = DatabaseUtils.GetTablesInSchema(
connectionString, timeoutSecs, false, HistorySchema.HistorySchemaName);
var builder = new Builder();
foreach (var t in tables)
{
if (!tablesInHistorySchema.Contains(t.Name))
{
builder.AddTable(t);
}
}
if (builder.HasTables)
{
_log.WarnFormat("Recreating missing history tables in schema: {0}", HistorySchema.HistorySchemaName);
builder.Execute(connectionString, timeoutSecs);
}
}
private void InternalCreateEmptyHistoryTables()
{
EnsureSchemaCreated();
var builder = HistoryTablesBuilder.Get();
builder.Execute(ConnectionString, TimeoutSecs);
}
public TimeSpan PerformDiff(Dictionary <string, PrimaryKeyInfo> pkInfo, long logId)
{
var stopwatch = Stopwatch.StartNew();
_log.DebugFormat("Performing full diff on stage: {0}", DatabaseUtils.GetConnectionDescription(ConnectionString));
var b = HistoryTablesBuilder.Get();
DoParallelProcessing(b, pkInfo, logId);
return(stopwatch.Elapsed);
}
public static HistoryTablesBuilder Get()
{
HistoryTablesBuilder result;
var o = _memoryCache.Get("builder");
if (o == null)
{
result = new HistoryTablesBuilder();
_memoryCache.Set("builder", result, DateTimeOffset.UtcNow.AddMinutes(CacheLifetimeMins));
}
else
{
result = (HistoryTablesBuilder)o;
}
return(result);
}
private void DoParallelProcessing(HistoryTablesBuilder b, Dictionary <string, PrimaryKeyInfo> pkInfo, long logId)
{
var tables = b.GetTables();
var sb = StagingTablesBuilder.Get(StagingSchema.PrimaryStagingSchemaName);
var pOptions = new ParallelOptions {
MaxDegreeOfParallelism = MaxDegreeOfParallelism
};
Parallel.ForEach(tables, pOptions, (historyTable, loopState) =>
{
if (!loopState.IsExceptional)
{
string tableName = historyTable.Name;
string tableNew = DatabaseUtils.GetQualifiedTableName(StagingSchema.PrimaryStagingSchemaName, tableName);
string tableOld = DatabaseUtils.GetQualifiedTableName(StagingSchema.SecondaryStagingSchemaName, tableName);
_log.DebugFormat("Performing diff on tables {0} and {1}", tableNew, tableOld);
var statsNew = SourceTimetableAndRowCount.Get(
ConnectionString, TimeoutSecs, tableName, StagingSchema.PrimaryStagingSchemaName);
var statsOld = SourceTimetableAndRowCount.Get(
ConnectionString, TimeoutSecs, tableName, StagingSchema.SecondaryStagingSchemaName);
HashSet <int> srcTimetableIds = UnionSrcTimetableIds(statsNew, statsOld);
// we could perform the diff on the whole table but to keep memory footprint lower
// for large tables we diff by timetable id...
foreach (var timetableId in srcTimetableIds)
{
_log.DebugFormat("Performing diff for timetable Id {0}", timetableId);
var stagingTableColumnNames = sb.GetColumnNames(tableName);
EtlProcess etl;
if (AllNewlyInserted(timetableId, statsNew, statsOld))
{
// optimisation here (no need to diff, just insert all into history)...
_log.DebugFormat("All rows are new in table {0} for timetable {1}", tableNew, timetableId);
etl = new HistoryEtlProcessBasic(
ConnectionString,
TimeoutSecs,
historyTable,
timetableId,
StagingSchema.PrimaryStagingSchemaName,
HistoryStatusInsert,
logId,
PipelineOptions);
}
else if (AllNewlyDeleted(timetableId, statsNew, statsOld))
{
// optimisation here (no need to diff)...
_log.DebugFormat("All rows have been deleted from table {0} for timetable {1}", tableNew, timetableId);
etl = new HistoryEtlProcessBasic(
ConnectionString,
TimeoutSecs,
historyTable,
timetableId,
StagingSchema.SecondaryStagingSchemaName,
HistoryStatusDelete,
logId,
PipelineOptions);
}
else
{
var pkInfoForTable = pkInfo[tableName];
if (pkInfoForTable == null)
{
throw new ApplicationException(
$"Could not find primary key info for table: {tableName}");
}
// identityColumnCounts originate from the source timetable tables
// so we increment by 1 to account for the src_timetable_id column...
var identityColCount = pkInfoForTable.Columns.Count + 1;
var td = new StageTableDiffer(
ConnectionString, TimeoutSecs, tableOld, tableNew, timetableId, identityColCount);
var diff = td.Execute();
etl = new HistoryEtlProcessDiff(
ConnectionString, TimeoutSecs, historyTable, diff, stagingTableColumnNames, logId, PipelineOptions);
}
etl.Execute();
var errors = etl.GetAllErrors().ToArray();
if (errors.Any())
{
loopState.Stop();
string msg = "Errors occurred during execution of history process";
_log.Error(msg);
// throw the first exception
throw new ApplicationException(msg, errors[0]);
}
}
}
});
}
