| 43 | } |
| 44 | |
| 45 | func (s *AccessLogStore) List(params accesslogs.ListParams) ([]accesslogs.Entry, error) { |
| 46 | ctx := context.Background() |
| 47 | where := []string{"1=1"} |
| 48 | args := []any{} |
| 49 | idx := 1 |
| 50 | |
| 51 | if params.NodeID != "" { |
| 52 | where = append(where, fmt.Sprintf("node_id = $%d", idx)) |
| 53 | args = append(args, params.NodeID) |
| 54 | idx++ |
| 55 | } |
| 56 | if params.Username != "" { |
| 57 | where = append(where, fmt.Sprintf("SPLIT_PART(username, '@', 1) = $%d", idx)) |
| 58 | args = append(args, params.Username) |
| 59 | idx++ |
| 60 | } |
| 61 | if !params.Since.IsZero() { |
| 62 | where = append(where, fmt.Sprintf("created_at >= $%d", idx)) |
| 63 | args = append(args, params.Since) |
| 64 | idx++ |
| 65 | } |
| 66 | if !params.Until.IsZero() { |
| 67 | where = append(where, fmt.Sprintf("created_at <= $%d", idx)) |
| 68 | args = append(args, params.Until) |
| 69 | idx++ |
| 70 | } |
| 71 | |
| 72 | limit := 0 |
| 73 | if params.Limit > 0 { |
| 74 | limit = params.Limit |
| 75 | } |
| 76 | |
| 77 | const selectCols = `id,node_id,username,source_ip,source_port,destination,remote_ip,route_tag,protocol,inbound_tag,created_at` |
| 78 | var query string |
| 79 | if limit > 0 { |
| 80 | query = fmt.Sprintf( |
| 81 | `SELECT %s FROM access_logs WHERE %s ORDER BY created_at DESC LIMIT %d OFFSET %d`, |
| 82 | selectCols, strings.Join(where, " AND "), limit, params.Offset, |
| 83 | ) |
| 84 | } else { |
| 85 | query = fmt.Sprintf( |
| 86 | `SELECT %s FROM access_logs WHERE %s ORDER BY created_at DESC OFFSET %d`, |
| 87 | selectCols, strings.Join(where, " AND "), params.Offset, |
| 88 | ) |
| 89 | } |
| 90 | |
| 91 | rows, err := s.db.Query(ctx, query, args...) |
| 92 | if err != nil { |
| 93 | return nil, fmt.Errorf("list access logs: %w", err) |
| 94 | } |
| 95 | defer rows.Close() |
| 96 | |
| 97 | var out []accesslogs.Entry |
| 98 | for rows.Next() { |
| 99 | var e accesslogs.Entry |
| 100 | if err := rows.Scan( |
| 101 | &e.ID, &e.NodeID, &e.Username, &e.SourceIP, &e.SourcePort, |
| 102 | &e.Destination, &e.RemoteIP, &e.RouteTag, &e.Protocol, &e.InboundTag, &e.CreatedAt, |