public static int DeleteFromTable(YearDatabase database, string schemaName, string tableName)
{
using (var connection = ConnectionStringHelper.GetDbConnection(database)) {
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = $@"DELETE FROM {schemaName}.""{tableName}""";
return(cmd.ExecuteNonQuery());
}
}
public static Dictionary <ContinuityProperty, bool?> ReportOnTable(YearDatabase database, string schema, string table, List <string> keys)
{
return((new List <TableProperty>()
{
CheckNonEmpty(database, schema, table),
CheckNoOverlapingIntervals(database, schema, table, keys),
CheckUniqueCurrentValue(database, schema, table, keys),
}).ToDictionary(x => x.Name, x => x.Value));
}
public static TableProperty CheckNonEmpty(YearDatabase database, string schemaName, string tableName)
{
using (var connection = ConnectionStringHelper.GetDbConnection(database)) {
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = $@"
SELECT
COUNT(*)
FROM
{schemaName}.""{tableName}""";
using (var reader = cmd.ExecuteReader()) {
reader.Read();
return(new TableProperty(ContinuityProperty.NotEmpty, reader.GetInt64(0) > 0));
}
}
}
public static bool?AssertEmptyResult(YearDatabase database, string commandText)
{
using (var connection = ConnectionStringHelper.GetDbConnection(database)) {
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = $@"
SELECT
COUNT(*)
FROM
({commandText}) AS t
";
using (var reader = cmd.ExecuteReader()) {
reader.Read();
return(reader.GetInt64(0) == 0);
}
}
}
public static TableProperty CheckNoOverlapingIntervals(YearDatabase database, string schemaName, string tableName, List <string> keyColumnNames)
{
var fullKey = GetKeyStatement(keyColumnNames);
var keyNotTime = GetKeyWithoutTimeColumn(keyColumnNames);
var commandText = $@"
SELECT
*
FROM (
SELECT
{fullKey},
""ValidityStart"" AS validityStartValue,
LAG(""ValidityEnd"") OVER (PARTITION BY {keyNotTime} ORDER BY ""ValidityStart"" ASC) AS previousValidityEnd,
row_number() OVER (PARTITION BY {keyNotTime} ORDER BY ""ValidityStart"" ASC) AS valueIndex
FROM {schemaName}.""{tableName}"" ) AS w
WHERE w.valueIndex > 1
AND validityStartValue <> previousValidityEnd";
return(new TableProperty(ContinuityProperty.NoOverlapingInterval, AssertEmptyResult(database, commandText)));
}
public static TableProperty CheckUniqueCurrentValue(YearDatabase database, string schemaName, string tableName, List <string> keyColumnNames)
{
var fullKey = string.Join(",", keyColumnNames.Select(x => $"\"{x}\""));
var keyNotTime = string.Join(",", keyColumnNames.Where(x => x != "ValidityStart").Select(x => $"\"{x}\""));
var commandText = $@"
SELECT
*
FROM
(SELECT
{keyNotTime},
SUM(CASE WHEN NOW() >= ""ValidityStart"" AND NOW() < ""ValidityEnd"" THEN 1 ELSE 0 END) AS current_count
FROM
{schemaName}. ""{tableName}"" AS w
GROUP BY
{keyNotTime}) AS a
WHERE a.current_count <> 1
";
return(new TableProperty(ContinuityProperty.UniqueCurrentValue, AssertEmptyResult(database, commandText)));
}
public static NpgsqlConnection GetDbConnection(YearDatabase database)
{
return(GetDbConnection(database.ToString()));
}