[InlineData(100000)] //should run in ~ 250ms public async Task FilterPerformanceFilterAll(int rows) { var data = Helpers.CreateLargeTable(rows); var transformFilter = new TransformFilter(); var filters = new List <TransformFunction>(); var newFilter = new TransformFunction( new Func <int, bool>((value) => value < 0), new TableColumn[] { new TableColumn(data.GetName(0)) }, null, null); filters.Add(newFilter); transformFilter.Functions = filters; transformFilter.SetInTransform(data); var count = 0; while (await transformFilter.ReadAsync()) { count++; } Assert.Equal(0, count); _output.WriteLine(transformFilter.PerformanceSummary()); }
[InlineData(100000)] //should run in ~ 250ms public async Task FilterPerformanceEmpty(int rows) { var data = Helpers.CreateLargeTable(rows); var transformFilter = new TransformFilter(); transformFilter.SetInTransform(data); var count = 0; while (await transformFilter.ReadAsync()) { count++; } Assert.Equal(rows, count); _output.WriteLine(transformFilter.PerformanceSummary()); }
public async Task Filters() { var table = Helpers.CreateSortedTestData(); //set a filter that filters all var conditions = new List <TransformFunction>(); var function = Functions.GetFunction("dexih.functions.BuiltIn.ConditionFunctions", "IsEqual").GetTransformFunction(); function.Inputs = new dexih.functions.Parameter[] { new dexih.functions.Parameter("StringColumn", ETypeCode.String, true, null, new TableColumn("StringColumn"), isArray: true), new dexih.functions.Parameter("Compare", ETypeCode.String, false, "junk", isArray: true) }; conditions.Add(function); var transformFilter = new TransformFilter(table, conditions, null); await transformFilter.Open(0, null, CancellationToken.None); Assert.Equal(5, transformFilter.FieldCount); var count = 0; while (await transformFilter.ReadAsync()) { count = count + 1; } Assert.Equal(0, count); //set a filter than filters to 1 row. conditions = new List <TransformFunction>(); function = Functions.GetFunction("dexih.functions.BuiltIn.ConditionFunctions", "IsEqual").GetTransformFunction(); function.Inputs = new dexih.functions.Parameter[] { new dexih.functions.Parameter("StringColumn", ETypeCode.String, true, null, new TableColumn("StringColumn"), isArray: true), new dexih.functions.Parameter("Compare", ETypeCode.String, false, "value03", isArray: true) }; conditions.Add(function); transformFilter.Conditions = conditions; transformFilter.Reset(); count = 0; while (await transformFilter.ReadAsync() == true) { count = count + 1; if (count == 1) { Assert.Equal(3, transformFilter["IntColumn"]); } } Assert.Equal(1, count); // use the "IN" function to filter 3 rows. conditions = new List <TransformFunction>(); function = Functions.GetFunction("dexih.functions.BuiltIn.ConditionFunctions", "IsIn").GetTransformFunction(); function.Inputs = new dexih.functions.Parameter[] { new dexih.functions.Parameter("Value", ETypeCode.String, true, null, new TableColumn("StringColumn")), new dexih.functions.Parameter("CompareTo", ETypeCode.String, false, "value03", isArray: true), new dexih.functions.Parameter("CompareTo", ETypeCode.String, false, "value05", isArray: true), new dexih.functions.Parameter("CompareTo", ETypeCode.String, false, "value07", isArray: true) }; conditions.Add(function); transformFilter.Conditions = conditions; table.Reset(); transformFilter.SetInTransform(table); count = 0; while (await transformFilter.ReadAsync() == true) { count = count + 1; } Assert.Equal(3, count); // create a mapping, and use the filter after the calculation. var mappings = new List <TransformFunction>(); function = Functions.GetFunction("dexih.functions.BuiltIn.MapFunctions", "Substring").GetTransformFunction(); function.TargetColumn = new TableColumn("Substring"); function.Inputs = new dexih.functions.Parameter[] { new dexih.functions.Parameter("name", ETypeCode.String, true, null, new TableColumn("StringColumn")), new dexih.functions.Parameter("start", ETypeCode.Int32, false, 5), new dexih.functions.Parameter("end", ETypeCode.Int32, false, 50) }; mappings.Add(function); table.Reset(); var transformMapping = new TransformMapping(table, false, null, mappings); conditions = new List <TransformFunction>(); function = Functions.GetFunction("dexih.functions.BuiltIn.ConditionFunctions", "LessThan").GetTransformFunction(); function.Inputs = new dexih.functions.Parameter[] { new dexih.functions.Parameter("Substring", ETypeCode.Int32, true, null, new TableColumn("Substring")), new dexih.functions.Parameter("Compare", ETypeCode.Int32, false, 5) }; conditions.Add(function); transformFilter.Conditions = conditions; transformFilter.SetInTransform(transformMapping); count = 0; while (await transformFilter.ReadAsync() == true) { count = count + 1; } Assert.Equal(4, count); }
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); } }