internal/bigquery: add vulndb requests
Read and write data about vulndb requests to BigQuery.
Change-Id: I87c1e4ec506ee1fd55c9c46aff235c961bd725a5
Reviewed-on: https://go-review.googlesource.com/c/pkgsite-metrics/+/465216
Auto-Submit: Julie Qiu <julieqiu@google.com>
Run-TryBot: Julie Qiu <julieqiu@google.com>
TryBot-Result: Gopher Robot <gobot@golang.org>
Reviewed-by: Jonathan Amsterdam <jba@google.com>
Reviewed-by: Julie Qiu <julieqiu@google.com>
diff --git a/internal/bigquery/bigquery_test.go b/internal/bigquery/bigquery_test.go
index 34d85fb..0715f43 100644
--- a/internal/bigquery/bigquery_test.go
+++ b/internal/bigquery/bigquery_test.go
@@ -16,6 +16,7 @@
"cloud.google.com/go/civil"
"github.com/google/go-cmp/cmp"
"github.com/google/go-cmp/cmp/cmpopts"
+ "golang.org/x/exp/slices"
"golang.org/x/pkgsite-metrics/internal/version"
"google.golang.org/api/iterator"
)
@@ -196,6 +197,39 @@
}
})
+ t.Run("request counts", func(t *testing.T) {
+ date := func(y, m, d int) civil.Date {
+ return civil.Date{Year: y, Month: time.Month(m), Day: d}
+ }
+
+ must(client.CreateTable(ctx, VulnDBRequestTableName))
+ defer client.Table(VulnDBRequestTableName).Delete(ctx)
+ counts := []*VulnDBRequestCount{
+ {Date: date(2022, 10, 1), Count: 1},
+ {Date: date(2022, 10, 3), Count: 3},
+ {Date: date(2022, 10, 4), Count: 4},
+ }
+ for _, row := range counts {
+ must(client.Upload(ctx, VulnDBRequestTableName, row))
+ }
+ // Insert duplicates with a later time; we expect to get these, not the originals.
+ time.Sleep(50 * time.Millisecond)
+ for _, row := range counts {
+ row.Count++
+ must(client.Upload(ctx, VulnDBRequestTableName, row))
+ }
+
+ got, err := readVulnDBRequestCounts(ctx, client)
+ if err != nil {
+ t.Fatal(err)
+ }
+ want := slices.Clone(counts)
+ slices.SortFunc(want, func(c1, c2 *VulnDBRequestCount) bool { return c1.Date.After(c2.Date) })
+ if diff := cmp.Diff(want, got, cmpopts.IgnoreFields(VulnDBRequestCount{}, "InsertedAt")); diff != "" {
+ t.Errorf("mismatch (-want, +got):\n%s", diff)
+ }
+ })
+
}
func readTable[T any](ctx context.Context, table *bq.Table, newT func() *T) ([]*T, error) {
diff --git a/internal/bigquery/vulndb_requests.go b/internal/bigquery/vulndb_requests.go
new file mode 100644
index 0000000..b9bc955
--- /dev/null
+++ b/internal/bigquery/vulndb_requests.go
@@ -0,0 +1,78 @@
+// Copyright 2022 The Go Authors. All rights reserved.
+// Use of this source code is governed by a BSD-style
+// license that can be found in the LICENSE file.
+
+// Track daily counts of requests made to vuln.go.dev in BigQuery.
+
+package bigquery
+
+import (
+ "context"
+ "fmt"
+ "time"
+
+ bq "cloud.google.com/go/bigquery"
+ "cloud.google.com/go/civil"
+ "golang.org/x/pkgsite-metrics/internal/derrors"
+)
+
+const (
+ // Vuln DB requests live in their own dataset that doesn't vary.
+ VulnDBRequestDatasetName = "vulndb"
+ VulnDBRequestTableName = "requests"
+)
+
+type VulnDBRequestCount struct {
+ Date civil.Date `bigquery:"date"`
+ Count int `bigquery:"count"`
+ InsertedAt time.Time `bigquery:"inserted_at"`
+}
+
+func init() {
+ s, err := bq.InferSchema(VulnDBRequestCount{})
+ if err != nil {
+ panic(err)
+ }
+ addTable(VulnDBRequestTableName, s)
+}
+
+// SetUploadTime is used by Client.Upload.
+func (v *VulnDBRequestCount) SetUploadTime(t time.Time) { v.InsertedAt = t }
+
+func WriteVulnDBRequestCounts(ctx context.Context, projectID string, rcs []*VulnDBRequestCount) (err error) {
+ defer derrors.Wrap(&err, "WriteVulnDBRequestCounts(%s)", projectID)
+ c, err := NewClientCreate(ctx, projectID, VulnDBRequestDatasetName)
+ if err != nil {
+ return err
+ }
+ if err := c.CreateTable(ctx, VulnDBRequestTableName); err != nil {
+ return err
+ }
+ return UploadMany(ctx, c, VulnDBRequestTableName, rcs, 0)
+}
+
+// ReadVulnDBRequestCounts returns daily counts for requests to the vuln DB, with the most recent first.
+func ReadVulnDBRequestCounts(ctx context.Context, projectID string) (_ []*VulnDBRequestCount, err error) {
+ defer derrors.Wrap(&err, "ReadVulnDBRequestCounts(%s)", projectID)
+
+ c, err := NewClient(ctx, projectID, VulnDBRequestDatasetName)
+ if err != nil {
+ return nil, err
+ }
+ return readVulnDBRequestCounts(ctx, c)
+}
+
+func readVulnDBRequestCounts(ctx context.Context, c *Client) (_ []*VulnDBRequestCount, err error) {
+ // Select the most recently inserted row for each date.
+ q := fmt.Sprintf("(%s) ORDER BY date DESC",
+ partitionQuery(c.FullTableName(VulnDBRequestTableName), "date", "inserted_at DESC"))
+ iter, err := c.Query(ctx, q)
+ if err != nil {
+ return nil, err
+ }
+ counts, err := All[VulnDBRequestCount](iter)
+ if err != nil {
+ return nil, err
+ }
+ return counts, nil
+}
diff --git a/static/worker.tmpl b/static/index.tmpl
similarity index 100%
rename from static/worker.tmpl
rename to static/index.tmpl
