23483697323

Committed 24 Mar 2026 10:01AM UTC coverage: 57.117% (-0.02%) from 57.133%

Build # 23483697323

Build Type

Pull #2327

github

Committed by

JFriel

Commit Message

add catch

Pull Request Pull Request #2327: Fix Cohort UI Issue

Coverage Stats

11545 of 21741 branches covered (53.1%)

Branch coverage included in aggregate %.

0 of 4 new or added lines in 1 file covered. (0.0%)

41 existing lines in 2 files now uncovered.

32666 of 55663 relevant lines covered (58.69%)

18072.7 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)	278✔
41	{
42	_server = table.Database.Server;	278✔
43	_table = table;	278✔
44	_archiveTable = _table.Database.ExpectTable($"{table.GetRuntimeName()}_Archive", table.Schema);	278✔
45	_columns = table.DiscoverColumns();	278✔
46	_primaryKeys = _columns.Where(c => c.IsPrimaryKey).ToArray();	2,898✔
47
48	_createDataLoadRunIdAlso = createDataLoadRunIDAlso;	278✔
49	}	278✔
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())	128✔
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();	120✔
60
61	//check _Archive does not already exist
62	foreach (var forbiddenColumnName in new[] { "hic_validTo", "hic_userID", "hic_status" })	960✔
63	if (_columns.Any(c =>	360!
64	c.GetRuntimeName().Equals(forbiddenColumnName, StringComparison.CurrentCultureIgnoreCase)))	3,414✔
UNCOV 65	throw new TriggerException(	×
UNCOV 66	$"Table {_table} already contains a column called {forbiddenColumnName} this column is reserved for Archiving");	×
67
68	var b_mustCreate_validFrom = !_columns.Any(c =>	120✔
69	c.GetRuntimeName().Equals(SpecialFieldNames.ValidFrom, StringComparison.CurrentCultureIgnoreCase));	1,126✔
70	var b_mustCreate_dataloadRunId =	120✔
71	!_columns.Any(c =>	120✔
72	c.GetRuntimeName()	988✔
73	.Equals(SpecialFieldNames.DataLoadRunID, StringComparison.CurrentCultureIgnoreCase)) &&	988✔
74	_createDataLoadRunIdAlso;	120✔
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)	120!
UNCOV 78	throw new TriggerException(	×
UNCOV 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)	120✔
83	_table.AddColumn(SpecialFieldNames.DataLoadRunID, new DatabaseTypeRequest(typeof(int)), true,	84✔
84	UserSettings.ArchiveTriggerTimeout);	84✔
85
86	var syntaxHelper = _server.GetQuerySyntaxHelper();	120✔
87
88
89	//must add validFrom outside of transaction if we want SMO to pick it up
90	if (b_mustCreate_validFrom)	120✔
91	AddValidFrom(_table, syntaxHelper);	102✔
92
93	//if we created columns we need to update _column
94	if (b_mustCreate_dataloadRunId \|\| b_mustCreate_validFrom)	120✔
95	_columns = _table.DiscoverColumns();	102✔
96
97	var sql = WorkOutArchiveTableCreationSQL();	120✔
98
99	if (!skipCreatingArchive)	120✔
100	{
101	using var con = _server.GetConnection();	106✔
102	con.Open();	106✔
103
104	using (var cmdCreateArchive = _server.GetCommand(sql, con))	106✔
105	{
106	cmdCreateArchive.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	106✔
107	cmdCreateArchive.ExecuteNonQuery();	106✔
108	}	106✔
109
110
111	_archiveTable.AddColumn("hic_validTo", new DatabaseTypeRequest(typeof(DateTime)), true,	106✔
112	UserSettings.ArchiveTriggerTimeout);	106✔
113	_archiveTable.AddColumn("hic_userID", new DatabaseTypeRequest(typeof(string), 128), true,	106✔
114	UserSettings.ArchiveTriggerTimeout);	106✔
115	_archiveTable.AddColumn("hic_status", new DatabaseTypeRequest(typeof(string), 1), true,	106✔
116	UserSettings.ArchiveTriggerTimeout);	106✔
117	}
118
119	return sql;	120✔
120	}
121
122	protected virtual void AddValidFrom(DiscoveredTable table, IQuerySyntaxHelper syntaxHelper)
123	{
124	var dateTimeDatatype =	70✔
125	syntaxHelper.TypeTranslater.GetSQLDBTypeForCSharpType(new DatabaseTypeRequest(typeof(DateTime)));	70✔
126	var nowFunction = syntaxHelper.GetScalarFunctionSql(MandatoryScalarFunctions.GetTodaysDate);	70✔
127
128	_table.AddColumn(SpecialFieldNames.ValidFrom, $" {dateTimeDatatype} DEFAULT {nowFunction}", true,	70✔
129	UserSettings.ArchiveTriggerTimeout);	70✔
130	}	70✔
131
132
133	private string WorkOutArchiveTableCreationSQL()
134	{
135	//script original table
136	var createTableSQL = _table.ScriptTableCreation(true, true, true);	120✔
137
138	var toReplaceTableName = $"CREATE TABLE {_table.GetFullyQualifiedName()}";	120✔
139
140	if (!createTableSQL.Contains(toReplaceTableName))	120!
UNCOV 141	throw new Exception($"Expected to find occurrence of {toReplaceTableName} in the SQL {createTableSQL}");	×
142
143	//rename table
144	createTableSQL = createTableSQL.Replace(toReplaceTableName,	120✔
145	$"CREATE TABLE {_archiveTable.GetFullyQualifiedName()}");	120✔
146
147	var toRemoveIdentities = "IDENTITY\\(\\d+,\\d+\\)";	120✔
148
149	//drop identity bit
150	createTableSQL = Regex.Replace(createTableSQL, toRemoveIdentities, "");	120✔
151
152	return createTableSQL;	120✔
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();	192✔
169
170	if (isEnabledSimple == TriggerStatus.Disabled \|\| isEnabledSimple == TriggerStatus.Missing)	192✔
171	return false;	66✔
172
173	CheckColumnDefinitionsMatchArchive();	126✔
174
175	return true;	122✔
176	}
177
178	private void CheckColumnDefinitionsMatchArchive()
179	{
180	var errors = new List<string>();	126✔
181
182	var archiveTableCols = _archiveTable.DiscoverColumns().ToArray();	126✔
183
184	foreach (var col in _columns)	2,412✔
185	{
186	var colInArchive = archiveTableCols.SingleOrDefault(c => c.GetRuntimeName().Equals(col.GetRuntimeName()));	29,228✔
187
188	if (colInArchive == null)	1,080✔
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,076!
UNCOV 192	errors.Add(	×
UNCOV 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())	126✔
197	throw new IrreconcilableColumnDifferencesInArchiveException(	4✔
198	$"The following column mismatch errors were seen:{Environment.NewLine}{string.Join(Environment.NewLine, errors)}");	4✔
199	}	122✔
200
201	private static bool AreCompatibleDatatypes(DiscoveredDataType mainDataType, DiscoveredDataType archiveDataType)
202	{
203	var t1 = mainDataType.SQLType;	1,076✔
204	var t2 = archiveDataType.SQLType;	1,076✔
205
206	if (t1.Equals(t2, StringComparison.CurrentCultureIgnoreCase))	1,076!
207	return true;	1,076✔
208
UNCOV 209	return t1.ToLower().Contains("identity") &&	×
UNCOV 210	t1.ToLower().Replace("identity", "").Trim().Equals(t2.ToLower().Trim());	×
211	}
212	}

HicServices / RDMP / 23483697323

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