6245535001

Committed 20 Sep 2023 07:44AM UTC coverage: 57.013%. First build

Build # 6245535001

Build Type

push

github

Committed by

web-flow

Commit Message

8.1.0 Release (#1628)

* Bump Newtonsoft.Json from 13.0.1 to 13.0.2

Bumps [Newtonsoft.Json](https://github.com/JamesNK/Newtonsoft.Json) from 13.0.1 to 13.0.2.
- [Release notes](https://github.com/JamesNK/Newtonsoft.Json/releases)
- [Commits](https://github.com/JamesNK/Newtonsoft.Json/compare/13.0.1...13.0.2)

---
updated-dependencies:
- dependency-name: Newtonsoft.Json
  dependency-type: direct:production
  update-type: version-update:semver-patch
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump NLog from 5.0.5 to 5.1.0

Bumps [NLog](https://github.com/NLog/NLog) from 5.0.5 to 5.1.0.
- [Release notes](https://github.com/NLog/NLog/releases)
- [Changelog](https://github.com/NLog/NLog/blob/dev/CHANGELOG.md)
- [Commits](https://github.com/NLog/NLog/compare/v5.0.5...v5.1.0)

---
updated-dependencies:
- dependency-name: NLog
  dependency-type: direct:production
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump NLog from 5.0.5 to 5.1.0

* Fix -r flag - should have been --results-directory all along

* Bump Newtonsoft.Json from 13.0.1 to 13.0.2

* Bump YamlDotNet from 12.0.2 to 12.1.0

Bumps [YamlDotNet](https://github.com/aaubry/YamlDotNet) from 12.0.2 to 12.1.0.
- [Release notes](https://github.com/aaubry/YamlDotNet/releases)
- [Commits](https://github.com/aaubry/YamlDotNet/compare/v12.0.2...v12.1.0)

---
updated-dependencies:
- dependency-name: YamlDotNet
  dependency-type: direct:production
  update-type: version-update:semver-minor
...

Signed-off-by: dependabot[bot] <support@github.com>

* Bump Moq from 4.18.2 to 4.18.3

Bumps [Moq](https://github.com/moq/moq4) from 4.18.2 to 4.18.3.
- [Release notes](https://github.com/moq/moq4/releases)
- [Changelog](https://github.com/moq/moq4/blob/main/CHANGELOG.md)
- [Commits](https://github.com/moq/moq4/compare/v4.18.2...v4.18.3)

---
updated-dependencies:
- dependency-name: Moq
... (continued)

Run Details

10732 of 20257 branches covered (0.0%)

Branch coverage included in aggregate %.

48141 of 48141 new or added lines in 1086 files covered. (100.0%)

30685 of 52388 relevant lines covered (58.57%)

7387.88 hits per line

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

90.31

/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 occured 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)"/>
    public MicrosoftSQLTriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true) : base(table,
        createDataLoadRunIDAlso)
    {
        _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;
    }

    private 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))
            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();
        sqlToRun += $",{Environment.NewLine}";
        sqlToRun += $"\thic_validTo datetime,{Environment.NewLine}";
        sqlToRun += "\thic_userID varchar(128),";
        sqlToRun += "\thic_status char(1)";


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

        var liveCols = _columns.Select(c => $"[{c.GetRuntimeName()}]").Union(new string[]
        {
            $"[{SpecialFieldNames.DataLoadRunID}]", $"[{SpecialFieldNames.ValidFrom}]"
        }).ToArray();

        var archiveCols = $"{string.Join(",", liveCols)},hic_validTo,hic_userID,hic_status";
        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;

        sqlToRun += $"\tINSERT @returntable{Environment.NewLine}";
        sqlToRun +=
            $"\tSELECT {cDotArchiveCols},NULL AS hic_validTo, NULL AS hic_userID, 'C' AS hic_status{Environment.NewLine}"; //c is for current
        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 occured 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)"/>
40	public MicrosoftSQLTriggerImplementer(DiscoveredTable table, bool createDataLoadRunIDAlso = true) : base(table,	156✔
41	createDataLoadRunIDAlso)	156✔
42	{
43	_schema = string.IsNullOrWhiteSpace(_table.Schema) ? "dbo" : _table.Schema;	156✔
44	_triggerName = $"{_schema}.{GetTriggerName()}";	156✔
45	}	156✔
46
47	public override void DropTrigger(out string problemsDroppingTrigger, out string thingsThatWorkedDroppingTrigger)
48	{
49	using var con = _server.GetConnection();	54✔
50	con.Open();	54✔
51
52	problemsDroppingTrigger = "";	54✔
53	thingsThatWorkedDroppingTrigger = "";	54✔
54
55	using (var cmdDropTrigger = _server.GetCommand($"DROP TRIGGER {_triggerName}", con))	54✔
56	{
57	try
58	{
59	cmdDropTrigger.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	54✔
60	thingsThatWorkedDroppingTrigger += $"Dropped Trigger successfully{Environment.NewLine}";	54✔
61	cmdDropTrigger.ExecuteNonQuery();	54✔
62	}	8✔
63	catch (Exception exception)	46✔
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}";	46✔
67	}	46✔
68	}
69
70	using (var cmdDropArchiveIndex = _server.GetCommand(	54✔
71	$"DROP INDEX PKsIndex ON {_archiveTable.GetRuntimeName()}", con))	54✔
72	{
73	try
74	{
75	cmdDropArchiveIndex.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	54✔
76	cmdDropArchiveIndex.ExecuteNonQuery();	54✔
77
78	thingsThatWorkedDroppingTrigger +=	10✔
79	$"Dropped index PKsIndex on Archive table successfully{Environment.NewLine}";	10✔
80	}	10✔
81	catch (Exception exception)	44✔
82	{
83	problemsDroppingTrigger += $"Failed to drop Archive Index:{exception.Message}{Environment.NewLine}";	44✔
84	}	44✔
85	}
86
87	using var cmdDropArchiveLegacyView = _server.GetCommand($"DROP FUNCTION {_table.GetRuntimeName()}_Legacy", con);	54✔
88	try
89	{
90	cmdDropArchiveLegacyView.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	54✔
91	cmdDropArchiveLegacyView.ExecuteNonQuery();	54✔
92	thingsThatWorkedDroppingTrigger += $"Dropped Legacy Table View successfully{Environment.NewLine}";	14✔
93	}	14✔
94	catch (Exception exception)	40✔
95	{
96	problemsDroppingTrigger +=	40✔
97	$"Failed to drop Legacy Table View:{exception.Message}{Environment.NewLine}";	40✔
98	}	40✔
99	}	54✔
100
101	public override string CreateTrigger(ICheckNotifier notifier)
102	{
103	var createArchiveTableSQL = base.CreateTrigger(notifier);	72✔
104
105	using var con = _server.GetConnection();	68✔
106	con.Open();	68✔
107
108	var trigger = GetCreateTriggerSQL();	68✔
109
110	using (var cmdAddTrigger = _server.GetCommand(trigger, con))	68✔
111	{
112	cmdAddTrigger.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	68✔
113	cmdAddTrigger.ExecuteNonQuery();	68✔
114	}	68✔
115
116
117	//Add key so that we can more easily do comparisons on primary key between main table and archive
118	var idxCompositeKeyBody = "";	68✔
119
120	foreach (var key in _primaryKeys)	388✔
121	idxCompositeKeyBody += $"[{key.GetRuntimeName()}] ASC,";	126✔
122
123	//remove trailing comma
124	idxCompositeKeyBody = idxCompositeKeyBody.TrimEnd(',');	68✔
125
126	var createIndexSQL =	68✔
127	$@"CREATE NONCLUSTERED INDEX [PKsIndex] ON {_archiveTable.GetFullyQualifiedName()} ({idxCompositeKeyBody})";	68✔
128	using (var cmdCreateIndex = _server.GetCommand(createIndexSQL, con))	68✔
129	{
130	try
131	{
132	cmdCreateIndex.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	68✔
133	cmdCreateIndex.ExecuteNonQuery();	68✔
134	}	68✔
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);	68✔
146
147	return createArchiveTableSQL;	68✔
148	}	68✔
149
150	private string GetCreateTriggerSQL()
151	{
152	if (!_primaryKeys.Any())	150!
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 =	150✔
157	$" UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM deleted where ";	150✔
158
159	//its a combo field so join on both when filling in hic_validFrom
160	foreach (var key in _primaryKeys)	824✔
161	updateValidToWhere += $"[{_table}].[{key}] = deleted.[{key}] AND ";	262✔
162
163	//trim off last AND
164	updateValidToWhere = updateValidToWhere[..^"AND ".Length];	150✔
165
166	var InsertedToDeletedJoin = "JOIN inserted i ON ";	150✔
167	InsertedToDeletedJoin += GetTableToTableEqualsSqlWithPrimaryKeys("i", "d");	150✔
168
169	var equalsSqlTableToInserted = GetTableToTableEqualsSqlWithPrimaryKeys(_table.GetRuntimeName(), "inserted");	150✔
170	var equalsSqlTableToDeleted = GetTableToTableEqualsSqlWithPrimaryKeys(_table.GetRuntimeName(), "deleted");	150✔
171
172	var columnNames = _columns.Select(c => c.GetRuntimeName()).ToList();	1,834✔
173
174	if (!columnNames.Contains(SpecialFieldNames.DataLoadRunID, StringComparer.CurrentCultureIgnoreCase))	150!
175	columnNames.Add(SpecialFieldNames.DataLoadRunID);	×
176
177	if (!columnNames.Contains(SpecialFieldNames.ValidFrom, StringComparer.CurrentCultureIgnoreCase))	150!
178	columnNames.Add(SpecialFieldNames.ValidFrom);	×
179
180	var colList = string.Join(",", columnNames.Select(c => $"[{c}]"));	1,834✔
181	var dDotColList = string.Join(",", columnNames.Select(c => $"d.[{c}]"));	1,834✔
182
183	return	150✔
184	$@"	150✔
185	CREATE TRIGGER {_triggerName} ON {_table.GetFullyQualifiedName()}	150✔
186	AFTER UPDATE, DELETE	150✔
187	AS BEGIN	150✔
188		150✔
189	SET NOCOUNT ON	150✔
190		150✔
191	declare @isPrimaryKeyChange bit = 0	150✔
192		150✔
193	--it will be a primary key change if deleted and inserted do not agree on primary key values	150✔
194	IF exists ( select 1 FROM deleted d RIGHT {InsertedToDeletedJoin} WHERE d.[{_primaryKeys.First().GetRuntimeName()}] is null)	150✔
195	begin	150✔
196	UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM inserted where {equalsSqlTableToInserted}	150✔
197	set @isPrimaryKeyChange = 1	150✔
198	end	150✔
199	else	150✔
200	begin	150✔
201	UPDATE [{_table}] SET {SpecialFieldNames.ValidFrom} = GETDATE() FROM deleted where {equalsSqlTableToDeleted}	150✔
202	set @isPrimaryKeyChange = 0	150✔
203	end	150✔
204		150✔
205	{updateValidToWhere}	150✔
206		150✔
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	150✔
208	FROM deleted d	150✔
209	LEFT {InsertedToDeletedJoin}	150✔
210		150✔
211	SET NOCOUNT OFF	150✔
212		150✔
213	RETURN	150✔
214	END	150✔
215	";	150✔
216	}
217
218	private string GetTableToTableEqualsSqlWithPrimaryKeys(string table1, string table2)
219	{
220	var toReturn = "";	450✔
221
222	foreach (var key in _primaryKeys)	2,472✔
223	toReturn += $" [{table1}].[{key.GetRuntimeName()}] = [{table2}].[{key.GetRuntimeName()}] AND ";	786✔
224
225	//trim off last AND
226	toReturn = toReturn[..^"AND ".Length];	450✔
227
228	return toReturn;	450✔
229	}
230
231	private void CreateViewOldVersionsTableValuedFunction(string sqlUsedToCreateArchiveTableSQL, DbConnection con)
232	{
233	var columnsInArchive = "";	68✔
234
235	var syntaxHelper = MicrosoftQuerySyntaxHelper.Instance;	68✔
236
237	var matchStartColumnExtraction = Regex.Match(sqlUsedToCreateArchiveTableSQL, "CREATE TABLE .*\\(");	68✔
238
239	if (!matchStartColumnExtraction.Success)	68!
240	throw new Exception("Could not find regex match at start of Archive table CREATE SQL");	×
241
242	var startExtractingColumnsAt = matchStartColumnExtraction.Index + matchStartColumnExtraction.Length;	68✔
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..];	68✔
245
246	//trim off excess crud at the end
247	columnsInArchive = columnsInArchive.Trim(new[] { ')', '\r', '\n' });	68✔
248
249	var sqlToRun = string.Format("CREATE FUNCTION [" + _schema + "].[{0}_Legacy]",	68✔
250	QuerySyntaxHelper.MakeHeaderNameSensible(_table.GetRuntimeName()));	68✔
251	sqlToRun += Environment.NewLine;	68✔
252	sqlToRun += $"({Environment.NewLine}";	68✔
253	sqlToRun += $"\t@index DATETIME{Environment.NewLine}";	68✔
254	sqlToRun += $"){Environment.NewLine}";	68✔
255	sqlToRun += $"RETURNS @returntable TABLE{Environment.NewLine}";	68✔
256	sqlToRun += $"({Environment.NewLine}";	68✔
257	sqlToRun += $"/the return table will follow the structure of the Archive table/{Environment.NewLine}";	68✔
258	sqlToRun += columnsInArchive;	68✔
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();	68✔
262	sqlToRun += $",{Environment.NewLine}";	68✔
263	sqlToRun += $"\thic_validTo datetime,{Environment.NewLine}";	68✔
264	sqlToRun += "\thic_userID varchar(128),";	68✔
265	sqlToRun += "\thic_status char(1)";	68✔
266
267
268	sqlToRun += $"){Environment.NewLine}";	68✔
269	sqlToRun += $"AS{Environment.NewLine}";	68✔
270	sqlToRun += $"BEGIN{Environment.NewLine}";	68✔
271	sqlToRun += Environment.NewLine;	68✔
272
273	var liveCols = _columns.Select(c => $"[{c.GetRuntimeName()}]").Union(new string[]	818✔
274	{	68✔
275	$"[{SpecialFieldNames.DataLoadRunID}]", $"[{SpecialFieldNames.ValidFrom}]"	68✔
276	}).ToArray();	68✔
277
278	var archiveCols = $"{string.Join(",", liveCols)},hic_validTo,hic_userID,hic_status";	68✔
279	var cDotArchiveCols = string.Join(",", liveCols.Select(s => $"c.{s}"));	818✔
280
281
282	sqlToRun += $"\tINSERT @returntable{Environment.NewLine}";	68✔
283	sqlToRun += string.Format(	68✔
284	"\tSELECT " + archiveCols + " FROM [{0}] WHERE @index BETWEEN ISNULL(" + SpecialFieldNames.ValidFrom +	68✔
285	", '1899/01/01') AND hic_validTo" + Environment.NewLine, _archiveTable);	68✔
286	sqlToRun += Environment.NewLine;	68✔
287
288	sqlToRun += $"\tINSERT @returntable{Environment.NewLine}";	68✔
289	sqlToRun +=	68✔
290	$"\tSELECT {cDotArchiveCols},NULL AS hic_validTo, NULL AS hic_userID, 'C' AS hic_status{Environment.NewLine}"; //c is for current	68✔
291	sqlToRun += string.Format("\tFROM [{0}] c" + Environment.NewLine, _table.GetRuntimeName());	68✔
292	sqlToRun += $"\tLEFT OUTER JOIN @returntable a ON {Environment.NewLine}";	68✔
293
294	for (var index = 0; index < _primaryKeys.Length; index++)	388✔
295	{
296	sqlToRun += string.Format("\ta.{0}=c.{0} " + Environment.NewLine,	126✔
297	syntaxHelper.EnsureWrapped(_primaryKeys[index].GetRuntimeName())); //add the primary key joins	126✔
298
299	if (index + 1 < _primaryKeys.Length)	126✔
300	sqlToRun += $"\tAND{Environment.NewLine}"; //add an AND because there are more coming	58✔
301	}
302
303	sqlToRun += string.Format("\tWHERE a.[{0}] IS NULL -- where archive record doesn't exist" + Environment.NewLine,	68✔
304	_primaryKeys.First().GetRuntimeName());	68✔
305	sqlToRun += $"\tAND @index > ISNULL(c.{SpecialFieldNames.ValidFrom}, '1899/01/01'){Environment.NewLine}";	68✔
306
307	sqlToRun += Environment.NewLine;	68✔
308	sqlToRun += $"RETURN{Environment.NewLine}";	68✔
309	sqlToRun += $"END{Environment.NewLine}";	68✔
310
311	using var cmd = _server.GetCommand(sqlToRun, con);	68✔
312	cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	68✔
313	cmd.ExecuteNonQuery();	68✔
314	}	136✔
315
316	public override TriggerStatus GetTriggerStatus()
317	{
318	var updateTriggerName = GetTriggerName();	152✔
319
320	var queryTriggerIsItDisabledOrMissing =	152✔
321	$"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";	152✔
322
323	try
324	{
325	using var conn = _server.GetConnection();	152✔
326	conn.Open();	152✔
327	using var cmd = _server.GetCommand(queryTriggerIsItDisabledOrMissing, conn);	152✔
328	cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	152✔
329	cmd.Parameters.Add(new SqlParameter("@triggerName", SqlDbType.VarChar));	152✔
330	cmd.Parameters["@triggerName"].Value = updateTriggerName;	152✔
331
332	var result = Convert.ToInt32(cmd.ExecuteScalar());	152✔
333
334	return result switch	152!
335	{	152✔
336	0 => TriggerStatus.Enabled,	98✔
337	1 => TriggerStatus.Disabled,	2✔
338	-1 => TriggerStatus.Missing,	52✔
339	_ => throw new NotSupportedException($"Query returned unexpected value:{result}")	×
340	};	152✔
341	}
342	catch (Exception e)	×
343	{
344	throw new Exception($"Failed to check if trigger {updateTriggerName} is enabled: {e}");	×
345	}
346	}	152✔
347
348	private string GetTriggerName() => $"{QuerySyntaxHelper.MakeHeaderNameSensible(_table.GetRuntimeName())}_OnUpdate";	390✔
349
350	public override bool CheckUpdateTriggerIsEnabledAndHasExpectedBody()
351	{
352	var baseResult = base.CheckUpdateTriggerIsEnabledAndHasExpectedBody();	128✔
353
354	if (!baseResult)	126✔
355	return false;	44✔
356
357	//now check the definition of it! - make sure it relates to primary keys etc
358	var updateTriggerName = GetTriggerName();	82✔
359	var query =	82✔
360	$"USE [{_table.Database.GetRuntimeName()}];SELECT OBJECT_DEFINITION (object_id) FROM sys.triggers WHERE name='{updateTriggerName}' and is_disabled=0";	82✔
361
362	try
363	{
364	using var con = _server.GetConnection();	82✔
365	string result;
366
367	con.Open();	82✔
368	using (var cmd = _server.GetCommand(query, con))	82✔
369	{
370	cmd.CommandTimeout = UserSettings.ArchiveTriggerTimeout;	82✔
371	result = cmd.ExecuteScalar() as string;	82✔
372	}	82✔
373
374
375	if (string.IsNullOrWhiteSpace(result))	82!
376	throw new TriggerMissingException(	×
377	$"Trigger {updateTriggerName} does not have an OBJECT_DEFINITION or is missing or is disabled");	×
378
379	var expectedSQL = GetCreateTriggerSQL();	82✔
380
381	expectedSQL = expectedSQL.Trim();	82✔
382	result = result.Trim();	82✔
383
384	if (!expectedSQL.Equals(result))	82✔
385	throw new ExpectedIdenticalStringsException($"Trigger {updateTriggerName} is corrupt",	6✔
386	expectedSQL, result);	6✔
387	}	76✔
388	catch (ExpectedIdenticalStringsException)	6✔
389	{
390	throw;	6✔
391	}
392	catch (IrreconcilableColumnDifferencesInArchiveException)	×
393	{
394	throw;	×
395	}
396	catch (Exception e)	×
397	{
398	throw new Exception(	×
399	$"Failed to check if trigger {updateTriggerName} is enabled. See InnerException for details", e);	×
400	}
401
402	return true;	76✔
403	}
404	}

HicServices / RDMP / 6245535001

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

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