private AggregationDistinctToken GenerateSQLToken(AggregationDistinctProjection projection)
{
ShardingAssert.ShouldBeNotNull(projection.GetAlias(), "alias is required");
String derivedAlias = DerivedColumn.IsDerivedColumnName(projection.GetAlias()) ? projection.GetAlias() : null;
return(new AggregationDistinctToken(projection.StartIndex, projection.StopIndex, projection.GetDistinctInnerExpression(), derivedAlias));
}
private void AddOutputColumn(DerivedColumn column)
{
IDTSOutputColumn100 col;
col = _component.OutputCollection[0].OutputColumnCollection.New();
col.Name = column.Name;
col.Description = column.Name;
col.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
col.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
col.SetDataTypeProperties(GetDataTypeFromString(column.Type), column.Length, column.Precision, column.Scale, column.Codepage);
col.ExternalMetadataColumnID = 0;
IDTSCustomProperty100 propExpression = col.CustomPropertyCollection.New();
propExpression.Name = "Expression";
propExpression.Value = column.Expression;
IDTSCustomProperty100 propFriendlyExpression = col.CustomPropertyCollection.New();
propFriendlyExpression.Name = "FriendlyExpression";
propFriendlyExpression.Value = column.Expression;
IDTSVirtualInput100 vi = _component.InputCollection[0].GetVirtualInput();
propExpression.Value = this.ExpressionCleanerAndInputMapBuilder(column.Expression, vi, DTSUsageType.UT_READONLY);
}
public static DerivedColumnComponentWrapper CreateComponent(DerivedColumn derivedColumn, DataFlowTaskWrapper dataFlowTaskWrapper)
{
List <string> duplicates = derivedColumn.DerivedColumns.GroupBy(c => c.ColumnName)
.Where(c => c.Count() > 1)
.Select(c => c.Key)
.Distinct()
.ToList();
if (duplicates.Count > 0)
{
string duplicateNames = string.Join(", ", duplicates);
throw new InvalidOperationException($"The following columns are duplicated in the derived column list: {duplicateNames}");
}
List <string> replaceColumns = derivedColumn.DerivedColumns.Where(c => c.Replace).Select(c => c.ColumnName).ToList();
DerivedColumnComponentWrapper derivedColumnComponentWrapper = new DerivedColumnComponentWrapper(dataFlowTaskWrapper, replaceColumns)
{
Name = derivedColumn.Name
};
dataFlowTaskWrapper.AddPath(derivedColumn.DataFlowInputPath.OutputPathName, derivedColumnComponentWrapper.Meta.InputCollection[0]);
try
{
derivedColumnComponentWrapper.GetMetadata(); // Get virtual input column metadata to the component.
}
catch (Exception e)
{
throw new Exception($"Failed to get metadata for data flow component {derivedColumn.Name}!", e);
}
foreach (DerivedColumnEntry column in derivedColumn.DerivedColumns)
{
try
{
if (column.Replace)
{
derivedColumnComponentWrapper.ReplaceColumn(column.ColumnName, column.Expression);
derivedColumnComponentWrapper.SetColumnDisposition(column.ColumnName, (RowDispositionType)column.ErrorRowDisposition, RowFailureType.Error, PathDirectionType.Input);
derivedColumnComponentWrapper.SetColumnDisposition(column.ColumnName, (RowDispositionType)column.TruncationRowDisposition, RowFailureType.Truncation, PathDirectionType.Input);
}
else
{
derivedColumnComponentWrapper.AddNewColumn(column.ColumnName, column.Expression);
derivedColumnComponentWrapper.SetColumnDisposition(column.ColumnName, (RowDispositionType)column.ErrorRowDisposition, RowFailureType.Error, PathDirectionType.Output);
derivedColumnComponentWrapper.SetColumnDisposition(column.ColumnName, (RowDispositionType)column.TruncationRowDisposition, RowFailureType.Truncation, PathDirectionType.Output);
}
}
catch (Exception e)
{
string msg = $"Could not set expression {column.Expression} on derived column {column.ColumnName}! Verify that it is a valid expression.";
e.Data.Add(Constants.ExceptionComponentMessageKey, msg);
throw;
}
}
return(derivedColumnComponentWrapper);
}
private void AppendAverageDerivedProjection(AggregationProjection averageProjection)
{
String innerExpression = averageProjection.GetInnerExpression();
String countAlias = DerivedColumn.Get(DerivedColumnEnum.AVG_COUNT_ALIAS).GetDerivedColumnAlias(aggregationAverageDerivedColumnCount);
AggregationProjection countProjection = new AggregationProjection(AggregationTypeEnum.COUNT, innerExpression, countAlias);
String sumAlias = DerivedColumn.Get(DerivedColumnEnum.AVG_SUM_ALIAS).GetDerivedColumnAlias(aggregationAverageDerivedColumnCount);
AggregationProjection sumProjection = new AggregationProjection(AggregationTypeEnum.SUM, innerExpression, sumAlias);
averageProjection.GetDerivedAggregationProjections().Add(countProjection);
averageProjection.GetDerivedAggregationProjections().Add(sumProjection);
aggregationAverageDerivedColumnCount++;
}
private AggregationDistinctProjection CreateProjection(string sql, AggregationDistinctProjectionSegment projectionSegment)
{
var innerExpression = sql.SubStringWithEndIndex(projectionSegment.GetInnerExpressionStartIndex(), projectionSegment.GetStopIndex() + 1);
var alias = projectionSegment.GetAlias() ?? DerivedColumn.Get(DerivedColumnEnum.AGGREGATION_DISTINCT_DERIVED).GetDerivedColumnAlias(aggregationDistinctDerivedColumnCount++);
AggregationDistinctProjection result = new AggregationDistinctProjection(
projectionSegment.GetStartIndex(), projectionSegment.GetStopIndex(), projectionSegment.GetAggregationType(), innerExpression, alias, projectionSegment.GetDistinctExpression());
if (AggregationTypeEnum.AVG == result.GetAggregationType())
{
AppendAverageDistinctDerivedProjection(result);
}
return(result);
}
private void AppendAverageDistinctDerivedProjection(AggregationDistinctProjection averageDistinctProjection)
{
var innerExpression = averageDistinctProjection.GetInnerExpression();
var distinctInnerExpression = averageDistinctProjection.GetDistinctInnerExpression();
var countAlias = DerivedColumn.Get(DerivedColumnEnum.AVG_COUNT_ALIAS).GetDerivedColumnAlias(aggregationAverageDerivedColumnCount);
AggregationDistinctProjection countDistinctProjection = new AggregationDistinctProjection(0, 0, AggregationTypeEnum.COUNT, innerExpression, countAlias, distinctInnerExpression);
var sumAlias = DerivedColumn.Get(DerivedColumnEnum.AVG_SUM_ALIAS).GetDerivedColumnAlias(aggregationAverageDerivedColumnCount);
AggregationDistinctProjection sumDistinctProjection = new AggregationDistinctProjection(0, 0, AggregationTypeEnum.SUM, innerExpression, sumAlias, distinctInnerExpression);
averageDistinctProjection.GetDerivedAggregationProjections().Add(countDistinctProjection);
averageDistinctProjection.GetDerivedAggregationProjections().Add(sumDistinctProjection);
aggregationAverageDerivedColumnCount++;
}
private void ReplaceExistingColumn(DerivedColumn column)
{
if (column.ReplaceExisting)
{
IDTSVirtualInput100 vi = _component.InputCollection[0].GetVirtualInput();
IDTSInputColumn100 col;
SetInputUsageType(vi, vi.VirtualInputColumnCollection[column.Name], DTSUsageType.UT_READWRITE);
col = _component.InputCollection[0].InputColumnCollection[column.Name];
col.CustomPropertyCollection["Expression"].Value = this.ExpressionCleanerAndInputMapBuilder(column.Expression, vi, DTSUsageType.UT_READONLY);
col.CustomPropertyCollection["FriendlyExpression"].Value = column.Expression;
}
}
private ICollection <IProjection> GetDerivedOrderColumns(ICollection <IProjection> projections, ICollection <OrderByItem> orderItems, DerivedColumn derivedColumn, SelectCommand selectCommand)
{
if (orderItems.IsEmpty())
{
return(new LinkedList <IProjection>());
}
ICollection <IProjection> result = new LinkedList <IProjection>();
int derivedColumnOffset = 0;
foreach (var orderItem in orderItems)
{
if (!ContainsProjection(projections, orderItem.GetSegment(), selectCommand))
{
result.Add(new DerivedProjection(((TextOrderByItemSegment)orderItem.GetSegment()).GetText(), derivedColumn.GetDerivedColumnAlias(derivedColumnOffset++)));
}
}
return(result);
}
private ICollection <IProjection> GetDerivedOrderByColumns(ICollection <IProjection> projections, OrderByContext orderByContext, SelectCommand selectCommand)
{
return(GetDerivedOrderColumns(projections, orderByContext.GetItems(), DerivedColumn.Get(DerivedColumnEnum.ORDER_BY_ALIAS), selectCommand));
}
private ICollection <IProjection> GetDerivedGroupByColumns(ICollection <IProjection> projections, GroupByContext groupByContext, SelectCommand selectCommand)
{
return(GetDerivedOrderColumns(projections, groupByContext.GetItems(), DerivedColumn.Get(DerivedColumnEnum.GROUP_BY_ALIAS), selectCommand));
}
