SONAR-9328 clean orphans in MANUAL_MEASURES

5 files changed, 299 insertions, 2 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasures.java
new file mode 100644
index 00000000000..dfd9c267fa6
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasures.java
@@ -0,0 +1,86 @@
+/*
+ * 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;
+
+/**
+ * Deletes rows from table MANUAL_MEASURES which:
+ * <ul>
+ *   <li>has no component uuid</li>
+ *   <li>or reference a non existing component</li>
+ *   <li>or reference a disabled component</li>
+ *   <li>or reference a component which is neither a project, nor a view, nor a module nor a subview</li>
+ * </ul>
+ */
+public class CleanOrphanRowsInManualMeasures extends DataChange {
+
+  private static final String SCOPE_PROJECT = "PRJ";
+  private static final String QUALIFIER_PROJECT = "TRK";
+  private static final String QUALIFIER_VIEW = "VW";
+  private static final String QUALIFIER_MODULE = "BRC";
+  private static final String QUALIFIER_SUBVIEW = "SVW";
+
+  public CleanOrphanRowsInManualMeasures(Database db) {
+    super(db);
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select" +
+      "   mm.id" +
+      " from manual_measures mm" +
+      " where" +
+      "   mm.component_uuid is null" +
+      "   or not exists (" +
+      "     select" +
+      "       1" +
+      "     from projects p" +
+      "     where" +
+      "       p.uuid = mm.component_uuid" +
+      "       and p.scope = ?" +
+      "       and p.qualifier in (?,?,?,?)" +
+      "       and p.enabled = ?" +
+      "  )")
+      .setString(1, SCOPE_PROJECT)
+      .setString(2, QUALIFIER_PROJECT)
+      .setString(3, QUALIFIER_VIEW)
+      .setString(4, QUALIFIER_MODULE)
+      .setString(5, QUALIFIER_SUBVIEW)
+      .setBoolean(6, true);
+    massUpdate.update("delete from manual_measures where id = ?");
+    massUpdate.rowPluralName("orphan properties");
+    massUpdate.execute(CleanOrphanRowsInManualMeasures::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 764f354370e..b75a7d1ecee 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
@@ -36,6 +36,7 @@ public class DbVersion65 implements DbVersion {
       .add(1706, "Recreate index EVENTS_COMPONENT_UUID", RecreateIndexEventsComponentUuid.class)
       .add(1707, "Ensure ISSUE.PROJECT_UUID is consistent", EnsureIssueProjectUuidConsistencyOnIssues.class)
       .add(1708, "Clean orphans from PROJECT_LINKS", CleanOrphanRowsInProjectLinks.class)
-      .add(1709, "Clean orphans from SETTINGS", CleanOrphanRowsInProperties.class);
+      .add(1709, "Clean orphans from SETTINGS", CleanOrphanRowsInProperties.class)
+      .add(1710, "Clean orphans from MANUAL_MEASURES", CleanOrphanRowsInManualMeasures.class);
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasuresTest.java
new file mode 100644
index 00000000000..ed2d7117ed2
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasuresTest.java
@@ -0,0 +1,152 @@
+/*
+ * 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 javax.annotation.Nullable;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.core.util.UuidFactoryFast;
+import org.sonar.db.CoreDbTester;
+
+import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic;
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class CleanOrphanRowsInManualMeasuresTest {
+
+  private static final String TABLE_MANUAL_MEASURES = "MANUAL_MEASURES";
+  private static final String SCOPE_PROJECT = "PRJ";
+  private static final String QUALIFIER_PROJECT = "TRK";
+  private static final String QUALIFIER_VIEW = "VW";
+  private static final String QUALIFIER_MODULE = "BRC";
+  private static final String QUALIFIER_SUBVIEW = "SVW";
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(CleanOrphanRowsInManualMeasuresTest.class, "manual_measures_and_projects.sql");
+
+  private final Random random = new Random();
+  private CleanOrphanRowsInManualMeasures underTest = new CleanOrphanRowsInManualMeasures(db.database());
+
+  @Test
+  public void execute_has_no_effect_if_MANUAL_MEASURES_is_empty() throws SQLException {
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_deletes_all_rows_in_MANUAL_MEASURES_when_PROJECTS_is_empty() throws SQLException {
+    insertManualMeasure(randomAlphabetic(5));
+    insertManualMeasure(null);
+    insertManualMeasure(randomAlphabetic(5));
+    insertManualMeasure(null);
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_MANUAL_MEASURES)).isZero();
+  }
+
+  @Test
+  public void execute_deletes_rows_without_component_uuid() throws SQLException {
+    insertManualMeasure(null);
+    insertManualMeasure(null);
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_MANUAL_MEASURES)).isZero();
+  }
+
+  @Test
+  public void execute_deletes_rows_which_component_does_not_exist() throws SQLException {
+    insertManualMeasure(randomAlphabetic(3));
+    insertManualMeasure(randomAlphabetic(6));
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_MANUAL_MEASURES)).isZero();
+  }
+
+  @Test
+  public void execute_deletes_rows_which_component_is_not_a_project_nor_a_view_nor_a_module_nor_a_subview() throws SQLException {
+    Long[] validMeasureIds = {
+      insertManualMeasure(insertComponent(SCOPE_PROJECT, QUALIFIER_PROJECT, true)),
+      insertManualMeasure(insertComponent(SCOPE_PROJECT, QUALIFIER_VIEW, true)),
+      insertManualMeasure(insertComponent(SCOPE_PROJECT, QUALIFIER_MODULE, true)),
+      insertManualMeasure(insertComponent(SCOPE_PROJECT, QUALIFIER_SUBVIEW, true))
+    };
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, QUALIFIER_PROJECT, false));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, QUALIFIER_VIEW, false));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, QUALIFIER_MODULE, false));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, QUALIFIER_SUBVIEW, false));
+    String invalidScope = randomAlphabetic(3);
+    insertManualMeasure(insertComponent(invalidScope, QUALIFIER_PROJECT, true));
+    insertManualMeasure(insertComponent(invalidScope, QUALIFIER_VIEW, true));
+    insertManualMeasure(insertComponent(invalidScope, QUALIFIER_MODULE, true));
+    insertManualMeasure(insertComponent(invalidScope, QUALIFIER_SUBVIEW, true));
+    insertManualMeasure(insertComponent(invalidScope, QUALIFIER_PROJECT, false));
+    insertManualMeasure(insertComponent(invalidScope, QUALIFIER_VIEW, false));
+    insertManualMeasure(insertComponent(invalidScope, QUALIFIER_MODULE, false));
+    insertManualMeasure(insertComponent(invalidScope, QUALIFIER_SUBVIEW, false));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, "DIR", true));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, "FIL", true));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, "DIR", false));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, "FIL", false));
+    insertManualMeasure(insertComponent("DIR", "DIR", true));
+    insertManualMeasure(insertComponent("FIL", "FIL", true));
+    insertManualMeasure(insertComponent("FIL", "TRK", true));
+    insertManualMeasure(insertComponent("DIR", "DIR", false));
+    insertManualMeasure(insertComponent("FIL", "FIL", false));
+    insertManualMeasure(insertComponent("FIL", "TRK", false));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, randomAlphabetic(3), true));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, randomAlphabetic(3), true));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, randomAlphabetic(3), false));
+    insertManualMeasure(insertComponent(SCOPE_PROJECT, randomAlphabetic(3), false));
+
+    underTest.execute();
+
+    assertThat(db.select("select id as \"ID\" from " + TABLE_MANUAL_MEASURES).stream().map(row -> (Long) row.get("ID")))
+      .containsOnly(validMeasureIds);
+  }
+
+  private long insertManualMeasure(@Nullable String componentUuid) {
+    String uuid = UuidFactoryFast.getInstance().create();
+    db.executeInsert(
+      TABLE_MANUAL_MEASURES,
+      "METRIC_ID", random.nextInt(),
+      "COMPONENT_UUID", componentUuid,
+      "TEXT_VALUE", uuid);
+    return (Long) db.selectFirst("select id as \"ID\" from " + TABLE_MANUAL_MEASURES + " where text_value = '" + uuid + "'").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 0c6abb3d4c1..1b5327a0b68 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, 10);
+    verifyMigrationCount(underTest, 11);
   }
 }
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasuresTest/manual_measures_and_projects.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasuresTest/manual_measures_and_projects.sql
new file mode 100644
index 00000000000..238b5b87afa
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInManualMeasuresTest/manual_measures_and_projects.sql
@@ -0,0 +1,58 @@
+CREATE TABLE "MANUAL_MEASURES" (
+  "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "METRIC_ID" INTEGER NOT NULL,
+  "COMPONENT_UUID" VARCHAR(50),
+  "VALUE" DOUBLE,
+  "TEXT_VALUE" VARCHAR(4000),
+  "USER_LOGIN" VARCHAR(255),
+  "DESCRIPTION" VARCHAR(4000),
+  "CREATED_AT" BIGINT,
+  "UPDATED_AT" BIGINT
+);
+CREATE INDEX "MANUAL_MEASURES_COMPONENT_UUID" ON "MANUAL_MEASURES" ("COMPONENT_UUID");
+
+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");