6245535001

Committed 20 Sep 2023 07:44AM UTC coverage: 57.013%. First build

Build # 6245535001

Build Type

push

github

Committed by

web-flow

Commit Message

8.1.0 Release (#1628)

* Bump Newtonsoft.Json from 13.0.1 to 13.0.2

Bumps [Newtonsoft.Json](https://github.com/JamesNK/Newtonsoft.Json) from 13.0.1 to 13.0.2.
- [Release notes](https://github.com/JamesNK/Newtonsoft.Json/releases)
- [Commits](https://github.com/JamesNK/Newtonsoft.Json/compare/13.0.1...13.0.2)

---
updated-dependencies:
- dependency-name: Newtonsoft.Json
  dependency-type: direct:production
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump NLog from 5.0.5 to 5.1.0

Bumps [NLog](https://github.com/NLog/NLog) from 5.0.5 to 5.1.0.
- [Release notes](https://github.com/NLog/NLog/releases)
- [Changelog](https://github.com/NLog/NLog/blob/dev/CHANGELOG.md)
- [Commits](https://github.com/NLog/NLog/compare/v5.0.5...v5.1.0)

---
updated-dependencies:
- dependency-name: NLog
  dependency-type: direct:production
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump NLog from 5.0.5 to 5.1.0

* Fix -r flag - should have been --results-directory all along

* Bump Newtonsoft.Json from 13.0.1 to 13.0.2

* Bump YamlDotNet from 12.0.2 to 12.1.0

Bumps [YamlDotNet](https://github.com/aaubry/YamlDotNet) from 12.0.2 to 12.1.0.
- [Release notes](https://github.com/aaubry/YamlDotNet/releases)
- [Commits](https://github.com/aaubry/YamlDotNet/compare/v12.0.2...v12.1.0)

---
updated-dependencies:
- dependency-name: YamlDotNet
  dependency-type: direct:production
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump Moq from 4.18.2 to 4.18.3

Bumps [Moq](https://github.com/moq/moq4) from 4.18.2 to 4.18.3.
- [Release notes](https://github.com/moq/moq4/releases)
- [Changelog](https://github.com/moq/moq4/blob/main/CHANGELOG.md)
- [Commits](https://github.com/moq/moq4/compare/v4.18.2...v4.18.3)

---
updated-dependencies:
- dependency-name: Moq
... (continued)

Run Details

10732 of 20257 branches covered (0.0%)

Branch coverage included in aggregate %.

48141 of 48141 new or added lines in 1086 files covered. (100.0%)

30685 of 52388 relevant lines covered (58.57%)

7387.88 hits per line

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

90.24

/Rdmp.Core/QueryBuilding/CohortQueryBuilder.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.Collections.Generic;
using System.Linq;
using System.Threading;
using Rdmp.Core.Curation.Data;
using Rdmp.Core.Curation.Data.Aggregation;
using Rdmp.Core.Curation.Data.Cohort;
using Rdmp.Core.Providers;
using Rdmp.Core.QueryBuilding.Parameters;

namespace Rdmp.Core.QueryBuilding;

/// <summary>
/// Builds complex cohort identification queries by combining subqueries with SQL set operations (UNION / INTERSECT / EXCEPT).  Cohort identification
/// sub queries fundamentally take the form of 'Select distinct patientId from TableX'.  All the complexity comes in the form of IFilters (WHERE Sql),
/// parameters, using cached query results, patient index tables etc.
/// 
/// <para>User cohort identification queries are all create under a CohortIdentificationConfiguration which will have a single root CohortAggregateContainer.  A
/// final count for the number of patients in the cohort can be determined by running the root CohortAggregateContainer.  The user will often want to run each
/// sub query independently however to get counts for each dataset involved.  Sub queries are defined in AggregateConfigurations.</para>
/// 
/// <para>In order to build complex multi table queries across multiple datasets with complex where/parameter/join logic with decent performance RDMP supports
/// caching.  Caching involves executing each sub query (AggregateConfiguration) and storing the resulting patient identifier list in an indexed table on
/// the caching server (See CachedAggregateConfigurationResultsManager).  These cached queries are versioned by the SQL used to generate them (to avoid stale
/// result lists).  Where available CohortQueryBuilder will use the cached result list instead of running the full query since it runs drastically faster.</para>
/// 
/// <para>The SQL code for individual queries is created by CohortQueryBuilderHelper (using AggregateBuilder).</para>
/// </summary>
public class CohortQueryBuilder
{
    private ICoreChildProvider _childProvider;
    private readonly ISqlParameter[] _globals;
    private object oSQLLock = new();
    private string _sql;

    public string SQL
    {
        get
        {
            lock (oSQLLock)
            {
                if (SQLOutOfDate)
                    RegenerateSQL();
                return _sql;
            }
        }
    }

    public int TopX { get; set; }

    private CohortAggregateContainer container;
    private AggregateConfiguration configuration;

    public ExternalDatabaseServer CacheServer
    {
        get => _cacheServer;
        set
        {
            _cacheServer = value;
            SQLOutOfDate = true;
        }
    }

    public ParameterManager ParameterManager = new();


    private CohortQueryBuilderHelper helper;
    public CohortQueryBuilderResult Results { get; private set; }

    #region constructors

    //Constructors - This one is the base one called by all others
    private CohortQueryBuilder(IEnumerable<ISqlParameter> globals, ICoreChildProvider childProvider)
    {
        _childProvider = childProvider;
        _globals = globals?.ToArray() ?? Array.Empty<ISqlParameter>();
        TopX = -1;

        SQLOutOfDate = true;

        foreach (var parameter in _globals)
            ParameterManager.AddGlobalParameter(parameter);
    }

    public CohortQueryBuilder(CohortIdentificationConfiguration configuration, ICoreChildProvider childProvider) : this(
        configuration.GetAllParameters(), childProvider)
    {
        if (configuration == null)
            throw new QueryBuildingException("Configuration has not been set yet");

        if (configuration.RootCohortAggregateContainer_ID == null)
            throw new QueryBuildingException(
                $"Root container not set on CohortIdentificationConfiguration {configuration}");

        if (configuration.QueryCachingServer_ID != null)
            CacheServer = configuration.QueryCachingServer;

        //set ourselves up to run with the root container
        container = configuration.RootCohortAggregateContainer;

        SetChildProviderIfNull();
    }

    public CohortQueryBuilder(CohortAggregateContainer c, IEnumerable<ISqlParameter> globals,
        ICoreChildProvider childProvider) : this(globals, childProvider)
    {
        //set ourselves up to run with the root container
        container = c;

        SetChildProviderIfNull();
    }

    public CohortQueryBuilder(AggregateConfiguration config, IEnumerable<ISqlParameter> globals,
        ICoreChildProvider childProvider) : this(globals, childProvider)
    {
        //set ourselves up to run with the root container
        configuration = config;

        SetChildProviderIfNull();
    }

    private void SetChildProviderIfNull()
    {
        _childProvider ??= new CatalogueChildProvider(
            configuration?.CatalogueRepository ?? container.CatalogueRepository, null, null, null);
    }

    #endregion

    public string GetDatasetSampleSQL(int topX = 1000, ICoreChildProvider childProvider = null)
    {
        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)");

        //Show the user all the fields (*) unless there is a HAVING or it is a Patient Index Table.
        var selectList =
            string.IsNullOrWhiteSpace(configuration.HavingSQL) && !configuration.IsJoinablePatientIndexTable()
                ? "*"
                : null;

        RecreateHelpers(new QueryBuilderCustomArgs(selectList, "" /*removes distinct*/, topX), CancellationToken.None);

        Results.BuildFor(configuration, ParameterManager);

        var sampleSQL = Results.Sql;

        //get resolved parameters for the select * query
        var finalParams = ParameterManager.GetFinalResolvedParametersList().ToArray();

        if (!finalParams.Any()) return sampleSQL;

        var parameterSql = QueryBuilder.GetParameterDeclarationSQL(finalParams);
        return $"{parameterSql}{Environment.NewLine}{sampleSQL}";
    }

    public void RegenerateSQL()
    {
        RegenerateSQL(CancellationToken.None);
    }

    public void RegenerateSQL(CancellationToken cancellationToken)
    {
        RecreateHelpers(null, cancellationToken);

        ParameterManager.ClearNonGlobals();

        Results.StopContainerWhenYouReach = _stopContainerWhenYouReach;

        if (container != null)
            Results.BuildFor(container,
                ParameterManager); //user constructed us with a container (and possibly subcontainers even - any one of them chock full of aggregates)
        else
            Results.BuildFor(configuration,
                ParameterManager); //user constructed us without a container, he only cares about 1 aggregate

        _sql = Results.Sql;

        //Still finalise the ParameterManager even if we are not writing out the parameters so that it is in the Finalized state
        var finalParameters = ParameterManager.GetFinalResolvedParametersList();

        if (!DoNotWriteOutParameters)
        {
            var parameterSql = "";

            //add the globals
            foreach (var param in finalParameters)
                parameterSql += QueryBuilder.GetParameterDeclarationSQL(param);

            _sql = parameterSql + _sql;
        }

        SQLOutOfDate = false;
    }

    private void RecreateHelpers(QueryBuilderCustomArgs customizations, CancellationToken cancellationToken)
    {
        helper = new CohortQueryBuilderHelper();
        Results = new CohortQueryBuilderResult(CacheServer, _childProvider, helper, customizations, cancellationToken);
    }

    /// <summary>
    /// Tells the Builder not to write out parameter SQL, unlike AggregateBuilder this will not clear the ParameterManager it will just hide them from the SQL output
    /// </summary>
    public bool DoNotWriteOutParameters
    {
        get => _doNotWriteOutParameters;
        set
        {
            _doNotWriteOutParameters = value;
            SQLOutOfDate = true;
        }
    }

    public bool SQLOutOfDate { get; set; }

    private IOrderable _stopContainerWhenYouReach;
    private bool _doNotWriteOutParameters;
    private ExternalDatabaseServer _cacheServer;


    public IOrderable StopContainerWhenYouReach
    {
        get => _stopContainerWhenYouReach;
        set
        {
            _stopContainerWhenYouReach = value;
            SQLOutOfDate = true;
        }
    }
}

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.Collections.Generic;
9	using System.Linq;
10	using System.Threading;
11	using Rdmp.Core.Curation.Data;
12	using Rdmp.Core.Curation.Data.Aggregation;
13	using Rdmp.Core.Curation.Data.Cohort;
14	using Rdmp.Core.Providers;
15	using Rdmp.Core.QueryBuilding.Parameters;
16
17	namespace Rdmp.Core.QueryBuilding;
18
19	/// <summary>
20	/// Builds complex cohort identification queries by combining subqueries with SQL set operations (UNION / INTERSECT / EXCEPT). Cohort identification
21	/// sub queries fundamentally take the form of 'Select distinct patientId from TableX'. All the complexity comes in the form of IFilters (WHERE Sql),
22	/// parameters, using cached query results, patient index tables etc.
23	///
24	/// <para>User cohort identification queries are all create under a CohortIdentificationConfiguration which will have a single root CohortAggregateContainer. A
25	/// final count for the number of patients in the cohort can be determined by running the root CohortAggregateContainer. The user will often want to run each
26	/// sub query independently however to get counts for each dataset involved. Sub queries are defined in AggregateConfigurations.</para>
27	///
28	/// <para>In order to build complex multi table queries across multiple datasets with complex where/parameter/join logic with decent performance RDMP supports
29	/// caching. Caching involves executing each sub query (AggregateConfiguration) and storing the resulting patient identifier list in an indexed table on
30	/// the caching server (See CachedAggregateConfigurationResultsManager). These cached queries are versioned by the SQL used to generate them (to avoid stale
31	/// result lists). Where available CohortQueryBuilder will use the cached result list instead of running the full query since it runs drastically faster.</para>
32	///
33	/// <para>The SQL code for individual queries is created by CohortQueryBuilderHelper (using AggregateBuilder).</para>
34	/// </summary>
35	public class CohortQueryBuilder
36	{
37	private ICoreChildProvider _childProvider;
38	private readonly ISqlParameter[] _globals;
39	private object oSQLLock = new();	336✔
40	private string _sql;
41
42	public string SQL
43	{
44	get
45	{
46	lock (oSQLLock)	338✔
47	{
48	if (SQLOutOfDate)	338✔
49	RegenerateSQL();	66✔
50	return _sql;	338✔
51	}
52	}	338✔
53	}
54
55	public int TopX { get; set; }	336✔
56
57	private CohortAggregateContainer container;
58	private AggregateConfiguration configuration;
59
60	public ExternalDatabaseServer CacheServer
61	{
62	get => _cacheServer;	336✔
63	set
64	{
65	_cacheServer = value;	154✔
66	SQLOutOfDate = true;	154✔
67	}	154✔
68	}
69
70	public ParameterManager ParameterManager = new();	336✔
71
72
73	private CohortQueryBuilderHelper helper;
74	public CohortQueryBuilderResult Results { get; private set; }	2,374✔
75
76	#region constructors
77
78	//Constructors - This one is the base one called by all others
79	private CohortQueryBuilder(IEnumerable<ISqlParameter> globals, ICoreChildProvider childProvider)	336✔
80	{
81	_childProvider = childProvider;	336✔
82	_globals = globals?.ToArray() ?? Array.Empty<ISqlParameter>();	336✔
83	TopX = -1;	336✔
84
85	SQLOutOfDate = true;	336✔
86
87	foreach (var parameter in _globals)	680✔
88	ParameterManager.AddGlobalParameter(parameter);	4✔
89	}	336✔
90
91	public CohortQueryBuilder(CohortIdentificationConfiguration configuration, ICoreChildProvider childProvider) : this(	42✔
92	configuration.GetAllParameters(), childProvider)	42✔
93	{
94	if (configuration == null)	42!
95	throw new QueryBuildingException("Configuration has not been set yet");	×
96
97	if (configuration.RootCohortAggregateContainer_ID == null)	42!
98	throw new QueryBuildingException(	×
99	$"Root container not set on CohortIdentificationConfiguration {configuration}");	×
100
101	if (configuration.QueryCachingServer_ID != null)	42✔
102	CacheServer = configuration.QueryCachingServer;	8✔
103
104	//set ourselves up to run with the root container
105	container = configuration.RootCohortAggregateContainer;	42✔
106
107	SetChildProviderIfNull();	42✔
108	}	42✔
109
110	public CohortQueryBuilder(CohortAggregateContainer c, IEnumerable<ISqlParameter> globals,
111	ICoreChildProvider childProvider) : this(globals, childProvider)	104✔
112	{
113	//set ourselves up to run with the root container
114	container = c;	104✔
115
116	SetChildProviderIfNull();	104✔
117	}	104✔
118
119	public CohortQueryBuilder(AggregateConfiguration config, IEnumerable<ISqlParameter> globals,
120	ICoreChildProvider childProvider) : this(globals, childProvider)	190✔
121	{
122	//set ourselves up to run with the root container
123	configuration = config;	190✔
124
125	SetChildProviderIfNull();	190✔
126	}	190✔
127
128	private void SetChildProviderIfNull()
129	{
130	_childProvider ??= new CatalogueChildProvider(	336✔
131	configuration?.CatalogueRepository ?? container.CatalogueRepository, null, null, null);	336✔
132	}	336✔
133
134	#endregion
135
136	public string GetDatasetSampleSQL(int topX = 1000, ICoreChildProvider childProvider = null)
137	{
138	if (configuration == null)	6!
139	throw new NotSupportedException(	×
140	"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)");	×
141
142	//Show the user all the fields (*) unless there is a HAVING or it is a Patient Index Table.
143	var selectList =	6✔
144	string.IsNullOrWhiteSpace(configuration.HavingSQL) && !configuration.IsJoinablePatientIndexTable()	6✔
145	? "*"	6✔
146	: null;	6✔
147
148	RecreateHelpers(new QueryBuilderCustomArgs(selectList, "" /removes distinct/, topX), CancellationToken.None);	6✔
149
150	Results.BuildFor(configuration, ParameterManager);	6✔
151
152	var sampleSQL = Results.Sql;	6✔
153
154	//get resolved parameters for the select * query
155	var finalParams = ParameterManager.GetFinalResolvedParametersList().ToArray();	6✔
156
157	if (!finalParams.Any()) return sampleSQL;	10✔
158
159	var parameterSql = QueryBuilder.GetParameterDeclarationSQL(finalParams);	2✔
160	return $"{parameterSql}{Environment.NewLine}{sampleSQL}";	2✔
161	}
162
163	public void RegenerateSQL()
164	{
165	RegenerateSQL(CancellationToken.None);	66✔
166	}	66✔
167
168	public void RegenerateSQL(CancellationToken cancellationToken)
169	{
170	RecreateHelpers(null, cancellationToken);	330✔
171
172	ParameterManager.ClearNonGlobals();	330✔
173
174	Results.StopContainerWhenYouReach = _stopContainerWhenYouReach;	330✔
175
176	if (container != null)	330✔
177	Results.BuildFor(container,	146✔
178	ParameterManager); //user constructed us with a container (and possibly subcontainers even - any one of them chock full of aggregates)	146✔
179	else
180	Results.BuildFor(configuration,	184✔
181	ParameterManager); //user constructed us without a container, he only cares about 1 aggregate	184✔
182
183	_sql = Results.Sql;	310✔
184
185	//Still finalise the ParameterManager even if we are not writing out the parameters so that it is in the Finalized state
186	var finalParameters = ParameterManager.GetFinalResolvedParametersList();	310✔
187
188	if (!DoNotWriteOutParameters)	310✔
189	{
190	var parameterSql = "";	310✔
191
192	//add the globals
193	foreach (var param in finalParameters)	824✔
194	parameterSql += QueryBuilder.GetParameterDeclarationSQL(param);	102✔
195
196	_sql = parameterSql + _sql;	310✔
197	}
198
199	SQLOutOfDate = false;	310✔
200	}	310✔
201
202	private void RecreateHelpers(QueryBuilderCustomArgs customizations, CancellationToken cancellationToken)
203	{
204	helper = new CohortQueryBuilderHelper();	336✔
205	Results = new CohortQueryBuilderResult(CacheServer, _childProvider, helper, customizations, cancellationToken);	336✔
206	}	336✔
207
208	/// <summary>
209	/// Tells the Builder not to write out parameter SQL, unlike AggregateBuilder this will not clear the ParameterManager it will just hide them from the SQL output
210	/// </summary>
211	public bool DoNotWriteOutParameters
212	{
213	get => _doNotWriteOutParameters;	310✔
214	set
215	{
216	_doNotWriteOutParameters = value;	×
217	SQLOutOfDate = true;	×
218	}	×
219	}
220
221	public bool SQLOutOfDate { get; set; }	1,144✔
222
223	private IOrderable _stopContainerWhenYouReach;
224	private bool _doNotWriteOutParameters;
225	private ExternalDatabaseServer _cacheServer;
226
227
228	public IOrderable StopContainerWhenYouReach
229	{
230	get => _stopContainerWhenYouReach;	×
231	set
232	{
233	_stopContainerWhenYouReach = value;	6✔
234	SQLOutOfDate = true;	6✔
235	}	6✔
236	}
237	}

HicServices / RDMP / 6245535001

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