public async Task <RepositoryManager> CreateRepositoryWithSourceTable()
{
var database = await CreateRepositoryWithConnections();
var dbConnection = await database.DbContext.DexihConnections.FirstAsync(c => c.Name == "source" && c.IsValid);
var table = new DexihTable()
{
Name = "table1",
Description = "table description",
BaseTableName = "table1",
DexihTableColumns = new[] {
new DexihTableColumn()
{
Name = "key1", LogicalName = "key1", DataType = ETypeCode.String, DeltaType = EDeltaType.NaturalKey
},
new DexihTableColumn()
{
Name = "natural key", LogicalName = "natural key", DataType = ETypeCode.Int32, DeltaType = EDeltaType.TrackingField
},
new DexihTableColumn()
{
Name = "ignore", LogicalName = "ignore", DataType = ETypeCode.Int32, DeltaType = EDeltaType.IgnoreField
}
},
ConnectionKey = dbConnection.Key,
HubKey = dbConnection.HubKey
};
await database.SaveTables(dbConnection.HubKey, new[] { table }, true, false, CancellationToken.None);
return(database);
}
public async Task NewTable()
{
//test 1, create initial table
using (var database = await CreateRepositoryWithSourceTable())
{
var dbConnection = await database.DbContext.DexihConnections.FirstAsync(c => c.Name == "source" && c.IsValid);
// check table is created.
var dbTable = await database.DbContext.DexihTables.Include(d => d.DexihTableColumns).SingleOrDefaultAsync(c => c.ConnectionKey == dbConnection.Key);
Assert.NotNull(dbTable);
Assert.Equal("table1", dbTable.Name);
Assert.Equal(3, dbTable.DexihTableColumns.Count());
// update the table, and check update has succeeded.
dbTable.Name = "updated table";
var saveResult = await database.SaveTables(dbConnection.HubKey, new[] { dbTable }, true, false, CancellationToken.None);
Assert.NotNull(saveResult);
var dbTable2 = await database.DbContext.DexihTables.Include(d => d.DexihTableColumns).SingleOrDefaultAsync(c => c.Key == dbTable.Key && c.IsValid);
Assert.Equal("updated table", dbTable2.Name);
// create a new table with the same name. save should fail
var duplicateTable = new DexihTable()
{
Name = "updated table",
ConnectionKey = dbConnection.Key,
BaseTableName = "updated table",
HubKey = dbConnection.HubKey
};
Assert.Throws <AggregateException>(() => database.SaveTables(dbConnection.HubKey, new[] { duplicateTable }, true, false, CancellationToken.None).Result);
// delete the table.
var deleteReturn = await database.DeleteTables(dbConnection.HubKey, new[] { dbTable.Key }, CancellationToken.None);
Assert.NotNull(deleteReturn);
// check the table is deleted
dbTable = await database.DbContext.DexihTables.SingleOrDefaultAsync(c => c.Key == dbTable2.Key && c.IsValid);
Assert.Null(dbTable);
// create a new table with the same name as the deleted one. save should succeed
var saveResult2 = await database.SaveTables(dbConnection.HubKey, new[] { duplicateTable }, true, false, CancellationToken.None);
Assert.NotNull(saveResult2);
}
}
public async Task Save_Connection_and_Tables()
{
var hub = await CreateHub();
// create initial connection
var connection = new DexihConnection()
{
Name = "connection",
Description = "description",
ConnectionAssemblyName = "assembly",
Server = "server",
Password = "******",
};
await _repositoryManager.SaveConnection(hub.HubKey, connection, CancellationToken.None);
Assert.True(connection.Key > 0);
// add table to the connection
var table = new DexihTable()
{
Name = "table",
Description = "description",
ConnectionKey = connection.Key,
DexihTableColumns = new List <DexihTableColumn>()
{
new DexihTableColumn()
{
Name = "column1", DataType = ETypeCode.Int32, DeltaType = EDeltaType.TrackingField
},
new DexihTableColumn()
{
Name = "column2", DataType = ETypeCode.Node, DeltaType = EDeltaType.TrackingField,
ChildColumns = new List <DexihTableColumn> ()
{
new DexihTableColumn()
{
Name = "childColumn1", DataType = ETypeCode.Int32, DeltaType = EDeltaType.TrackingField
},
new DexihTableColumn()
{
Name = "childColumn2", DataType = ETypeCode.Node, DeltaType = EDeltaType.TrackingField,
ChildColumns = new List <DexihTableColumn> ()
{
new DexihTableColumn()
{
Name = "grandChildColumn1", DataType = ETypeCode.Int32, DeltaType = EDeltaType.TrackingField
},
new DexihTableColumn()
{
Name = "grandChildColumn2", DataType = ETypeCode.Int32, DeltaType = EDeltaType.TrackingField
},
}
},
}
},
}
};
var savedTable = await _repositoryManager.SaveTable(hub.HubKey, table, true, false, CancellationToken.None);
Assert.True(savedTable.Key > 0);
// retrieve the connection and the table
var retrievedConnection = await _repositoryManager.GetConnection(hub.HubKey, connection.Key, true, CancellationToken.None);
Assert.Equal(connection.Key, retrievedConnection.Key);
Assert.Equal(connection.ConnectionAssemblyName, retrievedConnection.ConnectionAssemblyName);
Assert.Equal(connection.Server, retrievedConnection.Server);
Assert.Equal(connection, retrievedConnection);
var retrievedTable = await _repositoryManager.GetTable(hub.HubKey, savedTable.Key, true, CancellationToken.None);
retrievedTable = retrievedTable.CloneProperties <DexihTable>(); //clone the table to avoid conflicts with MemoryDatabase.
var columns = retrievedTable.DexihTableColumns.ToArray();
Assert.Equal(table.Name, retrievedTable.Name);
Assert.Equal(table.Description, retrievedTable.Description);
Assert.Equal(2, table.DexihTableColumns.Count);
Assert.Equal("column1", columns[0].Name);
Assert.Equal("column2", columns[1].Name);
var childColumns = columns[1].ChildColumns.ToArray();
Assert.Equal(2, childColumns.Length);
Assert.Equal(2, childColumns[1].ChildColumns.Count);
// update the table name
retrievedTable.Name = "table updated";
// update a column
columns[0].Name = "column1 updated";
// delete a column
retrievedTable.DexihTableColumns.Remove(columns[1]);
// add a column
retrievedTable.DexihTableColumns.Add(new DexihTableColumn()
{
Name = "column3", DataType = ETypeCode.Int32, DeltaType = EDeltaType.TrackingField
});
var savedTable2 = await _repositoryManager.SaveTable(hub.HubKey, retrievedTable, true, false, CancellationToken.None);
var columns2 = savedTable2.DexihTableColumns.Where(c => c.IsValid).ToArray();
var retrievedTable2 = await _repositoryManager.GetTable(hub.HubKey, savedTable2.Key, true, CancellationToken.None);
Assert.Equal(savedTable2.Name, retrievedTable2.Name);
Assert.Equal(2, columns2.Length);
Assert.Equal("column1 updated", columns2[0].Name);
Assert.Equal("column3", columns2[1].Name);
// delete the table
await _repositoryManager.DeleteTables(hub.HubKey, new[] { savedTable.Key }, CancellationToken.None);
// confirm table deleted
await Assert.ThrowsAsync <RepositoryManagerException>(async() => await _repositoryManager.GetTable(hub.HubKey, savedTable.Key, true, CancellationToken.None));
// retrieve the connection with tables, and confirm table not retrieved.
retrievedConnection = await _repositoryManager.GetConnection(hub.HubKey, connection.Key, true, CancellationToken.None);
Assert.Equal(0, hub.DexihTables.Count(c => c.IsValid));
}
/// <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);
}
}
/// <summary>
/// Converts a table to DexihTable. If an originalTable is included, TableKeys and ColumnKeys will be preserved where possible.
/// </summary>
/// <param name="table"></param>
/// <param name="originalTable"></param>
/// <returns></returns>
public DexihTable GetDexihTable(Table table, DexihTable originalTable = null)
{
if (table == null)
{
return(null);
}
var dbTable = originalTable ?? new DexihTable();
foreach (var dbColumn in dbTable.DexihTableColumns)
{
dbColumn.IsValid = false;
}
table.CopyProperties(dbTable, true);
dbTable.IsValid = true;
if (table is FlatFile flatFile)
{
dbTable.FileRootPath = flatFile.FileRootPath;
dbTable.FileIncomingPath = flatFile.FileIncomingPath;
dbTable.FileProcessedPath = flatFile.FileProcessedPath;
dbTable.FileRejectedPath = flatFile.FileRejectedPath;
dbTable.FileMatchPattern = flatFile.FileMatchPattern;
dbTable.UseCustomFilePaths = flatFile.UseCustomFilePaths;
dbTable.FileSample = flatFile.FileSample;
}
var key = -1;
if (table is WebService restFunction)
{
dbTable.RestfulUri = restFunction.RestfulUri;
dbTable.RowPath = restFunction.RowPath;
}
if (originalTable != null)
{
var position = 1;
foreach (var column in table.Columns)
{
var dbColumn = originalTable.DexihTableColumns.SingleOrDefault(c => c.Name == column.Name);
if (dbColumn == null)
{
dbColumn = new DexihTableColumn {
Key = key--
};
originalTable.DexihTableColumns.Add(dbColumn);
}
column.CopyProperties(dbColumn);
dbColumn.Position = position;
dbColumn.IsValid = true;
position++;
}
//remove columns that do not exist in the source anymore
foreach (var dbColumn in originalTable.DexihTableColumns.ToList())
{
if (table.Columns.All(c => c.Name != dbColumn.Name))
{
originalTable.DexihTableColumns.Remove(dbColumn);
}
}
}
else
{
var position = 1;
foreach (var column in table.Columns)
{
var dbColumn = new DexihTableColumn {
Key = key--
};
dbTable.DexihTableColumns.Add(dbColumn);
column.CopyProperties(dbColumn);
dbColumn.Position = position;
dbColumn.IsValid = true;
position++;
}
}
return(dbTable);
}
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);
}
}
