SONAR-9028 add DB migrations cleaning USER_ROLES and GROUP_ROLES

ThyPillow · May 31, 2017 · e276381 · e276381
1 parent 0223375
commit e276381
Show file tree

Hide file tree

Showing 8 changed files with 620 additions and 2 deletions.
diff --git a/.../java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRoles.java b/.../java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRoles.java
@@ -0,0 +1,71 @@
+/*
+ * 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;
+
+public class CleanOrphanRowsInGroupRoles extends DataChange {
+  public CleanOrphanRowsInGroupRoles(Database db) {
+    super(db);
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    deleteRowsWithoutComponent(context);
+    deleteRowsForNonRootComponent(context);
+  }
+
+  private static void deleteRowsWithoutComponent(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select distinct gr.resource_id from group_roles gr where" +
+        " gr.resource_id is not null" +
+        " and not exists (select id from projects p where p.id = gr.resource_id)");
+    massUpdate.rowPluralName("rows without component");
+    massUpdate.update("delete from group_roles where resource_id = ?");
+    massUpdate.execute(CleanOrphanRowsInGroupRoles::handle);
+  }
+
+  private static void deleteRowsForNonRootComponent(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select distinct gr.resource_id from group_roles gr" +
+        " inner join projects p on p.id = gr.resource_id" +
+        " where" +
+        " p.scope <> ?" +
+        " or (p.qualifier <> ? and p.qualifier <> ?)")
+        .setString(1, "PRJ")
+        .setString(2, "TRK")
+        .setString(3, "VW");
+    massUpdate.rowPluralName("rows for non-root component");
+    massUpdate.update("delete from group_roles where resource_id = ?");
+    massUpdate.execute(CleanOrphanRowsInGroupRoles::handle);
+  }
+
+  private static boolean handle(Select.Row row, SqlStatement update) throws SQLException {
+    long resourceId = row.getLong(1);
+
+    update.setLong(1, resourceId);
+    return true;
+  }
+}
diff --git a/...n/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRoles.java b/...n/java/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInUserRoles.java
@@ -0,0 +1,71 @@
+/*
+ * 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;
+
+public class CleanOrphanRowsInUserRoles extends DataChange {
+  public CleanOrphanRowsInUserRoles(Database db) {
+    super(db);
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    deleteRowsWithoutComponent(context);
+    deleteRowsForNonRootComponent(context);
+  }
+
+  private static void deleteRowsWithoutComponent(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select distinct ur.resource_id from user_roles ur where" +
+      " ur.resource_id is not null" +
+      " and not exists (select id from projects p where p.id = ur.resource_id)");
+    massUpdate.rowPluralName("rows without component");
+    massUpdate.update("delete from user_roles where resource_id = ?");
+    massUpdate.execute(CleanOrphanRowsInUserRoles::handle);
+  }
+
+  private static void deleteRowsForNonRootComponent(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select distinct ur.resource_id from user_roles ur" +
+      " inner join projects p on p.id = ur.resource_id" +
+      " where" +
+      " p.scope <> ?" +
+      " or (p.qualifier <> ? and p.qualifier <> ?)")
+      .setString(1, "PRJ")
+      .setString(2, "TRK")
+      .setString(3, "VW");
+    massUpdate.rowPluralName("rows for non-root component");
+    massUpdate.update("delete from user_roles where resource_id = ?");
+    massUpdate.execute(CleanOrphanRowsInUserRoles::handle);
+  }
+
+  private static boolean handle(Select.Row row, SqlStatement update) throws SQLException {
+    long resourceId = row.getLong(1);
+
+    update.setLong(1, resourceId);
+    return true;
+  }
+}
diff --git a/...gration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java b/...gration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java
@@ -27,6 +27,8 @@ public class DbVersion65 implements DbVersion {
   @Override
   public void addSteps(MigrationStepRegistry registry) {
     registry
-      .add(1700, "Drop table AUTHORS", DropTableAuthors.class);
+      .add(1700, "Drop table AUTHORS", DropTableAuthors.class)
+      .add(1701, "Clean orphans from USER_ROLES", CleanOrphanRowsInUserRoles.class)
+      .add(1702, "Clean orphans from GROUP_ROLES", CleanOrphanRowsInGroupRoles.class);
   }
 }
diff --git a/...a/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest.java b/...a/org/sonar/server/platform/db/migration/version/v65/CleanOrphanRowsInGroupRolesTest.java
@@ -0,0 +1,188 @@
+/*
+ * 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.junit.rules.ExpectedException;
+import org.sonar.db.CoreDbTester;
+
+import static java.lang.String.valueOf;
+import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric;
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class CleanOrphanRowsInGroupRolesTest {
+  private static final String TABLE_GROUP_ROLES = "group_roles";
+  private static final String PROJECT_SCOPE = "PRJ";
+  private static final String QUALIFIER_VW = "VW";
+  private static final String QUALIFIER_TRK = "TRK";
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(CleanOrphanRowsInGroupRolesTest.class, "group_roles_and_projects.sql");
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  private CleanOrphanRowsInGroupRoles underTest = new CleanOrphanRowsInGroupRoles(db.database());
+
+  @Test
+  public void execute_has_no_effect_on_empty_table() throws SQLException {
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isZero();
+  }
+
+  @Test
+  public void execute_does_not_delete_rows_without_resource_id() throws SQLException {
+    insertGroupRole(null);
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(1);
+
+  }
+
+  @Test
+  public void execute_deletes_rows_of_non_existent_component() throws SQLException {
+    insertGroupRole(new Random().nextInt());
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(0);
+  }
+
+  @Test
+  public void execute_deletes_rows_of_component_without_qualifier_PRJ() throws SQLException {
+    String scope = randomAlphanumeric(3);
+    insertGroupRole(insertComponent(scope, QUALIFIER_TRK));
+    insertGroupRole(insertComponent(scope, QUALIFIER_VW));
+    insertGroupRole(insertComponent(scope, randomAlphanumeric(3)));
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(3);
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(0);
+  }
+
+  @Test
+  public void execute_keeps_rows_with_qualifier_TRK() throws SQLException {
+    executeKeepsRowsWithSpecifiedQualifierAndScopeProject(QUALIFIER_TRK);
+  }
+
+  @Test
+  public void execute_keeps_rows_with_qualifier_VW() throws SQLException {
+    executeKeepsRowsWithSpecifiedQualifierAndScopeProject(QUALIFIER_VW);
+  }
+
+  @Test
+  public void execute_deletes_rows_of_component_with_qualifier_DEV() throws SQLException {
+    executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DEV");
+  }
+
+  @Test
+  public void execute_deletes_rows_of_component_with_qualifier_DEV_PRJ() throws SQLException {
+    executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DEV_PRJ");
+  }
+
+  @Test
+  public void execute_deletes_rows_of_component_with_qualifier_BRC() throws SQLException {
+    executeDeletesRowsWithSpecifiedQualifierAndScopeProject("BRC");
+  }
+
+  @Test
+  public void execute_deletes_rows_of_component_with_qualifier_DIR() throws SQLException {
+    executeDeletesRowsWithSpecifiedQualifierAndScopeProject("DIR");
+  }
+
+  @Test
+  public void execute_deletes_rows_of_component_with_qualifier_FIL() throws SQLException {
+    executeDeletesRowsWithSpecifiedQualifierAndScopeProject("FIL");
+  }
+
+  @Test
+  public void execute_deletes_rows_of_component_with_qualifier_UTS() throws SQLException {
+    executeDeletesRowsWithSpecifiedQualifierAndScopeProject("UTS");
+  }
+
+  @Test
+  public void execute_deletes_rows_of_component_with_unknown_qualifier() throws SQLException {
+    executeDeletesRowsWithSpecifiedQualifierAndScopeProject(randomAlphanumeric(3));
+  }
+
+  private void executeDeletesRowsWithSpecifiedQualifierAndScopeProject(String qualifier) throws SQLException {
+    int componentId = insertComponent(PROJECT_SCOPE, qualifier);
+    insertGroupRole(componentId);
+    insertAnyoneRole(componentId);
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(2);
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(0);
+  }
+
+  private void executeKeepsRowsWithSpecifiedQualifierAndScopeProject(String qualifier) throws SQLException {
+    int componentId = insertComponent(PROJECT_SCOPE, qualifier);
+    insertGroupRole(componentId);
+    insertAnyoneRole(componentId);
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(2);
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(2);
+  }
+
+  private void insertAnyoneRole(@Nullable Integer componentId) {
+    insertGroupRole(null, componentId);
+  }
+
+  private void insertGroupRole(@Nullable Integer componentId) {
+    insertGroupRole(new Random().nextInt(), componentId);
+  }
+
+  private void insertGroupRole(@Nullable Integer groupId, @Nullable Integer componentId) {
+    db.executeInsert(
+      TABLE_GROUP_ROLES,
+      "organization_uuid", randomAlphanumeric(3),
+      "group_id", groupId == null ? null : valueOf(groupId),
+      "resource_id", componentId == null ? null : valueOf(componentId),
+      "role", randomAlphanumeric(5));
+  }
+
+  private int insertComponent(@Nullable String scope, @Nullable String qualifier) {
+    return insertComponent(randomAlphanumeric(30), scope, qualifier);
+  }
+
+  private int insertComponent(String uuid, @Nullable String scope, @Nullable String qualifier) {
+    db.executeInsert(
+      "projects",
+      "organization_uuid", randomAlphanumeric(3),
+      "uuid", uuid,
+      "uuid_path", "path_of_" + uuid,
+      "root_uuid", uuid,
+      "project_uuid", uuid,
+      "scope", scope,
+      "qualifier", qualifier,
+      "private", valueOf(new Random().nextBoolean()),
+      "enabled", valueOf(new Random().nextBoolean()));
+    return ((Long) db.selectFirst("select id as \"ID\" from projects where uuid='" + uuid + "'").get("ID")).intValue();
+  }
+}