/
sources.go
84 lines (73 loc) · 2.16 KB
/
sources.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
/*
Copyright 2020 Adevinta
*/
package postgresql
import (
"database/sql"
"github.com/adevinta/vulnerability-db-api/pkg/model"
"github.com/adevinta/vulnerability-db-api/pkg/storage"
)
// ListSources returns a paginated list of sources.
func (db DB) ListSources(filter storage.Filter) ([]model.Source, storage.Pagination, error) {
offset := storage.Offset(filter.Page, filter.Size)
sources := []model.Source{}
query := "SELECT * FROM sources LIMIT $1 OFFSET $2"
logQuery(db.Logger, "ListSources", query, filter.Size, offset)
err := db.DB.Select(&sources, query, filter.Size, offset)
if err != nil {
return []model.Source{}, storage.Pagination{}, err
}
var size int
err = db.DB.Get(&size, "SELECT COUNT(id) FROM sources")
if err != nil {
return []model.Source{}, storage.Pagination{}, err
}
return sources, storage.Pagination{Limit: filter.Size, Offset: offset, Total: size}, nil
}
// GetSource returns a specifc source.
func (db DB) GetSource(id string) (model.Source, error) {
var source model.Source
query := "SELECT * FROM sources WHERE id = $1"
logQuery(db.Logger, "GetSource", query, id)
err := db.DB.Get(&source, query, id)
if err == sql.ErrNoRows {
return model.Source{}, nil
}
if err != nil {
return model.Source{}, err
}
return source, nil
}
// GetSourceMTTR returns the mean time to remedite among all the sources with
// the same name, component and options as the given source ID.
func (db DB) GetSourceMTTR(id string) (float32, error) {
var mttr float32
query := `
WITH relevant_sources AS (
SELECT name, component, options from sources
WHERE id = $1
)
SELECT COALESCE(AVG(ttr), 0)
FROM (
SELECT fexp.ttr
FROM finding_exposures fexp
INNER JOIN finding_events fe ON fexp.finding_id = fe.finding_id
INNER JOIN sources s ON fe.source_id=s.id
INNER JOIN relevant_sources rs
ON rs.name=s.name
AND rs.component = s.component
AND rs.options = s.options
WHERE fexp.ttr IS NOT NULL
GROUP BY fexp.finding_id, fexp.found_at
) AS exposures
`
logQuery(db.Logger, "GetSourceMTTR", query, id)
err := db.DB.Get(&mttr, query, id)
if err == sql.ErrNoRows {
return 0, nil
}
if err != nil {
return 0, err
}
return mttr, nil
}