From 2faae52ce1244549f32c4e7e796e200c31f34f74 Mon Sep 17 00:00:00 2001 From: =?utf8?q?L=C3=A9o=20Geoffroy?= Date: Thu, 25 May 2023 17:46:25 +0200 Subject: [PATCH] SONAR-19407 Migrate subportfolios to realign with component uuids --- .../migration/version/v101/DbVersion101.java | 3 +- .../FixDifferentUuidsForSubportfolios.java | 59 +++++++ ...FixDifferentUuidsForSubportfoliosTest.java | 166 ++++++++++++++++++ .../schema.sql | 71 ++++++++ 4 files changed, 298 insertions(+), 1 deletion(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfolios.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfoliosTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfoliosTest/schema.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java index 187036c4be2..7f381029eaf 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/DbVersion101.java @@ -58,6 +58,7 @@ public class DbVersion101 implements DbVersion { .add(10_1_014, "Increase size of 'ce_activity.task_type' from 15 to 40 characters", IncreaseTaskTypeColumnSizeInCeActivity.class) .add(10_1_015, "Add 'external_groups' table.", CreateExternalGroupsTable.class) .add(10_1_016, "Add index on 'external_groups(external_identity_provider, external_id).", CreateIndexOnExternalIdAndIdentityOnExternalGroupsTable.class) - .add(10_1_017, "Add 'code_variants' column in 'issues' table", AddCodeVariantsColumnInIssuesTable.class); + .add(10_1_017, "Add 'code_variants' column in 'issues' table", AddCodeVariantsColumnInIssuesTable.class) + .add(10_1_018, "Fix different uuids for subportfolios", FixDifferentUuidsForSubportfolios.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfolios.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfolios.java new file mode 100644 index 00000000000..b22b3eec48e --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfolios.java @@ -0,0 +1,59 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.Connection; +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 FixDifferentUuidsForSubportfolios extends DataChange { + private static final String SELECT_QUERY = """ + SELECT p.uuid, c.uuid + FROM portfolios p + INNER join components c on p.kee = c.kee AND p.uuid != c.uuid + and p.parent_uuid IS NOT NULL and p.root_uuid = c.branch_uuid and c.qualifier = 'SVW' + """; + + public FixDifferentUuidsForSubportfolios(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select(SELECT_QUERY); + massUpdate.update("update portfolios set parent_uuid=? where parent_uuid=?"); + massUpdate.update("update portfolios set uuid=? where uuid=?"); + massUpdate.update("update portfolio_projects set portfolio_uuid=? where portfolio_uuid=?"); + massUpdate.update("update portfolio_references set portfolio_uuid=? where portfolio_uuid=?"); + + massUpdate.execute((row, update, index) -> { + String portfolioUuid = row.getString(1); + String componentUuid = row.getString(2); + update.setString(1, componentUuid); + update.setString(2, portfolioUuid); + return true; + }); + } + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfoliosTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfoliosTest.java new file mode 100644 index 00000000000..d114249b725 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfoliosTest.java @@ -0,0 +1,166 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v101; + +import java.sql.SQLException; +import java.util.HashMap; +import java.util.Map; +import java.util.Set; +import javax.annotation.Nullable; +import org.assertj.core.api.Assertions; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.resources.Qualifiers; +import org.sonar.db.CoreDbTester; + +import static java.util.stream.Collectors.toSet; + +public class FixDifferentUuidsForSubportfoliosTest { + private static final String OLD_UUID = "differentSubPfUuid"; + private static final String SUB_PF_KEY = "subPfKey"; + private static final String NEW_SUBPF_UUID = "subPfUuid"; + private static final String PF_UUID = "pfUuid"; + private static final String NEW_CHILD_SUBPF_UUID = "childsubpfUuid"; + private static final String OLD_CHILD_SUBPF_UUID = "old_child_subpf_uuid"; + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(FixDifferentUuidsForSubportfoliosTest.class, "schema.sql"); + + private final FixDifferentUuidsForSubportfolios underTest = new FixDifferentUuidsForSubportfolios(db.database()); + + @Test + public void execute_shouldUpdatePortfoliosAndPortfolioProjectsAndPortfolioReferenceTable() throws SQLException { + insertPortfolio("pfKey", PF_UUID); + insertComponent(SUB_PF_KEY, NEW_SUBPF_UUID, PF_UUID, Qualifiers.SUBVIEW); + insertSubPortfolio(SUB_PF_KEY, PF_UUID, PF_UUID, OLD_UUID); + insertPortfolioProject("projUuid", OLD_UUID); + insertPortfolioReference("refUuid", OLD_UUID); + + underTest.execute(); + + Assertions.assertThat(findValueIn("portfolios", "UUID")).containsExactlyInAnyOrder(PF_UUID, NEW_SUBPF_UUID); + Assertions.assertThat(findValueIn("portfolio_projects", "PORTFOLIO_UUID")).containsExactlyInAnyOrder(NEW_SUBPF_UUID); + Assertions.assertThat(findValueIn("portfolio_references", "PORTFOLIO_UUID")).containsExactlyInAnyOrder(NEW_SUBPF_UUID); + } + + @Test + public void execute_shouldBeRentrant() throws SQLException { + insertPortfolio("pfKey", PF_UUID); + insertComponent(SUB_PF_KEY, NEW_SUBPF_UUID, PF_UUID, Qualifiers.SUBVIEW); + insertSubPortfolio(SUB_PF_KEY, PF_UUID, PF_UUID, OLD_UUID); + insertPortfolioProject("projUuid", OLD_UUID); + insertPortfolioReference("refUuid", OLD_UUID); + + underTest.execute(); + underTest.execute(); + + Assertions.assertThat(findValueIn("portfolios", "UUID")).containsExactlyInAnyOrder(PF_UUID, NEW_SUBPF_UUID); + Assertions.assertThat(findValueIn("portfolio_projects", "PORTFOLIO_UUID")).containsExactlyInAnyOrder(NEW_SUBPF_UUID); + Assertions.assertThat(findValueIn("portfolio_references", "PORTFOLIO_UUID")).containsExactlyInAnyOrder(NEW_SUBPF_UUID); + } + + @Test + public void execute_shouldFixUuidForSubPortfolioAtDifferentLevels() throws SQLException { + insertPortfolio("pfKey", PF_UUID); + + insertComponent(SUB_PF_KEY, NEW_SUBPF_UUID, PF_UUID, Qualifiers.SUBVIEW); + insertComponent("child_subpfkey", NEW_CHILD_SUBPF_UUID, PF_UUID, Qualifiers.SUBVIEW); + + insertSubPortfolio(SUB_PF_KEY, PF_UUID, PF_UUID, OLD_UUID); + insertSubPortfolio("child_subpfkey", OLD_UUID, PF_UUID, OLD_CHILD_SUBPF_UUID); + insertPortfolioProject("projUuid", OLD_CHILD_SUBPF_UUID); + insertPortfolioReference("refUuid", OLD_CHILD_SUBPF_UUID); + + underTest.execute(); + + Assertions.assertThat(findValueIn("portfolios", "UUID")).containsExactlyInAnyOrder(PF_UUID, NEW_SUBPF_UUID, NEW_CHILD_SUBPF_UUID); + Assertions.assertThat(findValueIn("portfolios", "PARENT_UUID")).containsExactlyInAnyOrder(null, PF_UUID, NEW_SUBPF_UUID); + Assertions.assertThat(findValueIn("portfolio_projects", "PORTFOLIO_UUID")).containsExactlyInAnyOrder(NEW_CHILD_SUBPF_UUID); + Assertions.assertThat(findValueIn("portfolio_references", "PORTFOLIO_UUID")).containsExactlyInAnyOrder(NEW_CHILD_SUBPF_UUID); + } + + private Set findValueIn(String table, String field) { + return db.select(String.format("select %s FROM %s", field, table)) + .stream() + .map(row -> (String) row.get(field)) + .collect(toSet()); + } + + + private String insertComponent(String key, String uuid, String branchUuid, String qualifier) { + Map map = new HashMap<>(); + map.put("UUID", uuid); + map.put("KEE", key); + map.put("BRANCH_UUID", branchUuid); + map.put("UUID_PATH", "." + uuid + "."); + map.put("QUALIFIER", qualifier); + map.put("ENABLED", true); + map.put("PRIVATE", true); + + db.executeInsert("components", map); + return uuid; + } + + private String insertPortfolio(String kee, String uuid) { + return insertSubPortfolio(kee, uuid, uuid); + } + + private String insertSubPortfolio(String kee, String rootUuid, String uuid) { + return insertSubPortfolio(kee, null, rootUuid, uuid); + } + + private String insertSubPortfolio(String kee, @Nullable String parentUuid, String rootUuid, String uuid) { + Map map = new HashMap<>(); + map.put("UUID", uuid); + map.put("KEE", kee); + map.put("NAME", uuid); + map.put("ROOT_UUID", rootUuid); + map.put("PRIVATE", false); + map.put("SELECTION_MODE", "MANUAL"); + map.put("PARENT_UUID", parentUuid); + map.put("CREATED_AT", System.currentTimeMillis()); + map.put("UPDATED_AT", System.currentTimeMillis()); + + + db.executeInsert("portfolios", map); + return uuid; + } + + private String insertPortfolioReference(String uuid, String portfolioUuid) { + Map map = new HashMap<>(); + map.put("UUID", uuid); + map.put("PORTFOLIO_UUID", portfolioUuid); + map.put("REFERENCE_UUID", "reference"); + map.put("CREATED_AT", System.currentTimeMillis()); + + db.executeInsert("portfolio_references", map); + return uuid; + } + + private String insertPortfolioProject(String uuid, String portfolioUuid) { + Map map = new HashMap<>(); + map.put("UUID", uuid); + map.put("PORTFOLIO_UUID", portfolioUuid); + map.put("PROJECT_UUID", portfolioUuid); + map.put("CREATED_AT", System.currentTimeMillis()); + + db.executeInsert("portfolio_projects", map); + return uuid; + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfoliosTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfoliosTest/schema.sql new file mode 100644 index 00000000000..ce64fc3dda3 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v101/FixDifferentUuidsForSubportfoliosTest/schema.sql @@ -0,0 +1,71 @@ +CREATE TABLE "COMPONENTS"( + "UUID" CHARACTER VARYING(50) NOT NULL, + "KEE" CHARACTER VARYING(1000), + "DEPRECATED_KEE" CHARACTER VARYING(400), + "NAME" CHARACTER VARYING(2000), + "LONG_NAME" CHARACTER VARYING(2000), + "DESCRIPTION" CHARACTER VARYING(2000), + "ENABLED" BOOLEAN DEFAULT TRUE NOT NULL, + "SCOPE" CHARACTER VARYING(3), + "QUALIFIER" CHARACTER VARYING(10), + "PRIVATE" BOOLEAN NOT NULL, + "LANGUAGE" CHARACTER VARYING(20), + "COPY_COMPONENT_UUID" CHARACTER VARYING(50), + "PATH" CHARACTER VARYING(2000), + "UUID_PATH" CHARACTER VARYING(1500) NOT NULL, + "BRANCH_UUID" CHARACTER VARYING(50) NOT NULL, + "MAIN_BRANCH_PROJECT_UUID" CHARACTER VARYING(50), + "B_CHANGED" BOOLEAN, + "B_NAME" CHARACTER VARYING(500), + "B_LONG_NAME" CHARACTER VARYING(500), + "B_DESCRIPTION" CHARACTER VARYING(2000), + "B_ENABLED" BOOLEAN, + "B_QUALIFIER" CHARACTER VARYING(10), + "B_LANGUAGE" CHARACTER VARYING(20), + "B_COPY_COMPONENT_UUID" CHARACTER VARYING(50), + "B_PATH" CHARACTER VARYING(2000), + "B_UUID_PATH" CHARACTER VARYING(1500), + "CREATED_AT" TIMESTAMP +); +CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER" NULLS FIRST); +CREATE INDEX "IDX_MAIN_BRANCH_PRJ_UUID" ON "COMPONENTS"("MAIN_BRANCH_PROJECT_UUID" NULLS FIRST); +CREATE UNIQUE INDEX "COMPONENTS_UUID" ON "COMPONENTS"("UUID" NULLS FIRST); +CREATE INDEX "COMPONENTS_BRANCH_UUID" ON "COMPONENTS"("BRANCH_UUID" NULLS FIRST); +CREATE UNIQUE INDEX "COMPONENTS_KEE_BRANCH_UUID" ON "COMPONENTS"("KEE" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); + + +CREATE TABLE "PORTFOLIOS"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "KEE" CHARACTER VARYING(400) NOT NULL, + "NAME" CHARACTER VARYING(2000) NOT NULL, + "DESCRIPTION" CHARACTER VARYING(2000), + "ROOT_UUID" CHARACTER VARYING(40) NOT NULL, + "PARENT_UUID" CHARACTER VARYING(40), + "PRIVATE" BOOLEAN NOT NULL, + "SELECTION_MODE" CHARACTER VARYING(50) NOT NULL, + "SELECTION_EXPRESSION" CHARACTER VARYING(4000), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "BRANCH_KEY" CHARACTER VARYING(255) +); +ALTER TABLE "PORTFOLIOS" ADD CONSTRAINT "PK_PORTFOLIOS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PORTFOLIOS_KEE" ON "PORTFOLIOS"("KEE" NULLS FIRST); + +CREATE TABLE "PORTFOLIO_REFERENCES"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PORTFOLIO_UUID" CHARACTER VARYING(40) NOT NULL, + "REFERENCE_UUID" CHARACTER VARYING(40) NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "BRANCH_UUID" CHARACTER VARYING(255) +); +ALTER TABLE "PORTFOLIO_REFERENCES" ADD CONSTRAINT "PK_PORTFOLIO_REFERENCES" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PORTFOLIO_REFERENCES" ON "PORTFOLIO_REFERENCES"("PORTFOLIO_UUID" NULLS FIRST, "REFERENCE_UUID" NULLS FIRST, "BRANCH_UUID" NULLS FIRST); + +CREATE TABLE "PORTFOLIO_PROJECTS"( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PORTFOLIO_UUID" CHARACTER VARYING(40) NOT NULL, + "PROJECT_UUID" CHARACTER VARYING(40) NOT NULL, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PORTFOLIO_PROJECTS" ADD CONSTRAINT "PK_PORTFOLIO_PROJECTS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PORTFOLIO_PROJECTS" ON "PORTFOLIO_PROJECTS"("PORTFOLIO_UUID" NULLS FIRST, "PROJECT_UUID" NULLS FIRST); -- 2.39.5