/// <summary>
/// Updates .SQL Property, note that this is automatically called when you query .SQL anyway so you do not need to manually call it.
/// </summary>
public void RegenerateSQL()
{
var checkNotifier = new ThrowImmediatelyCheckNotifier();
_sql = "";
//reset the Parameter knowledge
ParameterManager.ClearNonGlobals();
#region Setup to output the query, where we figure out all the joins etc
//reset everything
SelectColumns.Sort();
//work out all the filters
Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);
TableInfo primary;
TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out primary, _forceJoinsToTheseTables);
//force join to any TableInfos that would not be normally joined to but the user wants to anyway e.g. if theres WHERE sql that references them but no columns
if (_forceJoinsToTheseTables != null)
{
foreach (var force in _forceJoinsToTheseTables)
{
if (!TablesUsedInQuery.Contains(force))
{
TablesUsedInQuery.Add(force);
}
}
}
this.PrimaryExtractionTable = primary;
SqlQueryBuilderHelper.FindLookups(this);
JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);
//deal with case when there are no tables in the query or there are only lookup descriptions in the query
if (TablesUsedInQuery.Count == 0)
{
throw new Exception("There are no TablesUsedInQuery in this dataset");
}
_syntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(TablesUsedInQuery);
if (TopX != -1)
{
SqlQueryBuilderHelper.HandleTopX(this, _syntaxHelper, TopX);
}
else
{
SqlQueryBuilderHelper.ClearTopX(this);
}
//declare parameters
ParameterManager.AddParametersFor(Filters);
#endregion
/////////////////////////////////////////////Assemble Query///////////////////////////////
#region Preamble (including variable declarations/initializations)
//assemble the query - never use Environment.Newline, use TakeNewLine() so that QueryBuilder knows what line its got up to
string toReturn = "";
foreach (ISqlParameter parameter in ParameterManager.GetFinalResolvedParametersList())
{
//if the parameter is one that needs to be told what the query syntax helper is e.g. if it's a global parameter designed to work on multiple datasets
var needsToldTheSyntaxHelper = parameter as IInjectKnown <IQuerySyntaxHelper>;
if (needsToldTheSyntaxHelper != null)
{
needsToldTheSyntaxHelper.InjectKnown(_syntaxHelper);
}
if (CheckSyntax)
{
parameter.Check(checkNotifier);
}
toReturn += GetParameterDeclarationSQL(parameter);
}
//add user custom Parameter lines
toReturn = AppendCustomLines(toReturn, QueryComponent.VariableDeclaration);
#endregion
#region Select (including all IColumns)
toReturn += Environment.NewLine;
toReturn += "SELECT " + LimitationSQL + Environment.NewLine;
toReturn = AppendCustomLines(toReturn, QueryComponent.SELECT);
toReturn += Environment.NewLine;
toReturn = AppendCustomLines(toReturn, QueryComponent.QueryTimeColumn);
for (int i = 0; i < SelectColumns.Count; i++)
{
//output each of the ExtractionInformations that the user requested and record the line number for posterity
string columnAsSql = SelectColumns[i].GetSelectSQL(_hashingAlgorithm, _salt, _syntaxHelper);
//there is another one coming
if (i + 1 < SelectColumns.Count)
{
columnAsSql += ",";
}
toReturn += columnAsSql + Environment.NewLine;
}
#endregion
//work out basic JOINS Sql
toReturn += SqlQueryBuilderHelper.GetFROMSQL(this);
//add user custom JOIN lines
toReturn = AppendCustomLines(toReturn, QueryComponent.JoinInfoJoin);
#region Filters (WHERE)
toReturn += SqlQueryBuilderHelper.GetWHERESQL(this);
toReturn = AppendCustomLines(toReturn, QueryComponent.WHERE);
toReturn = AppendCustomLines(toReturn, QueryComponent.Postfix);
_sql = toReturn;
SQLOutOfDate = false;
#endregion
}
/// <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);
}