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