From a9cd8d9ab2ed78c580af5283f36d581648168a09 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Tue, 30 May 2017 15:00:22 +0200 Subject: [PATCH] SONAR-9435 add db migration to clean orphans in PROJECT_LINKS --- .../v65/CleanOrphanRowsInProjectLinks.java | 76 ++++++++++++ .../db/migration/version/v65/DbVersion65.java | 3 +- .../CleanOrphanRowsInProjectLinksTest.java | 115 ++++++++++++++++++ .../version/v65/DbVersion65Test.java | 2 +- .../project_links_and_projects.sql | 53 ++++++++ 5 files changed, 247 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinks.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinksTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinksTest/project_links_and_projects.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinks.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinks.java new file mode 100644 index 00000000000..4edbe8b7e6a --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinks.java @@ -0,0 +1,76 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +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; +import org.sonar.server.platform.db.migration.step.Select; +import org.sonar.server.platform.db.migration.step.SqlStatement; + +/** + * Delete rows from table PROJECT_LINKS which either: + * + */ +public class CleanOrphanRowsInProjectLinks extends DataChange { + public CleanOrphanRowsInProjectLinks(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select" + + " pl.id" + + " from project_links pl" + + " where" + + " not exists (" + + " select" + + " 1" + + " from projects" + + " p" + + " where" + + " p.uuid = pl.component_uuid" + + " and p.scope = ?" + + " and p.qualifier in (?,?)" + + " and p.enabled = ?" + + " )") + .setString(1, "PRJ") + .setString(2, "TRK") + .setString(3, "VW") + .setBoolean(4, true); + massUpdate.update("delete from project_links where id = ?"); + massUpdate.rowPluralName("orphan project links"); + massUpdate.execute(CleanOrphanRowsInProjectLinks::handle); + } + + private static boolean handle(Select.Row row, SqlStatement update) throws SQLException { + long id = row.getLong(1); + + update.setLong(1, id); + + return true; + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java index b5dcb1b5cb9..41cefababb8 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java @@ -34,6 +34,7 @@ public class DbVersion65 implements DbVersion { .add(1704, "Drop index EVENTS_COMPONENT_UUID", DropIndexEventsComponentUuid.class) .add(1705, "Make EVENTS.COMPONENT_UUID not nullable", MakeEventsComponentUuidNotNullable.class) .add(1706, "Recreate index EVENTS_COMPONENT_UUID", RecreateIndexEventsComponentUuid.class) - .add(1707, "Ensure ISSUE.PROJECT_UUID is consistent", EnsureIssueProjectUuidConsistencyOnIssues.class); + .add(1707, "Ensure ISSUE.PROJECT_UUID is consistent", EnsureIssueProjectUuidConsistencyOnIssues.class) + .add(1708, "Clean orphans from PROJECT_LINKS", CleanOrphanRowsInProjectLinks.class); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinksTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinksTest.java new file mode 100644 index 00000000000..12e785fb132 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinksTest.java @@ -0,0 +1,115 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v65; + +import java.sql.SQLException; +import java.util.Random; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic; +import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric; +import static org.assertj.core.api.Assertions.assertThat; + +public class CleanOrphanRowsInProjectLinksTest { + private static final String TABLE_PROJECT_LINKS = "project_links"; + private static final String SCOPE_PROJECT = "PRJ"; + private static final String QUALIFIER_VIEW = "VW"; + private static final String QUALIFIER_PROJECT = "TRK"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(CleanOrphanRowsInProjectLinksTest.class, "project_links_and_projects.sql"); + + private final Random random = new Random(); + private CleanOrphanRowsInProjectLinks underTest = new CleanOrphanRowsInProjectLinks(db.database()); + + @Test + public void execute_has_no_effect_when_table_is_empty() throws SQLException { + underTest.execute(); + } + + @Test + public void execute_remove_all_data_from_project_links_if_projects_is_empty() throws SQLException { + String componentUuid = randomAlphanumeric(6); + insertProjectLink(componentUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROJECT_LINKS)).isZero(); + } + + @Test + public void execute_remove_row_from_project_links_which_component_is_not_an_enabled_project_or_view() throws SQLException { + Long link1 = insertProjectLink(insertComponent(SCOPE_PROJECT, QUALIFIER_PROJECT, true)); + Long link2 = insertProjectLink(insertComponent(SCOPE_PROJECT, QUALIFIER_VIEW, true)); + insertProjectLink(insertComponent(SCOPE_PROJECT, QUALIFIER_PROJECT, false)); + insertProjectLink(insertComponent(SCOPE_PROJECT, QUALIFIER_VIEW, false)); + insertProjectLink(insertComponent(randomAlphabetic(3), QUALIFIER_PROJECT, true)); + insertProjectLink(insertComponent(randomAlphabetic(3), QUALIFIER_PROJECT, false)); + insertProjectLink(insertComponent(randomAlphabetic(3), QUALIFIER_VIEW, true)); + insertProjectLink(insertComponent(randomAlphabetic(3), QUALIFIER_VIEW, false)); + insertProjectLink(insertComponent(SCOPE_PROJECT, randomAlphabetic(3), true)); + insertProjectLink(insertComponent(SCOPE_PROJECT, randomAlphabetic(3), false)); + + underTest.execute(); + + assertThat(db.select("select id as \"ID\" from project_links").stream().map(row -> ((Long) row.get("ID")))) + .containsOnly(link1, link2); + } + + @Test + public void execute_removes_row_from_project_links_which_component_does_not_exist() throws SQLException { + String projectUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_PROJECT, true); + insertProjectLink(projectUuid + "_2"); + long linkId = insertProjectLink(projectUuid); + + underTest.execute(); + + assertThat(db.select("select id as \"ID\" from project_links").stream().map(row -> ((Long) row.get("ID")))) + .containsOnly(linkId); + } + + private long insertProjectLink(String componentUuid) { + String href = randomAlphanumeric(30); + db.executeInsert( + TABLE_PROJECT_LINKS, + "component_uuid", componentUuid, + "href", href); + return (long) db.selectFirst("select id as \"ID\" from project_links where component_uuid = '" + componentUuid + "' and href='" + href + "'") + .get("ID"); + } + + private String insertComponent(String scope, String qualifier, boolean enabled) { + String uuid = randomAlphabetic(5); + db.executeInsert( + "PROJECTS", + "ORGANIZATION_UUID", randomAlphabetic(5), + "UUID", uuid, + "UUID_PATH", "path_" + uuid, + "ROOT_UUID", "root_uuid_" + uuid, + "PROJECT_UUID", randomAlphabetic(5), + "SCOPE", scope, + "QUALIFIER", qualifier, + "PRIVATE", String.valueOf(random.nextBoolean()), + "ENABLED", String.valueOf(enabled)); + return uuid; + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java index f63f9942ec0..c58409eb432 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java @@ -35,6 +35,6 @@ public class DbVersion65Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 8); + verifyMigrationCount(underTest, 9); } } diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinksTest/project_links_and_projects.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinksTest/project_links_and_projects.sql new file mode 100644 index 00000000000..78a37841c57 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInProjectLinksTest/project_links_and_projects.sql @@ -0,0 +1,53 @@ +CREATE TABLE "PROJECT_LINKS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "COMPONENT_UUID" VARCHAR(50), + "LINK_TYPE" VARCHAR(20), + "NAME" VARCHAR(128), + "HREF" VARCHAR(2048) NOT NULL +); + +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), + "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"); -- 2.39.5