public string GetCommand()
{
var repo = new MemoryCatalogueRepository();
var qb = new QueryBuilder("distinct", null);
qb.AddColumn(new ColumnInfoToIColumn(repo, _keyColumn)
{
Order = 0
});
qb.AddColumn(new ColumnInfoToIColumn(repo, _descriptionColumn)
{
Order = 1
});
qb.TopX = 100;
var container = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.AND);
if (!string.IsNullOrWhiteSpace(tbCode.Text))
{
var codeFilter = new SpontaneouslyInventedFilter(repo, container, _keyColumn.GetFullyQualifiedName() + " LIKE '" + tbCode.Text + "%'", "Key Starts", "", null);
container.AddChild(codeFilter);
}
if (!string.IsNullOrWhiteSpace(tbDescription.Text))
{
var codeFilter = new SpontaneouslyInventedFilter(repo, container, _descriptionColumn.GetFullyQualifiedName() + " LIKE '%" + tbDescription.Text + "%'", "Description Contains", "", null);
container.AddChild(codeFilter);
}
qb.RootFilterContainer = container;
return(qb.SQL);
}
public void IsReadonly_SpontaneousContainer()
{
var memoryrepo = new MemoryCatalogueRepository();
var c = new SpontaneouslyInventedFilterContainer(memoryrepo, null, null, FilterContainerOperation.AND);
Assert.IsFalse(c.ShouldBeReadOnly(out _), "Spont containers should never be in UI but lets not tell the programmer they shouldn't be edited");
}
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);
}
//so hacky, we pass in the summary builder (a blatant lie!) and tell the CohortQueryBuilderHelper it belongs to AggregateConfiguration _cohort (when it doesn't). This
//will result in any PatientIndex tables associated with _cohort being propagated into the _summary builder
var cohortHelper = new CohortQueryBuilderHelper(_globals, new ParameterManager(_globals), _cohort.GetCohortIdentificationConfigurationIfAny().QueryCachingServer);
cohortHelper.AddJoinablesToBuilder(summaryBuilder, _cohort, 1);
//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);
}
/// <summary>
/// Generates the WHERE logic for the query. Adds a single root container with AND operation and then adds
/// all filters in <see cref="GetFilters"/>. It is better to override <see cref="GetFilters"/> unless you want
/// to create a nested container tree for the query.
/// </summary>
/// <returns></returns>
protected virtual IContainer GetWhereLogic()
{
//make a root WHERE container in memory
var memory = new MemoryCatalogueRepository();
var container = new SpontaneouslyInventedFilterContainer(memory, null, null, FilterContainerOperation.AND);
//Get all filters that we are to add and add them to the root
foreach (IFilter filter in GetFilters(memory, container))
{
container.AddChild(new SpontaneouslyInventedFilter(memory, container, filter.WhereSQL, filter.Name, filter.Description, filter.GetAllParameters()));
}
return(container);
}
protected override AggregateBuilder GetQueryBuilder(AggregateConfiguration aggregateConfiguration)
{
var basicQueryBuilder = base.GetQueryBuilder(aggregateConfiguration);
var rootContainer = basicQueryBuilder.RootFilterContainer;
//stick our IFilter into the root container (actually create a new root container with our filter in it and move the old root if any into it)
rootContainer =
new SpontaneouslyInventedFilterContainer(new MemoryCatalogueRepository(), rootContainer == null ? null : new[] { rootContainer },
new[] { _collection.GetFilter() }, FilterContainerOperation.AND);
basicQueryBuilder.RootFilterContainer = rootContainer;
return(basicQueryBuilder);
}
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 string GetMappingTableSql()
{
var repo = new MemoryCatalogueRepository();
var qb = new QueryBuilder("DISTINCT", null, null);
qb.AddColumn(new ColumnInfoToIColumn(repo, MappingFromColumn));
qb.AddColumn(new ColumnInfoToIColumn(repo, MappingToColumn));
if (!string.IsNullOrWhiteSpace(WHERELogic))
{
var container = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.AND);
var filter = new SpontaneouslyInventedFilter(repo, container, WHERELogic, "WHERELogic", null, null);
container.AddChild(filter);
qb.RootFilterContainer = container;
}
return(qb.SQL);
}
private void AddWHEREToBuilder_CategoryIsTOrNumberGreaterThan42(AggregateBuilder builder, DatabaseType type)
{
var syntaxHelper = new QuerySyntaxHelperFactory().Create(type);
var declaration = syntaxHelper.GetParameterDeclaration("@category", new DatabaseTypeRequest(typeof(string), 1));
var repo = new MemoryCatalogueRepository();
var ORContainer = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.OR);
var constParam = new ConstantParameter(declaration, "'T'", "T Category Only", syntaxHelper);
//this is deliberately duplication, it tests that the parameter compiles as well as that any dynamic sql doesn't get thrown by quotes
var filter1 = new SpontaneouslyInventedFilter(repo, ORContainer, "(Category=@category OR Category = 'T')", "Category Is @category",
"ensures the records belong to the category @category", new ISqlParameter[] { constParam });
var filter2 = new SpontaneouslyInventedFilter(repo, ORContainer, "NumberInTrouble > 42",
"number in trouble greater than 42", "See above", null);
ORContainer.AddChild(filter1);
ORContainer.AddChild(filter2);
builder.RootFilterContainer = ORContainer;
}
public void OpportunisticJoinRequired()
{
var memory = new MemoryRepository();
//tables and columns
TableInfo head = new TableInfo(CatalogueRepository, "Head");
ColumnInfo col1 = new ColumnInfo(CatalogueRepository, "TestResultSetNumber", "int", head);
ColumnInfo col2 = new ColumnInfo(CatalogueRepository, "PK", "int", head);
TableInfo result = new TableInfo(CatalogueRepository, "[biochemistry]..[Result]");
ColumnInfo col3 = new ColumnInfo(CatalogueRepository, "FK", "int", result);
ColumnInfo col4 = new ColumnInfo(CatalogueRepository, "Code", "varchar(10)", result);
ColumnInfo col5 = new ColumnInfo(CatalogueRepository, "[biochemistry]..[Result].[OmgBob]", "varchar(10)", result);
//we can join on col2 = col3
new JoinInfo(CatalogueRepository, col3, col2, ExtractionJoinType.Right, "");
//CASE 1 : Only 1 column used so no join needed
var queryBuilder = new QueryBuilder(null, null);
var icol1 = new ColumnInfoToIColumn(memory, col1);
icol1.Order = 1;
queryBuilder.AddColumn(icol1);
TableInfo primary;
var tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(1, tablesUsed.Count);
Assert.AreEqual(head, tablesUsed[0]);
//CASE 2 : 2 columns used one from each table so join is needed
queryBuilder = new QueryBuilder(null, null);
queryBuilder.AddColumn(new ColumnInfoToIColumn(memory, col1));
var icol4 = new ColumnInfoToIColumn(memory, col4);
icol4.Order = 2;
queryBuilder.AddColumn(icol4);
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(2, tablesUsed.Count);
Assert.AreEqual(head, tablesUsed[0]);
Assert.AreEqual(result, tablesUsed[1]);
Assert.AreEqual(CollapseWhitespace(@"SELECT
TestResultSetNumber,
Code
FROM
[biochemistry]..[Result] Right JOIN Head ON FK = PK"), CollapseWhitespace(queryBuilder.SQL));
var memoryRepository = new MemoryCatalogueRepository();
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, null, FilterContainerOperation.AND);
var spontFilter = new SpontaneouslyInventedFilter(memoryRepository, spontContainer, "[biochemistry]..[Result].[OmgBob] = 'T'",
"My Filter", "Causes spontaneous requirement for joining compeltely", null);
spontContainer.AddChild(spontFilter);
//CASE 3 : Only 1 column from Head but filter contains a reference to Result column
queryBuilder = new QueryBuilder(null, null);
queryBuilder.AddColumn(new ColumnInfoToIColumn(memory, col1));
//without the filter
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(1, tablesUsed.Count);
//set the filter
queryBuilder.RootFilterContainer = spontContainer;
//this is super sneaky but makes the queryBuilder populate it's Filters property... basically your not supposed to use SqlQueryBuilderHelper for this kind of thing
Console.WriteLine(queryBuilder.SQL);
queryBuilder.ParameterManager.ClearNonGlobals();
//with the filter
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(2, tablesUsed.Count);
}
protected override AggregateBuilder GetQueryBuilder(AggregateConfiguration aggregateConfiguration)
{
if (Request == null)
{
throw new Exception("Request has not been initialized yet, has SetCollection not yet been called?");
}
var repo = new MemoryCatalogueRepository();
//we are hijacking the query builder creation for this graph
AggregateBuilder toReturn = base.GetQueryBuilder(aggregateConfiguration);
//instead of only filtering on the filters of the Aggregate, also filter on the configurations data extraction filters AND on the cohort ID
var spontedContainer = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.AND);
//the aggregate has filters (it probably does)
if (toReturn.RootFilterContainer != null)
{
spontedContainer.AddChild(toReturn.RootFilterContainer);//add it
}
//the cohort extraction request has filters?
if (Request.QueryBuilder.RootFilterContainer != null)
{
spontedContainer.AddChild(Request.QueryBuilder.RootFilterContainer);//add those too
}
//now also add the cohort where statement
string cohortWhereSql = Request.ExtractableCohort.WhereSQL();
var spontedFilter = new SpontaneouslyInventedFilter(repo, spontedContainer, cohortWhereSql, "Cohort ID Filter",
"Cohort ID Filter (" + Request.ExtractableCohort + ")", null);
//now we need to figure out what impromptu joins are going on in the main query extraction
//Normally we have an impromptu join e.g. Inner Join MyCohortTable on CHI = MyCohortTable.CHI
//Also we can expect there to be joins to custom tables e.g. Left Join MyCohortCustomTable1 on CHI = MyCustomCohortTable1.CHI (so they can select freaky researcher columns like PatientBarcode etc
//Finally we expect that there is an impromptu filter which does the cohort ID restriction on the query - we have already dealt with that above with a SpontaneouslyInventedFilter so we can ignore those
//But maybe some other programmer has sneaked in some other custom lines we should worry about
var customLines = Request.QueryBuilder.CustomLines.ToArray();
//we expected a custom line for this (which we have dealt with above so throw it away)
customLines = customLines.Where(c => c.Text != Request.ExtractableCohort.WhereSQL()).ToArray();
//now all that should be left are custom lines which are joins (in theory, otherwise complain)
if (customLines.Any(c => c.LocationToInsert != QueryComponent.JoinInfoJoin))
{
throw new QueryBuildingException("We were busy hijacking the ISqlQueryBuilder returned by Request.GetQueryBuilder and were looking for the custom table / cohort table join custom lines but then we noticed there were other custom lines in the query (like non join ones!)");
}
if (!customLines.Any())
{
throw new Exception("Expected there to be at least 1 custom join line returned by the ISqlQueryBuilder fetched with Request.GetQueryBuilder but it had 0 so how did it know what cohort table to join against?");
}
foreach (CustomLine line in customLines)
{
toReturn.AddCustomLine(line.Text, QueryComponent.JoinInfoJoin);
}
spontedContainer.AddChild(spontedFilter);
//now set the original aggregate that we are hijacking to have the new sponted container (which includes the original filters + the extraction ones + the cohort ID one)
toReturn.RootFilterContainer = spontedContainer;
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);
}
