11978888584

Committed 22 Nov 2024 07:27PM UTC coverage: 57.383% (-0.002%) from 57.385%

Build # 11978888584

Build Type

push

github

Committed by

jas88

Commit Message

Fix up redundant type inheritance

Run Details

11206 of 21050 branches covered (53.24%)

Branch coverage included in aggregate %.

65 of 249 new or added lines in 42 files covered. (26.1%)

17 existing lines in 14 files now uncovered.

31718 of 53752 relevant lines covered (59.01%)

8290.69 hits per line

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

77.36

/Rdmp.Core/DataExport/Data/ExternalCohortTable.cs

// Copyright (c) The University of Dundee 2018-2019
// This file is part of the Research Data Management Platform (RDMP).
// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using FAnsi;
using FAnsi.Connections;
using FAnsi.Discovery;
using FAnsi.Discovery.QuerySyntax;
using Rdmp.Core.Curation.Data;
using Rdmp.Core.MapsDirectlyToDatabaseTable;
using Rdmp.Core.MapsDirectlyToDatabaseTable.Attributes;
using Rdmp.Core.Repositories;
using Rdmp.Core.ReusableLibraryCode;
using Rdmp.Core.ReusableLibraryCode.Annotations;
using Rdmp.Core.ReusableLibraryCode.Checks;
using Rdmp.Core.ReusableLibraryCode.DataAccess;

namespace Rdmp.Core.DataExport.Data;

/// <inheritdoc cref="IExternalCohortTable"/>
public class ExternalCohortTable : DatabaseEntity, IDataAccessCredentials, IExternalCohortTable
{
    #region Database Properties

    private string _name;

    /// <summary>
    /// Human readable name for the type of cohort which is stored in the database referenced by this object (e.g."CHI to Guid Cohorts")
    /// </summary>
    [NotNull]
    [Unique]
    public string Name
    {
        get => _name;
        set => SetField(ref _name, value);
    }

    #endregion

    [NoMappingToDatabase] private SelfCertifyingDataAccessPoint SelfCertifyingDataAccessPoint { get; set; }

    /// <inheritdoc/>
    public string DefinitionTableForeignKeyField
    {
        get => _definitionTableForeignKeyField;
        set => SetField(ref _definitionTableForeignKeyField,
            Qualify(Database ?? string.Empty, TableName ?? string.Empty, value));
    }

    /// <inheritdoc/>
    public string TableName
    {
        get => _tableName;
        set => SetField(ref _tableName, Qualify(Database ?? string.Empty, value ?? string.Empty));
    }

    private string Qualify(string db, string tbl, string col = null)
    {
        //if we already have a value being set that is qualified don't mess it up!
        if ((col ?? tbl ?? string.Empty).Contains('.'))
            return col ?? tbl;

        //they sent us something like "bob" for a table/column name, let's fully qualify it with the Database etc
        var syntax = GetQuerySyntaxHelper();

        return col == null
            ? syntax.EnsureFullyQualified(
                syntax.GetRuntimeName(db ?? string.Empty),
                null /*no schema*/,
                syntax.GetRuntimeName(tbl ?? string.Empty))
            : syntax.EnsureFullyQualified(
                syntax.GetRuntimeName(db ?? string.Empty),
                null /*no schema*/,
                syntax.GetRuntimeName(tbl ?? string.Empty),
                syntax.GetRuntimeName(col));
    }

    /// <inheritdoc/>
    public string DefinitionTableName
    {
        get => _definitionTableName;
        set => SetField(ref _definitionTableName, Qualify(Database ?? string.Empty, value ?? string.Empty));
    }

    /// <inheritdoc/>
    public string PrivateIdentifierField
    {
        get => _privateIdentifierField;
        set => SetField(ref _privateIdentifierField,
            Qualify(Database ?? string.Empty, TableName, value ?? string.Empty));
    }

    /// <inheritdoc/>
    /// <summary>
    /// When reading this, use GetReleaseIdentifier(ExtractableCohort cohort) where possible to respect cohort.OverrideReleaseIdentifierSQL
    /// </summary>
    public string ReleaseIdentifierField
    {
        get => _releaseIdentifierField;
        set => SetField(ref _releaseIdentifierField,
            Qualify(Database ?? string.Empty, TableName, value ?? string.Empty));
    }

    /// <summary>
    /// Fields expected to be part of any table referenced by the <see cref="DefinitionTableName"/> property
    /// </summary>
    public static readonly string[] CohortDefinitionTable_RequiredFields =
    {
        "id",
        // joins to CohortToDefinitionTableJoinColumn and is used as ID in all ExtractableCohort entities throughout DataExportManager
        "projectNumber",
        "description",
        "version",
        "dtCreated"
    };

    private string _privateIdentifierField;
    private string _releaseIdentifierField;
    private string _tableName;
    private string _definitionTableName;
    private string _definitionTableForeignKeyField;

    /// <summary>
    /// Returns <see cref="Name"/>
    /// </summary>
    /// <returns></returns>
    public override string ToString() => Name;

    public ExternalCohortTable()
    {
        SelfCertifyingDataAccessPoint = new SelfCertifyingDataAccessPoint();
    }

    /// <summary>
    /// Creates a new blank pointer to a cohort database.
    /// </summary>
    /// <param name="repository">Metadata repository in which to create the object</param>
    /// <param name="name"></param>
    /// <param name="databaseType"></param>
    public ExternalCohortTable(IDataExportRepository repository, string name, DatabaseType databaseType)
    {
        Repository = repository;
        SelfCertifyingDataAccessPoint = new SelfCertifyingDataAccessPoint(repository.CatalogueRepository, databaseType);
        Repository.InsertAndHydrate(this, new Dictionary<string, object>
        {
            { "Name", name ?? $"NewExternalSource{Guid.NewGuid()}" },
            { "DatabaseType", databaseType.ToString() }
        });
    }

    /// <summary>
    /// Reads an existing cohort database reference out of the metadata repository database
    /// </summary>
    /// <param name="repository"></param>
    /// <param name="r"></param>
    internal ExternalCohortTable(IDataExportRepository repository, DbDataReader r)
        : base(repository, r)
    {
        Name = r["Name"] as string;
        var databaseType = (DatabaseType)Enum.Parse(typeof(DatabaseType), r["DatabaseType"].ToString());

        SelfCertifyingDataAccessPoint = new SelfCertifyingDataAccessPoint(repository.CatalogueRepository, databaseType);

        Server = r["Server"] as string;
        Username = r["Username"] as string;
        Password = r["Password"] as string;
        Database = r["Database"] as string ?? string.Empty;

        TableName = Qualify(Database, r["TableName"] as string ?? string.Empty);
        DefinitionTableForeignKeyField = Qualify(Database, TableName,
            r["DefinitionTableForeignKeyField"] as string ?? string.Empty);

        DefinitionTableName = Qualify(Database, r["DefinitionTableName"] as string ?? string.Empty);

        PrivateIdentifierField = Qualify(Database, TableName, r["PrivateIdentifierField"] as string ?? string.Empty);
        ReleaseIdentifierField = Qualify(Database, TableName, r["ReleaseIdentifierField"] as string ?? string.Empty);
    }

    /// <inheritdoc/>
    public IQuerySyntaxHelper GetQuerySyntaxHelper() =>
        QuerySyntaxHelperFactory.Create(SelfCertifyingDataAccessPoint.DatabaseType);

    /// <inheritdoc/>
    public DiscoveredDatabase Discover() => SelfCertifyingDataAccessPoint.Discover(DataAccessContext.DataExport);

    /// <inheritdoc/>
    public DiscoveredTable DiscoverCohortTable()
    {
        var db = Discover();
        return db.ExpectTable(db.Server.GetQuerySyntaxHelper().GetRuntimeName(TableName));
    }

    public DiscoveredTable DiscoverDefinitionTable()
    {
        var db = Discover();
        return db.ExpectTable(db.Server.GetQuerySyntaxHelper().GetRuntimeName(DefinitionTableName));
    }

    /// <inheritdoc/>
    public DiscoveredColumn DiscoverPrivateIdentifier() => Discover(DiscoverCohortTable(), PrivateIdentifierField);

    /// <inheritdoc/>
    public DiscoveredColumn DiscoverReleaseIdentifier() => Discover(DiscoverCohortTable(), ReleaseIdentifierField);

    public DiscoveredColumn DiscoverDefinitionTableForeignKey() =>
        Discover(DiscoverCohortTable(), DefinitionTableForeignKeyField);

    private static DiscoveredColumn Discover(DiscoveredTable tbl, string column) =>
        tbl.DiscoverColumn(tbl.Database.Server.GetQuerySyntaxHelper().GetRuntimeName(column));


    /// <summary>
    /// Checks that the remote cohort storage database described by this class exists and contains a compatible schema.
    /// </summary>
    /// <param name="notifier"></param>
    public void Check(ICheckNotifier notifier)
    {
        //make sure we can get to server
        CheckCohortDatabaseAccessible(notifier);

        CheckCohortDatabaseHasCorrectTables(notifier);

        if (string.Equals(PrivateIdentifierField, ReleaseIdentifierField))
            notifier.OnCheckPerformed(
                new CheckEventArgs(ErrorCodes.ExtractionIsIdentifiable));
    }

    #region Stuff for checking the remote (not data export manager) table where the cohort is allegedly stored

    /// <inheritdoc/>
    public bool IDExistsInCohortTable(int originID)
    {
        var server = DataAccessPortal.ExpectServer(this, DataAccessContext.DataExport);

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

        var sql = $@"select count(*) from {DefinitionTableName} where id = {originID}";

        using var cmdGetDescriptionOfCohortFromConsus = server.GetCommand(sql, con);
        try
        {
            return int.Parse(cmdGetDescriptionOfCohortFromConsus.ExecuteScalar().ToString()) >= 1;
        }
        catch (Exception e)
        {
            throw new Exception(
                $"Could not connect to server {Server} (Database '{Database}') which is the data source of ExternalCohortTable (source) called '{Name}' (ID={ID})",
                e);
        }
    }

    private void CheckCohortDatabaseHasCorrectTables(ICheckNotifier notifier)
    {
        try
        {
            var database = Discover();

            var cohortTable = DiscoverCohortTable();
            if (cohortTable.Exists())
            {
                notifier.OnCheckPerformed(new CheckEventArgs($"Found table {cohortTable} in database {Database}",
                    CheckResult.Success));

                DiscoverPrivateIdentifier();
                DiscoverReleaseIdentifier();
                DiscoverDefinitionTableForeignKey();
            }
            else
            {
                notifier.OnCheckPerformed(new CheckEventArgs($"Could not find table {TableName} in database {Database}",
                    CheckResult.Fail));
            }

            var foundCohortDefinitionTable = DiscoverDefinitionTable();

            if (foundCohortDefinitionTable.Exists())
            {
                notifier.OnCheckPerformed(new CheckEventArgs(
                    $"Found table {DefinitionTableName} in database {Database}", CheckResult.Success));

                var cols = foundCohortDefinitionTable.DiscoverColumns();

                foreach (var requiredField in CohortDefinitionTable_RequiredFields)
                    ComplainIfColumnMissing(DefinitionTableName, cols, requiredField, notifier);
            }
            else
            {
                notifier.OnCheckPerformed(new CheckEventArgs(
                    $"Could not find table {DefinitionTableName} in database {Database}", CheckResult.Fail));
            }
        }
        catch (Exception e)
        {
            notifier.OnCheckPerformed(new CheckEventArgs(
                $"Could not check table intactness for ExternalCohortTable '{Name}'", CheckResult.Fail, e));
        }
    }

    private void CheckCohortDatabaseAccessible(ICheckNotifier notifier)
    {
        try
        {
            DataAccessPortal.ExpectServer(this, DataAccessContext.DataExport).TestConnection();

            notifier.OnCheckPerformed(new CheckEventArgs($"Connected to Cohort database '{Name}'", CheckResult.Success));
        }
        catch (Exception e)
        {
            notifier.OnCheckPerformed(new CheckEventArgs($"Could not connect to Cohort database called '{Name}'",
                CheckResult.Fail, e));
        }
    }

    /// <inheritdoc/>
    public void PushToServer(ICohortDefinition newCohortDefinition, IManagedConnection connection)
    {
        newCohortDefinition.ID = DiscoverDefinitionTable().Insert(new Dictionary<string, object>
        {
            { "projectNumber", newCohortDefinition.ProjectNumber },
            { "version", newCohortDefinition.Version },
            { "description", newCohortDefinition.Description }
        }, connection.ManagedTransaction);
    }

    #endregion

    private void ComplainIfColumnMissing(string tableNameFullyQualified, DiscoveredColumn[] columns,
        string colToFindCanBeFullyQualifiedIfYouLike, ICheckNotifier notifier)
    {
        var tofind = GetQuerySyntaxHelper().GetRuntimeName(colToFindCanBeFullyQualifiedIfYouLike);

        if (columns.Any(col => col.GetRuntimeName().Equals(tofind, StringComparison.OrdinalIgnoreCase)))
            notifier.OnCheckPerformed(new CheckEventArgs(
                $"Found required field {tofind} in table {tableNameFullyQualified}",
                CheckResult.Success));
        else
            notifier.OnCheckPerformed(new CheckEventArgs(
                $"Could not find required field {tofind} in table {tableNameFullyQualified}(It had the following columns:{columns.Aggregate("", static (s, n) => $"{s}{n},")})",
                CheckResult.Fail));
    }


    #region IDataAccessCredentials and IDataAccessPoint delegation

    /// <inheritdoc/>
    public string Password
    {
        get => SelfCertifyingDataAccessPoint.Password;
        set
        {
            SelfCertifyingDataAccessPoint.Password = value;
            OnPropertyChanged(null, value);
        }
    }

    /// <inheritdoc/>
    public string GetDecryptedPassword() => SelfCertifyingDataAccessPoint.GetDecryptedPassword() ?? "";

    /// <inheritdoc/>
    public string Username
    {
        get => SelfCertifyingDataAccessPoint.Username;
        set
        {
            if (Equals(SelfCertifyingDataAccessPoint.Username, value))
                return;

            var old = SelfCertifyingDataAccessPoint.Username;
            SelfCertifyingDataAccessPoint.Username = value;
            OnPropertyChanged(old, value);
        }
    }

    /// <inheritdoc/>
    public string Server
    {
        get => SelfCertifyingDataAccessPoint.Server;
        set
        {
            if (Equals(SelfCertifyingDataAccessPoint.Server, value))
                return;

            var old = SelfCertifyingDataAccessPoint.Server;
            SelfCertifyingDataAccessPoint.Server = value;
            OnPropertyChanged(old, value);
        }
    }

    /// <inheritdoc/>
    public string Database
    {
        get => SelfCertifyingDataAccessPoint.Database;
        set
        {
            if (Equals(SelfCertifyingDataAccessPoint.Database, value))
                return;

            var old = SelfCertifyingDataAccessPoint.Database;
            SelfCertifyingDataAccessPoint.Database = value;
            OnPropertyChanged(old, value);
        }
    }

    /// <inheritdoc/>
    public DatabaseType DatabaseType
    {
        get => SelfCertifyingDataAccessPoint.DatabaseType;
        set
        {
            if (Equals(SelfCertifyingDataAccessPoint.DatabaseType, value))
                return;

            var old = SelfCertifyingDataAccessPoint.DatabaseType;
            SelfCertifyingDataAccessPoint.DatabaseType = value;
            OnPropertyChanged(old, value);
        }
    }

    /// <inheritdoc/>
    public IDataAccessCredentials GetCredentialsIfExists(DataAccessContext context) =>
        SelfCertifyingDataAccessPoint.GetCredentialsIfExists(context);

    #endregion


    /// <summary>
    /// Returns SQL query for counting the number of unique patients in each cohort defined in the database
    /// referenced by this <see cref="ExternalCohortTable"/>
    /// </summary>
    /// <returns></returns>
    public string GetCountsDataTableSql()
    {
        var syntax = GetQuerySyntaxHelper();


        return $@"SELECT 
id as OriginID,
count(*) as Count,
count(distinct {ReleaseIdentifierField}) as CountDistinct,
{syntax.EnsureWrapped("projectNumber")} as {syntax.EnsureWrapped("ProjectNumber")},
version as {syntax.EnsureWrapped("Version")},
description as {syntax.EnsureWrapped("Description")},
{syntax.EnsureWrapped("dtCreated")}
  FROM
   {TableName}
   join 
   {DefinitionTableName} on {DefinitionTableForeignKeyField} = id
   group by 
   id,
   {syntax.EnsureWrapped("projectNumber")},
   version,
   description,
   {syntax.EnsureWrapped("dtCreated")}";
    }

    /// <summary>
    /// Returns SQL query for listing all cohorts stored in the database referenced by this <see cref="ExternalCohortTable"/>.
    /// This includes only the ids, project numbers, version, description etc not the actual patient identifiers themselves.
    /// </summary>
    /// <returns></returns>
    public string GetExternalDataSql()
    {
        var syntax = GetQuerySyntaxHelper();

        return $@"SELECT 
id as {syntax.EnsureWrapped("OriginID")},
{syntax.EnsureWrapped("projectNumber")} as {syntax.EnsureWrapped("ProjectNumber")},
version as {syntax.EnsureWrapped("Version")},
description as {syntax.EnsureWrapped("Description")},
{syntax.EnsureWrapped("dtCreated")}
  FROM
   {DefinitionTableName}";
    }

    /// <summary>
    /// Returns nothing
    /// </summary>
    /// <returns></returns>
    public IHasDependencies[] GetObjectsThisDependsOn() => Array.Empty<IHasDependencies>();

    /// <summary>
    /// Returns all cohorts in the source
    /// </summary>
    /// <returns></returns>
    public IHasDependencies[] GetObjectsDependingOnThis()
    {
        return (IHasDependencies[])Repository.GetAllObjects<ExtractableCohort>()
            .Where(c => c.ExternalCohortTable_ID == ID);
    }

    /// <summary>
    /// returns true if all the relevant fields are populated (table names, column names etc)
    /// </summary>
    /// <returns></returns>
    public bool IsFullyPopulated() =>
        !
            (string.IsNullOrWhiteSpace(TableName) ||
             string.IsNullOrWhiteSpace(PrivateIdentifierField) ||
             string.IsNullOrWhiteSpace(ReleaseIdentifierField) ||
             string.IsNullOrWhiteSpace(DefinitionTableForeignKeyField) ||
             string.IsNullOrWhiteSpace(DefinitionTableName));

    public bool DiscoverExistence(DataAccessContext context, out string reason) =>
        SelfCertifyingDataAccessPoint.DiscoverExistence(context, out reason);

    public void SetRepository(ICatalogueRepository repository)
    {
        SelfCertifyingDataAccessPoint.SetRepository(repository);
    }
}

1	// Copyright (c) The University of Dundee 2018-2019
2	// This file is part of the Research Data Management Platform (RDMP).
3	// RDMP is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
4	// RDMP is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
5	// You should have received a copy of the GNU General Public License along with RDMP. If not, see <https://www.gnu.org/licenses/>.
6
7	using System;
8	using System.Collections.Generic;
9	using System.Data.Common;
10	using System.Linq;
11	using FAnsi;
12	using FAnsi.Connections;
13	using FAnsi.Discovery;
14	using FAnsi.Discovery.QuerySyntax;
15	using Rdmp.Core.Curation.Data;
16	using Rdmp.Core.MapsDirectlyToDatabaseTable;
17	using Rdmp.Core.MapsDirectlyToDatabaseTable.Attributes;
18	using Rdmp.Core.Repositories;
19	using Rdmp.Core.ReusableLibraryCode;
20	using Rdmp.Core.ReusableLibraryCode.Annotations;
21	using Rdmp.Core.ReusableLibraryCode.Checks;
22	using Rdmp.Core.ReusableLibraryCode.DataAccess;
23
24	namespace Rdmp.Core.DataExport.Data;
25
26	/// <inheritdoc cref="IExternalCohortTable"/>
27	public class ExternalCohortTable : DatabaseEntity, IDataAccessCredentials, IExternalCohortTable
28	{
29	#region Database Properties
30
31	private string _name;
32
33	/// <summary>
34	/// Human readable name for the type of cohort which is stored in the database referenced by this object (e.g."CHI to Guid Cohorts")
35	/// </summary>
36	[NotNull]
37	[Unique]
38	public string Name
39	{
40	get => _name;	1,172✔
41	set => SetField(ref _name, value);	750✔
42	}
43
44	#endregion
45
46	[NoMappingToDatabase] private SelfCertifyingDataAccessPoint SelfCertifyingDataAccessPoint { get; set; }	22,814✔
47
48	/// <inheritdoc/>
49	public string DefinitionTableForeignKeyField
50	{
51	get => _definitionTableForeignKeyField;	1,272✔
52	set => SetField(ref _definitionTableForeignKeyField,	748✔
53	Qualify(Database ?? string.Empty, TableName ?? string.Empty, value));	748✔
54	}
55
56	/// <inheritdoc/>
57	public string TableName
58	{
59	get => _tableName;	6,032✔
60	set => SetField(ref _tableName, Qualify(Database ?? string.Empty, value ?? string.Empty));	748!
61	}
62
63	private string Qualify(string db, string tbl, string col = null)
64	{
65	//if we already have a value being set that is qualified don't mess it up!
66	if ((col ?? tbl ?? string.Empty).Contains('.'))	6,614!
67	return col ?? tbl;	5,402✔
68
69	//they sent us something like "bob" for a table/column name, let's fully qualify it with the Database etc
70	var syntax = GetQuerySyntaxHelper();	1,212✔
71
72	return col == null	1,212!
73	? syntax.EnsureFullyQualified(	1,212✔
74	syntax.GetRuntimeName(db ?? string.Empty),	1,212✔
75	null /no schema/,	1,212✔
76	syntax.GetRuntimeName(tbl ?? string.Empty))	1,212✔
77	: syntax.EnsureFullyQualified(	1,212✔
78	syntax.GetRuntimeName(db ?? string.Empty),	1,212✔
79	null /no schema/,	1,212✔
80	syntax.GetRuntimeName(tbl ?? string.Empty),	1,212✔
81	syntax.GetRuntimeName(col));	1,212✔
82	}
83
84	/// <inheritdoc/>
85	public string DefinitionTableName
86	{
87	get => _definitionTableName;	1,890✔
88	set => SetField(ref _definitionTableName, Qualify(Database ?? string.Empty, value ?? string.Empty));	744!
89	}
90
91	/// <inheritdoc/>
92	public string PrivateIdentifierField
93	{
94	get => _privateIdentifierField;	1,764✔
95	set => SetField(ref _privateIdentifierField,	750!
96	Qualify(Database ?? string.Empty, TableName, value ?? string.Empty));	750✔
97	}
98
99	/// <inheritdoc/>
100	/// <summary>
101	/// When reading this, use GetReleaseIdentifier(ExtractableCohort cohort) where possible to respect cohort.OverrideReleaseIdentifierSQL
102	/// </summary>
103	public string ReleaseIdentifierField
104	{
105	get => _releaseIdentifierField;	1,336✔
106	set => SetField(ref _releaseIdentifierField,	754!
107	Qualify(Database ?? string.Empty, TableName, value ?? string.Empty));	754✔
108	}
109
110	/// <summary>
111	/// Fields expected to be part of any table referenced by the <see cref="DefinitionTableName"/> property
112	/// </summary>
113	public static readonly string[] CohortDefinitionTable_RequiredFields =	2✔
114	{	2✔
115	"id",	2✔
116	// joins to CohortToDefinitionTableJoinColumn and is used as ID in all ExtractableCohort entities throughout DataExportManager	2✔
117	"projectNumber",	2✔
118	"description",	2✔
119	"version",	2✔
120	"dtCreated"	2✔
121	};	2✔
122
123	private string _privateIdentifierField;
124	private string _releaseIdentifierField;
125	private string _tableName;
126	private string _definitionTableName;
127	private string _definitionTableForeignKeyField;
128
129	/// <summary>
130	/// Returns <see cref="Name"/>
131	/// </summary>
132	/// <returns></returns>
133	public override string ToString() => Name;	8✔
134
135	public ExternalCohortTable()	×
136	{
137	SelfCertifyingDataAccessPoint = new SelfCertifyingDataAccessPoint();	×
138	}	×
139
140	/// <summary>
141	/// Creates a new blank pointer to a cohort database.
142	/// </summary>
143	/// <param name="repository">Metadata repository in which to create the object</param>
144	/// <param name="name"></param>
145	/// <param name="databaseType"></param>
146	public ExternalCohortTable(IDataExportRepository repository, string name, DatabaseType databaseType)	106✔
147	{
148	Repository = repository;	106✔
149	SelfCertifyingDataAccessPoint = new SelfCertifyingDataAccessPoint(repository.CatalogueRepository, databaseType);	106✔
150	Repository.InsertAndHydrate(this, new Dictionary<string, object>	106!
151	{	106✔
152	{ "Name", name ?? $"NewExternalSource{Guid.NewGuid()}" },	106✔
153	{ "DatabaseType", databaseType.ToString() }	106✔
154	});	106✔
155	}	106✔
156
157	/// <summary>
158	/// Reads an existing cohort database reference out of the metadata repository database
159	/// </summary>
160	/// <param name="repository"></param>
161	/// <param name="r"></param>
162	internal ExternalCohortTable(IDataExportRepository repository, DbDataReader r)
163	: base(repository, r)	574✔
164	{
165	Name = r["Name"] as string;	574✔
166	var databaseType = (DatabaseType)Enum.Parse(typeof(DatabaseType), r["DatabaseType"].ToString());	574✔
167
168	SelfCertifyingDataAccessPoint = new SelfCertifyingDataAccessPoint(repository.CatalogueRepository, databaseType);	574✔
169
170	Server = r["Server"] as string;	574✔
171	Username = r["Username"] as string;	574✔
172	Password = r["Password"] as string;	574✔
173	Database = r["Database"] as string ?? string.Empty;	574✔
174
175	TableName = Qualify(Database, r["TableName"] as string ?? string.Empty);	574✔
176	DefinitionTableForeignKeyField = Qualify(Database, TableName,	574✔
177	r["DefinitionTableForeignKeyField"] as string ?? string.Empty);	574✔
178
179	DefinitionTableName = Qualify(Database, r["DefinitionTableName"] as string ?? string.Empty);	574✔
180
181	PrivateIdentifierField = Qualify(Database, TableName, r["PrivateIdentifierField"] as string ?? string.Empty);	574✔
182	ReleaseIdentifierField = Qualify(Database, TableName, r["ReleaseIdentifierField"] as string ?? string.Empty);	574✔
183	}	574✔
184
185	/// <inheritdoc/>
186	public IQuerySyntaxHelper GetQuerySyntaxHelper() =>
187	QuerySyntaxHelperFactory.Create(SelfCertifyingDataAccessPoint.DatabaseType);	2,956✔
188
189	/// <inheritdoc/>
190	public DiscoveredDatabase Discover() => SelfCertifyingDataAccessPoint.Discover(DataAccessContext.DataExport);	1,054✔
191
192	/// <inheritdoc/>
193	public DiscoveredTable DiscoverCohortTable()
194	{
195	var db = Discover();	498✔
196	return db.ExpectTable(db.Server.GetQuerySyntaxHelper().GetRuntimeName(TableName));	498✔
197	}
198
199	public DiscoveredTable DiscoverDefinitionTable()
200	{
201	var db = Discover();	118✔
202	return db.ExpectTable(db.Server.GetQuerySyntaxHelper().GetRuntimeName(DefinitionTableName));	118✔
203	}
204
205	/// <inheritdoc/>
206	public DiscoveredColumn DiscoverPrivateIdentifier() => Discover(DiscoverCohortTable(), PrivateIdentifierField);	222✔
207
208	/// <inheritdoc/>
209	public DiscoveredColumn DiscoverReleaseIdentifier() => Discover(DiscoverCohortTable(), ReleaseIdentifierField);	80✔
210
211	public DiscoveredColumn DiscoverDefinitionTableForeignKey() =>
212	Discover(DiscoverCohortTable(), DefinitionTableForeignKeyField);	58✔
213
214	private static DiscoveredColumn Discover(DiscoveredTable tbl, string column) =>
215	tbl.DiscoverColumn(tbl.Database.Server.GetQuerySyntaxHelper().GetRuntimeName(column));	360✔
216
217
218	/// <summary>
219	/// Checks that the remote cohort storage database described by this class exists and contains a compatible schema.
220	/// </summary>
221	/// <param name="notifier"></param>
222	public void Check(ICheckNotifier notifier)
223	{
224	//make sure we can get to server
225	CheckCohortDatabaseAccessible(notifier);	60✔
226
227	CheckCohortDatabaseHasCorrectTables(notifier);	58✔
228
229	if (string.Equals(PrivateIdentifierField, ReleaseIdentifierField))	58✔
230	notifier.OnCheckPerformed(	4✔
231	new CheckEventArgs(ErrorCodes.ExtractionIsIdentifiable));	4✔
232	}	56✔
233
234	#region Stuff for checking the remote (not data export manager) table where the cohort is allegedly stored
235
236	/// <inheritdoc/>
237	public bool IDExistsInCohortTable(int originID)
238	{
239	var server = DataAccessPortal.ExpectServer(this, DataAccessContext.DataExport);	118✔
240
241	using var con = server.GetConnection();	118✔
242	con.Open();	118✔
243
244	var sql = $@"select count(*) from {DefinitionTableName} where id = {originID}";	118✔
245
246	using var cmdGetDescriptionOfCohortFromConsus = server.GetCommand(sql, con);	118✔
247	try
248	{
249	return int.Parse(cmdGetDescriptionOfCohortFromConsus.ExecuteScalar().ToString()) >= 1;	118✔
250	}
251	catch (Exception e)	×
252	{
253	throw new Exception(	×
254	$"Could not connect to server {Server} (Database '{Database}') which is the data source of ExternalCohortTable (source) called '{Name}' (ID={ID})",	×
255	e);	×
256	}
257	}	118✔
258
259	private void CheckCohortDatabaseHasCorrectTables(ICheckNotifier notifier)
260	{
261	try
262	{
263	var database = Discover();	58✔
264
265	var cohortTable = DiscoverCohortTable();	58✔
266	if (cohortTable.Exists())	58!
267	{
268	notifier.OnCheckPerformed(new CheckEventArgs($"Found table {cohortTable} in database {Database}",	58✔
269	CheckResult.Success));	58✔
270
271	DiscoverPrivateIdentifier();	58✔
272	DiscoverReleaseIdentifier();	58✔
273	DiscoverDefinitionTableForeignKey();	58✔
274	}
275	else
276	{
277	notifier.OnCheckPerformed(new CheckEventArgs($"Could not find table {TableName} in database {Database}",	×
NEW 278	CheckResult.Fail));	×
279	}
280
281	var foundCohortDefinitionTable = DiscoverDefinitionTable();	58✔
282
283	if (foundCohortDefinitionTable.Exists())	58!
284	{
285	notifier.OnCheckPerformed(new CheckEventArgs(	58✔
286	$"Found table {DefinitionTableName} in database {Database}", CheckResult.Success));	58✔
287
288	var cols = foundCohortDefinitionTable.DiscoverColumns();	58✔
289
290	foreach (var requiredField in CohortDefinitionTable_RequiredFields)	696✔
291	ComplainIfColumnMissing(DefinitionTableName, cols, requiredField, notifier);	290✔
292	}
293	else
294	{
295	notifier.OnCheckPerformed(new CheckEventArgs(	×
NEW 296	$"Could not find table {DefinitionTableName} in database {Database}", CheckResult.Fail));	×
297	}
298	}	58✔
299	catch (Exception e)	×
300	{
301	notifier.OnCheckPerformed(new CheckEventArgs(	×
302	$"Could not check table intactness for ExternalCohortTable '{Name}'", CheckResult.Fail, e));	×
303	}	×
304	}	58✔
305
306	private void CheckCohortDatabaseAccessible(ICheckNotifier notifier)
307	{
308	try
309	{
310	DataAccessPortal.ExpectServer(this, DataAccessContext.DataExport).TestConnection();	60✔
311
312	notifier.OnCheckPerformed(new CheckEventArgs($"Connected to Cohort database '{Name}'", CheckResult.Success));	58✔
313	}	58✔
314	catch (Exception e)	2✔
315	{
316	notifier.OnCheckPerformed(new CheckEventArgs($"Could not connect to Cohort database called '{Name}'",	2✔
317	CheckResult.Fail, e));	2✔
318	}	×
319	}	58✔
320
321	/// <inheritdoc/>
322	public void PushToServer(ICohortDefinition newCohortDefinition, IManagedConnection connection)
323	{
324	newCohortDefinition.ID = DiscoverDefinitionTable().Insert(new Dictionary<string, object>	56✔
325	{	56✔
326	{ "projectNumber", newCohortDefinition.ProjectNumber },	56✔
327	{ "version", newCohortDefinition.Version },	56✔
328	{ "description", newCohortDefinition.Description }	56✔
329	}, connection.ManagedTransaction);	56✔
330	}	56✔
331
332	#endregion
333
334	private void ComplainIfColumnMissing(string tableNameFullyQualified, DiscoveredColumn[] columns,
335	string colToFindCanBeFullyQualifiedIfYouLike, ICheckNotifier notifier)
336	{
337	var tofind = GetQuerySyntaxHelper().GetRuntimeName(colToFindCanBeFullyQualifiedIfYouLike);	290✔
338
339	if (columns.Any(col => col.GetRuntimeName().Equals(tofind, StringComparison.OrdinalIgnoreCase)))	1,160!
340	notifier.OnCheckPerformed(new CheckEventArgs(	290✔
341	$"Found required field {tofind} in table {tableNameFullyQualified}",	290✔
342	CheckResult.Success));	290✔
343	else
344	notifier.OnCheckPerformed(new CheckEventArgs(	×
NEW 345	$"Could not find required field {tofind} in table {tableNameFullyQualified}(It had the following columns:{columns.Aggregate("", static (s, n) => $"{s}{n},")})",	×
NEW 346	CheckResult.Fail));	×
UNCOV 347	}	×
348
349
350	#region IDataAccessCredentials and IDataAccessPoint delegation
351
352	/// <inheritdoc/>
353	public string Password
354	{
355	get => SelfCertifyingDataAccessPoint.Password;	300✔
356	set
357	{
358	SelfCertifyingDataAccessPoint.Password = value;	718✔
359	OnPropertyChanged(null, value);	718✔
360	}	718✔
361	}
362
363	/// <inheritdoc/>
364	public string GetDecryptedPassword() => SelfCertifyingDataAccessPoint.GetDecryptedPassword() ?? "";	4!
365
366	/// <inheritdoc/>
367	public string Username
368	{
369	get => SelfCertifyingDataAccessPoint.Username;	306✔
370	set
371	{
372	if (Equals(SelfCertifyingDataAccessPoint.Username, value))	718✔
373	return;	704✔
374
375	var old = SelfCertifyingDataAccessPoint.Username;	14✔
376	SelfCertifyingDataAccessPoint.Username = value;	14✔
377	OnPropertyChanged(old, value);	14✔
378	}	14✔
379	}
380
381	/// <inheritdoc/>
382	public string Server
383	{
384	get => SelfCertifyingDataAccessPoint.Server;	1,720✔
385	set
386	{
387	if (Equals(SelfCertifyingDataAccessPoint.Server, value))	756✔
388	return;	214✔
389
390	var old = SelfCertifyingDataAccessPoint.Server;	542✔
391	SelfCertifyingDataAccessPoint.Server = value;	542✔
392	OnPropertyChanged(old, value);	542✔
393	}	542✔
394	}
395
396	/// <inheritdoc/>
397	public string Database
398	{
399	get => SelfCertifyingDataAccessPoint.Database;	8,724✔
400	set
401	{
402	if (Equals(SelfCertifyingDataAccessPoint.Database, value))	758✔
403	return;	8✔
404
405	var old = SelfCertifyingDataAccessPoint.Database;	750✔
406	SelfCertifyingDataAccessPoint.Database = value;	750✔
407	OnPropertyChanged(old, value);	750✔
408	}	750✔
409	}
410
411	/// <inheritdoc/>
412	public DatabaseType DatabaseType
413	{
414	get => SelfCertifyingDataAccessPoint.DatabaseType;	852✔
415	set
416	{
417	if (Equals(SelfCertifyingDataAccessPoint.DatabaseType, value))	128!
418	return;	128✔
419
420	var old = SelfCertifyingDataAccessPoint.DatabaseType;	×
421	SelfCertifyingDataAccessPoint.DatabaseType = value;	×
422	OnPropertyChanged(old, value);	×
423	}	×
424	}
425
426	/// <inheritdoc/>
427	public IDataAccessCredentials GetCredentialsIfExists(DataAccessContext context) =>
428	SelfCertifyingDataAccessPoint.GetCredentialsIfExists(context);	528✔
429
430	#endregion
431
432
433	/// <summary>
434	/// Returns SQL query for counting the number of unique patients in each cohort defined in the database
435	/// referenced by this <see cref="ExternalCohortTable"/>
436	/// </summary>
437	/// <returns></returns>
438	public string GetCountsDataTableSql()
439	{
440	var syntax = GetQuerySyntaxHelper();	×
441
442
443	return $@"SELECT	×
444	id as OriginID,	×
445	count(*) as Count,	×
446	count(distinct {ReleaseIdentifierField}) as CountDistinct,	×
447	{syntax.EnsureWrapped("projectNumber")} as {syntax.EnsureWrapped("ProjectNumber")},	×
448	version as {syntax.EnsureWrapped("Version")},	×
449	description as {syntax.EnsureWrapped("Description")},	×
450	{syntax.EnsureWrapped("dtCreated")}	×
451	FROM	×
452	{TableName}	×
453	join	×
454	{DefinitionTableName} on {DefinitionTableForeignKeyField} = id	×
455	group by	×
456	id,	×
457	{syntax.EnsureWrapped("projectNumber")},	×
458	version,	×
459	description,	×
460	{syntax.EnsureWrapped("dtCreated")}";	×
461	}
462
463	/// <summary>
464	/// Returns SQL query for listing all cohorts stored in the database referenced by this <see cref="ExternalCohortTable"/>.
465	/// This includes only the ids, project numbers, version, description etc not the actual patient identifiers themselves.
466	/// </summary>
467	/// <returns></returns>
468	public string GetExternalDataSql()
469	{
470	var syntax = GetQuerySyntaxHelper();	344✔
471
472	return $@"SELECT	344✔
473	id as {syntax.EnsureWrapped("OriginID")},	344✔
474	{syntax.EnsureWrapped("projectNumber")} as {syntax.EnsureWrapped("ProjectNumber")},	344✔
475	version as {syntax.EnsureWrapped("Version")},	344✔
476	description as {syntax.EnsureWrapped("Description")},	344✔
477	{syntax.EnsureWrapped("dtCreated")}	344✔
478	FROM	344✔
479	{DefinitionTableName}";	344✔
480	}
481
482	/// <summary>
483	/// Returns nothing
484	/// </summary>
485	/// <returns></returns>
486	public IHasDependencies[] GetObjectsThisDependsOn() => Array.Empty<IHasDependencies>();	×
487
488	/// <summary>
489	/// Returns all cohorts in the source
490	/// </summary>
491	/// <returns></returns>
492	public IHasDependencies[] GetObjectsDependingOnThis()
493	{
494	return (IHasDependencies[])Repository.GetAllObjects<ExtractableCohort>()	×
495	.Where(c => c.ExternalCohortTable_ID == ID);	×
496	}
497
498	/// <summary>
499	/// returns true if all the relevant fields are populated (table names, column names etc)
500	/// </summary>
501	/// <returns></returns>
502	public bool IsFullyPopulated() =>
503	!	344!
504	(string.IsNullOrWhiteSpace(TableName) \|\|	344✔
505	string.IsNullOrWhiteSpace(PrivateIdentifierField) \|\|	344✔
506	string.IsNullOrWhiteSpace(ReleaseIdentifierField) \|\|	344✔
507	string.IsNullOrWhiteSpace(DefinitionTableForeignKeyField) \|\|	344✔
508	string.IsNullOrWhiteSpace(DefinitionTableName));	344✔
509
510	public bool DiscoverExistence(DataAccessContext context, out string reason) =>
511	SelfCertifyingDataAccessPoint.DiscoverExistence(context, out reason);	×
512
513	public void SetRepository(ICatalogueRepository repository)
514	{
515	SelfCertifyingDataAccessPoint.SetRepository(repository);	×
516	}	×
517	}

HicServices / RDMP / 11978888584

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