6237307473

Committed 19 Sep 2023 04:02PM UTC coverage: 57.015% (-0.4%) from 57.44%

Build # 6237307473

Build Type

push

github

Committed by

web-flow

Commit Message

Feature/rc4 (#1570)

* Syntax tidying
* Dependency updates
* Event handling singletons (ThrowImmediately and co)

---------

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: James A Sutherland <>
Co-authored-by: James Friel <jfriel001@dundee.ac.uk>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>

Run Details

10734 of 20259 branches covered (0.0%)

Branch coverage included in aggregate %.

5922 of 5922 new or added lines in 565 files covered. (100.0%)

30687 of 52390 relevant lines covered (58.57%)

7361.8 hits per line

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

87.4

/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 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()}");

        var toRemoveIdentities = "IDENTITY\\(\\d+,\\d+\\)";

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

        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.ToLower().Contains("identity") &&
               t1.ToLower().Replace("identity", "").Trim().Equals(t2.ToLower().Trim());
    }
}

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 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)	248✔
41	{
42	_server = table.Database.Server;	248✔
43	_table = table;	248✔
44	_archiveTable = _table.Database.ExpectTable($"{table.GetRuntimeName()}_Archive", table.Schema);	248✔
45	_columns = table.DiscoverColumns();	248✔
46	_primaryKeys = _columns.Where(c => c.IsPrimaryKey).ToArray();	2,184✔
47
48	_createDataLoadRunIdAlso = createDataLoadRunIDAlso;	248✔
49	}	248✔
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())	114✔
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();	106✔
60
61	//check _Archive does not already exist
62	foreach (var forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" })	848✔
63	if (_columns.Any(c =>	318!
64	c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase)))	2,628✔
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 =>	106✔
69	c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase));	864✔
70	var b_mustCreate_dataloadRunId =	106✔
71	!_columns.Any(c =>	106✔
72	c.GetRuntimeName()	740✔
73	.Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) &&	740✔
74	_createDataLoadRunIdAlso;	106✔
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)	106!
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)	106✔
83	_table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true,	76✔
84	UserSettings.ArchiveTriggerTimeout);	76✔
85
86	var syntaxHelper = _server.GetQuerySyntaxHelper();	106✔
87
88
89	//must add validFrom outside of transaction if we want SMO to pick it up
90	if (b_mustCreate_validFrom)	106✔
91	AddValidFrom(_table, syntaxHelper);	88✔
92
93	//if we created columns we need to update _column
94	if (b_mustCreate_dataloadRunId \|\| b_mustCreate_validFrom)	106✔
95	_columns = _table.DiscoverColumns();	88✔
96
97	var sql = WorkOutArchiveTableCreationSQL();	106✔
98
99	if (!skipCreatingArchive)	106✔
100	{
101	using var con = _server.GetConnection();	92✔
102	con.Open();	92✔
103
104	using (var cmdCreateArchive = _server.GetCommand(sql, con))	92✔
105	{
106	cmdCreateArchive.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	92✔
107	cmdCreateArchive.ExecuteNonQuery();	92✔
108	}	92✔
109
110
111	_archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true,	92✔
112	UserSettings.ArchiveTriggerTimeout);	92✔
113	_archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true,	92✔
114	UserSettings.ArchiveTriggerTimeout);	92✔
115	_archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true,	92✔
116	UserSettings.ArchiveTriggerTimeout);	92✔
117	}
118
119	return sql;	106✔
120	}
121
122	protected virtual void AddValidFrom(DiscoveredTable table, IQuerySyntaxHelper syntaxHelper)
123	{
124	var dateTimeDatatype =	56✔
125	syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));	56✔
126	var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);	56✔
127
128	_table.AddColumn(SpecialFieldNames.ValidFrom, $" {dateTimeDatatype} DEFAULT {nowFunction}", true,	56✔
129	UserSettings.ArchiveTriggerTimeout);	56✔
130	}	56✔
131
132
133	private string WorkOutArchiveTableCreationSQL()
134	{
135	//script original table
136	var createTableSQL = _table.ScriptTableCreation(true, true, true);	106✔
137
138	var toReplaceTableName = $"CREATE TABLE {_table.GetFullyQualifiedName()}";	106✔
139
140	if (!createTableSQL.Contains(toReplaceTableName))	106!
141	throw new Exception($"Expected to find occurrence of {toReplaceTableName} in the SQL {createTableSQL}");	×
142
143	//rename table
144	createTableSQL = createTableSQL.Replace(toReplaceTableName,	106✔
145	$"CREATE TABLE {_archiveTable.GetFullyQualifiedName()}");	106✔
146
147	var toRemoveIdentities = "IDENTITY\\(\\d+,\\d+\\)";	106✔
148
149	//drop identity bit
150	createTableSQL = Regex.Replace(createTableSQL, toRemoveIdentities, "");	106✔
151
152	return createTableSQL;	106✔
153	}
154
155	public abstract TriggerStatus GetTriggerStatus();
156
157	/// <summary>
158	/// Returns true if the trigger exists and the method body of the trigger matches the expected method body. This exists to handle
159	/// the situation where a trigger is created on a table then the schema of the live table or the archive table is altered subsequently.
160	///
161	/// <para>The best way to implement this is to regenerate the trigger and compare it to the current code fetched from the ddl</para>
162	///
163	/// </summary>
164	/// <returns></returns>
165	public virtual bool CheckUpdateTriggerIsEnabledAndHasExpectedBody()
166	{
167	//check server has trigger and it is on
168	var isEnabledSimple = GetTriggerStatus();	194✔
169
170	if (isEnabledSimple == TriggerStatus.Disabled \|\| isEnabledSimple == TriggerStatus.Missing)	194✔
171	return false;	62✔
172
173	CheckColumnDefinitionsMatchArchive();	132✔
174
175	return true;	128✔
176	}
177
178	private void CheckColumnDefinitionsMatchArchive()
179	{
180	var errors = new List<string>();	132✔
181
182	var archiveTableCols = _archiveTable.DiscoverColumns().ToArray();	132✔
183
184	foreach (var col in _columns)	2,616✔
185	{
186	var colInArchive = archiveTableCols.SingleOrDefault(c => c.GetRuntimeName().Equals(col.GetRuntimeName()));	32,876✔
187
188	if (colInArchive == null)	1,176✔
189	errors.Add(	4✔
190	$"Column {col.GetRuntimeName()} appears in Table '{_table}' but not in archive table '{_archiveTable}'");	4✔
191	else if (!AreCompatibleDatatypes(col.DataType, colInArchive.DataType))	1,172!
192	errors.Add(	×
193	$"Column {col.GetRuntimeName()} has data type '{col.DataType}' in '{_table}' but in Archive table '{_archiveTable}' it is defined as '{colInArchive.DataType}'");	×
194	}
195
196	if (errors.Any())	132✔
197	throw new IrreconcilableColumnDifferencesInArchiveException(	4✔
198	$"The following column mismatch errors were seen:{Environment.NewLine}{string.Join(Environment.NewLine, errors)}");	4✔
199	}	128✔
200
201	private static bool AreCompatibleDatatypes(DiscoveredDataType mainDataType, DiscoveredDataType archiveDataType)
202	{
203	var t1 = mainDataType.SQLType;	1,172✔
204	var t2 = archiveDataType.SQLType;	1,172✔
205
206	if (t1.Equals(t2, StringComparison.CurrentCultureIgnoreCase))	1,172!
207	return true;	1,172✔
208
209	return t1.ToLower().Contains("identity") &&	×
210	t1.ToLower().Replace("identity", "").Trim().Equals(t2.ToLower().Trim());	×
211	}
212	}

HicServices / RDMP / 6237307473

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