public void StartDatalink(DatalinkRun datalinkRun)
{
// datalinkRun.OnStatusUpdate += DatalinkStatus;
// datalinkRun.OnFinish += DatalinkStatus;
//raise an event to have the datalink started.
if (OnDatalinkStart != null)
{
datalinkRun.ActivateDatalink();
OnDatalinkStart.Invoke(datalinkRun);
}
else
{
throw new DatajobRunException($"Could not start the datalink {datalinkRun.Datalink.Name} due to a missing start event.");
}
}
/// <summary>
/// Runs the datalink test and returns the results
/// </summary>
/// <param name="cancellationToken"></param>
/// <returns></returns>
/// <exception cref="DatalinkTestRunException"></exception>
public async Task <List <TestResult> > Run(CancellationToken cancellationToken)
{
try
{
var ct = CancellationTokenSource.CreateLinkedTokenSource(_cancellationTokenSource.Token,
cancellationToken);
var token = ct.Token;
token.ThrowIfCancellationRequested();
var tempTargetTableKey = -10000;
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Started, null, null);
var passed = 0;
var failed = 0;
foreach (var step in _datalinkTest.DexihDatalinkTestSteps.OrderBy(c => c.Position).Where(c => c.IsValid))
{
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.");
}
UpdateProgress(1);
// prepare all the relevant tables
foreach (var testTable in step.DexihDatalinkTestTables)
{
await PrepareTestTable(testTable, token);
}
UpdateProgress(2);
datalink.AuditConnectionKey = _datalinkTest.AuditConnectionKey;
var dexihTargetConnection = _hub.DexihConnections.Single(c => c.IsValid && c.Key == step.TargetConnectionKey);
ICollection <DexihTable> targetTables;
// add a target table to store the data when the datalink doesn't have one.
if (!datalink.DexihDatalinkTargets.Any())
{
var target = new DexihDatalinkTarget()
{
TableKey = tempTargetTableKey--,
};
datalink.DexihDatalinkTargets.Add(target);
datalink.UpdateStrategy = EUpdateStrategy.Reload;
datalink.LoadStrategy = TransformWriterTarget.ETransformWriterMethod.Bulk;
// var targetTable = datalink.GetOutputTable();
var table = new DexihTable()
{
Key = target.TableKey,
DexihTableColumns = datalink.GetOutputTable().DexihDatalinkColumns
.Select(c => c.CloneProperties <DexihTableColumn>()).ToArray()
};
// dexihTargetConnection.DexihTables.Add(table);
targetTables = new List <DexihTable> {
table
};
}
else
{
targetTables = datalink.DexihDatalinkTargets.Select(c => _hub.GetTableFromKey(c.TableKey)).ToList();
}
if (targetTables.Count > 1)
{
throw new DatalinkTestRunException("Currently datalink tests can only be used with datalinks containing no more than one target table.");
}
foreach (var table in targetTables)
{
table.ConnectionKey = dexihTargetConnection.Key;
table.Name = step.TargetTableName;
table.Schema = step.TargetSchema;
}
UpdateProgress(50);
// run the datalink
var datalinkRun = new DatalinkRun(_transformSettings, _logger, WriterResult.AuditKey, datalink, _hub, null, _transformWriterOptions, _alertQueue, _alertEmails);
datalinkRun.WriterTarget.WriterResult.AuditType = "DatalinkTestStep";
datalinkRun.WriterTarget.WriterResult.ReferenceKey = step.Key;
// await datalinkRun.Initialize(cancellationToken);
await datalinkRun.Build(token);
await datalinkRun.Run(token);
UpdateProgress(70);
foreach (var table in targetTables)
{
var testResult = new TestResult()
{
Name = step.Name,
StartDate = DateTime.Now,
TestStepKey = step.Key
};
var dexihExpectedConnection = _hub.DexihConnections.Single(c => c.IsValid && c.Key == step.ExpectedConnectionKey);
var dexihExpectedTable = table.CloneProperties();
dexihExpectedTable.ConnectionKey = dexihExpectedConnection.Key;
dexihExpectedTable.Name = step.ExpectedTableName;
dexihExpectedTable.Schema = step.ExpectedSchema;
var expectedConnection = dexihExpectedConnection.GetConnection(_transformSettings);
var expectedTable = dexihExpectedTable.GetTable(_hub, expectedConnection, _transformSettings);
var expectedTransform = expectedConnection.GetTransformReader(expectedTable);
var targetConnection = dexihTargetConnection.GetConnection(_transformSettings);
var targetTable = table.GetTable(_hub, targetConnection, _transformSettings);
var targetTransform = targetConnection.GetTransformReader(targetTable);
// the error table is used to store any rows which do not match.
var dexihErrorConnection = _hub.DexihConnections.SingleOrDefault(c => c.IsValid && c.Key == step.ErrorConnectionKey);
Connection errorConnection = null;
Table errorTable = null;
if (dexihErrorConnection != null)
{
var dexihErrorTable = table.CloneProperties();
dexihErrorTable.ConnectionKey = dexihErrorConnection.Key;
dexihErrorTable.Name = step.ErrorTableName;
dexihErrorTable.Schema = step.ErrorSchema;
errorConnection = dexihErrorConnection.GetConnection(_transformSettings);
errorTable = dexihErrorTable.GetTable(_hub, errorConnection, _transformSettings);
foreach (var column in errorTable.Columns)
{
column.DeltaType = EDeltaType.NonTrackingField;
}
errorTable.Columns.Add(new TableColumn("error_audit_key", ETypeCode.Int64,
EDeltaType.CreateAuditKey));
errorTable.Columns.Add(new TableColumn("error_operation", ETypeCode.CharArray,
EDeltaType.DatabaseOperation)
{
MaxLength = 1
});
errorTable.Columns.Add(new TableColumn("mismatch_reason", ETypeCode.String,
EDeltaType.UpdateReason)
{
AllowDbNull = true
});
}
// use the delta transform to compare expected and target tables.
await using var delta = new TransformDelta(targetTransform, expectedTransform,
EUpdateStrategy.AppendUpdateDelete, 0, false, true);
await delta.Open(0, null, token);
testResult.RowsMismatching = 0;
testResult.RowsMissingFromSource = 0;
testResult.RowsMissingFromTarget = 0;
var operationColumn = delta.CacheTable.Columns.GetOrdinal(EDeltaType.DatabaseOperation);
var updateReasonColumn = delta.CacheTable.Columns.GetOrdinal(EDeltaType.UpdateReason);
var errorCache = new TableCache();
// loop through the delta. any rows which don't match on source/target should filter through, others will be ignored.
while (await delta.ReadAsync(token))
{
testResult.TestPassed = false;
switch (delta[operationColumn])
{
case 'C':
testResult.RowsMissingFromTarget++;
break;
case 'U':
testResult.RowsMismatching++;
break;
case 'D':
testResult.RowsMissingFromSource++;
break;
}
datalinkRun.WriterTarget.WriterResult.Failed++;
WriterResult.Failed++;
WriterResult.IncrementRowsCreated();
if (errorTable != null && errorCache.Count < MaxErrorRows)
{
var row = new object[errorTable.Columns.Count];
for (var i = 0; i < errorTable.Columns.Count; i++)
{
var column = errorTable[i];
switch (column.DeltaType)
{
case EDeltaType.CreateAuditKey:
row[i] = datalinkRun.WriterTarget.WriterResult.AuditKey;
break;
case EDeltaType.DatabaseOperation:
row[i] = delta[operationColumn];
break;
case EDeltaType.UpdateReason:
row[i] = delta[updateReasonColumn];
break;
default:
row[i] = delta[column.Name];
break;
}
}
errorCache.Add(row);
}
}
if (errorCache.Count > 0)
{
errorTable.Data = errorCache;
var createReader = new ReaderMemory(errorTable);
if (!await errorConnection.TableExists(errorTable, cancellationToken))
{
await errorConnection.CreateTable(errorTable, false, cancellationToken);
}
await errorConnection.ExecuteInsertBulk(errorTable, createReader, cancellationToken);
}
WriterResult.RowsIgnored += delta.TotalRowsIgnored;
WriterResult.RowsPreserved += delta.TotalRowsPreserved;
if (testResult.TestPassed == false)
{
failed++;
}
else
{
passed++;
}
if (datalinkRun.WriterTarget.WriterResult.RunStatus == TransformWriterResult.ERunStatus.Finished)
{
if (testResult.TestPassed)
{
datalinkRun.WriterTarget.WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Passed, "Datalink test passed", null);
}
else
{
datalinkRun.WriterTarget.WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Failed, $"Datalink test failed, {testResult.RowsMissingFromSource} rows missing from expected, {testResult.RowsMissingFromTarget} rows missing from actual, {testResult.RowsMismatching} rows with mismatching columns.", null);
}
}
TestResults.Add(testResult);
}
}
if (WriterResult.Failed > 0)
{
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Failed, $"{passed} tests passed, {failed} test failed.", null);
}
else
{
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Passed, $"{passed} tests passed.", null);
}
await WriterResult.CompleteDatabaseWrites();
return(TestResults);
}
catch (Exception ex)
{
WriterResult.SetRunStatus(TransformWriterResult.ERunStatus.Abended, ex.Message, ex);
return(TestResults);
}
}
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);
}
}
