From a23cea9726fdcbfb4348a9c2335ae82ecab85c79 Mon Sep 17 00:00:00 2001 From: Julien Lancelot Date: Wed, 15 Feb 2017 10:22:38 +0100 Subject: [PATCH] SONAR-8746 Remove measures related to periods 2 to 5 --- .../db/migration/version/v63/DbVersion63.java | 4 +- .../v63/DeleteMeasuresHavingNoValue.java | 56 ++++++ .../version/v63/DbVersion63Test.java | 2 +- .../v63/DeleteMeasuresHavingNoValueTest.java | 169 ++++++++++++++++++ .../project_measures.sql | 21 +++ .../org/sonar/db/version/rows-h2.sql | 1 + 6 files changed, 251 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValue.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValueTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValueTest/project_measures.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63.java index 39cac92ce80..c2815a97bcf 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63.java @@ -48,6 +48,8 @@ public class DbVersion63 implements DbVersion { .add(1513, "Make default organization guarded", MakeDefaultOrganizationGuarded.class) .add(1514, "Delete some entries in PROPERTIES", DeleteUselessProperties.class) .add(1515, "Unset user root flags", UnsetUserRootFlags.class) - .add(1516, "Add ORGANIZATIONS.USER_ID", AddUserIdToOrganizations.class); + .add(1516, "Add ORGANIZATIONS.USER_ID", AddUserIdToOrganizations.class) + .add(1517, "Delete PROJECT_MEASURES rows having no value", DeleteMeasuresHavingNoValue.class) + ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValue.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValue.java new file mode 100644 index 00000000000..e2aa14bdfc9 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValue.java @@ -0,0 +1,56 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v63; + +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; + +/** + * Delete measures having no value in order to remove measures related to periods 2 to 5. + *
+ * Here are some example of measures to remove : + * + */ +public class DeleteMeasuresHavingNoValue extends DataChange { + + public DeleteMeasuresHavingNoValue(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("SELECT pm.id FROM project_measures pm " + + "WHERE pm.value IS NULL AND pm.text_value IS NULL AND pm.variation_value_1 IS NULL AND pm.measure_data IS NULL "); + massUpdate.update("DELETE FROM project_measures WHERE id=?"); + massUpdate.rowPluralName("measures"); + massUpdate.execute((row, update) -> { + update.setLong(1, row.getLong(1)); + return true; + }); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63Test.java index 8d6fbfc49dd..e9c26d7fefa 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63Test.java @@ -41,7 +41,7 @@ public class DbVersion63Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 17); + verifyMigrationCount(underTest, 18); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValueTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValueTest.java new file mode 100644 index 00000000000..b65234b9e40 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValueTest.java @@ -0,0 +1,169 @@ +/* + * SonarQube + * Copyright (C) 2009-2017 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.v63; + +import com.google.common.collect.ImmutableMap; +import java.nio.charset.StandardCharsets; +import java.sql.SQLException; +import java.util.HashMap; +import java.util.Map; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +import static java.lang.String.valueOf; +import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric; +import static org.assertj.core.api.Assertions.assertThat; + +public class DeleteMeasuresHavingNoValueTest { + + private static final String TABLE_MEASURES = "project_measures"; + + @Rule + public DbTester db = DbTester.createForSchema(System2.INSTANCE, DeleteMeasuresHavingNoValueTest.class, "project_measures.sql"); + + private DeleteMeasuresHavingNoValue underTest = new DeleteMeasuresHavingNoValue(db.database()); + + @Test + public void migration_has_no_effect_on_empty_tables() throws SQLException { + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isZero(); + } + + @Test + public void migration_does_not_remove_measures_with_value() throws SQLException { + insertMeasure(5d, null, null, null); + insertMeasure(null, "text", null, null); + insertMeasure(null, null, "data", null); + insertMeasure(null, null, null, 50d); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isEqualTo(4); + } + + @Test + public void migration_removes_measures_with_no_values() throws SQLException { + insertMeasure(null, null, null, null); + insertMeasure(null, null, null, null); + insertMeasure(null, null, null, null); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isZero(); + } + + @Test + public void migration_does_not_remove_measures_having_variation_on_leak_period() throws SQLException { + insertMeasureOnlyOnVariations(10d, null, null, null, null); + insertMeasureOnlyOnVariations(11d, 2d, null, null, null); + insertMeasureOnlyOnVariations(12d, null, 3d, 4d, 5d); + insertMeasureOnlyOnVariations(12d, 2d, 3d, 4d, 5d); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isEqualTo(4); + } + + @Test + public void migration_removes_measures_having_only_variation_on_periods2_to_5() throws SQLException { + insertMeasureOnlyOnVariations(null, 2d, null, null, null); + insertMeasureOnlyOnVariations(null, null, 3d, null, null); + insertMeasureOnlyOnVariations(null, null, null, 4d, null); + insertMeasureOnlyOnVariations(null, null, null, null, 5d); + insertMeasureOnlyOnVariations(null, 2d, 3d, 4d, 5d); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isZero(); + } + + @Test + public void migration_is_reentrant() throws SQLException { + insertMeasure(5d, null, null, null); + insertMeasure(null, "text", null, null); + insertMeasure(null, null, null, null); + insertMeasure(null, null, null, null); + db.commit(); + + underTest.execute(); + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isEqualTo(2); + + underTest.execute(); + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isEqualTo(2); + } + + private void insertMeasure(@Nullable Double value, @Nullable String textValue, @Nullable String data, @Nullable Double variation) { + Map values = new HashMap<>(ImmutableMap.builder() + .put("METRIC_ID", valueOf(10)) + .put("COMPONENT_UUID", randomAlphanumeric(10)) + .put("ANALYSIS_UUID", randomAlphanumeric(10)) + .put("VARIATION_VALUE_2", 2d) + .put("VARIATION_VALUE_3", 3d) + .put("VARIATION_VALUE_4", 4d) + .put("VARIATION_VALUE_5", 5d) + .build()); + if (value != null) { + values.put("VALUE", valueOf(value)); + } + if (textValue != null) { + values.put("TEXT_VALUE", textValue); + } + if (variation != null) { + values.put("VARIATION_VALUE_1", variation); + } + if (data != null) { + values.put("MEASURE_DATA", data.getBytes(StandardCharsets.UTF_8)); + } + db.executeInsert(TABLE_MEASURES, values); + } + + private void insertMeasureOnlyOnVariations(@Nullable Double variation1, @Nullable Double variation2, @Nullable Double variation3, @Nullable Double variation4, + @Nullable Double variation5) { + Map values = new HashMap<>(ImmutableMap.of( + "METRIC_ID", valueOf(20), + "COMPONENT_UUID", randomAlphanumeric(10), + "ANALYSIS_UUID", randomAlphanumeric(10))); + if (variation1 != null) { + values.put("VARIATION_VALUE_1", valueOf(variation1)); + } + if (variation2 != null) { + values.put("VARIATION_VALUE_2", valueOf(variation2)); + } + if (variation3 != null) { + values.put("VARIATION_VALUE_3", valueOf(variation3)); + } + if (variation4 != null) { + values.put("VARIATION_VALUE_4", valueOf(variation4)); + } + if (variation5 != null) { + values.put("VARIATION_VALUE_4", valueOf(variation5)); + } + db.executeInsert(TABLE_MEASURES, values); + } + +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValueTest/project_measures.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValueTest/project_measures.sql new file mode 100644 index 00000000000..00ef5e22307 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/DeleteMeasuresHavingNoValueTest/project_measures.sql @@ -0,0 +1,21 @@ +CREATE TABLE "PROJECT_MEASURES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "VALUE" DOUBLE, + "METRIC_ID" INTEGER NOT NULL, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "ANALYSIS_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" BINARY +); +CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES" ("COMPONENT_UUID"); +CREATE INDEX "MEASURES_ANALYSIS_METRIC" ON "PROJECT_MEASURES" ("ANALYSIS_UUID", "METRIC_ID"); +CREATE INDEX "MEASURES_PERSON" ON "PROJECT_MEASURES" ("PERSON_ID"); diff --git a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql index a996126ea44..5892eee83ae 100644 --- a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql +++ b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql @@ -530,6 +530,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1513'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1514'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1515'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1516'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1517'); INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, IS_ROOT, CREATED_AT, UPDATED_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', false, '1418215735482', '1418215735482'); ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; -- 2.39.5