From d42b199206f3b61624695e5d72109618e1dd0ecf Mon Sep 17 00:00:00 2001 From: Simon Brandhof Date: Tue, 3 Oct 2017 18:00:49 +0200 Subject: [PATCH] SONAR-9881 Purge unused settings in database --- .../db/migration/version/v66/DbVersion66.java | 4 +- .../version/v66/PurgeTableProperties.java | 63 ++++++++++++ .../version/v66/DbVersion66Test.java | 2 +- .../version/v66/PurgeTablePropertiesTest.java | 95 +++++++++++++++++++ .../PurgeTablePropertiesTest/properties.sql | 11 +++ 5 files changed, 173 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v66/PurgeTableProperties.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v66/PurgeTablePropertiesTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v66/PurgeTablePropertiesTest/properties.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v66/DbVersion66.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v66/DbVersion66.java index bbaf6c984bf..6ab87afd264 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v66/DbVersion66.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v66/DbVersion66.java @@ -39,6 +39,8 @@ public class DbVersion66 implements DbVersion { .add(1810, "Add ce_activity.error_type", AddErrorTypeColumnToCeActivityTable.class) .add(1811, "Create table qprofile_edit_users", CreateTableQProfileEditUsers.class) .add(1812, "Create table qprofile_edit_groups", CreateTableQProfileEditGroups.class) - .add(1813, "Drop PR columns from project_brances", DropPrColumnsFromProjectBranches.class); + .add(1813, "Drop PR columns from project_branches", DropPrColumnsFromProjectBranches.class) + .add(1814, "Purge table properties", PurgeTableProperties.class) + ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v66/PurgeTableProperties.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v66/PurgeTableProperties.java new file mode 100644 index 00000000000..28e4dec494a --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v66/PurgeTableProperties.java @@ -0,0 +1,63 @@ +/* + * 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.v66; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; + +public class PurgeTableProperties extends DataChange { + + public PurgeTableProperties(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + deleteByKey(context, "views.analysisDelayingInMinutes"); + deleteByKey(context, "views.status"); + deleteByKey(context, "sonar.issuesdensity.weight"); + deleteByKey(context, "sonar.core.version"); + deleteByKeyPrefix(context, "sonar.reports."); + deleteByKeyPrefix(context, "sonar.report.license"); + deleteByKeyPrefix(context, "sonar.sqale."); + deleteByKeyPrefix(context, "sqale.license"); + deleteByKeyPrefix(context, "devcockpit."); + deleteByKeyPrefix(context, "masterproject."); + deleteByKeyPrefix(context, "sonar.natural."); + deleteByKeyPrefix(context, "sonarsource.natural."); + deleteByKeyPrefix(context, "sonarsource.identity."); + deleteByKeyPrefix(context, "sonar.build-stability."); + } + + private static void deleteByKey(Context context, String key) throws SQLException { + context.prepareUpsert("delete from properties where prop_key = ?") + .setString(1, key) + .execute() + .commit(); + } + + private static void deleteByKeyPrefix(Context context, String key) throws SQLException { + context.prepareUpsert("delete from properties where prop_key like ?") + .setString(1, key + "%") + .execute() + .commit(); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v66/DbVersion66Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v66/DbVersion66Test.java index de7450360cf..073819a0ee4 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v66/DbVersion66Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v66/DbVersion66Test.java @@ -36,7 +36,7 @@ public class DbVersion66Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 13); + verifyMigrationCount(underTest, 14); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v66/PurgeTablePropertiesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v66/PurgeTablePropertiesTest.java new file mode 100644 index 00000000000..c2e7b1cf816 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v66/PurgeTablePropertiesTest.java @@ -0,0 +1,95 @@ +/* + * 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.v66; + +import java.util.List; +import java.util.Map; +import java.util.stream.Collectors; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.assertj.core.api.Assertions.assertThat; + +public class PurgeTablePropertiesTest { + + private static final String TABLE_PROPERTIES = "properties"; + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(PurgeTablePropertiesTest.class, "properties.sql"); + + private PurgeTableProperties underTest = new PurgeTableProperties(db.database()); + + @Test + public void migration_has_no_effect_on_empty_db() throws Exception { + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(0); + } + + @Test + public void migration_deletes_properties_by_keys() throws Exception { + // to be deleted + insert("views.analysisDelayingInMinutes"); + insert("views.status"); + insert("sonar.issuesdensity.weight"); + // to be kept + insert("views.status.differentSuffix"); + insert("views.foo"); + + underTest.execute(); + + verifyRemainingKeys("views.status.differentSuffix", "views.foo"); + } + + @Test + public void migration_deletes_properties_by_key_prefixes() throws Exception { + // to be deleted + insert("sonar.sqale.foo"); + insert("sonar.sqale.bar"); + // to be kept + insert("sonar.sqale"); + insert("sqale"); + + underTest.execute(); + + verifyRemainingKeys("sonar.sqale", "sqale"); + } + + private void insert(String key) { + // test the different combinations of keys + db.executeInsert(TABLE_PROPERTIES, "prop_key", key, "text_value", "foo", "is_empty", false); + db.executeInsert(TABLE_PROPERTIES, "prop_key", key, "text_value", "foo", "is_empty", false, "user_id", 100); + db.executeInsert(TABLE_PROPERTIES, "prop_key", key, "text_value", "foo", "is_empty", false, "resource_id", 200); + } + + private void verifyRemainingKeys(String... expectedKeys) { + assertThat(selectKeys()) + // verify that the 3 different combinations of rows are still present + .hasSize(expectedKeys.length * 3) + .containsOnly(expectedKeys); + } + + private List selectKeys() { + List> rows = db.select("select prop_key as \"key\" from properties"); + return rows.stream() + .map(row -> (String) row.get("key")) + .collect(Collectors.toList()); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v66/PurgeTablePropertiesTest/properties.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v66/PurgeTablePropertiesTest/properties.sql new file mode 100644 index 00000000000..dfc39d8d285 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v66/PurgeTablePropertiesTest/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(2147483647), + "CREATED_AT" BIGINT +); +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY"); -- 2.39.5