13318089130

Committed 13 Feb 2025 10:13PM UTC coverage: 57.398% (+0.004%) from 57.394%

Build # 13318089130

Build Type

Pull #2134

github

Committed by

jas88

Commit Message

Update ChildProviderTests.cs

Fix up TestUpTo method

Pull Request Pull Request #2134: CodeQL fixups

Run Details

11346 of 21308 branches covered (53.25%)

Branch coverage included in aggregate %.

104 of 175 new or added lines in 45 files covered. (59.43%)

362 existing lines in 23 files now uncovered.

32218 of 54590 relevant lines covered (59.02%)

17091.93 hits per line

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

86.61

/Rdmp.Core/DataLoad/Triggers/Implementations/TriggerImplementer.cs

// Copyright (c) The University of Dundee 2018-2019
// This file is part of the Research Data Management Platform (RDMP).
// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using FAnsi.Discovery;
using FAnsi.Discovery.QuerySyntax;
using Rdmp.Core.DataLoad.Triggers.Exceptions;
using Rdmp.Core.ReusableLibraryCode.Checks;
using Rdmp.Core.ReusableLibraryCode.Settings;
using TypeGuesser;

namespace Rdmp.Core.DataLoad.Triggers.Implementations;

/// <summary>
/// Trigger implementer for that creates archive triggers on tables.  This is a prerequisite for the RDMP DLE and ensures that
/// when updates in a load replace live records the old state is persisted.
/// </summary>
public abstract partial class TriggerImplementer : ITriggerImplementer
{
    protected readonly bool _createDataLoadRunIdAlso;

    protected readonly DiscoveredServer _server;
    protected readonly DiscoveredTable _table;
    protected readonly DiscoveredTable _archiveTable;
    protected DiscoveredColumn[] _columns;
    protected readonly DiscoveredColumn[] _primaryKeys;

    /// <summary>
    /// Trigger implementer for that creates a trigger on <paramref name="table"/> which records old UPDATE
    /// records into an _Archive table.  <paramref name="table"/> must have primary keys
    /// </summary>
    /// <param name="table"></param>
    /// <param name="createDataLoadRunIDAlso"></param>
    protected TriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true)
    {
        _server = table.Database.Server;
        _table = table;
        _archiveTable = _table.Database.ExpectTable($"{table.GetRuntimeName()}_Archive", table.Schema);
        _columns = table.DiscoverColumns();
        _primaryKeys = _columns.Where(c => c.IsPrimaryKey).ToArray();

        _createDataLoadRunIdAlso = createDataLoadRunIDAlso;
    }

    public abstract void DropTrigger(out string problemsDroppingTrigger, out string thingsThatWorkedDroppingTrigger);

    public virtual string CreateTrigger(ICheckNotifier notifier)
    {
        if (!_primaryKeys.Any())
            throw new TriggerException("There must be at least 1 primary key");

        //if _Archive exists skip creating it
        var skipCreatingArchive = _archiveTable.Exists();

        //check _Archive does not already exist
        foreach (var forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" })
            if (_columns.Any(c =>
                    c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase)))
                throw new TriggerException(
                    $"Table {_table} already contains a column called {forbiddenColumnName} this column is reserved for Archiving");

        var b_mustCreate_validFrom = !_columns.Any(c =>
            c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase));
        var b_mustCreate_dataloadRunId =
            !_columns.Any(c =>
                c.GetRuntimeName()
                    .Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) &&
            _createDataLoadRunIdAlso;

        //forces column order dataloadrunID then valid from (doesnt prevent these being in the wrong place in the record but hey ho - possibly not an issue anyway since probably the 3 values in the archive are what matters for order - see the Trigger which populates *,X,Y,Z where * is all columns in mane table
        if (b_mustCreate_dataloadRunId && !b_mustCreate_validFrom)
            throw new TriggerException(
                $"Cannot create trigger because table contains {SpecialFieldNames.ValidFrom} but not {SpecialFieldNames.DataLoadRunID} (ID must be placed before valid from in column order)");

        //must add validFrom outside of transaction if we want SMO to pick it up
        if (b_mustCreate_dataloadRunId)
            _table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true,
                UserSettings.ArchiveTriggerTimeout);

        var syntaxHelper = _server.GetQuerySyntaxHelper();


        //must add validFrom outside of transaction if we want SMO to pick it up
        if (b_mustCreate_validFrom)
            AddValidFrom(_table, syntaxHelper);

        //if we created columns we need to update _column
        if (b_mustCreate_dataloadRunId || b_mustCreate_validFrom)
            _columns = _table.DiscoverColumns();

        var sql = WorkOutArchiveTableCreationSQL();

        if (!skipCreatingArchive)
        {
            using var con = _server.GetConnection();
            con.Open();

            using (var cmdCreateArchive = _server.GetCommand(sql, con))
            {
                cmdCreateArchive.CommandTimeout = UserSettings.ArchiveTriggerTimeout;
                cmdCreateArchive.ExecuteNonQuery();
            }


            _archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true,
                UserSettings.ArchiveTriggerTimeout);
            _archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true,
                UserSettings.ArchiveTriggerTimeout);
            _archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true,
                UserSettings.ArchiveTriggerTimeout);
        }

        return sql;
    }

    protected virtual void AddValidFrom(DiscoveredTable table, IQuerySyntaxHelper syntaxHelper)
    {
        var dateTimeDatatype =
            syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));
        var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);

        _table.AddColumn(SpecialFieldNames.ValidFrom, $" {dateTimeDatatype} DEFAULT {nowFunction}", true,
            UserSettings.ArchiveTriggerTimeout);
    }


    private string WorkOutArchiveTableCreationSQL()
    {
        //script original table
        var createTableSQL = _table.ScriptTableCreation(true, true, true);

        var toReplaceTableName = $"CREATE TABLE {_table.GetFullyQualifiedName()}";

        if (!createTableSQL.Contains(toReplaceTableName))
            throw new Exception($"Expected to find occurrence of {toReplaceTableName} in the SQL {createTableSQL}");

        //rename table
        createTableSQL = createTableSQL.Replace(toReplaceTableName,
            $"CREATE TABLE {_archiveTable.GetFullyQualifiedName()}");

        //drop identity bit
        createTableSQL = RemoveIdentities().Replace(createTableSQL, "");

        return createTableSQL;
    }

    public abstract TriggerStatus GetTriggerStatus();

    /// <summary>
    /// Returns true if the trigger exists and the method body of the trigger matches the expected method body.  This exists to handle
    /// the situation where a trigger is created on a table then the schema of the live table or the archive table is altered subsequently.
    /// 
    /// <para>The best way to implement this is to regenerate the trigger and compare it to the current code fetched from the ddl</para>
    /// 
    /// </summary>
    /// <returns></returns>
    public virtual bool CheckUpdateTriggerIsEnabledAndHasExpectedBody()
    {
        //check server has trigger and it is on
        var isEnabledSimple = GetTriggerStatus();

        if (isEnabledSimple == TriggerStatus.Disabled || isEnabledSimple == TriggerStatus.Missing)
            return false;

        CheckColumnDefinitionsMatchArchive();

        return true;
    }

    private void CheckColumnDefinitionsMatchArchive()
    {
        var errors = new List<string>();

        var archiveTableCols = _archiveTable.DiscoverColumns().ToArray();

        foreach (var col in _columns)
        {
            var colInArchive = archiveTableCols.SingleOrDefault(c => c.GetRuntimeName().Equals(col.GetRuntimeName()));

            if (colInArchive == null)
                errors.Add(
                    $"Column {col.GetRuntimeName()} appears in Table '{_table}' but not in archive table '{_archiveTable}'");
            else if (!AreCompatibleDatatypes(col.DataType, colInArchive.DataType))
                errors.Add(
                    $"Column {col.GetRuntimeName()} has data type '{col.DataType}' in '{_table}' but in Archive table '{_archiveTable}' it is defined as '{colInArchive.DataType}'");
        }

        if (errors.Any())
            throw new IrreconcilableColumnDifferencesInArchiveException(
                $"The following column mismatch errors were seen:{Environment.NewLine}{string.Join(Environment.NewLine, errors)}");
    }

    private static bool AreCompatibleDatatypes(DiscoveredDataType mainDataType, DiscoveredDataType archiveDataType)
    {
        var t1 = mainDataType.SQLType;
        var t2 = archiveDataType.SQLType;

        if (t1.Equals(t2, StringComparison.CurrentCultureIgnoreCase))
            return true;

        return t1.Contains("identity", StringComparison.OrdinalIgnoreCase) &&
               t1.Replace("identity", "", StringComparison.OrdinalIgnoreCase).Trim()
                   .Equals(t2.Trim(), StringComparison.OrdinalIgnoreCase);
    }

    [GeneratedRegex(@"IDENTITY\(\d+,\d+\)")]
    private static partial Regex RemoveIdentities();
}

1	// Copyright (c) The University of Dundee 2018-2019
2	// This file is part of the Research Data Management Platform (RDMP).
3	// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
4	// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
5	// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.
6
7	using System;
8	using System.Collections.Generic;
9	using System.Linq;
10	using System.Text.RegularExpressions;
11	using FAnsi.Discovery;
12	using FAnsi.Discovery.QuerySyntax;
13	using Rdmp.Core.DataLoad.Triggers.Exceptions;
14	using Rdmp.Core.ReusableLibraryCode.Checks;
15	using Rdmp.Core.ReusableLibraryCode.Settings;
16	using TypeGuesser;
17
18	namespace Rdmp.Core.DataLoad.Triggers.Implementations;
19
20	/// <summary>
21	/// Trigger implementer for that creates archive triggers on tables. This is a prerequisite for the RDMP DLE and ensures that
22	/// when updates in a load replace live records the old state is persisted.
23	/// </summary>
24	public abstract partial class TriggerImplementer : ITriggerImplementer
25	{
26	protected readonly bool _createDataLoadRunIdAlso;
27
28	protected readonly DiscoveredServer _server;
29	protected readonly DiscoveredTable _table;
30	protected readonly DiscoveredTable _archiveTable;
31	protected DiscoveredColumn[] _columns;
32	protected readonly DiscoveredColumn[] _primaryKeys;
33
34	/// <summary>
35	/// Trigger implementer for that creates a trigger on <paramref name="table"/> which records old UPDATE
36	/// records into an _Archive table. <paramref name="table"/> must have primary keys
37	/// </summary>
38	/// <param name="table"></param>
39	/// <param name="createDataLoadRunIDAlso"></param>
40	protected TriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true)	280✔
41	{
42	_server = table.Database.Server;	280✔
43	_table = table;	280✔
44	_archiveTable = _table.Database.ExpectTable($"{table.GetRuntimeName()}_Archive", table.Schema);	280✔
45	_columns = table.DiscoverColumns();	280✔
46	_primaryKeys = _columns.Where(c => c.IsPrimaryKey).ToArray();	2,976✔
47
48	_createDataLoadRunIdAlso = createDataLoadRunIDAlso;	280✔
49	}	280✔
50
51	public abstract void DropTrigger(out string problemsDroppingTrigger, out string thingsThatWorkedDroppingTrigger);
52
53	public virtual string CreateTrigger(ICheckNotifier notifier)
54	{
55	if (!_primaryKeys.Any())	132✔
56	throw new TriggerException("There must be at least 1 primary key");	8✔
57
58	//if _Archive exists skip creating it
59	var skipCreatingArchive = _archiveTable.Exists();	124✔
60
61	//check _Archive does not already exist
62	foreach (var forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" })	992✔
63	if (_columns.Any(c =>	372!
64	c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase)))	3,894✔
65	throw new TriggerException(	×
66	$"Table {_table} already contains a column called {forbiddenColumnName} this column is reserved for Archiving");	×
67
68	var b_mustCreate_validFrom = !_columns.Any(c =>	124✔
69	c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase));	1,286✔
70	var b_mustCreate_dataloadRunId =	124✔
71	!_columns.Any(c =>	124✔
72	c.GetRuntimeName()	1,144✔
73	.Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) &&	1,144✔
74	_createDataLoadRunIdAlso;	124✔
75
76	//forces column order dataloadrunID then valid from (doesnt prevent these being in the wrong place in the record but hey ho - possibly not an issue anyway since probably the 3 values in the archive are what matters for order - see the Trigger which populates ,X,Y,Z where is all columns in mane table
77	if (b_mustCreate_dataloadRunId && !b_mustCreate_validFrom)	124!
78	throw new TriggerException(	×
79	$"Cannot create trigger because table contains {SpecialFieldNames.ValidFrom} but not {SpecialFieldNames.DataLoadRunID} (ID must be placed before valid from in column order)");	×
80
81	//must add validFrom outside of transaction if we want SMO to pick it up
82	if (b_mustCreate_dataloadRunId)	124✔
83	_table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true,	84✔
84	UserSettings.ArchiveTriggerTimeout);	84✔
85
86	var syntaxHelper = _server.GetQuerySyntaxHelper();	124✔
87
88
89	//must add validFrom outside of transaction if we want SMO to pick it up
90	if (b_mustCreate_validFrom)	124✔
91	AddValidFrom(_table, syntaxHelper);	106✔
92
93	//if we created columns we need to update _column
94	if (b_mustCreate_dataloadRunId \|\| b_mustCreate_validFrom)	124✔
95	_columns = _table.DiscoverColumns();	106✔
96
97	var sql = WorkOutArchiveTableCreationSQL();	124✔
98
99	if (!skipCreatingArchive)	124✔
100	{
101	using var con = _server.GetConnection();	110✔
102	con.Open();	110✔
103
104	using (var cmdCreateArchive = _server.GetCommand(sql, con))	110✔
105	{
106	cmdCreateArchive.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	110✔
107	cmdCreateArchive.ExecuteNonQuery();	110✔
108	}	110✔
109
110
111	_archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true,	110✔
112	UserSettings.ArchiveTriggerTimeout);	110✔
113	_archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true,	110✔
114	UserSettings.ArchiveTriggerTimeout);	110✔
115	_archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true,	110✔
116	UserSettings.ArchiveTriggerTimeout);	110✔
117	}
118
119	return sql;	124✔
120	}
121
122	protected virtual void AddValidFrom(DiscoveredTable table, IQuerySyntaxHelper syntaxHelper)
123	{
124	var dateTimeDatatype =	74✔
125	syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));	74✔
126	var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);	74✔
127
128	_table.AddColumn(SpecialFieldNames.ValidFrom, $" {dateTimeDatatype} DEFAULT {nowFunction}", true,	74✔
129	UserSettings.ArchiveTriggerTimeout);	74✔
130	}	74✔
131
132
133	private string WorkOutArchiveTableCreationSQL()
134	{
135	//script original table
136	var createTableSQL = _table.ScriptTableCreation(true, true, true);	124✔
137
138	var toReplaceTableName = $"CREATE TABLE {_table.GetFullyQualifiedName()}";	124✔
139
140	if (!createTableSQL.Contains(toReplaceTableName))	124!
141	throw new Exception($"Expected to find occurrence of {toReplaceTableName} in the SQL {createTableSQL}");	×
142
143	//rename table
144	createTableSQL = createTableSQL.Replace(toReplaceTableName,	124✔
145	$"CREATE TABLE {_archiveTable.GetFullyQualifiedName()}");	124✔
146
147	//drop identity bit
148	createTableSQL = RemoveIdentities().Replace(createTableSQL, "");	124✔
149
150	return createTableSQL;	124✔
151	}
152
153	public abstract TriggerStatus GetTriggerStatus();
154
155	/// <summary>
156	/// Returns true if the trigger exists and the method body of the trigger matches the expected method body. This exists to handle
157	/// the situation where a trigger is created on a table then the schema of the live table or the archive table is altered subsequently.
158	///
159	/// <para>The best way to implement this is to regenerate the trigger and compare it to the current code fetched from the ddl</para>
160	///
161	/// </summary>
162	/// <returns></returns>
163	public virtual bool CheckUpdateTriggerIsEnabledAndHasExpectedBody()
164	{
165	//check server has trigger and it is on
166	var isEnabledSimple = GetTriggerStatus();	192✔
167
168	if (isEnabledSimple == TriggerStatus.Disabled \|\| isEnabledSimple == TriggerStatus.Missing)	192✔
169	return false;	66✔
170
171	CheckColumnDefinitionsMatchArchive();	126✔
172
173	return true;	122✔
174	}
175
176	private void CheckColumnDefinitionsMatchArchive()
177	{
178	var errors = new List<string>();	126✔
179
180	var archiveTableCols = _archiveTable.DiscoverColumns().ToArray();	126✔
181
182	foreach (var col in _columns)	2,412✔
183	{
184	var colInArchive = archiveTableCols.SingleOrDefault(c => c.GetRuntimeName().Equals(col.GetRuntimeName()));	29,228✔
185
186	if (colInArchive == null)	1,080✔
187	errors.Add(	4✔
188	$"Column {col.GetRuntimeName()} appears in Table '{_table}' but not in archive table '{_archiveTable}'");	4✔
189	else if (!AreCompatibleDatatypes(col.DataType, colInArchive.DataType))	1,076!
190	errors.Add(	×
191	$"Column {col.GetRuntimeName()} has data type '{col.DataType}' in '{_table}' but in Archive table '{_archiveTable}' it is defined as '{colInArchive.DataType}'");	×
192	}
193
194	if (errors.Any())	126✔
195	throw new IrreconcilableColumnDifferencesInArchiveException(	4✔
196	$"The following column mismatch errors were seen:{Environment.NewLine}{string.Join(Environment.NewLine, errors)}");	4✔
197	}	122✔
198
199	private static bool AreCompatibleDatatypes(DiscoveredDataType mainDataType, DiscoveredDataType archiveDataType)
200	{
201	var t1 = mainDataType.SQLType;	1,076✔
202	var t2 = archiveDataType.SQLType;	1,076✔
203
204	if (t1.Equals(t2, StringComparison.CurrentCultureIgnoreCase))	1,076!
205	return true;	1,076✔
206
NEW 207	return t1.Contains("identity", StringComparison.OrdinalIgnoreCase) &&	×
NEW 208	t1.Replace("identity", "", StringComparison.OrdinalIgnoreCase).Trim()	×
NEW 209	.Equals(t2.Trim(), StringComparison.OrdinalIgnoreCase);	×
210	}
211
212	[GeneratedRegex(@"IDENTITY\(\d+,\d+\)")]
213	private static partial Regex RemoveIdentities();
214	}

HicServices / RDMP / 13318089130

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