/// <summary>
/// Delete the Messages Entities that are inserted passed a given <paramref name="retentionPeriod"/>
/// and has a <see cref="Operation"/> within the given <paramref name="allowedOperations"/>.
/// </summary>
/// <param name="tableName"></param>
/// <param name="retentionPeriod">The retention period.</param>
/// <param name="allowedOperations">The allowed operations.</param>
public void BatchDeleteOverRetentionPeriod(
string tableName,
TimeSpan retentionPeriod,
IEnumerable <Operation> allowedOperations)
{
DatastoreTable.EnsureTableNameIsKnown(tableName);
string operations = string.Join(", ", allowedOperations.Select(x => "'" + x.ToString() + "'"));
string outMessagesWhere =
tableName.Equals("OutMessages")
? @" AND (
(m.EbmsMessageType = 'UserMessage' AND m.Status IN('Ack', 'Nack'))
OR m.EbmsMessageType IN('Receipt', 'Error')
)"
: string.Empty;
string sql =
$"DELETE m FROM {tableName} m " +
$"WHERE m.InsertionTime < GETDATE() - {retentionPeriod.TotalDays:##.##} " +
$"AND Operation IN ({operations})" +
outMessagesWhere;
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
// The DatastoreTable makes sure that we only use known table names.
// The list of Operation enums makes sure that only use Operation values.
// The TotalDays of the TimeSpan is an integer.
int rows = _context.Database.ExecuteSqlCommand(sql);
#pragma warning restore EF1000 // Possible SQL injection vulnerability.
LogManager.GetCurrentClassLogger().Trace($"Cleaned {rows} row(s) for table '{tableName}'");
}
/// <summary>
/// Exclusively retrieves the entities.
/// </summary>
/// <param name="tableName">Name of the Db table.</param>
/// <param name="filter">Order by this field.</param>
/// <param name="takeRows">Take this amount of rows.</param>
/// <returns></returns>
public IEnumerable <Entity> ExclusivelyRetrieveEntities(string tableName, string filter, int takeRows)
{
string filterExpression = filter.Replace("\'", "\"");
return(DatastoreTable.FromTableName(tableName)(_context)
.Where(filterExpression)
.ToList());
}
/// <summary>
/// Exclusively retrieves the entities.
/// </summary>
/// <param name="tableName">Name of the Db table.</param>
/// <param name="filter">Order by this field.</param>
/// <param name="takeRows">Take this amount of rows.</param>
/// <returns></returns>
public IEnumerable <Entity> ExclusivelyRetrieveEntities(string tableName, string filter, int takeRows)
{
if (!(DatastoreTable.IsTableNameKnown(tableName) && _tablesByName.ContainsKey(tableName)))
{
throw new ConfigurationErrorsException($"The configured table {tableName} could not be found");
}
return(_tablesByName[tableName](_context)
.Where(filter.Replace("\'", "\""))
.OrderBy(x => x.InsertionTime)
.Take(takeRows)
.ToList());
}
/// <summary>
/// Exclusively retrieves the entities.
/// </summary>
/// <param name="tableName">Name of the Db table.</param>
/// <param name="filter">Order by this field.</param>
/// <param name="takeRows">Take this amount of rows.</param>
/// <returns></returns>
public IEnumerable <Entity> ExclusivelyRetrieveEntities(string tableName, string filter, int takeRows)
{
DatastoreTable.EnsureTableNameIsKnown(tableName);
string filterExpression = filter.Replace("\'", "\"");
return(DatastoreTable
.FromTableName(tableName)(_context)
.Where(filterExpression)
.OrderBy(e => e.InsertionTime)
.Take(takeRows)
.ToList());
}
/// <summary>
/// Delete the Messages Entities that are inserted passed a given <paramref name="retentionPeriod"/>
/// and has a <see cref="Operation"/> within the given <paramref name="allowedOperations"/>.
/// </summary>
/// <param name="tableName"></param>
/// <param name="retentionPeriod">The retention period.</param>
/// <param name="allowedOperations">The allowed operations.</param>
public void BatchDeleteOverRetentionPeriod(
string tableName,
TimeSpan retentionPeriod,
IEnumerable <Operation> allowedOperations)
{
IQueryable <Entity> entities =
DatastoreTable.FromTableName(tableName)(_context)
.Cast <Entity>()
.Where(x => x.InsertionTime < DateTimeOffset.Now.Subtract(retentionPeriod) &&
allowedOperations.Contains(GetOperation[tableName](x)));
_context.RemoveRange(entities);
_context.SaveChanges();
}
/// <summary>
/// Delete the Messages Entities that are inserted passed a given <paramref name="retentionPeriod"/>
/// and has a <see cref="Operation"/> within the given <paramref name="allowedOperations"/>.
/// </summary>
/// <param name="tableName"></param>
/// <param name="retentionPeriod">The retention period.</param>
/// <param name="allowedOperations">The allowed operations.</param>
public void BatchDeleteOverRetentionPeriod(
string tableName,
TimeSpan retentionPeriod,
IEnumerable <Operation> allowedOperations)
{
DatastoreTable.EnsureTableNameIsKnown(tableName);
string operations = string.Join(", ", allowedOperations.Select(x => "'" + x.ToString() + "'"));
string outMessagesWhere =
tableName.Equals("OutMessages")
? @" AND (
(EbmsMessageType = 'UserMessage' AND Status IN('Ack', 'Nack'))
OR EbmsMessageType IN('Receipt', 'Error')
)"
: string.Empty;
// Sqlite doesn't allow JOIN statements in DELETE statements
string retrySql =
"DELETE FROM RetryReliability "
+ "WHERE Id IN ("
+ $"SELECT m.Id FROM {tableName} m "
+ $"WHERE m.InsertionTime<datetime('now', '-{retentionPeriod.TotalDays} day') "
+ $"AND m.Operation IN ({operations}) "
+ $"{outMessagesWhere})";
string entitySql =
$"DELETE FROM {tableName} " +
$"WHERE InsertionTime<datetime('now', '-{retentionPeriod.TotalDays} day') " +
$"AND Operation IN ({operations}) " +
outMessagesWhere;
#pragma warning disable EF1000 // Possible SQL injection vulnerability:
// The DatastoreTable makes sure that we only use known table names.
// The list of Operation enums makes sure that only use Operation values.
// The TotalDays of the TimeSpan is an integer.
int retryRows = _context.Database.ExecuteSqlCommand(retrySql);
int entityRows = _context.Database.ExecuteSqlCommand(entitySql);
#pragma warning restore EF1000 // Possible SQL injection vulnerability.
Logger.Trace($"Cleaned {retryRows} row(s) for table 'RetryReliability'");
Logger.Trace($"Cleaned {entityRows} row(s) for table '{tableName}'");
}