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>
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 0000000..75b9a93
--- /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 0000000..7da8d12
--- /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;
