From 63a1ca96eb962563528336b0813abac13d521bf6 Mon Sep 17 00:00:00 2001 From: Jacek Date: Thu, 30 Jul 2020 12:16:49 +0200 Subject: [PATCH] SONAR-13698 Delete 'project_alm_settings' rows when project is deleted --- .../org/sonar/db/purge/PurgeCommands.java | 8 ++ .../java/org/sonar/db/purge/PurgeDao.java | 1 + .../java/org/sonar/db/purge/PurgeMapper.java | 1 + .../org/sonar/db/purge/PurgeMapper.xml | 4 + .../java/org/sonar/db/purge/PurgeDaoTest.java | 17 ++- .../MigrationConfigurationModule.java | 2 + .../db/migration/version/v85/DbVersion85.java | 31 +++++ .../v85/DeleteProjectAlmSettingsOrphans.java | 46 ++++++++ .../version/v85/DbVersion85Test.java | 42 +++++++ .../DeleteProjectAlmSettingsOrphansTest.java | 108 ++++++++++++++++++ .../schema.sql | 30 +++++ 11 files changed, 289 insertions(+), 1 deletion(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphans.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85Test.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphansTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphansTest/schema.sql diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java index 8f3e4309222..cba1d524e1d 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeCommands.java @@ -406,6 +406,13 @@ class PurgeCommands { profiler.stop(); } + public void deleteProjectAlmSettings(String rootUuid) { + profiler.start("deleteProjectAlmSettings (project_alm_settings)"); + purgeMapper.deleteProjectAlmSettingsByProjectUuid(rootUuid); + session.commit(); + profiler.stop(); + } + void deleteBranch(String rootUuid) { profiler.start("deleteBranch (project_branches)"); purgeMapper.deleteBranchByUuid(rootUuid); @@ -426,4 +433,5 @@ class PurgeCommands { session.commit(); profiler.stop(); } + } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java index 10a2e5ba01a..3b51bd337f1 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeDao.java @@ -203,6 +203,7 @@ public class PurgeDao implements Dao { commands.deleteLiveMeasures(rootUuid); commands.deleteProjectMappings(rootUuid); commands.deleteProjectAlmBindings(rootUuid); + commands.deleteProjectAlmSettings(rootUuid); commands.deletePermissions(rootUuid); commands.deleteNewCodePeriods(rootUuid); commands.deleteBranch(rootUuid); diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java index b269b5010c1..1ce7f336a0b 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/purge/PurgeMapper.java @@ -149,4 +149,5 @@ public interface PurgeMapper { void deleteNewCodePeriodsByRootUuid(String rootUuid); + void deleteProjectAlmSettingsByProjectUuid(@Param("projectUuid") String projectUuid); } diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml index 17453714d35..98ec062eb74 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/purge/PurgeMapper.xml @@ -489,6 +489,10 @@ delete from project_mappings where project_uuid=#{projectUuid,jdbcType=VARCHAR} + + delete from project_alm_settings where project_uuid=#{projectUuid,jdbcType=VARCHAR} + + delete from project_alm_bindings where project_uuid=#{projectUuid,jdbcType=VARCHAR} diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeDaoTest.java index de9d82c07bd..31c684af97e 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeDaoTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/purge/PurgeDaoTest.java @@ -48,6 +48,7 @@ import org.sonar.db.DbClient; import org.sonar.db.DbSession; import org.sonar.db.DbTester; import org.sonar.db.alm.ALM; +import org.sonar.db.alm.setting.AlmSettingDto; import org.sonar.db.ce.CeActivityDto; import org.sonar.db.ce.CeQueueDto; import org.sonar.db.ce.CeQueueDto.Status; @@ -335,7 +336,7 @@ public class PurgeDaoTest { // note: projectEvent3 has no component change // delete non existing analysis has no effect - underTest.deleteAnalyses(dbSession, new PurgeProfiler(), singletonList( "foo")); + underTest.deleteAnalyses(dbSession, new PurgeProfiler(), singletonList("foo")); assertThat(uuidsIn("event_component_changes", "event_analysis_uuid")) .containsOnly(projectAnalysis1.getUuid(), projectAnalysis2.getUuid()); assertThat(db.countRowsOfTable("event_component_changes")) @@ -1237,6 +1238,20 @@ public class PurgeDaoTest { assertThat(dbClient.projectAlmBindingsDao().findProjectKey(dbSession, alm, otherRepoId)).isNotEmpty(); } + @Test + public void deleteProject_deletes_project_alm_settings() { + ProjectDto project = db.components().insertPublicProjectDto(); + ProjectDto otherProject = db.components().insertPublicProjectDto(); + AlmSettingDto almSettingDto = db.almSettings().insertGitlabAlmSetting(); + db.almSettings().insertGitlabProjectAlmSetting(almSettingDto, project); + db.almSettings().insertGitlabProjectAlmSetting(almSettingDto, otherProject); + + underTest.deleteProject(dbSession, project.getUuid()); + + assertThat(dbClient.projectAlmSettingDao().selectByProject(dbSession, project)).isEmpty(); + assertThat(dbClient.projectAlmSettingDao().selectByProject(dbSession, otherProject)).isNotEmpty(); + } + @Test public void deleteNonRootComponents_has_no_effect_when_parameter_is_empty() { DbSession dbSession = mock(DbSession.class); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java index 5732d5dcbfb..afca486ff34 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java @@ -32,6 +32,7 @@ import org.sonar.server.platform.db.migration.version.v83.DbVersion83; import org.sonar.server.platform.db.migration.version.v84.DbVersion84; import org.sonar.server.platform.db.migration.version.v84.util.DropPrimaryKeySqlGenerator; import org.sonar.server.platform.db.migration.version.v84.util.SqlHelper; +import org.sonar.server.platform.db.migration.version.v85.DbVersion85; public class MigrationConfigurationModule extends Module { @Override @@ -44,6 +45,7 @@ public class MigrationConfigurationModule extends Module { DbVersion82.class, DbVersion83.class, DbVersion84.class, + DbVersion85.class, // migration steps MigrationStepRegistryImpl.class, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java new file mode 100644 index 00000000000..3945b46b195 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85.java @@ -0,0 +1,31 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v85; + +import org.sonar.server.platform.db.migration.step.MigrationStepRegistry; +import org.sonar.server.platform.db.migration.version.DbVersion; + +public class DbVersion85 implements DbVersion { + @Override + public void addSteps(MigrationStepRegistry registry) { + registry + .add(4000, "Delete 'project_alm_settings' orphans", DeleteProjectAlmSettingsOrphans.class); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphans.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphans.java new file mode 100644 index 00000000000..246ce9d2f62 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphans.java @@ -0,0 +1,46 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v85; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class DeleteProjectAlmSettingsOrphans extends DataChange { + + public DeleteProjectAlmSettingsOrphans(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select pas.uuid from project_alm_settings pas left join " + + "projects p on pas.project_uuid = p.uuid where p.uuid is null;"); + massUpdate.update("delete from project_alm_settings where uuid = ?"); + + massUpdate.execute((row, update) -> { + String notExistingProjectUuid = row.getString(1); + update.setString(1, notExistingProjectUuid); + return true; + }); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85Test.java new file mode 100644 index 00000000000..af8dd778519 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DbVersion85Test.java @@ -0,0 +1,42 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v85; + +import org.junit.Test; +import org.sonar.server.platform.db.migration.version.DbVersion; + +import static org.sonar.server.platform.db.migration.version.DbVersionTestUtils.verifyMigrationNotEmpty; +import static org.sonar.server.platform.db.migration.version.DbVersionTestUtils.verifyMinimumMigrationNumber; + +public class DbVersion85Test { + + private DbVersion underTest = new DbVersion85(); + + @Test + public void migrationNumber_starts_at_4000() { + verifyMinimumMigrationNumber(underTest, 4000); + } + + @Test + public void verify_migration_count() { + verifyMigrationNotEmpty(underTest); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphansTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphansTest.java new file mode 100644 index 00000000000..ee46b5f6c53 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphansTest.java @@ -0,0 +1,108 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info AT sonarsource DOT com + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public License + * along with this program; if not, write to the Free Software Foundation, + * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + */ +package org.sonar.server.platform.db.migration.version.v85; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.core.util.Uuids; +import org.sonar.db.CoreDbTester; + +import static org.assertj.core.api.Assertions.assertThat; + +public class DeleteProjectAlmSettingsOrphansTest { + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DeleteProjectAlmSettingsOrphansTest.class, "schema.sql"); + + private DeleteProjectAlmSettingsOrphans underTest = new DeleteProjectAlmSettingsOrphans(db.database()); + + @Test + public void execute_migration() throws SQLException { + String projectUuid1 = insertProject(); + String projectAlmSetting1 = insertProjectAlmSetting(projectUuid1); + String projectUuid2 = insertProject(); + String projectAlmSetting2 = insertProjectAlmSetting(projectUuid2); + String projectUuid3 = insertProject(); + String projectAlmSetting3 = insertProjectAlmSetting(projectUuid3); + + // create orphans + insertProjectAlmSetting(); + insertProjectAlmSetting(); + insertProjectAlmSetting(); + insertProjectAlmSetting(); + + underTest.execute(); + + assertProjectAlmSettingsRowsExist(projectAlmSetting1, projectAlmSetting2, projectAlmSetting3); + } + + private void assertProjectAlmSettingsRowsExist(String... projectAlmSettings) { + assertThat(db.select("select uuid from project_alm_settings") + .stream() + .map(rows -> rows.get("UUID"))).containsOnly(projectAlmSettings); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + String projectUuid = insertProject(); + String projectAlmSetting = insertProjectAlmSetting(projectUuid); + // create orphans + insertProjectAlmSetting(); + insertProjectAlmSetting(); + insertProjectAlmSetting(); + insertProjectAlmSetting(); + + underTest.execute(); + // should be re-entrant + underTest.execute(); + + assertProjectAlmSettingsRowsExist(projectAlmSetting); + } + + private String insertProjectAlmSetting(String projectUuid) { + String uuid = Uuids.createFast(); + db.executeInsert("PROJECT_ALM_SETTINGS", + "UUID", uuid, + "ALM_SETTING_UUID", uuid + "-name", + "PROJECT_UUID", projectUuid, + "UPDATED_AT", System2.INSTANCE.now(), + "CREATED_AT", System2.INSTANCE.now()); + return uuid; + } + + private String insertProjectAlmSetting() { + String notExistingProjectUuid = Uuids.createFast(); + return insertProjectAlmSetting(notExistingProjectUuid); + } + + private String insertProject() { + String uuid = Uuids.createFast(); + db.executeInsert("PROJECTS", + "UUID", uuid, + "KEE", uuid + "-key", + "QUALIFIER", "TRK", + "ORGANIZATION_UUID", uuid + "-key", + "PRIVATE", Boolean.toString(false), + "UPDATED_AT", System.currentTimeMillis()); + return uuid; + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphansTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphansTest/schema.sql new file mode 100644 index 00000000000..9f63206c4c5 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v85/DeleteProjectAlmSettingsOrphansTest/schema.sql @@ -0,0 +1,30 @@ +CREATE TABLE "PROJECTS"( + "UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400) NOT NULL, + "QUALIFIER" VARCHAR(10) NOT NULL, + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "PRIVATE" BOOLEAN NOT NULL, + "TAGS" VARCHAR(500), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PROJECTS" ADD CONSTRAINT "PK_NEW_PROJECTS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PROJECTS_KEE" ON "PROJECTS"("KEE"); +CREATE INDEX "IDX_QUALIFIER" ON "PROJECTS"("QUALIFIER"); + +CREATE TABLE "PROJECT_ALM_SETTINGS"( + "UUID" VARCHAR(40) NOT NULL, + "ALM_SETTING_UUID" VARCHAR(40) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "ALM_REPO" VARCHAR(256), + "ALM_SLUG" VARCHAR(256), + "UPDATED_AT" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "SUMMARY_COMMENT_ENABLED" BOOLEAN +); +ALTER TABLE "PROJECT_ALM_SETTINGS" ADD CONSTRAINT "PK_PROJECT_ALM_SETTINGS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PROJECT_ALM_SETTINGS" ON "PROJECT_ALM_SETTINGS"("PROJECT_UUID"); +CREATE INDEX "PROJECT_ALM_SETTINGS_ALM" ON "PROJECT_ALM_SETTINGS"("ALM_SETTING_UUID"); +CREATE INDEX "PROJECT_ALM_SETTINGS_SLUG" ON "PROJECT_ALM_SETTINGS"("ALM_SLUG"); -- 2.39.5