internal/postgres: replace units.path references with paths.path
Instead of reading from units.path, read from paths.path instead.
In a future CL, we will be deprecating units.path.
Change-Id: I18307ac4b8114afc5c21395b6ef547d25cdcb77f
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/282114
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>
diff --git a/internal/postgres/search.go b/internal/postgres/search.go
index 07db186..f8ce3b5 100644
--- a/internal/postgres/search.go
+++ b/internal/postgres/search.go
@@ -332,7 +332,7 @@
}
query := fmt.Sprintf(`
SELECT
- u.path,
+ p.path,
u.name,
d.synopsis,
u.license_types,
@@ -410,8 +410,8 @@
SETWEIGHT(TO_TSVECTOR($4), 'C') ||
SETWEIGHT(TO_TSVECTOR($5), 'D')
),
- hll_hash(u.path) & (%[1]d - 1),
- hll_zeros(hll_hash(u.path))
+ hll_hash(p.path) & (%[1]d - 1),
+ hll_zeros(hll_hash(p.path))
FROM
units u
INNER JOIN
@@ -427,7 +427,7 @@
ON
u.id = d.unit_id
WHERE
- u.path = $1
+ p.path = $1
%s
LIMIT 1
ON CONFLICT (package_path)
@@ -521,10 +521,12 @@
FROM modules m
INNER JOIN units u
ON m.id = u.module_id
+ INNER JOIN paths p
+ ON p.id = u.path_id
LEFT JOIN readmes r
ON u.id = r.unit_id
INNER JOIN search_documents sd
- ON sd.package_path = u.path
+ ON sd.package_path = p.path
AND sd.module_path = m.module_path
AND sd.version = m.version
WHERE sd.updated_at < $1
diff --git a/internal/postgres/stdlib.go b/internal/postgres/stdlib.go
index 553e8bf..a2b3798 100644
--- a/internal/postgres/stdlib.go
+++ b/internal/postgres/stdlib.go
@@ -22,8 +22,10 @@
defer derrors.Wrap(&err, "DB.GetStdlibPaths(ctx, %q)", suffix)
q := `
- SELECT path
- FROM units
+ SELECT p.path
+ FROM units u
+ INNER JOIN paths p
+ ON p.id = u.path_id
WHERE module_id = (
-- latest release version of stdlib
SELECT id
@@ -33,10 +35,10 @@
version_type = 'release' DESC,
sort_version DESC
LIMIT 1)
- AND name != ''
- AND path NOT LIKE 'cmd/%'
- AND path LIKE '%/' || $2
- ORDER BY path
+ AND u.name != ''
+ AND p.path NOT LIKE 'cmd/%'
+ AND p.path LIKE '%/' || $2
+ ORDER BY p.path
`
err = db.db.RunQuery(ctx, q, func(rows *sql.Rows) error {
var p string
diff --git a/internal/postgres/unit.go b/internal/postgres/unit.go
index 9238a1d..c8521f9 100644
--- a/internal/postgres/unit.go
+++ b/internal/postgres/unit.go
@@ -214,9 +214,10 @@
query := `
SELECT u.id
FROM units u
+ INNER JOIN paths p ON (p.id = u.path_id)
INNER JOIN modules m ON (u.module_id = m.id)
WHERE
- u.path = $1
+ p.path = $1
AND m.module_path = $2
AND m.version = $3;`
err = db.db.QueryRow(ctx, query, fullPath, modulePath, resolvedVersion).Scan(&unitID)
@@ -260,7 +261,7 @@
query := `
SELECT
- u.path,
+ p.path,
u.name,
u.redistributable,
d.synopsis,
@@ -269,6 +270,8 @@
FROM modules m
INNER JOIN units u
ON u.module_id = m.id
+ INNER JOIN paths p
+ ON p.id = u.path_id
LEFT JOIN documentation d
ON d.unit_id = u.id
WHERE
@@ -342,6 +345,8 @@
WHERE package_path = $1
), 0) AS num_imported_by
FROM units u
+ INNER JOIN paths p
+ ON p.id = u.path_id
INNER JOIN modules m
ON u.module_id = m.id
LEFT JOIN documentation d
@@ -349,7 +354,7 @@
LEFT JOIN readmes r
ON r.unit_id = u.id
WHERE
- u.path = $1
+ p.path = $1
AND m.module_path = $2
AND m.version = $3;`
@@ -406,7 +411,7 @@
query := `
SELECT
u.id,
- u.path,
+ p.path,
u.module_id,
u.v1_path,
u.name,
@@ -416,6 +421,10 @@
FROM
units u
INNER JOIN
+ paths p
+ ON
+ p.id = u.path_id
+ INNER JOIN
modules m
ON
u.module_id = m.id
@@ -449,12 +458,14 @@
FROM modules m
INNER JOIN units u
ON u.module_id = m.id
+ INNER JOIN paths p
+ ON u.path_id = p.id
INNER JOIN readmes r
ON u.id = r.unit_id
WHERE
m.module_path=$1
AND m.version=$2
- AND m.module_path=u.path`, modulePath, resolvedVersion).Scan(&readme.Filepath, &readme.Contents)
+ AND m.module_path=p.path`, modulePath, resolvedVersion).Scan(&readme.Filepath, &readme.Contents)
switch err {
case sql.ErrNoRows:
return nil, derrors.NotFound
diff --git a/internal/postgres/version.go b/internal/postgres/version.go
index d1e100a..692b229 100644
--- a/internal/postgres/version.go
+++ b/internal/postgres/version.go
@@ -60,7 +60,9 @@
u.v1_path = (
SELECT u2.v1_path
FROM units as u2
- WHERE u2.path = $1
+ INNER JOIN paths p
+ ON p.id = u2.path_id
+ WHERE p.path = $1
LIMIT 1
)
AND version_type in (%s)
@@ -163,7 +165,11 @@
}
v1Path := internal.V1Path(fullPath, modulePath)
- row = db.db.QueryRow(ctx, `SELECT path FROM units WHERE v1_path = $1 AND module_id = $2;`, v1Path, modID)
+ row = db.db.QueryRow(ctx, `
+ SELECT p.path
+ FROM units u
+ INNER JOIN paths p ON p.id = u.path_id
+ WHERE v1_path = $1 AND module_id = $2;`, v1Path, modID)
var path string
switch err := row.Scan(&path); err {
case nil:
@@ -196,7 +202,11 @@
// See if the unit path exists at that version.
var x int
- err = db.db.QueryRow(ctx, `SELECT 1 FROM units WHERE path = $1 AND module_id = $2`, unitPath, modID).Scan(&x)
+ err = db.db.QueryRow(ctx, `
+ SELECT 1
+ FROM units u
+ INNER JOIN paths p ON p.id = u.path_id
+ WHERE p.path = $1 AND u.module_id = $2`, unitPath, modID).Scan(&x)
switch err {
case nil:
return true, nil
