13318089130

Committed 13 Feb 2025 10:13PM UTC coverage: 57.398% (+0.004%) from 57.394%

Build # 13318089130

Build Type

Pull #2134

github

Committed by

jas88

Commit Message

Update ChildProviderTests.cs

Fix up TestUpTo method

Pull Request Pull Request #2134: CodeQL fixups

Run Details

11346 of 21308 branches covered (53.25%)

Branch coverage included in aggregate %.

104 of 175 new or added lines in 45 files covered. (59.43%)

362 existing lines in 23 files now uncovered.

32218 of 54590 relevant lines covered (59.02%)

17091.93 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

82.88

/Rdmp.Core/QueryBuilding/CohortQueryBuilderHelper.cs

// Copyright (c) The University of Dundee 2018-2019
// This file is part of the Research Data Management Platform (RDMP).
// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.

using System;
using System.Linq;
using FAnsi.Discovery.QuerySyntax;
using FAnsi.Naming;
using Rdmp.Core.Curation.Data;
using Rdmp.Core.Curation.Data.Aggregation;
using Rdmp.Core.Curation.Data.Spontaneous;
using Rdmp.Core.MapsDirectlyToDatabaseTable;
using Rdmp.Core.QueryBuilding.Parameters;

namespace Rdmp.Core.QueryBuilding;

/// <summary>
/// Helper for CohortQueryBuilder which contains code for building individual cohort identification subqueries.  Subqueries are actually built by
/// AggregateBuilder but this class handles tab indentation, parameter renaming (where there are other subqueries with conflicting sql parameter names),
/// injecting globals etc.
/// </summary>
public class CohortQueryBuilderHelper
{
    /// <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 dependent 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 static CohortQueryBuilderDependencySql GetSQLForAggregate(AggregateConfiguration aggregate,
        QueryBuilderArgs args)
    {
        var isJoinAggregate = aggregate.IsCohortIdentificationAggregate;

        //make sure it is a valid configuration
        if (!aggregate.IsAcceptableAsCohortGenerationSource(out var 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)
        var 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;

            var 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, !extractionIdentifier.GroupBy);
            else
                foreach (var agg in aggregate.AggregateDimensions)
                {
                    builder.AddColumn(agg, false, !agg.GroupBy);
                }
        }
        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, !extractionIdentifier.GroupBy);
            else
            {
                foreach (var agg in aggregate.AggregateDimensions)
                {
                    builder.AddColumn(agg, true, !agg.GroupBy);
                }
            } //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
        var 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;
    }

    public static void AddJoinToBuilder(AggregateConfiguration user, IColumn usersExtractionIdentifier,
        AggregateBuilder builder, QueryBuilderArgs args)
    {
        var joinableTableAlias = args.JoinIfAny.GetJoinTableAlias();
        var joinDirection = args.JoinIfAny.GetJoinDirectionSQL();

        IHasRuntimeName joinOn = null;

        if (args.JoinedTo.Catalogue.IsApiCall(out var plugin))
        {
            if (plugin == null)
                throw new Exception(
                    $"No IPluginCohortCompiler was found that supports API cohort set '{args.JoinedTo}'");

            joinOn = plugin.GetJoinColumnForPatientIndexTable(args.JoinedTo);
        }
        else
        {
            joinOn = args.JoinedTo.AggregateDimensions.SingleOrDefault(d => d.IsExtractionIdentifier);
        }

        if (joinOn == null)
            throw new QueryBuildingException(
                $"AggregateConfiguration {user} uses a join aggregate (patient index aggregate) of {args.JoinedTo} but that AggregateConfiguration does not have an IsExtractionIdentifier dimension so how are we supposed to join these tables on the patient identifier?");

        // will end up with something like this where 51 is the ID of the joinTable:
        // LEFT Join (***INCEPTION QUERY***)ix51 on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[patientIdentifier] = ix51.patientIdentifier

        builder.AddCustomLine(
            $" {joinDirection} Join ({Environment.NewLine}{TabIn(args.JoinSql.Sql, 1)}{Environment.NewLine}){joinableTableAlias}{Environment.NewLine}on {usersExtractionIdentifier.SelectSQL} = {joinableTableAlias}.{joinOn.GetRuntimeName()}",
            QueryComponent.JoinInfoJoin);
    }

    public static string TabIn(string str, int numberOfTabs)
    {
        if (string.IsNullOrWhiteSpace(str))
            return str;

        var tabs = new string('\t', numberOfTabs);
        return tabs + str.Replace(Environment.NewLine, Environment.NewLine + tabs);
    }
}

1	// Copyright (c) The University of Dundee 2018-2019
2	// This file is part of the Research Data Management Platform (RDMP).
3	// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
4	// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
5	// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.
6
7	using System;
8	using System.Linq;
9	using FAnsi.Discovery.QuerySyntax;
10	using FAnsi.Naming;
11	using Rdmp.Core.Curation.Data;
12	using Rdmp.Core.Curation.Data.Aggregation;
13	using Rdmp.Core.Curation.Data.Spontaneous;
14	using Rdmp.Core.MapsDirectlyToDatabaseTable;
15	using Rdmp.Core.QueryBuilding.Parameters;
16
17	namespace Rdmp.Core.QueryBuilding;
18
19	/// <summary>
20	/// Helper for CohortQueryBuilder which contains code for building individual cohort identification subqueries. Subqueries are actually built by
21	/// AggregateBuilder but this class handles tab indentation, parameter renaming (where there are other subqueries with conflicting sql parameter names),
22	/// injecting globals etc.
23	/// </summary>
24	public class CohortQueryBuilderHelper
25	{
26	/// <summary>
27	/// Returns the SQL you need to include in your nested query (in UNION / EXCEPT / INTERSECT). This does not include parameter declarations (which
28	/// would appear at the very top) and includes rename operations dependent on what has been written out before by (tracked by <see cref="ParameterManager"/>).
29	///
30	/// <para>Use <paramref name="args"/> for the original un renamed / including parameter declarations e.g. to test for cache hits</para>
31	/// </summary>
32	/// <param name="aggregate"></param>
33	/// <param name="args"></param>
34	/// <returns></returns>
35	public static CohortQueryBuilderDependencySql GetSQLForAggregate(AggregateConfiguration aggregate,
36	QueryBuilderArgs args)
37	{
38	var isJoinAggregate = aggregate.IsCohortIdentificationAggregate;	556✔
39
40	//make sure it is a valid configuration
41	if (!aggregate.IsAcceptableAsCohortGenerationSource(out var reason))	556!
42	throw new QueryBuildingException(	×
43	$"Cannot generate a cohort using AggregateConfiguration {aggregate} because:{reason}");	×
44
45	//get the extraction identifier (method IsAcceptableAsCohortGenerationSource will ensure this linq returns 1 so no need to check again)
46	var extractionIdentifier = aggregate.AggregateDimensions.Single(d => d.IsExtractionIdentifier);	1,318✔
47
48	//create a builder but do it manually, we care about group bys etc or count(*) even
49	AggregateBuilder builder;
50
51	//we are getting SQL for a cohort identification aggregate without a HAVING/count statement so it is actually just 'select patientIdentifier from tableX'
52	if (string.IsNullOrWhiteSpace(aggregate.HavingSQL) && string.IsNullOrWhiteSpace(aggregate.CountSQL))	556✔
53	{
54	//select list is the extraction identifier
55	string selectList;
56
57	if (!isJoinAggregate)	524✔
58	selectList = extractionIdentifier.SelectSQL + (extractionIdentifier.Alias != null	12!
59	? $" {extractionIdentifier.Alias}"	12✔
60	: "");	12✔
61	else
62	//unless we are also including other columns because this is a patient index joinable inception query
63	selectList = string.Join($",{Environment.NewLine}",	512✔
64	aggregate.AggregateDimensions.Select(e =>	512✔
65	e.SelectSQL +	1,230!
66	(e.Alias != null	1,230✔
67	? $" {e.Alias}"	1,230✔
68	: ""))); //joinable patient index tables have patientIdentifier + 1 or more other columns	1,230✔
69
70	if (args.OverrideSelectList != null)	524✔
71	selectList = args.OverrideSelectList;	4✔
72
73	var limitationSQL = args?.OverrideLimitationSQL ?? "distinct";	524!
74
75	//select list is either [chi] or [chi],[mycolumn],[myexcitingcol] (in the case of a patient index table)
76	builder = new AggregateBuilder(limitationSQL, selectList, aggregate, aggregate.ForcedJoins);	524✔
77
78	//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
79	if (!isJoinAggregate)	524✔
80	builder.AddColumn(extractionIdentifier, false, !extractionIdentifier.GroupBy);	12✔
81	else
82	foreach (var agg in aggregate.AggregateDimensions)	2,460✔
83	{
84	builder.AddColumn(agg, false, !agg.GroupBy);	718✔
85	}
86	}
87	else
88	{
89	if (args.OverrideSelectList != null)	32!
90	throw new NotSupportedException(	×
91	"Cannot override Select list on aggregates that have HAVING / Count SQL configured in them");	×
92
93	builder = new AggregateBuilder("distinct", aggregate.CountSQL, aggregate, aggregate.ForcedJoins);	32✔
94
95	//add the extraction information and do group by it
96	if (!isJoinAggregate)	32!
97	builder.AddColumn(extractionIdentifier, true, !extractionIdentifier.GroupBy);	×
98	else
99	{
100	foreach (var agg in aggregate.AggregateDimensions)	128✔
101	{
102	builder.AddColumn(agg, true, !agg.GroupBy);	32✔
103	}
104	} //it's a joinable inception query (See JoinableCohortAggregateConfiguration) - these are allowed additional columns
105
106	builder.DoNotWriteOutOrderBy = true;	32✔
107	}
108
109	if (args.TopX != -1)	556✔
110	builder.AggregateTopX = new SpontaneouslyInventedAggregateTopX(new MemoryRepository(), args.TopX,	6✔
111	AggregateTopXOrderByDirection.Descending, null);	6✔
112
113	//make sure builder has globals
114	foreach (var global in args.Globals)	1,128✔
115	builder.ParameterManager.AddGlobalParameter(global);	8✔
116
117	//Add the inception join
118	if (args.JoinIfAny != null)	556✔
119	AddJoinToBuilder(aggregate, extractionIdentifier, builder, args);	116✔
120
121	//set the where container
122	builder.RootFilterContainer = aggregate.RootFilterContainer;	556✔
123
124	//we will be harnessing the parameters via ImportAndElevate so do not add them to the SQL directly
125	builder.DoNotWriteOutParameters = true;	556✔
126	var builderSqlWithoutParameters = builder.SQL;	556✔
127
128	//get the SQL from the builder (for the current configuration) - without parameters
129	var currentBlock = builderSqlWithoutParameters;	556✔
130
131	var toReturn = new CohortQueryBuilderDependencySql(currentBlock, builder.ParameterManager);	556✔
132
133	if (args.JoinSql != null) toReturn.ParametersUsed.MergeWithoutRename(args.JoinSql.ParametersUsed);	672✔
134
135	//we need to generate the full SQL with parameters (and no rename operations) so we can do cache hit tests
136	//renaming is deferred to later
137	return toReturn;	540✔
138	}
139
140	public static void AddJoinToBuilder(AggregateConfiguration user, IColumn usersExtractionIdentifier,
141	AggregateBuilder builder, QueryBuilderArgs args)
142	{
143	var joinableTableAlias = args.JoinIfAny.GetJoinTableAlias();	116✔
144	var joinDirection = args.JoinIfAny.GetJoinDirectionSQL();	116✔
145
146	IHasRuntimeName joinOn = null;	116✔
147
148	if (args.JoinedTo.Catalogue.IsApiCall(out var plugin))	116✔
149	{
150	if (plugin == null)	8!
151	throw new Exception(	×
UNCOV 152	$"No IPluginCohortCompiler was found that supports API cohort set '{args.JoinedTo}'");	×
153
154	joinOn = plugin.GetJoinColumnForPatientIndexTable(args.JoinedTo);	8✔
155	}
156	else
157	{
158	joinOn = args.JoinedTo.AggregateDimensions.SingleOrDefault(d => d.IsExtractionIdentifier);	418✔
159	}
160
161	if (joinOn == null)	116!
162	throw new QueryBuildingException(	×
UNCOV 163	$"AggregateConfiguration {user} uses a join aggregate (patient index aggregate) of {args.JoinedTo} but that AggregateConfiguration does not have an IsExtractionIdentifier dimension so how are we supposed to join these tables on the patient identifier?");	×
164
165	// will end up with something like this where 51 is the ID of the joinTable:
166	// LEFT Join (*INCEPTION QUERY*)ix51 on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[patientIdentifier] = ix51.patientIdentifier
167
168	builder.AddCustomLine(	116✔
169	$" {joinDirection} Join ({Environment.NewLine}{TabIn(args.JoinSql.Sql, 1)}{Environment.NewLine}){joinableTableAlias}{Environment.NewLine}on {usersExtractionIdentifier.SelectSQL} = {joinableTableAlias}.{joinOn.GetRuntimeName()}",	116✔
170	QueryComponent.JoinInfoJoin);	116✔
171	}	116✔
172
173	public static string TabIn(string str, int numberOfTabs)
174	{
175	if (string.IsNullOrWhiteSpace(str))	116!
UNCOV 176	return str;	×
177
178	var tabs = new string('\t', numberOfTabs);	116✔
179	return tabs + str.Replace(Environment.NewLine, Environment.NewLine + tabs);	116✔
180	}
181	}

HicServices / RDMP / 13318089130

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous