From af2bb873abd4ff9f9f3995cf2ca92ac21618e208 Mon Sep 17 00:00:00 2001 From: Julien Lancelot Date: Fri, 21 Feb 2020 13:54:15 +0100 Subject: [PATCH] SONAR-12966 Remove QGate conditions on 'security_hotspots' and 'new_security_hotspots' --- .../db/migration/version/v82/DbVersion82.java | 3 +- ...ConditionsUsingSecurityHotspotMetrics.java | 46 +++++++ .../version/v82/DbVersion82Test.java | 2 +- ...itionsUsingSecurityHotspotMetricsTest.java | 120 ++++++++++++++++++ .../schema.sql | 34 +++++ 5 files changed, 203 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetrics.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetricsTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetricsTest/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 cda4abb3e96..806dd5589fc 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 @@ -36,6 +36,7 @@ public class DbVersion82 implements DbVersion { .add(3207, "Drop 'TAGS' column from COMPONENTS table", DropTagsColumnFromComponentsTable.class) .add(3208, "Remove old Security Review Rating measures", DeleteSecurityReviewRatingMeasures.class) .add(3209, "Create ALM_PATS table", CreateAlmPatsTable.class) - .add(3210, "Add index on ALM_slug", AddIndexOnSlugOfProjectAlmSettings.class); + .add(3210, "Add index on ALM_slug", AddIndexOnSlugOfProjectAlmSettings.class) + .add(3211, "Delete conditions using 'security_hotspots' and 'new_security_hotspots' metrics", DeleteQgateConditionsUsingSecurityHotspotMetrics.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetrics.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetrics.java new file mode 100644 index 00000000000..a8c6e21bffa --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetrics.java @@ -0,0 +1,46 @@ +/* + * 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 org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class DeleteQgateConditionsUsingSecurityHotspotMetrics extends DataChange { + + public DeleteQgateConditionsUsingSecurityHotspotMetrics(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select qgc.id from quality_gate_conditions qgc " + + " inner join metrics m on m.id = qgc.metric_id and " + + " m.name in ('security_hotspots', 'new_security_hotspots')"); + massUpdate.update("delete from quality_gate_conditions where id = ?"); + massUpdate.execute((row, handler) -> { + handler.setLong(1, row.getLong(1)); + 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 ccac28b2c85..74e56f4dfc8 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, 11); + verifyMigrationCount(underTest, 12); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetricsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetricsTest.java new file mode 100644 index 00000000000..3f7d8815abb --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetricsTest.java @@ -0,0 +1,120 @@ +/* + * 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 com.google.common.collect.ImmutableMap; +import java.sql.SQLException; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import org.apache.commons.lang.math.RandomUtils; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DataChange; + +import static com.google.common.primitives.Longs.asList; +import static java.lang.String.format; +import static java.util.Collections.singletonList; +import static java.util.stream.Collectors.toList; +import static org.assertj.core.api.Assertions.assertThat; + +public class DeleteQgateConditionsUsingSecurityHotspotMetricsTest { + + private static final String TABLE_QUALITY_GATE_CONDITIONS = "quality_gate_conditions"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DeleteQgateConditionsUsingSecurityHotspotMetricsTest.class, "schema.sql"); + + private DataChange underTest = new DeleteQgateConditionsUsingSecurityHotspotMetrics(db.database()); + + @Test + public void remove_conditions_on_security_hotspots() throws SQLException { + long securityHotspotsMetric = insertMetric("security_hotspots"); + insertQualityGateCondition(securityHotspotsMetric); + long newSecurityHotspotsMetric = insertMetric("new_security_hotspots"); + insertQualityGateCondition(newSecurityHotspotsMetric); + long nclocMetric = insertMetric("ncloc"); + long conditionOnNcloc = insertQualityGateCondition(nclocMetric); + + underTest.execute(); + + verifyConditionIds(singletonList(conditionOnNcloc)); + } + + @Test + public void do_not_remove_any_condition_when_no_security_hotspot_metrics() throws SQLException { + long nclocMetric = insertMetric("ncloc"); + long conditionOnNcloc = insertQualityGateCondition(nclocMetric); + long issuesMetric = insertMetric("issues"); + long conditionOnIssues = insertQualityGateCondition(issuesMetric); + + underTest.execute(); + + verifyConditionIds(asList(conditionOnNcloc, conditionOnIssues)); + } + + @Test + public void do_nothing_when_no_condition() throws SQLException { + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_QUALITY_GATE_CONDITIONS)).isZero(); + } + + @Test + public void migration_is_reentrant() throws SQLException { + long securityHotspotsMetric = insertMetric("security_hotspots"); + insertQualityGateCondition(securityHotspotsMetric); + long newSecurityHotspotsMetric = insertMetric("new_security_hotspots"); + insertQualityGateCondition(newSecurityHotspotsMetric); + long otherMetric = insertMetric("ncloc"); + long conditionOnOtherMetric = insertQualityGateCondition(otherMetric); + + underTest.execute(); + underTest.execute(); + + verifyConditionIds(asList(conditionOnOtherMetric)); + } + + private void verifyConditionIds(List expectedConditionIds) { + List> results = db.select("select ID from " + TABLE_QUALITY_GATE_CONDITIONS); + assertThat(results.stream() + .map(map -> (long) map.get("ID")) + .collect(toList())) + .containsExactlyInAnyOrderElementsOf(expectedConditionIds); + } + + private long insertQualityGateCondition(long metricId) { + long qualityGateId = RandomUtils.nextInt(); + Map values = new HashMap<>(ImmutableMap.of("QGATE_ID", qualityGateId, "METRIC_ID", metricId, "OPERATOR", "GT")); + values.put("VALUE_ERROR", RandomUtils.nextInt()); + db.executeInsert(TABLE_QUALITY_GATE_CONDITIONS, values); + String sql = format("select id as \"id\" from %s where qgate_id='%s' and metric_id='%s'", TABLE_QUALITY_GATE_CONDITIONS, qualityGateId, metricId); + return (Long) db + .selectFirst(sql) + .get("id"); + } + + private long insertMetric(String key) { + db.executeInsert("metrics", "NAME", key); + return (Long) db.selectFirst(format("select id as \"id\" from metrics where name='%s'", key)).get("id"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetricsTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetricsTest/schema.sql new file mode 100644 index 00000000000..8a5718c3bc3 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v82/DeleteQgateConditionsUsingSecurityHotspotMetricsTest/schema.sql @@ -0,0 +1,34 @@ +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 "QUALITY_GATE_CONDITIONS"( + "ID" INTEGER NOT NULL AUTO_INCREMENT (1,1), + "QGATE_ID" INTEGER, + "METRIC_ID" INTEGER, + "PERIOD" INTEGER, + "OPERATOR" VARCHAR(3), + "VALUE_ERROR" VARCHAR(64), + "VALUE_WARNING" VARCHAR(64), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); +ALTER TABLE "QUALITY_GATE_CONDITIONS" ADD CONSTRAINT "PK_QUALITY_GATE_CONDITIONS" PRIMARY KEY("ID"); -- 2.39.5