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

63.1

/Rdmp.Core/QueryBuilding/QueryTimeColumn.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;
using System.Diagnostics;
using System.Linq;
using FAnsi.Discovery.QuerySyntax;
using Rdmp.Core.Curation.Data;
using Rdmp.Core.Curation.DataHelper;
using Rdmp.Core.ReusableLibraryCode.Checks;

namespace Rdmp.Core.QueryBuilding;

/// <summary>
/// The SELECT portion of QueryBuilder is built up via AddColumn which takes an IColumn.  Each IColumn is a single line of SELECT Sql which might be as
/// simple as the name of a column but might be a method with an alias or even a count e.g. 'sum(distinct mycol) as Total'.  These IColumns are wrapped by
/// QueryTimeColumn which is a wrapper for IColumn which is gradually populated with facts discovered during QueryBuilding such as whether it is from a Lookup
/// Table, whether it maps to an underlying ColumnInfo etc.  These facts are used later on by QueryBuilder to decide which tables/joins are needed in the FROM
/// section of the query etc
/// </summary>
public class QueryTimeColumn : IComparable
{
    /// <summary>
    /// The <see cref="UnderlyingColumn"/> is from a <see cref="Lookup"/> and is a description column but there was no associated
    /// foreign key column found in the query.
    /// </summary>
    public bool IsIsolatedLookupDescription { get; set; }

    /// <summary>
    /// The <see cref="UnderlyingColumn"/> is NOT from a <see cref="Lookup"/> but it is a code column (foreign key) which could be linked to a <see cref="Lookup"/>.
    /// The <see cref="Lookup"/> will be included in the query if one or more description columns follow this column in the query
    /// </summary>
    public bool IsLookupForeignKey { get; private set; }

    /// <summary>
    /// The <see cref="UnderlyingColumn"/> is from a <see cref="Lookup"/> and is a description column and there WAS an associated foreign key column previously found in the query.
    /// </summary>
    public bool IsLookupDescription { get; private set; }

    /// <summary>
    /// The alias given to the <see cref="Lookup"/> table this column belongs to (if any).  This allows you to have the same description column several times in the query e.g.
    /// SendingLocation, Description, ReleaseLocation, Description
    /// </summary>
    public int LookupTableAlias { get; private set; }

    /// <summary>
    /// The <see cref="Lookup"/> that this column is related in the context of the query being generated
    /// </summary>
    public Lookup LookupTable { get; private set; }

    /// <summary>
    /// The SELECT column definition including extraction options such as Order and HashOnDataRelease etc
    /// </summary>
    public IColumn IColumn { get; set; }

    /// <summary>
    /// The actual database model layer column.  The same <see cref="ColumnInfo"/> can appear multiple times in the same query e.g. if extracting DateOfBirth and YearOfBirth where
    /// these are both transforms of the same underlying column.
    /// </summary>
    public ColumnInfo UnderlyingColumn { get; set; }

    /// <summary>
    /// Creates a new <see cref="QueryTimeColumn"/> ready for annotation with facts as they are discovered during query building
    /// </summary>
    /// <param name="column"></param>
    public QueryTimeColumn(IColumn column)
    {
        IColumn = column;
        UnderlyingColumn = column.ColumnInfo;
    }

    /// <inheritdoc/>
    public override int GetHashCode() => IColumn == null ? -1 : IColumn.ID;

    /// <inheritdoc/>
    public override bool Equals(object obj)
    {
        if (obj is QueryTimeColumn other)
            return
                other.IColumn.Equals(IColumn);

        throw new Exception(".Equals only works for objects of type QueryTimeColumn");
    }

    /// <inheritdoc/>
    public int CompareTo(object obj) =>
        obj is QueryTimeColumn column
            ? IColumn.Order -
              column.IColumn.Order
            : 0;

    /// <summary>
    /// Computes and records the <see cref="Lookup"/> related facts about all the <see cref="QueryTimeColumn"/> provided when building a query which requires the
    /// supplied list of <paramref name="tablesUsedInQuery"/>.
    /// </summary>
    /// <param name="ColumnsInOrder"></param>
    /// <param name="tablesUsedInQuery"></param>
    public static void SetLookupStatus(QueryTimeColumn[] ColumnsInOrder, List<ITableInfo> tablesUsedInQuery)
    {
        ColumnInfo lastForeignKeyFound = null;
        var lookupTablesFound = 0;

        var firstTable = tablesUsedInQuery.FirstOrDefault();

        var allAvailableLookups = Array.Empty<Lookup>();

        if (firstTable != null)
            allAvailableLookups = firstTable.Repository.GetAllObjects<Lookup>();

        foreach (var column in ColumnsInOrder)
        {
            //it is a custom column
            if (column.UnderlyingColumn == null)
                continue;

            var foreignKeyLookupInvolvement = allAvailableLookups
                .Where(l => l.ForeignKey_ID == column.UnderlyingColumn.ID).ToArray();
            var lookupDescriptionInvolvement = allAvailableLookups
                .Where(l => l.Description_ID == column.UnderlyingColumn.ID).ToArray();

            if (foreignKeyLookupInvolvement.Select(l => l.PrimaryKey.TableInfo_ID).Distinct().Count() > 1)
                throw new Exception(
                    $"Column {column.UnderlyingColumn} is configured as a foreign key for multiple different Lookup tables");

            if (foreignKeyLookupInvolvement.Length > 0)
            {
                if (lookupDescriptionInvolvement.Length > 0)
                    throw new QueryBuildingException(
                        $"Column {column.UnderlyingColumn} is both a Lookup.ForeignKey and a Lookup.Description");


                lastForeignKeyFound = column.UnderlyingColumn;
                column.IsLookupForeignKey = true;
                column.IsLookupDescription = false;
                column.LookupTableAlias = ++lookupTablesFound;
                column.LookupTable = foreignKeyLookupInvolvement[0];
            }

            if (lookupDescriptionInvolvement.Length > 0)
            {
                var lookupDescriptionIsIsolated = false;

                //we have not found any foreign keys yet: that's a problem
                if (lastForeignKeyFound == null)
                {
                    var potentialWinners =
                        lookupDescriptionInvolvement.Where(
                            l => tablesUsedInQuery.Any(t => t.ID == l.ForeignKey.TableInfo_ID)).ToArray();

                    if (potentialWinners.Length ==
                        1) //or there are many options but only one which is in our existing table collection
                    {
                        lastForeignKeyFound =
                            potentialWinners[0]
                                .ForeignKey; //use if there aren't multiple foreign keys to pick from (which would result in uncertainty)
                        lookupDescriptionIsIsolated = true;
                    }
                    else
                        //otherwise there are multiple foreign keys for this description and the user has not put in a foreign key to let us choose the correct one
                    {
                        throw new QueryBuildingException(
                            $"Found lookup description before encountering any lookup foreign keys (Description column was {column.UnderlyingColumn}) - make sure you always order Descriptions after their Foreign key and ensure they are in a contiguous block");
                    }
                }

                var correctLookupDescriptionInvolvement = lookupDescriptionInvolvement
                    .Where(lookup => lookup.ForeignKey.ID == lastForeignKeyFound.ID).ToArray();

                if (correctLookupDescriptionInvolvement.Length == 0)
                {
                    //so there are no compatible foreign keys or the columns are a jumbled mess

                    //either way the last seen fk (or guessed fk) isn't right.  So what fks could potentially be used with the Column?
                    var probableCorrectColumn = lookupDescriptionInvolvement.Where(
                            l =>
                                //any lookup where there is...
                                ColumnsInOrder.Any(
                                    qtc =>
                                        //a column with an ID equal to the fk
                                        qtc.UnderlyingColumn != null && qtc.UnderlyingColumn.ID == l.ForeignKey_ID))
                        .ToArray();


                    var suggestions = "";
                    if (probableCorrectColumn.Any())
                        suggestions =
                            $"Possible foreign keys include:{string.Join(",", probableCorrectColumn.Select(l => l.ForeignKey))}";

                    throw new QueryBuildingException(
                        $"Encountered Lookup Description Column ({column.IColumn}) after first encountering Foreign Key ({lastForeignKeyFound}).  Lookup description columns (_Desc) must come after the associated Foreign key.{suggestions}");
                }

                if (correctLookupDescriptionInvolvement.Length > 1)
                    throw new QueryBuildingException(
                        $"Lookup description {column.UnderlyingColumn} appears to be configured as a Lookup Description twice with the same Lookup Table");

                column.IsIsolatedLookupDescription = lookupDescriptionIsIsolated;
                column.IsLookupForeignKey = false;
                column.IsLookupDescription = true;
                column.LookupTableAlias =
                    lookupTablesFound; // must belong to same one as previously encountered foreign key
                column.LookupTable = correctLookupDescriptionInvolvement[0];

                //see if there are any supplemental joins to tables that are not involved in the query
                var supplementalJoins = correctLookupDescriptionInvolvement[0].GetSupplementalJoins();

                if (supplementalJoins != null)
                    foreach (var supplementalJoin in supplementalJoins)
                        if (tablesUsedInQuery.All(t => t.ID != supplementalJoin.ForeignKey.TableInfo_ID))
                            throw new QueryBuildingException(
                                $"Lookup requires supplemental join to column {supplementalJoin.ForeignKey} which is contained in a table that is not part of the SELECT column collection");
            }
        }
    }


    /// <summary>
    /// Returns the line of SELECT Sql for this column that will appear in the final query
    /// </summary>
    /// <param name="hashingPattern"></param>
    /// <param name="salt"></param>
    /// <param name="syntaxHelper"></param>
    /// <returns></returns>
    public string GetSelectSQL(string hashingPattern, string salt, IQuerySyntaxHelper syntaxHelper)
    {
        var toReturn = IColumn.SelectSQL;

        //deal with hashing
        if (string.IsNullOrWhiteSpace(salt) == false && IColumn.HashOnDataRelease)
        {
            if (string.IsNullOrWhiteSpace(IColumn.Alias))
                throw new ArgumentException(
                    $"IExtractableColumn {IColumn} is missing an Alias (required for hashing)");

            //if there is no custom hashing pattern
            toReturn = string.IsNullOrWhiteSpace(hashingPattern)
                ? syntaxHelper.HowDoWeAchieveMd5(toReturn)
                : //use the DBMS specific one
                string.Format(hashingPattern, toReturn, salt); //otherwise use the custom one
        }

        // the SELECT SQL may span multiple lines, so collapse it to a single line cleaning up any whitespace issues, e.g. to avoid double spaces in the collapsed version
        var trimmedSelectSQL =
            toReturn.Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries)
                .Select(s => s.Trim());
        toReturn = string.Join(" ", trimmedSelectSQL);

        //append alias to the end of the line if there is an alias
        if (!string.IsNullOrWhiteSpace(IColumn.Alias))
            toReturn += syntaxHelper.AliasPrefix + IColumn.Alias.Trim();

        //cannot be both, we check for this earlier (see SetLookupStatus)
        Debug.Assert(!(IsLookupDescription && IsLookupForeignKey));

        //replace table name with table alias if it is a LookupDescription
        if (IsLookupDescription)
        {
            var tableName = LookupTable.PrimaryKey.TableInfo.Name;

            if (!toReturn.Contains(tableName))
                throw new Exception(
                    $"Column \"{toReturn}\" is a Lookup Description but its SELECT SQL does not include the Lookup table name \"{tableName}\"");

            toReturn = toReturn.Replace(tableName, JoinHelper.GetLookupTableAlias(LookupTableAlias));
        }

        //actually don't need to do anything special for LookupForeignKeys

        return toReturn;
    }

    /// <summary>
    /// Runs checks on the <see cref="Core.QueryBuilding.IColumn"/> and translates any failures into <see cref="SyntaxErrorException"/>
    /// </summary>
    public void CheckSyntax()
    {
        //make sure to only throw SyntaxErrorException errors in here
        try
        {
            IColumn.Check(ThrowImmediatelyCheckNotifier.Quiet);
            var runtimeName = IColumn.GetRuntimeName();

            if (string.IsNullOrWhiteSpace(runtimeName))
                throw new SyntaxErrorException("no runtime name");
        }
        catch (SyntaxErrorException exception)
        {
            throw new SyntaxErrorException(
                $"Syntax failure on IExtractableColumn with SelectSQL=\"{IColumn.SelectSQL}\"", exception);
        }
    }

    /// <summary>
    /// For a given column that <see cref="IsLookupForeignKey"/> returns true if there is an associated column from the lookup (i.e. a description column). This
    /// should determine whether or not to link to the table in the FROM section of the query.
    /// </summary>
    /// <param name="selectColumns"></param>
    /// <returns></returns>
    public bool IsLookupForeignKeyActuallyUsed(List<QueryTimeColumn> selectColumns)
    {
        if (!IsLookupForeignKey)
            return false;

        //see if the description is used anywhere in the actual query columns!
        return selectColumns.Any(c => c.IsLookupDescription && c.LookupTable.ID == LookupTable.ID);
    }
}

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;
10	using System.Diagnostics;
11	using System.Linq;
12	using FAnsi.Discovery.QuerySyntax;
13	using Rdmp.Core.Curation.Data;
14	using Rdmp.Core.Curation.DataHelper;
15	using Rdmp.Core.ReusableLibraryCode.Checks;
16
17	namespace Rdmp.Core.QueryBuilding;
18
19	/// <summary>
20	/// The SELECT portion of QueryBuilder is built up via AddColumn which takes an IColumn. Each IColumn is a single line of SELECT Sql which might be as
21	/// simple as the name of a column but might be a method with an alias or even a count e.g. 'sum(distinct mycol) as Total'. These IColumns are wrapped by
22	/// QueryTimeColumn which is a wrapper for IColumn which is gradually populated with facts discovered during QueryBuilding such as whether it is from a Lookup
23	/// Table, whether it maps to an underlying ColumnInfo etc. These facts are used later on by QueryBuilder to decide which tables/joins are needed in the FROM
24	/// section of the query etc
25	/// </summary>
26	public class QueryTimeColumn : IComparable
27	{
28	/// <summary>
29	/// The <see cref="UnderlyingColumn"/> is from a <see cref="Lookup"/> and is a description column but there was no associated
30	/// foreign key column found in the query.
31	/// </summary>
32	public bool IsIsolatedLookupDescription { get; set; }	13,732✔
33
34	/// <summary>
35	/// The <see cref="UnderlyingColumn"/> is NOT from a <see cref="Lookup"/> but it is a code column (foreign key) which could be linked to a <see cref="Lookup"/>.
36	/// The <see cref="Lookup"/> will be included in the query if one or more description columns follow this column in the query
37	/// </summary>
38	public bool IsLookupForeignKey { get; private set; }	13,914✔
39
40	/// <summary>
41	/// The <see cref="UnderlyingColumn"/> is from a <see cref="Lookup"/> and is a description column and there WAS an associated foreign key column previously found in the query.
42	/// </summary>
43	public bool IsLookupDescription { get; private set; }	7,930✔
44
45	/// <summary>
46	/// The alias given to the <see cref="Lookup"/> table this column belongs to (if any). This allows you to have the same description column several times in the query e.g.
47	/// SendingLocation, Description, ReleaseLocation, Description
48	/// </summary>
49	public int LookupTableAlias { get; private set; }	120✔
50
51	/// <summary>
52	/// The <see cref="Lookup"/> that this column is related in the context of the query being generated
53	/// </summary>
54	public Lookup LookupTable { get; private set; }	318✔
55
56	/// <summary>
57	/// The SELECT column definition including extraction options such as Order and HashOnDataRelease etc
58	/// </summary>
59	public IColumn IColumn { get; set; }	205,292✔
60
61	/// <summary>
62	/// The actual database model layer column. The same <see cref="ColumnInfo"/> can appear multiple times in the same query e.g. if extracting DateOfBirth and YearOfBirth where
63	/// these are both transforms of the same underlying column.
64	/// </summary>
65	public ColumnInfo UnderlyingColumn { get; set; }	20,496✔
66
67	/// <summary>
68	/// Creates a new <see cref="QueryTimeColumn"/> ready for annotation with facts as they are discovered during query building
69	/// </summary>
70	/// <param name="column"></param>
71	public QueryTimeColumn(IColumn column)	6,132✔
72	{
73	IColumn = column;	6,132✔
74	UnderlyingColumn = column.ColumnInfo;	6,132✔
75	}	6,132✔
76
77	/// <inheritdoc/>
78	public override int GetHashCode() => IColumn == null ? -1 : IColumn.ID;	×
79
80	/// <inheritdoc/>
81	public override bool Equals(object obj)
82	{
83	if (obj is QueryTimeColumn other)	68,562!
84	return	68,562✔
85	other.IColumn.Equals(IColumn);	68,562✔
86
87	throw new Exception(".Equals only works for objects of type QueryTimeColumn");	×
88	}
89
90	/// <inheritdoc/>
91	public int CompareTo(object obj) =>
92	obj is QueryTimeColumn column	13,130!
93	? IColumn.Order -	13,130✔
94	column.IColumn.Order	13,130✔
95	: 0;	13,130✔
96
97	/// <summary>
98	/// Computes and records the <see cref="Lookup"/> related facts about all the <see cref="QueryTimeColumn"/> provided when building a query which requires the
99	/// supplied list of <paramref name="tablesUsedInQuery"/>.
100	/// </summary>
101	/// <param name="ColumnsInOrder"></param>
102	/// <param name="tablesUsedInQuery"></param>
103	public static void SetLookupStatus(QueryTimeColumn[] ColumnsInOrder, List<ITableInfo> tablesUsedInQuery)
104	{
105	ColumnInfo lastForeignKeyFound = null;	96✔
106	var lookupTablesFound = 0;	96✔
107
108	var firstTable = tablesUsedInQuery.FirstOrDefault();	96✔
109
110	var allAvailableLookups = Array.Empty<Lookup>();	96✔
111
112	if (firstTable != null)	96✔
113	allAvailableLookups = firstTable.Repository.GetAllObjects<Lookup>();	96✔
114
115	foreach (var column in ColumnsInOrder)	2,076✔
116	{
117	//it is a custom column
118	if (column.UnderlyingColumn == null)	942✔
119	continue;
120
121	var foreignKeyLookupInvolvement = allAvailableLookups	938✔
122	.Where(l => l.ForeignKey_ID == column.UnderlyingColumn.ID).ToArray();	1,752✔
123	var lookupDescriptionInvolvement = allAvailableLookups	938✔
124	.Where(l => l.Description_ID == column.UnderlyingColumn.ID).ToArray();	1,752✔
125
126	if (foreignKeyLookupInvolvement.Select(l => l.PrimaryKey.TableInfo_ID).Distinct().Count() > 1)	968!
127	throw new Exception(	×
128	$"Column {column.UnderlyingColumn} is configured as a foreign key for multiple different Lookup tables");	×
129
130	if (foreignKeyLookupInvolvement.Length > 0)	938✔
131	{
132	if (lookupDescriptionInvolvement.Length > 0)	30!
133	throw new QueryBuildingException(	×
134	$"Column {column.UnderlyingColumn} is both a Lookup.ForeignKey and a Lookup.Description");	×
135
136
137	lastForeignKeyFound = column.UnderlyingColumn;	30✔
138	column.IsLookupForeignKey = true;	30✔
139	column.IsLookupDescription = false;	30✔
140	column.LookupTableAlias = ++lookupTablesFound;	30✔
141	column.LookupTable = foreignKeyLookupInvolvement[0];	30✔
142	}
143
144	if (lookupDescriptionInvolvement.Length > 0)	938✔
145	{
146	var lookupDescriptionIsIsolated = false;	30✔
147
148	//we have not found any foreign keys yet: that's a problem
149	if (lastForeignKeyFound == null)	30!
150	{
151	var potentialWinners =	×
152	lookupDescriptionInvolvement.Where(	×
153	l => tablesUsedInQuery.Any(t => t.ID == l.ForeignKey.TableInfo_ID)).ToArray();	×
154
155	if (potentialWinners.Length ==	×
156	1) //or there are many options but only one which is in our existing table collection	×
157	{
158	lastForeignKeyFound =	×
159	potentialWinners[0]	×
160	.ForeignKey; //use if there aren't multiple foreign keys to pick from (which would result in uncertainty)	×
161	lookupDescriptionIsIsolated = true;	×
162	}
163	else
164	//otherwise there are multiple foreign keys for this description and the user has not put in a foreign key to let us choose the correct one
165	{
166	throw new QueryBuildingException(	×
167	$"Found lookup description before encountering any lookup foreign keys (Description column was {column.UnderlyingColumn}) - make sure you always order Descriptions after their Foreign key and ensure they are in a contiguous block");	×
168	}
169	}
170
171	var correctLookupDescriptionInvolvement = lookupDescriptionInvolvement	30✔
172	.Where(lookup => lookup.ForeignKey.ID == lastForeignKeyFound.ID).ToArray();	60✔
173
174	if (correctLookupDescriptionInvolvement.Length == 0)	30!
175	{
176	//so there are no compatible foreign keys or the columns are a jumbled mess
177
178	//either way the last seen fk (or guessed fk) isn't right. So what fks could potentially be used with the Column?
179	var probableCorrectColumn = lookupDescriptionInvolvement.Where(	×
180	l =>	×
181	//any lookup where there is...	×
182	ColumnsInOrder.Any(	×
183	qtc =>	×
184	//a column with an ID equal to the fk	×
185	qtc.UnderlyingColumn != null && qtc.UnderlyingColumn.ID == l.ForeignKey_ID))	×
186	.ToArray();	×
187
188
189	var suggestions = "";	×
190	if (probableCorrectColumn.Any())	×
191	suggestions =	×
192	$"Possible foreign keys include:{string.Join(",", probableCorrectColumn.Select(l => l.ForeignKey))}";	×
193
194	throw new QueryBuildingException(	×
195	$"Encountered Lookup Description Column ({column.IColumn}) after first encountering Foreign Key ({lastForeignKeyFound}). Lookup description columns (_Desc) must come after the associated Foreign key.{suggestions}");	×
196	}
197
198	if (correctLookupDescriptionInvolvement.Length > 1)	30!
199	throw new QueryBuildingException(	×
200	$"Lookup description {column.UnderlyingColumn} appears to be configured as a Lookup Description twice with the same Lookup Table");	×
201
202	column.IsIsolatedLookupDescription = lookupDescriptionIsIsolated;	30✔
203	column.IsLookupForeignKey = false;	30✔
204	column.IsLookupDescription = true;	30✔
205	column.LookupTableAlias =	30✔
206	lookupTablesFound; // must belong to same one as previously encountered foreign key	30✔
207	column.LookupTable = correctLookupDescriptionInvolvement[0];	30✔
208
209	//see if there are any supplemental joins to tables that are not involved in the query
210	var supplementalJoins = correctLookupDescriptionInvolvement[0].GetSupplementalJoins();	30✔
211
212	if (supplementalJoins != null)	30✔
213	foreach (var supplementalJoin in supplementalJoins)	92✔
214	if (tablesUsedInQuery.All(t => t.ID != supplementalJoin.ForeignKey.TableInfo_ID))	32!
215	throw new QueryBuildingException(	×
216	$"Lookup requires supplemental join to column {supplementalJoin.ForeignKey} which is contained in a table that is not part of the SELECT column collection");	×
217	}
218	}
219	}	96✔
220
221
222	/// <summary>
223	/// Returns the line of SELECT Sql for this column that will appear in the final query
224	/// </summary>
225	/// <param name="hashingPattern"></param>
226	/// <param name="salt"></param>
227	/// <param name="syntaxHelper"></param>
228	/// <returns></returns>
229	public string GetSelectSQL(string hashingPattern, string salt, IQuerySyntaxHelper syntaxHelper)
230	{
231	var toReturn = IColumn.SelectSQL;	5,718✔
232
233	//deal with hashing
234	if (string.IsNullOrWhiteSpace(salt) == false && IColumn.HashOnDataRelease)	5,718✔
235	{
236	if (string.IsNullOrWhiteSpace(IColumn.Alias))	6!
237	throw new ArgumentException(	×
238	$"IExtractableColumn {IColumn} is missing an Alias (required for hashing)");	×
239
240	//if there is no custom hashing pattern
241	toReturn = string.IsNullOrWhiteSpace(hashingPattern)	6!
242	? syntaxHelper.HowDoWeAchieveMd5(toReturn)	6✔
243	: //use the DBMS specific one	6✔
244	string.Format(hashingPattern, toReturn, salt); //otherwise use the custom one	6✔
245	}
246
247	// the SELECT SQL may span multiple lines, so collapse it to a single line cleaning up any whitespace issues, e.g. to avoid double spaces in the collapsed version
248	var trimmedSelectSQL =	5,718✔
249	toReturn.Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries)	5,718✔
250	.Select(s => s.Trim());	11,642✔
251	toReturn = string.Join(" ", trimmedSelectSQL);	5,718✔
252
253	//append alias to the end of the line if there is an alias
254	if (!string.IsNullOrWhiteSpace(IColumn.Alias))	5,718✔
255	toReturn += syntaxHelper.AliasPrefix + IColumn.Alias.Trim();	408✔
256
257	//cannot be both, we check for this earlier (see SetLookupStatus)
258	Debug.Assert(!(IsLookupDescription && IsLookupForeignKey));
259
260	//replace table name with table alias if it is a LookupDescription
261	if (IsLookupDescription)	5,718✔
262	{
263	var tableName = LookupTable.PrimaryKey.TableInfo.Name;	30✔
264
265	if (!toReturn.Contains(tableName))	30!
266	throw new Exception(	×
267	$"Column \"{toReturn}\" is a Lookup Description but its SELECT SQL does not include the Lookup table name \"{tableName}\"");	×
268
269	toReturn = toReturn.Replace(tableName, JoinHelper.GetLookupTableAlias(LookupTableAlias));	30✔
270	}
271
272	//actually don't need to do anything special for LookupForeignKeys
273
274	return toReturn;	5,718✔
275	}
276
277	/// <summary>
278	/// Runs checks on the <see cref="Core.QueryBuilding.IColumn"/> and translates any failures into <see cref="SyntaxErrorException"/>
279	/// </summary>
280	public void CheckSyntax()
281	{
282	//make sure to only throw SyntaxErrorException errors in here
283	try
284	{
285	IColumn.Check(ThrowImmediatelyCheckNotifier.Quiet);	4,602✔
286	var runtimeName = IColumn.GetRuntimeName();	4,602✔
287
288	if (string.IsNullOrWhiteSpace(runtimeName))	4,602!
289	throw new SyntaxErrorException("no runtime name");	×
290	}	4,602✔
291	catch (SyntaxErrorException exception)	×
292	{
293	throw new SyntaxErrorException(	×
294	$"Syntax failure on IExtractableColumn with SelectSQL=\"{IColumn.SelectSQL}\"", exception);	×
295	}
296	}	4,602✔
297
298	/// <summary>
299	/// For a given column that <see cref="IsLookupForeignKey"/> returns true if there is an associated column from the lookup (i.e. a description column). This
300	/// should determine whether or not to link to the table in the FROM section of the query.
301	/// </summary>
302	/// <param name="selectColumns"></param>
303	/// <returns></returns>
304	public bool IsLookupForeignKeyActuallyUsed(List<QueryTimeColumn> selectColumns)
305	{
306	if (!IsLookupForeignKey)	76!
307	return false;	×
308
309	//see if the description is used anywhere in the actual query columns!
310	return selectColumns.Any(c => c.IsLookupDescription && c.LookupTable.ID == LookupTable.ID);	696✔
311	}
312	}

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