| | | 1 | | using Elsa.Diagnostics.StructuredLogs.Models; |
| | | 2 | | using Elsa.Diagnostics.StructuredLogs.Persistence.Relational.Contracts; |
| | | 3 | | |
| | | 4 | | namespace Elsa.Diagnostics.StructuredLogs.Persistence.Relational.Services; |
| | | 5 | | |
| | | 6 | | public class RelationalStructuredLogSqlBuilder(IRelationalStructuredLogDialect dialect) |
| | | 7 | | { |
| | | 8 | | private readonly string _table = dialect.QuoteIdentifier("StructuredLogEvents"); |
| | | 9 | | |
| | | 10 | | public string BuildInsert() |
| | | 11 | | { |
| | | 12 | | var columns = Columns; |
| | | 13 | | var columnList = string.Join(", ", columns.Select(dialect.QuoteIdentifier)); |
| | | 14 | | var parameterList = string.Join(", ", columns.Select(x => $"{dialect.ParameterPrefix}{x}")); |
| | | 15 | | return $"INSERT INTO {_table} ({columnList}) VALUES ({parameterList})"; |
| | | 16 | | } |
| | | 17 | | |
| | | 18 | | public QueryDefinition BuildQuery(StructuredLogFilter filter) |
| | | 19 | | { |
| | | 20 | | var parameters = new Dictionary<string, object?>(); |
| | | 21 | | var predicates = BuildFilterPredicates(filter, parameters); |
| | | 22 | | var where = predicates.Count == 0 ? "" : $" WHERE {string.Join(" AND ", predicates)}"; |
| | | 23 | | var limit = Math.Clamp(filter.Take ?? 100, 0, 1000); |
| | | 24 | | var sql = $"SELECT {string.Join(", ", Columns.Select(dialect.QuoteIdentifier))} FROM {_table}{where} ORDER BY {d |
| | | 25 | | sql = dialect.ApplyLimit(sql, limit); |
| | | 26 | | return new(sql, parameters); |
| | | 27 | | } |
| | | 28 | | |
| | | 29 | | public string BuildListSources() |
| | | 30 | | { |
| | | 31 | | var sourceId = dialect.QuoteIdentifier("SourceId"); |
| | | 32 | | var receivedAt = dialect.QuoteIdentifier("ReceivedAt"); |
| | | 33 | | return $"SELECT {sourceId}, MAX({receivedAt}) AS {dialect.QuoteIdentifier("LastSeen")} FROM {_table} GROUP BY {s |
| | | 34 | | } |
| | | 35 | | |
| | | 36 | | public QueryDefinition BuildDeleteOlderThan(string cutoff) |
| | | 37 | | { |
| | | 38 | | return new($"DELETE FROM {_table} WHERE {dialect.QuoteIdentifier("ReceivedAt")} < {dialect.ParameterPrefix}Cutof |
| | | 39 | | } |
| | | 40 | | |
| | | 41 | | public QueryDefinition BuildDeleteRowsBeyondMax(int maxRows) |
| | | 42 | | { |
| | | 43 | | var id = dialect.QuoteIdentifier("Id"); |
| | | 44 | | var receivedAt = dialect.QuoteIdentifier("ReceivedAt"); |
| | | 45 | | var sequence = dialect.QuoteIdentifier("Sequence"); |
| | | 46 | | var selectSql = $"SELECT {id} FROM {_table} ORDER BY {receivedAt} DESC, {sequence} DESC, {id} DESC"; |
| | | 47 | | var sql = $"DELETE FROM {_table} WHERE {id} IN ({dialect.ApplyOffset(selectSql, maxRows)})"; |
| | | 48 | | return new(sql, new Dictionary<string, object?>()); |
| | | 49 | | } |
| | | 50 | | |
| | | 51 | | private List<string> BuildFilterPredicates(StructuredLogFilter filter, IDictionary<string, object?> parameters) |
| | | 52 | | { |
| | | 53 | | var predicates = new List<string>(); |
| | | 54 | | |
| | | 55 | | if (filter.MinimumLevel is { } minimumLevel) |
| | | 56 | | AddPredicate(predicates, parameters, "Level", ">=", (int)minimumLevel); |
| | | 57 | | |
| | | 58 | | if (filter.Levels is { Count: > 0 }) |
| | | 59 | | { |
| | | 60 | | var names = filter.Levels.Select((level, index) => |
| | | 61 | | { |
| | | 62 | | var name = $"Level{index}"; |
| | | 63 | | parameters[name] = (int)level; |
| | | 64 | | return $"{dialect.ParameterPrefix}{name}"; |
| | | 65 | | }); |
| | | 66 | | predicates.Add($"{dialect.QuoteIdentifier("Level")} IN ({string.Join(", ", names)})"); |
| | | 67 | | } |
| | | 68 | | |
| | | 69 | | if (!string.IsNullOrWhiteSpace(filter.CategoryPrefix)) |
| | | 70 | | AddPredicate(predicates, parameters, "Category", "LIKE", $"{filter.CategoryPrefix}%"); |
| | | 71 | | |
| | | 72 | | if (!string.IsNullOrWhiteSpace(filter.Text)) |
| | | 73 | | { |
| | | 74 | | parameters["Text"] = $"%{filter.Text}%"; |
| | | 75 | | var textParameter = $"{dialect.ParameterPrefix}Text"; |
| | | 76 | | predicates.Add($"({dialect.QuoteIdentifier("Message")} LIKE {textParameter} OR {dialect.QuoteIdentifier("Mes |
| | | 77 | | } |
| | | 78 | | |
| | | 79 | | AddStringPredicate(predicates, parameters, "TenantId", filter.TenantId); |
| | | 80 | | AddStringPredicate(predicates, parameters, "WorkflowDefinitionId", filter.WorkflowDefinitionId); |
| | | 81 | | AddStringPredicate(predicates, parameters, "WorkflowInstanceId", filter.WorkflowInstanceId); |
| | | 82 | | AddStringPredicate(predicates, parameters, "TraceId", filter.TraceId); |
| | | 83 | | AddStringPredicate(predicates, parameters, "SpanId", filter.SpanId); |
| | | 84 | | AddStringPredicate(predicates, parameters, "CorrelationId", filter.CorrelationId); |
| | | 85 | | AddStringPredicate(predicates, parameters, "SourceId", filter.SourceId); |
| | | 86 | | |
| | | 87 | | if (filter.From is { } from) |
| | | 88 | | AddPredicate(predicates, parameters, "Timestamp", ">=", RelationalStructuredLogMapper.FormatTimestamp(from), |
| | | 89 | | |
| | | 90 | | if (filter.To is { } to) |
| | | 91 | | AddPredicate(predicates, parameters, "Timestamp", "<=", RelationalStructuredLogMapper.FormatTimestamp(to), " |
| | | 92 | | |
| | | 93 | | return predicates; |
| | | 94 | | } |
| | | 95 | | |
| | | 96 | | private void AddStringPredicate(ICollection<string> predicates, IDictionary<string, object?> parameters, string colu |
| | | 97 | | { |
| | | 98 | | if (string.IsNullOrWhiteSpace(value)) |
| | | 99 | | return; |
| | | 100 | | |
| | | 101 | | AddPredicate(predicates, parameters, column, "=", value); |
| | | 102 | | } |
| | | 103 | | |
| | | 104 | | private void AddPredicate(ICollection<string> predicates, IDictionary<string, object?> parameters, string column, st |
| | | 105 | | { |
| | | 106 | | var name = parameterName ?? column; |
| | | 107 | | predicates.Add($"{dialect.QuoteIdentifier(column)} {op} {dialect.ParameterPrefix}{name}"); |
| | | 108 | | parameters[name] = value; |
| | | 109 | | } |
| | | 110 | | |
| | | 111 | | private static readonly string[] Columns = |
| | | 112 | | [ |
| | | 113 | | "Id", |
| | | 114 | | "Sequence", |
| | | 115 | | "Timestamp", |
| | | 116 | | "ReceivedAt", |
| | | 117 | | "Level", |
| | | 118 | | "Category", |
| | | 119 | | "EventId", |
| | | 120 | | "EventName", |
| | | 121 | | "Message", |
| | | 122 | | "MessageTemplate", |
| | | 123 | | "ExceptionJson", |
| | | 124 | | "ScopesJson", |
| | | 125 | | "PropertiesJson", |
| | | 126 | | "TraceId", |
| | | 127 | | "SpanId", |
| | | 128 | | "CorrelationId", |
| | | 129 | | "TenantId", |
| | | 130 | | "WorkflowDefinitionId", |
| | | 131 | | "WorkflowInstanceId", |
| | | 132 | | "SourceId" |
| | | 133 | | ]; |
| | | 134 | | } |
| | | 135 | | |
| | 45 | 136 | | public record QueryDefinition(string Sql, IReadOnlyDictionary<string, object?> Parameters); |