26566925905

Committed 28 May 2026 09:37AM UTC coverage: 57.023% (-0.08%) from 57.101%

Build # 26566925905

Build Type

push

github

Committed by

JFriel

Commit Message

Merge branch 'bugfix/cohort-commit-issue' of https://github.com/HicServices/RDMP into bugfix/cohort-commit-issue

Coverage Stats

11562 of 21813 branches covered (53.01%)

Branch coverage included in aggregate %.

32688 of 55787 relevant lines covered (58.59%)

18057.84 hits per line

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

0.0

/Rdmp.Core/DataViewing/ViewColumnExtractCollection.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.Linq;
using FAnsi;
using FAnsi.Discovery.QuerySyntax;
using Rdmp.Core.Curation.Data;
using Rdmp.Core.Curation.Data.Dashboarding;
using Rdmp.Core.Curation.Data.Spontaneous;
using Rdmp.Core.MapsDirectlyToDatabaseTable;
using Rdmp.Core.QueryBuilding;
using Rdmp.Core.Repositories;
using Rdmp.Core.ReusableLibraryCode.DataAccess;

namespace Rdmp.Core.DataViewing;

/// <summary>
/// Builds a query to fetch data in a <see cref="ColumnInfo"/> (Based on the <see cref="ViewType"/>)
/// </summary>
public class ViewColumnExtractCollection : PersistableObjectCollection, IViewSQLAndResultsCollection
{
    public ViewType ViewType { get; private set; }
    public bool DisableRun => false;

    /// <summary>
    /// The SELECT column (can be null if this instance was constructed using a <see cref="ColumnInfo"/>)
    /// </summary>
    public ExtractionInformation ExtractionInformation =>
        DatabaseObjects.OfType<ExtractionInformation>().SingleOrDefault();

    /// <summary>
    /// The SELECT column (can be null if this instance was constructed using a <see cref="ExtractionInformation"/>)
    /// </summary>
    public ColumnInfo ColumnInfo => DatabaseObjects.OfType<ColumnInfo>().SingleOrDefault();


    #region Constructors

    /// <summary>
    /// for persistence, do not use
    /// </summary>
    public ViewColumnExtractCollection()
    {
    }

    public ViewColumnExtractCollection(ColumnInfo c, ViewType viewType, IFilter filter = null) : this()
    {
        DatabaseObjects.Add(c);
        if (filter != null)
            DatabaseObjects.Add(filter);
        ViewType = viewType;
    }

    public ViewColumnExtractCollection(ColumnInfo c, ViewType viewType, IContainer container) : this()
    {
        DatabaseObjects.Add(c);
        if (container != null)
            DatabaseObjects.Add(container);
        ViewType = viewType;
    }

    public ViewColumnExtractCollection(ExtractionInformation ei, ViewType viewType, IFilter filter = null) : this()
    {
        DatabaseObjects.Add(ei);
        if (filter != null)
            DatabaseObjects.Add(filter);
        ViewType = viewType;
    }

    public ViewColumnExtractCollection(ExtractionInformation ei, ViewType viewType, IContainer container) : this()
    {
        DatabaseObjects.Add(ei);
        if (container != null)
            DatabaseObjects.Add(container);
        ViewType = viewType;
    }

    #endregion

    public override string SaveExtraText() => PersistStringHelper.SaveDictionaryToString(new Dictionary<string, string>
        { { "ViewType", ViewType.ToString() } });

    public override void LoadExtraText(string s)
    {
        var value = PersistStringHelper.GetValueIfExistsFromPersistString("ViewType", s);
        ViewType = (ViewType)Enum.Parse(typeof(ViewType), value);
    }

    public IEnumerable<DatabaseEntity> GetToolStripObjects()
    {
        if (GetFilterIfAny() is ConcreteFilter f)
            yield return f;

        if (GetContainerIfAny() is ConcreteContainer c)
            yield return c;

        yield return GetTableInfo() as TableInfo;
    }

    public IDataAccessPoint GetDataAccessPoint() => GetTableInfo();

    private ITableInfo GetTableInfo() => ExtractionInformation != null
        ? ExtractionInformation.ColumnInfo?.TableInfo
        : (ITableInfo)ColumnInfo?.TableInfo;

    public string GetSql()
    {
        var qb = new QueryBuilder(null, null, new[] { GetTableInfo() });

        if (ViewType == ViewType.TOP_100)
            qb.TopX = 100;

        if (ViewType == ViewType.Distribution)
            AddDistributionColumns(qb);
        else
            qb.AddColumn(GetIColumn());

        var filter = GetFilterIfAny();
        var container = GetContainerIfAny();

        if (filter != null && container != null)
            throw new Exception("Cannot generate SQL with both filter and container");

        if (filter != null && !string.IsNullOrWhiteSpace(filter.WhereSQL))
            qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(new MemoryCatalogueRepository(), null,
                new[] { filter }, FilterContainerOperation.AND);
        else if (container != null) qb.RootFilterContainer = container;

        if (ViewType == ViewType.Aggregate)
            qb.AddCustomLine("count(*) as Count,", QueryComponent.QueryTimeColumn);

        var sql = qb.SQL;

        if (ViewType == ViewType.Aggregate)
            sql += $"{Environment.NewLine} GROUP BY {GetColumnSelectSql()}";

        if (ViewType == ViewType.Aggregate)
            sql += $"{Environment.NewLine} ORDER BY count(*) DESC";

        return sql;
    }

    private IColumn GetIColumn()
    {
        if (ExtractionInformation != null) return ExtractionInformation;
        return ColumnInfo != null ? new ColumnInfoToIColumn(new MemoryRepository(), ColumnInfo) : (IColumn)null;
    }

    private void AddDistributionColumns(QueryBuilder qb)
    {
        var repo = new MemoryRepository();
        qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountTotal", "count(1)"));
        qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountNull",
            $"SUM(CASE WHEN {GetColumnSelectSql()} IS NULL THEN 1 ELSE 0  END)"));
        qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountZero",
            $"SUM(CASE WHEN {GetColumnSelectSql()} = 0 THEN 1  ELSE 0 END)"));

        qb.AddColumn(new SpontaneouslyInventedColumn(repo, "Max", $"max({GetColumnSelectSql()})"));
        qb.AddColumn(new SpontaneouslyInventedColumn(repo, "Min", $"min({GetColumnSelectSql()})"));

        switch (ColumnInfo.GetQuerySyntaxHelper().DatabaseType)
        {
            case DatabaseType.MicrosoftSQLServer:
                qb.AddColumn(new SpontaneouslyInventedColumn(repo, "stdev ", $"stdev({GetColumnSelectSql()})"));
                break;
            case DatabaseType.MySql:
            case DatabaseType.Oracle:
                qb.AddColumn(new SpontaneouslyInventedColumn(repo, "stddev ", $"stddev({GetColumnSelectSql()})"));
                break;
            default:
                throw new ArgumentOutOfRangeException();
        }

        qb.AddColumn(new SpontaneouslyInventedColumn(repo, "avg", $"avg({GetColumnSelectSql()})"));
    }

    /// <summary>
    /// Returns the column Select SQL (without alias) for use in query building
    /// </summary>
    /// <returns></returns>
    private string GetColumnSelectSql() => GetIColumn().SelectSQL;

    public string GetTabName() => $"{GetIColumn()}({ViewType})";

    public void AdjustAutocomplete(IAutoCompleteProvider autoComplete)
    {
        if (ColumnInfo != null) autoComplete.Add(ColumnInfo);
    }

    private IFilter GetFilterIfAny()
    {
        return (IFilter)DatabaseObjects.SingleOrDefault(o => o is IFilter);
    }

    private IContainer GetContainerIfAny()
    {
        return (IContainer)DatabaseObjects.SingleOrDefault(o => o is IContainer);
    }


    public IQuerySyntaxHelper GetQuerySyntaxHelper()
    {
        var c = ColumnInfo;
        return c?.GetQuerySyntaxHelper();
    }
}

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.Linq;
10	using FAnsi;
11	using FAnsi.Discovery.QuerySyntax;
12	using Rdmp.Core.Curation.Data;
13	using Rdmp.Core.Curation.Data.Dashboarding;
14	using Rdmp.Core.Curation.Data.Spontaneous;
15	using Rdmp.Core.MapsDirectlyToDatabaseTable;
16	using Rdmp.Core.QueryBuilding;
17	using Rdmp.Core.Repositories;
18	using Rdmp.Core.ReusableLibraryCode.DataAccess;
19
20	namespace Rdmp.Core.DataViewing;
21
22	/// <summary>
23	/// Builds a query to fetch data in a <see cref="ColumnInfo"/> (Based on the <see cref="ViewType"/>)
24	/// </summary>
25	public class ViewColumnExtractCollection : PersistableObjectCollection, IViewSQLAndResultsCollection
26	{
27	public ViewType ViewType { get; private set; }	×
28	public bool DisableRun => false;	×
29
30	/// <summary>
31	/// The SELECT column (can be null if this instance was constructed using a <see cref="ColumnInfo"/>)
32	/// </summary>
33	public ExtractionInformation ExtractionInformation =>
34	DatabaseObjects.OfType<ExtractionInformation>().SingleOrDefault();	×
35
36	/// <summary>
37	/// The SELECT column (can be null if this instance was constructed using a <see cref="ExtractionInformation"/>)
38	/// </summary>
39	public ColumnInfo ColumnInfo => DatabaseObjects.OfType<ColumnInfo>().SingleOrDefault();	×
40
41
42	#region Constructors
43
44	/// <summary>
45	/// for persistence, do not use
46	/// </summary>
47	public ViewColumnExtractCollection()	×
48	{
49	}	×
50
51	public ViewColumnExtractCollection(ColumnInfo c, ViewType viewType, IFilter filter = null) : this()	×
52	{
53	DatabaseObjects.Add(c);	×
54	if (filter != null)	×
55	DatabaseObjects.Add(filter);	×
56	ViewType = viewType;	×
57	}	×
58
59	public ViewColumnExtractCollection(ColumnInfo c, ViewType viewType, IContainer container) : this()	×
60	{
61	DatabaseObjects.Add(c);	×
62	if (container != null)	×
63	DatabaseObjects.Add(container);	×
64	ViewType = viewType;	×
65	}	×
66
67	public ViewColumnExtractCollection(ExtractionInformation ei, ViewType viewType, IFilter filter = null) : this()	×
68	{
69	DatabaseObjects.Add(ei);	×
70	if (filter != null)	×
71	DatabaseObjects.Add(filter);	×
72	ViewType = viewType;	×
73	}	×
74
75	public ViewColumnExtractCollection(ExtractionInformation ei, ViewType viewType, IContainer container) : this()	×
76	{
77	DatabaseObjects.Add(ei);	×
78	if (container != null)	×
79	DatabaseObjects.Add(container);	×
80	ViewType = viewType;	×
81	}	×
82
83	#endregion
84
85	public override string SaveExtraText() => PersistStringHelper.SaveDictionaryToString(new Dictionary<string, string>	×
86	{ { "ViewType", ViewType.ToString() } });	×
87
88	public override void LoadExtraText(string s)
89	{
90	var value = PersistStringHelper.GetValueIfExistsFromPersistString("ViewType", s);	×
91	ViewType = (ViewType)Enum.Parse(typeof(ViewType), value);	×
92	}	×
93
94	public IEnumerable<DatabaseEntity> GetToolStripObjects()
95	{
96	if (GetFilterIfAny() is ConcreteFilter f)	×
97	yield return f;	×
98
99	if (GetContainerIfAny() is ConcreteContainer c)	×
100	yield return c;	×
101
102	yield return GetTableInfo() as TableInfo;	×
103	}	×
104
105	public IDataAccessPoint GetDataAccessPoint() => GetTableInfo();	×
106
107	private ITableInfo GetTableInfo() => ExtractionInformation != null	×
108	? ExtractionInformation.ColumnInfo?.TableInfo	×
109	: (ITableInfo)ColumnInfo?.TableInfo;	×
110
111	public string GetSql()
112	{
113	var qb = new QueryBuilder(null, null, new[] { GetTableInfo() });	×
114
115	if (ViewType == ViewType.TOP_100)	×
116	qb.TopX = 100;	×
117
118	if (ViewType == ViewType.Distribution)	×
119	AddDistributionColumns(qb);	×
120	else
121	qb.AddColumn(GetIColumn());	×
122
123	var filter = GetFilterIfAny();	×
124	var container = GetContainerIfAny();	×
125
126	if (filter != null && container != null)	×
127	throw new Exception("Cannot generate SQL with both filter and container");	×
128
129	if (filter != null && !string.IsNullOrWhiteSpace(filter.WhereSQL))	×
130	qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(new MemoryCatalogueRepository(), null,	×
131	new[] { filter }, FilterContainerOperation.AND);	×
132	else if (container != null) qb.RootFilterContainer = container;	×
133
134	if (ViewType == ViewType.Aggregate)	×
135	qb.AddCustomLine("count(*) as Count,", QueryComponent.QueryTimeColumn);	×
136
137	var sql = qb.SQL;	×
138
139	if (ViewType == ViewType.Aggregate)	×
140	sql += $"{Environment.NewLine} GROUP BY {GetColumnSelectSql()}";	×
141
142	if (ViewType == ViewType.Aggregate)	×
143	sql += $"{Environment.NewLine} ORDER BY count(*) DESC";	×
144
145	return sql;	×
146	}
147
148	private IColumn GetIColumn()
149	{
150	if (ExtractionInformation != null) return ExtractionInformation;	×
151	return ColumnInfo != null ? new ColumnInfoToIColumn(new MemoryRepository(), ColumnInfo) : (IColumn)null;	×
152	}
153
154	private void AddDistributionColumns(QueryBuilder qb)
155	{
156	var repo = new MemoryRepository();	×
157	qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountTotal", "count(1)"));	×
158	qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountNull",	×
159	$"SUM(CASE WHEN {GetColumnSelectSql()} IS NULL THEN 1 ELSE 0 END)"));	×
160	qb.AddColumn(new SpontaneouslyInventedColumn(repo, "CountZero",	×
161	$"SUM(CASE WHEN {GetColumnSelectSql()} = 0 THEN 1 ELSE 0 END)"));	×
162
163	qb.AddColumn(new SpontaneouslyInventedColumn(repo, "Max", $"max({GetColumnSelectSql()})"));	×
164	qb.AddColumn(new SpontaneouslyInventedColumn(repo, "Min", $"min({GetColumnSelectSql()})"));	×
165
166	switch (ColumnInfo.GetQuerySyntaxHelper().DatabaseType)	×
167	{
168	case DatabaseType.MicrosoftSQLServer:
169	qb.AddColumn(new SpontaneouslyInventedColumn(repo, "stdev ", $"stdev({GetColumnSelectSql()})"));	×
170	break;	×
171	case DatabaseType.MySql:
172	case DatabaseType.Oracle:
173	qb.AddColumn(new SpontaneouslyInventedColumn(repo, "stddev ", $"stddev({GetColumnSelectSql()})"));	×
174	break;	×
175	default:
176	throw new ArgumentOutOfRangeException();	×
177	}
178
179	qb.AddColumn(new SpontaneouslyInventedColumn(repo, "avg", $"avg({GetColumnSelectSql()})"));	×
180	}	×
181
182	/// <summary>
183	/// Returns the column Select SQL (without alias) for use in query building
184	/// </summary>
185	/// <returns></returns>
186	private string GetColumnSelectSql() => GetIColumn().SelectSQL;	×
187
188	public string GetTabName() => $"{GetIColumn()}({ViewType})";	×
189
190	public void AdjustAutocomplete(IAutoCompleteProvider autoComplete)
191	{
192	if (ColumnInfo != null) autoComplete.Add(ColumnInfo);	×
193	}	×
194
195	private IFilter GetFilterIfAny()
196	{
197	return (IFilter)DatabaseObjects.SingleOrDefault(o => o is IFilter);	×
198	}
199
200	private IContainer GetContainerIfAny()
201	{
202	return (IContainer)DatabaseObjects.SingleOrDefault(o => o is IContainer);	×
203	}
204
205
206	public IQuerySyntaxHelper GetQuerySyntaxHelper()
207	{
208	var c = ColumnInfo;	×
209	return c?.GetQuerySyntaxHelper();	×
210	}
211	}

HicServices / RDMP / 26566925905

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