/// <summary>
/// Runs copies the datalink's current source/target tables and uses them as the basis for future test.
/// </summary>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public async Task <bool> RunSnapshot(CancellationToken cancellationToken)
{
try
{
var ct = CancellationTokenSource.CreateLinkedTokenSource(_cancellationTokenSource.Token,
cancellationToken);
var token = ct.Token;
token.ThrowIfCancellationRequested();
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Started, null, null);
var percent = 0;
foreach (var step in _datalinkTest.DexihDatalinkTestSteps.OrderBy(c => c.Position).Where(c => c.IsValid))
{
percent += 100 / _datalinkTest.DexihDatalinkTestSteps.Count;
var datalink = _hub.DexihDatalinks.SingleOrDefault(c => c.IsValid && c.Key == step.DatalinkKey);
if (datalink == null)
{
throw new DatalinkTestRunException(
$"The datalink test {_datalinkTest.Name} failed as the datalink with the key {step.DatalinkKey} could not be found.");
}
// copy the expected results
if (datalink.LoadStrategy == TransformWriterTarget.ETransformWriterMethod.Bulk)
{
foreach (var target in datalink.DexihDatalinkTargets)
{
var dbDatalinkTargetTable = _hub.GetTableFromKey(target.TableKey);
if (dbDatalinkTargetTable == null)
{
throw new DatalinkTestRunException($"The table with key {target.TableKey} could not be found.");
}
var dbDatalinkTargetConnection = _hub.DexihConnections.SingleOrDefault(c => c.IsValid && c.Key == dbDatalinkTargetTable.ConnectionKey);
if (dbDatalinkTargetConnection == null)
{
throw new DatalinkTestRunException($"The connection for the table {dbDatalinkTargetTable.Name} with the connection key {dbDatalinkTargetTable.Key} could not be found.");
}
var targetConnection = dbDatalinkTargetConnection.GetConnection(_transformSettings);
var targetTable = dbDatalinkTargetTable.GetTable(_hub, targetConnection, _transformSettings);
var dbExpectedConnection = _hub.DexihConnections.SingleOrDefault(c => c.IsValid && c.Key == step.ExpectedConnectionKey);
if (dbExpectedConnection == null)
{
throw new DatalinkTestRunException($"The connection for the step {step.Name} with the connection key {step.ExpectedConnectionKey} could not be found.");
}
var dbExpectedTable = dbDatalinkTargetTable.CloneProperties();
dbExpectedTable.Name = step.ExpectedTableName;
dbExpectedTable.Schema = step.ExpectedSchema;
var expectedConnection = dbExpectedConnection.GetConnection(_transformSettings);
var expectedTable = dbExpectedTable.GetTable(_hub, targetConnection, _transformSettings);
UpdateProgress(percent);
await expectedConnection.CreateTable(expectedTable, true, token);
await using (var targetReader = targetConnection.GetTransformReader(targetTable))
await using (var targetReader2 = new ReaderConvertDataTypes(expectedConnection, targetReader))
{
await targetReader2.Open(0, null, token);
await expectedConnection.ExecuteInsertBulk(expectedTable, targetReader2, token);
WriterResult.IncrementRowsCreated(targetReader2.TransformRows);
WriterResult.IncrementRowsReadPrimary(targetReader2.TotalRowsReadPrimary);
}
}
}
else
{
// if there is no target table, then copy the outputs of the datalink.
var dbExpectedConnection = _hub.DexihConnections.Single(c => c.IsValid && c.Key == step.ExpectedConnectionKey);
var dbExpectedTable = datalink.GetOutputTable();
var expectedTable = dbExpectedTable.GetTable(null, null);
expectedTable.Name = step.ExpectedTableName;
expectedTable.Schema = step.ExpectedSchema;
var expectedConnection = dbExpectedConnection.GetConnection(_transformSettings);
var transformOperations = new TransformsManager(_transformSettings);
var runPlan =
transformOperations.CreateRunPlan(_hub, datalink, null, null, null, _transformWriterOptions);
UpdateProgress(percent);
await expectedConnection.CreateTable(expectedTable, true, cancellationToken);
await using (var transform = runPlan.sourceTransform)
await using (var transform2 = new ReaderConvertDataTypes(expectedConnection, transform))
{
await transform2.Open(0, null, cancellationToken);
await expectedConnection.ExecuteInsertBulk(expectedTable, transform2, cancellationToken);
WriterResult.IncrementRowsCreated(transform2.TransformRows);
WriterResult.IncrementRowsReadPrimary(transform2.TotalRowsReadPrimary);
}
}
foreach (var testTable in step.DexihDatalinkTestTables)
{
var dbDatalinkTable = _hub.GetTableFromKey(testTable.TableKey);
var dbDatalinkConnection = _hub.DexihConnections.Single(c => c.IsValid && c.Key == dbDatalinkTable.ConnectionKey);
var datalinkConnection = dbDatalinkConnection.GetConnection(_transformSettings);
var datalinkTable = dbDatalinkTable.GetTable(_hub, datalinkConnection, _transformSettings);
var dbSourceConnection =
_hub.DexihConnections.Single(c => c.IsValid && c.Key == testTable.SourceConnectionKey);
var dbSourceTable = dbDatalinkTable.CloneProperties();
dbSourceTable.Name = testTable.SourceTableName;
dbSourceTable.Schema = testTable.SourceSchema;
var testConnection = dbSourceConnection.GetConnection(_transformSettings);
var testTable1 = dbSourceTable.GetTable(_hub, testConnection, _transformSettings);
UpdateProgress(percent);
await testConnection.CreateTable(testTable1, true, cancellationToken);
await using (var datalinkReader = datalinkConnection.GetTransformReader(datalinkTable))
await using (var datalinkReader2 = new ReaderConvertDataTypes(testConnection, datalinkReader))
{
await datalinkReader2.Open(0, null, cancellationToken);
await testConnection.ExecuteInsertBulk(testTable1, datalinkReader2, cancellationToken);
WriterResult.IncrementRowsCreated(datalinkReader2.TransformRows);
WriterResult.IncrementRowsReadPrimary(datalinkReader2.TotalRowsReadPrimary);
}
}
}
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Finished, "Finished", null);
await WriterResult.CompleteDatabaseWrites();
return(true);
}
catch (Exception ex)
{
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Abended, ex.Message, ex);
return(false);
}
}
public (Transform sourceTransform, Table sourceTable) CreateRunPlan(DexihHub hub, DexihDatalink hubDatalink, InputColumn[] inputColumns, long?maxDatalinkTransformKey, object maxIncrementalValue, TransformWriterOptions transformWriterOptions) //Last datatransform key is used to preview the output of a specific transform in the series.
{
try
{
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name} started.");
var timer = Stopwatch.StartNew();
if (transformWriterOptions == null)
{
transformWriterOptions = new TransformWriterOptions();
}
var primaryTransformResult = GetSourceTransform(hub, hubDatalink.SourceDatalinkTable, inputColumns, transformWriterOptions);
var primaryTransform = primaryTransformResult.sourceTransform;
var sourceTable = primaryTransformResult.sourceTable;
var updateStrategy = hubDatalink.UpdateStrategy;
//add a filter for the incremental column (if there is one)
TableColumn incrementalCol = null;
if (updateStrategy == EUpdateStrategy.AppendUpdateDeletePreserve || updateStrategy == EUpdateStrategy.AppendUpdatePreserve)
{
incrementalCol = primaryTransform.CacheTable?.GetColumn(EDeltaType.ValidFromDate);
}
else
{
incrementalCol = primaryTransform.CacheTable?.Columns.SingleOrDefault(c => c.IsIncrementalUpdate);
}
if (transformWriterOptions.ResetIncremental)
{
maxIncrementalValue = transformWriterOptions.ResetIncrementalValue;
}
if (maxDatalinkTransformKey == null &&
transformWriterOptions.IsEmptyTarget() == false &&
!(updateStrategy == EUpdateStrategy.Reload || updateStrategy == EUpdateStrategy.AppendUpdateDelete || updateStrategy == EUpdateStrategy.AppendUpdateDeletePreserve) &&
incrementalCol != null &&
maxIncrementalValue != null &&
maxIncrementalValue.ToString() != "")
{
var mappings = new Mappings()
{
new MapFilter(incrementalCol, maxIncrementalValue, ECompare.GreaterThan)
};
var filterTransform = new TransformFilter(primaryTransform, mappings)
{
Name = $"Prefilter maxIncremental {maxIncrementalValue}"
};
filterTransform.SetInTransform(primaryTransform);
primaryTransform = filterTransform;
}
DexihTable targetTable = null;
var target = hubDatalink.DexihDatalinkTargets.FirstOrDefault(c => c.NodeDatalinkColumnKey == null);
if (target != null)
{
targetTable = hub.GetTableFromKey(target.TableKey);
}
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name}. Added incremental filter. Elapsed: {timer.Elapsed}");
//loop through the transforms to create the chain.
foreach (var datalinkTransform in hubDatalink.DexihDatalinkTransforms.OrderBy(c => c.Position).Where(c => c.IsValid))
{
//if this is an empty transform, then ignore it.
if (datalinkTransform.DexihDatalinkTransformItems.Count == 0)
{
if (datalinkTransform.TransformType == ETransformType.Filter || datalinkTransform.TransformType == ETransformType.Mapping && datalinkTransform.PassThroughColumns)
{
if (datalinkTransform.Key == maxDatalinkTransformKey)
{
break;
}
continue;
}
}
//if contains a join table, then add it in.
Transform referenceTransform = null;
if (datalinkTransform.JoinDatalinkTable != null)
{
var joinTransformResult = GetSourceTransform(hub, datalinkTransform.JoinDatalinkTable, null, transformWriterOptions);
referenceTransform = joinTransformResult.sourceTransform;
}
var transform = datalinkTransform.GetTransform(hub, hubDatalink, transformWriterOptions.GlobalSettings, _transformSettings, primaryTransform, referenceTransform, targetTable, _logger);
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name}, adding transform {datalinkTransform.Name}. Elapsed: {timer.Elapsed}");
primaryTransform = transform;
if (datalinkTransform.Key == maxDatalinkTransformKey)
{
break;
}
}
//if the maxDatalinkTransformKey is null (i.e. we are not doing a preview), and there are profiles add a profile transform.
if (maxDatalinkTransformKey == null && hubDatalink.DexihDatalinkProfiles != null && hubDatalink.DexihDatalinkProfiles.Count > 0 && targetTable != null)
{
var profileRules = new Mappings();
foreach (var profile in hubDatalink.DexihDatalinkProfiles)
{
foreach (var column in targetTable.DexihTableColumns.Where(c => c.IsSourceColumn))
{
var profileFunction = GetProfileFunction(profile.FunctionAssemblyName, profile.FunctionClassName, profile.FunctionMethodName, column.Name, profile.DetailedResults, transformWriterOptions.GlobalSettings);
profileRules.Add(profileFunction);
}
}
var transform = new TransformProfile(primaryTransform, profileRules)
{
Name = "User defined profiles"
};
primaryTransform = transform;
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name}, adding profiling. Elapsed: {timer.Elapsed}");
}
if (transformWriterOptions.SelectQuery != null)
{
var transform = new TransformQuery(primaryTransform, transformWriterOptions.SelectQuery)
{
Name = "Select Query Filter"
};
primaryTransform = transform;
}
_logger?.LogTrace($"CreateRunPlan {hubDatalink.Name}, completed. Elapsed: {timer.Elapsed}");
return(primaryTransform, sourceTable);
}
catch (Exception ex)
{
throw new TransformManagerException($"Create run plan failed. {ex.Message}", ex);
}
}
public async Task <bool> Run(CancellationToken cancellationToken)
{
try
{
cancellationToken.ThrowIfCancellationRequested();
WriterResult.StartTime = DateTime.Now;
WriterResult.LastUpdateTime = DateTime.Now;
var runStatus = WriterResult.SetRunStatus(ERunStatus.Started, null, null);
if (!runStatus)
{
throw new DatajobRunException($"Failed to set status");
}
if (_transformWriterOptions.TargetAction == TransformWriterOptions.ETargetAction.Truncate)
{
runStatus = WriterResult.SetRunStatus(ERunStatus.Started, "Truncating tables...", null);
var targetTables = new HashSet <DexihTable>();
foreach (var step in Datajob.DexihDatalinkSteps.Where(c => c.IsValid))
{
var datalink = _hub.DexihDatalinks.SingleOrDefault(c => c.IsValid && c.Key == step.DatalinkKey);
if (datalink == null)
{
throw new DatajobRunException($"The datalink in the step {step.Name} with the datalink key {step.DatalinkKey} cound not be found.");
}
foreach (var target in datalink.DexihDatalinkTargets)
{
if (target.TableKey > 0)
{
var table = _hub.GetTableFromKey(target.TableKey);
if (table != null)
{
targetTables.Add(table);
}
}
}
}
// this loops through attempting to truncate tables one by one.
// is repeats when there are failures to accomodate for some table having foriegn keys
var atLeastOneSuccess = true;
var atLeastOneFail = true;
while (atLeastOneSuccess && atLeastOneFail)
{
atLeastOneSuccess = false;
atLeastOneFail = false;
var newTagetTables = new HashSet <DexihTable>();
foreach (var dbTable in targetTables)
{
var dbConnection = _hub.DexihConnections.SingleOrDefault(c => c.Key == dbTable.ConnectionKey);
if (dbConnection == null)
{
throw new DatajobRunException($"The connection for the table {dbTable.Name} with the connection key {dbTable.ConnectionKey} could not be found.");
}
var connection = dbConnection.GetConnection(_transformSettings);
var table = dbTable.GetTable(_hub, connection, _transformSettings);
try
{
await connection.TruncateTable(table, cancellationToken);
atLeastOneSuccess = true;
}
catch
{
atLeastOneFail = true;
newTagetTables.Add(dbTable);
}
}
targetTables = newTagetTables;
}
if (targetTables.Count > 0)
{
var message = $"The job failed as the following tables could not be truncated: {string.Join(", ", targetTables.Select(c => c.Name))}";
WriterResult.SetRunStatus(ERunStatus.Abended, message, null);
return(false);
}
}
var inputParameters = new InputParameters();
foreach (var parameter in Datajob.Parameters)
{
inputParameters.Add(new InputParameter()
{
Name = parameter.Name, Value = parameter.Value, Rank = parameter.Rank
});
}
//start all jobs async
foreach (var step in Datajob.DexihDatalinkSteps.Where(c => c.IsValid))
{
var datalink = _hub.DexihDatalinks.SingleOrDefault(c => c.IsValid && c.Key == step.DatalinkKey);
if (datalink == null)
{
throw new DatajobRunException($"The step {step.Name} contains a datalink with the key {step.DatalinkKey} which can not be found.");
}
foreach (var parameter in step.Parameters)
{
parameter.Value = inputParameters.SetParameters(parameter.Value, parameter.Rank);
}
var transformSettings = new TransformSettings
{
HubVariables = _transformSettings.HubVariables,
RemoteSettings = _transformSettings.RemoteSettings,
InputParameters = step.Parameters.ToArray <InputParameterBase>(),
ClientFactory = _transformSettings.ClientFactory
};
var inputColumns = step.DexihDatalinkStepColumns.Select(c => c.ToInputColumn()).ToArray();
var datalinkRun = new DatalinkRun(transformSettings, _logger, WriterResult.AuditKey, datalink, _hub, inputColumns, _transformWriterOptions, _alertQueue, _alertEmails)
{
DatalinkStepKey = step.Key
};
DatalinkSteps.Add(datalinkRun);
//start datalinks that have no dependencies.
if (step.DexihDatalinkDependencies == null || step.DexihDatalinkDependencies.Count == 0)
{
StartDatalink(datalinkRun);
}
}
WriterResult.SetRunStatus(ERunStatus.Running, null, null);
await WaitUntilFinished();
return(true);
}
catch (OperationCanceledException)
{
Cancel();
await WaitUntilFinished();
WriterResult.SetRunStatus(ERunStatus.Cancelled, "Datajob was cancelled", null);
throw new DatajobRunException($"The datajob {Datajob.Name} was cancelled.");
}
catch (Exception ex)
{
Cancel();
await WaitUntilFinished();
var message = $"The job {Datajob.Name} failed. {ex.Message}";
WriterResult?.SetRunStatus(ERunStatus.Abended, message, ex);
throw new DatajobRunException(message, ex);
}
finally
{
await WriterResult.CompleteDatabaseWrites();
OnFinish?.Invoke(this);
}
}
public (Transform sourceTransform, Table sourceTable) GetSourceTransform(DexihHub hub, DexihDatalinkTable hubDatalinkTable, InputColumn[] inputColumns, TransformWriterOptions transformWriterOptions)
{
try
{
Transform sourceTransform;
Table sourceTable;
var referenceTableAlias = hubDatalinkTable.Key.ToString();
switch (hubDatalinkTable.SourceType)
{
case ESourceType.Datalink:
var datalink = hub.DexihDatalinks.SingleOrDefault(c => c.Key == hubDatalinkTable.SourceDatalinkKey);
if (datalink == null)
{
throw new TransformManagerException($"The source datalink with the key {hubDatalinkTable.SourceDatalinkKey} was not found");
}
(sourceTransform, sourceTable) = CreateRunPlan(hub, datalink, inputColumns, null, false, null);
break;
case ESourceType.Table:
if (hubDatalinkTable.SourceTableKey == null)
{
throw new TransformManagerException($"The source table key was null.");
}
var sourceDbTable = hub.GetTableFromKey(hubDatalinkTable.SourceTableKey.Value);
if (sourceDbTable == null)
{
throw new TransformManagerException($"The source table with the key {hubDatalinkTable.SourceTableKey.Value} could not be found.");
}
var sourceDbConnection = hub.DexihConnections.SingleOrDefault(c => c.Key == sourceDbTable.ConnectionKey && c.IsValid);
if (sourceDbConnection == null)
{
throw new TransformException($"The connection with key {sourceDbTable.ConnectionKey} could not be found.");
}
var sourceConnection = sourceDbConnection.GetConnection(_transformSettings);
sourceTable = sourceDbTable.GetTable(hub, sourceConnection, inputColumns, _transformSettings, referenceTableAlias);
if (hubDatalinkTable.DisableVersioning)
{
sourceTable.IsVersioned = false;
}
sourceTransform = sourceConnection.GetTransformReader(sourceTable, transformWriterOptions.PreviewMode);
break;
case ESourceType.Rows:
var rowCreator = new ReaderRowCreator();
rowCreator.InitializeRowCreator(hubDatalinkTable.RowsStartAt ?? 1, hubDatalinkTable.RowsEndAt ?? 1, hubDatalinkTable.RowsIncrement ?? 1);
rowCreator.TableAlias = hubDatalinkTable.Key.ToString();
sourceTable = rowCreator.GetTable();
sourceTransform = rowCreator;
break;
case ESourceType.Function:
sourceTable = hubDatalinkTable.GetTable(null, inputColumns);
var data = new object[sourceTable.Columns.Count];
for (var i = 0; i < sourceTable.Columns.Count; i++)
{
data[i] = sourceTable.Columns[i].DefaultValue;
}
sourceTable.Data.Add(data);
var defaultRow = new ReaderDynamic(sourceTable);
defaultRow.Reset();
sourceTransform = defaultRow;
break;
default:
throw new TransformManagerException($"Error getting the source transform.");
}
sourceTransform.TableAlias = referenceTableAlias;
// compare the table in the transform to the source datalink columns. If any are missing, add a mapping
// transform to include them.
var transformColumns = sourceTransform.CacheTable.Columns;
var datalinkColumns = hubDatalinkTable.DexihDatalinkColumns;
// add a mapping transform to include inputColumns.
var mappings = new Mappings();
foreach (var column in datalinkColumns.Where(c => c.DeltaType != EDeltaType.IgnoreField))
{
var transformColumn = transformColumns.SingleOrDefault(c => c.Name == column.Name);
if (transformColumn == null)
{
var newColumn = column.GetTableColumn(inputColumns);
mappings.Add(new MapInputColumn(newColumn));
}
else
{
transformColumn.DeltaType = column.DeltaType;
transformColumn.IsIncrementalUpdate = column.IsIncrementalUpdate;
}
}
if (mappings.Count > 0)
{
sourceTransform = new TransformMapping(sourceTransform, mappings)
{
Name = "Input Column Mapping"
};
}
sourceTransform.IgnoreQuery = hubDatalinkTable.DisablePushDown;
return(sourceTransform, sourceTable);
}
catch (Exception ex)
{
throw new TransformManagerException($"Get source transform failed. {ex.Message}", ex);
}
}
