internal/postgres: GetLatestMajorPathForV1Path

GetLatestMajorPathForV1Path returns the path that is the latest major
for the v1path.

Change-Id: Iec02b0771427b535817b5bd3794f6aa1a807280d
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/282617
Trust: Julie Qiu <julie@golang.org>
Run-TryBot: Julie Qiu <julie@golang.org>
TryBot-Result: kokoro <noreply+kokoro@google.com>
Reviewed-by: Jonathan Amsterdam <jba@google.com>

1 files changed, 71 insertions, 0 deletions

diff --git a/internal/postgres/path.go b/internal/postgres/path.go
new file mode 100644
index 00000000..225c8d3c
--- /dev/null
+++ b/internal/postgres/path.go
@@ -0,0 +1,71 @@
+// Copyright 2021 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.
+
+package postgres
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"strconv"
+	"strings"
+
+	"golang.org/x/pkgsite/internal"
+	"golang.org/x/pkgsite/internal/derrors"
+)
+
+// GetLatestMajorPathForV1Path reports the latest unit path in the series for
+// the given v1path.
+func (db *DB) GetLatestMajorPathForV1Path(ctx context.Context, v1path string) (_ string, err error) {
+	defer derrors.Wrap(&err, "DB.GetLatestPathForV1Path(ctx, %q)", v1path)
+	q := `
+		SELECT p.path, m.series_path
+		FROM paths p
+		INNER JOIN units u ON u.path_id = p.id
+		INNER JOIN modules m ON u.module_id = m.id
+		WHERE u.v1path_id = (
+			SELECT p.id
+			FROM paths p
+			INNER JOIN units u ON u.v1path_id = p.id
+			WHERE p.path = $1
+			ORDER BY p.path DESC
+			LIMIT 1
+		);`
+	paths := map[string]string{}
+	err = db.db.RunQuery(ctx, q, func(rows *sql.Rows) error {
+		var p, sp string
+		if err := rows.Scan(&p, &sp); err != nil {
+			return err
+		}
+		paths[p] = sp
+		return nil
+	}, v1path)
+	if err != nil {
+		return "", err
+	}
+
+	var (
+		maj     int
+		majPath string
+	)
+	for p, sp := range paths {
+		// Trim the series path and suffix from the unit path.
+		// Keep only the N following vN.
+		suffix := internal.Suffix(v1path, sp)
+		v := strings.TrimSuffix(strings.TrimPrefix(
+			strings.TrimSuffix(strings.TrimPrefix(p, sp), suffix), "/v"), "/")
+		var i int
+		if v != "" {
+			i, err = strconv.Atoi(v)
+			if err != nil {
+				return "", fmt.Errorf("strconv.Atoi(%q): %v", v, err)
+			}
+		}
+		if maj <= i {
+			maj = i
+			majPath = p
+		}
+	}
+	return majPath, nil
+}