From 230e7bd43b3ee84d0e2732b121a100740261161e Mon Sep 17 00:00:00 2001 From: =?utf8?q?L=C3=A9o=20Geoffroy?= <99647462+leo-geoffroy-sonarsource@users.noreply.github.com> Date: Wed, 4 Jan 2023 15:59:32 +0100 Subject: [PATCH] SONAR-17737 Support special index name for index drop in migrations --- .../main/java/org/sonar/db/DatabaseUtils.java | 39 +++++++------ .../java/org/sonar/db/DatabaseUtilsTest.java | 49 ++++++++++------- .../org/sonar/db/DatabaseUtilsTest/sql.sql | 11 +++- ...ponentsProjectUuidWithSpecialNameTest.java | 55 +++++++++++++++++++ .../schema.sql | 41 ++++++++++++++ 5 files changed, 158 insertions(+), 37 deletions(-) create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v97/DropIndexForComponentsProjectUuidWithSpecialNameTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v97/DropIndexForComponentsProjectUuidWithSpecialNameTest/schema.sql diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java b/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java index 8ca76a94209..c540743f96b 100644 --- a/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java +++ b/server/sonar-db-core/src/main/java/org/sonar/db/DatabaseUtils.java @@ -44,6 +44,7 @@ import java.util.function.Consumer; import java.util.function.Function; import java.util.function.IntFunction; import java.util.function.IntSupplier; +import java.util.function.Predicate; import java.util.regex.Pattern; import javax.annotation.CheckForNull; import javax.annotation.Nonnull; @@ -60,7 +61,7 @@ public class DatabaseUtils { public static final int PARTITION_SIZE_FOR_ORACLE = 1000; public static final String ORACLE_DRIVER_NAME = "Oracle JDBC driver"; public static final Pattern ORACLE_OBJECT_NAME_RULE = Pattern.compile("\"[^\"\\u0000]+\"|\\p{L}[\\p{L}\\p{N}_$#@]*"); - + public static final String INDEX_NAME_VARIATION = "^idx_\\d+_%s$"; /** * @see DatabaseMetaData#getTableTypes() */ @@ -160,8 +161,8 @@ public class DatabaseUtils { * The goal is to prevent issue with ORACLE when there's more than 1000 elements in a 'in ('X', 'Y', ...)' * and with MsSQL when there's more than 2000 parameters in a query * - * @param inputs the whole list of elements to be partitioned - * @param consumer the mapper method to be executed, for example {@code mapper(dbSession)::selectByUuids} + * @param inputs the whole list of elements to be partitioned + * @param consumer the mapper method to be executed, for example {@code mapper(dbSession)::selectByUuids} * @param partitionSizeManipulations the function that computes the number of usages of a partition, for example * {@code partitionSize -> partitionSize / 2} when the partition of elements * in used twice in the SQL request. @@ -329,35 +330,41 @@ public class DatabaseUtils { * Finds an index by searching by its lower case or upper case name. If an index is found, it's name is returned with the matching case. * This is useful when we need to drop an index that could exist with either lower case or upper case name. * See SONAR-13594 + * Related to ticket SONAR-17737, some index name can be changed to pattern idx_{number}_index_name. We also want to be able to identify and return them */ public static Optional findExistingIndex(Connection connection, String tableName, String indexName) { - Optional result = findIndex(connection, tableName.toLowerCase(Locale.US), indexName); - if (result.isPresent()) { - return result; - } - // in tests, tables have uppercase name - return findIndex(connection, tableName.toUpperCase(Locale.US), indexName); + Predicate indexSelector = idx -> indexName.equalsIgnoreCase(idx) || indexMatchesPattern(idx, format(INDEX_NAME_VARIATION, indexName)); + return findIndex(connection, tableName.toLowerCase(Locale.US), indexSelector) + .or(() -> findIndex(connection, tableName.toUpperCase(Locale.US), indexSelector)); + } + + private static boolean indexMatchesPattern(String idx, String pattern) { + return Pattern.compile(pattern, Pattern.CASE_INSENSITIVE).matcher(idx).matches(); } private static Optional findIndex(Connection connection, String tableName, String indexName) { + return findIndex(connection, tableName, indexName::equalsIgnoreCase); + } + + private static Optional findIndex(Connection connection, String tableName, Predicate indexMatcher) { String schema = getSchema(connection); if (StringUtils.isNotEmpty(schema)) { String driverName = getDriver(connection); // Fix for double quoted schema name in Oracle if (ORACLE_DRIVER_NAME.equals(driverName) && !ORACLE_OBJECT_NAME_RULE.matcher(schema).matches()) { - return getOracleIndex(connection, tableName, indexName, schema); + return getOracleIndex(connection, tableName, indexMatcher, schema); } } - return getIndex(connection, tableName, indexName, schema); + return getIndex(connection, tableName, indexMatcher, schema); } - private static Optional getIndex(Connection connection, String tableName, String indexName, @Nullable String schema) { + private static Optional getIndex(Connection connection, String tableName, Predicate indexMatcher, @Nullable String schema) { try (ResultSet rs = connection.getMetaData().getIndexInfo(connection.getCatalog(), schema, tableName, false, true)) { while (rs.next()) { String idx = rs.getString("INDEX_NAME"); - if (indexName.equalsIgnoreCase(idx)) { + if (indexMatcher.test(idx)) { return Optional.of(idx); } } @@ -367,12 +374,12 @@ public class DatabaseUtils { } } - private static Optional getOracleIndex(Connection connection, String tableName, String indexName, @Nonnull String schema) { + private static Optional getOracleIndex(Connection connection, String tableName, Predicate indexMatcher, @Nonnull String schema) { try (ResultSet rs = connection.getMetaData().getIndexInfo(connection.getCatalog(), null, tableName, false, true)) { while (rs.next()) { String idx = rs.getString("INDEX_NAME"); String tableSchema = rs.getString("TABLE_SCHEM"); - if (schema.equalsIgnoreCase(tableSchema) && indexName.equalsIgnoreCase(idx)) { + if (schema.equalsIgnoreCase(tableSchema) && indexMatcher.test(idx)) { return Optional.of(idx); } } @@ -451,7 +458,7 @@ public class DatabaseUtils { /** * @throws IllegalArgumentException if the collection is not null and has strictly more - * than {@link #PARTITION_SIZE_FOR_ORACLE} values. + * than {@link #PARTITION_SIZE_FOR_ORACLE} values. */ public static void checkThatNotTooManyConditions(@Nullable Collection values, String message) { if (values != null) { diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java b/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java index 0f06c4d59bc..03785dec338 100644 --- a/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java +++ b/server/sonar-db-core/src/test/java/org/sonar/db/DatabaseUtilsTest.java @@ -28,11 +28,11 @@ import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Collections; -import java.util.HashSet; import java.util.List; import java.util.Locale; import java.util.Objects; import java.util.Optional; +import java.util.Set; import javax.annotation.Nullable; import org.junit.Rule; import org.junit.Test; @@ -43,7 +43,6 @@ import org.sonar.core.util.stream.MoreCollectors; import org.sonar.db.dialect.Oracle; import static com.google.common.collect.Lists.newArrayList; -import static java.util.Arrays.asList; import static org.assertj.core.api.Assertions.assertThat; import static org.assertj.core.api.Assertions.assertThatThrownBy; import static org.assertj.core.api.Assertions.fail; @@ -70,7 +69,7 @@ public class DatabaseUtilsTest { @Test public void find_index_with_lower_case() throws SQLException { - String tableName = "schema_migrations"; + String tableName = "SCHEMA_MIGRATIONS"; String indexName = "lower_case_name"; try (Connection connection = dbTester.openConnection()) { assertThat(DatabaseUtils.findExistingIndex(connection, tableName, indexName)).contains(indexName); @@ -80,7 +79,7 @@ public class DatabaseUtilsTest { @Test public void find_index_with_upper_case() throws SQLException { - String tableName = "schema_migrations"; + String tableName = "SCHEMA_MIGRATIONS"; String indexName = "UPPER_CASE_NAME"; try (Connection connection = dbTester.openConnection()) { assertThat(DatabaseUtils.findExistingIndex(connection, tableName, indexName)).contains(indexName); @@ -89,22 +88,35 @@ public class DatabaseUtilsTest { } } + @Test + public void find_index_with_special_name() throws SQLException { + String tableName = "SCHEMA_MIGRATIONS"; + String indexName = "INDEX_NAME"; + try (Connection connection = dbTester.openConnection()) { + assertThat(DatabaseUtils.findExistingIndex(connection, tableName, indexName)).contains("idx_1234_index_name"); + assertThat(DatabaseUtils.findExistingIndex(connection, tableName.toLowerCase(Locale.US), indexName)).contains("idx_1234_index_name"); + assertThat(DatabaseUtils.findExistingIndex(connection, tableName.toLowerCase(Locale.US), indexName.toLowerCase(Locale.US))).contains("idx_1234_index_name"); + assertThat(DatabaseUtils.findExistingIndex(connection, tableName, "index")).isEmpty(); + assertThat(DatabaseUtils.findExistingIndex(connection, tableName, "index_name_2")).isEmpty(); + assertThat(DatabaseUtils.findExistingIndex(connection, tableName, "index_name_")).isEmpty(); + } + } + @Test public void find_column_with_lower_case_table_name_and_upper_case_column_name() throws SQLException { String tableName = "tablea"; String columnName = "COLUMNA"; try (Connection connection = dbTester.openConnection()) { assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName)).isTrue(); - assertThat(DatabaseUtils.tableColumnExists(connection, tableName.toLowerCase(Locale.US), columnName)).isTrue(); } } + @Test public void find_column_with_upper_case_table_name_and_upper_case_column_name() throws SQLException { String tableName = "TABLEA"; String columnName = "COLUMNA"; try (Connection connection = dbTester.openConnection()) { assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName)).isTrue(); - assertThat(DatabaseUtils.tableColumnExists(connection, tableName.toLowerCase(Locale.US), columnName)).isTrue(); } } @@ -114,7 +126,6 @@ public class DatabaseUtilsTest { String columnName = "columna"; try (Connection connection = dbTester.openConnection()) { assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName)).isTrue(); - assertThat(DatabaseUtils.tableColumnExists(connection, tableName.toLowerCase(Locale.US), columnName)).isTrue(); } } @@ -124,8 +135,6 @@ public class DatabaseUtilsTest { String columnName = "columna"; try (Connection connection = dbTester.openConnection()) { assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName)).isTrue(); - assertThat(DatabaseUtils.tableColumnExists(connection, tableName, columnName.toLowerCase(Locale.US))).isTrue(); - assertThat(DatabaseUtils.tableColumnExists(connection, tableName.toLowerCase(Locale.US), columnName.toLowerCase(Locale.US))).isTrue(); } } @@ -199,34 +208,34 @@ public class DatabaseUtilsTest { @Test public void toUniqueAndSortedList_throws_NPE_if_arg_contains_a_null() { - assertThatThrownBy(() -> toUniqueAndSortedList(asList("A", null, "C"))) + assertThatThrownBy(() -> toUniqueAndSortedList(List.of("A", null, "C"))) .isInstanceOf(NullPointerException.class); } @Test public void toUniqueAndSortedList_throws_NPE_if_arg_is_a_set_containing_a_null() { - assertThatThrownBy(() -> toUniqueAndSortedList(new HashSet<>(asList("A", null, "C")))) + assertThatThrownBy(() -> toUniqueAndSortedList(Set.of("A", null, "C"))) .isInstanceOf(NullPointerException.class); } @Test public void toUniqueAndSortedList_enforces_natural_order() { - assertThat(toUniqueAndSortedList(asList("A", "B", "C"))).containsExactly("A", "B", "C"); - assertThat(toUniqueAndSortedList(asList("B", "A", "C"))).containsExactly("A", "B", "C"); - assertThat(toUniqueAndSortedList(asList("B", "C", "A"))).containsExactly("A", "B", "C"); + assertThat(toUniqueAndSortedList(List.of("A", "B", "C"))).containsExactly("A", "B", "C"); + assertThat(toUniqueAndSortedList(List.of("B", "A", "C"))).containsExactly("A", "B", "C"); + assertThat(toUniqueAndSortedList(List.of("B", "C", "A"))).containsExactly("A", "B", "C"); } @Test public void toUniqueAndSortedList_removes_duplicates() { - assertThat(toUniqueAndSortedList(asList("A", "A", "A"))).containsExactly("A"); - assertThat(toUniqueAndSortedList(asList("A", "C", "A"))).containsExactly("A", "C"); - assertThat(toUniqueAndSortedList(asList("C", "C", "B", "B", "A", "N", "C", "A"))).containsExactly("A", "B", "C", "N"); + assertThat(toUniqueAndSortedList(List.of("A", "A", "A"))).containsExactly("A"); + assertThat(toUniqueAndSortedList(List.of("A", "C", "A"))).containsExactly("A", "C"); + assertThat(toUniqueAndSortedList(List.of("C", "C", "B", "B", "A", "N", "C", "A"))).containsExactly("A", "B", "C", "N"); } @Test public void toUniqueAndSortedList_removes_duplicates_and_apply_natural_order_of_any_Comparable() { assertThat( - toUniqueAndSortedList(asList(myComparable(2), myComparable(5), myComparable(2), myComparable(4), myComparable(-1), myComparable(10)))) + toUniqueAndSortedList(List.of(myComparable(2), myComparable(5), myComparable(2), myComparable(4), myComparable(-1), myComparable(10)))) .containsExactly( myComparable(-1), myComparable(2), myComparable(4), myComparable(5), myComparable(10)); } @@ -434,7 +443,7 @@ public class DatabaseUtilsTest { public void executeLargeInputs_uses_specified_partition_size_manipulations() { List> partitions = new ArrayList<>(); List outputs = DatabaseUtils.executeLargeInputs( - asList(1, 2, 3), + List.of(1, 2, 3), partition -> { partitions.add(partition); return partition; @@ -442,7 +451,7 @@ public class DatabaseUtilsTest { i -> i / 500); assertThat(outputs).containsExactly(1, 2, 3); - assertThat(partitions).containsExactly(asList(1, 2), asList(3)); + assertThat(partitions).containsExactly(List.of(1, 2), List.of(3)); } @Test diff --git a/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql b/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql index e0fbf26800c..07cd0a57d26 100644 --- a/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql +++ b/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql @@ -3,9 +3,18 @@ CREATE TABLE "schema_migrations" ( ); CREATE TABLE "TABLEA" ( - "COLUMNA" VARCHAR(256) NOT NULL + "COLUMNA" VARCHAR(256) NOT NULL, + "COLUMNB" VARCHAR(256) NOT NULL ); CREATE INDEX UPPER_CASE_NAME ON schema_migrations (version); CREATE INDEX lower_case_name ON schema_migrations (version); + +--For test on special index name +CREATE INDEX idx_1234_index_name ON schema_migrations (version); +CREATE INDEX idx_index_name_2 ON schema_migrations (version); +CREATE INDEX idx__index_name_2 ON schema_migrations (version); + + +CREATE INDEX test ON TABLEA(COLUMNA, COLUMNB); diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v97/DropIndexForComponentsProjectUuidWithSpecialNameTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v97/DropIndexForComponentsProjectUuidWithSpecialNameTest.java new file mode 100644 index 00000000000..2db08eefe1a --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v97/DropIndexForComponentsProjectUuidWithSpecialNameTest.java @@ -0,0 +1,55 @@ +/* + * 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.v97; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.sonar.db.CoreDbTester.createForSchema; + +public class DropIndexForComponentsProjectUuidWithSpecialNameTest { + + private static final String TABLE = "components"; + private static final String INDEX = "idx_1234_projects_project_uuid"; + + @Rule + public final CoreDbTester db = createForSchema(DropIndexForComponentsProjectUuidWithSpecialNameTest.class, "schema.sql"); + + private final DropIndexForComponentsProjectUuid dropIndexForRuleDescSection = new DropIndexForComponentsProjectUuid(db.database()); + + @Test + public void migration_should_drop_unique_index() throws SQLException { + db.assertIndex(TABLE, INDEX, "project_uuid"); + + dropIndexForRuleDescSection.execute(); + + db.assertIndexDoesNotExist(TABLE, INDEX); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + dropIndexForRuleDescSection.execute(); + dropIndexForRuleDescSection.execute(); + + db.assertIndexDoesNotExist(TABLE, INDEX); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v97/DropIndexForComponentsProjectUuidWithSpecialNameTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v97/DropIndexForComponentsProjectUuidWithSpecialNameTest/schema.sql new file mode 100644 index 00000000000..aece92157fe --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v97/DropIndexForComponentsProjectUuidWithSpecialNameTest/schema.sql @@ -0,0 +1,41 @@ +CREATE TABLE "COMPONENTS"( + "UUID" CHARACTER VARYING(50) NOT NULL, + "KEE" CHARACTER VARYING(1000), + "DEPRECATED_KEE" CHARACTER VARYING(400), + "NAME" CHARACTER VARYING(2000), + "LONG_NAME" CHARACTER VARYING(2000), + "DESCRIPTION" CHARACTER VARYING(2000), + "ENABLED" BOOLEAN DEFAULT TRUE NOT NULL, + "SCOPE" CHARACTER VARYING(3), + "QUALIFIER" CHARACTER VARYING(10), + "PRIVATE" BOOLEAN NOT NULL, + "ROOT_UUID" CHARACTER VARYING(50) NOT NULL, + "LANGUAGE" CHARACTER VARYING(20), + "COPY_COMPONENT_UUID" CHARACTER VARYING(50), + "PATH" CHARACTER VARYING(2000), + "UUID_PATH" CHARACTER VARYING(1500) NOT NULL, + "PROJECT_UUID" CHARACTER VARYING(50) NOT NULL, + "MODULE_UUID" CHARACTER VARYING(50), + "MODULE_UUID_PATH" CHARACTER VARYING(1500), + "MAIN_BRANCH_PROJECT_UUID" CHARACTER VARYING(50), + "B_CHANGED" BOOLEAN, + "B_NAME" CHARACTER VARYING(500), + "B_LONG_NAME" CHARACTER VARYING(500), + "B_DESCRIPTION" CHARACTER VARYING(2000), + "B_ENABLED" BOOLEAN, + "B_QUALIFIER" CHARACTER VARYING(10), + "B_LANGUAGE" CHARACTER VARYING(20), + "B_COPY_COMPONENT_UUID" CHARACTER VARYING(50), + "B_PATH" CHARACTER VARYING(2000), + "B_UUID_PATH" CHARACTER VARYING(1500), + "B_MODULE_UUID" CHARACTER VARYING(50), + "B_MODULE_UUID_PATH" CHARACTER VARYING(1500), + "CREATED_AT" TIMESTAMP +); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "COMPONENTS"("KEE" NULLS FIRST); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "COMPONENTS"("MODULE_UUID" NULLS FIRST); +CREATE INDEX "IDX_1234_PROJECTS_PROJECT_UUID" ON "COMPONENTS"("PROJECT_UUID" NULLS FIRST); +CREATE INDEX "PROJECTS_QUALIFIER" ON "COMPONENTS"("QUALIFIER" NULLS FIRST); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "COMPONENTS"("ROOT_UUID" NULLS FIRST); +CREATE INDEX "PROJECTS_UUID" ON "COMPONENTS"("UUID" NULLS FIRST); +CREATE INDEX "IDX_MAIN_BRANCH_PRJ_UUID" ON "COMPONENTS"("MAIN_BRANCH_PROJECT_UUID" NULLS FIRST); -- 2.39.5