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

93.94

/Rdmp.Core/QueryBuilding/QueryBuilder.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.Text.RegularExpressions;
using FAnsi.Discovery.QuerySyntax;
using Rdmp.Core.Curation.Data;
using Rdmp.Core.MapsDirectlyToDatabaseTable.Injection;
using Rdmp.Core.QueryBuilding.Parameters;
using Rdmp.Core.ReusableLibraryCode.Checks;

namespace Rdmp.Core.QueryBuilding;

/// <summary>
/// This class maintains a list of user defined ExtractionInformation objects.  It can produce SQL which will try to
/// extract this set of ExtractionInformation objects only from the database.  This includes determining which ExtractionInformation
/// are Lookups, which tables the various objects come from, figuring out whether they can be joined by using JoinInfo in the catalogue
/// 
/// <para>It will throw when query SQL if it is not possible to join all the underlying tables or there are any other problems.</para>
/// 
/// <para>You can ask it what is on line X or ask what line number has ExtractionInformation Y on it</para>
/// 
/// <para>ExtractionInformation is sorted by column order prior to generating the SQL (i.e. not the order you add them to the query builder)</para>
/// </summary>
public class QueryBuilder : ISqlQueryBuilder
{
    private readonly ITableInfo[] _forceJoinsToTheseTables;
    private readonly object oSQLLock = new();

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

    /// <inheritdoc/>
    public string LimitationSQL { get; private set; }

    /// <inheritdoc/>
    public List<QueryTimeColumn> SelectColumns { get; private set; }

    /// <inheritdoc/>
    public List<ITableInfo> TablesUsedInQuery { get; private set; }

    /// <inheritdoc/>
    public List<JoinInfo> JoinsUsedInQuery { get; private set; }

    /// <inheritdoc/>
    public List<CustomLine> CustomLines { get; private set; }

    /// <inheritdoc/>
    public CustomLine TopXCustomLine { get; set; }

    /// <inheritdoc/>
    public ParameterManager ParameterManager { get; private set; }

    /// <summary>
    /// Optional field, this specifies where to start gargantuan joins such as when there are 3+ joins and multiple primary key tables e.g. in a star schema.
    /// If this is not set and there are too many JoinInfos defined in the Catalogue then the class will bomb out with the Exception
    /// </summary>
    public ITableInfo PrimaryExtractionTable { get; set; }

    /// <summary>
    /// A container that contains all the subcontainers and filters to be assembled during the query (use a SpontaneouslyInventedFilterContainer if you want to inject your
    /// own container tree at runtime rather than referencing a database entity)
    /// </summary>
    public IContainer RootFilterContainer
    {
        get => _rootFilterContainer;
        set
        {
            _rootFilterContainer = value;
            SQLOutOfDate = true;
        }
    }

    /// <inheritdoc/>
    public bool CheckSyntax { get; set; }


    private string _salt;

    /// <summary>
    /// Only use this if you want IColumns which are marked as requiring Hashing to be hashed.  Once you set this on a QueryEditor all fields so marked will be hashed using the
    /// specified salt
    /// </summary>
    /// <param name="salt">A 3 letter string indicating the desired SALT</param>
    public void SetSalt(string salt)
    {
        if (string.IsNullOrWhiteSpace(salt))
            throw new NullReferenceException("Salt cannot be blank");

        _salt = salt;
    }

    public void SetLimitationSQL(string limitationSQL)
    {
        if (limitationSQL != null && limitationSQL.Contains("top"))
            throw new Exception("Use TopX property instead of limitation SQL to achieve this");

        LimitationSQL = limitationSQL;
        SQLOutOfDate = true;
    }

    /// <inheritdoc/>
    public List<IFilter> Filters { get; private set; }

    /// <summary>
    /// Limits the number of returned rows to the supplied maximum or -1 if there is no maximum
    /// </summary>
    public int TopX
    {
        get => _topX;
        set
        {
            //it already has that value
            if (_topX == value)
                return;

            _topX = value;
            SQLOutOfDate = true;
        }
    }

    private string _sql;

    /// <inheritdoc/>
    public bool SQLOutOfDate { get; set; }

    private IContainer _rootFilterContainer;
    private readonly string _hashingAlgorithm;
    private int _topX;

    public IQuerySyntaxHelper QuerySyntaxHelper { get; set; }

    /// <summary>
    /// Used to build extraction queries based on ExtractionInformation sets
    /// </summary>
    /// <param name="limitationSQL">Any text you want after SELECT to limit the results e.g. "DISTINCT" or "TOP 10"</param>
    /// <param name="hashingAlgorithm"></param>
    /// <param name="forceJoinsToTheseTables"></param>
    public QueryBuilder(string limitationSQL, string hashingAlgorithm, ITableInfo[] forceJoinsToTheseTables = null)
    {
        _forceJoinsToTheseTables = forceJoinsToTheseTables;
        SetLimitationSQL(limitationSQL);
        ParameterManager = new ParameterManager();
        CustomLines = new List<CustomLine>();

        CheckSyntax = true;
        SelectColumns = new List<QueryTimeColumn>();

        _hashingAlgorithm = hashingAlgorithm;

        TopX = -1;
    }

    /// <inheritdoc/>
    public void AddColumnRange(IColumn[] columnsToAdd)
    {
        //add the new ones to the list
        foreach (var col in columnsToAdd)
            AddColumn(col);

        SQLOutOfDate = true;
    }

    /// <inheritdoc/>
    public void AddColumn(IColumn col)
    {
        var toAdd = new QueryTimeColumn(col);

        //if it is new, add it to the list
        if (!SelectColumns.Contains(toAdd))
        {
            SelectColumns.Add(toAdd);
            SQLOutOfDate = true;
        }
    }

    /// <inheritdoc/>
    public CustomLine AddCustomLine(string text, QueryComponent positionToInsert)
    {
        SQLOutOfDate = true;
        return SqlQueryBuilderHelper.AddCustomLine(this, text, positionToInsert);
    }

    /// <summary>
    /// Updates .SQL Property, note that this is automatically called when you query .SQL anyway so you do not need to manually call it.
    /// </summary>
    public void RegenerateSQL()
    {
        var checkNotifier = ThrowImmediatelyCheckNotifier.Quiet;

        _sql = "";

        //reset the Parameter knowledge
        ParameterManager.ClearNonGlobals();

        #region Setup to output the query, where we figure out all the joins etc

        //reset everything

        SelectColumns.Sort();

        //work out all the filters
        Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);

        TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out var primary, _forceJoinsToTheseTables);

        //force join to any TableInfos that would not be normally joined to but the user wants to anyway e.g. if there's WHERE sql that references them but no columns
        if (_forceJoinsToTheseTables != null)
            foreach (var force in _forceJoinsToTheseTables)
                if (!TablesUsedInQuery.Contains(force))
                    TablesUsedInQuery.Add(force);

        PrimaryExtractionTable = primary;

        SqlQueryBuilderHelper.FindLookups(this);

        JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);

        //deal with case when there are no tables in the query or there are only lookup descriptions in the query
        if (TablesUsedInQuery.Count == 0)
            throw new Exception("There are no TablesUsedInQuery in this dataset");


        QuerySyntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(TablesUsedInQuery);

        if (TopX != -1)
            SqlQueryBuilderHelper.HandleTopX(this, QuerySyntaxHelper, TopX);
        else
            SqlQueryBuilderHelper.ClearTopX(this);

        //declare parameters
        ParameterManager.AddParametersFor(Filters);

        #endregion

        /////////////////////////////////////////////Assemble Query///////////////////////////////

        #region Preamble (including variable declarations/initializations)

        //assemble the query - never use Environment.Newline, use TakeNewLine() so that QueryBuilder knows what line its got up to
        var toReturn = "";

        foreach (var parameter in ParameterManager.GetFinalResolvedParametersList())
        {
            //if the parameter is one that needs to be told what the query syntax helper is e.g. if it's a global parameter designed to work on multiple datasets
            if (parameter is IInjectKnown<IQuerySyntaxHelper> needsToldTheSyntaxHelper)
                needsToldTheSyntaxHelper.InjectKnown(QuerySyntaxHelper);

            if (CheckSyntax)
                parameter.Check(checkNotifier);

            toReturn += GetParameterDeclarationSQL(parameter);
        }

        //add user custom Parameter lines
        toReturn = AppendCustomLines(toReturn, QueryComponent.VariableDeclaration);

        #endregion

        #region Select (including all IColumns)

        toReturn += Environment.NewLine;
        toReturn += $"SELECT {LimitationSQL}{Environment.NewLine}";

        toReturn = AppendCustomLines(toReturn, QueryComponent.SELECT);
        toReturn += Environment.NewLine;

        toReturn = AppendCustomLines(toReturn, QueryComponent.QueryTimeColumn);

        for (var i = 0; i < SelectColumns.Count; i++)
        {
            //output each of the ExtractionInformations that the user requested and record the line number for posterity
            var columnAsSql = SelectColumns[i].GetSelectSQL(_hashingAlgorithm, _salt, QuerySyntaxHelper);

            //there is another one coming
            if (i + 1 < SelectColumns.Count)
                columnAsSql += ",";

            toReturn += columnAsSql + Environment.NewLine;
        }

        #endregion

        //work out basic JOINS Sql
        toReturn += SqlQueryBuilderHelper.GetFROMSQL(this);

        //add user custom JOIN lines
        toReturn = AppendCustomLines(toReturn, QueryComponent.JoinInfoJoin);

        #region Filters (WHERE)

        toReturn += SqlQueryBuilderHelper.GetWHERESQL(this);

        toReturn = AppendCustomLines(toReturn, QueryComponent.WHERE);
        toReturn = AppendCustomLines(toReturn, QueryComponent.Postfix);

        _sql = toReturn;
        SQLOutOfDate = false;

        #endregion
    }

    private string AppendCustomLines(string toReturn, QueryComponent stage)
    {
        var lines = SqlQueryBuilderHelper.GetCustomLinesSQLForStage(this, stage).ToArray();
        if (lines.Any())
        {
            toReturn += Environment.NewLine;
            toReturn += string.Join(Environment.NewLine, lines.Select(l => l.Text));
        }

        return toReturn;
    }

    /// <inheritdoc/>
    public IEnumerable<Lookup> GetDistinctRequiredLookups() => SqlQueryBuilderHelper.GetDistinctRequiredLookups(this);

    /// <summary>
    /// Generates Sql to comment, declare and set the initial value for the supplied <see cref="ISqlParameter"/>.
    /// </summary>
    /// <param name="sqlParameter"></param>
    /// <returns></returns>
    public static string GetParameterDeclarationSQL(ISqlParameter sqlParameter)
    {
        var toReturn = "";

        if (!string.IsNullOrWhiteSpace(sqlParameter.Comment))
            toReturn += $"/*{sqlParameter.Comment}*/{Environment.NewLine}";

        toReturn += sqlParameter.ParameterSQL + Environment.NewLine;

        //it's a table valued parameter! advanced
        if (!string.IsNullOrEmpty(sqlParameter.Value) &&
            Regex.IsMatch(sqlParameter.Value, @"\binsert\s+into\b", RegexOptions.IgnoreCase))
            toReturn += $"{sqlParameter.Value};{Environment.NewLine}";
        else
            toReturn +=
                $"SET {sqlParameter.ParameterName}={sqlParameter.Value};{Environment.NewLine}"; //its a regular value

        return toReturn;
    }

    public static string GetParameterDeclarationSQL(IEnumerable<ISqlParameter> sqlParameters) =>
        string.Join("", sqlParameters.Select(GetParameterDeclarationSQL));
}

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.Text.RegularExpressions;
11	using FAnsi.Discovery.QuerySyntax;
12	using Rdmp.Core.Curation.Data;
13	using Rdmp.Core.MapsDirectlyToDatabaseTable.Injection;
14	using Rdmp.Core.QueryBuilding.Parameters;
15	using Rdmp.Core.ReusableLibraryCode.Checks;
16
17	namespace Rdmp.Core.QueryBuilding;
18
19	/// <summary>
20	/// This class maintains a list of user defined ExtractionInformation objects. It can produce SQL which will try to
21	/// extract this set of ExtractionInformation objects only from the database. This includes determining which ExtractionInformation
22	/// are Lookups, which tables the various objects come from, figuring out whether they can be joined by using JoinInfo in the catalogue
23	///
24	/// <para>It will throw when query SQL if it is not possible to join all the underlying tables or there are any other problems.</para>
25	///
26	/// <para>You can ask it what is on line X or ask what line number has ExtractionInformation Y on it</para>
27	///
28	/// <para>ExtractionInformation is sorted by column order prior to generating the SQL (i.e. not the order you add them to the query builder)</para>
29	/// </summary>
30	public class QueryBuilder : ISqlQueryBuilder
31	{
32	private readonly ITableInfo[] _forceJoinsToTheseTables;
33	private readonly object oSQLLock = new();	418✔
34
35	/// <inheritdoc/>
36	public string SQL
37	{
38	get
39	{
40	lock (oSQLLock)	432✔
41	{
42	if (SQLOutOfDate)	432✔
43	RegenerateSQL();	296✔
44	return _sql;	428✔
45	}
46	}	428✔
47	}
48
49	/// <inheritdoc/>
50	public string LimitationSQL { get; private set; }	850✔
51
52	/// <inheritdoc/>
53	public List<QueryTimeColumn> SelectColumns { get; private set; }	24,420✔
54
55	/// <inheritdoc/>
56	public List<ITableInfo> TablesUsedInQuery { get; private set; }	5,726✔
57
58	/// <inheritdoc/>
59	public List<JoinInfo> JoinsUsedInQuery { get; private set; }	1,142✔
60
61	/// <inheritdoc/>
62	public List<CustomLine> CustomLines { get; private set; }	3,210✔
63
64	/// <inheritdoc/>
65	public CustomLine TopXCustomLine { get; set; }	490✔
66
67	/// <inheritdoc/>
68	public ParameterManager ParameterManager { get; private set; }	2,374✔
69
70	/// <summary>
71	/// Optional field, this specifies where to start gargantuan joins such as when there are 3+ joins and multiple primary key tables e.g. in a star schema.
72	/// If this is not set and there are too many JoinInfos defined in the Catalogue then the class will bomb out with the Exception
73	/// </summary>
74	public ITableInfo PrimaryExtractionTable { get; set; }	1,030✔
75
76	/// <summary>
77	/// A container that contains all the subcontainers and filters to be assembled during the query (use a SpontaneouslyInventedFilterContainer if you want to inject your
78	/// own container tree at runtime rather than referencing a database entity)
79	/// </summary>
80	public IContainer RootFilterContainer
81	{
82	get => _rootFilterContainer;	882✔
83	set
84	{
85	_rootFilterContainer = value;	136✔
86	SQLOutOfDate = true;	136✔
87	}	136✔
88	}
89
90	/// <inheritdoc/>
91	public bool CheckSyntax { get; set; }	4,876✔
92
93
94	private string _salt;
95
96	/// <summary>
97	/// Only use this if you want IColumns which are marked as requiring Hashing to be hashed. Once you set this on a QueryEditor all fields so marked will be hashed using the
98	/// specified salt
99	/// </summary>
100	/// <param name="salt">A 3 letter string indicating the desired SALT</param>
101	public void SetSalt(string salt)
102	{
103	if (string.IsNullOrWhiteSpace(salt))	112!
104	throw new NullReferenceException("Salt cannot be blank");	×
105
106	_salt = salt;	112✔
107	}	112✔
108
109	public void SetLimitationSQL(string limitationSQL)
110	{
111	if (limitationSQL != null && limitationSQL.Contains("top"))	430!
112	throw new Exception("Use TopX property instead of limitation SQL to achieve this");	×
113
114	LimitationSQL = limitationSQL;	430✔
115	SQLOutOfDate = true;	430✔
116	}	430✔
117
118	/// <inheritdoc/>
119	public List<IFilter> Filters { get; private set; }	2,740✔
120
121	/// <summary>
122	/// Limits the number of returned rows to the supplied maximum or -1 if there is no maximum
123	/// </summary>
124	public int TopX
125	{
126	get => _topX;	454✔
127	set
128	{
129	//it already has that value
130	if (_topX == value)	556✔
131	return;	102✔
132
133	_topX = value;	454✔
134	SQLOutOfDate = true;	454✔
135	}	454✔
136	}
137
138	private string _sql;
139
140	/// <inheritdoc/>
141	public bool SQLOutOfDate { get; set; }	6,630✔
142
143	private IContainer _rootFilterContainer;
144	private readonly string _hashingAlgorithm;
145	private int _topX;
146
147	public IQuerySyntaxHelper QuerySyntaxHelper { get; set; }	4,640✔
148
149	/// <summary>
150	/// Used to build extraction queries based on ExtractionInformation sets
151	/// </summary>
152	/// <param name="limitationSQL">Any text you want after SELECT to limit the results e.g. "DISTINCT" or "TOP 10"</param>
153	/// <param name="hashingAlgorithm"></param>
154	/// <param name="forceJoinsToTheseTables"></param>
155	public QueryBuilder(string limitationSQL, string hashingAlgorithm, ITableInfo[] forceJoinsToTheseTables = null)	418✔
156	{
157	_forceJoinsToTheseTables = forceJoinsToTheseTables;	418✔
158	SetLimitationSQL(limitationSQL);	418✔
159	ParameterManager = new ParameterManager();	418✔
160	CustomLines = new List<CustomLine>();	418✔
161
162	CheckSyntax = true;	418✔
163	SelectColumns = new List<QueryTimeColumn>();	418✔
164
165	_hashingAlgorithm = hashingAlgorithm;	418✔
166
167	TopX = -1;	418✔
168	}	418✔
169
170	/// <inheritdoc/>
171	public void AddColumnRange(IColumn[] columnsToAdd)
172	{
173	//add the new ones to the list
174	foreach (var col in columnsToAdd)	8,328✔
175	AddColumn(col);	3,736✔
176
177	SQLOutOfDate = true;	428✔
178	}	428✔
179
180	/// <inheritdoc/>
181	public void AddColumn(IColumn col)
182	{
183	var toAdd = new QueryTimeColumn(col);	4,084✔
184
185	//if it is new, add it to the list
186	if (!SelectColumns.Contains(toAdd))	4,084✔
187	{
188	SelectColumns.Add(toAdd);	4,084✔
189	SQLOutOfDate = true;	4,084✔
190	}
191	}	4,084✔
192
193	/// <inheritdoc/>
194	public CustomLine AddCustomLine(string text, QueryComponent positionToInsert)
195	{
196	SQLOutOfDate = true;	236✔
197	return SqlQueryBuilderHelper.AddCustomLine(this, text, positionToInsert);	236✔
198	}
199
200	/// <summary>
201	/// Updates .SQL Property, note that this is automatically called when you query .SQL anyway so you do not need to manually call it.
202	/// </summary>
203	public void RegenerateSQL()
204	{
205	var checkNotifier = ThrowImmediatelyCheckNotifier.Quiet;	430✔
206
207	_sql = "";	430✔
208
209	//reset the Parameter knowledge
210	ParameterManager.ClearNonGlobals();	430✔
211
212	#region Setup to output the query, where we figure out all the joins etc
213
214	//reset everything
215
216	SelectColumns.Sort();	430✔
217
218	//work out all the filters
219	Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);	430✔
220
221	TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out var primary, _forceJoinsToTheseTables);	430✔
222
223	//force join to any TableInfos that would not be normally joined to but the user wants to anyway e.g. if there's WHERE sql that references them but no columns
224	if (_forceJoinsToTheseTables != null)	422✔
225	foreach (var force in _forceJoinsToTheseTables)	296✔
226	if (!TablesUsedInQuery.Contains(force))	12!
227	TablesUsedInQuery.Add(force);	×
228
229	PrimaryExtractionTable = primary;	422✔
230
231	SqlQueryBuilderHelper.FindLookups(this);	422✔
232
233	JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);	422✔
234
235	//deal with case when there are no tables in the query or there are only lookup descriptions in the query
236	if (TablesUsedInQuery.Count == 0)	422!
237	throw new Exception("There are no TablesUsedInQuery in this dataset");	×
238
239
240	QuerySyntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(TablesUsedInQuery);	422✔
241
242	if (TopX != -1)	422✔
243	SqlQueryBuilderHelper.HandleTopX(this, QuerySyntaxHelper, TopX);	32✔
244	else
245	SqlQueryBuilderHelper.ClearTopX(this);	390✔
246
247	//declare parameters
248	ParameterManager.AddParametersFor(Filters);	422✔
249
250	#endregion
251
252	/////////////////////////////////////////////Assemble Query///////////////////////////////
253
254	#region Preamble (including variable declarations/initializations)
255
256	//assemble the query - never use Environment.Newline, use TakeNewLine() so that QueryBuilder knows what line its got up to
257	var toReturn = "";	422✔
258
259	foreach (var parameter in ParameterManager.GetFinalResolvedParametersList())	1,426✔
260	{
261	//if the parameter is one that needs to be told what the query syntax helper is e.g. if it's a global parameter designed to work on multiple datasets
262	if (parameter is IInjectKnown<IQuerySyntaxHelper> needsToldTheSyntaxHelper)	292✔
263	needsToldTheSyntaxHelper.InjectKnown(QuerySyntaxHelper);	4✔
264
265	if (CheckSyntax)	292✔
266	parameter.Check(checkNotifier);	292✔
267
268	toReturn += GetParameterDeclarationSQL(parameter);	292✔
269	}
270
271	//add user custom Parameter lines
272	toReturn = AppendCustomLines(toReturn, QueryComponent.VariableDeclaration);	420✔
273
274	#endregion
275
276	#region Select (including all IColumns)
277
278	toReturn += Environment.NewLine;	420✔
279	toReturn += $"SELECT {LimitationSQL}{Environment.NewLine}";	420✔
280
281	toReturn = AppendCustomLines(toReturn, QueryComponent.SELECT);	420✔
282	toReturn += Environment.NewLine;	420✔
283
284	toReturn = AppendCustomLines(toReturn, QueryComponent.QueryTimeColumn);	420✔
285
286	for (var i = 0; i < SelectColumns.Count; i++)	9,084✔
287	{
288	//output each of the ExtractionInformations that the user requested and record the line number for posterity
289	var columnAsSql = SelectColumns[i].GetSelectSQL(_hashingAlgorithm, _salt, QuerySyntaxHelper);	4,122✔
290
291	//there is another one coming
292	if (i + 1 < SelectColumns.Count)	4,122✔
293	columnAsSql += ",";	3,702✔
294
295	toReturn += columnAsSql + Environment.NewLine;	4,122✔
296	}
297
298	#endregion
299
300	//work out basic JOINS Sql
301	toReturn += SqlQueryBuilderHelper.GetFROMSQL(this);	420✔
302
303	//add user custom JOIN lines
304	toReturn = AppendCustomLines(toReturn, QueryComponent.JoinInfoJoin);	420✔
305
306	#region Filters (WHERE)
307
308	toReturn += SqlQueryBuilderHelper.GetWHERESQL(this);	420✔
309
310	toReturn = AppendCustomLines(toReturn, QueryComponent.WHERE);	420✔
311	toReturn = AppendCustomLines(toReturn, QueryComponent.Postfix);	420✔
312
313	_sql = toReturn;	420✔
314	SQLOutOfDate = false;	420✔
315
316	#endregion
317	}	420✔
318
319	private string AppendCustomLines(string toReturn, QueryComponent stage)
320	{
321	var lines = SqlQueryBuilderHelper.GetCustomLinesSQLForStage(this, stage).ToArray();	2,520✔
322	if (lines.Any())	2,520✔
323	{
324	toReturn += Environment.NewLine;	284✔
325	toReturn += string.Join(Environment.NewLine, lines.Select(l => l.Text));	710✔
326	}
327
328	return toReturn;	2,520✔
329	}
330
331	/// <inheritdoc/>
332	public IEnumerable<Lookup> GetDistinctRequiredLookups() => SqlQueryBuilderHelper.GetDistinctRequiredLookups(this);	74✔
333
334	/// <summary>
335	/// Generates Sql to comment, declare and set the initial value for the supplied <see cref="ISqlParameter"/>.
336	/// </summary>
337	/// <param name="sqlParameter"></param>
338	/// <returns></returns>
339	public static string GetParameterDeclarationSQL(ISqlParameter sqlParameter)
340	{
341	var toReturn = "";	588✔
342
343	if (!string.IsNullOrWhiteSpace(sqlParameter.Comment))	588✔
344	toReturn += $"/{sqlParameter.Comment}/{Environment.NewLine}";	278✔
345
346	toReturn += sqlParameter.ParameterSQL + Environment.NewLine;	588✔
347
348	//it's a table valued parameter! advanced
349	if (!string.IsNullOrEmpty(sqlParameter.Value) &&	588!
350	Regex.IsMatch(sqlParameter.Value, @"\binsert\s+into\b", RegexOptions.IgnoreCase))	588✔
351	toReturn += $"{sqlParameter.Value};{Environment.NewLine}";	×
352	else
353	toReturn +=	588✔
354	$"SET {sqlParameter.ParameterName}={sqlParameter.Value};{Environment.NewLine}"; //its a regular value	588✔
355
356	return toReturn;	588✔
357	}
358
359	public static string GetParameterDeclarationSQL(IEnumerable<ISqlParameter> sqlParameters) =>
360	string.Join("", sqlParameters.Select(GetParameterDeclarationSQL));	214✔
361	}

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