public string GetDatasetSampleSQL(int topX = 1000, ICoreChildProvider childProvider = null)
{
if (configuration == null)
{
throw new NotSupportedException("Can only generate select * statements when constructed for a single AggregateConfiguration, this was constructed with a container as the root entity (it may even reflect a UNION style query that spans datasets)");
}
//Show the user all the fields (*) unless there is a HAVING or it is a Patient Index Table.
string selectList =
string.IsNullOrWhiteSpace(configuration.HavingSQL) && !configuration.IsJoinablePatientIndexTable() ? "*" : null;
RecreateHelpers(new QueryBuilderCustomArgs(selectList, "" /*removes distinct*/, topX));
Results.BuildFor(configuration, ParameterManager);
string sampleSQL = Results.Sql;
string parameterSql = "";
//get resolved parameters for the select * query
var finalParams = ParameterManager.GetFinalResolvedParametersList().ToArray();
if (finalParams.Any())
{
parameterSql = QueryBuilder.GetParameterDeclarationSQL(finalParams);
return(parameterSql + Environment.NewLine + sampleSQL);
}
return(sampleSQL);
}
public void FindOverridenParameters_TwoTest()
{
var myParameter1 = new ConstantParameter("DECLARE @fish as int", "1", "fishes be here", new MicrosoftQuerySyntaxHelper());
var myParameter2 = new ConstantParameter("DECLARE @fish as int", "2", "fishes be here", new MicrosoftQuerySyntaxHelper());
var overridingParameter = new ConstantParameter("DECLARE @fish as int", "3", "overriding value", new MicrosoftQuerySyntaxHelper());
var pm = new ParameterManager();
pm.ParametersFoundSoFarInQueryGeneration[ParameterLevel.TableInfo].Add(myParameter1);
pm.ParametersFoundSoFarInQueryGeneration[ParameterLevel.CompositeQueryLevel].Add(myParameter2);
pm.ParametersFoundSoFarInQueryGeneration[ParameterLevel.Global].Add(overridingParameter);
var overrides = pm.GetOverridenParameters().ToArray();
Assert.IsNull(pm.GetOverrideIfAnyFor(overridingParameter));
Assert.AreEqual(pm.GetOverrideIfAnyFor(myParameter1), overridingParameter);
Assert.AreEqual(pm.GetOverrideIfAnyFor(myParameter2), overridingParameter);
Assert.AreEqual(2, overrides.Length);
Assert.AreEqual(myParameter1, overrides[0]);
Assert.AreEqual(myParameter2, overrides[1]);
var final = pm.GetFinalResolvedParametersList().ToArray();
Assert.AreEqual(1, final.Length);
Assert.AreEqual(overridingParameter, final[0]);
}
public void RegenerateSQL()
{
RecreateHelper();
_sql = "";
if (container != null)
{
AddContainerRecursively(container, 0); //user constructed us with a container (and possibly subcontainers even - any one of them chock full of aggregates)
}
else
{
AddAggregate(configuration, -1);//user constructed us without a container, he only cares about 1 aggregate
}
//Still finalise the ParameterManager even if we are not writting out the parameters so that it is in the Finalized state
var finalParameters = ParameterManager.GetFinalResolvedParametersList();
if (!DoNotWriteOutParameters)
{
string parameterSql = "";
//add the globals
foreach (ISqlParameter param in finalParameters)
{
parameterSql += QueryBuilder.GetParameterDeclarationSQL(param);
}
_sql = parameterSql + _sql;
}
SQLOutOfDate = false;
}
public void Test_ParameterManager_SimpleRename()
{
var p1 = new ConstantParameter("DECLARE @fish as int", "1", "fishes be here", new MicrosoftQuerySyntaxHelper());
var p2 = new ConstantParameter("DECLARE @fish as int", "2", "fishes be here", new MicrosoftQuerySyntaxHelper());
var pm1 = new ParameterManager();
var pm2 = new ParameterManager();
var pm3 = new ParameterManager();
pm1.ParametersFoundSoFarInQueryGeneration[ParameterLevel.QueryLevel].Add(p1);
pm2.ParametersFoundSoFarInQueryGeneration[ParameterLevel.QueryLevel].Add(p2);
pm3.ImportAndElevateResolvedParametersFromSubquery(pm1, out Dictionary <string, string> renames1);
pm3.ImportAndElevateResolvedParametersFromSubquery(pm2, out Dictionary <string, string> renames2);
var final = pm3.GetFinalResolvedParametersList().ToArray();
//the final composite parameters should have a rename in them
Assert.AreEqual("@fish", final[0].ParameterName);
Assert.AreEqual("@fish_2", final[1].ParameterName);
Assert.IsEmpty(renames1);
Assert.AreEqual("@fish", renames2.Single().Key);
Assert.AreEqual("@fish_2", renames2.Single().Value);
}
/// <inheritdoc/>
public ISqlParameter[] GetAllParameters(AggregateConfiguration aggregate)
{
var parameterManager = new ParameterManager();
foreach (var p in _globals)
{
parameterManager.AddGlobalParameter(p);
}
parameterManager.AddParametersFor(aggregate, ParameterLevel.QueryLevel);
return(parameterManager.GetFinalResolvedParametersList().ToArray());
}
public void FindOverridenParameters_CaseSensitivityTest()
{
var baseParameter = new ConstantParameter("DECLARE @fish as int", "1", "fishes be here", new MicrosoftQuerySyntaxHelper());
var overridingParameter = new ConstantParameter("DECLARE @Fish as int", "3", "overriding value", new MicrosoftQuerySyntaxHelper());
var pm = new ParameterManager();
pm.ParametersFoundSoFarInQueryGeneration[ParameterLevel.TableInfo].Add(baseParameter);
pm.ParametersFoundSoFarInQueryGeneration[ParameterLevel.QueryLevel].Add(overridingParameter);
var parameters = pm.GetFinalResolvedParametersList().ToArray();
Assert.AreEqual(1, parameters.Count());
var final = parameters.Single();
Assert.AreEqual("@Fish", final.ParameterName);
Assert.AreEqual("3", final.Value);
}
public void FindOverridenParameters_OneOnlyTest(ParameterLevel addAt, ParameterLevel overridingLevel)
{
var myParameter = new ConstantParameter("DECLARE @fish as int", "1", "fishes be here", new MicrosoftQuerySyntaxHelper());
var overridingParameter = new ConstantParameter("DECLARE @fish as int", "999", "overriding value", new MicrosoftQuerySyntaxHelper());
var pm = new ParameterManager();
pm.ParametersFoundSoFarInQueryGeneration[ParameterLevel.TableInfo].Add(myParameter);
pm.ParametersFoundSoFarInQueryGeneration[overridingLevel].Add(overridingParameter);
var overrides = pm.GetOverridenParameters().ToArray();
Assert.IsNull(pm.GetOverrideIfAnyFor(overridingParameter));
Assert.AreEqual(pm.GetOverrideIfAnyFor(myParameter), overridingParameter);
Assert.AreEqual(1, overrides.Length);
Assert.AreEqual(myParameter, overrides[0]);
var final = pm.GetFinalResolvedParametersList().ToArray();
Assert.AreEqual(1, final.Length);
Assert.AreEqual(overridingParameter, final[0]);
}
/// <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);
}