diff --git a/db/post_migrate/20250922093654_add_integrations_index_on_id_instance_group_id_organization_id.rb b/db/post_migrate/20250922093654_add_integrations_index_on_id_instance_group_id_organization_id.rb new file mode 100644 index 0000000000000000000000000000000000000000..7a525453f73aab05be1c3fa765df9e6e5362e419 --- /dev/null +++ b/db/post_migrate/20250922093654_add_integrations_index_on_id_instance_group_id_organization_id.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class AddIntegrationsIndexOnIdInstanceGroupIdOrganizationId < Gitlab::Database::Migration[2.3] + milestone '18.5' + disable_ddl_transaction! + + INDEX_NAME = 'tmp_idx_integrations_on_id_instance_group_id_organization_id' + + def up + add_concurrent_index( + :integrations, + :id, + where: 'instance = FALSE AND group_id IS NOT NULL AND organization_id IS NOT NULL', + name: INDEX_NAME + ) + end + + def down + remove_concurrent_index_by_name :integrations, INDEX_NAME + end +end diff --git a/db/post_migrate/20250922093658_add_integrations_index_on_id_instance_project_id_organization_id.rb b/db/post_migrate/20250922093658_add_integrations_index_on_id_instance_project_id_organization_id.rb new file mode 100644 index 0000000000000000000000000000000000000000..2d2fda93f2a2af68f26bd08d957a2f95f083e0d7 --- /dev/null +++ b/db/post_migrate/20250922093658_add_integrations_index_on_id_instance_project_id_organization_id.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class AddIntegrationsIndexOnIdInstanceProjectIdOrganizationId < Gitlab::Database::Migration[2.3] + milestone '18.5' + disable_ddl_transaction! + + INDEX_NAME = 'tmp_idx_integrations_on_id_instance_project_id_organization_id' + + def up + add_concurrent_index( + :integrations, + :id, + where: 'instance = FALSE AND project_id IS NOT NULL AND organization_id IS NOT NULL', + name: INDEX_NAME + ) + end + + def down + remove_concurrent_index_by_name :integrations, INDEX_NAME + end +end diff --git a/db/post_migrate/20250922093663_backfill_group_integrations_organization_id.rb b/db/post_migrate/20250922093663_backfill_group_integrations_organization_id.rb new file mode 100644 index 0000000000000000000000000000000000000000..5664a1ec3fd4c65f6137ad35ceec400eefd6c149 --- /dev/null +++ b/db/post_migrate/20250922093663_backfill_group_integrations_organization_id.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +class BackfillGroupIntegrationsOrganizationId < Gitlab::Database::Migration[2.3] + milestone '18.5' + restrict_gitlab_migration gitlab_schema: :gitlab_main_org + disable_ddl_transaction! + + BATCH_SIZE = 50 + + def up + integrations = define_batchable_model('integrations') + + integrations + .where(instance: false) + .where.not(group_id: nil) + .where.not(organization_id: nil) + .each_batch(of: BATCH_SIZE) do |batch| + batch.update_all(organization_id: nil) + end + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20250922093667_backfill_project_integrations_organization_id.rb b/db/post_migrate/20250922093667_backfill_project_integrations_organization_id.rb new file mode 100644 index 0000000000000000000000000000000000000000..4bfbfa7733c15b1e93f4e769789e323a3609a8b2 --- /dev/null +++ b/db/post_migrate/20250922093667_backfill_project_integrations_organization_id.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +class BackfillProjectIntegrationsOrganizationId < Gitlab::Database::Migration[2.3] + milestone '18.5' + restrict_gitlab_migration gitlab_schema: :gitlab_main_org + disable_ddl_transaction! + + BATCH_SIZE = 50 + + def up + integrations = define_batchable_model('integrations') + + integrations + .where(instance: false) + .where.not(project_id: nil) + .where.not(organization_id: nil) + .each_batch(of: BATCH_SIZE) do |batch| + batch.update_all(organization_id: nil) + end + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20250922093672_integrations_validate_multiple_column_not_null_constraint.rb b/db/post_migrate/20250922093672_integrations_validate_multiple_column_not_null_constraint.rb new file mode 100644 index 0000000000000000000000000000000000000000..a9f62513ec0d0bef6899427a749fde731686ccef --- /dev/null +++ b/db/post_migrate/20250922093672_integrations_validate_multiple_column_not_null_constraint.rb @@ -0,0 +1,19 @@ +# frozen_string_literal: true + +class IntegrationsValidateMultipleColumnNotNullConstraint < Gitlab::Database::Migration[2.3] + milestone '18.5' + + CONSTRAINT_NAME = 'check_2aae034509' + + def up + validate_multi_column_not_null_constraint :integrations, + :project_id, + :group_id, + :organization_id, + constraint_name: CONSTRAINT_NAME + end + + def down + # no-op + end +end diff --git a/db/post_migrate/20250922093676_remove_integrations_index_on_id_instance_group_id_organization_id.rb b/db/post_migrate/20250922093676_remove_integrations_index_on_id_instance_group_id_organization_id.rb new file mode 100644 index 0000000000000000000000000000000000000000..76d1072798958207fca7ce9c2c79718c1e892494 --- /dev/null +++ b/db/post_migrate/20250922093676_remove_integrations_index_on_id_instance_group_id_organization_id.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class RemoveIntegrationsIndexOnIdInstanceGroupIdOrganizationId < Gitlab::Database::Migration[2.3] + milestone '18.5' + disable_ddl_transaction! + + INDEX_NAME = 'tmp_idx_integrations_on_id_instance_group_id_organization_id' + + def up + remove_concurrent_index_by_name :integrations, INDEX_NAME + end + + def down + add_concurrent_index( + :integrations, + :id, + where: 'instance = FALSE AND group_id IS NOT NULL AND organization_id IS NOT NULL', + name: INDEX_NAME + ) + end +end diff --git a/db/post_migrate/20250922093680_remove_integrations_index_on_id_instance_project_id_organization_id.rb b/db/post_migrate/20250922093680_remove_integrations_index_on_id_instance_project_id_organization_id.rb new file mode 100644 index 0000000000000000000000000000000000000000..2eb96783bf59a84df1846a567077e2804b95e4eb --- /dev/null +++ b/db/post_migrate/20250922093680_remove_integrations_index_on_id_instance_project_id_organization_id.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +class RemoveIntegrationsIndexOnIdInstanceProjectIdOrganizationId < Gitlab::Database::Migration[2.3] + milestone '18.5' + disable_ddl_transaction! + + INDEX_NAME = 'tmp_idx_integrations_on_id_instance_project_id_organization_id' + + def up + remove_concurrent_index_by_name :integrations, INDEX_NAME + end + + def down + add_concurrent_index( + :integrations, + :id, + where: 'instance = FALSE AND project_id IS NOT NULL AND organization_id IS NOT NULL', + name: INDEX_NAME + ) + end +end diff --git a/db/schema_migrations/20250922093654 b/db/schema_migrations/20250922093654 new file mode 100644 index 0000000000000000000000000000000000000000..6e8e9e41ffafee3b768ca1e6ea01445b0952e6e4 --- /dev/null +++ b/db/schema_migrations/20250922093654 @@ -0,0 +1 @@ +3aa8ad1b6fddf15abe02d8dcf27712461b5e6743f41140e90e5dba4a720b4bdc \ No newline at end of file diff --git a/db/schema_migrations/20250922093658 b/db/schema_migrations/20250922093658 new file mode 100644 index 0000000000000000000000000000000000000000..c651755aa017e986ae06ef4348b5d24cd9954d3c --- /dev/null +++ b/db/schema_migrations/20250922093658 @@ -0,0 +1 @@ +3722927c94bd30774728b67579c3081007dd04c3e6e5b3a83212f940ca99ea06 \ No newline at end of file diff --git a/db/schema_migrations/20250922093663 b/db/schema_migrations/20250922093663 new file mode 100644 index 0000000000000000000000000000000000000000..2b4b1a4734c118ce85caf5475331f045e61dc9e6 --- /dev/null +++ b/db/schema_migrations/20250922093663 @@ -0,0 +1 @@ +fc64c6cdea25a4f5e1824681e24332f9288ea7e767f0dc5d9df412b91b4b0c04 \ No newline at end of file diff --git a/db/schema_migrations/20250922093667 b/db/schema_migrations/20250922093667 new file mode 100644 index 0000000000000000000000000000000000000000..6af0b584b8f305bcc3c486b97496d5831fcaa9cf --- /dev/null +++ b/db/schema_migrations/20250922093667 @@ -0,0 +1 @@ +db3cafa3888e6135edbe142d5b1df00240e3fde32c6838cf4ecb2002107fdd57 \ No newline at end of file diff --git a/db/schema_migrations/20250922093672 b/db/schema_migrations/20250922093672 new file mode 100644 index 0000000000000000000000000000000000000000..d14d8b9ed8cc54200a539a76864c76dcd96f74ae --- /dev/null +++ b/db/schema_migrations/20250922093672 @@ -0,0 +1 @@ +a136ec1f4a35eca5d266e333fc06893cbb8da298a5ad14e41438444a234bf075 \ No newline at end of file diff --git a/db/schema_migrations/20250922093676 b/db/schema_migrations/20250922093676 new file mode 100644 index 0000000000000000000000000000000000000000..0c7a74e3a2f0c3de71225ce1b8ac3741f86b1d25 --- /dev/null +++ b/db/schema_migrations/20250922093676 @@ -0,0 +1 @@ +bcb5efc1049e1651b4bb46adcdbc4ceaf0d72e754988d259f6765a5e1716b799 \ No newline at end of file diff --git a/db/schema_migrations/20250922093680 b/db/schema_migrations/20250922093680 new file mode 100644 index 0000000000000000000000000000000000000000..b68f52093d592780c96ec27b5e745b13ca56420b --- /dev/null +++ b/db/schema_migrations/20250922093680 @@ -0,0 +1 @@ +a31c207fc01b229e3887cf9b0a81864e425eef8b3a03778a148180c42ca6c03c \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 6e9b49a2849ca7ce206c231a2fb2e2ced8649d96..cf8414891ffeeb47280a4698078ca9360f8fc545 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -18099,6 +18099,7 @@ CREATE TABLE integrations ( group_mention_events boolean DEFAULT false NOT NULL, group_confidential_mention_events boolean DEFAULT false NOT NULL, organization_id bigint, + CONSTRAINT check_2aae034509 CHECK ((num_nonnulls(group_id, organization_id, project_id) = 1)), CONSTRAINT check_a948a0aa7e CHECK ((char_length(type_new) <= 255)) ); @@ -32678,9 +32679,6 @@ ALTER TABLE epic_issues ALTER TABLE workspaces ADD CONSTRAINT check_2a89035b04 CHECK ((personal_access_token_id IS NOT NULL)) NOT VALID; -ALTER TABLE integrations - ADD CONSTRAINT check_2aae034509 CHECK ((num_nonnulls(group_id, organization_id, project_id) = 1)) NOT VALID; - ALTER TABLE security_scans ADD CONSTRAINT check_2d56d882f6 CHECK ((project_id IS NOT NULL)) NOT VALID; diff --git a/spec/migrations/backfill_group_integrations_organization_id_spec.rb b/spec/migrations/backfill_group_integrations_organization_id_spec.rb new file mode 100644 index 0000000000000000000000000000000000000000..d7d0d9cc848cd75a930e63788ee3e7f7515ddfb4 --- /dev/null +++ b/spec/migrations/backfill_group_integrations_organization_id_spec.rb @@ -0,0 +1,96 @@ +# frozen_string_literal: true + +require 'spec_helper' + +require_migration! + +RSpec.describe BackfillGroupIntegrationsOrganizationId, feature_category: :integrations do + let(:organizations) { table(:organizations) } + let(:integrations) { table(:integrations) } + let(:namespaces) { table(:namespaces) } + let(:projects) { table(:projects) } + + let!(:organization) { organizations.create!(id: 1, name: 'Default', path: 'default') } + let!(:group) { namespaces.create!(name: 'bar', path: 'bar', type: 'Group', organization_id: 1) } + + let!(:project) do + projects.create!( + name: 'baz', + path: 'baz', + organization_id: 1, + namespace_id: group.id, + project_namespace_id: group.id + ) + end + + let(:integration_to_backfill) do + integrations.create!(group_id: group.id, type_new: 'Integrations::Zentao', organization_id: organization.id) + end + + let(:another_integration_to_backfill) do + integrations.create!(group_id: group.id, type_new: 'Integrations::Telegram', organization_id: organization.id) + end + + let(:valid_integration) do + integrations.create!(group_id: group.id, type_new: 'Integrations::Discord') + end + + let(:project_integration) do + integrations.create!(project_id: project.id, type_new: 'Integrations::Discord') + end + + let(:organization_integration) do + integrations.create!(instance: true, organization_id: organization.id, type_new: 'Integrations::Zentao') + end + + before do + ApplicationRecord.connection.execute('ALTER TABLE integrations DROP CONSTRAINT IF EXISTS check_2aae034509;') + end + + after do + ApplicationRecord + .connection + .execute( + 'ALTER TABLE integrations ADD CONSTRAINT check_2aae034509 ' \ + 'CHECK ((num_nonnulls(group_id, organization_id, project_id) = 1)) NOT VALID;' + ) + end + + describe "#up" do + it 'sets organization_id to nil for group integrations that have it' do + expect(integration_to_backfill.group_id).to eq(group.id) + expect(integration_to_backfill.organization_id).to eq(organization.id) + + expect(another_integration_to_backfill.group_id).to eq(group.id) + expect(another_integration_to_backfill.organization_id).to eq(organization.id) + + expect(valid_integration.group_id).to eq(group.id) + expect(valid_integration.organization_id).to be_nil + + expect(project_integration.project_id).to eq(project.id) + expect(project_integration.organization_id).to be_nil + + expect(organization_integration.organization_id).to eq(organization.id) + expect(organization_integration.project_id).to be_nil + expect(organization_integration.group_id).to be_nil + + migrate! + + expect(integration_to_backfill.reload.group_id).to eq(group.id) + expect(integration_to_backfill.reload.organization_id).to be_nil + + expect(another_integration_to_backfill.reload.group_id).to eq(group.id) + expect(another_integration_to_backfill.reload.organization_id).to be_nil + + expect(valid_integration.reload.group_id).to eq(group.id) + expect(valid_integration.reload.organization_id).to be_nil + + expect(project_integration.reload.project_id).to eq(project.id) + expect(project_integration.reload.organization_id).to be_nil + + expect(organization_integration.reload.organization_id).to eq(organization.id) + expect(organization_integration.reload.project_id).to be_nil + expect(organization_integration.reload.group_id).to be_nil + end + end +end diff --git a/spec/migrations/backfill_project_integrations_organization_id_spec.rb b/spec/migrations/backfill_project_integrations_organization_id_spec.rb new file mode 100644 index 0000000000000000000000000000000000000000..a618f7217645b940d8007f269696cfead27c154f --- /dev/null +++ b/spec/migrations/backfill_project_integrations_organization_id_spec.rb @@ -0,0 +1,96 @@ +# frozen_string_literal: true + +require 'spec_helper' + +require_migration! + +RSpec.describe BackfillProjectIntegrationsOrganizationId, feature_category: :integrations do + let(:organizations) { table(:organizations) } + let(:integrations) { table(:integrations) } + let(:namespaces) { table(:namespaces) } + let(:projects) { table(:projects) } + + let!(:organization) { organizations.create!(id: 1, name: 'Default', path: 'default') } + let!(:group) { namespaces.create!(name: 'bar', path: 'bar', type: 'Group', organization_id: 1) } + + let!(:project) do + projects.create!( + name: 'baz', + path: 'baz', + organization_id: 1, + namespace_id: group.id, + project_namespace_id: group.id + ) + end + + let(:integration_to_backfill) do + integrations.create!(project_id: project.id, type_new: 'Integrations::Zentao', organization_id: organization.id) + end + + let(:another_integration_to_backfill) do + integrations.create!(project_id: project.id, type_new: 'Integrations::Telegram', organization_id: organization.id) + end + + let(:valid_integration) do + integrations.create!(project_id: project.id, type_new: 'Integrations::Discord') + end + + let(:group_integration) do + integrations.create!(group_id: group.id, type_new: 'Integrations::Discord') + end + + let(:organization_integration) do + integrations.create!(instance: true, organization_id: organization.id, type_new: 'Integrations::Zentao') + end + + before do + ApplicationRecord.connection.execute('ALTER TABLE integrations DROP CONSTRAINT IF EXISTS check_2aae034509;') + end + + after do + ApplicationRecord + .connection + .execute( + 'ALTER TABLE integrations ADD CONSTRAINT check_2aae034509 ' \ + 'CHECK ((num_nonnulls(group_id, organization_id, project_id) = 1)) NOT VALID;' + ) + end + + describe "#up" do + it 'sets organization_id to nil for group integrations that have it' do + expect(integration_to_backfill.project_id).to eq(project.id) + expect(integration_to_backfill.organization_id).to eq(organization.id) + + expect(another_integration_to_backfill.project_id).to eq(project.id) + expect(another_integration_to_backfill.organization_id).to eq(organization.id) + + expect(valid_integration.project_id).to eq(project.id) + expect(valid_integration.organization_id).to be_nil + + expect(group_integration.group_id).to eq(group.id) + expect(group_integration.organization_id).to be_nil + + expect(organization_integration.organization_id).to eq(organization.id) + expect(organization_integration.project_id).to be_nil + expect(organization_integration.group_id).to be_nil + + migrate! + + expect(integration_to_backfill.reload.project_id).to eq(project.id) + expect(integration_to_backfill.reload.organization_id).to be_nil + + expect(another_integration_to_backfill.reload.project_id).to eq(project.id) + expect(another_integration_to_backfill.reload.organization_id).to be_nil + + expect(valid_integration.reload.project_id).to eq(project.id) + expect(valid_integration.reload.organization_id).to be_nil + + expect(group_integration.reload.group_id).to eq(group.id) + expect(group_integration.reload.organization_id).to be_nil + + expect(organization_integration.reload.organization_id).to eq(organization.id) + expect(organization_integration.reload.project_id).to be_nil + expect(organization_integration.reload.group_id).to be_nil + end + end +end