6706566541

Committed 31 Oct 2023 12:34PM UTC coverage: 56.913% (+0.004%) from 56.909%

Build # 6706566541

Build Type

Pull #1664

github

Committed by

JFriel

Commit Message

Revert "Feature/cleanup (#1659)"

This reverts commit 4e59d8ae0.

Pull Request Pull Request #1664: Revert "Feature/cleanup (#1659)"

Run Details

10696 of 20239 branches covered (0.0%)

Branch coverage included in aggregate %.

23 of 23 new or added lines in 5 files covered. (100.0%)

30536 of 52209 relevant lines covered (58.49%)

7346.54 hits per line

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

63.3

/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 == false)
            throw new Exception(".Equals only works for objects of type QueryTimeColumn");

        var other = obj as QueryTimeColumn;
        return
            other.IColumn.Equals(IColumn);
    }

    /// <inheritdoc/>
    public int CompareTo(object obj) =>
        obj is QueryTimeColumn
            ? IColumn.Order -
              (obj as QueryTimeColumn).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>();
                
        for (var i = 0; i < ColumnsInOrder.Length; i++)
        {
            //it is a custom column
            if (ColumnsInOrder[i].UnderlyingColumn == null)
                continue;

            var foreignKeyLookupInvolvement = allAvailableLookups
                .Where(l => l.ForeignKey_ID == ColumnsInOrder[i].UnderlyingColumn.ID).ToArray();
            var lookupDescriptionInvolvement = allAvailableLookups
                .Where(l => l.Description_ID == ColumnsInOrder[i].UnderlyingColumn.ID).ToArray();

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

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


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

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

                //we have not found any foreign keys yet thats 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 it 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 {ColumnsInOrder[i].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 ({ColumnsInOrder[i].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 {ColumnsInOrder[i].UnderlyingColumn} appears to be configured as a Lookup Description twice with the same Lookup Table");

                ColumnsInOrder[i].IsIsolatedLookupDescription = lookupDescriptionIsIsolated;
                ColumnsInOrder[i].IsLookupForeignKey = false;
                ColumnsInOrder[i].IsLookupDescription = true;
                ColumnsInOrder[i].LookupTableAlias =
                    lookupTablesFound; // must belong to same one as previously encountered foreign key
                ColumnsInOrder[i].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; }	12,676✔
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; }	12,858✔
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,382✔
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; }	171,288✔
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; }	18,940✔
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)	5,604✔
72	{
73	IColumn = column;	5,604✔
74	UnderlyingColumn = column.ColumnInfo;	5,604✔
75	}	5,604✔
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 == false)	55,780!
84	throw new Exception(".Equals only works for objects of type QueryTimeColumn");	×
85
86	var other = obj as QueryTimeColumn;	55,780✔
87	return	55,780✔
88	other.IColumn.Equals(IColumn);	55,780✔
89	}
90
91	/// <inheritdoc/>
92	public int CompareTo(object obj) =>
93	obj is QueryTimeColumn	11,156!
94	? IColumn.Order -	11,156✔
95	(obj as QueryTimeColumn).IColumn.Order	11,156✔
96	: 0;	11,156✔
97
98	/// <summary>
99	/// Computes and records the <see cref="Lookup"/> related facts about all the <see cref="QueryTimeColumn"/> provided when building a query which requires the
100	/// supplied list of <paramref name="tablesUsedInQuery"/>.
101	/// </summary>
102	/// <param name="ColumnsInOrder"></param>
103	/// <param name="tablesUsedInQuery"></param>
104	public static void SetLookupStatus(QueryTimeColumn[] ColumnsInOrder, List<ITableInfo> tablesUsedInQuery)
105	{
106	ColumnInfo lastForeignKeyFound = null;	96✔
107	var lookupTablesFound = 0;	96✔
108
109	var firstTable = tablesUsedInQuery.FirstOrDefault();	96✔
110
111	var allAvailableLookups = Array.Empty<Lookup>();	96✔
112
113	if (firstTable != null)	96✔
114	allAvailableLookups = firstTable.Repository.GetAllObjects<Lookup>();	96✔
115
116	for (var i = 0; i < ColumnsInOrder.Length; i++)	2,076✔
117	{
118	//it is a custom column
119	if (ColumnsInOrder[i].UnderlyingColumn == null)	942✔
120	continue;
121
122	var foreignKeyLookupInvolvement = allAvailableLookups	938✔
123	.Where(l => l.ForeignKey_ID == ColumnsInOrder[i].UnderlyingColumn.ID).ToArray();	1,752✔
124	var lookupDescriptionInvolvement = allAvailableLookups	938✔
125	.Where(l => l.Description_ID == ColumnsInOrder[i].UnderlyingColumn.ID).ToArray();	1,752✔
126
127	if (foreignKeyLookupInvolvement.Select(l => l.PrimaryKey.TableInfo_ID).Distinct().Count() > 1)	968!
128	throw new Exception(	×
129	$"Column {ColumnsInOrder[i].UnderlyingColumn} is configured as a foreign key for multiple different Lookup tables");	×
130
131	if (foreignKeyLookupInvolvement.Length > 0)	938✔
132	{
133	if (lookupDescriptionInvolvement.Length > 0)	30!
134	throw new QueryBuildingException(	×
135	$"Column {ColumnsInOrder[i].UnderlyingColumn} is both a Lookup.ForeignKey and a Lookup.Description");	×
136
137
138	lastForeignKeyFound = ColumnsInOrder[i].UnderlyingColumn;	30✔
139	ColumnsInOrder[i].IsLookupForeignKey = true;	30✔
140	ColumnsInOrder[i].IsLookupDescription = false;	30✔
141	ColumnsInOrder[i].LookupTableAlias = ++lookupTablesFound;	30✔
142	ColumnsInOrder[i].LookupTable = foreignKeyLookupInvolvement[0];	30✔
143	}
144
145	if (lookupDescriptionInvolvement.Length > 0)	938✔
146	{
147	var lookupDescriptionIsIsolated = false;	30✔
148
149	//we have not found any foreign keys yet thats a problem
150	if (lastForeignKeyFound == null)	30!
151	{
152	var potentialWinners =	×
153	lookupDescriptionInvolvement.Where(	×
154	l => tablesUsedInQuery.Any(t => t.ID == l.ForeignKey.TableInfo_ID)).ToArray();	×
155
156	if (potentialWinners.Length ==	×
157	1) //or there are many options but only one which is in our existing table collection	×
158	{
159	lastForeignKeyFound =	×
160	potentialWinners[0]	×
161	.ForeignKey; //use it there aren't multiple foreign keys to pick from (which would result in uncertainty)	×
162	lookupDescriptionIsIsolated = true;	×
163	}
164	else
165	//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
166	{
167	throw new QueryBuildingException(	×
168	$"Found lookup description before encountering any lookup foreign keys (Description column was {ColumnsInOrder[i].UnderlyingColumn}) - make sure you always order Descriptions after their Foreign key and ensure they are in a contiguous block");	×
169	}
170	}
171
172	var correctLookupDescriptionInvolvement = lookupDescriptionInvolvement	30✔
173	.Where(lookup => lookup.ForeignKey.ID == lastForeignKeyFound.ID).ToArray();	60✔
174
175	if (correctLookupDescriptionInvolvement.Length == 0)	30!
176	{
177	//so there are no compatible foreign keys or the columns are a jumbled mess
178
179	//either way the last seen fk (or guessed fk) isn't right. So what fks could potentially be used with the Column?
180	var probableCorrectColumn = lookupDescriptionInvolvement.Where(	×
181	l =>	×
182	//any lookup where there is...	×
183	ColumnsInOrder.Any(	×
184	qtc =>	×
185	//a column with an ID equal to the fk	×
186	qtc.UnderlyingColumn != null && qtc.UnderlyingColumn.ID == l.ForeignKey_ID))	×
187	.ToArray();	×
188
189
190	var suggestions = "";	×
191	if (probableCorrectColumn.Any())	×
192	suggestions =	×
193	$"Possible foreign keys include:{string.Join(",", probableCorrectColumn.Select(l => l.ForeignKey))}";	×
194
195	throw new QueryBuildingException(	×
196	$"Encountered Lookup Description Column ({ColumnsInOrder[i].IColumn}) after first encountering Foreign Key ({lastForeignKeyFound}). Lookup description columns (_Desc) must come after the associated Foreign key.{suggestions}");	×
197	}
198
199	if (correctLookupDescriptionInvolvement.Length > 1)	30!
200	throw new QueryBuildingException(	×
201	$"Lookup description {ColumnsInOrder[i].UnderlyingColumn} appears to be configured as a Lookup Description twice with the same Lookup Table");	×
202
203	ColumnsInOrder[i].IsIsolatedLookupDescription = lookupDescriptionIsIsolated;	30✔
204	ColumnsInOrder[i].IsLookupForeignKey = false;	30✔
205	ColumnsInOrder[i].IsLookupDescription = true;	30✔
206	ColumnsInOrder[i].LookupTableAlias =	30✔
207	lookupTablesFound; // must belong to same one as previously encountered foreign key	30✔
208	ColumnsInOrder[i].LookupTable = correctLookupDescriptionInvolvement[0];	30✔
209
210	//see if there are any supplemental joins to tables that are not involved in the query
211	var supplementalJoins = correctLookupDescriptionInvolvement[0].GetSupplementalJoins();	30✔
212
213	if (supplementalJoins != null)	30✔
214	foreach (var supplementalJoin in supplementalJoins)	92✔
215	if (tablesUsedInQuery.All(t => t.ID != supplementalJoin.ForeignKey.TableInfo_ID))	32!
216	throw new QueryBuildingException(	×
217	$"Lookup requires supplemental join to column {supplementalJoin.ForeignKey} which is contained in a table that is not part of the SELECT column collection");	×
218	}
219	}
220	}	96✔
221
222
223	/// <summary>
224	/// Returns the line of SELECT Sql for this column that will appear in the final query
225	/// </summary>
226	/// <param name="hashingPattern"></param>
227	/// <param name="salt"></param>
228	/// <param name="syntaxHelper"></param>
229	/// <returns></returns>
230	public string GetSelectSQL(string hashingPattern, string salt, IQuerySyntaxHelper syntaxHelper)
231	{
232	var toReturn = IColumn.SelectSQL;	5,170✔
233
234	//deal with hashing
235	if (string.IsNullOrWhiteSpace(salt) == false && IColumn.HashOnDataRelease)	5,170✔
236	{
237	if (string.IsNullOrWhiteSpace(IColumn.Alias))	6!
238	throw new ArgumentException(	×
239	$"IExtractableColumn {IColumn} is missing an Alias (required for hashing)");	×
240
241	//if there is no custom hashing pattern
242	toReturn = string.IsNullOrWhiteSpace(hashingPattern)	6!
243	? syntaxHelper.HowDoWeAchieveMd5(toReturn)	6✔
244	: //use the DBMS specific one	6✔
245	string.Format(hashingPattern, toReturn, salt); //otherwise use the custom one	6✔
246	}
247
248	// 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
249	var trimmedSelectSQL =	5,170✔
250	toReturn.Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries)	5,170✔
251	.Select(s => s.Trim());	10,546✔
252	toReturn = string.Join(" ", trimmedSelectSQL);	5,170✔
253
254	//append alias to the end of the line if there is an alias
255	if (!string.IsNullOrWhiteSpace(IColumn.Alias))	5,170✔
256	toReturn += syntaxHelper.AliasPrefix + IColumn.Alias.Trim();	410✔
257
258	//cannot be both, we check for this earlier (see SetLookupStatus)
259	Debug.Assert(!(IsLookupDescription && IsLookupForeignKey));
260
261	//replace table name with table alias if it is a LookupDescription
262	if (IsLookupDescription)	5,170✔
263	{
264	var tableName = LookupTable.PrimaryKey.TableInfo.Name;	30✔
265
266	if (!toReturn.Contains(tableName))	30!
267	throw new Exception(	×
268	$"Column \"{toReturn}\" is a Lookup Description but its SELECT SQL does not include the Lookup table name \"{tableName}\"");	×
269
270	toReturn = toReturn.Replace(tableName, JoinHelper.GetLookupTableAlias(LookupTableAlias));	30✔
271	}
272
273	//actually don't need to do anything special for LookupForeignKeys
274
275	return toReturn;	5,170✔
276	}
277
278	/// <summary>
279	/// Runs checks on the <see cref="Core.QueryBuilding.IColumn"/> and translates any failures into <see cref="SyntaxErrorException"/>
280	/// </summary>
281	public void CheckSyntax()
282	{
283	//make sure to only throw SyntaxErrorException errors in here
284	try
285	{
286	IColumn.Check(ThrowImmediatelyCheckNotifier.Quiet);	4,130✔
287	var runtimeName = IColumn.GetRuntimeName();	4,130✔
288
289	if (string.IsNullOrWhiteSpace(runtimeName))	4,130!
290	throw new SyntaxErrorException("no runtime name");	×
291	}	4,130✔
292	catch (SyntaxErrorException exception)	×
293	{
294	throw new SyntaxErrorException(	×
295	$"Syntax failure on IExtractableColumn with SelectSQL=\"{IColumn.SelectSQL}\"", exception);	×
296	}
297	}	4,130✔
298
299	/// <summary>
300	/// 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
301	/// should determine whether or not to link to the table in the FROM section of the query.
302	/// </summary>
303	/// <param name="selectColumns"></param>
304	/// <returns></returns>
305	public bool IsLookupForeignKeyActuallyUsed(List<QueryTimeColumn> selectColumns)
306	{
307	if (!IsLookupForeignKey)	76!
308	return false;	×
309
310	//see if the description is used anywhere in the actual query columns!
311	return selectColumns.Any(c => c.IsLookupDescription && c.LookupTable.ID == LookupTable.ID);	696✔
312	}
313	}

HicServices / RDMP / 6706566541

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