public void Build(CohortQueryBuilderResult parent, ISqlParameter[] globals)
{
bool isSolitaryPatientIndexTable = CohortSet.IsJoinablePatientIndexTable();
//Includes the parameter declaration and no rename operations (i.e. couldn't be used for building the tree but can be used for cache hit testing).
if (JoinedTo != null)
{
SqlJoinableCacheless = parent.Helper.GetSQLForAggregate(JoinedTo,
new QueryBuilderArgs(new QueryBuilderCustomArgs(), //don't respect customizations in the inception bit!
globals));
SqlJoinableCached = GetCachFetchSqlIfPossible(parent, JoinedTo, SqlJoinableCacheless, true);
}
if (isSolitaryPatientIndexTable)
{
//explicit execution of a patient index table on it's own
//the full uncached SQL for the query
SqlCacheless = parent.Helper.GetSQLForAggregate(CohortSet, new QueryBuilderArgs(parent.Customise, globals));
if (SqlJoinableCached != null)
{
throw new QueryBuildingException("Patient index tables can't use other patient index tables!");
}
}
else
{
//the full uncached SQL for the query
SqlCacheless = parent.Helper.GetSQLForAggregate(CohortSet,
new QueryBuilderArgs(PatientIndexTableIfAny, JoinedTo,
SqlJoinableCacheless, parent.Customise, globals));
//if the joined to table is cached we can generate a partial too with full sql for the outer sql block and a cache fetch join
if (SqlJoinableCached != null)
{
SqlPartiallyCached = parent.Helper.GetSQLForAggregate(CohortSet,
new QueryBuilderArgs(PatientIndexTableIfAny, JoinedTo,
SqlJoinableCached, parent.Customise, globals));
}
}
//We would prefer a cache hit on the exact uncached SQL
SqlFullyCached = GetCachFetchSqlIfPossible(parent, CohortSet, SqlCacheless, isSolitaryPatientIndexTable);
//but if that misses we would take a cache hit of an execution of the SqlPartiallyCached
if (SqlFullyCached == null && SqlPartiallyCached != null)
{
SqlFullyCached = GetCachFetchSqlIfPossible(parent, CohortSet, SqlPartiallyCached, isSolitaryPatientIndexTable);
}
}
/// <summary>
/// Creates arguments for an <see cref="AggregateConfiguration"/> which has a JOIN to a patient index table. All arguments must be provided
/// </summary>
/// <param name="join">The join usage relationship object (includes join direction etc)</param>
/// <param name="joinedTo">The patient index to which the join is made to (e.g. <see cref="JoinableCohortAggregateConfiguration.AggregateConfiguration"/>)</param>
/// <param name="joinSql">The full SQL of the join</param>
/// <param name="customisations"></param>
/// <param name="globals"></param>
public QueryBuilderArgs(JoinableCohortAggregateConfigurationUse join, AggregateConfiguration joinedTo, CohortQueryBuilderDependencySql joinSql, QueryBuilderCustomArgs customisations, ISqlParameter[] globals) : this(customisations, globals)
{
JoinIfAny = join;
JoinedTo = joinedTo;
JoinSql = joinSql;
if (JoinIfAny == null != (JoinedTo == null) || JoinIfAny == null != (JoinSql == null))
{
throw new Exception("You must provide all arguments or no arguments");
}
if (JoinedTo != null)
{
if (!JoinedTo.IsCohortIdentificationAggregate || !JoinedTo.IsJoinablePatientIndexTable())
{
throw new ArgumentException($"JoinedTo ({JoinedTo}) was not a patient index table", nameof(joinedTo));
}
}
}
private CohortQueryBuilderDependencySql GetCachFetchSqlIfPossible(CohortQueryBuilderResult parent, AggregateConfiguration aggregate, CohortQueryBuilderDependencySql sql, bool isPatientIndexTable)
{
if (parent.CacheManager == null)
{
return(null);
}
string parameterSql = QueryBuilder.GetParameterDeclarationSQL(sql.ParametersUsed.Clone().GetFinalResolvedParametersList());
string hitTestSql = parameterSql + sql.Sql;
var existingTable = parent.CacheManager.GetLatestResultsTable(aggregate, isPatientIndexTable
?AggregateOperation.JoinableInceptionQuery:AggregateOperation.IndexedExtractionIdentifierList, hitTestSql);
//if there is a cached entry matching the cacheless SQL then we can just do a select from it (in theory)
if (existingTable != null)
{
string sqlCachFetch = CachedAggregateConfigurationResultsManager.CachingPrefix + aggregate.Name + @"*/" + Environment.NewLine +
"select * from " + existingTable.GetFullyQualifiedName() + Environment.NewLine;
//Cache fetch does not require any parameters
return(new CohortQueryBuilderDependencySql(sqlCachFetch, new ParameterManager()));
}
return(null);
}
/// <summary>
/// Returns the SQL you need to include in your nested query (in UNION / EXCEPT / INTERSECT). This does not include parameter declarations (which
/// would appear at the very top) and includes rename operations dependant on what has been written out before by (tracked by <see cref="ParameterManager"/>).
///
/// <para>Use <paramref name="args"/> for the original un renamed / including parameter declarations e.g. to test for cache hits</para>
/// </summary>
/// <param name="aggregate"></param>
/// <param name="args"></param>
/// <returns></returns>
public CohortQueryBuilderDependencySql GetSQLForAggregate(AggregateConfiguration aggregate, QueryBuilderArgs args)
{
bool isJoinAggregate = aggregate.IsCohortIdentificationAggregate;
//make sure it is a valid configuration
string reason;
if (!aggregate.IsAcceptableAsCohortGenerationSource(out reason))
{
throw new QueryBuildingException("Cannot generate a cohort using AggregateConfiguration " + aggregate + " because:" + reason);
}
//get the extraction identifier (method IsAcceptableAsCohortGenerationSource will ensure this linq returns 1 so no need to check again)
AggregateDimension extractionIdentifier = aggregate.AggregateDimensions.Single(d => d.IsExtractionIdentifier);
//create a builder but do it manually, we care about group bys etc or count(*) even
AggregateBuilder builder;
//we are getting SQL for a cohort identification aggregate without a HAVING/count statement so it is actually just 'select patientIdentifier from tableX'
if (string.IsNullOrWhiteSpace(aggregate.HavingSQL) && string.IsNullOrWhiteSpace(aggregate.CountSQL))
{
//select list is the extraction identifier
string selectList;
if (!isJoinAggregate)
{
selectList = extractionIdentifier.SelectSQL + (extractionIdentifier.Alias != null ? " " + extractionIdentifier.Alias: "");
}
else
{
//unless we are also including other columns because this is a patient index joinable inception query
selectList = string.Join("," + Environment.NewLine,
aggregate.AggregateDimensions.Select(e => e.SelectSQL + (e.Alias != null ? " " + e.Alias : ""))); //joinable patient index tables have patientIdentifier + 1 or more other columns
}
if (args.OverrideSelectList != null)
{
selectList = args.OverrideSelectList;
}
string limitationSQL = args?.OverrideLimitationSQL ?? "distinct";
//select list is either [chi] or [chi],[mycolumn],[myexcitingcol] (in the case of a patient index table)
builder = new AggregateBuilder(limitationSQL, selectList, aggregate, aggregate.ForcedJoins);
//false makes it skip them in the SQL it generates (it uses them only in determining JOIN requirements etc but since we passed in the select SQL explicitly it should be the equivellent of telling the query builder to generate a regular select
if (!isJoinAggregate)
{
builder.AddColumn(extractionIdentifier, false);
}
else
{
builder.AddColumnRange(aggregate.AggregateDimensions.ToArray(), false);
}
}
else
{
if (args.OverrideSelectList != null)
{
throw new NotSupportedException("Cannot override Select list on aggregates that have HAVING / Count SQL configured in them");
}
builder = new AggregateBuilder("distinct", aggregate.CountSQL, aggregate, aggregate.ForcedJoins);
//add the extraction information and do group by it
if (!isJoinAggregate)
{
builder.AddColumn(extractionIdentifier, true);
}
else
{
builder.AddColumnRange(aggregate.AggregateDimensions.ToArray(), true);//it's a joinable inception query (See JoinableCohortAggregateConfiguration) - these are allowed additional columns
}
builder.DoNotWriteOutOrderBy = true;
}
if (args.TopX != -1)
{
builder.AggregateTopX = new SpontaneouslyInventedAggregateTopX(new MemoryRepository(), args.TopX, AggregateTopXOrderByDirection.Descending, null);
}
//make sure builder has globals
foreach (var global in args.Globals)
{
builder.ParameterManager.AddGlobalParameter(global);
}
//Add the inception join
if (args.JoinIfAny != null)
{
AddJoinToBuilder(aggregate, extractionIdentifier, builder, args);
}
//set the where container
builder.RootFilterContainer = aggregate.RootFilterContainer;
//we will be harnessing the parameters via ImportAndElevate so do not add them to the SQL directly
builder.DoNotWriteOutParameters = true;
var builderSqlWithoutParameters = builder.SQL;
//get the SQL from the builder (for the current configuration) - without parameters
string currentBlock = builderSqlWithoutParameters;
var toReturn = new CohortQueryBuilderDependencySql(currentBlock, builder.ParameterManager);
if (args.JoinSql != null)
{
toReturn.ParametersUsed.MergeWithoutRename(args.JoinSql.ParametersUsed);
}
//we need to generate the full SQL with parameters (and no rename operations) so we can do cache hit tests
//renaming is deferred to later
return(toReturn);
}
private AggregateBuilder GetAdjustedForRecordsIn(IFilter singleFilterOnly = null)
{
if (_cohort == null)
{
throw new NotSupportedException("This method only works when there is a cohort aggregate, it does not work for CohortAggregateContainers");
}
var memoryRepository = new MemoryCatalogueRepository();
//Get a builder for creating the basic aggregate graph
var summaryBuilder = _summary.GetQueryBuilder();
//Find it's root container if it has one
var summaryRootContainer = summaryBuilder.RootFilterContainer;
//work out a filter SQL that will restrict the graph generated only to the cohort
IContainer cohortRootContainer = _cohort.RootFilterContainer;
//if we are only graphing a single filter from the Cohort
if (singleFilterOnly != null)
{
cohortRootContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new [] { singleFilterOnly }, FilterContainerOperation.AND);
}
var joinUse = _cohort.PatientIndexJoinablesUsed.SingleOrDefault();
var joinTo = joinUse?.JoinableCohortAggregateConfiguration?.AggregateConfiguration;
//if there is a patient index table we must join to it
if (joinUse != null)
{
//get sql for the join table
var builder = new CohortQueryBuilder(joinTo, _globals, null);
var joinableSql = new CohortQueryBuilderDependencySql(builder.SQL, builder.ParameterManager);
var helper = new CohortQueryBuilderHelper();
var extractionIdentifierColumn = _summary.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any)
.Where(ei => ei.IsExtractionIdentifier).ToArray();
if (extractionIdentifierColumn.Length != 1)
{
throw new Exception($"Catalogue behind {_summary} must have exactly 1 IsExtractionIdentifier column but it had " + extractionIdentifierColumn.Length);
}
helper.AddJoinToBuilder(_summary, extractionIdentifierColumn[0], summaryBuilder, new QueryBuilderArgs(joinUse, joinTo, joinableSql, null, _globals));
}
//if the cohort has no WHERE SQL
if (cohortRootContainer == null)
{
return(summaryBuilder); //summary can be run verbatim
}
//the summary has no WHERE SQL
if (summaryRootContainer == null)
{
summaryBuilder.RootFilterContainer = cohortRootContainer;//hijack the cohorts root container
}
else
{
//they both have WHERE SQL
//Create a new spontaneous container (virtual memory only container) that contains both subtrees
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, new[] { cohortRootContainer, summaryRootContainer }, null, FilterContainerOperation.AND);
summaryBuilder.RootFilterContainer = spontContainer;
}
//better import the globals because WHERE logic from the cohort has been inherited... only problem will be if there are conflicting globals in users aggregate but that's just tough luck
foreach (ISqlParameter p in _globals)
{
summaryBuilder.ParameterManager.AddGlobalParameter(p);
}
return(summaryBuilder);
}