SONAR-10301 db migration cleans orphans in CE child tables

5 files changed, 310 insertions, 1 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphans.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphans.java
new file mode 100644
index 00000000000..5ed4aca93f9
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphans.java
@@ -0,0 +1,56 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2018 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.v71;
+
+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 CleanCeChildTablesOrphans extends DataChange {
+  public CleanCeChildTablesOrphans(Database db) {
+    super(db);
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    deleteOrphansInCeChildTable(context, "ce_task_input");
+    deleteOrphansInCeChildTable(context, "ce_scanner_context");
+    deleteOrphansInCeChildTable(context, "ce_task_characteristics");
+  }
+
+  private static void deleteOrphansInCeChildTable(Context context, String childTableName) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select task_uuid from " + childTableName + " child where" +
+      " not exists (select 1 from ce_activity a where a.uuid = child.task_uuid)" +
+      " and not exists (select 1 from ce_queue q where q.uuid = child.task_uuid)");
+    massUpdate.rowPluralName("orphans rows in " + childTableName);
+    massUpdate.update("delete from " + childTableName + " where task_uuid=?");
+    massUpdate.execute(CleanCeChildTablesOrphans::deleteByTaskUuid);
+  }
+
+  private static boolean deleteByTaskUuid(Select.Row row, SqlStatement update) throws SQLException {
+    String taskUuid = row.getString(1);
+    update.setString(1, taskUuid);
+    return true;
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/DbVersion71.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/DbVersion71.java
index 552c1f73abd..3ea0374ddfc 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/DbVersion71.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v71/DbVersion71.java
@@ -33,6 +33,7 @@ public class DbVersion71 implements DbVersion {
       .add(2003, "Make scope not nullable in rules", MakeScopeNotNullableInRules.class)
       .add(2004, "Use rule id in QPROFILE_CHANGES", UseRuleIdInQPChangesData.class)
       .add(2005, "Create table DEPRECATED_RULE_KEYS", CreateDeprecatedRuleKeysTable.class)
+      .add(2006, "Clean orphans in Compute Engine child tables", CleanCeChildTablesOrphans.class)
     ;
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphansTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphansTest.java
new file mode 100644
index 00000000000..3e9c36ad8cf
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphansTest.java
@@ -0,0 +1,183 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2018 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.v71;
+
+import java.sql.SQLException;
+import java.util.Random;
+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.randomAlphanumeric;
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class CleanCeChildTablesOrphansTest {
+
+  @Rule
+  public final CoreDbTester db = CoreDbTester.createForSchema(CleanCeChildTablesOrphansTest.class, "ce_tables.sql");
+
+  private final Random random = new Random();
+  private CleanCeChildTablesOrphans underTest = new CleanCeChildTablesOrphans(db.database());
+
+  @Test
+  public void execute_has_no_effect_if_tables_are_empty() throws SQLException {
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_deletes_rows_of_ce_task_input_which_task_uuid_appears_in_neither_ce_queue_nor_ce_activity() throws SQLException {
+    String taskInQueueUuid = insertCeQueue();
+    String taskInActivityUuid = insertCeActivity();
+    insertCeTaskInput(taskInQueueUuid);
+    insertCeTaskInput(taskInActivityUuid);
+    insertCeTaskInput("missing_task");
+
+    underTest.execute();
+
+    assertThat(db.select("select task_uuid as \"TASK_UUID\" from ce_task_input"))
+      .extracting(r -> (String) r.get("TASK_UUID"))
+      .containsOnly(taskInQueueUuid, taskInActivityUuid);
+  }
+
+  @Test
+  public void execute_deletes_rows_of_ce_scanner_context_which_task_uuid_appears_in_neither_ce_queue_nor_ce_activity() throws SQLException {
+    String taskInQueueUuid = insertCeQueue();
+    String taskInActivityUuid = insertCeActivity();
+    insertCeScannerContext(taskInQueueUuid);
+    insertCeScannerContext(taskInActivityUuid);
+    insertCeScannerContext("missing_task");
+
+    underTest.execute();
+
+    assertThat(db.select("select task_uuid as \"TASK_UUID\" from ce_scanner_context"))
+      .extracting(r -> (String) r.get("TASK_UUID"))
+      .containsOnly(taskInQueueUuid, taskInActivityUuid);
+  }
+
+  @Test
+  public void execute_deletes_rows_of_ce_task_characteristics_which_task_uuid_appears_in_neither_ce_queue_nor_ce_activity() throws SQLException {
+    String taskInQueueUuid = insertCeQueue();
+    String taskInActivityUuid = insertCeActivity();
+    insertCeTaskCharacteristics(taskInQueueUuid);
+    insertCeTaskCharacteristics(taskInActivityUuid);
+    insertCeTaskCharacteristics("missing_task");
+
+    underTest.execute();
+
+    assertThat(db.select("select task_uuid as \"TASK_UUID\" from ce_task_characteristics"))
+      .extracting(r -> (String) r.get("TASK_UUID"))
+      .containsOnly(taskInQueueUuid, taskInActivityUuid);
+  }
+
+  @Test
+  public void execute_is_reentrant() throws SQLException {
+    String taskInQueueUuid = insertCeQueue();
+    String taskInActivityUuid = insertCeActivity();
+    insertCeScannerContext(taskInQueueUuid);
+    insertCeScannerContext(taskInActivityUuid);
+    insertCeTaskInput(taskInQueueUuid);
+    insertCeTaskInput(taskInActivityUuid);
+    insertCeTaskCharacteristics(taskInQueueUuid);
+    insertCeTaskCharacteristics(taskInActivityUuid);
+    insertCeTaskInput("missing_task");
+    insertCeScannerContext("missing_task");
+    insertCeTaskCharacteristics("missing_task");
+
+    underTest.execute();
+
+    verifyOrphansDeleted(taskInQueueUuid, taskInActivityUuid);
+
+    underTest.execute();
+
+    verifyOrphansDeleted(taskInQueueUuid, taskInActivityUuid);
+  }
+
+  private void verifyOrphansDeleted(String taskInQueueUuid, String taskInActivityUuid) {
+    assertThat(db.select("select task_uuid as \"TASK_UUID\" from ce_task_input"))
+      .extracting(r -> (String) r.get("TASK_UUID"))
+      .containsOnly(taskInQueueUuid, taskInActivityUuid);
+    assertThat(db.select("select task_uuid as \"TASK_UUID\" from ce_scanner_context"))
+      .extracting(r -> (String) r.get("TASK_UUID"))
+      .containsOnly(taskInQueueUuid, taskInActivityUuid);
+    assertThat(db.select("select task_uuid as \"TASK_UUID\" from ce_task_characteristics"))
+      .extracting(r -> (String) r.get("TASK_UUID"))
+      .containsOnly(taskInQueueUuid, taskInActivityUuid);
+  }
+
+  private String insertCeQueue() {
+    String uuid = UuidFactoryFast.getInstance().create();
+    db.executeInsert(
+      "ce_queue",
+      "UUID", uuid,
+      "TASK_TYPE", randomAlphanumeric(10),
+      "STATUS", randomAlphanumeric(10),
+      "EXECUTION_COUNT", random.nextInt(99),
+      "CREATED_AT", random.nextInt(95654354),
+      "UPDATED_AT", random.nextInt(95654354));
+    return uuid;
+  }
+
+  private String insertCeActivity() {
+    String uuid = UuidFactoryFast.getInstance().create();
+    db.executeInsert(
+      "ce_activity",
+      "UUID", uuid,
+      "TASK_TYPE", randomAlphanumeric(10),
+      "STATUS", randomAlphanumeric(10),
+      "IS_LAST", random.nextBoolean(),
+      "IS_LAST_KEY", randomAlphanumeric(15),
+      "EXECUTION_COUNT", random.nextInt(99),
+      "SUBMITTED_AT", random.nextInt(95654354),
+      "CREATED_AT", random.nextInt(95654354),
+      "UPDATED_AT", random.nextInt(95654354));
+    return uuid;
+  }
+
+  private void insertCeTaskInput(String taskUuid) {
+    db.executeInsert(
+      "ce_task_input",
+      "TASK_UUID", taskUuid,
+      "INPUT_DATA", randomAlphanumeric(123).getBytes(),
+      "CREATED_AT", random.nextInt(95654354),
+      "UPDATED_AT", random.nextInt(95654354));
+  }
+
+  private void insertCeScannerContext(String taskUuid) {
+    db.executeInsert(
+      "ce_scanner_context",
+      "TASK_UUID", taskUuid,
+      "CONTEXT_DATA", randomAlphanumeric(123).getBytes(),
+      "CREATED_AT", random.nextInt(95654354),
+      "UPDATED_AT", random.nextInt(95654354));
+  }
+
+  private void insertCeTaskCharacteristics(String taskUuid) {
+    for (int i = 0; i < 1 + random.nextInt(3); i++) {
+      String uuid = UuidFactoryFast.getInstance().create();
+      db.executeInsert(
+        "ce_task_characteristics",
+        "UUID", uuid,
+        "TASK_UUID", taskUuid,
+        "KEE", "kee_" + uuid + i,
+        "TEXT_VALUE", randomAlphanumeric(18));
+    }
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/DbVersion71Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/DbVersion71Test.java
index 0d40b7520a9..08045d33253 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/DbVersion71Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v71/DbVersion71Test.java
@@ -36,7 +36,7 @@ public class DbVersion71Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 6);
+    verifyMigrationCount(underTest, 7);
   }
 
 }
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphansTest/ce_tables.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphansTest/ce_tables.sql
new file mode 100644
index 00000000000..cab879a5f89
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v71/CleanCeChildTablesOrphansTest/ce_tables.sql
@@ -0,0 +1,69 @@
+CREATE TABLE "CE_QUEUE" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "UUID" VARCHAR(40) NOT NULL,
+  "TASK_TYPE" VARCHAR(15) NOT NULL,
+  "COMPONENT_UUID" VARCHAR(40) NULL,
+  "STATUS" VARCHAR(15) NOT NULL,
+  "SUBMITTER_LOGIN" VARCHAR(255) NULL,
+  "WORKER_UUID" VARCHAR(40) NULL,
+  "EXECUTION_COUNT" INTEGER NOT NULL,
+  "STARTED_AT" BIGINT NULL,
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL
+);
+CREATE UNIQUE INDEX "CE_QUEUE_UUID" ON "CE_QUEUE" ("UUID");
+CREATE INDEX "CE_QUEUE_COMPONENT_UUID" ON "CE_QUEUE" ("COMPONENT_UUID");
+CREATE INDEX "CE_QUEUE_STATUS" ON "CE_QUEUE" ("STATUS");
+
+
+CREATE TABLE "CE_ACTIVITY" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "UUID" VARCHAR(40) NOT NULL,
+  "TASK_TYPE" VARCHAR(15) NOT NULL,
+  "COMPONENT_UUID" VARCHAR(40) NULL,
+  "ANALYSIS_UUID" VARCHAR(50) NULL,
+  "STATUS" VARCHAR(15) NOT NULL,
+  "IS_LAST" BOOLEAN NOT NULL,
+  "IS_LAST_KEY" VARCHAR(55) NOT NULL,
+  "SUBMITTER_LOGIN" VARCHAR(255) NULL,
+  "WORKER_UUID" VARCHAR(40) NULL,
+  "EXECUTION_COUNT" INTEGER NOT NULL,
+  "SUBMITTED_AT" BIGINT NOT NULL,
+  "STARTED_AT" BIGINT NULL,
+  "EXECUTED_AT" BIGINT NULL,
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL,
+  "EXECUTION_TIME_MS" BIGINT NULL,
+  "ERROR_MESSAGE" VARCHAR(1000),
+  "ERROR_STACKTRACE" CLOB,
+  "ERROR_TYPE" VARCHAR(20)
+);
+
+CREATE UNIQUE INDEX "CE_ACTIVITY_UUID" ON "CE_ACTIVITY" ("UUID");
+CREATE INDEX "CE_ACTIVITY_COMPONENT_UUID" ON "CE_ACTIVITY" ("COMPONENT_UUID");
+CREATE INDEX "CE_ACTIVITY_ISLASTKEY" ON "CE_ACTIVITY" ("IS_LAST_KEY");
+CREATE INDEX "CE_ACTIVITY_ISLAST_STATUS" ON "CE_ACTIVITY" ("IS_LAST", "STATUS");
+
+CREATE TABLE "CE_TASK_CHARACTERISTICS" (
+  "UUID" VARCHAR(40) NOT NULL PRIMARY KEY,
+  "TASK_UUID" VARCHAR(40) NOT NULL,
+  "KEE" VARCHAR(50) NOT NULL,
+  "TEXT_VALUE" VARCHAR(4000)
+);
+CREATE INDEX "CE_TASK_CHARACTERISTICS_TASK_UUID" ON "CE_TASK_CHARACTERISTICS" ("TASK_UUID");
+
+
+CREATE TABLE "CE_TASK_INPUT" (
+  "TASK_UUID" VARCHAR(40) NOT NULL PRIMARY KEY,
+  "INPUT_DATA" BLOB,
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL
+);
+
+
+CREATE TABLE "CE_SCANNER_CONTEXT" (
+  "TASK_UUID" VARCHAR(40) NOT NULL PRIMARY KEY,
+  "CONTEXT_DATA" BLOB NOT NULL,
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL
+);