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
|
// Copyright 2025 syzkaller project authors. All rights reserved.
// Use of this source code is governed by Apache 2 LICENSE that can be found in the LICENSE file.
package db
import (
"context"
"time"
"cloud.google.com/go/spanner"
)
type StatsRepository struct {
client *spanner.Client
}
func NewStatsRepository(client *spanner.Client) *StatsRepository {
return &StatsRepository{
client: client,
}
}
type CountPerWeek struct {
Date time.Time `spanner:"Date"`
Count int64 `spanner:"Count"`
}
func (repo *StatsRepository) ProcessedSeriesPerWeek(ctx context.Context) (
[]*CountPerWeek, error) {
return readEntities[CountPerWeek](ctx, repo.client.Single(), spanner.Statement{
SQL: `SELECT
TIMESTAMP_TRUNC(Sessions.FinishedAt, WEEK) as Date,
COUNT(*) as Count
FROM Series
JOIN Sessions ON Sessions.ID = Series.LatestSessionID
WHERE FinishedAt IS NOT NULL
GROUP BY Date
ORDER BY Date`,
})
}
func (repo *StatsRepository) FindingsPerWeek(ctx context.Context) (
[]*CountPerWeek, error) {
return readEntities[CountPerWeek](ctx, repo.client.Single(), spanner.Statement{
SQL: `SELECT
TIMESTAMP_TRUNC(Sessions.FinishedAt, WEEK) as Date,
COUNT(*) as Count
FROM Findings
JOIN Sessions ON Sessions.ID = Findings.SessionID AND Sessions.FinishedAt IS NOT NULL
GROUP BY Date
ORDER BY Date`,
})
}
type StatusPerWeek struct {
Date time.Time `spanner:"Date"`
Total int64 `spanner:"Total"`
Finished int64
Skipped int64 `spanner:"Skipped"`
WithFailedSteps int64 `spanner:"WithFailedSteps"`
WithSkippedSteps int64 `spanner:"WithSkippedSteps"`
}
func (repo *StatsRepository) SessionStatusPerWeek(ctx context.Context) (
[]*StatusPerWeek, error) {
rows, err := readEntities[StatusPerWeek](ctx, repo.client.Single(), spanner.Statement{
SQL: `WITH SessionTestAggregates AS (
SELECT
SessionID,
COUNTIF(Result = 'error') > 0 AS HasFailedSteps,
COUNTIF(Result = 'skipped') > 0 AS HasSkippedSteps
FROM SessionTests
GROUP BY SessionID
)
SELECT
TIMESTAMP_TRUNC(Sessions.FinishedAt, WEEK) AS Date,
COUNT(Sessions.ID) AS Total,
COUNTIF(Sessions.SkipReason IS NOT NULL) AS Skipped,
COUNTIF(sta.HasFailedSteps) AS WithFailedSteps,
COUNTIF(sta.HasSkippedSteps AND NOT sta.HasFailedSteps) AS WithSkippedSteps
FROM Sessions
LEFT JOIN
SessionTestAggregates AS sta ON Sessions.ID = sta.SessionID
WHERE Sessions.FinishedAt IS NOT NULL
GROUP BY Date
ORDER BY Date`,
})
if err != nil {
return nil, err
}
for _, row := range rows {
row.Finished = row.Total - row.Skipped - row.WithFailedSteps - row.WithSkippedSteps
}
return rows, err
}
type DelayPerWeek struct {
Date time.Time `spanner:"Date"`
DelayHours float64 `spanner:"AvgDelayHours"`
}
func (repo *StatsRepository) DelayPerWeek(ctx context.Context) (
[]*DelayPerWeek, error) {
return readEntities[DelayPerWeek](ctx, repo.client.Single(), spanner.Statement{
SQL: `SELECT
TIMESTAMP_TRUNC(Sessions.StartedAt, WEEK) as Date,
AVG(TIMESTAMP_DIFF(Sessions.StartedAt,Sessions.CreatedAt, HOUR)) as AvgDelayHours
FROM Sessions
WHERE StartedAt IS NOT NULL
GROUP BY Date
ORDER BY Date`,
})
}
|
