From 104274adf1f1d70808fec00c91b338260cbf5e23 Mon Sep 17 00:00:00 2001 From: =?utf8?q?L=C3=A9o=20Geoffroy?= Date: Fri, 20 Oct 2023 14:43:27 +0200 Subject: [PATCH] SONAR-19378 Add unicity constraint on properties table --- .../sonar/db/property/PropertiesDaoIT.java | 42 ++++++- server/sonar-db-dao/src/schema/schema-sq.ddl | 1 + .../sql/DeduplicateTableBuilder.java | 65 +++++++++++ .../CreateIndexForRuleImpactChangesTable.java | 4 +- ...IndexForGithubPermissionsMappingTable.java | 4 +- .../CreateUniqueIndexForPropertiesTable.java | 53 +++++++++ .../migration/version/v103/DbVersion103.java | 4 +- .../v103/DeduplicatePropertiesTable.java | 50 ++++++++ .../sql/DeduplicateTableBuilderTest.java | 60 ++++++++++ ...eateUniqueIndexForPropertiesTableTest.java | 56 +++++++++ .../v103/DeduplicatePropertiesTableTest.java | 110 ++++++++++++++++++ .../schema.sql | 14 +++ .../DeduplicatePropertiesTableTest/schema.sql | 14 +++ 13 files changed, 467 insertions(+), 10 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DeduplicateTableBuilder.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTable.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DeduplicateTableBuilderTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTableTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTableTest/schema.sql diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/property/PropertiesDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/property/PropertiesDaoIT.java index 016ed6843a1..063f7a37bb0 100644 --- a/server/sonar-db-dao/src/it/java/org/sonar/db/property/PropertiesDaoIT.java +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/property/PropertiesDaoIT.java @@ -22,7 +22,9 @@ package org.sonar.db.property; import com.tngtech.java.junit.dataprovider.DataProvider; import com.tngtech.java.junit.dataprovider.DataProviderRunner; import com.tngtech.java.junit.dataprovider.UseDataProvider; +import java.sql.SQLException; import java.util.Arrays; +import java.util.Date; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; @@ -67,6 +69,7 @@ import static org.sonar.db.property.PropertyTesting.newUserPropertyDto; @RunWith(DataProviderRunner.class) public class PropertiesDaoIT { + private static final String VALUE_SIZE_4000 = String.format("%1$4000.4000s", "*"); private static final String VALUE_SIZE_4001 = VALUE_SIZE_4000 + "P"; private static final long INITIAL_DATE = 1_444_000L; @@ -514,7 +517,7 @@ public class PropertiesDaoIT { @DataProvider public static Object[][] allValuesForSelect() { - return new Object[][] { + return new Object[][]{ {null, ""}, {"", ""}, {"some value", "some value"}, @@ -820,7 +823,7 @@ public class PropertiesDaoIT { @DataProvider public static Object[][] valueUpdatesDataProvider() { - return new Object[][] { + return new Object[][]{ {null, null}, {null, ""}, {null, "some value"}, @@ -996,6 +999,35 @@ public class PropertiesDaoIT { .isInstanceOf(IllegalArgumentException.class); } + @Test + public void insert_shouldFail_whenPropertyAlreadyExists() { + PropertiesMapper mapper = db.getSession().getSqlSession().getMapper(PropertiesMapper.class); + + mapper.insertAsText("uuid1", "key", null, null, "value", new Date().getTime()); + assertThatThrownBy(() -> { + mapper.insertAsText("uuid2", "key", null, null, "value", new Date().getTime()); + }).hasCauseInstanceOf(SQLException.class); + } + + @Test + public void insert_shouldFail_whenPropertyAlreadyExistsOnKeyAndUser() { + PropertiesMapper mapper = db.getSession().getSqlSession().getMapper(PropertiesMapper.class); + + mapper.insertAsText("uuid3", "key", "user", null, "value", new Date().getTime()); + assertThatThrownBy(() -> mapper.insertAsText("uuid4", "key", "user", null, "value", new Date().getTime())) + .hasCauseInstanceOf(SQLException.class); + } + + @Test + public void insert_shouldFail_whenPropertyAlreadyExistsOnKeyAndUserAndEntity() { + PropertiesMapper mapper = db.getSession().getSqlSession().getMapper(PropertiesMapper.class); + + mapper.insertAsText("uuid5", "key", "user", "entity", "value", new Date().getTime()); + assertThatThrownBy(() -> mapper.insertAsText("uuid6", "key", "user", "entity", "value", new Date().getTime())) + .hasCauseInstanceOf(SQLException.class); + + } + @CheckForNull private PropertyDto findByKey(List properties, String key) { for (PropertyDto property : properties) { @@ -1030,9 +1062,9 @@ public class PropertiesDaoIT { } return (String) db.selectFirst(session, "select uuid as \"uuid\" from properties" + - " where prop_key='" + key + "'" + - " and user_uuid" + (userUuid == null ? " is null" : "='" + userUuid + "'") + - " and entity_uuid" + (entityUuid == null ? " is null" : "='" + entityUuid + "'")).get("uuid"); + " where prop_key='" + key + "'" + + " and user_uuid" + (userUuid == null ? " is null" : "='" + userUuid + "'") + + " and entity_uuid" + (entityUuid == null ? " is null" : "='" + entityUuid + "'")).get("uuid"); } private ProjectDto insertPrivateProject(String projectKey) { diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index f60e01c4f7d..87456cc0ecf 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -774,6 +774,7 @@ CREATE TABLE "PROPERTIES"( ); ALTER TABLE "PROPERTIES" ADD CONSTRAINT "PK_PROPERTIES" PRIMARY KEY("UUID"); CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES"("PROP_KEY" NULLS FIRST); +CREATE UNIQUE NULLS NOT DISTINCT INDEX "UNIQ_PROPERTIES" ON "PROPERTIES"("PROP_KEY" NULLS FIRST, "ENTITY_UUID" NULLS FIRST, "USER_UUID" NULLS FIRST); CREATE TABLE "PUSH_EVENTS"( "UUID" CHARACTER VARYING(40) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DeduplicateTableBuilder.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DeduplicateTableBuilder.java new file mode 100644 index 00000000000..198fb98f95e --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/sql/DeduplicateTableBuilder.java @@ -0,0 +1,65 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.sql; + +import java.util.ArrayList; +import java.util.List; +import org.sonar.server.platform.db.migration.def.Validations; + +import static com.google.common.base.Preconditions.checkArgument; +import static java.util.Collections.singletonList; +import static org.sonar.server.platform.db.migration.def.Validations.validateColumnName; +import static org.sonar.server.platform.db.migration.def.Validations.validateTableName; + +public class DeduplicateTableBuilder { + + private final String tableName; + private final List referenceColumns = new ArrayList<>(); + private String identityColumn; + + public DeduplicateTableBuilder(String tableName) { + this.tableName = tableName; + } + + public DeduplicateTableBuilder setIdentityColumn(String identityColumn) { + this.identityColumn = identityColumn; + return this; + } + + public DeduplicateTableBuilder addReferenceColumn(String referenceColumn) { + this.referenceColumns.add(referenceColumn); + return this; + } + + public List build() { + validateTableName(tableName); + validateColumnName(identityColumn); + checkArgument(!referenceColumns.isEmpty(), "At least one reference column must be specified"); + referenceColumns.forEach(Validations::validateColumnName); + return singletonList(createSqlStatement()); + } + + private String createSqlStatement() { + StringBuilder sql = new StringBuilder().append("delete from ").append(tableName).append(" "); + sql.append("where ").append(identityColumn).append(" not in (select min(").append(identityColumn).append(") from ").append(tableName).append(" "); + sql.append("group by ").append(String.join(", ", referenceColumns)).append(")"); + return sql.toString(); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateIndexForRuleImpactChangesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateIndexForRuleImpactChangesTable.java index 75a3e56a50c..7c87e4422cc 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateIndexForRuleImpactChangesTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateIndexForRuleImpactChangesTable.java @@ -42,9 +42,9 @@ public class CreateIndexForRuleImpactChangesTable extends DdlChange { } } - private static void createUniqueIndex(Context context, Connection connection) { + private void createUniqueIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(TABLE_NAME) .setName(INDEX_NAME) .addColumn("rule_change_uuid") diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForGithubPermissionsMappingTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForGithubPermissionsMappingTable.java index 7be92b7c9b1..08927e73bcd 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForGithubPermissionsMappingTable.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForGithubPermissionsMappingTable.java @@ -47,9 +47,9 @@ public class CreateUniqueIndexForGithubPermissionsMappingTable extends DdlChange } } - private static void createUniqueIndex(Context context, Connection connection) { + private void createUniqueIndex(Context context, Connection connection) { if (!DatabaseUtils.indexExistsIgnoreCase(GITHUB_PERMISSIONS_MAPPING_TABLE_NAME, INDEX_NAME, connection)) { - context.execute(new CreateIndexBuilder() + context.execute(new CreateIndexBuilder(getDialect()) .setTable(GITHUB_PERMISSIONS_MAPPING_TABLE_NAME) .setName(INDEX_NAME) .addColumn(GITHUB_ROLE_COLUMN) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTable.java new file mode 100644 index 00000000000..f85738cde85 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTable.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v103; + +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.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class CreateUniqueIndexForPropertiesTable extends DdlChange { + + public static final String INDEX_NAME = "uniq_properties"; + public static final String PROPERTIES_TABLE_NAME = "properties"; + + public CreateUniqueIndexForPropertiesTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection connection = getDatabase().getDataSource().getConnection()) { + if (!DatabaseUtils.indexExistsIgnoreCase(PROPERTIES_TABLE_NAME, INDEX_NAME, connection)) { + context.execute(new CreateIndexBuilder(getDialect()) + .setTable(PROPERTIES_TABLE_NAME) + .setName(INDEX_NAME) + .addColumn("prop_key", false) + .addColumn("entity_uuid", true) + .addColumn("user_uuid", true) + .setUnique(true) + .build()); + } + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/DbVersion103.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/DbVersion103.java index 80d692844c8..e0516c66a55 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/DbVersion103.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/DbVersion103.java @@ -53,6 +53,8 @@ public class DbVersion103 implements DbVersion { .add(10_3_009, "Create table 'rule_changes'", CreateRuleChangesTable.class) .add(10_3_010, "Create table 'rule_impact_changes", CreateRuleImpactChangesTable.class) .add(10_3_011, "Create index for 'rule_impact_changes", CreateIndexForRuleImpactChangesTable.class) - .add(10_3_012, "Add 'sq_version' column in 'qprofile_changes' table", AddSqVersionColumnInQprofileChangesTable.class); + .add(10_3_012, "Add 'sq_version' column in 'qprofile_changes' table", AddSqVersionColumnInQprofileChangesTable.class) + .add(10_3_013, "Deduplicate potential records in 'properties' table", DeduplicatePropertiesTable.class) + .add(10_3_014, "Create unique index to 'properties' table", CreateUniqueIndexForPropertiesTable.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTable.java new file mode 100644 index 00000000000..e435479ef1e --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTable.java @@ -0,0 +1,50 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v103; + +import java.sql.SQLException; +import java.util.List; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.DeduplicateTableBuilder; +import org.sonar.server.platform.db.migration.step.DataChange; + +public class DeduplicatePropertiesTable extends DataChange { + private static final String PROPERTIES_TABLE = "properties"; + + public DeduplicatePropertiesTable(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + List queries = new DeduplicateTableBuilder(PROPERTIES_TABLE) + .addReferenceColumn("prop_key") + .addReferenceColumn("entity_uuid") + .addReferenceColumn("user_uuid") + .setIdentityColumn("uuid") + .build(); + + for (String q : queries) { + context.prepareUpsert(q) + .execute() + .commit(); + } + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DeduplicateTableBuilderTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DeduplicateTableBuilderTest.java new file mode 100644 index 00000000000..1aa146124bc --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/sql/DeduplicateTableBuilderTest.java @@ -0,0 +1,60 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.sql; + +import java.util.List; +import junit.framework.TestCase; +import org.assertj.core.api.Assertions; +import org.junit.Test; + +public class DeduplicateTableBuilderTest { + + @Test + public void build_shouldReturnExpectedSql(){ + List queries = new DeduplicateTableBuilder("example_table") + .addReferenceColumn("reference_column1") + .addReferenceColumn("reference_column2") + .setIdentityColumn("identity_column") + .build(); + + Assertions.assertThat(queries) + .containsExactly("delete from example_table where identity_column not in (select min(identity_column) from example_table group by reference_column1, reference_column2)"); + } + + @Test + public void build_shouldThrowException_whenIdentityColumnUndefined(){ + DeduplicateTableBuilder builder = new DeduplicateTableBuilder("example_table") + .addReferenceColumn("reference_column1") + .addReferenceColumn("reference_column2"); + + Assertions.assertThatThrownBy(builder::build).isInstanceOf(NullPointerException.class) + .hasMessage("Column name cannot be null"); + } + + @Test + public void build_shouldThrowException_whenReferenceColumnUndefined(){ + DeduplicateTableBuilder builder = new DeduplicateTableBuilder("example_table") + .setIdentityColumn("identity_column"); + + Assertions.assertThatThrownBy(builder::build).isInstanceOf(IllegalArgumentException.class) + .hasMessage("At least one reference column must be specified"); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTableTest.java new file mode 100644 index 00000000000..35c5eb42c6b --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTableTest.java @@ -0,0 +1,56 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v103; + +import java.sql.SQLException; +import junit.framework.TestCase; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.sonar.server.platform.db.migration.version.v103.CreateUniqueIndexForPropertiesTable.INDEX_NAME; +import static org.sonar.server.platform.db.migration.version.v103.CreateUniqueIndexForPropertiesTable.PROPERTIES_TABLE_NAME; + + +public class CreateUniqueIndexForPropertiesTableTest { + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateUniqueIndexForPropertiesTableTest.class, "schema.sql"); + + private final CreateUniqueIndexForPropertiesTable createIndex = new CreateUniqueIndexForPropertiesTable(db.database()); + + @Test + public void migration_should_create_index() throws SQLException { + db.assertIndexDoesNotExist(PROPERTIES_TABLE_NAME, INDEX_NAME); + + createIndex.execute(); + + db.assertUniqueIndex(PROPERTIES_TABLE_NAME, INDEX_NAME, "prop_key", "entity_uuid", "user_uuid"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + createIndex.execute(); + createIndex.execute(); + + db.assertUniqueIndex(PROPERTIES_TABLE_NAME, INDEX_NAME, "prop_key", "entity_uuid", "user_uuid"); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTableTest.java new file mode 100644 index 00000000000..ecae8b64c02 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTableTest.java @@ -0,0 +1,110 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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.v103; + +import java.sql.SQLException; +import java.util.Date; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.core.util.Uuids; +import org.sonar.db.CoreDbTester; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.tuple; + +public class DeduplicatePropertiesTableTest { + public static final String KEY = "key"; + public static final String ENTITY = "entity"; + public static final String USER = "user"; + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(DeduplicatePropertiesTableTest.class, "schema.sql"); + + private final DeduplicatePropertiesTable underTest = new DeduplicatePropertiesTable(db.database()); + + + public void createBaseProperties() { + createProperty(KEY, null, null); + createProperty(KEY, USER, null); + createProperty(KEY, USER, ENTITY); + } + + private void createProperty(String key, @Nullable String user, @Nullable String entity) { + db.executeInsert("PROPERTIES", + "UUID", Uuids.createFast(), + "PROP_KEY", key, + "TEXT_VALUE", "value", + "ENTITY_UUID", entity, + "USER_UUID", user, + "IS_EMPTY", false, + "CREATED_AT", new Date().getTime()); + } + + @Test + public void execute_shouldDeduplicateRows_WhenOnlyKeyIsSpecified() throws SQLException { + createBaseProperties(); + createProperty(KEY, null, null); + createProperty(KEY, null, null); + underTest.execute(); + assertThat(db.select("select * from properties")) + .hasSize(3) + .extracting(str -> str.get("PROP_KEY"), str -> str.get("USER_UUID"), str -> str.get("ENTITY_UUID")) + .containsExactlyInAnyOrder(tuple(KEY, null, null), tuple(KEY, USER, null), tuple(KEY, USER, ENTITY)); + } + + @Test + public void execute_shouldDeduplicateRows_WhenOnlyKeyAndUserAreSpecified() throws SQLException { + createBaseProperties(); + createProperty(KEY, USER, null); + createProperty(KEY, USER, null); + underTest.execute(); + assertThat(db.select("select * from properties")) + .hasSize(3) + .extracting(str -> str.get("PROP_KEY"), str -> str.get("USER_UUID"), str -> str.get("ENTITY_UUID")) + .containsExactlyInAnyOrder(tuple(KEY, null, null), tuple(KEY, USER, null), tuple(KEY, USER, ENTITY)); + } + + @Test + public void execute_shouldDeduplicateRows_WhenKeyUserAndEntityAreSpecified() throws SQLException { + createBaseProperties(); + createProperty(KEY, USER, ENTITY); + createProperty(KEY, USER, ENTITY); + underTest.execute(); + assertThat(db.select("select * from properties")) + .hasSize(3) + .extracting(str -> str.get("PROP_KEY"), str -> str.get("USER_UUID"), str -> str.get("ENTITY_UUID")) + .containsExactlyInAnyOrder(tuple(KEY, null, null), tuple(KEY, USER, null), tuple(KEY, USER, ENTITY)); + } + + @Test + public void execute_shouldBeReentrant() throws SQLException { + createBaseProperties(); + createProperty(KEY, USER, ENTITY); + + underTest.execute(); + underTest.execute(); + + assertThat(db.select("select * from properties")) + .hasSize(3) + .extracting(str -> str.get("PROP_KEY"), str -> str.get("USER_UUID"), str -> str.get("ENTITY_UUID")) + .containsExactlyInAnyOrder(tuple(KEY, null, null), tuple(KEY, USER, null), tuple(KEY, USER, ENTITY)); + } + +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTableTest/schema.sql new file mode 100644 index 00000000000..92be388c2ed --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v103/CreateUniqueIndexForPropertiesTableTest/schema.sql @@ -0,0 +1,14 @@ +CREATE TABLE "PROPERTIES" +( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PROP_KEY" CHARACTER VARYING(512) NOT NULL, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" CHARACTER VARYING(4000), + "CLOB_VALUE" CHARACTER LARGE OBJECT, + "CREATED_AT" BIGINT NOT NULL, + "ENTITY_UUID" CHARACTER VARYING(40), + "USER_UUID" CHARACTER VARYING(255) +); +ALTER TABLE "PROPERTIES" + ADD CONSTRAINT "PK_PROPERTIES" PRIMARY KEY ("UUID"); +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY" NULLS FIRST); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTableTest/schema.sql new file mode 100644 index 00000000000..92be388c2ed --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v103/DeduplicatePropertiesTableTest/schema.sql @@ -0,0 +1,14 @@ +CREATE TABLE "PROPERTIES" +( + "UUID" CHARACTER VARYING(40) NOT NULL, + "PROP_KEY" CHARACTER VARYING(512) NOT NULL, + "IS_EMPTY" BOOLEAN NOT NULL, + "TEXT_VALUE" CHARACTER VARYING(4000), + "CLOB_VALUE" CHARACTER LARGE OBJECT, + "CREATED_AT" BIGINT NOT NULL, + "ENTITY_UUID" CHARACTER VARYING(40), + "USER_UUID" CHARACTER VARYING(255) +); +ALTER TABLE "PROPERTIES" + ADD CONSTRAINT "PK_PROPERTIES" PRIMARY KEY ("UUID"); +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY" NULLS FIRST); -- 2.39.5