9859858140

Committed 09 Jul 2024 03:24PM UTC coverage: 56.679% (-0.2%) from 56.916%

Build # 9859858140

Build Type

push

github

Committed by

JFriel

Commit Message

update

Run Details

10912 of 20750 branches covered (52.59%)

Branch coverage included in aggregate %.

30965 of 53135 relevant lines covered (58.28%)

7908.05 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 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;
        var globals1 = globals?.ToArray() ?? Array.Empty<ISqlParameter>();
        TopX = -1;

        SQLOutOfDate = true;

        foreach (var parameter in globals1)
            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 object oSQLLock = new();	358✔
39	private string _sql;
40
41	public string SQL
42	{
43	get
44	{
45	lock (oSQLLock)	362✔
46	{
47	if (SQLOutOfDate)	362✔
48	RegenerateSQL();	70✔
49	return _sql;	362✔
50	}
51	}	362✔
52	}
53
54	public int TopX { get; set; }	358✔
55
56	private CohortAggregateContainer container;
57	private AggregateConfiguration configuration;
58
59	public ExternalDatabaseServer CacheServer
60	{
61	get => _cacheServer;	358✔
62	set
63	{
64	_cacheServer = value;	158✔
65	SQLOutOfDate = true;	158✔
66	}	158✔
67	}
68
69	public ParameterManager ParameterManager = new();	358✔
70
71
72	private CohortQueryBuilderHelper helper;
73	public CohortQueryBuilderResult Results { get; private set; }	2,534✔
74
75	#region constructors
76
77	//Constructors - This one is the base one called by all others
78	private CohortQueryBuilder(IEnumerable<ISqlParameter> globals, ICoreChildProvider childProvider)	358✔
79	{
80	_childProvider = childProvider;	358✔
81	var globals1 = globals?.ToArray() ?? Array.Empty<ISqlParameter>();	358✔
82	TopX = -1;	358✔
83
84	SQLOutOfDate = true;	358✔
85
86	foreach (var parameter in globals1)	724✔
87	ParameterManager.AddGlobalParameter(parameter);	4✔
88	}	358✔
89
90	public CohortQueryBuilder(CohortIdentificationConfiguration configuration, ICoreChildProvider childProvider) : this(	46✔
91	configuration.GetAllParameters(), childProvider)	46✔
92	{
93	if (configuration == null)	46!
94	throw new QueryBuildingException("Configuration has not been set yet");	×
95
96	if (configuration.RootCohortAggregateContainer_ID == null)	46!
97	throw new QueryBuildingException(	×
98	$"Root container not set on CohortIdentificationConfiguration {configuration}");	×
99
100	if (configuration.QueryCachingServer_ID != null)	46✔
101	CacheServer = configuration.QueryCachingServer;	12✔
102
103	//set ourselves up to run with the root container
104	container = configuration.RootCohortAggregateContainer;	46✔
105
106	SetChildProviderIfNull();	46✔
107	}	46✔
108
109	public CohortQueryBuilder(CohortAggregateContainer c, IEnumerable<ISqlParameter> globals,
110	ICoreChildProvider childProvider) : this(globals, childProvider)	122✔
111	{
112	//set ourselves up to run with the root container
113	container = c;	122✔
114
115	SetChildProviderIfNull();	122✔
116	}	122✔
117
118	public CohortQueryBuilder(AggregateConfiguration config, IEnumerable<ISqlParameter> globals,
119	ICoreChildProvider childProvider) : this(globals, childProvider)	190✔
120	{
121	//set ourselves up to run with the root container
122	configuration = config;	190✔
123
124	SetChildProviderIfNull();	190✔
125	}	190✔
126
127	private void SetChildProviderIfNull()
128	{
129	_childProvider ??= new CatalogueChildProvider(	358✔
130	configuration?.CatalogueRepository ?? container.CatalogueRepository, null, null, null);	358✔
131	}	358✔
132
133	#endregion
134
135	public string GetDatasetSampleSQL(int topX = 1000, ICoreChildProvider childProvider = null)
136	{
137	if (configuration == null)	6!
138	throw new NotSupportedException(	×
139	"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)");	×
140
141	//Show the user all the fields (*) unless there is a HAVING or it is a Patient Index Table.
142	var selectList =	6✔
143	string.IsNullOrWhiteSpace(configuration.HavingSQL) && !configuration.IsJoinablePatientIndexTable()	6✔
144	? "*"	6✔
145	: null;	6✔
146
147	RecreateHelpers(new QueryBuilderCustomArgs(selectList, "" /removes distinct/, topX), CancellationToken.None);	6✔
148
149	Results.BuildFor(configuration, ParameterManager);	6✔
150
151	var sampleSQL = Results.Sql;	6✔
152
153	//get resolved parameters for the select * query
154	var finalParams = ParameterManager.GetFinalResolvedParametersList().ToArray();	6✔
155
156	if (!finalParams.Any()) return sampleSQL;	10✔
157
158	var parameterSql = QueryBuilder.GetParameterDeclarationSQL(finalParams);	2✔
159	return $"{parameterSql}{Environment.NewLine}{sampleSQL}";	2✔
160	}
161
162	public void RegenerateSQL()
163	{
164	RegenerateSQL(CancellationToken.None);	70✔
165	}	70✔
166
167	public void RegenerateSQL(CancellationToken cancellationToken)
168	{
169	RecreateHelpers(null, cancellationToken);	352✔
170
171	ParameterManager.ClearNonGlobals();	352✔
172
173	Results.StopContainerWhenYouReach = _stopContainerWhenYouReach;	352✔
174
175	if (container != null)	352✔
176	Results.BuildFor(container,	168✔
177	ParameterManager); //user constructed us with a container (and possibly subcontainers even - any one of them chock full of aggregates)	168✔
178	else
179	Results.BuildFor(configuration,	184✔
180	ParameterManager); //user constructed us without a container, he only cares about 1 aggregate	184✔
181
182	_sql = Results.Sql;	332✔
183
184	//Still finalise the ParameterManager even if we are not writing out the parameters so that it is in the Finalized state
185	var finalParameters = ParameterManager.GetFinalResolvedParametersList();	332✔
186
187	if (!DoNotWriteOutParameters)	332✔
188	{
189	var parameterSql = "";	332✔
190
191	//add the globals
192	foreach (var param in finalParameters)	868✔
193	parameterSql += QueryBuilder.GetParameterDeclarationSQL(param);	102✔
194
195	_sql = parameterSql + _sql;	332✔
196	}
197
198	SQLOutOfDate = false;	332✔
199	}	332✔
200
201	private void RecreateHelpers(QueryBuilderCustomArgs customizations, CancellationToken cancellationToken)
202	{
203	helper = new CohortQueryBuilderHelper();	358✔
204	Results = new CohortQueryBuilderResult(CacheServer, _childProvider, helper, customizations, cancellationToken);	358✔
205	}	358✔
206
207	/// <summary>
208	/// 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
209	/// </summary>
210	public bool DoNotWriteOutParameters
211	{
212	get => _doNotWriteOutParameters;	332✔
213	set
214	{
215	_doNotWriteOutParameters = value;	×
216	SQLOutOfDate = true;	×
217	}	×
218	}
219
220	public bool SQLOutOfDate { get; set; }	1,216✔
221
222	private IOrderable _stopContainerWhenYouReach;
223	private bool _doNotWriteOutParameters;
224	private ExternalDatabaseServer _cacheServer;
225
226
227	public IOrderable StopContainerWhenYouReach
228	{
229	get => _stopContainerWhenYouReach;	×
230	set
231	{
232	_stopContainerWhenYouReach = value;	6✔
233	SQLOutOfDate = true;	6✔
234	}	6✔
235	}
236	}

HicServices / RDMP / 9859858140

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