9758065908

Committed 02 Jul 2024 09:03AM UTC coverage: 56.679% (-0.2%) from 56.914%

Build # 9758065908

Build Type

push

github

Committed by

web-flow

Commit Message

Release/8.2.0 (#1867)

* add extraction additions

* interim

* add test

* interim

* working dedupe

* improved checking

* add timestamp option

* fix extra looping

* add check

* start on tests

* tidy up code

* update link

* tidy up

* Rename executeFullExtractionToDatabaseMSSql.md to ExecuteFullExtractionToDatabaseMSSql.md

* fix typo

* add docs

* update

* update documentation

* attempt fix docs

* update docs

* tidy up code

* better tests

* add real test

* tidy up

* interim

* grab existiing entity

* no new data

* add basic tests

* attempt to fix test

* interim

* interim commit

* working clash

* add test

* fix test

* improved clash checker

* tidy up

* update test

* fix up test

* update from codeql

* tidy up code

* fix bad merge

* fix typo

* skip over for now

* revert change

* Task/RDMP-180 Add instance settings table (#1820)

* working settings interface

* add documentation

* add missing files

* update namespace

* add icon

* update from run

* make key unique

* add tests

* update tests

* update for tests

* fix unique name issue

* tidy up

* tidy up from review

* works

* nested deprications

* recursive deprication

* tidy up

* add newline

* Task/rdmp 174 dqe improvements (#1849)

* working scallable graph

* add changelog

* add axis override

* interim

* working increments

* working ui refresh

* update changelog

* tidy up code

* add missing file

* tidy up

* Task/rdmp 155 migrate catalogue tables (#1805)

* start of UI

* interim

* working switch

* improved ui

* fix build

* rename duped file

* imterim

* add checks

* start of tests

* local tests  working

* add tests

* improved ui

* tidy up

* add single item use

* broken test

* updated tests

* tidy up imports

* add some documentation

* fix docume... (continued)

Run Details

10912 of 20750 branches covered (52.59%)

Branch coverage included in aggregate %.

369 of 831 new or added lines in 38 files covered. (44.4%)

375 existing lines in 25 files now uncovered.

30965 of 53135 relevant lines covered (58.28%)

7845.71 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 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 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 dependant 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!
NEW 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(	×
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(	×
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!
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 / 9758065908

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