diff options
6 files changed, 244 insertions, 1 deletions
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 21b4611f42f..c77e68592ba 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 @@ -46,6 +46,7 @@ public class DbVersion63 implements DbVersion { .add(1511, "Populate ORGANIZATIONS.GUARDED", PopulateColumnGuardedOfOrganizations.class) .add(1512, "Make ORGANIZATIONS.GUARDED not nullable", MakeColumnGuardedOfOrganizationsNotNullable.class) .add(1513, "Make default organization guarded", MakeDefaultOrganizationGuarded.class) + .add(1514, "Delete some entries in PROPERTIES", DeleteUselessProperties.class) ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DeleteUselessProperties.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DeleteUselessProperties.java new file mode 100644 index 00000000000..248e3b061b1 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DeleteUselessProperties.java @@ -0,0 +1,59 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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; + +/** + * Remove the following settings from the PROPERTIES table : + * - Settings about period 2 to 5 (sonar.timemachine.periodX + sonar.timemachine.periodX.VW + etc.) + * - sonar.technicalDebt.hoursInDay + * - sonar.authenticator.createUser + */ +public class DeleteUselessProperties extends DataChange { + + public DeleteUselessProperties(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate().rowPluralName("useless settings"); + massUpdate.select("SELECT id FROM properties WHERE " + + "prop_key LIKE ? OR prop_key LIKE ? OR prop_key LIKE ? OR prop_key LIKE ? OR " + + "prop_key IN (?, ?)") + .setString(1, "sonar.timemachine.period2%") + .setString(2, "sonar.timemachine.period3%") + .setString(3, "sonar.timemachine.period4%") + .setString(4, "sonar.timemachine.period5%") + .setString(5, "sonar.technicalDebt.hoursInDay") + .setString(6, "sonar.authenticator.createUser"); + massUpdate.update("DELETE FROM properties WHERE id=?"); + massUpdate.execute((row, update) -> { + long propertyId = row.getLong(1); + update.setLong(1, propertyId); + 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 51cd811a0b0..3d5240010f8 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, 14); + verifyMigrationCount(underTest, 15); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DeleteUselessPropertiesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DeleteUselessPropertiesTest.java new file mode 100644 index 00000000000..4c659e44fe7 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DeleteUselessPropertiesTest.java @@ -0,0 +1,171 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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.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.api.utils.System2; +import org.sonar.db.DbTester; + +import static java.lang.String.valueOf; +import static org.assertj.core.api.Assertions.assertThat; + +public class DeleteUselessPropertiesTest { + + 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 DbTester db = DbTester.createForSchema(System2.INSTANCE, DeleteUselessPropertiesTest.class, "properties.sql"); + + private DeleteUselessProperties underTest = new DeleteUselessProperties(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_hours_in_day_setting() throws SQLException { + insertProperty("sonar.technicalDebt.hoursInDay", null); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isZero(); + } + + @Test + public void migration_removes_create_user_setting() throws SQLException { + insertProperty("sonar.authenticator.createUser", null); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isZero(); + } + + @Test + public void migration_removes_period2_to_period5_settings() throws SQLException { + insertProperty("sonar.timemachine.period2", null); + insertProperty("sonar.timemachine.period3", null); + insertProperty("sonar.timemachine.period4", null); + insertProperty("sonar.timemachine.period5", null); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isZero(); + } + + @Test + public void migration_removes_period2_to_period5_settings_related_to_component() throws SQLException { + insertProperty("sonar.timemachine.period2", COMPONENT_ID_1); + insertProperty("sonar.timemachine.period2", COMPONENT_ID_1); + insertProperty("sonar.timemachine.period2", COMPONENT_ID_2); + insertProperty("sonar.timemachine.period3", COMPONENT_ID_1); + insertProperty("sonar.timemachine.period4", COMPONENT_ID_2); + insertProperty("sonar.timemachine.period5", COMPONENT_ID_2); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isZero(); + } + + @Test + public void migration_removes_period2_to_period5_settings_related_to_qualifiers() throws SQLException { + insertProperty("sonar.timemachine.period2.TRK", COMPONENT_ID_1); + insertProperty("sonar.timemachine.period2.TRK", null); + insertProperty("sonar.timemachine.period2.VW", COMPONENT_ID_1); + insertProperty("sonar.timemachine.period2.DEV", COMPONENT_ID_1); + insertProperty("sonar.timemachine.period2", COMPONENT_ID_1); + insertProperty("sonar.timemachine.period3", COMPONENT_ID_2); + insertProperty("sonar.timemachine.period3.TRK", COMPONENT_ID_2); + db.commit(); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isZero(); + } + + @Test + public void migration_ignores_not_relevant_settings() throws SQLException { + insertProperty("sonar.core.serverBaseURL", null); + insertProperty("sonar.dbcleaner.cleanDirectory", null); + insertProperty("sonar.dbcleaner.cleanDirectory", COMPONENT_ID_1); + // Only these settings should be removed + insertProperty("sonar.timemachine.period4", null); + insertProperty("sonar.timemachine.period5", null); + db.commit(); + + underTest.execute(); + + verifyPropertyKeys("sonar.core.serverBaseURL", "sonar.dbcleaner.cleanDirectory"); + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(3); + } + + @Test + public void migration_is_reentrant() throws SQLException { + insertProperty("sonar.core.serverBaseURL", null); + insertProperty("sonar.dbcleaner.cleanDirectory", null); + insertProperty("sonar.dbcleaner.cleanDirectory", COMPONENT_ID_1); + // Only these settings should be removed + insertProperty("sonar.timemachine.period4", null); + insertProperty("sonar.timemachine.period5", null); + db.commit(); + + underTest.execute(); + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(3); + + underTest.execute(); + assertThat(db.countRowsOfTable(TABLE_PROPERTIES)).isEqualTo(3); + } + + private void insertProperty(String key, @Nullable Integer componentId) { + Map<String, Object> 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<Map<String, Object>> rows = db.select("select prop_key from " + TABLE_PROPERTIES); + Set<Object> result = rows.stream().map(cols -> 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/v63/DeleteUselessPropertiesTest/properties.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/DeleteUselessPropertiesTest/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/v63/DeleteUselessPropertiesTest/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"); 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 0d00f5a933b..5051b8112d8 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 @@ -527,6 +527,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1510'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1511'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1512'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1513'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1514'); 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', true, '1418215735482', '1418215735482'); ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; |