From e0a058d7e9dc8cb4dce7b25e01d9702aa3554576 Mon Sep 17 00:00:00 2001 From: Belen Pruvost Date: Fri, 9 Apr 2021 12:29:00 +0200 Subject: [PATCH] SONAR-14681 improve project_measures metric purge performance --- server/sonar-db-dao/src/schema/schema-sq.ddl | 1 + .../db/migration/version/v84/DbVersion84.java | 9 +- ...AndAnalysisUuidOfProjectMeasuresTable.java | 66 ++++++++++++++ ...dexOnMetricUuidOfProjectMeasuresTable.java | 7 +- ...teSecurityReviewRatingProjectMeasures.java | 46 +++++----- ...nalysisUuidOfProjectMeasuresTableTest.java | 50 +++++++++++ ...nMetricUuidOfProjectMeasuresTableTest.java | 4 +- ...curityReviewRatingProjectMeasuresTest.java | 87 +++++++++++++++++++ .../schema.sql | 21 +++++ .../schema.sql | 42 +++++++++ 10 files changed, 301 insertions(+), 32 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable.java rename server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/{v82 => v84/metrics/projectmeasures}/DeleteSecurityReviewRatingProjectMeasures.java (52%) create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasuresTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasuresTest/schema.sql diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index 6a5712939d0..3430f5a74c4 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -637,6 +637,7 @@ CREATE TABLE "PROJECT_MEASURES"( ALTER TABLE "PROJECT_MEASURES" ADD CONSTRAINT "PK_PROJECT_MEASURES" PRIMARY KEY("UUID"); CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES"("COMPONENT_UUID"); CREATE INDEX "MEASURES_ANALYSIS_METRIC" ON "PROJECT_MEASURES"("ANALYSIS_UUID", "METRIC_UUID"); +CREATE INDEX "PROJECT_MEASURES_METRIC" ON "PROJECT_MEASURES"("METRIC_UUID"); CREATE TABLE "PROJECT_QGATES"( "PROJECT_UUID" VARCHAR(40) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java index 8e4a77256da..8785f8d7a7b 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/DbVersion84.java @@ -126,8 +126,10 @@ import org.sonar.server.platform.db.migration.version.v84.metrics.manualmeasures import org.sonar.server.platform.db.migration.version.v84.metrics.manualmeasures.DropMetricIdColumnOfManualMeasuresTable; import org.sonar.server.platform.db.migration.version.v84.metrics.manualmeasures.MakeManualMeasuresMetricUuidNotNullable; import org.sonar.server.platform.db.migration.version.v84.metrics.manualmeasures.PopulateManualMeasuresMetricUuid; +import org.sonar.server.platform.db.migration.version.v84.metrics.projectmeasures.AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable; import org.sonar.server.platform.db.migration.version.v84.metrics.projectmeasures.AddIndexOnMetricUuidOfProjectMeasuresTable; import org.sonar.server.platform.db.migration.version.v84.metrics.projectmeasures.AddMetricUuidColumnToProjectMeasures; +import org.sonar.server.platform.db.migration.version.v84.metrics.projectmeasures.DeleteSecurityReviewRatingProjectMeasures; import org.sonar.server.platform.db.migration.version.v84.metrics.projectmeasures.DropIndexOnMetricIdOfProjectMeasuresTable; import org.sonar.server.platform.db.migration.version.v84.metrics.projectmeasures.DropMetricIdColumnOfProjectMeasuresTable; import org.sonar.server.platform.db.migration.version.v84.metrics.projectmeasures.MakeProjectMeasuresMetricUuidNotNullable; @@ -525,7 +527,7 @@ public class DbVersion84 implements DbVersion { .add(3533, "Populate 'metric_uuid' for 'PROJECT_MEASURES'", PopulateProjectMeasuresMetricUuid.class) .add(3534, "Make 'metric_uuid' column not nullable for 'PROJECT_MEASURES'", MakeProjectMeasuresMetricUuidNotNullable.class) .add(3535, "Drop index on 'metric_id' and 'analysis_uuid' columns of 'PROJECT_MEASURES' table", DropIndexOnMetricIdOfProjectMeasuresTable.class) - .add(3536, "Add index on 'metric_uuid' and 'analysis_uuid' columns of 'PROJECT_MEASURES' table", AddIndexOnMetricUuidOfProjectMeasuresTable.class) + .add(3536, "Add index on 'metric_uuid' and 'analysis_uuid' columns of 'PROJECT_MEASURES' table", AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable.class) // Migration of FK in QUALITY_GATE_CONDITIONS to METRICS .add(3537, "Add 'metric_uuid' column on 'QUALITY_GATE_CONDITIONS' table", AddMetricUuidColumnToQualityGateConditions.class) @@ -788,6 +790,11 @@ public class DbVersion84 implements DbVersion { // Migration of ALM_SETTINGS table .add(3807, "Add columns 'CLIENT_ID' and 'CLIENT_SECRET' to 'ALM_SETTINGS' table", AddClientIdAndClientSecretColumns.class) + + + // Removing old data from project_measures + .add(3808, "Add index on 'metric_uuid' column of 'PROJECT_MEASURES' table", AddIndexOnMetricUuidOfProjectMeasuresTable.class) + .add(3809, "Remove old Security Review Rating ProjectMeasures", DeleteSecurityReviewRatingProjectMeasures.class) ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable.java new file mode 100644 index 00000000000..347edef0708 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable.java @@ -0,0 +1,66 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v84.metrics.projectmeasures; + +import java.sql.Connection; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable extends DdlChange { + private static final String TABLE_NAME = "project_measures"; + private static final String INDEX_NAME = "measures_analysis_metric"; + + public AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + if (!indexExists()) { + context.execute(new CreateIndexBuilder() + .setUnique(false) + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("analysis_uuid") + .setIsNullable(false) + .setLimit(VarcharColumnDef.UUID_SIZE) + .build()) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("metric_uuid") + .setIsNullable(false) + .setLimit(VarcharColumnDef.UUID_SIZE) + .build()) + .build()); + } + } + + private boolean indexExists() throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + return DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidOfProjectMeasuresTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidOfProjectMeasuresTable.java index 02efabdab41..8473487e14f 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidOfProjectMeasuresTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidOfProjectMeasuresTable.java @@ -31,7 +31,7 @@ import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVar public class AddIndexOnMetricUuidOfProjectMeasuresTable extends DdlChange { private static final String TABLE_NAME = "project_measures"; - private static final String INDEX_NAME = "measures_analysis_metric"; + private static final String INDEX_NAME = "project_measures_metric"; public AddIndexOnMetricUuidOfProjectMeasuresTable(Database db) { super(db); @@ -44,11 +44,6 @@ public class AddIndexOnMetricUuidOfProjectMeasuresTable extends DdlChange { .setUnique(false) .setTable(TABLE_NAME) .setName(INDEX_NAME) - .addColumn(newVarcharColumnDefBuilder() - .setColumnName("analysis_uuid") - .setIsNullable(false) - .setLimit(VarcharColumnDef.UUID_SIZE) - .build()) .addColumn(newVarcharColumnDefBuilder() .setColumnName("metric_uuid") .setIsNullable(false) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingProjectMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasures.java similarity index 52% rename from server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingProjectMeasures.java rename to server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasures.java index cdadbe18934..d4fc9f53eee 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v82/DeleteSecurityReviewRatingProjectMeasures.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasures.java @@ -17,7 +17,7 @@ * 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; +package org.sonar.server.platform.db.migration.version.v84.metrics.projectmeasures; import java.sql.SQLException; import javax.annotation.Nullable; @@ -29,7 +29,6 @@ public class DeleteSecurityReviewRatingProjectMeasures extends DataChange { private static final String SECURITY_REVIEW_RATING_METRIC_KEY = "security_review_rating"; private static final String SECURITY_REVIEW_RATING_EFFORT_METRIC_KEY = "security_review_rating_effort"; - private static final String SELECT_COMPONENTS_STATEMENT = "select c.uuid from components c where c.scope in ('PRJ')"; public DeleteSecurityReviewRatingProjectMeasures(Database db) { super(db); @@ -37,37 +36,38 @@ public class DeleteSecurityReviewRatingProjectMeasures extends DataChange { @Override protected void execute(Context context) throws SQLException { - Integer reviewRatingId = getMetricId(context, SECURITY_REVIEW_RATING_METRIC_KEY); - Integer reviewRatingEffortId = getMetricId(context, SECURITY_REVIEW_RATING_EFFORT_METRIC_KEY); - if (reviewRatingId != null) { - deleteFromProjectMeasures(context, reviewRatingId, reviewRatingEffortId); + String reviewRatingUuid = getMetricUuid(context, SECURITY_REVIEW_RATING_METRIC_KEY); + String reviewRatingEffortUuid = getMetricUuid(context, SECURITY_REVIEW_RATING_EFFORT_METRIC_KEY); + if (reviewRatingUuid != null) { + deleteFromProjectMeasures(context, reviewRatingUuid, reviewRatingEffortUuid); } } @Nullable - private static Integer getMetricId(Context context, String metricName) throws SQLException { - return context.prepareSelect("select id from metrics where name = ?") + private static String getMetricUuid(Context context, String metricName) throws SQLException { + return context.prepareSelect("select uuid from metrics where name = ?") .setString(1, metricName) - .get(row -> row.getNullableInt(1)); + .get(row -> row.getNullableString(1)); } - private static void deleteFromProjectMeasures(Context context, Integer reviewRatingId, @Nullable Integer reviewRatingEffortId) throws SQLException { - MassUpdate deleteFromProjectMeasures = context.prepareMassUpdate(); + private static void deleteFromProjectMeasures(Context context, String reviewRatingUuid, @Nullable String reviewRatingEffortUuid) throws SQLException { + deleteFromProjectMeasures(context, reviewRatingUuid); - deleteFromProjectMeasures.select(SELECT_COMPONENTS_STATEMENT); - if (reviewRatingEffortId != null) { - deleteFromProjectMeasures.update("delete from project_measures where component_uuid = ? and metric_id in (?, ?)"); - } else { - deleteFromProjectMeasures.update("delete from project_measures where component_uuid = ? and metric_id = ?"); + if (reviewRatingEffortUuid != null) { + deleteFromProjectMeasures(context, reviewRatingEffortUuid); } + } - deleteFromProjectMeasures.execute((row, update) -> { - String componentUuid = row.getString(1); - update.setString(1, componentUuid) - .setInt(2, reviewRatingId); - if (reviewRatingEffortId != null) { - update.setInt(3, reviewRatingEffortId); - } + private static void deleteFromProjectMeasures(Context context, @Nullable String metricUuid) throws SQLException { + if (metricUuid == null) { + return; + } + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select uuid from project_measures where metric_uuid = ?") + .setString(1, metricUuid); + massUpdate.update("delete from project_measures where uuid = ?"); + massUpdate.execute((row, update) -> { + update.setString(1, row.getString(1)); return true; }); } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest.java new file mode 100644 index 00000000000..073c6b20a71 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest.java @@ -0,0 +1,50 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v84.metrics.projectmeasures; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.MigrationStep; + +public class AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest.class, "schema.sql"); + + private MigrationStep underTest = new AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTable(db.database()); + + @Test + public void execute() throws SQLException { + underTest.execute(); + + db.assertIndex("project_measures", "measures_analysis_metric", "analysis_uuid", "metric_uuid"); + } + + @Test + public void migration_is_re_entrant() throws SQLException { + underTest.execute(); + + // re-entrant + underTest.execute(); + + db.assertIndex("project_measures", "measures_analysis_metric", "analysis_uuid", "metric_uuid"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidOfProjectMeasuresTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidOfProjectMeasuresTableTest.java index da75278cd50..47482eabfe4 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidOfProjectMeasuresTableTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidOfProjectMeasuresTableTest.java @@ -35,7 +35,7 @@ public class AddIndexOnMetricUuidOfProjectMeasuresTableTest { public void execute() throws SQLException { underTest.execute(); - db.assertIndex("project_measures", "measures_analysis_metric", "analysis_uuid", "metric_uuid"); + db.assertIndex("project_measures", "project_measures_metric", "metric_uuid"); } @Test @@ -45,6 +45,6 @@ public class AddIndexOnMetricUuidOfProjectMeasuresTableTest { // re-entrant underTest.execute(); - db.assertIndex("project_measures", "measures_analysis_metric", "analysis_uuid", "metric_uuid"); + db.assertIndex("project_measures", "project_measures_metric", "metric_uuid"); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasuresTest.java new file mode 100644 index 00000000000..b332e8b3eb6 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasuresTest.java @@ -0,0 +1,87 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v84.metrics.projectmeasures; + +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 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 DeleteSecurityReviewRatingProjectMeasuresTest { + private static final String PROJECT_MEASURES_TABLE = "project_measures"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DeleteSecurityReviewRatingProjectMeasuresTest.class, "schema.sql"); + + private DataChange underTest = new DeleteSecurityReviewRatingProjectMeasures(db.database()); + + @Test + public void removes_project_measure_for_review_rating_and_review_rating_effort_metrics() throws SQLException { + String codeSmellMetric = insertMetric(1L, "security_review_rating"); + insertProjectMeasure(codeSmellMetric); + String reviewRatingEffort = insertMetric(2L, "security_review_rating_effort"); + insertProjectMeasure(reviewRatingEffort); + String anotherMetric = insertMetric(3L, "another_metric"); + String anotherMetricProjectMeasure = insertProjectMeasure(anotherMetric); + + underTest.execute(); + verifyProjectMeasureIds(singletonList(anotherMetricProjectMeasure)); + } + + + private String insertMetric(Long id, String key) { + db.executeInsert("metrics", + "id", id, + "uuid", id, + "name", key); + + return (String) db.selectFirst(format("select uuid as \"uuid\" from metrics where name='%s'", key)).get("uuid"); + } + + private String insertProjectMeasure(String metricUuid) { + double projectMeasureUUid = RandomUtils.nextDouble(); + Map values = new HashMap<>(ImmutableMap.of("uuid", projectMeasureUUid, "metric_uuid", metricUuid, + "analysis_uuid", "analysis_uuid", "component_uuid", "component_uuid")); + db.executeInsert(PROJECT_MEASURES_TABLE, values); + String sql = format("select uuid as \"uuid\" from %s where metric_uuid='%s'", PROJECT_MEASURES_TABLE, metricUuid); + return (String) db + .selectFirst(sql) + .get("uuid"); + } + + private void verifyProjectMeasureIds(List expectedProjectMeasureIds) { + List> results = db.select("select uuid from " + PROJECT_MEASURES_TABLE); + assertThat(results.stream() + .map(map -> (String) map.get("UUID")) + .collect(toList())) + .containsExactlyInAnyOrderElementsOf(expectedProjectMeasureIds); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest/schema.sql new file mode 100644 index 00000000000..bcd93c5d249 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/AddIndexOnMetricUuidAndAnalysisUuidOfProjectMeasuresTableTest/schema.sql @@ -0,0 +1,21 @@ +CREATE TABLE "PROJECT_MEASURES"( + "VALUE" DOUBLE, + "METRIC_UUID" VARCHAR(40) NOT NULL, + "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, + "UUID" VARCHAR(40) NOT NULL +); +ALTER TABLE "PROJECT_MEASURES" ADD CONSTRAINT "PK_PROJECT_MEASURES" PRIMARY KEY("UUID"); +CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES"("COMPONENT_UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasuresTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasuresTest/schema.sql new file mode 100644 index 00000000000..8200c0c931e --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v84/metrics/projectmeasures/DeleteSecurityReviewRatingProjectMeasuresTest/schema.sql @@ -0,0 +1,42 @@ +CREATE TABLE "METRICS"( + "ID" INTEGER NOT NULL AUTO_INCREMENT (1,1), + "UUID" VARCHAR(40) NOT NULL, + "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 "PROJECT_MEASURES"( + "VALUE" DOUBLE, + "METRIC_UUID" VARCHAR(40) 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, + "UUID" VARCHAR(40) NOT NULL +); +ALTER TABLE "PROJECT_MEASURES" ADD CONSTRAINT "PK_PROJECT_MEASURES" PRIMARY KEY("UUID"); +CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES"("COMPONENT_UUID"); -- 2.39.5