From b254c167844fc717d916dcfcdb39396eb169e60c Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Wed, 15 Jan 2025 22:27:57 +0200 Subject: [PATCH 01/11] Backfill archived and traversal_ids for vulnerability_statistics Changelog: added --- ...versal_ids_to_vulnerability_statistics.yml | 8 ++ ...aversal_ids_to_vulnerability_statistics.rb | 27 ++++++ db/schema_migrations/20250115095725 | 1 + ...aversal_ids_to_vulnerability_statistics.rb | 34 +++++++ ...al_ids_to_vulnerability_statistics_spec.rb | 97 +++++++++++++++++++ ...al_ids_to_vulnerability_statistics_spec.rb | 27 ++++++ 6 files changed, 194 insertions(+) create mode 100644 db/docs/batched_background_migrations/backfill_archived_and_traversal_ids_to_vulnerability_statistics.yml create mode 100644 db/post_migrate/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb create mode 100644 db/schema_migrations/20250115095725 create mode 100644 lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb create mode 100644 spec/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb create mode 100644 spec/migrations/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb diff --git a/db/docs/batched_background_migrations/backfill_archived_and_traversal_ids_to_vulnerability_statistics.yml b/db/docs/batched_background_migrations/backfill_archived_and_traversal_ids_to_vulnerability_statistics.yml new file mode 100644 index 00000000000000..eee0a1fd11612d --- /dev/null +++ b/db/docs/batched_background_migrations/backfill_archived_and_traversal_ids_to_vulnerability_statistics.yml @@ -0,0 +1,8 @@ +--- +migration_job_name: BackfillArchivedAndTraversalIdsToVulnerabilityStatistics +description: Backfill project.archived and project.namespace.traversal_ids values to the denormalized columns of the same name on vulnerability_statistics +feature_category: vulnerability_management +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/177993 +milestone: '17.8' +queued_migration_version: 20250115095725 +finalized_by: # version of the migration that finalized this BBM diff --git a/db/post_migrate/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/db/post_migrate/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb new file mode 100644 index 00000000000000..465ce2f1975d60 --- /dev/null +++ b/db/post_migrate/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -0,0 +1,27 @@ +# frozen_string_literal: true + +class QueueBackfillArchivedAndTraversalIdsToVulnerabilityStatistics < Gitlab::Database::Migration[2.2] + milestone '17.8' + + restrict_gitlab_migration gitlab_schema: :gitlab_sec + + MIGRATION = "BackfillArchivedAndTraversalIdsToVulnerabilityStatistics" + DELAY_INTERVAL = 2.minutes + BATCH_SIZE = 1000 + SUB_BATCH_SIZE = 100 + + def up + queue_batched_background_migration( + MIGRATION, + :vulnerability_statistics, + :id, + job_interval: DELAY_INTERVAL, + batch_size: BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + delete_batched_background_migration(MIGRATION, :vulnerability_statistics, :id, []) + end +end diff --git a/db/schema_migrations/20250115095725 b/db/schema_migrations/20250115095725 new file mode 100644 index 00000000000000..d64d55fea57912 --- /dev/null +++ b/db/schema_migrations/20250115095725 @@ -0,0 +1 @@ +99a40016963b505fa899ae8eecb4d3dd29c51f1c7f6fc1ec2b3d9406c7155cf0 \ No newline at end of file diff --git a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb new file mode 100644 index 00000000000000..4f8d5069348b33 --- /dev/null +++ b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -0,0 +1,34 @@ +# frozen_string_literal: true + +module Gitlab + module BackgroundMigration + class BackfillArchivedAndTraversalIdsToVulnerabilityStatistics < BatchedMigrationJob + operation_name :backfill_archived_and_traversal_ids_in_vulnerability_statistics_table + feature_category :vulnerability_management + + def perform + each_sub_batch do |sub_batch| + connection.exec_update(update_sql(sub_batch)) + end + end + + private + + def update_sql(sub_batch) + <<~SQL + UPDATE + vulnerability_statistics + SET + traversal_ids = namespaces.traversal_ids, + archived = projects.archived + FROM + projects + INNER JOIN namespaces ON namespaces.id = projects.namespace_id + WHERE + vulnerability_statistics.id IN (#{sub_batch.select(:id).to_sql}) AND + vulnerability_statistics.project_id = projects.id + SQL + end + end + end +end diff --git a/spec/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb b/spec/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb new file mode 100644 index 00000000000000..bc161bfb0f7013 --- /dev/null +++ b/spec/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb @@ -0,0 +1,97 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::BackgroundMigration::BackfillArchivedAndTraversalIdsToVulnerabilityStatistics, migration: :gitlab_sec, feature_category: :vulnerability_management do + let(:organizations) { table(:organizations) } + let(:namespaces) { table(:namespaces) } + let(:projects) { table(:projects) } + let(:vulnerability_statistics) { table(:vulnerability_statistics) } + let(:args) do + min, max = vulnerability_statistics.pick('MIN(id)', 'MAX(id)') + { + start_id: min, + end_id: max, + batch_table: 'vulnerability_statistics', + batch_column: 'id', + sub_batch_size: 100, + pause_ms: 0, + connection: ::Gitlab::Database::SecApplicationRecord.connection + } + end + + let(:organization) { organizations.create!(name: 'Organization', path: 'organization') } + let!(:group_namespace) do + namespaces.create!( + name: 'gitlab-org', + path: 'gitlab-org', + type: 'Group', + organization_id: organization.id + ).tap { |namespace| namespace.update!(traversal_ids: [namespace.id]) } + end + + let!(:other_group_namespace) do + namespaces.create!( + name: 'gitlab-com', + path: 'gitlab-com', + type: 'Group', + organization_id: organization.id + ).tap { |namespace| namespace.update!(traversal_ids: [namespace.id]) } + end + + let!(:project) { create_project('gitlab', group_namespace) } + let!(:other_project) { create_project('www-gitlab-com', other_group_namespace) } + + subject(:perform_migration) { described_class.new(**args).perform } + + before do + [project, other_project].each do |p| + create_vulnerability_statistic(project_id: p.id) + end + end + + it 'backfills traversal_ids and archived', :aggregate_failures do + perform_migration + + vulnerability_statistics.find_each do |vulnerability_statistic| + project = projects.find(vulnerability_statistic.project_id) + namespace = namespaces.find(project.namespace_id) + + expect(vulnerability_statistic.traversal_ids).to eq(namespace.traversal_ids) + expect(vulnerability_statistic.archived).to eq(project.archived) + end + end + + def create_vulnerability_statistic(project_id:) + vulnerability_statistics.create!( + project_id: project_id, + total: 0, + critical: 0, + high: 0, + medium: 0, + low: 0, + unknown: 0, + info: 0, + created_at: Time.current, + updated_at: Time.current + ) + end + + def create_project(name, group) + project_namespace = namespaces.create!( + name: name, + path: name, + type: 'Project', + organization_id: organization.id + ) + + projects.create!( + namespace_id: group.id, + project_namespace_id: project_namespace.id, + organization_id: organization.id, + name: name, + path: name, + archived: true + ) + end +end diff --git a/spec/migrations/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb b/spec/migrations/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb new file mode 100644 index 00000000000000..f723a7296739a5 --- /dev/null +++ b/spec/migrations/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb @@ -0,0 +1,27 @@ +# frozen_string_literal: true + +require 'spec_helper' +require_migration! + +RSpec.describe QueueBackfillArchivedAndTraversalIdsToVulnerabilityStatistics, migration: :gitlab_sec, feature_category: :vulnerability_management do + let!(:batched_migration) { described_class::MIGRATION } + + it 'schedules a new batched migration' do + reversible_migration do |migration| + migration.before -> { + expect(batched_migration).not_to have_scheduled_batched_migration + } + + migration.after -> { + expect(batched_migration).to have_scheduled_batched_migration( + gitlab_schema: :gitlab_sec, + table_name: :vulnerability_statistics, + column_name: :id, + interval: described_class::DELAY_INTERVAL, + batch_size: described_class::BATCH_SIZE, + sub_batch_size: described_class::SUB_BATCH_SIZE + ) + } + end + end +end -- GitLab From 57e74aa883416647b1e18768c9d66cec10bdc4dd Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Thu, 16 Jan 2025 09:55:04 +0200 Subject: [PATCH 02/11] Add letter_grade when creating vulnerability_statistics --- ...rchived_and_traversal_ids_to_vulnerability_statistics_spec.rb | 1 + 1 file changed, 1 insertion(+) diff --git a/spec/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb b/spec/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb index bc161bfb0f7013..bc14878643807d 100644 --- a/spec/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb +++ b/spec/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics_spec.rb @@ -72,6 +72,7 @@ def create_vulnerability_statistic(project_id:) low: 0, unknown: 0, info: 0, + letter_grade: 0, created_at: Time.current, updated_at: Time.current ) -- GitLab From 2abe87fc0a99e68da69d9fa1a836a8156d76e421 Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Thu, 16 Jan 2025 10:21:29 +0000 Subject: [PATCH 03/11] Update MS to 17.9 --- ...l_archived_and_traversal_ids_to_vulnerability_statistics.yml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/db/docs/batched_background_migrations/backfill_archived_and_traversal_ids_to_vulnerability_statistics.yml b/db/docs/batched_background_migrations/backfill_archived_and_traversal_ids_to_vulnerability_statistics.yml index eee0a1fd11612d..6044c1b7ed53a4 100644 --- a/db/docs/batched_background_migrations/backfill_archived_and_traversal_ids_to_vulnerability_statistics.yml +++ b/db/docs/batched_background_migrations/backfill_archived_and_traversal_ids_to_vulnerability_statistics.yml @@ -3,6 +3,6 @@ migration_job_name: BackfillArchivedAndTraversalIdsToVulnerabilityStatistics description: Backfill project.archived and project.namespace.traversal_ids values to the denormalized columns of the same name on vulnerability_statistics feature_category: vulnerability_management introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/177993 -milestone: '17.8' +milestone: '17.9' queued_migration_version: 20250115095725 finalized_by: # version of the migration that finalized this BBM -- GitLab From 5c0f7453494e0a0def4e507d41551a2b893e2ad0 Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Thu, 16 Jan 2025 10:21:56 +0000 Subject: [PATCH 04/11] Update MS to 17.9 --- ...ll_archived_and_traversal_ids_to_vulnerability_statistics.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/db/post_migrate/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/db/post_migrate/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb index 465ce2f1975d60..52ae389625b44b 100644 --- a/db/post_migrate/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb +++ b/db/post_migrate/20250115095725_queue_backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -1,7 +1,7 @@ # frozen_string_literal: true class QueueBackfillArchivedAndTraversalIdsToVulnerabilityStatistics < Gitlab::Database::Migration[2.2] - milestone '17.8' + milestone '17.9' restrict_gitlab_migration gitlab_schema: :gitlab_sec -- GitLab From 4defeb7a84a5067ceceb041ad38ffed146107882 Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Thu, 16 Jan 2025 12:25:12 +0200 Subject: [PATCH 05/11] Fix cross-join in BBM --- ...aversal_ids_to_vulnerability_statistics.rb | 37 ++++++++++++++++--- 1 file changed, 31 insertions(+), 6 deletions(-) diff --git a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb index 4f8d5069348b33..1dc00261574f0a 100644 --- a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb +++ b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -14,19 +14,44 @@ def perform private + class Project < ::Gitlab::Database::Migration[2.2]::MigrationRecord + self.table_name = 'projects' + belongs_to :namespace + scope :joins_namespace, -> { joins(:namespace) } + scope :archived_and_traversal_ids, ->(project_ids) { + where(id: project_ids).joins_namespace.limit(project_ids.length) + .pluck(:id, :archived, :traversal_ids) + } + end + + def project_to_archived_traversal_ids(project_ids) + # Query main DB for namespace and traversal_ids for affected projects by id + project_info = Project.archived_and_traversal_ids(project_ids) + return if project_info.blank? + + values = project_info.map do |row| + [ + row[0], + row[1], + Arel.sql("ARRAY#{row[2]}::bigint[]") + ] + end + + Arel::Nodes::ValuesList.new(values).to_sql + end + def update_sql(sub_batch) <<~SQL UPDATE vulnerability_statistics SET - traversal_ids = namespaces.traversal_ids, - archived = projects.archived + traversal_ids = project_to_archived_traversal_ids.traversal_ids, + archived = project_to_archived_traversal_ids.archived FROM - projects - INNER JOIN namespaces ON namespaces.id = projects.namespace_id + vulnerability_statistics + INNER JOIN project_to_archived_traversal_ids ON project_to_archived_traversal_ids.project_id = vulnerability_statistics.project_id WHERE - vulnerability_statistics.id IN (#{sub_batch.select(:id).to_sql}) AND - vulnerability_statistics.project_id = projects.id + vulnerability_statistics.id IN (#{sub_batch.select(:id).to_sql}) SQL end end -- GitLab From 4ca58c33a7a4bc64a8ec6fda3ec64e4b08600a52 Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Thu, 16 Jan 2025 14:52:28 +0200 Subject: [PATCH 06/11] Create separate query to get project information --- ...aversal_ids_to_vulnerability_statistics.rb | 45 +++++++++++++------ 1 file changed, 31 insertions(+), 14 deletions(-) diff --git a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb index 1dc00261574f0a..392439035ad8b2 100644 --- a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb +++ b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -6,9 +6,34 @@ class BackfillArchivedAndTraversalIdsToVulnerabilityStatistics < BatchedMigratio operation_name :backfill_archived_and_traversal_ids_in_vulnerability_statistics_table feature_category :vulnerability_management + UPDATE_SQL = <<~SQL + WITH project_to_archived_traversal_ids (project_id, archived, traversal_ids) AS ( + %{with_values} + ) + UPDATE + vulnerability_statistics + SET + traversal_ids = project_to_archived_traversal_ids.traversal_ids, + archived = project_to_archived_traversal_ids.archived + FROM + vulnerability_statistics + INNER JOIN project_to_archived_traversal_ids ON project_to_archived_traversal_ids.project_id = vulnerability_statistics.project_id + WHERE + vulnerability_statistics.id IN (%{vulnerability_statistics_ids}) + SQL + def perform each_sub_batch do |sub_batch| - connection.exec_update(update_sql(sub_batch)) + # rubocop:disable CodeReuse/ActiveRecord -- specific for this backfill + vulnerability_statistics_ids = sub_batch.pluck(:id).map { |id| Integer(id) }.join(', ') + affected_project_ids = sub_batch.pluck(:project_id) + # rubocop:enable CodeReuse/ActiveRecord + project_to_archived_traversal_ids = project_to_archived_traversal_ids(affected_project_ids) + + next if project_to_namespace_traversal_ids.blank? + + ::Gitlab::Database::SecApplicationRecord.connection.execute(update_sql(vulnerability_statistics_ids, + project_to_archived_traversal_ids)) end end @@ -18,10 +43,12 @@ class Project < ::Gitlab::Database::Migration[2.2]::MigrationRecord self.table_name = 'projects' belongs_to :namespace scope :joins_namespace, -> { joins(:namespace) } + # rubocop: disable CodeReuse/ActiveRecord -- redefining to avoid using application code in migration scope :archived_and_traversal_ids, ->(project_ids) { where(id: project_ids).joins_namespace.limit(project_ids.length) .pluck(:id, :archived, :traversal_ids) } + # rubocop: enable CodeReuse/ActiveRecord end def project_to_archived_traversal_ids(project_ids) @@ -40,19 +67,9 @@ def project_to_archived_traversal_ids(project_ids) Arel::Nodes::ValuesList.new(values).to_sql end - def update_sql(sub_batch) - <<~SQL - UPDATE - vulnerability_statistics - SET - traversal_ids = project_to_archived_traversal_ids.traversal_ids, - archived = project_to_archived_traversal_ids.archived - FROM - vulnerability_statistics - INNER JOIN project_to_archived_traversal_ids ON project_to_archived_traversal_ids.project_id = vulnerability_statistics.project_id - WHERE - vulnerability_statistics.id IN (#{sub_batch.select(:id).to_sql}) - SQL + def update_sql(vulnerability_statistics_ids, with_project_information) + format(UPDATE_SQL, vulnerability_statistics_ids: vulnerability_statistics_ids, + with_values: with_project_information) end end end -- GitLab From 37f87848696f3e295c3221dc8bf31fcb2494e20f Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Thu, 16 Jan 2025 15:48:20 +0200 Subject: [PATCH 07/11] Fix wrong parameter name --- ...ll_archived_and_traversal_ids_to_vulnerability_statistics.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb index 392439035ad8b2..46fa180f11e5e5 100644 --- a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb +++ b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -30,7 +30,7 @@ def perform # rubocop:enable CodeReuse/ActiveRecord project_to_archived_traversal_ids = project_to_archived_traversal_ids(affected_project_ids) - next if project_to_namespace_traversal_ids.blank? + next if project_to_archived_traversal_ids.blank? ::Gitlab::Database::SecApplicationRecord.connection.execute(update_sql(vulnerability_statistics_ids, project_to_archived_traversal_ids)) -- GitLab From 82a2b305bab28afdb9c417e82d8cdfa4738c21eb Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Thu, 16 Jan 2025 16:16:42 +0200 Subject: [PATCH 08/11] Fix duplicate vulnerability_statistics definition --- ..._archived_and_traversal_ids_to_vulnerability_statistics.rb | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb index 46fa180f11e5e5..7b0c7e4fa3850d 100644 --- a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb +++ b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -16,9 +16,9 @@ class BackfillArchivedAndTraversalIdsToVulnerabilityStatistics < BatchedMigratio traversal_ids = project_to_archived_traversal_ids.traversal_ids, archived = project_to_archived_traversal_ids.archived FROM - vulnerability_statistics - INNER JOIN project_to_archived_traversal_ids ON project_to_archived_traversal_ids.project_id = vulnerability_statistics.project_id + project_to_archived_traversal_ids WHERE + project_to_archived_traversal_ids.project_id = vulnerability_statistics.project_id AND vulnerability_statistics.id IN (%{vulnerability_statistics_ids}) SQL -- GitLab From fdf8f70818318ecad7c458926b2e0fc97951e045 Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Sun, 19 Jan 2025 12:27:52 +0200 Subject: [PATCH 09/11] Replaced seprate plucks with single pluck and transpose --- ...rchived_and_traversal_ids_to_vulnerability_statistics.rb | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb index 7b0c7e4fa3850d..28505d3a6ea1c3 100644 --- a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb +++ b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -25,8 +25,7 @@ class BackfillArchivedAndTraversalIdsToVulnerabilityStatistics < BatchedMigratio def perform each_sub_batch do |sub_batch| # rubocop:disable CodeReuse/ActiveRecord -- specific for this backfill - vulnerability_statistics_ids = sub_batch.pluck(:id).map { |id| Integer(id) }.join(', ') - affected_project_ids = sub_batch.pluck(:project_id) + vulnerability_statistics_ids, affected_project_ids = sub_batch.pluck(:id, :project_id).transpose # rubocop:enable CodeReuse/ActiveRecord project_to_archived_traversal_ids = project_to_archived_traversal_ids(affected_project_ids) @@ -68,7 +67,8 @@ def project_to_archived_traversal_ids(project_ids) end def update_sql(vulnerability_statistics_ids, with_project_information) - format(UPDATE_SQL, vulnerability_statistics_ids: vulnerability_statistics_ids, + format(UPDATE_SQL, + vulnerability_statistics_ids: vulnerability_statistics_ids.map { |id| Integer(id) }.join(', '), with_values: with_project_information) end end -- GitLab From 994370edb8bc3c4846dc0d7fbf44c2f6cc8e58e1 Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Mon, 20 Jan 2025 11:03:20 +0200 Subject: [PATCH 10/11] Added _sql suffix method returning project values --- ...ll_archived_and_traversal_ids_to_vulnerability_statistics.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb index 28505d3a6ea1c3..0d4ee95411caed 100644 --- a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb +++ b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -50,7 +50,7 @@ class Project < ::Gitlab::Database::Migration[2.2]::MigrationRecord # rubocop: enable CodeReuse/ActiveRecord end - def project_to_archived_traversal_ids(project_ids) + def project_ids_to_values_sql(project_ids) # Query main DB for namespace and traversal_ids for affected projects by id project_info = Project.archived_and_traversal_ids(project_ids) return if project_info.blank? -- GitLab From 5d46bdfbe3cba4dd7980204da6f06afcfed46835 Mon Sep 17 00:00:00 2001 From: Schmil Monderer Date: Mon, 20 Jan 2025 09:20:51 +0000 Subject: [PATCH 11/11] Rename call to project_to_archived_traversal_ids --- ...rchived_and_traversal_ids_to_vulnerability_statistics.rb | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb index 0d4ee95411caed..974e8a3c12d944 100644 --- a/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb +++ b/lib/gitlab/background_migration/backfill_archived_and_traversal_ids_to_vulnerability_statistics.rb @@ -27,12 +27,12 @@ def perform # rubocop:disable CodeReuse/ActiveRecord -- specific for this backfill vulnerability_statistics_ids, affected_project_ids = sub_batch.pluck(:id, :project_id).transpose # rubocop:enable CodeReuse/ActiveRecord - project_to_archived_traversal_ids = project_to_archived_traversal_ids(affected_project_ids) + values_sql = project_ids_to_values_sql(affected_project_ids) - next if project_to_archived_traversal_ids.blank? + next if values_sql.blank? ::Gitlab::Database::SecApplicationRecord.connection.execute(update_sql(vulnerability_statistics_ids, - project_to_archived_traversal_ids)) + values_sql)) end end -- GitLab