-
Notifications
You must be signed in to change notification settings - Fork 3
/
events.go
136 lines (119 loc) · 3.9 KB
/
events.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
/*
Copyright 2020 Adevinta
*/
package postgresql
import (
"fmt"
"github.com/adevinta/vulnerability-db-api/pkg/model"
"github.com/adevinta/vulnerability-db-api/pkg/storage"
)
// ListFindingEvents returns a filtered list of finding events.
func (db DB) ListFindingEvents(filter storage.Filter) ([]model.FindingEvent, storage.Pagination, error) {
offset := storage.Offset(filter.Page, filter.Size)
listQuery, err := filterTemplate(`
SELECT * FROM finding_events
WHERE 1=1
{{ if .MinScore }} AND score >= :minScore {{ end }}
{{ if .MaxScore }} AND score <= :maxScore {{ end }}
{{ if .MinDate }} AND time >= :minDate {{ end }}
{{ if .MaxDate }} AND time <= :mxDateUpBound {{ end }}
LIMIT :limit OFFSET :offset
`, filter)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
sizeQuery, err := filterTemplate(`
SELECT COUNT(id) FROM finding_events
WHERE 1=1
{{ if .MinScore }} AND score >= :minScore {{ end }}
{{ if .MaxScore }} AND score <= :maxScore {{ end }}
{{ if .MinDate }} AND time >= :minDate {{ end }}
{{ if .MaxDate }} AND time <= :mxDateUpBound {{ end }}
`, filter)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
// Param names can not contain as prefix a previously defined
// variable name to avoid collisions when processing stmts
args := map[string]interface{}{
"status": filter.Status,
"minScore": filter.MinScore,
"maxScore": filter.MaxScore,
"minDate": filter.MinDate,
"maxDate": filter.MaxDate,
"mxDateUpBound": fmt.Sprint(filter.MaxDate, dateUpBoundOffset),
"limit": filter.Size,
"offset": offset,
}
events := []model.FindingEvent{}
logQuery(db.Logger, "ListEvents", listQuery, args)
nstmt, err := db.DB.PrepareNamed(listQuery)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
defer nstmt.Close()
err = nstmt.Select(&events, args)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
var size int
nstmt, err = db.DB.PrepareNamed(sizeQuery)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
defer nstmt.Close()
err = nstmt.Get(&size, args)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
return events, storage.Pagination{Limit: filter.Size, Offset: offset, Total: size}, nil
}
// ListFindingEventsByFinding returns a filtered list of finding events for a specific finding.
func (db DB) ListFindingEventsByFinding(id string, filter storage.Filter) ([]model.FindingEvent, storage.Pagination, error) {
offset := storage.Offset(filter.Page, filter.Size)
listQuery, err := filterTemplate(`
SELECT * FROM finding_events WHERE finding_id = :findingID
ORDER BY time DESC
LIMIT :limit OFFSET :offset
`, filter)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
sizeQuery, err := filterTemplate(`
SELECT COUNT(*) FROM (
SELECT DISTINCT finding_id, source_id, time
FROM finding_events WHERE finding_id = :findingID
) TMP
`, filter)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
args := map[string]interface{}{
"findingID": id,
"status": filter.Status,
"limit": filter.Size,
"offset": offset,
}
events := []model.FindingEvent{}
logQuery(db.Logger, "ListFindingEventsByFinding", listQuery, args)
nstmt, err := db.DB.PrepareNamed(listQuery)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
defer nstmt.Close()
err = nstmt.Select(&events, args)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
var size int
nstmt, err = db.DB.PrepareNamed(sizeQuery)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
defer nstmt.Close()
err = nstmt.Get(&size, args)
if err != nil {
return []model.FindingEvent{}, storage.Pagination{}, err
}
return events, storage.Pagination{Limit: filter.Size, Offset: offset, Total: size}, nil
}