public string GetDatasetSampleSQL(int topX = 1000)
{
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)");
}
//create a clone of ourselves so we don't mess up the ParameterManager of this instance
var cloneBuilder = new CohortQueryBuilder(configuration, _globals);
cloneBuilder.TopX = topX;
cloneBuilder.CacheServer = CacheServer;
//preview means we override the select columns with *
var overrideSelectList = "*";
// unless its a patient index table or has HAVING sql
if (!string.IsNullOrWhiteSpace(configuration.HavingSQL) || _isExplicitRequestForJoinableInceptionAggregateQuery)
{
overrideSelectList = null;
}
string sampleSQL =
cloneBuilder.GetSQLForAggregate(configuration,
0,
_isExplicitRequestForJoinableInceptionAggregateQuery,
overrideSelectList,
""); //gets rid of the distinct keyword
string parameterSql = "";
//get resolved parameters for the select * query (via the clone builder
var finalParams = cloneBuilder.ParameterManager.GetFinalResolvedParametersList().ToArray();
if (finalParams.Any())
{
foreach (ISqlParameter param in finalParams)
{
parameterSql += QueryBuilder.GetParameterDeclarationSQL(param);
}
return(parameterSql + Environment.NewLine + sampleSQL);
}
return(sampleSQL);
}
private AggregateBuilder GetAdjustedForExtractionIdentifiersIn()
{
var cachingServer = GetQueryCachingServer();
if (cachingServer == null)
{
throw new NotSupportedException("No Query Caching Server configured");
}
var memoryRepository = new MemoryCatalogueRepository();
//Get a builder for creating the basic aggregate graph
var builder = _summary.GetQueryBuilder();
//Find it's root container if it has one
var oldRootContainer = builder.RootFilterContainer;
//Create a new spontaneous container (virtual memory only container, this will include an in line filter that restricts the graph to match the cohort and then include a subcontainer with the old root container - if there was one)
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, oldRootContainer != null ? new[] { oldRootContainer } : null, null, FilterContainerOperation.AND);
//work out a filter SQL that will restrict the graph generated only to the cohort
CohortQueryBuilder cohortQueryBuilder = GetBuilder();
cohortQueryBuilder.CacheServer = cachingServer;
//It is comming direct from the cache so we don't need to output any parameters... the only ones that would appear are the globals anyway and those are not needed since cache
cohortQueryBuilder.DoNotWriteOutParameters = true;
//the basic cohort SQL select chi from dataset where ....
var cohortSql = cohortQueryBuilder.SQL;
if (cohortQueryBuilder.CountOfCachedSubQueries == 0 || cohortQueryBuilder.CountOfSubQueries != cohortQueryBuilder.CountOfCachedSubQueries)
{
throw new NotSupportedException("Only works for 100% Cached queries, your query has " + cohortQueryBuilder.CountOfCachedSubQueries + "/" + cohortQueryBuilder.CountOfSubQueries + " queries cached");
}
//there will be a single dimension on the cohort aggregate so this translates to "MyTable.MyDataset.CHI in Select(
var filterSql = _extractionIdentifierColumn.SelectSQL + " IN (" + cohortSql + ")";
//Add a filter which restricts the graph generated to the cohort only
spontContainer.AddChild(new SpontaneouslyInventedFilter(memoryRepository, spontContainer, filterSql, "Patient is in cohort", "Ensures the patients in the summary aggregate are also in the cohort aggregate (and only them)", null));
builder.RootFilterContainer = spontContainer;
return(builder);
}
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);
}
