13318089130

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

Build # 13318089130

Build Type

Pull #2134

github

Committed by

jas88

Commit Message

Update ChildProviderTests.cs

Fix up TestUpTo method

Pull Request Pull Request #2134: CodeQL fixups

Run Details

11346 of 21308 branches covered (53.25%)

Branch coverage included in aggregate %.

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

362 existing lines in 23 files now uncovered.

32218 of 54590 relevant lines covered (59.02%)

17091.93 hits per line

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

93.94

/Rdmp.Core/Curation/DataHelper/RegexRedaction/RegexRedactionHelper.cs

﻿// Copyright (c) The University of Dundee 2024-2024
// 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 FAnsi.Discovery.QuerySyntax;
using FAnsi.Discovery;
using Rdmp.Core.Curation.Data;
using Rdmp.Core.MapsDirectlyToDatabaseTable;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using Rdmp.Core.Repositories;

namespace Rdmp.Core.Curation.DataHelper.RegexRedaction
{
    public static class RegexRedactionHelper
    {
        public enum Constants {
            pksToSave_Temp,
            redactionsToSaveTable_Temp,
            ID,
            RedactionConfiguration_ID,
            ColumnInfo_ID,
            startingIndex,
            ReplacementValue,
            RedactedValue, 
            RegexRedaction_ID,
            Value,
            TEMP_RedactionUpdates
        };


        public static DataTable GenerateRedactionsDataTable()
        {
            DataTable redactionsToSaveTable = new DataTable();
            redactionsToSaveTable.Columns.Add(nameof(Constants.RedactionConfiguration_ID));
            redactionsToSaveTable.Columns.Add(nameof(Constants.ColumnInfo_ID));
            redactionsToSaveTable.Columns.Add(nameof(Constants.startingIndex));
            redactionsToSaveTable.Columns.Add(nameof(Constants.ReplacementValue));
            redactionsToSaveTable.Columns.Add(nameof(Constants.RedactedValue));
            return redactionsToSaveTable;
        }

        public static DataTable GeneratePKDataTable()
        {
            DataTable pkDataTable = new DataTable();
            pkDataTable.Columns.Add(nameof(Constants.RegexRedaction_ID));
            pkDataTable.Columns.Add(nameof(Constants.ColumnInfo_ID));
            pkDataTable.Columns.Add(nameof(Constants.Value));
            pkDataTable.Columns.Add(nameof(Constants.ID), typeof(int));
            return pkDataTable;
        }

        public static string ConvertPotentialDateTimeObject(string value, string currentColumnType)
        {
            var matchValue = $"'{value}'";
            if (currentColumnType is not ("datetime2" or "datetime")) return matchValue;

            var x = DateTime.Parse(value);
            const string format = "yyyy-MM-dd HH:mm:ss:fff";
            matchValue = $"'{x.ToString(format)}'";
            return matchValue;
        }

        private static string GetRedactionValue(string value, ColumnInfo column, DataRow m, List<CatalogueItem> _cataloguePKs, RegexRedactionConfiguration _redactionConfiguration, DataTable redactionsToSaveTable, DataTable pksToSave, DataTable redactionUpates)
        {
            var pkLookup = Enumerable.Range(0, _cataloguePKs.Count).ToDictionary(i => _cataloguePKs[i].ColumnInfo, i => m[i + 1].ToString());
            var matches = Regex.Matches(value, _redactionConfiguration.RegexPattern);
            var offset = 0;
            foreach (var match in matches)
            {
                var foundMatch = match.ToString();
                var startingIndex = value.IndexOf(foundMatch);
                string replacementValue = _redactionConfiguration.RedactionString;

                var lengthDiff = (float)foundMatch.Length - replacementValue.Length;
                if (lengthDiff < 0)
                {
                    throw new Exception($"Redaction string '{_redactionConfiguration.RedactionString}' is longer than found match '{foundMatch}'.");
                }
                if (lengthDiff > 0)
                {
                    var start = (int)Math.Floor(lengthDiff / 2);
                    var end = (int)Math.Ceiling(lengthDiff / 2);
                    replacementValue = replacementValue.PadLeft(start + replacementValue.Length, '<');
                    replacementValue = replacementValue.PadRight(end + replacementValue.Length, '>');
                }
                value = value[..startingIndex] + replacementValue + value[(startingIndex + foundMatch.Length)..];
                redactionsToSaveTable.Rows.Add([_redactionConfiguration.ID, column.ID, startingIndex, replacementValue, foundMatch]);
                foreach (var pk in pkLookup)
                {
                    pksToSave.Rows.Add([redactionUpates.Rows.Count + offset, pk.Key.ID, pk.Value]);
                }
                offset++;
            }

            return value;
        }

        public static void Redact(ColumnInfo column, DataRow match, List<CatalogueItem> _cataloguePKs, RegexRedactionConfiguration _redactionConfiguration, DataTable redactionsToSaveTable, DataTable pksToSave, DataTable redactionUpates)
        {

            var redactedValue = GetRedactionValue(match[0].ToString(), column, match, _cataloguePKs, _redactionConfiguration, redactionsToSaveTable, pksToSave, redactionUpates);
            match[0] = redactedValue;
            redactionUpates.ImportRow(match);
        }

        public static void SaveRedactions(ICatalogueRepository catalogueRepo, DiscoveredTable pksToSave, DiscoveredTable redactionsToSaveTable, DiscoveredServer _server, int timeout = 30000)
        {
            var sql = $@"
                DECLARE @output TABLE (id1 int, inc int IDENTITY(1,1))
                INSERT INTO RegexRedaction(RedactionConfiguration_ID,ColumnInfo_ID,startingIndex,ReplacementValue,RedactedValue) OUTPUT inserted.id as id1 INTO @output
                SELECT RedactionConfiguration_ID,ColumnInfo_ID,startingIndex,ReplacementValue,RedactedValue FROM {redactionsToSaveTable.GetFullyQualifiedName()};
                                
                                DECLARE @IDMATCHER TABLE (RegexRedaction_ID int,ColumnInfo_ID int ,Value varchar(max),ID int , id1 int , inc int)
                                insert into @IDMATCHER(RegexRedaction_ID, ColumnInfo_ID,Value,ID,id1,inc)
                                select RegexRedaction_ID, ColumnInfo_ID,Value,ID,id1,inc
                                FROM {pksToSave.GetFullyQualifiedName()} as t1
                                JOIN @output as t2 ON t1.RegexRedaction_ID+1 = t2.inc
                                where t1.RegexRedaction_ID+1 = t2.inc;

                                update @IDMATCHER
                                set RegexRedaction_ID = id1
                                where RegexRedaction_ID+1 = inc;

                INSERT INTO RegexRedactionKey(RegexRedaction_ID,ColumnInfo_ID,Value)
                            select RegexRedaction_ID,ColumnInfo_ID,Value  FROM @IDMATCHER;
            ";
           
                (catalogueRepo as TableRepository).Insert(sql, null, timeout);
        }

        public static void DoJoinUpdate(ColumnInfo column, DiscoveredTable _discoveredTable, DiscoveredServer _server, DataTable redactionUpates, DiscoveredColumn[] _discoveredPKColumns, int timeout = 30000)
        {
            var redactionTable = _discoveredTable.Database.CreateTable(nameof(Constants.TEMP_RedactionUpdates), redactionUpates);
            var updateHelper = _server.GetQuerySyntaxHelper().UpdateHelper;

            var sqlLines = new List<CustomLine>
        {
            new CustomLine($"t1.{column.GetRuntimeName()} = t2.{column.GetRuntimeName()}", QueryComponent.SET)
        };
            foreach (var pk in _discoveredPKColumns)
            {
                sqlLines.Add(new CustomLine($"t1.{pk.GetRuntimeName()} = t2.{pk.GetRuntimeName()}", QueryComponent.WHERE));
                sqlLines.Add(new CustomLine(string.Format("t1.{0} = t2.{0}", pk.GetRuntimeName()), QueryComponent.JoinInfoJoin));

            }
            var sql = updateHelper.BuildUpdate(_discoveredTable, redactionTable, sqlLines);
            var conn = _server.GetConnection();
            conn.Open();
            using (var cmd = _server.GetCommand(sql, conn))
            {
                cmd.CommandTimeout = timeout;
                cmd.ExecuteNonQuery();
            }
            conn.Close();
            redactionTable.Drop();
        }
    }
}

1	// Copyright (c) The University of Dundee 2024-2024
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 FAnsi.Discovery.QuerySyntax;
8	using FAnsi.Discovery;
9	using Rdmp.Core.Curation.Data;
10	using Rdmp.Core.MapsDirectlyToDatabaseTable;
11	using System;
12	using System.Collections.Generic;
13	using System.Data;
14	using System.Linq;
15	using System.Text.RegularExpressions;
16	using Rdmp.Core.Repositories;
17
18	namespace Rdmp.Core.Curation.DataHelper.RegexRedaction
19	{
20	public static class RegexRedactionHelper
21	{
22	public enum Constants {
23	pksToSave_Temp,
24	redactionsToSaveTable_Temp,
25	ID,
26	RedactionConfiguration_ID,
27	ColumnInfo_ID,
28	startingIndex,
29	ReplacementValue,
30	RedactedValue,
31	RegexRedaction_ID,
32	Value,
33	TEMP_RedactionUpdates
34	};
35
36
37	public static DataTable GenerateRedactionsDataTable()
38	{
39	DataTable redactionsToSaveTable = new DataTable();	34✔
40	redactionsToSaveTable.Columns.Add(nameof(Constants.RedactionConfiguration_ID));	34✔
41	redactionsToSaveTable.Columns.Add(nameof(Constants.ColumnInfo_ID));	34✔
42	redactionsToSaveTable.Columns.Add(nameof(Constants.startingIndex));	34✔
43	redactionsToSaveTable.Columns.Add(nameof(Constants.ReplacementValue));	34✔
44	redactionsToSaveTable.Columns.Add(nameof(Constants.RedactedValue));	34✔
45	return redactionsToSaveTable;	34✔
46	}
47
48	public static DataTable GeneratePKDataTable()
49	{
50	DataTable pkDataTable = new DataTable();	34✔
51	pkDataTable.Columns.Add(nameof(Constants.RegexRedaction_ID));	34✔
52	pkDataTable.Columns.Add(nameof(Constants.ColumnInfo_ID));	34✔
53	pkDataTable.Columns.Add(nameof(Constants.Value));	34✔
54	pkDataTable.Columns.Add(nameof(Constants.ID), typeof(int));	34✔
55	return pkDataTable;	34✔
56	}
57
58	public static string ConvertPotentialDateTimeObject(string value, string currentColumnType)
59	{
60	var matchValue = $"'{value}'";	20✔
61	if (currentColumnType is not ("datetime2" or "datetime")) return matchValue;	40!
62
NEW 63	var x = DateTime.Parse(value);	×
64	const string format = "yyyy-MM-dd HH:mm:ss:fff";
NEW 65	matchValue = $"'{x.ToString(format)}'";	×
UNCOV 66	return matchValue;	×
67	}
68
69	private static string GetRedactionValue(string value, ColumnInfo column, DataRow m, List<CatalogueItem> _cataloguePKs, RegexRedactionConfiguration _redactionConfiguration, DataTable redactionsToSaveTable, DataTable pksToSave, DataTable redactionUpates)
70	{
71	var pkLookup = Enumerable.Range(0, _cataloguePKs.Count).ToDictionary(i => _cataloguePKs[i].ColumnInfo, i => m[i + 1].ToString());	10,000,104✔
72	var matches = Regex.Matches(value, _redactionConfiguration.RegexPattern);	2,000,028✔
73	var offset = 0;	2,000,028✔
74	foreach (var match in matches)	8,000,120✔
75	{
76	var foundMatch = match.ToString();	2,000,034✔
77	var startingIndex = value.IndexOf(foundMatch);	2,000,034✔
78	string replacementValue = _redactionConfiguration.RedactionString;	2,000,034✔
79
80	var lengthDiff = (float)foundMatch.Length - replacementValue.Length;	2,000,034✔
81	if (lengthDiff < 0)	2,000,034✔
82	{
83	throw new Exception($"Redaction string '{_redactionConfiguration.RedactionString}' is longer than found match '{foundMatch}'.");	4✔
84	}
85	if (lengthDiff > 0)	2,000,030✔
86	{
87	var start = (int)Math.Floor(lengthDiff / 2);	2,000,030✔
88	var end = (int)Math.Ceiling(lengthDiff / 2);	2,000,030✔
89	replacementValue = replacementValue.PadLeft(start + replacementValue.Length, '<');	2,000,030✔
90	replacementValue = replacementValue.PadRight(end + replacementValue.Length, '>');	2,000,030✔
91	}
92	value = value[..startingIndex] + replacementValue + value[(startingIndex + foundMatch.Length)..];	2,000,030✔
93	redactionsToSaveTable.Rows.Add([_redactionConfiguration.ID, column.ID, startingIndex, replacementValue, foundMatch]);	2,000,030✔
94	foreach (var pk in pkLookup)	12,000,140✔
95	{
96	pksToSave.Rows.Add([redactionUpates.Rows.Count + offset, pk.Key.ID, pk.Value]);	4,000,040✔
97	}
98	offset++;	2,000,030✔
99	}
100
101	return value;	2,000,024✔
102	}
103
104	public static void Redact(ColumnInfo column, DataRow match, List<CatalogueItem> _cataloguePKs, RegexRedactionConfiguration _redactionConfiguration, DataTable redactionsToSaveTable, DataTable pksToSave, DataTable redactionUpates)
105	{
106
107	var redactedValue = GetRedactionValue(match[0].ToString(), column, match, _cataloguePKs, _redactionConfiguration, redactionsToSaveTable, pksToSave, redactionUpates);	2,000,028✔
108	match[0] = redactedValue;	2,000,024✔
109	redactionUpates.ImportRow(match);	2,000,024✔
110	}	2,000,024✔
111
112	public static void SaveRedactions(ICatalogueRepository catalogueRepo, DiscoveredTable pksToSave, DiscoveredTable redactionsToSaveTable, DiscoveredServer _server, int timeout = 30000)
113	{
114	var sql = $@"	24✔
115	DECLARE @output TABLE (id1 int, inc int IDENTITY(1,1))	24✔
116	INSERT INTO RegexRedaction(RedactionConfiguration_ID,ColumnInfo_ID,startingIndex,ReplacementValue,RedactedValue) OUTPUT inserted.id as id1 INTO @output	24✔
117	SELECT RedactionConfiguration_ID,ColumnInfo_ID,startingIndex,ReplacementValue,RedactedValue FROM {redactionsToSaveTable.GetFullyQualifiedName()};	24✔
118		24✔
119	DECLARE @IDMATCHER TABLE (RegexRedaction_ID int,ColumnInfo_ID int ,Value varchar(max),ID int , id1 int , inc int)	24✔
120	insert into @IDMATCHER(RegexRedaction_ID, ColumnInfo_ID,Value,ID,id1,inc)	24✔
121	select RegexRedaction_ID, ColumnInfo_ID,Value,ID,id1,inc	24✔
122	FROM {pksToSave.GetFullyQualifiedName()} as t1	24✔
123	JOIN @output as t2 ON t1.RegexRedaction_ID+1 = t2.inc	24✔
124	where t1.RegexRedaction_ID+1 = t2.inc;	24✔
125		24✔
126	update @IDMATCHER	24✔
127	set RegexRedaction_ID = id1	24✔
128	where RegexRedaction_ID+1 = inc;	24✔
129		24✔
130	INSERT INTO RegexRedactionKey(RegexRedaction_ID,ColumnInfo_ID,Value)	24✔
131	select RegexRedaction_ID,ColumnInfo_ID,Value FROM @IDMATCHER;	24✔
132	";	24✔
133
134	(catalogueRepo as TableRepository).Insert(sql, null, timeout);	24✔
135	}	24✔
136
137	public static void DoJoinUpdate(ColumnInfo column, DiscoveredTable _discoveredTable, DiscoveredServer _server, DataTable redactionUpates, DiscoveredColumn[] _discoveredPKColumns, int timeout = 30000)
138	{
139	var redactionTable = _discoveredTable.Database.CreateTable(nameof(Constants.TEMP_RedactionUpdates), redactionUpates);	24✔
140	var updateHelper = _server.GetQuerySyntaxHelper().UpdateHelper;	24✔
141
142	var sqlLines = new List<CustomLine>	24✔
143	{	24✔
144	new CustomLine($"t1.{column.GetRuntimeName()} = t2.{column.GetRuntimeName()}", QueryComponent.SET)	24✔
145	};	24✔
146	foreach (var pk in _discoveredPKColumns)	116✔
147	{
148	sqlLines.Add(new CustomLine($"t1.{pk.GetRuntimeName()} = t2.{pk.GetRuntimeName()}", QueryComponent.WHERE));	34✔
149	sqlLines.Add(new CustomLine(string.Format("t1.{0} = t2.{0}", pk.GetRuntimeName()), QueryComponent.JoinInfoJoin));	34✔
150
151	}
152	var sql = updateHelper.BuildUpdate(_discoveredTable, redactionTable, sqlLines);	24✔
153	var conn = _server.GetConnection();	24✔
154	conn.Open();	24✔
155	using (var cmd = _server.GetCommand(sql, conn))	24✔
156	{
157	cmd.CommandTimeout = timeout;	24✔
158	cmd.ExecuteNonQuery();	24✔
159	}	24✔
160	conn.Close();	24✔
161	redactionTable.Drop();	24✔
162	}	24✔
163	}
164	}

HicServices / RDMP / 13318089130

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

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