From a0168a796011952a099bf0d5473221e85cdf4973 Mon Sep 17 00:00:00 2001 From: Eric Hartmann Date: Tue, 5 Jun 2018 16:06:56 +0200 Subject: [PATCH] SONAR-10416 Add a migration to purge orphans of CE tables --- .../db/migration/version/v72/DbVersion72.java | 1 + .../version/v72/PurgeOrphansForCE.java | 76 ++++++++ .../version/v72/DbVersion72Test.java | 2 +- .../version/v72/PurgeOrphansForCETest.java | 174 ++++++++++++++++++ .../version/v72/PurgeOrphansForCETest/ce.sql | 75 ++++++++ 5 files changed, 327 insertions(+), 1 deletion(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCE.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCETest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCETest/ce.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v72/DbVersion72.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v72/DbVersion72.java index 243ec08c19f..8a8e0aec026 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v72/DbVersion72.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v72/DbVersion72.java @@ -54,6 +54,7 @@ public class DbVersion72 implements DbVersion { .add(2124, "Add FILE_SOURCE.LINE_COUNT", AddFileSourceLineCount.class) .add(2125, "Populate FILE_SOURCE.LINE_COUNT", PopulateFileSourceLineCount.class) .add(2126, "Make FILE_SOURCE.LINE_COUNT not nullable", MakeFileSourceLineCountNotNullable.class) + .add(2127, "Purge orphans for Compute Engine", PurgeOrphansForCE.class) ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCE.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCE.java new file mode 100644 index 00000000000..28e2b2ed4e6 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCE.java @@ -0,0 +1,76 @@ +/* + * 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.v72; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; + +public class PurgeOrphansForCE extends DataChange { + + private final Database db; + + public PurgeOrphansForCE(Database db) { + super(db); + this.db = db; + } + + @Override + public void execute(Context context) throws SQLException { + switch (db.getDialect().getId()) { + case "mssql": + case "mysql": + executeForMySQLAndMsSQL(context); + break; + default: + executeGeneric(context); + break; + } + } + + private static void executeGeneric(Context context) throws SQLException { + context.prepareUpsert("delete from ce_task_characteristics ctc where not exists (select 1 from ce_activity ca where ca.uuid = ctc.task_uuid)") + .execute() + .commit(); + + context.prepareUpsert("delete from ce_task_input cti where not exists (select 1 from ce_activity ca where ca.uuid = cti.task_uuid)") + .execute() + .commit(); + + context.prepareUpsert("delete from ce_scanner_context csc where not exists (select 1 from ce_activity ca where ca.uuid = csc.task_uuid)") + .execute() + .commit(); + } + + private static void executeForMySQLAndMsSQL(Context context) throws SQLException { + context.prepareUpsert("delete ctc from ce_task_characteristics as ctc where not exists (select 1 from ce_activity ca where ca.uuid = ctc.task_uuid)") + .execute() + .commit(); + + context.prepareUpsert("delete cti from ce_task_input as cti where not exists (select 1 from ce_activity ca where ca.uuid = cti.task_uuid)") + .execute() + .commit(); + + context.prepareUpsert("delete csc from ce_scanner_context as csc where not exists (select 1 from ce_activity ca where ca.uuid = csc.task_uuid)") + .execute() + .commit(); + + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v72/DbVersion72Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v72/DbVersion72Test.java index bd24b8f02ec..8c25fd54a3b 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v72/DbVersion72Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v72/DbVersion72Test.java @@ -34,7 +34,7 @@ public class DbVersion72Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 27); + verifyMigrationCount(underTest, 28); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCETest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCETest.java new file mode 100644 index 00000000000..452f0ee9bed --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCETest.java @@ -0,0 +1,174 @@ +/* + * 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.v72; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric; +import static org.assertj.core.api.Assertions.assertThat; +import static org.sonar.db.CoreDbTester.createForSchema; + +public class PurgeOrphansForCETest { + + @Rule + public CoreDbTester db = createForSchema(PurgeOrphansForCETest.class, "ce.sql"); + + private PurgeOrphansForCE underTest = new PurgeOrphansForCE(db.database()); + private String uuid; + + @Test + public void test_is_reentrant() throws SQLException { + underTest.execute(); + underTest.execute(); + } + + @Test + public void purge_should_not_delete_data_containing() throws SQLException { + for (int i = 0; i < 10 ; i++) { + insertCeActivity(randomAlphanumeric(15)); + } + + underTest.execute(); + + assertThat(db.countRowsOfTable("CE_ACTIVITY")).isEqualTo(10); + assertThat(db.countRowsOfTable("CE_TASK_CHARACTERISTICS")).isEqualTo(10 * 10); + assertThat(db.countRowsOfTable("CE_TASK_INPUT")).isEqualTo(10); + assertThat(db.countRowsOfTable("CE_SCANNER_CONTEXT")).isEqualTo(10); + } + + @Test + public void purge_should_delete_orphans() throws SQLException { + for (int i = 0; i < 10 ; i++) { + uuid = randomAlphanumeric(20); + + insertCeCharacteristics(uuid); + insertCeScannerContext(uuid); + insertCeTaskInput(uuid); + } + + underTest.execute(); + + assertThat(db.countRowsOfTable("CE_ACTIVITY")).isEqualTo(0); + assertThat(db.countRowsOfTable("CE_TASK_CHARACTERISTICS")).isEqualTo(0); + assertThat(db.countRowsOfTable("CE_TASK_INPUT")).isEqualTo(0); + assertThat(db.countRowsOfTable("CE_SCANNER_CONTEXT")).isEqualTo(0); + } + + @Test + public void purge_should_keep_existant_ce_activity_and_delete_orphans() throws SQLException { + for (int i = 0; i < 5 ; i++) { + insertCeActivity(randomAlphanumeric(15)); + uuid = randomAlphanumeric(20); + + insertCeCharacteristics(uuid); + insertCeScannerContext(uuid); + insertCeTaskInput(uuid); + } + + underTest.execute(); + + assertThat(db.countRowsOfTable("CE_ACTIVITY")).isEqualTo(5); + assertThat(db.countRowsOfTable("CE_TASK_CHARACTERISTICS")).isEqualTo(5 * 10); + assertThat(db.countRowsOfTable("CE_TASK_INPUT")).isEqualTo(5); + assertThat(db.countRowsOfTable("CE_SCANNER_CONTEXT")).isEqualTo(5); + + assertThat( + db.selectFirst("select count(*) as count from ce_task_characteristics ctc where length(task_uuid) = 20") + .get("COUNT") + ).isEqualTo(0L); + assertThat( + db.selectFirst("select count(*) as count from ce_task_input ctc where length(task_uuid) = 20") + .get("COUNT") + ).isEqualTo(0L); + assertThat( + db.selectFirst("select count(*) as count from ce_scanner_context ctc where length(task_uuid) = 20") + .get("COUNT") + ).isEqualTo(0L); + + assertThat( + db.selectFirst("select count(*) as count from ce_task_characteristics ctc where not exists (select 1 from ce_activity ca where ca.uuid = ctc.task_uuid)") + .get("COUNT") + ).isEqualTo(0L); + assertThat( + db.selectFirst("select count(*) as count from ce_task_input cti where not exists (select 1 from ce_activity ca where ca.uuid = cti.task_uuid)") + .get("COUNT") + ).isEqualTo(0L); + assertThat( + db.selectFirst("select count(*) as count from ce_scanner_context csc where not exists (select 1 from ce_activity ca where ca.uuid = csc.task_uuid)") + .get("COUNT") + ).isEqualTo(0L); + } + + private void insertCeActivity(String uuid) { + long now = System.currentTimeMillis(); + + db.executeInsert("CE_ACTIVITY", + "UUID", uuid, + "TASK_TYPE", randomAlphanumeric(15), + "COMPONENT_UUID", randomAlphanumeric(15), + "ANALYSIS_UUID", randomAlphanumeric(15), + "STATUS", randomAlphanumeric(15), + "IS_LAST", false, + "IS_LAST_KEY", randomAlphanumeric(15), + "SUBMITTER_UUID", randomAlphanumeric(15), + "EXECUTION_COUNT", 0, + "SUBMITTED_AT", now, + "CREATED_AT", now, + "UPDATED_AT", now + ); + + insertCeTaskInput(uuid); + insertCeScannerContext(uuid); + + for (int i = 0; i < 10; i++) { + insertCeCharacteristics(uuid); + } + } + + private void insertCeCharacteristics(String uuid) { + db.executeInsert("CE_TASK_CHARACTERISTICS", + "UUID", randomAlphanumeric(15), + "TASK_UUID", uuid, + "KEE", randomAlphanumeric(15)); + } + + private void insertCeTaskInput(String uuid) { + long now = System.currentTimeMillis(); + + db.executeInsert("CE_TASK_INPUT", + "TASK_UUID", uuid, + "INPUT_DATA", randomAlphanumeric(15).getBytes(), + "CREATED_AT", now, + "UPDATED_AT", now); + } + + private void insertCeScannerContext(String uuid) { + long now = System.currentTimeMillis(); + + db.executeInsert("CE_SCANNER_CONTEXT", + "TASK_UUID", uuid, + "CONTEXT_DATA", randomAlphanumeric(15).getBytes(), + "CREATED_AT", now, + "UPDATED_AT", now); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCETest/ce.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCETest/ce.sql new file mode 100644 index 00000000000..53f08abdc36 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v72/PurgeOrphansForCETest/ce.sql @@ -0,0 +1,75 @@ +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_UUID" 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_UUID" 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, + "TASK_UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(50) NOT NULL, + "TEXT_VALUE" VARCHAR(4000), + + CONSTRAINT "PK_CE_TASK_CHARACTERISTICS" PRIMARY KEY ("UUID") +); +CREATE INDEX "CE_TASK_CHARACTERISTICS_TASK_UUID" ON "CE_TASK_CHARACTERISTICS" ("TASK_UUID"); + + +CREATE TABLE "CE_TASK_INPUT" ( + "TASK_UUID" VARCHAR(40) NOT NULL, + "INPUT_DATA" BLOB, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_CE_TASK_INPUT" PRIMARY KEY ("TASK_UUID") +); + + +CREATE TABLE "CE_SCANNER_CONTEXT" ( + "TASK_UUID" VARCHAR(40) NOT NULL, + "CONTEXT_DATA" BLOB NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_CE_SCANNER_CONTEXT" PRIMARY KEY ("TASK_UUID") +); -- 2.39.5