protected QuerySyntaxHelper(ITypeTranslater translater, IAggregateHelper aggregateHelper, IUpdateHelper updateHelper, DatabaseType databaseType) { TypeTranslater = translater; AggregateHelper = aggregateHelper; UpdateHelper = updateHelper; DatabaseType = databaseType; }
private void GetGroupBySQL(List <CustomLine> queryLines, IAggregateHelper aggregateHelper) { //now are there columns that... if (SelectColumns.Count(col => !(col.IColumn is AggregateCountColumn) //are not count(*) style columns && !_skipGroupByForThese.Contains(col.IColumn)) > 0) //and are not being skipped for GROUP BY { //yes there are! better group by then! queryLines.Add(new CustomLine("group by ", QueryComponent.GroupBy)); foreach (var col in SelectColumns) { if (col.IColumn is AggregateCountColumn) { continue; } //was added with skip for group by enabled if (_skipGroupByForThese.Contains(col.IColumn)) { continue; } string select; string alias; _syntaxHelper.SplitLineIntoSelectSQLAndAlias(col.GetSelectSQL(null, null, _syntaxHelper), out select, out alias); var line = new CustomLine(select + ",", QueryComponent.GroupBy); FlagLineBasedOnIcolumn(line, col.IColumn); queryLines.Add(line); } //clear trailing last comma queryLines.Last().Text = queryLines.Last().Text.TrimEnd('\n', '\r', ','); queryLines.Add(new CustomLine(GetHavingSql(), QueryComponent.Having)); CompileCustomLinesInStageAndAddToList(QueryComponent.GroupBy, queryLines); //order by only if we are not pivotting if (!DoNotWriteOutOrderBy) { queryLines.Add(new CustomLine("order by ", QueryComponent.OrderBy)); //if theres a top X (with an explicit order by) if (AggregateTopX != null) { queryLines.Add(new CustomLine(GetOrderBySQL(AggregateTopX), QueryComponent.OrderBy) { Role = CustomLineRole.TopX }); } else { foreach (var col in SelectColumns) { if (col.IColumn is AggregateCountColumn) { continue; } //was added with skip for group by enabled if (_skipGroupByForThese.Contains(col.IColumn)) { continue; } string select; string alias; _syntaxHelper.SplitLineIntoSelectSQLAndAlias(col.GetSelectSQL(null, null, _syntaxHelper), out select, out alias); var line = new CustomLine(select + ",", QueryComponent.OrderBy); FlagLineBasedOnIcolumn(line, col.IColumn); queryLines.Add(line); } } queryLines.Last().Text = queryLines.Last().Text.TrimEnd(','); } } else { queryLines.Add(new CustomLine(GetHavingSql(), QueryComponent.GroupBy)); } queryLines.Last().Text = queryLines.Last().Text.TrimEnd('\n', '\r', ','); CompileCustomLinesInStageAndAddToList(QueryComponent.Postfix, queryLines); }
/// <summary> /// Populates _sql (SQL property) and resolves all parameters, filters containers etc. Basically Finalizes this query builder /// </summary> public void RegenerateSQL() { SelectColumns.Sort(); //things we discover below, set them all to default values again _pivotDimension = null; _axisAppliesToDimension = null; _axis = null; _isCohortIdentificationAggregate = false; ParameterManager.ClearNonGlobals(); if (_queryLevelParameterProvider != null) { ParameterManager.AddParametersFor(_queryLevelParameterProvider, ParameterLevel.QueryLevel); } TableInfo primary; TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out primary, _forceJoinsToTheseTables); var tables = _forceJoinsToTheseTables != null ? TablesUsedInQuery.Union(_forceJoinsToTheseTables).ToList() : TablesUsedInQuery; if (!tables.Any()) { throw new QueryBuildingException("No tables could be identified for the query. Try adding a column or a force join"); } //get the database language syntax based on the tables used in the query _syntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(tables); //tell the count column what language it is if (_countColumn != null) { _isCohortIdentificationAggregate = _aggregateConfigurationIfAny != null && _aggregateConfigurationIfAny.IsCohortIdentificationAggregate; //if it is not a cic aggregate then make sure it has an alias e.g. count(*) AS MyCount. cic aggregates take extreme liberties with this field like passing in 'distinct chi' and '*' and other wacky stuff that is so not cool _countColumn.SetQuerySyntaxHelper(_syntaxHelper, !_isCohortIdentificationAggregate); } IAggregateHelper aggregateHelper = _syntaxHelper.AggregateHelper; if (_pivotID != -1) { try { _pivotDimension = SelectColumns.Single( qtc => qtc.IColumn is AggregateDimension && ((AggregateDimension)qtc.IColumn).ID == _pivotID); } catch (Exception e) { throw new QueryBuildingException("Problem occurred when trying to find PivotDimension ID " + _pivotID + " in SelectColumns list", e); } } foreach (AggregateDimension dimension in SelectColumns.Select(c => c.IColumn).Where(e => e is AggregateDimension)) { var availableAxis = dimension.AggregateContinuousDateAxis; if (availableAxis != null) { if (_axis != null) { throw new QueryBuildingException( "Multiple dimensions have an AggregateContinuousDateAxis within the same configuration (Dimensions " + _axisAppliesToDimension.GetRuntimeName() + " and " + dimension.GetRuntimeName() + ")"); } else { _axis = availableAxis; _axisAppliesToDimension = dimension; } } } if (_pivotDimension != null) { if (_pivotDimension.IColumn == _axisAppliesToDimension) { throw new QueryBuildingException("Column " + _pivotDimension.IColumn + " is both a PIVOT and has an AXIS configured on it, you cannot have both."); } } //work out all the filters Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer); //tell the manager about them ParameterManager.AddParametersFor(Filters); if (AggregateTopX != null) { SqlQueryBuilderHelper.HandleTopX(this, _syntaxHelper, AggregateTopX.TopX); } else { SqlQueryBuilderHelper.ClearTopX(this); } //if user wants to force join to some other tables that don't appear in the SELECT list, who are we to stop him! if (_forceJoinsToTheseTables != null) { foreach (TableInfo t in _forceJoinsToTheseTables) { if (!TablesUsedInQuery.Contains(t)) { TablesUsedInQuery.Add(t); ParameterManager.AddParametersFor(t); } //if user has force joined to a primary extraction table if (t.IsPrimaryExtractionTable) { if (primary == null) //we don't currently know the primary (i.e. none of the SELECT columns were from primary tables so use this table as primary) { primary = t; } else if (primary.ID == t.ID) //we know the primary already but it is the same table so thats fine { continue; } else { //this isn't fine throw new QueryBuildingException("You chose to FORCE a join to table " + t + " which is marked IsPrimaryExtractionTable but you have also selected a column called " + primary + " which is also an IsPrimaryExtractionTable (cannot have 2 different primary extraction tables)"); } } } } this.PrimaryExtractionTable = primary; SqlQueryBuilderHelper.FindLookups(this); JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this); var queryLines = new List <CustomLine>(); _sql = ""; ValidateDimensions(); //assuming we were not told to ignore the writing out of parameters! if (!DoNotWriteOutParameters) { foreach (ISqlParameter parameter in ParameterManager.GetFinalResolvedParametersList()) { queryLines.Add(new CustomLine(QueryBuilder.GetParameterDeclarationSQL(parameter), QueryComponent.VariableDeclaration)); } } CompileCustomLinesInStageAndAddToList(QueryComponent.VariableDeclaration, queryLines); //put the name in as SQL comments followed by the SQL e.g. the name of an AggregateConfiguration or whatever GetSelectSQL(queryLines); queryLines.Add(new CustomLine(SqlQueryBuilderHelper.GetFROMSQL(this), QueryComponent.FROM)); CompileCustomLinesInStageAndAddToList(QueryComponent.JoinInfoJoin, queryLines); queryLines.Add(new CustomLine(SqlQueryBuilderHelper.GetWHERESQL(this), QueryComponent.WHERE)); CompileCustomLinesInStageAndAddToList(QueryComponent.WHERE, queryLines); GetGroupBySQL(queryLines, aggregateHelper); queryLines = queryLines.Where(l => !string.IsNullOrWhiteSpace(l.Text)).ToList(); _sql = aggregateHelper.BuildAggregate(queryLines, _axis); }