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

91.3

/Rdmp.Core/DataLoad/Modules/Mutilators/QueryBuilders/BackfillSqlHelper.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 FAnsi.Discovery;
using Rdmp.Core.Curation.Data;

namespace Rdmp.Core.DataLoad.Modules.Mutilators.QueryBuilders;

/// <summary>
/// Helps generate sql queries for reverting/deleting STAGING based on records in LIVE during a backfill data load (See StagingBackfillMutilator).
/// </summary>
public class BackfillSqlHelper
{
    private readonly ColumnInfo _timePeriodicityField;
    private readonly DiscoveredDatabase _stagingDbInfo;
    private readonly DiscoveredDatabase _liveDbInfo;
    private readonly TableInfo _tiWithTimeColumn;

    public BackfillSqlHelper(ColumnInfo timePeriodicityField, DiscoveredDatabase stagingDbInfo,
        DiscoveredDatabase liveDbInfo)
    {
        _timePeriodicityField = timePeriodicityField;
        _stagingDbInfo = stagingDbInfo;
        _liveDbInfo = liveDbInfo;
        _tiWithTimeColumn = _timePeriodicityField.TableInfo;
    }

    /// <summary>
    /// Composes the SQL which joins the supplied table back up or down to the TimePeriodicity table, so we can assign the rows an effective load date
    /// </summary>
    /// <param name="tableAlias"></param>
    /// <param name="tableInfo"></param>
    /// <param name="timePeriodTableAlias"></param>
    /// <param name="dbInfo"></param>
    /// <param name="joinPath"></param>
    /// <returns></returns>
    public string CreateSqlForJoinToTimePeriodicityTable(string tableAlias, ITableInfo tableInfo,
        string timePeriodTableAlias, DiscoveredDatabase dbInfo, List<JoinInfo> joinPath)
    {
        if (tableInfo.ID == _timePeriodicityField.TableInfo_ID && joinPath.Count > 0)
            throw new InvalidOperationException(
                "You have asked for a join where the original table *is* the TimePeriodicityTable but a non-empty join path has been provided. There should be no path when dealing directly with the TimePeriodicity table");

        // Simple case, there is no join so we are just selecting the row and aliasing the TimePeriodicityField for the provided table
        if (!joinPath.Any())
            return string.Format(@"SELECT {0}.*, {0}.{1} AS TimePeriodicityField FROM {2} {0}",
                tableAlias, _timePeriodicityField.GetRuntimeName(),
                $"[{dbInfo.GetRuntimeName()}]..[{tableInfo.GetRuntimeName()}]");

        // Ensure that the TimePeriodicityTable is at the end of the path (to make constructing the join a bit easier)
        if (joinPath[0].ForeignKey.TableInfo_ID == _tiWithTimeColumn.ID ||
            joinPath[0].PrimaryKey.TableInfo_ID == _tiWithTimeColumn.ID)
            joinPath.Reverse();

        if (joinPath[^1].ForeignKey.TableInfo_ID != _tiWithTimeColumn.ID &&
            joinPath[^1].PrimaryKey.TableInfo_ID != _tiWithTimeColumn.ID)
            throw new InvalidOperationException(
                "The TimePeriodicity table is not at the beginning or end of the join path.");

        var sql =
            $@"SELECT {tableAlias}.*, {timePeriodTableAlias}.{_timePeriodicityField.GetRuntimeName()} AS TimePeriodicityField 
FROM {$"[{dbInfo.GetRuntimeName()}]..[{tableInfo.GetRuntimeName()}]"} {tableAlias}";

        // Is our table a parent or child? The join is composed differently.
        var ascending = tableInfo.ID == joinPath[0].ForeignKey.TableInfo_ID;

        for (var i = 0; i < joinPath.Count; i++)
        {
            var join = joinPath[i];

            if (ascending)
            {
                var parentTable = join.PrimaryKey.TableInfo;
                var childTableAlias = i == 0 ? tableAlias : $"j{i}";
                var parentTableAlias = i == joinPath.Count - 1 ? timePeriodTableAlias : $"j{i + 1}";

                sql += string.Format(@"
LEFT JOIN {0} {1} ON {1}.{3} = {2}.{4}",
                    $"[{dbInfo.GetRuntimeName()}]..[{parentTable.GetRuntimeName()}]",
                    parentTableAlias,
                    childTableAlias,
                    join.PrimaryKey.GetRuntimeName(),
                    join.ForeignKey.GetRuntimeName());
            }
            else
            {
                var childTable = join.ForeignKey.TableInfo;
                var parentTableAlias = i == 0 ? tableAlias : $"j{i + 1}";
                var childTableAlias = i == joinPath.Count - 1 ? timePeriodTableAlias : $"j{i}";

                sql += string.Format(@"
LEFT JOIN {0} {1} ON {2}.{3} = {1}.{4}",
                    $"[{dbInfo.GetRuntimeName()}]..[{childTable.GetRuntimeName()}]",
                    childTableAlias,
                    parentTableAlias,
                    join.PrimaryKey.GetRuntimeName(),
                    join.ForeignKey.GetRuntimeName());
            }
        }

        return sql;
    }


    public string GetSQLComparingStagingAndLiveTables(ITableInfo tiCurrent, List<JoinInfo> joinPathToTimeTable)
    {
        // All rows in STAGING tiCurrent + the time from the TimePeriodicity table
        var toLoadWithTimeSQL =
            $"({CreateSqlForJoinToTimePeriodicityTable("CurrentTable", tiCurrent, "TimePeriodicityTable", _stagingDbInfo, joinPathToTimeTable)}) AS ToLoadWithTime";

        // All rows in LIVE tiCurrent + the time from the TimePeriodicity table
        var loadedWithTimeSQL =
            $"({CreateSqlForJoinToTimePeriodicityTable("LiveCurrentTable", tiCurrent, "LiveTimePeriodicityTable", _liveDbInfo, joinPathToTimeTable)}) AS LoadedWithTime";

        var pksForCurrent = tiCurrent.ColumnInfos.Where(info => info.IsPrimaryKey);
        var pkEquality = string.Join(" AND ",
            pksForCurrent.Select(
                info => $"ToLoadWithTime.{info.GetRuntimeName()} = LoadedWithTime.{info.GetRuntimeName()}"));

        // Join to leave valid STAGING rows which are stale, or whose relation with the TimePeriodicity field has been severed and as such should be deleted anyway (since we can't assign a date to the record)
        var cte = $@"
{toLoadWithTimeSQL} 
RIGHT JOIN 
{loadedWithTimeSQL} 
ON {pkEquality} 
WHERE ToLoadWithTime.ID IS NOT NULL 
    AND (ToLoadWithTime.TimePeriodicityField <= LoadedWithTime.TimePeriodicityField OR ToLoadWithTime.TimePeriodicityField IS NULL) 
)";
        return cte;
    }
}

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 FAnsi.Discovery;
11	using Rdmp.Core.Curation.Data;
12
13	namespace Rdmp.Core.DataLoad.Modules.Mutilators.QueryBuilders;
14
15	/// <summary>
16	/// Helps generate sql queries for reverting/deleting STAGING based on records in LIVE during a backfill data load (See StagingBackfillMutilator).
17	/// </summary>
18	public class BackfillSqlHelper
19	{
20	private readonly ColumnInfo _timePeriodicityField;
21	private readonly DiscoveredDatabase _stagingDbInfo;
22	private readonly DiscoveredDatabase _liveDbInfo;
23	private readonly TableInfo _tiWithTimeColumn;
24
25	public BackfillSqlHelper(ColumnInfo timePeriodicityField, DiscoveredDatabase stagingDbInfo,	24✔
26	DiscoveredDatabase liveDbInfo)	24✔
27	{
28	_timePeriodicityField = timePeriodicityField;	24✔
29	_stagingDbInfo = stagingDbInfo;	24✔
30	_liveDbInfo = liveDbInfo;	24✔
31	_tiWithTimeColumn = _timePeriodicityField.TableInfo;	24✔
32	}	24✔
33
34	/// <summary>
35	/// Composes the SQL which joins the supplied table back up or down to the TimePeriodicity table, so we can assign the rows an effective load date
36	/// </summary>
37	/// <param name="tableAlias"></param>
38	/// <param name="tableInfo"></param>
39	/// <param name="timePeriodTableAlias"></param>
40	/// <param name="dbInfo"></param>
41	/// <param name="joinPath"></param>
42	/// <returns></returns>
43	public string CreateSqlForJoinToTimePeriodicityTable(string tableAlias, ITableInfo tableInfo,
44	string timePeriodTableAlias, DiscoveredDatabase dbInfo, List<JoinInfo> joinPath)
45	{
46	if (tableInfo.ID == _timePeriodicityField.TableInfo_ID && joinPath.Count > 0)	234!
47	throw new InvalidOperationException(	×
48	"You have asked for a join where the original table is the TimePeriodicityTable but a non-empty join path has been provided. There should be no path when dealing directly with the TimePeriodicity table");	×
49
50	// Simple case, there is no join so we are just selecting the row and aliasing the TimePeriodicityField for the provided table
51	if (!joinPath.Any())	234✔
52	return string.Format(@"SELECT {0}.*, {0}.{1} AS TimePeriodicityField FROM {2} {0}",	176✔
53	tableAlias, _timePeriodicityField.GetRuntimeName(),	176✔
54	$"[{dbInfo.GetRuntimeName()}]..[{tableInfo.GetRuntimeName()}]");	176✔
55
56	// Ensure that the TimePeriodicityTable is at the end of the path (to make constructing the join a bit easier)
57	if (joinPath[0].ForeignKey.TableInfo_ID == _tiWithTimeColumn.ID \|\|	58✔
58	joinPath[0].PrimaryKey.TableInfo_ID == _tiWithTimeColumn.ID)	58✔
59	joinPath.Reverse();	58✔
60
61	if (joinPath[^1].ForeignKey.TableInfo_ID != _tiWithTimeColumn.ID &&	58!
62	joinPath[^1].PrimaryKey.TableInfo_ID != _tiWithTimeColumn.ID)	58✔
63	throw new InvalidOperationException(	×
64	"The TimePeriodicity table is not at the beginning or end of the join path.");	×
65
66	var sql =	58✔
67	$@"SELECT {tableAlias}.*, {timePeriodTableAlias}.{_timePeriodicityField.GetRuntimeName()} AS TimePeriodicityField	58✔
68	FROM {$"[{dbInfo.GetRuntimeName()}]..[{tableInfo.GetRuntimeName()}]"} {tableAlias}";	58✔
69
70	// Is our table a parent or child? The join is composed differently.
71	var ascending = tableInfo.ID == joinPath[0].ForeignKey.TableInfo_ID;	58✔
72
73	for (var i = 0; i < joinPath.Count; i++)	236✔
74	{
75	var join = joinPath[i];	60✔
76
77	if (ascending)	60✔
78	{
79	var parentTable = join.PrimaryKey.TableInfo;	28✔
80	var childTableAlias = i == 0 ? tableAlias : $"j{i}";	28✔
81	var parentTableAlias = i == joinPath.Count - 1 ? timePeriodTableAlias : $"j{i + 1}";	28✔
82
83	sql += string.Format(@"	28✔
84	LEFT JOIN {0} {1} ON {1}.{3} = {2}.{4}",	28✔
85	$"[{dbInfo.GetRuntimeName()}]..[{parentTable.GetRuntimeName()}]",	28✔
86	parentTableAlias,	28✔
87	childTableAlias,	28✔
88	join.PrimaryKey.GetRuntimeName(),	28✔
89	join.ForeignKey.GetRuntimeName());	28✔
90	}
91	else
92	{
93	var childTable = join.ForeignKey.TableInfo;	32✔
94	var parentTableAlias = i == 0 ? tableAlias : $"j{i + 1}";	32!
95	var childTableAlias = i == joinPath.Count - 1 ? timePeriodTableAlias : $"j{i}";	32!
96
97	sql += string.Format(@"	32✔
98	LEFT JOIN {0} {1} ON {2}.{3} = {1}.{4}",	32✔
99	$"[{dbInfo.GetRuntimeName()}]..[{childTable.GetRuntimeName()}]",	32✔
100	childTableAlias,	32✔
101	parentTableAlias,	32✔
102	join.PrimaryKey.GetRuntimeName(),	32✔
103	join.ForeignKey.GetRuntimeName());	32✔
104	}
105	}
106
107	return sql;	58✔
108	}
109
110
111	public string GetSQLComparingStagingAndLiveTables(ITableInfo tiCurrent, List<JoinInfo> joinPathToTimeTable)
112	{
113	// All rows in STAGING tiCurrent + the time from the TimePeriodicity table
114	var toLoadWithTimeSQL =	116✔
115	$"({CreateSqlForJoinToTimePeriodicityTable("CurrentTable", tiCurrent, "TimePeriodicityTable", _stagingDbInfo, joinPathToTimeTable)}) AS ToLoadWithTime";	116✔
116
117	// All rows in LIVE tiCurrent + the time from the TimePeriodicity table
118	var loadedWithTimeSQL =	116✔
119	$"({CreateSqlForJoinToTimePeriodicityTable("LiveCurrentTable", tiCurrent, "LiveTimePeriodicityTable", _liveDbInfo, joinPathToTimeTable)}) AS LoadedWithTime";	116✔
120
121	var pksForCurrent = tiCurrent.ColumnInfos.Where(info => info.IsPrimaryKey);	712✔
122	var pkEquality = string.Join(" AND ",	116✔
123	pksForCurrent.Select(	116✔
124	info => $"ToLoadWithTime.{info.GetRuntimeName()} = LoadedWithTime.{info.GetRuntimeName()}"));	232✔
125
126	// Join to leave valid STAGING rows which are stale, or whose relation with the TimePeriodicity field has been severed and as such should be deleted anyway (since we can't assign a date to the record)
127	var cte = $@"	116✔
128	{toLoadWithTimeSQL}	116✔
129	RIGHT JOIN	116✔
130	{loadedWithTimeSQL}	116✔
131	ON {pkEquality}	116✔
132	WHERE ToLoadWithTime.ID IS NOT NULL	116✔
133	AND (ToLoadWithTime.TimePeriodicityField <= LoadedWithTime.TimePeriodicityField OR ToLoadWithTime.TimePeriodicityField IS NULL)	116✔
134	)";	116✔
135	return cte;	116✔
136	}
137	}

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