From c7fc077fc0e70e6198224f86ef70901c94919a4f Mon Sep 17 00:00:00 2001 From: Ethan Lee Date: Thu, 13 Nov 2025 14:50:14 -0500 Subject: 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 LUCI-TryBot-Result: Go LUCI Reviewed-by: Robert Findley Auto-Submit: Ethan Lee --- .../000157_add_num_imports_col_to_units.down.sql | 9 ++++++++ .../000157_add_num_imports_col_to_units.up.sql | 25 ++++++++++++++++++++++ 2 files changed, 34 insertions(+) create mode 100644 migrations/000157_add_num_imports_col_to_units.down.sql create mode 100644 migrations/000157_add_num_imports_col_to_units.up.sql 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; -- cgit v1.3