28572360334

Committed 02 Jul 2026 07:13AM UTC coverage: 56.995% (+0.02%) from 56.975%

Build # 28572360334

Build Type

Pull #2343

github

Committed by

JFriel

Commit Message

update merge component

Pull Request Pull Request #2343: Task/rdmp 376 extraction structural changes

Coverage Stats

11608 of 21899 branches covered (53.01%)

Branch coverage included in aggregate %.

54 of 96 new or added lines in 4 files covered. (56.25%)

9 existing lines in 2 files now uncovered.

32771 of 55966 relevant lines covered (58.56%)

9432.03 hits per line

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

89.44

/Rdmp.Core/DataLoad/Triggers/Implementations/MicrosoftSQLTriggerImplementer.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.Data;
using System.Data.Common;
using System.Linq;
using System.Text.RegularExpressions;
using FAnsi.Discovery;
using FAnsi.Implementations.MicrosoftSQL;
using Microsoft.Data.SqlClient;
using Rdmp.Core.DataLoad.Triggers.Exceptions;
using Rdmp.Core.ReusableLibraryCode.Checks;
using Rdmp.Core.ReusableLibraryCode.Exceptions;
using Rdmp.Core.ReusableLibraryCode.Settings;

namespace Rdmp.Core.DataLoad.Triggers.Implementations;

/// <summary>
/// Creates an _Archive table to match a live table and a Database Trigger On Update which moves old versions of records to the _Archive table when the main table
/// is UPDATEd.  An _Archive table is an exact match of columns as the live table (which must have primary keys) but also includes several audit fields (date it
/// was archived etc).  The _Archive table can be used to view the changes that occurred during data loading (See DiffDatabaseDataFetcher) and/or generate a
/// 'way back machine' view of the data at a given date in the past (See CreateViewOldVersionsTableValuedFunction method).
/// 
/// <para>This class is super Microsoft Sql Server specific.  It is not suitable to create backup triggers on tables in which you expect high volitility (lots of frequent
/// updates all the time).</para>
/// 
/// <para>Also contains methods for confirming that a trigger exists on a given table, that the primary keys still match when it was created and the _Archive table hasn't
/// got a different schema to the live table (e.g. if you made a change to the live table this would pick up that the _Archive wasn't updated).</para>
/// </summary>
public class MicrosoftSQLTriggerImplementer : TriggerImplementer
{
    private string _schema;
    private string _triggerName;

    /// <inheritdoc cref="TriggerImplementer(DiscoveredTable,bool,bool)"/>
    public MicrosoftSQLTriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true, bool dontAddDataLoadrunID = false) : base(table,
        createDataLoadRunIDAlso, dontAddDataLoadrunID)
    {
        _schema = string.IsNullOrWhiteSpace(_table.Schema) ? "dbo" : _table.Schema;
        _triggerName = $"{_schema}.{GetTriggerName()}";
    }

    public override void DropTrigger(out string problemsDroppingTrigger, out string thingsThatWorkedDroppingTrigger)
    {
        using var con = _server.GetConnection();
        con.Open();

        problemsDroppingTrigger = "";
        thingsThatWorkedDroppingTrigger = "";

        using (var cmdDropTrigger = _server.GetCommand($"DROP TRIGGER {_triggerName}", con))
        {
            try
            {
                cmdDropTrigger.CommandTimeout = UserSettings.ArchiveTriggerTimeout;
                thingsThatWorkedDroppingTrigger += $"Dropped Trigger successfully{Environment.NewLine}";
                cmdDropTrigger.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                //this is not a problem really since it is likely that DLE chose to recreate the trigger because it was FUBARed or missing, this is just belt and braces try and drop anything that is lingering, whether or not it is there
                problemsDroppingTrigger += $"Failed to drop Trigger:{exception.Message}{Environment.NewLine}";
            }
        }

        using (var cmdDropArchiveIndex = _server.GetCommand(
                   $"DROP INDEX PKsIndex ON {_archiveTable.GetRuntimeName()}", con))
        {
            try
            {
                cmdDropArchiveIndex.CommandTimeout = UserSettings.ArchiveTriggerTimeout;
                cmdDropArchiveIndex.ExecuteNonQuery();

                thingsThatWorkedDroppingTrigger +=
                    $"Dropped index PKsIndex on Archive table successfully{Environment.NewLine}";
            }
            catch (Exception exception)
            {
                problemsDroppingTrigger += $"Failed to drop Archive Index:{exception.Message}{Environment.NewLine}";
            }
        }

        using var cmdDropArchiveLegacyView = _server.GetCommand($"DROP FUNCTION {_table.GetRuntimeName()}_Legacy", con);
        try
        {
            cmdDropArchiveLegacyView.CommandTimeout = UserSettings.ArchiveTriggerTimeout;
            cmdDropArchiveLegacyView.ExecuteNonQuery();
            thingsThatWorkedDroppingTrigger += $"Dropped Legacy Table View successfully{Environment.NewLine}";
        }
        catch (Exception exception)
        {
            problemsDroppingTrigger +=
                $"Failed to drop Legacy Table View:{exception.Message}{Environment.NewLine}";
        }
    }

    public override string CreateTrigger(ICheckNotifier notifier)
    {
        var createArchiveTableSQL = base.CreateTrigger(notifier);

        using var con = _server.GetConnection();
        con.Open();

        var trigger = GetCreateTriggerSQL();

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


        //Add key so that we can more easily do comparisons on primary key between main table and archive
        var idxCompositeKeyBody = "";

        foreach (var key in _primaryKeys)
            idxCompositeKeyBody += $"[{key.GetRuntimeName()}] ASC,";

        //remove trailing comma
        idxCompositeKeyBody = idxCompositeKeyBody.TrimEnd(',');

        var createIndexSQL =
            $@"CREATE NONCLUSTERED INDEX [PKsIndex] ON {_archiveTable.GetFullyQualifiedName()} ({idxCompositeKeyBody})";
        using (var cmdCreateIndex = _server.GetCommand(createIndexSQL, con))
        {
            try
            {
                cmdCreateIndex.CommandTimeout = UserSettings.ArchiveTriggerTimeout;
                cmdCreateIndex.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                notifier.OnCheckPerformed(new CheckEventArgs(
                    "Could not create index on archive table because of timeout, possibly your _Archive table has a lot of data in it",
                    CheckResult.Fail, e));

                return null;
            }
        }

        CreateViewOldVersionsTableValuedFunction(createArchiveTableSQL, con);

        return createArchiveTableSQL;
    }

    public override string GetCreateTriggerSQL()
    {
        if (!_primaryKeys.Any())
            throw new TriggerException("There must be at least 1 primary key");

        //this is the SQL to join on the main table to the deleted to record the hic_validFrom
        var updateValidToWhere =
            $" UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM deleted where ";

        //its a combo field so join on both when filling in hic_validFrom
        foreach (var key in _primaryKeys)
            updateValidToWhere += $"[{_table}].[{key}] = deleted.[{key}] AND ";

        //trim off last AND
        updateValidToWhere = updateValidToWhere[..^"AND ".Length];

        var InsertedToDeletedJoin = "JOIN inserted i ON ";
        InsertedToDeletedJoin += GetTableToTableEqualsSqlWithPrimaryKeys("i", "d");

        var equalsSqlTableToInserted = GetTableToTableEqualsSqlWithPrimaryKeys(_table.GetRuntimeName(), "inserted");
        var equalsSqlTableToDeleted = GetTableToTableEqualsSqlWithPrimaryKeys(_table.GetRuntimeName(), "deleted");

        var columnNames = _columns.Select(c => c.GetRuntimeName()).ToList();

        if (!columnNames.Contains(SpecialFieldNames.DataLoadRunID, StringComparer.CurrentCultureIgnoreCase) && !_dontAddDataLoadRunId)
            columnNames.Add(SpecialFieldNames.DataLoadRunID);

        if (!columnNames.Contains(SpecialFieldNames.ValidFrom, StringComparer.CurrentCultureIgnoreCase))
            columnNames.Add(SpecialFieldNames.ValidFrom);

        var colList = string.Join(",", columnNames.Select(c => $"[{c}]"));
        var dDotColList = string.Join(",", columnNames.Select(c => $"d.[{c}]"));

        return
            $@"
CREATE TRIGGER {_triggerName} ON {_table.GetFullyQualifiedName()}
AFTER UPDATE, DELETE
AS BEGIN

SET NOCOUNT ON

declare @isPrimaryKeyChange bit = 0

--it will be a primary key change if deleted and inserted do not agree on primary key values
IF exists ( select 1 FROM deleted d RIGHT {InsertedToDeletedJoin} WHERE d.[{_primaryKeys.First().GetRuntimeName()}] is null)
begin
        UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM inserted where {equalsSqlTableToInserted}
        set @isPrimaryKeyChange = 1
end
else
begin
        UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM deleted where {equalsSqlTableToDeleted}
        set @isPrimaryKeyChange = 0
end

{updateValidToWhere}

INSERT INTO [{_archiveTable.GetRuntimeName()}] ({colList},hic_validTo,hic_userID,hic_status) SELECT {dDotColList}, GETDATE(), SYSTEM_USER, CASE WHEN @isPrimaryKeyChange = 1 then 'K' WHEN i.[{_primaryKeys.First().GetRuntimeName()}] IS NULL THEN 'D' WHEN d.[{_primaryKeys.First().GetRuntimeName()}] IS NULL THEN 'I' ELSE 'U' END
FROM deleted d 
LEFT {InsertedToDeletedJoin}

SET NOCOUNT OFF

RETURN
END  
";
    }

    private string GetTableToTableEqualsSqlWithPrimaryKeys(string table1, string table2)
    {
        var toReturn = "";

        foreach (var key in _primaryKeys)
            toReturn += $" [{table1}].[{key.GetRuntimeName()}] = [{table2}].[{key.GetRuntimeName()}] AND ";

        //trim off last AND
        toReturn = toReturn[..^"AND ".Length];

        return toReturn;
    }

    private void CreateViewOldVersionsTableValuedFunction(string sqlUsedToCreateArchiveTableSQL, DbConnection con)
    {
        var columnsInArchive = "";

        var syntaxHelper = MicrosoftQuerySyntaxHelper.Instance;

        var matchStartColumnExtraction = Regex.Match(sqlUsedToCreateArchiveTableSQL, "CREATE TABLE .*\\(");

        if (!matchStartColumnExtraction.Success)
            throw new Exception("Could not find regex match at start of Archive table CREATE SQL");

        var startExtractingColumnsAt = matchStartColumnExtraction.Index + matchStartColumnExtraction.Length;
        //trim off excess crud at start and we should have just the columns bit of the create (plus crud at the end)
        columnsInArchive = sqlUsedToCreateArchiveTableSQL[startExtractingColumnsAt..];

        //trim off excess crud at the end
        columnsInArchive = columnsInArchive.Trim(new[] { ')', '\r', '\n' });

        var sqlToRun = string.Format("CREATE FUNCTION [" + _schema + "].[{0}_Legacy]",
            QuerySyntaxHelper.MakeHeaderNameSensible(_table.GetRuntimeName()));
        sqlToRun += Environment.NewLine;
        sqlToRun += $"({Environment.NewLine}";
        sqlToRun += $"\t@index DATETIME{Environment.NewLine}";
        sqlToRun += $"){Environment.NewLine}";
        sqlToRun += $"RETURNS @returntable TABLE{Environment.NewLine}";
        sqlToRun += $"({Environment.NewLine}";
        sqlToRun += $"/*the return table will follow the structure of the Archive table*/{Environment.NewLine}";
        sqlToRun += columnsInArchive;

        //these were added during transaction so we have to specify them again here because transaction will not have been committed yet
        sqlToRun = sqlToRun.Trim();
        if (!sqlToRun.Contains("hic_validTo"))
        {
            sqlToRun += $",{Environment.NewLine}";
            sqlToRun += $"hic_validTo datetime";
        }
        if (!sqlToRun.Contains("hic_userID"))
        {
            sqlToRun += $",{Environment.NewLine}";

            sqlToRun += "hic_userID varchar(128)";
        }
        if (!sqlToRun.Contains("hic_status"))
        {
            sqlToRun += $",{Environment.NewLine}";
            sqlToRun += "hic_status char(1)";
        }


        sqlToRun += $"){Environment.NewLine}";
        sqlToRun += $"AS{Environment.NewLine}";
        sqlToRun += $"BEGIN{Environment.NewLine}";
        sqlToRun += Environment.NewLine;


        var liveCols = _archiveTable.DiscoverColumns().Select(c => $"[{c.GetRuntimeName()}]")
            .ToList();

        if (!liveCols.Contains($"[{SpecialFieldNames.DataLoadRunID}]")) liveCols.Add($"[{SpecialFieldNames.DataLoadRunID}]");
        if (!liveCols.Contains($"[{SpecialFieldNames.ValidFrom}]")) liveCols.Add($"[{SpecialFieldNames.ValidFrom}]");
        liveCols = liveCols.Where(col => !_dontAddDataLoadRunId || col != $"[{SpecialFieldNames.DataLoadRunID}]").ToList();

        var archiveCols = $"{string.Join(",", liveCols)}";

        var cDotArchiveCols = string.Join(",", liveCols.Select(s => $"c.{s}"));


        sqlToRun += $"\tINSERT @returntable{Environment.NewLine}";
        sqlToRun += string.Format(
            "\tSELECT " + archiveCols + " FROM [{0}] WHERE @index BETWEEN ISNULL(" + SpecialFieldNames.ValidFrom +
            ", '1899/01/01') AND hic_validTo" + Environment.NewLine, _archiveTable);
        sqlToRun += Environment.NewLine;

        //these are columns that exist in the archive, but not the table. So have to be set as NULL as <value>
        var nullCoulmns = _archiveTable.DiscoverColumns().Select(c => $"[{c.GetRuntimeName()}]").Except(_table.DiscoverColumns().Select(c => $"[{c.GetRuntimeName()}]"));
        cDotArchiveCols = string.Join(",", liveCols.Select(s => nullCoulmns.Contains(s) ? $"NULL AS {s}" : $"c.{s}"));

        sqlToRun += $"\tINSERT @returntable{Environment.NewLine}";
        sqlToRun +=
            $"\tSELECT {cDotArchiveCols}";
        sqlToRun += string.Format("\tFROM [{0}] c" + Environment.NewLine, _table.GetRuntimeName());
        sqlToRun += $"\tLEFT OUTER JOIN @returntable a ON {Environment.NewLine}";

        for (var index = 0; index < _primaryKeys.Length; index++)
        {
            sqlToRun += string.Format("\ta.{0}=c.{0} " + Environment.NewLine,
                syntaxHelper.EnsureWrapped(_primaryKeys[index].GetRuntimeName())); //add the primary key joins

            if (index + 1 < _primaryKeys.Length)
                sqlToRun += $"\tAND{Environment.NewLine}"; //add an AND because there are more coming
        }

        sqlToRun += string.Format("\tWHERE a.[{0}] IS NULL -- where archive record doesn't exist" + Environment.NewLine,
            _primaryKeys.First().GetRuntimeName());
        sqlToRun += $"\tAND @index > ISNULL(c.{SpecialFieldNames.ValidFrom}, '1899/01/01'){Environment.NewLine}";

        sqlToRun += Environment.NewLine;
        sqlToRun += $"RETURN{Environment.NewLine}";
        sqlToRun += $"END{Environment.NewLine}";

        using var cmd = _server.GetCommand(sqlToRun, con);
        cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;
        cmd.ExecuteNonQuery();
    }

    public override TriggerStatus GetTriggerStatus()
    {
        var updateTriggerName = GetTriggerName();

        var queryTriggerIsItDisabledOrMissing =
            $"USE [{_table.Database.GetRuntimeName()}]; \r\nif exists (select 1 from sys.triggers WHERE name=@triggerName) SELECT is_disabled  FROM sys.triggers WHERE name=@triggerName else select -1 is_disabled";

        try
        {
            using var conn = _server.GetConnection();
            conn.Open();
            using var cmd = _server.GetCommand(queryTriggerIsItDisabledOrMissing, conn);
            cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;
            cmd.Parameters.Add(new SqlParameter("@triggerName", SqlDbType.VarChar));
            cmd.Parameters["@triggerName"].Value = updateTriggerName;

            var result = Convert.ToInt32(cmd.ExecuteScalar());

            return result switch
            {
                0 => TriggerStatus.Enabled,
                1 => TriggerStatus.Disabled,
                -1 => TriggerStatus.Missing,
                _ => throw new NotSupportedException($"Query returned unexpected value:{result}")
            };
        }
        catch (Exception e)
        {
            throw new Exception($"Failed to check if trigger {updateTriggerName} is enabled: {e}");
        }
    }

    private string GetTriggerName() => $"{QuerySyntaxHelper.MakeHeaderNameSensible(_table.GetRuntimeName())}_OnUpdate";

    public override bool CheckUpdateTriggerIsEnabledAndHasExpectedBody()
    {
        var baseResult = base.CheckUpdateTriggerIsEnabledAndHasExpectedBody();

        if (!baseResult)
            return false;

        //now check the definition of it! - make sure it relates to primary keys etc
        var updateTriggerName = GetTriggerName();
        var query =
            $"USE [{_table.Database.GetRuntimeName()}];SELECT OBJECT_DEFINITION (object_id) FROM sys.triggers WHERE name='{updateTriggerName}' and is_disabled=0";

        try
        {
            using var con = _server.GetConnection();
            string result;

            con.Open();
            using (var cmd = _server.GetCommand(query, con))
            {
                cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;
                result = cmd.ExecuteScalar() as string;
            }


            if (string.IsNullOrWhiteSpace(result))
                throw new TriggerMissingException(
                    $"Trigger {updateTriggerName} does not have an OBJECT_DEFINITION or is missing or is disabled");

            var expectedSQL = GetCreateTriggerSQL();

            expectedSQL = expectedSQL.Trim();
            result = result.Trim();

            if (!expectedSQL.Equals(result))
                throw new ExpectedIdenticalStringsException($"Trigger {updateTriggerName} is corrupt",
                    expectedSQL, result);
        }
        catch (ExpectedIdenticalStringsException)
        {
            throw;
        }
        catch (IrreconcilableColumnDifferencesInArchiveException)
        {
            throw;
        }
        catch (Exception e)
        {
            throw new Exception(
                $"Failed to check if trigger {updateTriggerName} is enabled.  See InnerException for details", e);
        }

        return true;
    }

}

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.Data;
9	using System.Data.Common;
10	using System.Linq;
11	using System.Text.RegularExpressions;
12	using FAnsi.Discovery;
13	using FAnsi.Implementations.MicrosoftSQL;
14	using Microsoft.Data.SqlClient;
15	using Rdmp.Core.DataLoad.Triggers.Exceptions;
16	using Rdmp.Core.ReusableLibraryCode.Checks;
17	using Rdmp.Core.ReusableLibraryCode.Exceptions;
18	using Rdmp.Core.ReusableLibraryCode.Settings;
19
20	namespace Rdmp.Core.DataLoad.Triggers.Implementations;
21
22	/// <summary>
23	/// Creates an _Archive table to match a live table and a Database Trigger On Update which moves old versions of records to the _Archive table when the main table
24	/// is UPDATEd. An _Archive table is an exact match of columns as the live table (which must have primary keys) but also includes several audit fields (date it
25	/// was archived etc). The _Archive table can be used to view the changes that occurred during data loading (See DiffDatabaseDataFetcher) and/or generate a
26	/// 'way back machine' view of the data at a given date in the past (See CreateViewOldVersionsTableValuedFunction method).
27	///
28	/// <para>This class is super Microsoft Sql Server specific. It is not suitable to create backup triggers on tables in which you expect high volitility (lots of frequent
29	/// updates all the time).</para>
30	///
31	/// <para>Also contains methods for confirming that a trigger exists on a given table, that the primary keys still match when it was created and the _Archive table hasn't
32	/// got a different schema to the live table (e.g. if you made a change to the live table this would pick up that the _Archive wasn't updated).</para>
33	/// </summary>
34	public class MicrosoftSQLTriggerImplementer : TriggerImplementer
35	{
36	private string _schema;
37	private string _triggerName;
38
39	/// <inheritdoc cref="TriggerImplementer(DiscoveredTable,bool,bool)"/>
40	public MicrosoftSQLTriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true, bool dontAddDataLoadrunID = false) : base(table,	139✔
41	createDataLoadRunIDAlso, dontAddDataLoadrunID)	139✔
42	{
43	_schema = string.IsNullOrWhiteSpace(_table.Schema) ? "dbo" : _table.Schema;	139✔
44	_triggerName = $"{_schema}.{GetTriggerName()}";	139✔
45	}	139✔
46
47	public override void DropTrigger(out string problemsDroppingTrigger, out string thingsThatWorkedDroppingTrigger)
48	{
49	using var con = _server.GetConnection();	38✔
50	con.Open();	38✔
51
52	problemsDroppingTrigger = "";	38✔
53	thingsThatWorkedDroppingTrigger = "";	38✔
54
55	using (var cmdDropTrigger = _server.GetCommand($"DROP TRIGGER {_triggerName}", con))	38✔
56	{
57	try
58	{
59	cmdDropTrigger.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	38✔
60	thingsThatWorkedDroppingTrigger += $"Dropped Trigger successfully{Environment.NewLine}";	38✔
61	cmdDropTrigger.ExecuteNonQuery();	38✔
62	}	15✔
63	catch (Exception exception)	23✔
64	{
65	//this is not a problem really since it is likely that DLE chose to recreate the trigger because it was FUBARed or missing, this is just belt and braces try and drop anything that is lingering, whether or not it is there
66	problemsDroppingTrigger += $"Failed to drop Trigger:{exception.Message}{Environment.NewLine}";	23✔
67	}	23✔
68	}
69
70	using (var cmdDropArchiveIndex = _server.GetCommand(	38✔
71	$"DROP INDEX PKsIndex ON {_archiveTable.GetRuntimeName()}", con))	38✔
72	{
73	try
74	{
75	cmdDropArchiveIndex.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	38✔
76	cmdDropArchiveIndex.ExecuteNonQuery();	38✔
77
78	thingsThatWorkedDroppingTrigger +=	16✔
79	$"Dropped index PKsIndex on Archive table successfully{Environment.NewLine}";	16✔
80	}	16✔
81	catch (Exception exception)	22✔
82	{
83	problemsDroppingTrigger += $"Failed to drop Archive Index:{exception.Message}{Environment.NewLine}";	22✔
84	}	22✔
85	}
86
87	using var cmdDropArchiveLegacyView = _server.GetCommand($"DROP FUNCTION {_table.GetRuntimeName()}_Legacy", con);	38✔
88	try
89	{
90	cmdDropArchiveLegacyView.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	38✔
91	cmdDropArchiveLegacyView.ExecuteNonQuery();	38✔
92	thingsThatWorkedDroppingTrigger += $"Dropped Legacy Table View successfully{Environment.NewLine}";	18✔
93	}	18✔
94	catch (Exception exception)	20✔
95	{
96	problemsDroppingTrigger +=	20✔
97	$"Failed to drop Legacy Table View:{exception.Message}{Environment.NewLine}";	20✔
98	}	20✔
99	}	38✔
100
101	public override string CreateTrigger(ICheckNotifier notifier)
102	{
103	var createArchiveTableSQL = base.CreateTrigger(notifier);	61✔
104
105	using var con = _server.GetConnection();	59✔
106	con.Open();	59✔
107
108	var trigger = GetCreateTriggerSQL();	59✔
109
110	using (var cmdAddTrigger = _server.GetCommand(trigger, con))	59✔
111	{
112	cmdAddTrigger.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	59✔
113	cmdAddTrigger.ExecuteNonQuery();	59✔
114	}	59✔
115
116
117	//Add key so that we can more easily do comparisons on primary key between main table and archive
118	var idxCompositeKeyBody = "";	59✔
119
120	foreach (var key in _primaryKeys)	294✔
121	idxCompositeKeyBody += $"[{key.GetRuntimeName()}] ASC,";	88✔
122
123	//remove trailing comma
124	idxCompositeKeyBody = idxCompositeKeyBody.TrimEnd(',');	59✔
125
126	var createIndexSQL =	59✔
127	$@"CREATE NONCLUSTERED INDEX [PKsIndex] ON {_archiveTable.GetFullyQualifiedName()} ({idxCompositeKeyBody})";	59✔
128	using (var cmdCreateIndex = _server.GetCommand(createIndexSQL, con))	59✔
129	{
130	try
131	{
132	cmdCreateIndex.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	59✔
133	cmdCreateIndex.ExecuteNonQuery();	59✔
134	}	59✔
135	catch (SqlException e)	×
136	{
137	notifier.OnCheckPerformed(new CheckEventArgs(	×
138	"Could not create index on archive table because of timeout, possibly your _Archive table has a lot of data in it",	×
139	CheckResult.Fail, e));	×
140
141	return null;	×
142	}
143	}
144
145	CreateViewOldVersionsTableValuedFunction(createArchiveTableSQL, con);	59✔
146
147	return createArchiveTableSQL;	59✔
148	}	59✔
149
150	public override string GetCreateTriggerSQL()
151	{
152	if (!_primaryKeys.Any())	98!
153	throw new TriggerException("There must be at least 1 primary key");	×
154
155	//this is the SQL to join on the main table to the deleted to record the hic_validFrom
156	var updateValidToWhere =	98✔
157	$" UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM deleted where ";	98✔
158
159	//its a combo field so join on both when filling in hic_validFrom
160	foreach (var key in _primaryKeys)	500✔
161	updateValidToWhere += $"[{_table}].[{key}] = deleted.[{key}] AND ";	152✔
162
163	//trim off last AND
164	updateValidToWhere = updateValidToWhere[..^"AND ".Length];	98✔
165
166	var InsertedToDeletedJoin = "JOIN inserted i ON ";	98✔
167	InsertedToDeletedJoin += GetTableToTableEqualsSqlWithPrimaryKeys("i", "d");	98✔
168
169	var equalsSqlTableToInserted = GetTableToTableEqualsSqlWithPrimaryKeys(_table.GetRuntimeName(), "inserted");	98✔
170	var equalsSqlTableToDeleted = GetTableToTableEqualsSqlWithPrimaryKeys(_table.GetRuntimeName(), "deleted");	98✔
171
172	var columnNames = _columns.Select(c => c.GetRuntimeName()).ToList();	1,739✔
173
174	if (!columnNames.Contains(SpecialFieldNames.DataLoadRunID, StringComparer.CurrentCultureIgnoreCase) && !_dontAddDataLoadRunId)	98!
175	columnNames.Add(SpecialFieldNames.DataLoadRunID);	×
176
177	if (!columnNames.Contains(SpecialFieldNames.ValidFrom, StringComparer.CurrentCultureIgnoreCase))	98!
178	columnNames.Add(SpecialFieldNames.ValidFrom);	×
179
180	var colList = string.Join(",", columnNames.Select(c => $"[{c}]"));	1,739✔
181	var dDotColList = string.Join(",", columnNames.Select(c => $"d.[{c}]"));	1,739✔
182
183	return	98✔
184	$@"	98✔
185	CREATE TRIGGER {_triggerName} ON {_table.GetFullyQualifiedName()}	98✔
186	AFTER UPDATE, DELETE	98✔
187	AS BEGIN	98✔
188		98✔
189	SET NOCOUNT ON	98✔
190		98✔
191	declare @isPrimaryKeyChange bit = 0	98✔
192		98✔
193	--it will be a primary key change if deleted and inserted do not agree on primary key values	98✔
194	IF exists ( select 1 FROM deleted d RIGHT {InsertedToDeletedJoin} WHERE d.[{_primaryKeys.First().GetRuntimeName()}] is null)	98✔
195	begin	98✔
196	UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM inserted where {equalsSqlTableToInserted}	98✔
197	set @isPrimaryKeyChange = 1	98✔
198	end	98✔
199	else	98✔
200	begin	98✔
201	UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM deleted where {equalsSqlTableToDeleted}	98✔
202	set @isPrimaryKeyChange = 0	98✔
203	end	98✔
204		98✔
205	{updateValidToWhere}	98✔
206		98✔
207	INSERT INTO [{_archiveTable.GetRuntimeName()}] ({colList},hic_validTo,hic_userID,hic_status) SELECT {dDotColList}, GETDATE(), SYSTEM_USER, CASE WHEN @isPrimaryKeyChange = 1 then 'K' WHEN i.[{_primaryKeys.First().GetRuntimeName()}] IS NULL THEN 'D' WHEN d.[{_primaryKeys.First().GetRuntimeName()}] IS NULL THEN 'I' ELSE 'U' END	98✔
208	FROM deleted d	98✔
209	LEFT {InsertedToDeletedJoin}	98✔
210		98✔
211	SET NOCOUNT OFF	98✔
212		98✔
213	RETURN	98✔
214	END	98✔
215	";	98✔
216	}
217
218	private string GetTableToTableEqualsSqlWithPrimaryKeys(string table1, string table2)
219	{
220	var toReturn = "";	294✔
221
222	foreach (var key in _primaryKeys)	1,500✔
223	toReturn += $" [{table1}].[{key.GetRuntimeName()}] = [{table2}].[{key.GetRuntimeName()}] AND ";	456✔
224
225	//trim off last AND
226	toReturn = toReturn[..^"AND ".Length];	294✔
227
228	return toReturn;	294✔
229	}
230
231	private void CreateViewOldVersionsTableValuedFunction(string sqlUsedToCreateArchiveTableSQL, DbConnection con)
232	{
233	var columnsInArchive = "";	59✔
234
235	var syntaxHelper = MicrosoftQuerySyntaxHelper.Instance;	59✔
236
237	var matchStartColumnExtraction = Regex.Match(sqlUsedToCreateArchiveTableSQL, "CREATE TABLE .*\\(");	59✔
238
239	if (!matchStartColumnExtraction.Success)	59!
240	throw new Exception("Could not find regex match at start of Archive table CREATE SQL");	×
241
242	var startExtractingColumnsAt = matchStartColumnExtraction.Index + matchStartColumnExtraction.Length;	59✔
243	//trim off excess crud at start and we should have just the columns bit of the create (plus crud at the end)
244	columnsInArchive = sqlUsedToCreateArchiveTableSQL[startExtractingColumnsAt..];	59✔
245
246	//trim off excess crud at the end
247	columnsInArchive = columnsInArchive.Trim(new[] { ')', '\r', '\n' });	59✔
248
249	var sqlToRun = string.Format("CREATE FUNCTION [" + _schema + "].[{0}_Legacy]",	59✔
250	QuerySyntaxHelper.MakeHeaderNameSensible(_table.GetRuntimeName()));	59✔
251	sqlToRun += Environment.NewLine;	59✔
252	sqlToRun += $"({Environment.NewLine}";	59✔
253	sqlToRun += $"\t@index DATETIME{Environment.NewLine}";	59✔
254	sqlToRun += $"){Environment.NewLine}";	59✔
255	sqlToRun += $"RETURNS @returntable TABLE{Environment.NewLine}";	59✔
256	sqlToRun += $"({Environment.NewLine}";	59✔
257	sqlToRun += $"/the return table will follow the structure of the Archive table/{Environment.NewLine}";	59✔
258	sqlToRun += columnsInArchive;	59✔
259
260	//these were added during transaction so we have to specify them again here because transaction will not have been committed yet
261	sqlToRun = sqlToRun.Trim();	59✔
262	if (!sqlToRun.Contains("hic_validTo"))	59✔
263	{
264	sqlToRun += $",{Environment.NewLine}";	44✔
265	sqlToRun += $"hic_validTo datetime";	44✔
266	}
267	if (!sqlToRun.Contains("hic_userID"))	59✔
268	{
269	sqlToRun += $",{Environment.NewLine}";	44✔
270
271	sqlToRun += "hic_userID varchar(128)";	44✔
272	}
273	if (!sqlToRun.Contains("hic_status"))	59✔
274	{
275	sqlToRun += $",{Environment.NewLine}";	44✔
276	sqlToRun += "hic_status char(1)";	44✔
277	}
278
279
280	sqlToRun += $"){Environment.NewLine}";	59✔
281	sqlToRun += $"AS{Environment.NewLine}";	59✔
282	sqlToRun += $"BEGIN{Environment.NewLine}";	59✔
283	sqlToRun += Environment.NewLine;	59✔
284
285
286	var liveCols = _archiveTable.DiscoverColumns().Select(c => $"[{c.GetRuntimeName()}]")	1,460✔
287	.ToList();	59✔
288
289	if (!liveCols.Contains($"[{SpecialFieldNames.DataLoadRunID}]")) liveCols.Add($"[{SpecialFieldNames.DataLoadRunID}]");	59!
290	if (!liveCols.Contains($"[{SpecialFieldNames.ValidFrom}]")) liveCols.Add($"[{SpecialFieldNames.ValidFrom}]");	59!
291	liveCols = liveCols.Where(col => !_dontAddDataLoadRunId \|\| col != $"[{SpecialFieldNames.DataLoadRunID}]").ToList();	1,460!
292
293	var archiveCols = $"{string.Join(",", liveCols)}";	59!
294
295	var cDotArchiveCols = string.Join(",", liveCols.Select(s => $"c.{s}"));	1,460✔
296
297
298	sqlToRun += $"\tINSERT @returntable{Environment.NewLine}";	59✔
299	sqlToRun += string.Format(	59✔
300	"\tSELECT " + archiveCols + " FROM [{0}] WHERE @index BETWEEN ISNULL(" + SpecialFieldNames.ValidFrom +	59✔
301	", '1899/01/01') AND hic_validTo" + Environment.NewLine, _archiveTable);	59✔
302	sqlToRun += Environment.NewLine;	59✔
303
304	//these are columns that exist in the archive, but not the table. So have to be set as NULL as <value>
305	var nullCoulmns = _archiveTable.DiscoverColumns().Select(c => $"[{c.GetRuntimeName()}]").Except(_table.DiscoverColumns().Select(c => $"[{c.GetRuntimeName()}]"));	97,897✔
306	cDotArchiveCols = string.Join(",", liveCols.Select(s => nullCoulmns.Contains(s) ? $"NULL AS {s}" : $"c.{s}"));	1,460✔
307
308	sqlToRun += $"\tINSERT @returntable{Environment.NewLine}";	59✔
309	sqlToRun +=	59✔
310	$"\tSELECT {cDotArchiveCols}";	59✔
311	sqlToRun += string.Format("\tFROM [{0}] c" + Environment.NewLine, _table.GetRuntimeName());	59✔
312	sqlToRun += $"\tLEFT OUTER JOIN @returntable a ON {Environment.NewLine}";	59✔
313
314	for (var index = 0; index < _primaryKeys.Length; index++)	294✔
315	{
316	sqlToRun += string.Format("\ta.{0}=c.{0} " + Environment.NewLine,	88✔
317	syntaxHelper.EnsureWrapped(_primaryKeys[index].GetRuntimeName())); //add the primary key joins	88✔
318
319	if (index + 1 < _primaryKeys.Length)	88✔
320	sqlToRun += $"\tAND{Environment.NewLine}"; //add an AND because there are more coming	29✔
321	}
322
323	sqlToRun += string.Format("\tWHERE a.[{0}] IS NULL -- where archive record doesn't exist" + Environment.NewLine,	59✔
324	_primaryKeys.First().GetRuntimeName());	59✔
325	sqlToRun += $"\tAND @index > ISNULL(c.{SpecialFieldNames.ValidFrom}, '1899/01/01'){Environment.NewLine}";	59✔
326
327	sqlToRun += Environment.NewLine;	59✔
328	sqlToRun += $"RETURN{Environment.NewLine}";	59✔
329	sqlToRun += $"END{Environment.NewLine}";	59✔
330
331	using var cmd = _server.GetCommand(sqlToRun, con);	59✔
332	cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	59✔
333	cmd.ExecuteNonQuery();	59✔
334	}	118✔
335
336	public override TriggerStatus GetTriggerStatus()
337	{
338	var updateTriggerName = GetTriggerName();	131✔
339
340	var queryTriggerIsItDisabledOrMissing =	131✔
341	$"USE [{_table.Database.GetRuntimeName()}]; \r\nif exists (select 1 from sys.triggers WHERE name=@triggerName) SELECT is_disabled FROM sys.triggers WHERE name=@triggerName else select -1 is_disabled";	131✔
342
343	try
344	{
345	using var conn = _server.GetConnection();	131✔
346	conn.Open();	131✔
347	using var cmd = _server.GetCommand(queryTriggerIsItDisabledOrMissing, conn);	131✔
348	cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	131✔
349	cmd.Parameters.Add(new SqlParameter("@triggerName", SqlDbType.VarChar));	131✔
350	cmd.Parameters["@triggerName"].Value = updateTriggerName;	131✔
351
352	var result = Convert.ToInt32(cmd.ExecuteScalar());	131✔
353
354	return result switch	131!
355	{	131✔
356	0 => TriggerStatus.Enabled,	78✔
357	1 => TriggerStatus.Disabled,	1✔
358	-1 => TriggerStatus.Missing,	52✔
UNCOV 359	_ => throw new NotSupportedException($"Query returned unexpected value:{result}")	×
360	};	131✔
361	}
UNCOV 362	catch (Exception e)	×
363	{
UNCOV 364	throw new Exception($"Failed to check if trigger {updateTriggerName} is enabled: {e}");	×
365	}
366	}	131✔
367
368	private string GetTriggerName() => $"{QuerySyntaxHelper.MakeHeaderNameSensible(_table.GetRuntimeName())}_OnUpdate";	309✔
369
370	public override bool CheckUpdateTriggerIsEnabledAndHasExpectedBody()
371	{
372	var baseResult = base.CheckUpdateTriggerIsEnabledAndHasExpectedBody();	64✔
373
374	if (!baseResult)	63✔
375	return false;	24✔
376
377	//now check the definition of it! - make sure it relates to primary keys etc
378	var updateTriggerName = GetTriggerName();	39✔
379	var query =	39✔
380	$"USE [{_table.Database.GetRuntimeName()}];SELECT OBJECT_DEFINITION (object_id) FROM sys.triggers WHERE name='{updateTriggerName}' and is_disabled=0";	39✔
381
382	try
383	{
384	using var con = _server.GetConnection();	39✔
385	string result;
386
387	con.Open();	39✔
388	using (var cmd = _server.GetCommand(query, con))	39✔
389	{
390	cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	39✔
391	result = cmd.ExecuteScalar() as string;	39✔
392	}	39✔
393
394
395	if (string.IsNullOrWhiteSpace(result))	39!
UNCOV 396	throw new TriggerMissingException(	×
397	$"Trigger {updateTriggerName} does not have an OBJECT_DEFINITION or is missing or is disabled");	×
398
399	var expectedSQL = GetCreateTriggerSQL();	39✔
400
401	expectedSQL = expectedSQL.Trim();	39✔
402	result = result.Trim();	39✔
403
404	if (!expectedSQL.Equals(result))	39✔
405	throw new ExpectedIdenticalStringsException($"Trigger {updateTriggerName} is corrupt",	3✔
406	expectedSQL, result);	3✔
407	}	36✔
408	catch (ExpectedIdenticalStringsException)	3✔
409	{
410	throw;	3✔
411	}
UNCOV 412	catch (IrreconcilableColumnDifferencesInArchiveException)	×
413	{
UNCOV 414	throw;	×
415	}
UNCOV 416	catch (Exception e)	×
417	{
UNCOV 418	throw new Exception(	×
419	$"Failed to check if trigger {updateTriggerName} is enabled. See InnerException for details", e);	×
420	}
421
422	return true;	36✔
423	}
424
425	}

HicServices / RDMP / 28572360334

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