public DatajobRun(TransformSettings transformSettings, ILogger logger, DexihDatajob datajob, DexihHub hub, TransformWriterOptions transformWriterOptions, IAlertQueue alertQueue, string[] alertEmails) { _transformSettings = transformSettings; _logger = logger; _alertQueue = alertQueue; _alertEmails = alertEmails; _transformWriterOptions = transformWriterOptions; if (datajob.AuditConnectionKey > 0) { var dbAuditConnection = hub.DexihConnections.SingleOrDefault(c => c.IsValid && c.Key == datajob.AuditConnectionKey); if (dbAuditConnection == null) { throw new DatalinkRunException( $"Audit connection with key {datajob.AuditConnectionKey} was not found."); } _auditConnection = dbAuditConnection.GetConnection(_transformSettings); } else { _auditConnection = new ConnectionMemory(); } Datajob = datajob; _hub = hub; }
public DatalinkTestRun( TransformSettings transformSettings, ILogger logger, DexihDatalinkTest datalinkTest, DexihHub hub, TransformWriterOptions transformWriterOptions, IAlertQueue alertQueue, string[] alertEmails ) { _transformSettings = transformSettings; _transformWriterOptions = transformWriterOptions; _logger = logger; // create a copy of the hub as the test run will update objects. _hub = hub.Serialize().Deserialize <DexihHub>(); // _hub = hub.CloneProperties(); _datalinkTest = datalinkTest; _alertQueue = alertQueue; _alertEmails = alertEmails; Connection auditConnection; if (datalinkTest.AuditConnectionKey > 0) { var dbAuditConnection = _hub.DexihConnections.SingleOrDefault(c => c.IsValid && c.Key == datalinkTest.AuditConnectionKey); if (dbAuditConnection == null) { throw new DatalinkRunException( $"Audit connection with key {datalinkTest.AuditConnectionKey} was not found."); } auditConnection = dbAuditConnection.GetConnection(_transformSettings); } else { auditConnection = new ConnectionMemory(); } TestResults = new List <TestResult>(); WriterResult = new TransformWriterResult() { AuditConnection = auditConnection, AuditConnectionKey = datalinkTest.AuditConnectionKey ?? 0, AuditType = Constants.DatalinkTest, HubKey = _hub.HubKey, ReferenceKey = datalinkTest.Key, ParentAuditKey = 0, ReferenceName = datalinkTest.Name, SourceTableKey = 0, SourceTableName = "", TransformWriterOptions = _transformWriterOptions }; }
private async Task <(Transform transform, string name)> GetDatalinkTransform(long key, long?datalinkTransformKey, InputColumn[] inputColumns, InputParameters inputParameters, SelectQuery selectQuery, CancellationToken cancellationToken) { var dbDatalink = Cache.Hub.DexihDatalinks.SingleOrDefault(c => c.Key == key); if (dbDatalink == null) { throw new DownloadDataException( $"The datalink with key {key} could not be found in the cache."); } dbDatalink.UpdateParameters(inputParameters); var transformWriterOptions = new TransformWriterOptions() { PreviewMode = true, }; var transformSettings = new TransformSettings() { HubVariables = TransformSettings.HubVariables, InputParameters = dbDatalink.Parameters.ToArray(), RemoteSettings = TransformSettings.RemoteSettings, ClientFactory = TransformSettings.ClientFactory }; var transformManager = new TransformsManager(transformSettings); //Get the last Transform that will load the target table. var runPlan = transformManager.CreateRunPlan(Cache.Hub, dbDatalink, inputColumns, datalinkTransformKey, null, transformWriterOptions); var transform = runPlan.sourceTransform; var openReturn = await transform.Open(0, selectQuery, cancellationToken); if (!openReturn) { throw new DownloadDataException( $"The datalink {dbDatalink.Name} failed to open for reading."); } // transform.SetCacheMethod(ECacheMethod.DemandCache); transform.SetEncryptionMethod(EEncryptionMethod.MaskSecureFields, ""); return(transform, dbDatalink.Name); }
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 (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); } }