github.com/google/syzkaller@v0.0.0-20251211124644-a066d2bc4b02/syz-cluster/pkg/db/stats_repo.go (about)

     1  // Copyright 2025 syzkaller project authors. All rights reserved.
     2  // Use of this source code is governed by Apache 2 LICENSE that can be found in the LICENSE file.
     3  
     4  package db
     5  
     6  import (
     7  	"context"
     8  	"time"
     9  
    10  	"cloud.google.com/go/spanner"
    11  )
    12  
    13  type StatsRepository struct {
    14  	client *spanner.Client
    15  }
    16  
    17  func NewStatsRepository(client *spanner.Client) *StatsRepository {
    18  	return &StatsRepository{
    19  		client: client,
    20  	}
    21  }
    22  
    23  type CountPerWeek struct {
    24  	Date  time.Time `spanner:"Date"`
    25  	Count int64     `spanner:"Count"`
    26  }
    27  
    28  func (repo *StatsRepository) ProcessedSeriesPerWeek(ctx context.Context) (
    29  	[]*CountPerWeek, error) {
    30  	return readEntities[CountPerWeek](ctx, repo.client.Single(), spanner.Statement{
    31  		SQL: `SELECT
    32    TIMESTAMP_TRUNC(Sessions.FinishedAt, WEEK) as Date,
    33    COUNT(*) as Count
    34  FROM Series
    35  JOIN Sessions ON Sessions.ID = Series.LatestSessionID
    36  WHERE FinishedAt IS NOT NULL
    37  GROUP BY Date
    38  ORDER BY Date`,
    39  	})
    40  }
    41  
    42  func (repo *StatsRepository) ReportsPerWeek(ctx context.Context) (
    43  	[]*CountPerWeek, error) {
    44  	return readEntities[CountPerWeek](ctx, repo.client.Single(), spanner.Statement{
    45  		SQL: `SELECT
    46    TIMESTAMP_TRUNC(SessionReports.ReportedAt, WEEK) as Date,
    47    COUNT(*) as Count
    48  FROM Findings
    49  JOIN SessionReports ON SessionReports.SessionID = Findings.SessionID
    50  WHERE SessionReports.Moderation = FALSE AND SessionReports.ReportedAt IS NOT NULL
    51  GROUP BY Date
    52  ORDER BY Date`,
    53  	})
    54  }
    55  
    56  func (repo *StatsRepository) FindingsPerWeek(ctx context.Context) (
    57  	[]*CountPerWeek, error) {
    58  	return readEntities[CountPerWeek](ctx, repo.client.Single(), spanner.Statement{
    59  		SQL: `SELECT
    60    TIMESTAMP_TRUNC(Sessions.FinishedAt, WEEK) as Date,
    61    COUNT(*) as Count
    62  FROM Findings
    63  JOIN Sessions ON Sessions.ID = Findings.SessionID AND Sessions.FinishedAt IS NOT NULL
    64  GROUP BY Date
    65  ORDER BY Date`,
    66  	})
    67  }
    68  
    69  type StatusPerWeek struct {
    70  	Date             time.Time `spanner:"Date"`
    71  	Total            int64     `spanner:"Total"`
    72  	Finished         int64
    73  	Skipped          int64 `spanner:"Skipped"`
    74  	WithFailedSteps  int64 `spanner:"WithFailedSteps"`
    75  	WithSkippedSteps int64 `spanner:"WithSkippedSteps"`
    76  }
    77  
    78  func (repo *StatsRepository) SessionStatusPerWeek(ctx context.Context) (
    79  	[]*StatusPerWeek, error) {
    80  	rows, err := readEntities[StatusPerWeek](ctx, repo.client.Single(), spanner.Statement{
    81  		SQL: `WITH SessionTestAggregates AS (
    82    SELECT
    83      SessionID,
    84      COUNTIF(Result = 'error') > 0 AS HasFailedSteps,
    85      COUNTIF(Result = 'skipped') > 0 AS HasSkippedSteps
    86    FROM SessionTests
    87    GROUP BY SessionID
    88  )
    89  SELECT
    90    TIMESTAMP_TRUNC(Sessions.FinishedAt, WEEK) AS Date,
    91    COUNT(Sessions.ID) AS Total,
    92    COUNTIF(Sessions.SkipReason IS NOT NULL) AS Skipped,
    93    COUNTIF(sta.HasFailedSteps) AS WithFailedSteps,
    94    COUNTIF(sta.HasSkippedSteps AND NOT sta.HasFailedSteps) AS WithSkippedSteps
    95  FROM Sessions
    96  LEFT JOIN
    97    SessionTestAggregates AS sta ON Sessions.ID = sta.SessionID
    98  WHERE Sessions.FinishedAt IS NOT NULL
    99  GROUP BY Date
   100  ORDER BY Date`,
   101  	})
   102  	if err != nil {
   103  		return nil, err
   104  	}
   105  	for _, row := range rows {
   106  		row.Finished = row.Total - row.Skipped - row.WithFailedSteps - row.WithSkippedSteps
   107  	}
   108  	return rows, err
   109  }
   110  
   111  type DelayPerWeek struct {
   112  	Date       time.Time `spanner:"Date"`
   113  	DelayHours float64   `spanner:"AvgDelayHours"`
   114  }
   115  
   116  func (repo *StatsRepository) DelayPerWeek(ctx context.Context) (
   117  	[]*DelayPerWeek, error) {
   118  	return readEntities[DelayPerWeek](ctx, repo.client.Single(), spanner.Statement{
   119  		SQL: `SELECT
   120    TIMESTAMP_TRUNC(Sessions.StartedAt, WEEK) as Date,
   121    AVG(TIMESTAMP_DIFF(Sessions.StartedAt,Sessions.CreatedAt, HOUR)) as AvgDelayHours
   122  FROM Sessions
   123  WHERE StartedAt IS NOT NULL
   124  GROUP BY Date
   125  ORDER BY Date`,
   126  	})
   127  }