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