From 16c25a7b2211d67ed55076b43c379a30497b3da2 Mon Sep 17 00:00:00 2001 From: Jacek Date: Fri, 31 Jan 2020 15:56:44 +0100 Subject: [PATCH] SONAR-12962 delete security review rating measures and live measures --- .../db/migration/version/v82/DbVersion82.java | 3 +- .../DeleteSecurityReviewRatingMeasures.java | 78 ++++++++ .../version/v82/DbVersion82Test.java | 2 +- ...eleteSecurityReviewRatingMeasuresTest.java | 180 ++++++++++++++++++ .../schema.sql | 104 ++++++++++ 5 files changed, 365 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasures.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasuresTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasuresTest/schema.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DbVersion82.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DbVersion82.java index 3413cd865cc..e55f28070cf 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DbVersion82.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DbVersion82.java @@ -33,6 +33,7 @@ public class DbVersion82 implements DbVersion { .add(3204, "Rename table 'PROJECTS' to 'COMPONENTS'", RenameProjectsTableToComponents.class) .add(3205, "Add PROJECTS table", CreateProjectsTable.class) .add(3206, "Populate PROJECTS table", PopulateProjectsTable.class) - .add(3207, "Drop 'TAGS' column from COMPONENTS table", DropTagsColumnFromComponentsTable.class); + .add(3207, "Drop 'TAGS' column from COMPONENTS table", DropTagsColumnFromComponentsTable.class) + .add(3208, "Remove old Security Review Rating measures", DeleteSecurityReviewRatingMeasures.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasures.java new file mode 100644 index 00000000000..798da563fe4 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasures.java @@ -0,0 +1,78 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 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.v82; + +import java.sql.SQLException; +import javax.annotation.Nullable; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class DeleteSecurityReviewRatingMeasures extends DataChange { + + private static final String SECURITY_REVIEW_RATING_METRIC_KEY = "security_review_rating"; + private static final String SELECT_COMPONENTS_STATEMENT = "select c.uuid from components c where c.scope in ('PRJ') and c.qualifier in ('VW', 'SVW', 'APP', 'TRK')"; + + public DeleteSecurityReviewRatingMeasures(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + Integer metricId = getSecurityReviewRatingMetricId(context); + if (metricId != null) { + deleteFromProjectMeasures(context, metricId); + deleteFromLiveMeasures(context, metricId); + } + } + + @Nullable + private static Integer getSecurityReviewRatingMetricId(Context context) throws SQLException { + return context.prepareSelect("select id from metrics where name = ?") + .setString(1, SECURITY_REVIEW_RATING_METRIC_KEY) + .get(row -> row.getNullableInt(1)); + } + + private static void deleteFromLiveMeasures(Context context, Integer metricId) throws SQLException { + MassUpdate deleteFromLiveMeasures = context.prepareMassUpdate(); + + deleteFromLiveMeasures.select(SELECT_COMPONENTS_STATEMENT); + deleteFromLiveMeasures.update("delete from live_measures where project_uuid = ? and metric_id = ?"); + + deleteFromLiveMeasures.execute((row, update) -> { + update.setString(1, row.getString(1)); + update.setInt(2, metricId); + return true; + }); + } + + private static void deleteFromProjectMeasures(Context context, Integer metricId) throws SQLException { + MassUpdate deleteFromProjectMeasures = context.prepareMassUpdate(); + + deleteFromProjectMeasures.select(SELECT_COMPONENTS_STATEMENT); + deleteFromProjectMeasures.update("delete from project_measures where component_uuid = ? and metric_id = ?"); + + deleteFromProjectMeasures.execute((row, update) -> { + update.setString(1, row.getString(1)); + update.setInt(2, metricId); + return true; + }); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DbVersion82Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DbVersion82Test.java index 8de6c4a4159..fd3fe9956bd 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DbVersion82Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DbVersion82Test.java @@ -36,7 +36,7 @@ public class DbVersion82Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 8); + verifyMigrationCount(underTest, 9); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasuresTest.java new file mode 100644 index 00000000000..3be55d29ac9 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasuresTest.java @@ -0,0 +1,180 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 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.v82; + +import java.sql.SQLException; +import java.util.Random; +import java.util.UUID; +import java.util.stream.Collectors; +import java.util.stream.IntStream; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DataChange; + +import static org.apache.commons.lang.math.RandomUtils.nextInt; +import static org.assertj.core.api.Assertions.assertThat; + +public class DeleteSecurityReviewRatingMeasuresTest { + private static final String PROJECT_MEASURES_TABLE_NAME = "PROJECT_MEASURES"; + private static final String LIVE_MEASURES_TABLE_NAME = "LIVE_MEASURES"; + + private static final int SECURITY_REVIEW_RATING_METRIC_ID = 200; + private static final String SECURITY_REVIEW_RATING_METRIC_KEY = "security_review_rating"; + + private static final Random RANDOM = new Random(); + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DeleteSecurityReviewRatingMeasuresTest.class, "schema.sql"); + + private DataChange underTest = new DeleteSecurityReviewRatingMeasures(db.database()); + + @Test + public void should_not_fail_if_metric_not_defined() throws SQLException { + insertMetric(1, "another metric#1"); + insertMetric(2, "another metric#2"); + + String projectUuid = insertComponent("PRJ", "TRK"); + insertMeasure(4, SECURITY_REVIEW_RATING_METRIC_ID, projectUuid); + insertLiveMeasure("uuid-4", SECURITY_REVIEW_RATING_METRIC_ID, projectUuid, projectUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(PROJECT_MEASURES_TABLE_NAME)).isEqualTo(1); + assertThat(db.countRowsOfTable(LIVE_MEASURES_TABLE_NAME)).isEqualTo(1); + } + + @Test + public void should_remove_security_rating_review_from_measures_and_live_measures() throws SQLException { + insertMetric(SECURITY_REVIEW_RATING_METRIC_ID, SECURITY_REVIEW_RATING_METRIC_KEY); + insertMetric(1, "another metric#1"); + insertMetric(2, "another metric#2"); + + String portfolioUuid = insertComponent("PRJ", "VW"); + String subPortfolioUuid = insertComponent("PRJ", "SVW"); + String applicationUuid = insertComponent("PRJ", "APP"); + String projectUuid = insertComponent("PRJ", "TRK"); + + insertMeasure(1, SECURITY_REVIEW_RATING_METRIC_ID, portfolioUuid); + insertMeasure(2, SECURITY_REVIEW_RATING_METRIC_ID, subPortfolioUuid); + insertMeasure(3, SECURITY_REVIEW_RATING_METRIC_ID, applicationUuid); + insertMeasure(4, SECURITY_REVIEW_RATING_METRIC_ID, projectUuid); + + // other random metrics + int totalOtherMeasures = IntStream.range(5, 10 + RANDOM.nextInt(10)) + .peek(i -> insertMeasure(i, RANDOM.nextInt(100), projectUuid)) + .boxed() + .collect(Collectors.toList()) + .size(); + + insertLiveMeasure("uuid-1", SECURITY_REVIEW_RATING_METRIC_ID, portfolioUuid, portfolioUuid); + insertLiveMeasure("uuid-2", SECURITY_REVIEW_RATING_METRIC_ID, subPortfolioUuid, subPortfolioUuid); + insertLiveMeasure("uuid-3", SECURITY_REVIEW_RATING_METRIC_ID, applicationUuid, applicationUuid); + insertLiveMeasure("uuid-4", SECURITY_REVIEW_RATING_METRIC_ID, projectUuid, projectUuid); + insertLiveMeasure("uuid-5", SECURITY_REVIEW_RATING_METRIC_ID, projectUuid, getRandomUuid()); + insertLiveMeasure("uuid-6", SECURITY_REVIEW_RATING_METRIC_ID, projectUuid, getRandomUuid()); + + // other random metrics + long totalOtherLiveMeasures = IntStream.range(0, 10 + RANDOM.nextInt(10)) + .peek(i -> insertLiveMeasure("uuid-other-" + i, RANDOM.nextInt(100), projectUuid, getRandomUuid())) + .boxed() + .collect(Collectors.toList()) + .size(); + + underTest.execute(); + + assertSecurityReviewRatingMeasuresDeleted(); + assertSecurityReviewRatingLiveMeasuresDeleted(); + + assertThat(db.countRowsOfTable(PROJECT_MEASURES_TABLE_NAME)).isEqualTo(totalOtherMeasures); + assertThat(db.countRowsOfTable(LIVE_MEASURES_TABLE_NAME)).isEqualTo(totalOtherLiveMeasures); + + // should not fail if called twice + underTest.execute(); + } + + @Test + public void should_not_fail_if_empty_tables() throws SQLException { + insertMetric(SECURITY_REVIEW_RATING_METRIC_ID, SECURITY_REVIEW_RATING_METRIC_KEY); + insertMetric(1, "another metric#1"); + insertMetric(2, "another metric#2"); + + underTest.execute(); + + assertThat(db.countRowsOfTable(PROJECT_MEASURES_TABLE_NAME)).isEqualTo(0); + assertThat(db.countRowsOfTable(LIVE_MEASURES_TABLE_NAME)).isEqualTo(0); + } + + private void assertSecurityReviewRatingLiveMeasuresDeleted() { + assertThat(db.countSql("select count(uuid) from LIVE_MEASURES where metric_id = " + SECURITY_REVIEW_RATING_METRIC_ID)) + .isEqualTo(0); + } + + private void assertSecurityReviewRatingMeasuresDeleted() { + assertThat(db.countSql("select count(id) from project_measures where metric_id = " + SECURITY_REVIEW_RATING_METRIC_ID)) + .isEqualTo(0); + } + + private void insertMeasure(int id, int metricId, String componentUuid) { + db.executeInsert("PROJECT_MEASURES", + "ID", id, + "METRIC_ID", metricId, + "ANALYSIS_UUID", getRandomUuid(), + "COMPONENT_UUID", componentUuid); + } + + private String getRandomUuid() { + return UUID.randomUUID().toString(); + } + + private void insertLiveMeasure(String uuid, int metricId, String projectUuid, String componentUuid) { + db.executeInsert("LIVE_MEASURES", + "UUID", uuid, + "PROJECT_UUID", projectUuid, + "COMPONENT_UUID", componentUuid, + "METRIC_ID", metricId, + "CREATED_AT", System.currentTimeMillis(), + "UPDATED_AT", System.currentTimeMillis()); + } + + private void insertMetric(int id, String name) { + db.executeInsert("METRICS", + "ID", id, + "NAME", name, + "DIRECTION", 0, + "QUALITATIVE", true); + } + + private String insertComponent(String scope, String qualifier) { + int id = nextInt(); + String uuid = getRandomUuid(); + db.executeInsert("COMPONENTS", + "ID", id, + "UUID", uuid, + "ORGANIZATION_UUID", "default", + "PROJECT_UUID", uuid, + "UUID_PATH", ".", + "ROOT_UUID", uuid, + "PRIVATE", Boolean.toString(false), + "SCOPE", scope, + "QUALIFIER", qualifier); + return uuid; + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasuresTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasuresTest/schema.sql new file mode 100644 index 00000000000..8afa160d62e --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingMeasuresTest/schema.sql @@ -0,0 +1,104 @@ +CREATE TABLE "COMPONENTS"( + "ID" INTEGER NOT NULL AUTO_INCREMENT (1,1), + "UUID" VARCHAR(50) NOT NULL, + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400), + "DEPRECATED_KEE" VARCHAR(400), + "NAME" VARCHAR(2000), + "LONG_NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "ENABLED" BOOLEAN DEFAULT TRUE NOT NULL, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "PRIVATE" BOOLEAN NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "LANGUAGE" VARCHAR(20), + "COPY_COMPONENT_UUID" VARCHAR(50), + "PATH" VARCHAR(2000), + "UUID_PATH" VARCHAR(1500) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(1500), + "MAIN_BRANCH_PROJECT_UUID" VARCHAR(50), + "B_CHANGED" BOOLEAN, + "B_NAME" VARCHAR(500), + "B_LONG_NAME" VARCHAR(500), + "B_DESCRIPTION" VARCHAR(2000), + "B_ENABLED" BOOLEAN, + "B_QUALIFIER" VARCHAR(10), + "B_LANGUAGE" VARCHAR(20), + "B_COPY_COMPONENT_UUID" VARCHAR(50), + "B_PATH" VARCHAR(2000), + "B_UUID_PATH" VARCHAR(1500), + "B_MODULE_UUID" VARCHAR(50), + "B_MODULE_UUID_PATH" VARCHAR(1500), + "CREATED_AT" TIMESTAMP +); +ALTER TABLE "COMPONENTS" ADD CONSTRAINT "PK_PROJECTS" PRIMARY KEY("ID"); +CREATE INDEX "PROJECTS_ORGANIZATION" ON "COMPONENTS"("ORGANIZATION_UUID"); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "COMPONENTS"("KEE"); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "COMPONENTS"("MODULE_UUID"); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "COMPONENTS"("PROJECT_UUID"); +CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER"); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "COMPONENTS"("ROOT_UUID"); +CREATE INDEX "PROJECTS_UUID" ON "COMPONENTS"("UUID"); + +CREATE TABLE "METRICS"( + "ID" INTEGER NOT NULL AUTO_INCREMENT (1,1), + "NAME" VARCHAR(64) NOT NULL, + "DESCRIPTION" VARCHAR(255), + "DIRECTION" INTEGER DEFAULT 0 NOT NULL, + "DOMAIN" VARCHAR(64), + "SHORT_NAME" VARCHAR(64), + "QUALITATIVE" BOOLEAN DEFAULT FALSE NOT NULL, + "VAL_TYPE" VARCHAR(8), + "USER_MANAGED" BOOLEAN DEFAULT FALSE, + "ENABLED" BOOLEAN DEFAULT TRUE, + "WORST_VALUE" DOUBLE, + "BEST_VALUE" DOUBLE, + "OPTIMIZED_BEST_VALUE" BOOLEAN, + "HIDDEN" BOOLEAN, + "DELETE_HISTORICAL_DATA" BOOLEAN, + "DECIMAL_SCALE" INTEGER +); +ALTER TABLE "METRICS" ADD CONSTRAINT "PK_METRICS" PRIMARY KEY("ID"); +CREATE UNIQUE INDEX "METRICS_UNIQUE_NAME" ON "METRICS"("NAME"); + +CREATE TABLE "LIVE_MEASURES"( + "UUID" VARCHAR(40) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "METRIC_ID" INTEGER NOT NULL, + "VALUE" DOUBLE, + "TEXT_VALUE" VARCHAR(4000), + "VARIATION" DOUBLE, + "MEASURE_DATA" BLOB, + "UPDATE_MARKER" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "LIVE_MEASURES" ADD CONSTRAINT "PK_LIVE_MEASURES" PRIMARY KEY("UUID"); +CREATE INDEX "LIVE_MEASURES_PROJECT" ON "LIVE_MEASURES"("PROJECT_UUID"); +CREATE UNIQUE INDEX "LIVE_MEASURES_COMPONENT" ON "LIVE_MEASURES"("COMPONENT_UUID", "METRIC_ID"); + +CREATE TABLE "PROJECT_MEASURES"( + "ID" BIGINT NOT NULL AUTO_INCREMENT (1,1), + "VALUE" DOUBLE, + "METRIC_ID" INTEGER NOT NULL, + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "TEXT_VALUE" VARCHAR(4000), + "ALERT_STATUS" VARCHAR(5), + "ALERT_TEXT" VARCHAR(4000), + "DESCRIPTION" VARCHAR(4000), + "PERSON_ID" INTEGER, + "VARIATION_VALUE_1" DOUBLE, + "VARIATION_VALUE_2" DOUBLE, + "VARIATION_VALUE_3" DOUBLE, + "VARIATION_VALUE_4" DOUBLE, + "VARIATION_VALUE_5" DOUBLE, + "MEASURE_DATA" BLOB +); +ALTER TABLE "PROJECT_MEASURES" ADD CONSTRAINT "PK_PROJECT_MEASURES" PRIMARY KEY("ID"); +CREATE INDEX "MEASURES_ANALYSIS_METRIC" ON "PROJECT_MEASURES"("ANALYSIS_UUID", "METRIC_ID"); +CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES"("COMPONENT_UUID"); -- 2.39.5