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