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

95.5

/Rdmp.Core/QueryCaching/Aggregation/CachedAggregateConfigurationResultsManager.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.Data.Common;
using System.Text.RegularExpressions;
using FAnsi.Discovery;
using FAnsi.Extensions;
using FAnsi.Naming;
using NLog;
using Rdmp.Core.Curation.Data;
using Rdmp.Core.Curation.Data.Aggregation;
using Rdmp.Core.QueryCaching.Aggregation.Arguments;
using Rdmp.Core.ReusableLibraryCode;
using Rdmp.Core.ReusableLibraryCode.DataAccess;

namespace Rdmp.Core.QueryCaching.Aggregation;

/// <summary>
/// Handles the caching and versioning of AggregateConfigurations in a QueryCaching database (QueryCaching.Database.csproj).  Query caching is the process
/// of storing the SQL query and resulting DataTable from running an Aggregate Configuration SQL query (Usually built by an AggregateBuilder).
/// 
/// <para>Caching is vital for large CohortIdentificationConfigurations which feature many complicated subqueries with WHERE conditions and even Patient Index
/// Tables (See JoinableCohortAggregateConfiguration).  The only way some of these queries can finish in a sensible time frame (i.e. minutes instead of days)
/// is to execute each subquery (AggregateConfiguration) and cache the resulting identifier lists with primary key indexes.  The
/// CohortIdentificationConfiguration can then be built into a query that uses the cached results (See CohortQueryBuilder).</para>
/// 
/// <para>In order to ensure the cache is never stale the exact SQL query is stored in a table (CachedAggregateConfigurationResults) so that if the user changes
/// the AggregateConfiguration the cached DataTable is discarded (until the user executes and caches the new version).</para>
/// 
/// <para> CachedAggregateConfigurationResultsManager can cache any CacheCommitArguments (includes not just patient identifier lists but also aggregate graphs and
/// patient index tables).</para>
/// </summary>
public partial class CachedAggregateConfigurationResultsManager
{
    private readonly DiscoveredServer _server;
    private DiscoveredDatabase _database;

    private readonly Logger _logger = LogManager.GetCurrentClassLogger();

    /// <summary>
    /// The name of the table in the query cache which tracks the SQL executed and the resulting tables generated when caching
    /// </summary>
    public const string ResultsManagerTable = "CachedAggregateConfigurationResults";

    public CachedAggregateConfigurationResultsManager(IExternalDatabaseServer server)
    {
        _server = DataAccessPortal.ExpectServer(server, DataAccessContext.InternalDataProcessing);
        _database = DataAccessPortal.ExpectDatabase(server, DataAccessContext.InternalDataProcessing);
    }

    public const string CachingPrefix = "/*Cached:";

    public IHasFullyQualifiedNameToo GetLatestResultsTableUnsafe(AggregateConfiguration configuration,
        AggregateOperation operation) => GetLatestResultsTableUnsafe(configuration, operation, out _);

    public IHasFullyQualifiedNameToo GetLatestResultsTableUnsafe(AggregateConfiguration configuration,
        AggregateOperation operation, out string sql)
    {
        var syntax = _database.Server.GetQuerySyntaxHelper();
        var mgrTable = _database.ExpectTable(ResultsManagerTable);

        using (var con = _server.GetConnection())
        {
            con.Open();
            using var cmd = DatabaseCommandHelper.GetCommand(
                $@"Select 
{syntax.EnsureWrapped("TableName")},
{syntax.EnsureWrapped("SqlExecuted")} from {mgrTable.GetFullyQualifiedName()}
WHERE {syntax.EnsureWrapped("AggregateConfiguration_ID")} = {configuration.ID}
AND {syntax.EnsureWrapped("Operation")} = '{operation}'", con);
            using var r = cmd.ExecuteReader();
            if (r.Read())
            {
                var tableName = r["TableName"].ToString();
                sql = r["SqlExecuted"] as string;
                return _database.ExpectTable(tableName);
            }
        }

        sql = null;
        return null;
    }

    /// <summary>
    /// Returns the name of the query cache results table for <paramref name="configuration"/> if the <paramref name="currentSql"/> matches
    /// the SQL run when the cache result was generated.  Returns null if no cache result is found or there are changes in the <paramref name="currentSql"/>
    /// since the cache result was generated.
    /// </summary>
    /// <param name="configuration"></param>
    /// <param name="operation"></param>
    /// <param name="currentSql"></param>
    /// <returns></returns>
    public IHasFullyQualifiedNameToo GetLatestResultsTable(AggregateConfiguration configuration,
        AggregateOperation operation, string currentSql)
    {
        var syntax = _database.Server.GetQuerySyntaxHelper();
        var mgrTable = _database.ExpectTable(ResultsManagerTable);

        using var con = _server.GetConnection();
        con.Open();

        using var cmd = DatabaseCommandHelper.GetCommand(
            $@"Select 
{syntax.EnsureWrapped("TableName")},
{syntax.EnsureWrapped("SqlExecuted")} 
from {mgrTable.GetFullyQualifiedName()} 
WHERE 
{syntax.EnsureWrapped("AggregateConfiguration_ID")} = {configuration.ID} AND
{syntax.EnsureWrapped("Operation")} = '{operation}'", con);
        using var r = cmd.ExecuteReader();
        if (r.Read())
        {
            if (IsMatchOnSqlExecuted(r, currentSql))
            {
                var tableName = r["TableName"].ToString();
                return _database.ExpectTable(tableName);
            }

            return null; //this means that there was outdated SQL, we could show this to user at some point
        }

        return null;
    }

    private bool IsMatchOnSqlExecuted(DbDataReader r, string currentSql)
    {
        //replace all white space with single space
        var standardisedDatabaseSql = Spaces().Replace(r["SqlExecuted"].ToString(), " ");
        var standardisedUsersSql = Spaces().Replace(currentSql, " ");

        var match = standardisedDatabaseSql.ToLower().Trim().Equals(standardisedUsersSql.ToLower().Trim());

        if (match) return true;

        _logger.Info("Cache Miss:");
        _logger.Info("Current Sql:");
        _logger.Info(standardisedUsersSql);
        _logger.Info("Cached Sql:");
        _logger.Info(standardisedDatabaseSql);
        return false;
    }

    public void CommitResults(CacheCommitArguments arguments)
    {
        var configuration = arguments.Configuration;
        var operation = arguments.Operation;

        DeleteCacheEntryIfAny(configuration, operation);

        //Do not change Types of source columns unless there is an explicit override
        arguments.Results.SetDoNotReType(true);

        using var con = _server.GetConnection();
        con.Open();

        var nameWeWillGiveTableInCache = $"{operation}_AggregateConfiguration{configuration.ID}";

        //either it has no name or it already has name we want so its ok
        arguments.Results.TableName = nameWeWillGiveTableInCache;

        //add explicit types
        var tbl = _database.ExpectTable(nameWeWillGiveTableInCache);
        if (tbl.Exists())
            tbl.Drop();

        tbl = _database.CreateTable(nameWeWillGiveTableInCache, arguments.Results, arguments.ExplicitColumns);

        if (!tbl.Exists())
            throw new Exception("Cache table did not exist even after CreateTable completed without error!");

        var mgrTable = _database.ExpectTable(ResultsManagerTable);

        mgrTable.Insert(new Dictionary<string, object>
        {
            { "Committer", Environment.UserName },
            { "AggregateConfiguration_ID", configuration.ID },
            { "SqlExecuted", arguments.SQL.Trim() },
            { "Operation", operation.ToString() },
            { "TableName", tbl.GetRuntimeName() }
        });

        arguments.CommitTableDataCompleted(tbl);
    }

    /// <summary>
    /// Deletes any cache entries for <paramref name="configuration"/> in its role as <paramref name="operation"/>
    /// </summary>
    /// <param name="configuration"></param>
    /// <param name="operation"></param>
    /// <returns>True if a cache entry was found and deleted otherwise false</returns>
    /// <exception cref="Exception"></exception>
    public bool DeleteCacheEntryIfAny(AggregateConfiguration configuration, AggregateOperation operation)
    {
        var table = GetLatestResultsTableUnsafe(configuration, operation);
        var mgrTable = _database.ExpectTable(ResultsManagerTable);

        if (table != null)
        {
            using var con = _server.GetConnection();
            con.Open();

            //drop the data
            _database.ExpectTable(table.GetRuntimeName()).Drop();

            //delete the record!
            using var cmd = DatabaseCommandHelper.GetCommand(
                $"DELETE FROM {mgrTable.GetFullyQualifiedName()} WHERE AggregateConfiguration_ID = {configuration.ID} AND Operation = '{operation}'",
                con);
            var deletedRows = cmd.ExecuteNonQuery();
            return deletedRows != 1
                ? throw new Exception(
                    $"Expected exactly 1 record in CachedAggregateConfigurationResults to be deleted when erasing its record of operation {operation} but there were {deletedRows} affected records")
                : true;
        }

        return false;
    }

    [GeneratedRegex("\\s+")]
    private static partial Regex Spaces();
}

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.Data.Common;
10	using System.Text.RegularExpressions;
11	using FAnsi.Discovery;
12	using FAnsi.Extensions;
13	using FAnsi.Naming;
14	using NLog;
15	using Rdmp.Core.Curation.Data;
16	using Rdmp.Core.Curation.Data.Aggregation;
17	using Rdmp.Core.QueryCaching.Aggregation.Arguments;
18	using Rdmp.Core.ReusableLibraryCode;
19	using Rdmp.Core.ReusableLibraryCode.DataAccess;
20
21	namespace Rdmp.Core.QueryCaching.Aggregation;
22
23	/// <summary>
24	/// Handles the caching and versioning of AggregateConfigurations in a QueryCaching database (QueryCaching.Database.csproj). Query caching is the process
25	/// of storing the SQL query and resulting DataTable from running an Aggregate Configuration SQL query (Usually built by an AggregateBuilder).
26	///
27	/// <para>Caching is vital for large CohortIdentificationConfigurations which feature many complicated subqueries with WHERE conditions and even Patient Index
28	/// Tables (See JoinableCohortAggregateConfiguration). The only way some of these queries can finish in a sensible time frame (i.e. minutes instead of days)
29	/// is to execute each subquery (AggregateConfiguration) and cache the resulting identifier lists with primary key indexes. The
30	/// CohortIdentificationConfiguration can then be built into a query that uses the cached results (See CohortQueryBuilder).</para>
31	///
32	/// <para>In order to ensure the cache is never stale the exact SQL query is stored in a table (CachedAggregateConfigurationResults) so that if the user changes
33	/// the AggregateConfiguration the cached DataTable is discarded (until the user executes and caches the new version).</para>
34	///
35	/// <para> CachedAggregateConfigurationResultsManager can cache any CacheCommitArguments (includes not just patient identifier lists but also aggregate graphs and
36	/// patient index tables).</para>
37	/// </summary>
38	public partial class CachedAggregateConfigurationResultsManager
39	{
40	private readonly DiscoveredServer _server;
41	private DiscoveredDatabase _database;
42
43	private readonly Logger _logger = LogManager.GetCurrentClassLogger();	248✔
44
45	/// <summary>
46	/// The name of the table in the query cache which tracks the SQL executed and the resulting tables generated when caching
47	/// </summary>
48	public const string ResultsManagerTable = "CachedAggregateConfigurationResults";
49
50	public CachedAggregateConfigurationResultsManager(IExternalDatabaseServer server)	248✔
51	{
52	_server = DataAccessPortal.ExpectServer(server, DataAccessContext.InternalDataProcessing);	248✔
53	_database = DataAccessPortal.ExpectDatabase(server, DataAccessContext.InternalDataProcessing);	248✔
54	}	248✔
55
56	public const string CachingPrefix = "/*Cached:";
57
58	public IHasFullyQualifiedNameToo GetLatestResultsTableUnsafe(AggregateConfiguration configuration,
59	AggregateOperation operation) => GetLatestResultsTableUnsafe(configuration, operation, out _);	132✔
60
61	public IHasFullyQualifiedNameToo GetLatestResultsTableUnsafe(AggregateConfiguration configuration,
62	AggregateOperation operation, out string sql)
63	{
64	var syntax = _database.Server.GetQuerySyntaxHelper();	150✔
65	var mgrTable = _database.ExpectTable(ResultsManagerTable);	150✔
66
67	using (var con = _server.GetConnection())	150✔
68	{
69	con.Open();	150✔
70	using var cmd = DatabaseCommandHelper.GetCommand(	150✔
71	$@"Select	150✔
72	{syntax.EnsureWrapped("TableName")},	150✔
73	{syntax.EnsureWrapped("SqlExecuted")} from {mgrTable.GetFullyQualifiedName()}	150✔
74	WHERE {syntax.EnsureWrapped("AggregateConfiguration_ID")} = {configuration.ID}	150✔
75	AND {syntax.EnsureWrapped("Operation")} = '{operation}'", con);	150✔
76	using var r = cmd.ExecuteReader();	150✔
77	if (r.Read())	150✔
78	{
79	var tableName = r["TableName"].ToString();	34✔
80	sql = r["SqlExecuted"] as string;	34✔
81	return _database.ExpectTable(tableName);	34✔
82	}
83	}
84
85	sql = null;	116✔
86	return null;	116✔
87	}	34✔
88
89	/// <summary>
90	/// Returns the name of the query cache results table for <paramref name="configuration"/> if the <paramref name="currentSql"/> matches
91	/// the SQL run when the cache result was generated. Returns null if no cache result is found or there are changes in the <paramref name="currentSql"/>
92	/// since the cache result was generated.
93	/// </summary>
94	/// <param name="configuration"></param>
95	/// <param name="operation"></param>
96	/// <param name="currentSql"></param>
97	/// <returns></returns>
98	public IHasFullyQualifiedNameToo GetLatestResultsTable(AggregateConfiguration configuration,
99	AggregateOperation operation, string currentSql)
100	{
101	var syntax = _database.Server.GetQuerySyntaxHelper();	218✔
102	var mgrTable = _database.ExpectTable(ResultsManagerTable);	218✔
103
104	using var con = _server.GetConnection();	218✔
105	con.Open();	218✔
106
107	using var cmd = DatabaseCommandHelper.GetCommand(	218✔
108	$@"Select	218✔
109	{syntax.EnsureWrapped("TableName")},	218✔
110	{syntax.EnsureWrapped("SqlExecuted")}	218✔
111	from {mgrTable.GetFullyQualifiedName()}	218✔
112	WHERE	218✔
113	{syntax.EnsureWrapped("AggregateConfiguration_ID")} = {configuration.ID} AND	218✔
114	{syntax.EnsureWrapped("Operation")} = '{operation}'", con);	218✔
115	using var r = cmd.ExecuteReader();	218✔
116	if (r.Read())	218✔
117	{
118	if (IsMatchOnSqlExecuted(r, currentSql))	116✔
119	{
120	var tableName = r["TableName"].ToString();	104✔
121	return _database.ExpectTable(tableName);	104✔
122	}
123
124	return null; //this means that there was outdated SQL, we could show this to user at some point	12✔
125	}
126
127	return null;	102✔
128	}	218✔
129
130	private bool IsMatchOnSqlExecuted(DbDataReader r, string currentSql)
131	{
132	//replace all white space with single space
133	var standardisedDatabaseSql = Spaces().Replace(r["SqlExecuted"].ToString(), " ");	116✔
134	var standardisedUsersSql = Spaces().Replace(currentSql, " ");	116✔
135
136	var match = standardisedDatabaseSql.ToLower().Trim().Equals(standardisedUsersSql.ToLower().Trim());	116✔
137
138	if (match) return true;	220✔
139
140	_logger.Info("Cache Miss:");	12✔
141	_logger.Info("Current Sql:");	12✔
142	_logger.Info(standardisedUsersSql);	12✔
143	_logger.Info("Cached Sql:");	12✔
144	_logger.Info(standardisedDatabaseSql);	12✔
145	return false;	12✔
146	}
147
148	public void CommitResults(CacheCommitArguments arguments)
149	{
150	var configuration = arguments.Configuration;	96✔
151	var operation = arguments.Operation;	96✔
152
153	DeleteCacheEntryIfAny(configuration, operation);	96✔
154
155	//Do not change Types of source columns unless there is an explicit override
156	arguments.Results.SetDoNotReType(true);	96✔
157
158	using var con = _server.GetConnection();	96✔
159	con.Open();	96✔
160
161	var nameWeWillGiveTableInCache = $"{operation}_AggregateConfiguration{configuration.ID}";	96✔
162
163	//either it has no name or it already has name we want so its ok
164	arguments.Results.TableName = nameWeWillGiveTableInCache;	96✔
165
166	//add explicit types
167	var tbl = _database.ExpectTable(nameWeWillGiveTableInCache);	96✔
168	if (tbl.Exists())	96!
169	tbl.Drop();	×
170
171	tbl = _database.CreateTable(nameWeWillGiveTableInCache, arguments.Results, arguments.ExplicitColumns);	96✔
172
173	if (!tbl.Exists())	96!
174	throw new Exception("Cache table did not exist even after CreateTable completed without error!");	×
175
176	var mgrTable = _database.ExpectTable(ResultsManagerTable);	96✔
177
178	mgrTable.Insert(new Dictionary<string, object>	96✔
179	{	96✔
180	{ "Committer", Environment.UserName },	96✔
181	{ "AggregateConfiguration_ID", configuration.ID },	96✔
182	{ "SqlExecuted", arguments.SQL.Trim() },	96✔
183	{ "Operation", operation.ToString() },	96✔
184	{ "TableName", tbl.GetRuntimeName() }	96✔
185	});	96✔
186
187	arguments.CommitTableDataCompleted(tbl);	96✔
188	}	188✔
189
190	/// <summary>
191	/// Deletes any cache entries for <paramref name="configuration"/> in its role as <paramref name="operation"/>
192	/// </summary>
193	/// <param name="configuration"></param>
194	/// <param name="operation"></param>
195	/// <returns>True if a cache entry was found and deleted otherwise false</returns>
196	/// <exception cref="Exception"></exception>
197	public bool DeleteCacheEntryIfAny(AggregateConfiguration configuration, AggregateOperation operation)
198	{
199	var table = GetLatestResultsTableUnsafe(configuration, operation);	120✔
200	var mgrTable = _database.ExpectTable(ResultsManagerTable);	120✔
201
202	if (table != null)	120✔
203	{
204	using var con = _server.GetConnection();	8✔
205	con.Open();	8✔
206
207	//drop the data
208	_database.ExpectTable(table.GetRuntimeName()).Drop();	8✔
209
210	//delete the record!
211	using var cmd = DatabaseCommandHelper.GetCommand(	8✔
212	$"DELETE FROM {mgrTable.GetFullyQualifiedName()} WHERE AggregateConfiguration_ID = {configuration.ID} AND Operation = '{operation}'",	8✔
213	con);	8✔
214	var deletedRows = cmd.ExecuteNonQuery();	8✔
215	return deletedRows != 1	8!
216	? throw new Exception(	8✔
217	$"Expected exactly 1 record in CachedAggregateConfigurationResults to be deleted when erasing its record of operation {operation} but there were {deletedRows} affected records")	8✔
218	: true;	8✔
219	}
220
221	return false;	112✔
222	}	8✔
223
224	[GeneratedRegex("\\s+")]
225	private static partial Regex Spaces();
226	}

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