/// <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) { 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) { 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}'"); }