From c47c9c878ceaeb699f5c6fca6ac5f18ce10a507b Mon Sep 17 00:00:00 2001 From: Julien HENRY Date: Tue, 11 Dec 2018 12:09:21 +0100 Subject: [PATCH] SONAR-10180 Clean DB: remove property and folder/module level measures --- .../db/migration/version/v76/DbVersion76.java | 2 + .../v76/DeleteModuleAndFolderMeasures.java | 52 ++++++++ .../version/v76/DeleteUselessProperty.java | 45 +++++++ .../version/v76/DbVersion76Test.java | 2 +- .../DeleteModuleAndFolderMeasuresTest.java | 125 ++++++++++++++++++ .../v76/DeleteUselessPropertyTest.java | 108 +++++++++++++++ .../project_measures.sql | 67 ++++++++++ .../DeleteUselessPropertyTest/properties.sql | 11 ++ 8 files changed, 411 insertions(+), 1 deletion(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasures.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DeleteUselessProperty.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasuresTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DeleteUselessPropertyTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasuresTest/project_measures.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v76/DeleteUselessPropertyTest/properties.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DbVersion76.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DbVersion76.java index 550e9b6286d..98804723ac6 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DbVersion76.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DbVersion76.java @@ -30,6 +30,8 @@ public class DbVersion76 implements DbVersion { .add(2500, "Create table USER_PROPERTIES", CreateUserPropertiesTable.class) .add(2501, "Add index in table USER_PROPERTIES", AddUniqueIndexInUserPropertiesTable.class) .add(2502, "Archive module properties in a new project level property", MigrateModuleProperties.class) + .add(2503, "Delete useless 'sonar.dbcleaner.cleanDirectory' property", DeleteUselessProperty.class) + .add(2504, "Delete useless module and folder level measures", DeleteModuleAndFolderMeasures.class) .add(2505, "Fix the direction values of certain metrics (prepare for migration of conditions)", FixDirectionOfMetrics.class) .add(2506, "Migrate quality gate conditions using warning, period and no more supported operations", MigrateNoMoreUsedQualityGateConditions.class); } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasures.java new file mode 100644 index 00000000000..48f699bc209 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasures.java @@ -0,0 +1,52 @@ +/* + * 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.v76; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.SupportsBlueGreen; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +/** + * Remove module and folder level measures + */ +@SupportsBlueGreen +public class DeleteModuleAndFolderMeasures extends DataChange { + + public DeleteModuleAndFolderMeasures(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate().rowPluralName("components"); + massUpdate.select("SELECT p.uuid FROM projects p WHERE p.qualifier in ('DIR', 'BRC') AND exists(SELECT 1 FROM project_measures m WHERE m.component_uuid = p.uuid)"); + massUpdate.update("DELETE FROM project_measures WHERE component_uuid=?") + // important to keep the number of rows in a transaction under control. A component may have dozens/hundreds of measures to be deleted. + .setBatchSize(1); + massUpdate.execute((row, update) -> { + String componentUuid = row.getString(1); + update.setString(1, componentUuid); + return true; + }); + } + +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DeleteUselessProperty.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DeleteUselessProperty.java new file mode 100644 index 00000000000..c91ba9dcb94 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v76/DeleteUselessProperty.java @@ -0,0 +1,45 @@ +/* + * 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.v76; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.SupportsBlueGreen; +import org.sonar.server.platform.db.migration.step.DataChange; + +/** + * Remove the following settings from the PROPERTIES table : + * - sonar.dbcleaner.cleanDirectory + */ +@SupportsBlueGreen +public class DeleteUselessProperty extends DataChange { + + public DeleteUselessProperty(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.prepareUpsert("DELETE FROM properties WHERE prop_key = 'sonar.dbcleaner.cleanDirectory'") + .execute() + .commit(); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DbVersion76Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DbVersion76Test.java index 595ce34382e..deba20879e5 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DbVersion76Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DbVersion76Test.java @@ -35,7 +35,7 @@ public class DbVersion76Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 5); + verifyMigrationCount(underTest, 7); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasuresTest.java new file mode 100644 index 00000000000..a9838f2a623 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasuresTest.java @@ -0,0 +1,125 @@ +/* + * 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.v76; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static java.lang.String.valueOf; +import static org.assertj.core.api.Assertions.assertThat; + +public class DeleteModuleAndFolderMeasuresTest { + + private static final String TABLE_MEASURES = "project_measures"; + private static final int COMPONENT_ID_1 = 125; + private static final int COMPONENT_ID_2 = 604; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DeleteModuleAndFolderMeasuresTest.class, "project_measures.sql"); + + private DeleteModuleAndFolderMeasures underTest = new DeleteModuleAndFolderMeasures(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_removes_module_level_measures() throws SQLException { + String moduleUuid = insertComponent(1, "BRC"); + insertMeasure(1, moduleUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isZero(); + } + + @Test + public void migration_removes_folder_level_measures() throws SQLException { + String dirUuid = insertComponent(1, "DIR"); + insertMeasure(1, dirUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isZero(); + } + + @Test + public void migration_ignores_not_relevant_measures() throws SQLException { + String projectUuid = insertComponent(1, "TRK"); + insertMeasure(1, projectUuid); + String moduleUuid = insertComponent(2, "BRC"); + insertMeasure(2, moduleUuid); + insertMeasure(3, moduleUuid); + String dirUuid = insertComponent(3, "DIR"); + insertMeasure(4, dirUuid); + insertMeasure(5, dirUuid); + String fileUuid = insertComponent(4, "FIL"); + insertMeasure(6, fileUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isEqualTo(2); + } + + @Test + public void migration_is_reentrant() throws SQLException { + String dirUuid = insertComponent(3, "DIR"); + insertMeasure(1, dirUuid); + String fileUuid = insertComponent(4, "FIL"); + insertMeasure(2, fileUuid); + + underTest.execute(); + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isEqualTo(1); + + underTest.execute(); + assertThat(db.countRowsOfTable(TABLE_MEASURES)).isEqualTo(1); + } + + private String insertComponent(long id, String qualifier) { + String uuid = "uuid_" + id; + db.executeInsert( + "projects", + "ID", valueOf(id), + "QUALIFIER", qualifier, + "ORGANIZATION_UUID", "org_" + id, + "UUID_PATH", "path_" + id, + "ROOT_UUID", "root_" + id, + "PROJECT_UUID", "project_" + id, + "PRIVATE", false, + "UUID", uuid); + return uuid; + } + + private void insertMeasure(long id, String componentUuid) { + db.executeInsert( + "project_measures", + "ID", valueOf(id), + "COMPONENT_UUID", componentUuid, + "METRIC_ID", valueOf(id + 10), + "ANALYSIS_UUID", valueOf(id + 100), + "VALUE", valueOf(id + 1000)); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DeleteUselessPropertyTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DeleteUselessPropertyTest.java new file mode 100644 index 00000000000..db89c37c346 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v76/DeleteUselessPropertyTest.java @@ -0,0 +1,108 @@ +/* + * 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.v76; + +import com.google.common.collect.ImmutableMap; +import java.sql.SQLException; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.stream.Collectors; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static java.lang.String.valueOf; +import static org.assertj.core.api.Assertions.assertThat; + +public class DeleteUselessPropertyTest { + + private static final String TABLE_PROPERTIES = "properties"; + private static final int COMPONENT_ID_1 = 125; + private static final int COMPONENT_ID_2 = 604; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DeleteUselessPropertyTest.class, "properties.sql"); + + private DeleteUselessProperty underTest = new DeleteUselessProperty(db.database()); + + @Test + public void migration_has_no_effect_on_empty_tables() throws SQLException { + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isZero(); + } + + @Test + public void migration_removes_cleanDirectory_settings_related_to_component() throws SQLException { + insertProperty("sonar.dbcleaner.cleanDirectory", COMPONENT_ID_1); + insertProperty("sonar.dbcleaner.cleanDirectory", COMPONENT_ID_1); + insertProperty("sonar.dbcleaner.cleanDirectory", COMPONENT_ID_2); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isZero(); + } + + @Test + public void migration_ignores_not_relevant_settings() throws SQLException { + insertProperty("sonar.core.serverBaseURL", null); + // Only this setting should be removed + insertProperty("sonar.dbcleaner.cleanDirectory", null); + + underTest.execute(); + + verifyPropertyKeys("sonar.core.serverBaseURL"); + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(1); + } + + @Test + public void migration_is_reentrant() throws SQLException { + insertProperty("sonar.core.serverBaseURL", null); + // Only this setting should be removed + insertProperty("sonar.dbcleaner.cleanDirectory", null); + + underTest.execute(); + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(1); + + underTest.execute(); + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(1); + } + + private void insertProperty(String key, @Nullable Integer componentId) { + Map values = new HashMap<>(ImmutableMap.of( + "PROP_KEY", key, + "IS_EMPTY", false, + "CREATED_AT", 456789)); + if (componentId != null) { + values.put("RESOURCE_ID", valueOf(componentId)); + } + db.executeInsert(TABLE_PROPERTIES, values); + } + + private void verifyPropertyKeys(String... propertyKeys) { + List> rows = db.select("select prop_key from " + TABLE_PROPERTIES); + Set result = rows.stream().map(cols -> (String)cols.get("PROP_KEY")).collect(Collectors.toSet()); + assertThat(result).containsOnly(propertyKeys); + } + +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasuresTest/project_measures.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasuresTest/project_measures.sql new file mode 100644 index 00000000000..dc4e4390dd8 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v76/DeleteModuleAndFolderMeasuresTest/project_measures.sql @@ -0,0 +1,67 @@ +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 TABLE "PROJECTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400), + "UUID" VARCHAR(50) NOT NULL, + "UUID_PATH" VARCHAR(1500) NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(1500), + "MAIN_BRANCH_PROJECT_UUID" VARCHAR(50), + "NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "PRIVATE" BOOLEAN NOT NULL, + "TAGS" VARCHAR(500), + "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "DEPRECATED_KEE" VARCHAR(400), + "PATH" VARCHAR(2000), + "LANGUAGE" VARCHAR(20), + "COPY_COMPONENT_UUID" VARCHAR(50), + "LONG_NAME" VARCHAR(2000), + "DEVELOPER_UUID" VARCHAR(50), + "CREATED_AT" TIMESTAMP, + "AUTHORIZATION_UPDATED_AT" BIGINT, + "B_CHANGED" BOOLEAN, + "B_COPY_COMPONENT_UUID" VARCHAR(50), + "B_DESCRIPTION" VARCHAR(2000), + "B_ENABLED" BOOLEAN, + "B_UUID_PATH" VARCHAR(1500), + "B_LANGUAGE" VARCHAR(20), + "B_LONG_NAME" VARCHAR(500), + "B_MODULE_UUID" VARCHAR(50), + "B_MODULE_UUID_PATH" VARCHAR(1500), + "B_NAME" VARCHAR(500), + "B_PATH" VARCHAR(2000), + "B_QUALIFIER" VARCHAR(10) +); +CREATE INDEX "PROJECTS_ORGANIZATION" ON "PROJECTS" ("ORGANIZATION_UUID"); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE"); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "PROJECTS" ("ROOT_UUID"); +CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID"); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "PROJECTS" ("PROJECT_UUID"); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "PROJECTS" ("MODULE_UUID"); +CREATE INDEX "PROJECTS_QUALIFIER" ON "PROJECTS" ("QUALIFIER"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v76/DeleteUselessPropertyTest/properties.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v76/DeleteUselessPropertyTest/properties.sql new file mode 100644 index 00000000000..d84c238cd48 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v76/DeleteUselessPropertyTest/properties.sql @@ -0,0 +1,11 @@ +CREATE TABLE "PROPERTIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROP_KEY" VARCHAR(512) NOT NULL, + "RESOURCE_ID" INTEGER, + "USER_ID" INTEGER, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" VARCHAR(4000), + "CLOB_VALUE" CLOB, + "CREATED_AT" BIGINT +); +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY"); -- 2.39.5