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

85.19

/Rdmp.Core/Curation/DataHelper/JoinHelper.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.Diagnostics.Contracts;
using System.Text.RegularExpressions;
using Rdmp.Core.Curation.Data;

namespace Rdmp.Core.Curation.DataHelper;

/// <summary>
/// Generates ANSI Sql for joining tables together in the FROM line of an SQL query
/// </summary>
public class JoinHelper
{
    /// <summary>
    /// Assembles ANSI Sql for the JOIN section of a query including any supplemental join columns (e.g. T1 LEFT JOIN T2 on T1.A = T2.A AND T1.B = T2.B)
    /// </summary>
    /// <param name="join"></param>
    /// <returns></returns>
    public static string GetJoinSQL(IJoin join)
    {
        TableInfo fkTable = null;
        if (join.ForeignKey != null)
            fkTable = join.ForeignKey.TableInfo;

        TableInfo pkTable = null;
        if (join.PrimaryKey != null)
            pkTable = join.PrimaryKey.TableInfo;

        var foreignTable = fkTable == null ? "" : fkTable.Name;
        var primaryTable = pkTable == null ? "" : pkTable.Name;

        var key1 = join.ForeignKey == null ? "" : join.ForeignKey.Name;
        var key2 = join.PrimaryKey == null ? "" : join.PrimaryKey.Name;

        var joinType = join.ExtractionJoinType.ToString();

        var SQL =
            $"{foreignTable} {joinType} JOIN {primaryTable}{GetOnSql(join, foreignTable, primaryTable, key1, key2, out var hasCustomSql)}";

        SQL = AppendCollation(SQL, join.Collation);

        if (hasCustomSql)
            return SQL;

        SQL = AppendSupplementalJoins(SQL, join);

        return SQL;
    }

    private static string GetOnSql(IJoin join, string key1Table, string key2Table, string key1, string key2,
        out bool hasCustomSql)
    {
        var custom = join.GetCustomJoinSql();

        if (!string.IsNullOrWhiteSpace(custom))
        {
            hasCustomSql = true;
            custom = custom.Replace("{0}", key1Table);
            custom = custom.Replace("{1}", key2Table);

            // remove newlines in users SQL
            custom = Regex.Replace(custom, "\r?\n", " ");

            return $" ON {custom}";
        }

        hasCustomSql = false;

        return $" ON {key1} = {key2}";
    }

    /// <summary>
    /// Returns the first half of the join with an inverted join type
    /// 
    /// <para>Explanation:joins are defined as FK table JOIN_TYPE PK table so if you are requesting a join to the FK table it is assumed you are coming from the pk table therefore the join type is INVERTED i.e. LEFT becomes RIGHT</para>
    /// 
    /// </summary>
    /// <param name="join"></param>
    /// <returns></returns>
    public static string GetJoinSQLForeignKeySideOnly(IJoin join)
    {
        TableInfo fkTable = null;
        if (join.ForeignKey != null)
            fkTable = join.ForeignKey.TableInfo;

        TableInfo pkTable = null;
        if (join.PrimaryKey != null)
            pkTable = join.PrimaryKey.TableInfo;

        var foreignTable = fkTable == null ? "" : fkTable.Name;
        var primaryTable = pkTable == null ? "" : pkTable.Name;

        var key1 = join.ForeignKey == null ? "" : join.ForeignKey.Name;
        var key2 = join.PrimaryKey == null ? "" : join.PrimaryKey.Name;

        var SQL =
            $" {join.GetInvertedJoinType()} JOIN {foreignTable}{GetOnSql(join, foreignTable, primaryTable, key1, key2, out var hasCustomSql)}";

        SQL = AppendCollation(SQL, join);

        if (hasCustomSql)
            return SQL;

        SQL = AppendSupplementalJoins(SQL, join);


        return SQL;
    }


    /// <summary>
    /// Gets the JOIN Sql for the JoinInfo as foreign key JOIN primary key on fk.col1 = pk.col2.  Pass in a number
    /// in order to have the primary key table be assigned an alias e.g. 1 to give it t1
    /// 
    /// <para>Because join type refers to FK join PK and you are requesting "X" + " JOIN PK table on x" then the join is inverted e.g. LEFT => RIGHT and RIGHT => LEFT
    /// unless it is a lookup join which is always LEFT</para>
    /// </summary>
    /// <param name="join"></param>
    /// <param name="aliasNumber"></param>
    /// <returns></returns>
    public static string GetJoinSQLPrimaryKeySideOnly(IJoin join, int aliasNumber = -1)
    {
        TableInfo fkTable = null;
        if (join.ForeignKey != null)
            fkTable = join.ForeignKey.TableInfo;

        TableInfo pkTable = null;
        if (join.PrimaryKey != null)
            pkTable = join.PrimaryKey.TableInfo;

        var foreignTable = fkTable == null ? "" : fkTable.Name;
        var primaryTable = pkTable == null ? "" : pkTable.Name;

        //null check... could be required for display purposes where you have set up half the join when this is called
        var key1 = join.ForeignKey == null ? "" : join.ForeignKey.Name;
        var key2 = join.PrimaryKey == null ? "" : join.PrimaryKey.Name;

        string toReturn;
        bool hasCustomSql;

        //The lookup table is not being assigned an alias
        if (aliasNumber == -1)
        {
            toReturn =
                $" {join.ExtractionJoinType} JOIN {primaryTable}{GetOnSql(join, foreignTable, primaryTable, key1, key2, out hasCustomSql)}";
        }
        else
        {
            var lookupAlias = GetLookupTableAlias(aliasNumber);

            //the lookup table IS being assigned an alias so append As X after table name and change key2 of the join to X.col instead of tablename.col
            toReturn =
                $" {join.ExtractionJoinType} JOIN {primaryTable}{GetLookupTableAlias(aliasNumber, true)}{GetOnSql(join, foreignTable, lookupAlias, key1, key2.Replace(pkTable.Name, lookupAlias), out hasCustomSql)}";
        }

        toReturn = AppendCollation(toReturn, join);

        if (hasCustomSql)
            return toReturn;

        toReturn = AppendSupplementalJoins(toReturn, join, aliasNumber);

        return toReturn;
    }

    /// <summary>
    /// Gets the suffix for a given lookup table number
    /// </summary>
    /// <param name="aliasNumber">the lookup number e.g. 1 gives lookup_1</param>
    /// <param name="requirePrefix">pass in true if you require the prefix " AS " (may vary depending on database context in future e.g. perhaps MySql refers to tables by different alias syntax)</param>
    /// <returns></returns>
    public static string GetLookupTableAlias(int aliasNumber, bool requirePrefix = false) =>
        requirePrefix ? $" AS lookup_{aliasNumber}" : $"lookup_{aliasNumber}";


    [Pure]
    private static string AppendSupplementalJoins(string sql, IJoin join, int aliasNumber = -1)
    {
        var supplementalJoins = join.GetSupplementalJoins();

        if (supplementalJoins != null)
            foreach (var supplementalJoin in supplementalJoins)
            {
                var rightHalf = supplementalJoin.PrimaryKey.ToString();

                if (aliasNumber != -1)
                {
                    var lookupTable = join.PrimaryKey.TableInfo;
                    rightHalf = rightHalf.Replace(lookupTable.Name, GetLookupTableAlias(aliasNumber));
                }

                sql += $" AND {supplementalJoin.ForeignKey} = {rightHalf}";
                sql = AppendCollation(sql, supplementalJoin);
            }


        return sql;
    }


    [Pure]
    private static string AppendCollation(string sql, ISupplementalJoin supplementalJoin) =>
        AppendCollation(sql, supplementalJoin.Collation);

    [Pure]
    private static string AppendCollation(string sql, IJoin join) => AppendCollation(sql, join.Collation);

    [Pure]
    private static string AppendCollation(string sql, string collation) =>
        !string.IsNullOrWhiteSpace(collation) ? $"{sql} collate {collation}" : sql;
}

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.Diagnostics.Contracts;
8	using System.Text.RegularExpressions;
9	using Rdmp.Core.Curation.Data;
10
11	namespace Rdmp.Core.Curation.DataHelper;
12
13	/// <summary>
14	/// Generates ANSI Sql for joining tables together in the FROM line of an SQL query
15	/// </summary>
16	public class JoinHelper
17	{
18	/// <summary>
19	/// Assembles ANSI Sql for the JOIN section of a query including any supplemental join columns (e.g. T1 LEFT JOIN T2 on T1.A = T2.A AND T1.B = T2.B)
20	/// </summary>
21	/// <param name="join"></param>
22	/// <returns></returns>
23	public static string GetJoinSQL(IJoin join)
24	{
25	TableInfo fkTable = null;	76✔
26	if (join.ForeignKey != null)	76✔
27	fkTable = join.ForeignKey.TableInfo;	76✔
28
29	TableInfo pkTable = null;	76✔
30	if (join.PrimaryKey != null)	76✔
31	pkTable = join.PrimaryKey.TableInfo;	76✔
32
33	var foreignTable = fkTable == null ? "" : fkTable.Name;	76!
34	var primaryTable = pkTable == null ? "" : pkTable.Name;	76!
35
36	var key1 = join.ForeignKey == null ? "" : join.ForeignKey.Name;	76!
37	var key2 = join.PrimaryKey == null ? "" : join.PrimaryKey.Name;	76!
38
39	var joinType = join.ExtractionJoinType.ToString();	76✔
40
41	var SQL =	76✔
42	$"{foreignTable} {joinType} JOIN {primaryTable}{GetOnSql(join, foreignTable, primaryTable, key1, key2, out var hasCustomSql)}";	76✔
43
44	SQL = AppendCollation(SQL, join.Collation);	76✔
45
46	if (hasCustomSql)	76✔
47	return SQL;	2✔
48
49	SQL = AppendSupplementalJoins(SQL, join);	74✔
50
51	return SQL;	74✔
52	}
53
54	private static string GetOnSql(IJoin join, string key1Table, string key2Table, string key1, string key2,
55	out bool hasCustomSql)
56	{
57	var custom = join.GetCustomJoinSql();	114✔
58
59	if (!string.IsNullOrWhiteSpace(custom))	114✔
60	{
61	hasCustomSql = true;	2✔
62	custom = custom.Replace("{0}", key1Table);	2✔
63	custom = custom.Replace("{1}", key2Table);	2✔
64
65	// remove newlines in users SQL
66	custom = Regex.Replace(custom, "\r?\n", " ");	2✔
67
68	return $" ON {custom}";	2✔
69	}
70
71	hasCustomSql = false;	112✔
72
73	return $" ON {key1} = {key2}";	112✔
74	}
75
76	/// <summary>
77	/// Returns the first half of the join with an inverted join type
78	///
79	/// <para>Explanation:joins are defined as FK table JOIN_TYPE PK table so if you are requesting a join to the FK table it is assumed you are coming from the pk table therefore the join type is INVERTED i.e. LEFT becomes RIGHT</para>
80	///
81	/// </summary>
82	/// <param name="join"></param>
83	/// <returns></returns>
84	public static string GetJoinSQLForeignKeySideOnly(IJoin join)
85	{
86	TableInfo fkTable = null;	8✔
87	if (join.ForeignKey != null)	8✔
88	fkTable = join.ForeignKey.TableInfo;	8✔
89
90	TableInfo pkTable = null;	8✔
91	if (join.PrimaryKey != null)	8✔
92	pkTable = join.PrimaryKey.TableInfo;	8✔
93
94	var foreignTable = fkTable == null ? "" : fkTable.Name;	8!
95	var primaryTable = pkTable == null ? "" : pkTable.Name;	8!
96
97	var key1 = join.ForeignKey == null ? "" : join.ForeignKey.Name;	8!
98	var key2 = join.PrimaryKey == null ? "" : join.PrimaryKey.Name;	8!
99
100	var SQL =	8✔
101	$" {join.GetInvertedJoinType()} JOIN {foreignTable}{GetOnSql(join, foreignTable, primaryTable, key1, key2, out var hasCustomSql)}";	8✔
102
103	SQL = AppendCollation(SQL, join);	8✔
104
105	if (hasCustomSql)	8!
106	return SQL;	×
107
108	SQL = AppendSupplementalJoins(SQL, join);	8✔
109
110
111	return SQL;	8✔
112	}
113
114
115	/// <summary>
116	/// Gets the JOIN Sql for the JoinInfo as foreign key JOIN primary key on fk.col1 = pk.col2. Pass in a number
117	/// in order to have the primary key table be assigned an alias e.g. 1 to give it t1
118	///
119	/// <para>Because join type refers to FK join PK and you are requesting "X" + " JOIN PK table on x" then the join is inverted e.g. LEFT => RIGHT and RIGHT => LEFT
120	/// unless it is a lookup join which is always LEFT</para>
121	/// </summary>
122	/// <param name="join"></param>
123	/// <param name="aliasNumber"></param>
124	/// <returns></returns>
125	public static string GetJoinSQLPrimaryKeySideOnly(IJoin join, int aliasNumber = -1)
126	{
127	TableInfo fkTable = null;	30✔
128	if (join.ForeignKey != null)	30✔
129	fkTable = join.ForeignKey.TableInfo;	30✔
130
131	TableInfo pkTable = null;	30✔
132	if (join.PrimaryKey != null)	30✔
133	pkTable = join.PrimaryKey.TableInfo;	30✔
134
135	var foreignTable = fkTable == null ? "" : fkTable.Name;	30!
136	var primaryTable = pkTable == null ? "" : pkTable.Name;	30!
137
138	//null check... could be required for display purposes where you have set up half the join when this is called
139	var key1 = join.ForeignKey == null ? "" : join.ForeignKey.Name;	30!
140	var key2 = join.PrimaryKey == null ? "" : join.PrimaryKey.Name;	30!
141
142	string toReturn;
143	bool hasCustomSql;
144
145	//The lookup table is not being assigned an alias
146	if (aliasNumber == -1)	30!
147	{
148	toReturn =	×
149	$" {join.ExtractionJoinType} JOIN {primaryTable}{GetOnSql(join, foreignTable, primaryTable, key1, key2, out hasCustomSql)}";	×
150	}
151	else
152	{
153	var lookupAlias = GetLookupTableAlias(aliasNumber);	30✔
154
155	//the lookup table IS being assigned an alias so append As X after table name and change key2 of the join to X.col instead of tablename.col
156	toReturn =	30✔
157	$" {join.ExtractionJoinType} JOIN {primaryTable}{GetLookupTableAlias(aliasNumber, true)}{GetOnSql(join, foreignTable, lookupAlias, key1, key2.Replace(pkTable.Name, lookupAlias), out hasCustomSql)}";	30✔
158	}
159
160	toReturn = AppendCollation(toReturn, join);	30✔
161
162	if (hasCustomSql)	30!
163	return toReturn;	×
164
165	toReturn = AppendSupplementalJoins(toReturn, join, aliasNumber);	30✔
166
167	return toReturn;	30✔
168	}
169
170	/// <summary>
171	/// Gets the suffix for a given lookup table number
172	/// </summary>
173	/// <param name="aliasNumber">the lookup number e.g. 1 gives lookup_1</param>
174	/// <param name="requirePrefix">pass in true if you require the prefix " AS " (may vary depending on database context in future e.g. perhaps MySql refers to tables by different alias syntax)</param>
175	/// <returns></returns>
176	public static string GetLookupTableAlias(int aliasNumber, bool requirePrefix = false) =>
177	requirePrefix ? $" AS lookup_{aliasNumber}" : $"lookup_{aliasNumber}";	106✔
178
179
180	[Pure]
181	private static string AppendSupplementalJoins(string sql, IJoin join, int aliasNumber = -1)
182	{
183	var supplementalJoins = join.GetSupplementalJoins();	112✔
184
185	if (supplementalJoins != null)	112✔
186	foreach (var supplementalJoin in supplementalJoins)	260✔
187	{
188	var rightHalf = supplementalJoin.PrimaryKey.ToString();	18✔
189
190	if (aliasNumber != -1)	18✔
191	{
192	var lookupTable = join.PrimaryKey.TableInfo;	16✔
193	rightHalf = rightHalf.Replace(lookupTable.Name, GetLookupTableAlias(aliasNumber));	16✔
194	}
195
196	sql += $" AND {supplementalJoin.ForeignKey} = {rightHalf}";	18✔
197	sql = AppendCollation(sql, supplementalJoin);	18✔
198	}
199
200
201	return sql;	112✔
202	}
203
204
205	[Pure]
206	private static string AppendCollation(string sql, ISupplementalJoin supplementalJoin) =>
207	AppendCollation(sql, supplementalJoin.Collation);	18✔
208
209	[Pure]
210	private static string AppendCollation(string sql, IJoin join) => AppendCollation(sql, join.Collation);	38✔
211
212	[Pure]
213	private static string AppendCollation(string sql, string collation) =>
214	!string.IsNullOrWhiteSpace(collation) ? $"{sql} collate {collation}" : sql;	132!
215	}

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