From 7147ade12e435064e7ba6f881c3dc656077d114f Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Mon, 10 Jun 2019 15:59:32 +0200 Subject: [PATCH] SONAR-12146 delete orphans from table PROJECT_BRANCHES --- .../db/migration/version/v78/DbVersion78.java | 3 +- .../v78/RemoveOrphansFromProjectBranches.java | 45 ++++++++ .../version/v78/DbVersion78Test.java | 2 +- .../RemoveOrphansFromProjectBranchesTest.java | 104 ++++++++++++++++++ .../project_and_project_branches.sql | 62 +++++++++++ 5 files changed, 214 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranches.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranchesTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranchesTest/project_and_project_branches.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v78/DbVersion78.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v78/DbVersion78.java index 77651d22b04..1910c2ea02b 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v78/DbVersion78.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v78/DbVersion78.java @@ -34,6 +34,7 @@ public class DbVersion78 implements DbVersion { .add(2704, "Add InternalComponentProperties table", CreateInternalComponentPropertiesTable.class) .add(2705, "Add column snapshots.revision", AddSnapshotRevision.class) .add(2706, "Migrate revision from analysis_properties to snapshots.revision", MigrateRevision.class) - .add(2707, "Update statuses of Security Hotspots", UpdateSecurityHotspotsStatuses.class); + .add(2707, "Update statuses of Security Hotspots", UpdateSecurityHotspotsStatuses.class) + .add(2708, "Remove orphans from PROJECT_BRANCHES", RemoveOrphansFromProjectBranches.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranches.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranches.java new file mode 100644 index 00000000000..fd717ed0c70 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranches.java @@ -0,0 +1,45 @@ +/* + * SonarQube + * Copyright (C) 2009-2019 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.v78; + +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 RemoveOrphansFromProjectBranches extends DataChange { + public RemoveOrphansFromProjectBranches(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select pb.uuid from project_branches pb where not exists (select 1 from projects p where p.uuid = pb.uuid)"); + massUpdate.update("delete from project_branches where uuid=?"); + massUpdate.rowPluralName("orphans in table PROJECT_BRANCHES"); + massUpdate.execute((row, update) -> { + String uuid = row.getString(1); + + update.setString(1, uuid); + return true; + }); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v78/DbVersion78Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v78/DbVersion78Test.java index e0fc5f75175..524248a37df 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v78/DbVersion78Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v78/DbVersion78Test.java @@ -35,7 +35,7 @@ public class DbVersion78Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 8); + verifyMigrationCount(underTest, 9); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranchesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranchesTest.java new file mode 100644 index 00000000000..56bdbfeaf32 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranchesTest.java @@ -0,0 +1,104 @@ +/* + * SonarQube + * Copyright (C) 2009-2019 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.v78; + +import java.sql.SQLException; +import java.util.Random; +import java.util.stream.IntStream; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.assertj.core.api.Assertions.assertThat; + +public class RemoveOrphansFromProjectBranchesTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(RemoveOrphansFromProjectBranchesTest.class, "project_and_project_branches.sql"); + + private RemoveOrphansFromProjectBranches underTest = new RemoveOrphansFromProjectBranches(db.database()); + + @Test + public void migration_has_no_effect_on_empty_tables() throws SQLException { + String[] keeps = IntStream.range(0, 1 + new Random().nextInt(30)).mapToObj(i -> { + String uuid = "Keep_" + i; + insertIntoProjectBranches(uuid); + insertIntoProjects(uuid); + return uuid; + }) + .toArray(String[]::new); + String[] deletes = IntStream.range(0, 1 + new Random().nextInt(30)).mapToObj(i -> { + String uuid = "Dl_" + i; + insertIntoProjectBranches(uuid); + return uuid; + }) + .toArray(String[]::new); + + underTest.execute(); + + assertThat(db.select("select uuid as \"UUID\" from project_branches").stream().map(t -> t.get("UUID"))) + .containsOnly(keeps) + .doesNotContain(deletes); + } + + private void insertIntoProjects(String uuid) { + db.executeInsert("PROJECTS", + "ORGANIZATION_UUID", "org_" + uuid, + "PROJECT_UUID", "PU_" + uuid, + "UUID", uuid, + "UUID_PATH", "UP_" + uuid, + "ROOT_UUID", "R_" + uuid, + "PRIVATE", new Random().nextBoolean()); + } + + @Test + public void migration_deletes_all_rows_in_project_branches_table_if_projects_table_is_empty() throws SQLException { + IntStream.range(0, 1 + new Random().nextInt(30)).forEach(this::insertIntoProjectBranches); + + underTest.execute(); + + assertThat(db.countRowsOfTable("PROJECT_BRANCHES")).isZero(); + } + + @Test + public void db_migration_is_reentrant() throws SQLException { + migration_deletes_all_rows_in_project_branches_table_if_projects_table_is_empty(); + + underTest.execute(); + + assertThat(db.countRowsOfTable("PROJECT_BRANCHES")).isZero(); + } + + private void insertIntoProjectBranches(int i) { + String uuid = "UUID_" + i; + insertIntoProjectBranches(uuid); + } + + private void insertIntoProjectBranches(String uuid) { + int i1 = new Random().nextInt(9_000); + db.executeInsert( + "PROJECT_BRANCHES", + "UUID", uuid, + "PROJECT_UUID", "PU_" + uuid, + "KEE", "KEE_" + uuid, + "KEY_TYPE", uuid + "_KT", + "CREATED_AT", 12 + i1, + "UPDATED_AT", 500 + i1); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranchesTest/project_and_project_branches.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranchesTest/project_and_project_branches.sql new file mode 100644 index 00000000000..d8f071e7ed5 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v78/RemoveOrphansFromProjectBranchesTest/project_and_project_branches.sql @@ -0,0 +1,62 @@ +CREATE TABLE "PROJECTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400), + "UUID" VARCHAR(50) NOT NULL, + "UUID_PATH" VARCHAR(1500) NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(1500), + "MAIN_BRANCH_PROJECT_UUID" VARCHAR(50), + "NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "PRIVATE" BOOLEAN NOT NULL, + "TAGS" VARCHAR(500), + "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "DEPRECATED_KEE" VARCHAR(400), + "PATH" VARCHAR(2000), + "LANGUAGE" VARCHAR(20), + "COPY_COMPONENT_UUID" VARCHAR(50), + "LONG_NAME" VARCHAR(2000), + "DEVELOPER_UUID" VARCHAR(50), + "CREATED_AT" TIMESTAMP, + "AUTHORIZATION_UPDATED_AT" BIGINT, + "B_CHANGED" BOOLEAN, + "B_COPY_COMPONENT_UUID" VARCHAR(50), + "B_DESCRIPTION" VARCHAR(2000), + "B_ENABLED" BOOLEAN, + "B_UUID_PATH" VARCHAR(1500), + "B_LANGUAGE" VARCHAR(20), + "B_LONG_NAME" VARCHAR(500), + "B_MODULE_UUID" VARCHAR(50), + "B_MODULE_UUID_PATH" VARCHAR(1500), + "B_NAME" VARCHAR(500), + "B_PATH" VARCHAR(2000), + "B_QUALIFIER" VARCHAR(10) +); +CREATE INDEX "PROJECTS_ORGANIZATION" ON "PROJECTS" ("ORGANIZATION_UUID"); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE"); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "PROJECTS" ("ROOT_UUID"); +CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID"); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "PROJECTS" ("PROJECT_UUID"); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "PROJECTS" ("MODULE_UUID"); +CREATE INDEX "PROJECTS_QUALIFIER" ON "PROJECTS" ("QUALIFIER"); + +CREATE TABLE "PROJECT_BRANCHES" ( + "UUID" VARCHAR(50) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "KEE" VARCHAR(255) NOT NULL, + "KEY_TYPE" VARCHAR(12) NOT NULL, + "BRANCH_TYPE" VARCHAR(12), + "MERGE_BRANCH_UUID" VARCHAR(50), + "PULL_REQUEST_BINARY" BLOB, + "MANUAL_BASELINE_ANALYSIS_UUID" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_PROJECT_BRANCHES" PRIMARY KEY ("UUID") +); +CREATE UNIQUE INDEX "PROJECT_BRANCHES_KEE_KEY_TYPE" ON "PROJECT_BRANCHES" ("PROJECT_UUID", "KEE", "KEY_TYPE"); -- 2.39.5