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

0.0

/Rdmp.Core/DataExport/DataExtraction/ExtractTableVerbatim.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.Common;
using System.IO;
using System.Linq;
using FAnsi.Discovery;
using Rdmp.Core.DataExport.DataExtraction.FileOutputFormats;
using Rdmp.Core.DataViewing;
using Rdmp.Core.ReusableLibraryCode.DataAccess;

namespace Rdmp.Core.DataExport.DataExtraction;

/// <summary>
/// Helper class for fetching entire tables from a database and writing them to CSV.  It uses CSVOutputFormat.CleanString to strip out problem characters.
/// Records are read one at a time rather than downloading as a DataTable to allow any size of table to be processed without running out of memory.
/// </summary>
public class ExtractTableVerbatim
{
    private readonly string[] _tableNames;
    private readonly string _specificSQL;

    private readonly DirectoryInfo _outputDirectory;
    private readonly string _separator;
    private readonly string _dateTimeFormat;
    private string _specificSQLTableName;
    private DiscoveredServer _server;
    private Stream _stream;

    /// <summary>
    /// The number of decimal places to round floating point numbers to.  This only applies to data which is hard typed Float and not to string values
    /// </summary>
    private int? RoundFloatsTo { get; set; }

    public string OutputFilename { get; private set; }

    public ExtractTableVerbatim(DiscoveredServer server, string[] tableNames, DirectoryInfo outputDirectory,
        string separator, string dateTimeFormat)
    {
        if (tableNames.Length == 0)
            throw new ArgumentException("You must select at least one table to extract");

        _tableNames = tableNames;
        _outputDirectory = outputDirectory;
        _separator = separator;
        _dateTimeFormat = dateTimeFormat ?? GetDefaultDateTimeFormat();
        _server = server;
    }

    public ExtractTableVerbatim(DirectoryInfo outputDirectory, string separator, string dateTimeFormat,
        params DiscoveredTable[] tables)
        : this(tables.Select(t => t.Database.Server).Distinct().Single(),
            tables.Select(t => t.GetFullyQualifiedName()).ToArray(),
            outputDirectory,
            separator,
            dateTimeFormat)
    {
    }

    /// <summary>
    /// Runs the supplied SQL and puts it out to the file specified (in the outputDirectory), will deal with stripping separators etc automatically
    /// </summary>
    /// <param name="server"></param>
    /// <param name="sql">Some SQL you want to run (instead of a specific table)</param>
    /// <param name="outputName">The name of the csv file you would like to create in the outputDirectory.  Do not include.csv in your string it will be put on automatically</param>
    /// <param name="outputDirectory"></param>
    /// <param name="separator"></param>
    /// <param name="dateTimeFormat"></param>
    public ExtractTableVerbatim(DiscoveredServer server, string sql, string outputName, DirectoryInfo outputDirectory,
        string separator, string dateTimeFormat)
    {
        _specificSQL = sql;
        _specificSQLTableName = outputName;
        _outputDirectory = outputDirectory;
        _separator = separator;
        _dateTimeFormat = dateTimeFormat ?? GetDefaultDateTimeFormat();
        _server = server;
    }

    private static string GetDefaultDateTimeFormat() => "yyyy-MM-dd hh:mm:ss";


    /// <summary>
    /// Runs the supplied SQL and puts it out to the <paramref name="stream"/> specified, will deal with stripping separators etc automatically
    /// </summary>
    /// <param name="server"></param>
    /// <param name="sql">Some SQL you want to run (instead of a specific table)</param>
    /// <param name="stream">The output stream to write data to</param>
    /// <param name="separator"></param>
    /// <param name="dateTimeFormat"></param>
    public ExtractTableVerbatim(DiscoveredServer server, string sql, Stream stream, string separator,
        string dateTimeFormat)
        : this(server, sql, null, null, separator, dateTimeFormat)
    {
        _stream = stream;
    }

    public int DoExtraction()
    {
        var linesWritten = 0;

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

        if (_specificSQL != null) linesWritten += ExtractSQL(_specificSQL, _specificSQLTableName, con);

        if (_tableNames != null)
            foreach (var table in _tableNames)
                linesWritten += ExtractSQL($"select * from {table}", table, con);

        con.Close();

        return linesWritten;
    }

    private int ExtractSQL(string sql, string tableName, DbConnection con)
    {
        int linesWritten;

        using var cmdExtract = _server.GetCommand(sql, con);
        string filename = null;

        if (_outputDirectory != null)
        {
            if (!Directory.Exists(_outputDirectory.FullName))
                Directory.CreateDirectory(_outputDirectory.FullName);

            filename = tableName.Replace("[", "").Replace("]", "").ToLower().Trim();

            if (!filename.EndsWith(".csv"))
                filename += ".csv";
        }

        StreamWriter sw;

        if (_stream != null)
        {
            sw = new StreamWriter(_stream);
        }
        else
        {
            if (_outputDirectory == null)
                throw new Exception(
                    $"{nameof(_outputDirectory)} cannot be null when using file output mode (only with an explicit stream out).");

            if (filename == null)
                throw new Exception(
                    $"{nameof(filename)} cannot be null when using file output mode (only with an explicit stream out).");

            OutputFilename = Path.Combine(_outputDirectory.FullName, filename);
            sw = new StreamWriter(OutputFilename);
        }

        cmdExtract.CommandTimeout = 500000;

        using (var r = cmdExtract.ExecuteReader())
        {
            WriteHeader(sw, r, _separator, _dateTimeFormat);
            linesWritten = WriteBody(sw, r, _separator, _dateTimeFormat, RoundFloatsTo);

            r.Close();
        }

        sw.Flush();
        sw.Close();

        return linesWritten;
    }

    public static void WriteHeader(StreamWriter sw, DbDataReader r, string separator, string dateTimeFormat)
    {
        //write headers
        for (var i = 0; i < r.FieldCount; i++)
        {
            sw.Write(CSVOutputFormat.CleanString(r.GetName(i), separator, out _, dateTimeFormat, null));
            if (i < r.FieldCount - 1)
                sw.Write(separator);
            else
                sw.WriteLine();
        }
    }

    public static int WriteBody(StreamWriter sw, DbDataReader r, string separator, string dateTimeFormat,
        int? roundFloatsTo)
    {
        var linesWritten = 0;

        while (r.Read())
        {
            //write values
            for (var i = 0; i < r.FieldCount; i++)
            {
                //clean string
                sw.Write(CSVOutputFormat.CleanString(r[i], separator, out _, dateTimeFormat, roundFloatsTo));
                if (i < r.FieldCount - 1)
                    sw.Write(separator); //if not the last element add a ','
                else
                    sw.WriteLine();
            }

            linesWritten++;
        }

        return linesWritten;
    }

    /// <summary>
    /// Runs the query described in <paramref name="collection"/> and extracts the data into <paramref name="toFile"/>
    /// </summary>
    /// <param name="collection"></param>
    /// <param name="toFile"></param>
    /// <param name="context">Determines which access credentials (if any) are used to run the query</param>
    public static void ExtractDataToFile(IViewSQLAndResultsCollection collection, FileInfo toFile,
        DataAccessContext context = DataAccessContext.InternalDataProcessing)
    {
        var point = collection.GetDataAccessPoint();
        var db = DataAccessPortal.ExpectDatabase(point, context);

        if (!toFile.Directory.Exists) toFile.Directory.Create();

        using var fs = File.OpenWrite(toFile.FullName);
        var toRun = new ExtractTableVerbatim(db.Server, collection.GetSql(), fs, ",", null);
        toRun.DoExtraction();
    }
}

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.Common;
9	using System.IO;
10	using System.Linq;
11	using FAnsi.Discovery;
12	using Rdmp.Core.DataExport.DataExtraction.FileOutputFormats;
13	using Rdmp.Core.DataViewing;
14	using Rdmp.Core.ReusableLibraryCode.DataAccess;
15
16	namespace Rdmp.Core.DataExport.DataExtraction;
17
18	/// <summary>
19	/// Helper class for fetching entire tables from a database and writing them to CSV. It uses CSVOutputFormat.CleanString to strip out problem characters.
20	/// Records are read one at a time rather than downloading as a DataTable to allow any size of table to be processed without running out of memory.
21	/// </summary>
22	public class ExtractTableVerbatim
23	{
24	private readonly string[] _tableNames;
25	private readonly string _specificSQL;
26
27	private readonly DirectoryInfo _outputDirectory;
28	private readonly string _separator;
29	private readonly string _dateTimeFormat;
30	private string _specificSQLTableName;
31	private DiscoveredServer _server;
32	private Stream _stream;
33
34	/// <summary>
35	/// The number of decimal places to round floating point numbers to. This only applies to data which is hard typed Float and not to string values
36	/// </summary>
37	private int? RoundFloatsTo { get; set; }	×
38
39	public string OutputFilename { get; private set; }	×
40
41	public ExtractTableVerbatim(DiscoveredServer server, string[] tableNames, DirectoryInfo outputDirectory,	×
42	string separator, string dateTimeFormat)	×
43	{
44	if (tableNames.Length == 0)	×
45	throw new ArgumentException("You must select at least one table to extract");	×
46
47	_tableNames = tableNames;	×
48	_outputDirectory = outputDirectory;	×
49	_separator = separator;	×
50	_dateTimeFormat = dateTimeFormat ?? GetDefaultDateTimeFormat();	×
51	_server = server;	×
52	}	×
53
54	public ExtractTableVerbatim(DirectoryInfo outputDirectory, string separator, string dateTimeFormat,
55	params DiscoveredTable[] tables)
56	: this(tables.Select(t => t.Database.Server).Distinct().Single(),	×
57	tables.Select(t => t.GetFullyQualifiedName()).ToArray(),	×
58	outputDirectory,	×
59	separator,	×
60	dateTimeFormat)	×
61	{
62	}	×
63
64	/// <summary>
65	/// Runs the supplied SQL and puts it out to the file specified (in the outputDirectory), will deal with stripping separators etc automatically
66	/// </summary>
67	/// <param name="server"></param>
68	/// <param name="sql">Some SQL you want to run (instead of a specific table)</param>
69	/// <param name="outputName">The name of the csv file you would like to create in the outputDirectory. Do not include.csv in your string it will be put on automatically</param>
70	/// <param name="outputDirectory"></param>
71	/// <param name="separator"></param>
72	/// <param name="dateTimeFormat"></param>
73	public ExtractTableVerbatim(DiscoveredServer server, string sql, string outputName, DirectoryInfo outputDirectory,	×
74	string separator, string dateTimeFormat)	×
75	{
76	_specificSQL = sql;	×
77	_specificSQLTableName = outputName;	×
78	_outputDirectory = outputDirectory;	×
79	_separator = separator;	×
80	_dateTimeFormat = dateTimeFormat ?? GetDefaultDateTimeFormat();	×
81	_server = server;	×
82	}	×
83
84	private static string GetDefaultDateTimeFormat() => "yyyy-MM-dd hh:mm:ss";	×
85
86
87	/// <summary>
88	/// Runs the supplied SQL and puts it out to the <paramref name="stream"/> specified, will deal with stripping separators etc automatically
89	/// </summary>
90	/// <param name="server"></param>
91	/// <param name="sql">Some SQL you want to run (instead of a specific table)</param>
92	/// <param name="stream">The output stream to write data to</param>
93	/// <param name="separator"></param>
94	/// <param name="dateTimeFormat"></param>
95	public ExtractTableVerbatim(DiscoveredServer server, string sql, Stream stream, string separator,
96	string dateTimeFormat)
97	: this(server, sql, null, null, separator, dateTimeFormat)	×
98	{
99	_stream = stream;	×
100	}	×
101
102	public int DoExtraction()
103	{
104	var linesWritten = 0;	×
105
106	using var con = _server.GetConnection();	×
107	con.Open();	×
108
109	if (_specificSQL != null) linesWritten += ExtractSQL(_specificSQL, _specificSQLTableName, con);	×
110
111	if (_tableNames != null)	×
112	foreach (var table in _tableNames)	×
113	linesWritten += ExtractSQL($"select * from {table}", table, con);	×
114
115	con.Close();	×
116
117	return linesWritten;	×
118	}	×
119
120	private int ExtractSQL(string sql, string tableName, DbConnection con)
121	{
122	int linesWritten;
123
124	using var cmdExtract = _server.GetCommand(sql, con);	×
125	string filename = null;	×
126
127	if (_outputDirectory != null)	×
128	{
129	if (!Directory.Exists(_outputDirectory.FullName))	×
130	Directory.CreateDirectory(_outputDirectory.FullName);	×
131
132	filename = tableName.Replace("[", "").Replace("]", "").ToLower().Trim();	×
133
134	if (!filename.EndsWith(".csv"))	×
135	filename += ".csv";	×
136	}
137
138	StreamWriter sw;
139
140	if (_stream != null)	×
141	{
142	sw = new StreamWriter(_stream);	×
143	}
144	else
145	{
146	if (_outputDirectory == null)	×
147	throw new Exception(	×
148	$"{nameof(_outputDirectory)} cannot be null when using file output mode (only with an explicit stream out).");	×
149
150	if (filename == null)	×
151	throw new Exception(	×
152	$"{nameof(filename)} cannot be null when using file output mode (only with an explicit stream out).");	×
153
154	OutputFilename = Path.Combine(_outputDirectory.FullName, filename);	×
155	sw = new StreamWriter(OutputFilename);	×
156	}
157
158	cmdExtract.CommandTimeout = 500000;	×
159
160	using (var r = cmdExtract.ExecuteReader())	×
161	{
162	WriteHeader(sw, r, _separator, _dateTimeFormat);	×
163	linesWritten = WriteBody(sw, r, _separator, _dateTimeFormat, RoundFloatsTo);	×
164
165	r.Close();	×
166	}	×
167
168	sw.Flush();	×
169	sw.Close();	×
170
171	return linesWritten;	×
172	}	×
173
174	public static void WriteHeader(StreamWriter sw, DbDataReader r, string separator, string dateTimeFormat)
175	{
176	//write headers
177	for (var i = 0; i < r.FieldCount; i++)	×
178	{
179	sw.Write(CSVOutputFormat.CleanString(r.GetName(i), separator, out _, dateTimeFormat, null));	×
180	if (i < r.FieldCount - 1)	×
181	sw.Write(separator);	×
182	else
183	sw.WriteLine();	×
184	}
185	}	×
186
187	public static int WriteBody(StreamWriter sw, DbDataReader r, string separator, string dateTimeFormat,
188	int? roundFloatsTo)
189	{
190	var linesWritten = 0;	×
191
192	while (r.Read())	×
193	{
194	//write values
195	for (var i = 0; i < r.FieldCount; i++)	×
196	{
197	//clean string
198	sw.Write(CSVOutputFormat.CleanString(r[i], separator, out _, dateTimeFormat, roundFloatsTo));	×
199	if (i < r.FieldCount - 1)	×
200	sw.Write(separator); //if not the last element add a ','	×
201	else
202	sw.WriteLine();	×
203	}
204
205	linesWritten++;	×
206	}
207
208	return linesWritten;	×
209	}
210
211	/// <summary>
212	/// Runs the query described in <paramref name="collection"/> and extracts the data into <paramref name="toFile"/>
213	/// </summary>
214	/// <param name="collection"></param>
215	/// <param name="toFile"></param>
216	/// <param name="context">Determines which access credentials (if any) are used to run the query</param>
217	public static void ExtractDataToFile(IViewSQLAndResultsCollection collection, FileInfo toFile,
218	DataAccessContext context = DataAccessContext.InternalDataProcessing)
219	{
220	var point = collection.GetDataAccessPoint();	×
221	var db = DataAccessPortal.ExpectDatabase(point, context);	×
222
223	if (!toFile.Directory.Exists) toFile.Directory.Create();	×
224
225	using var fs = File.OpenWrite(toFile.FullName);	×
226	var toRun = new ExtractTableVerbatim(db.Server, collection.GetSql(), fs, ",", null);	×
227	toRun.DoExtraction();	×
228	}	×
229	}

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