migrations: add num_imports column to units table

For golang/go#76284

Change-Id: Ia6526f4c7c155b8101fc36d100ea4df46d40171c
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/720340
kokoro-CI: kokoro <noreply+kokoro@google.com>
LUCI-TryBot-Result: Go LUCI <golang-scoped@luci-project-accounts.iam.gserviceaccount.com>
Reviewed-by: Robert Findley <rfindley@google.com>
Auto-Submit: Ethan Lee <ethanalee@google.com>

2 files changed, 34 insertions, 0 deletions

diff --git a/migrations/000157_add_num_imports_col_to_units.down.sql b/migrations/000157_add_num_imports_col_to_units.down.sql
new file mode 100644
index 00000000..75b9a936
--- /dev/null
+++ b/migrations/000157_add_num_imports_col_to_units.down.sql
@@ -0,0 +1,9 @@
+-- Copyright 2025 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.
+
+BEGIN;
+
+ALTER TABLE units DROP COLUMN num_imports;
+
+END;
diff --git a/migrations/000157_add_num_imports_col_to_units.up.sql b/migrations/000157_add_num_imports_col_to_units.up.sql
new file mode 100644
index 00000000..7da8d12f
--- /dev/null
+++ b/migrations/000157_add_num_imports_col_to_units.up.sql
@@ -0,0 +1,25 @@
+-- Copyright 2025 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.
+
+BEGIN;
+
+ALTER TABLE units ADD COLUMN num_imports INTEGER;
+
+-- Backfill the num_imports column with the count of imports for each unit.
+-- This UPDATE uses a subquery to count imports per unit_id from the imports table.
+UPDATE units u
+SET num_imports = sub.import_count
+FROM (
+    SELECT unit_id, COUNT(unit_id) AS import_count
+    FROM imports
+    GROUP BY unit_id
+) AS sub
+WHERE u.id = sub.unit_id;
+
+-- Set num_imports to 0 for units that have no entries in the imports table.
+UPDATE units
+SET num_imports = 0
+WHERE num_imports IS NULL;
+
+END;