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); } }