SONAR-13594 Upgrade to 8.4 fails on MS SQL Server

6 files changed, 92 insertions, 39 deletions

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 fff31a94824..ff53e51f5d8 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
@@ -39,6 +39,7 @@ import java.util.Date;
 import java.util.HashSet;
 import java.util.List;
 import java.util.Locale;
+import java.util.Optional;
 import java.util.Set;
 import java.util.function.Consumer;
 import java.util.function.IntFunction;
@@ -282,10 +283,10 @@ public class DatabaseUtils {
   }
 
   /**
-    * @param table case-insensitive name of table
-    * @return true if a table exists with this name, otherwise false
-    * @throws SQLException
-    */
+   * @param table case-insensitive name of table
+   * @return true if a table exists with this name, otherwise false
+   * @throws SQLException
+   */
   public static boolean tableExists(String table, Connection connection) {
     return doTableExists(table, connection) ||
       doTableExists(table.toLowerCase(Locale.ENGLISH), connection) ||
@@ -310,25 +311,43 @@ public class DatabaseUtils {
     }
   }
 
-  public static boolean indexExists(String table, String index, Connection connection) {
-    return doIndexExists(table, index, connection) ||
-      doIndexExists(table.toLowerCase(Locale.ENGLISH), index, connection) ||
-      doIndexExists(table.toUpperCase(Locale.ENGLISH), index, connection);
+  public static boolean indexExistsIgnoreCase(String table, String index, Connection connection) {
+    return doIndexExistsIgnoreIndexCase(table, index, connection) ||
+      doIndexExistsIgnoreIndexCase(table.toLowerCase(Locale.ENGLISH), index, connection) ||
+      doIndexExistsIgnoreIndexCase(table.toUpperCase(Locale.ENGLISH), index, connection);
+  }
+
+  private static boolean doIndexExistsIgnoreIndexCase(String table, String index, Connection connection) {
+    return findIndex(connection, table, index).isPresent();
   }
 
-  private static boolean doIndexExists(String table, String index, Connection connection) {
+  /**
+   * 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
+   */
+  public static Optional<String> findExistingIndex(Connection connection, String tableName, String indexName) {
+    Optional<String> 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);
+  }
+
+  private static Optional<String> findIndex(Connection connection, String tableName, String indexName) {
     String schema = getSchema(connection);
 
-    try (ResultSet rs = connection.getMetaData().getIndexInfo(connection.getCatalog(), schema, table, false, true)) {
+    try (ResultSet rs = connection.getMetaData().getIndexInfo(connection.getCatalog(), schema, tableName, false, true)) {
       while (rs.next()) {
-        String indexName = rs.getString("INDEX_NAME");
-        if (index.equalsIgnoreCase(indexName)) {
-          return true;
+        String idx = rs.getString("INDEX_NAME");
+        if (indexName.equalsIgnoreCase(idx)) {
+          return Optional.of(idx);
         }
       }
-      return false;
+      return Optional.empty();
     } catch (SQLException e) {
-      throw wrapSqlException(e, "Can not check that table %s exists", table);
+      throw wrapSqlException(e, "Can not check that table %s exists", tableName);
     }
   }
 
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 383133974c5..7342a0a47c5 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
@@ -30,6 +30,7 @@ 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 javax.annotation.Nullable;
 import org.junit.Rule;
@@ -57,19 +58,41 @@ import static org.sonar.db.DatabaseUtils.toUniqueAndSortedList;
 public class DatabaseUtilsTest {
 
   @Rule
-  public CoreDbTester dbTester = CoreDbTester.createForSchema(DatabaseUtilsTest.class, "just_one_table.sql");
+  public CoreDbTester dbTester = CoreDbTester.createForSchema(DatabaseUtilsTest.class, "sql.sql", false);
   @Rule
   public ExpectedException expectedException = ExpectedException.none();
   @Rule
   public LogTester logTester = new LogTester();
 
   @Test
+  public void find_index_with_lower_case() throws SQLException {
+    String tableName = "schema_migrations";
+    String indexName = "lower_case_name";
+    try (Connection connection = dbTester.openConnection()) {
+      assertThat(DatabaseUtils.findExistingIndex(connection, tableName, indexName)).contains(indexName);
+      assertThat(DatabaseUtils.findExistingIndex(connection, tableName.toLowerCase(Locale.US), indexName)).contains(indexName);
+    }
+  }
+
+  @Test
+  public void find_index_with_upper_case() throws SQLException {
+    String tableName = "schema_migrations";
+    String indexName = "UPPER_CASE_NAME";
+    try (Connection connection = dbTester.openConnection()) {
+      assertThat(DatabaseUtils.findExistingIndex(connection, tableName, indexName)).contains(indexName);
+      assertThat(DatabaseUtils.findExistingIndex(connection, tableName, indexName.toLowerCase(Locale.US))).contains(indexName);
+      assertThat(DatabaseUtils.findExistingIndex(connection, tableName.toLowerCase(Locale.US), indexName.toLowerCase(Locale.US))).contains(indexName);
+    }
+  }
+
+  @Test
   public void should_close_connection() throws Exception {
-    Connection connection = dbTester.openConnection();
-    assertThat(isClosed(connection)).isFalse();
+    try (Connection connection = dbTester.openConnection()) {
+      assertThat(isClosed(connection)).isFalse();
 
-    DatabaseUtils.closeQuietly(connection);
-    assertThat(isClosed(connection)).isTrue();
+      DatabaseUtils.closeQuietly(connection);
+      assertThat(isClosed(connection)).isTrue();
+    }
   }
 
   @Test
@@ -80,9 +103,7 @@ public class DatabaseUtilsTest {
 
   @Test
   public void should_close_statement_and_resultset() throws Exception {
-    Connection connection = dbTester.openConnection();
-    try {
-      PreparedStatement statement = connection.prepareStatement(selectDual());
+    try (Connection connection = dbTester.openConnection(); PreparedStatement statement = connection.prepareStatement(selectDual())) {
       ResultSet rs = statement.executeQuery();
 
       DatabaseUtils.closeQuietly(rs);
@@ -90,8 +111,6 @@ public class DatabaseUtilsTest {
 
       assertThat(isClosed(statement)).isTrue();
       assertThat(isClosed(rs)).isTrue();
-    } finally {
-      DatabaseUtils.closeQuietly(connection);
     }
   }
 
@@ -167,8 +186,8 @@ public class DatabaseUtilsTest {
   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))))
-        .containsExactly(
-          myComparable(-1), myComparable(2), myComparable(4), myComparable(5), myComparable(10));
+      .containsExactly(
+        myComparable(-1), myComparable(2), myComparable(4), myComparable(5), myComparable(10));
   }
 
   private static DatabaseUtilsTest.MyComparable myComparable(int ordinal) {
@@ -291,8 +310,8 @@ public class DatabaseUtilsTest {
       },
       i -> i / 500);
 
-    assertThat(outputs).containsExactly(1,2,3);
-    assertThat(partitions).containsExactly(asList(1,2), asList(3));
+    assertThat(outputs).containsExactly(1, 2, 3);
+    assertThat(partitions).containsExactly(asList(1, 2), asList(3));
   }
 
   @Test
diff --git a/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/just_one_table.sql b/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/just_one_table.sql
deleted file mode 100644
index a1019056ef5..00000000000
--- a/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/just_one_table.sql
+++ /dev/null
@@ -1,3 +0,0 @@
-CREATE TABLE "SCHEMA_MIGRATIONS" (
-  "VERSION" VARCHAR(256) NOT NULL
-);
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
new file mode 100644
index 00000000000..ec4b8782e31
--- /dev/null
+++ b/server/sonar-db-core/src/test/resources/org/sonar/db/DatabaseUtilsTest/sql.sql
@@ -0,0 +1,7 @@
+CREATE TABLE "schema_migrations" (
+  "version" VARCHAR(256) NOT NULL
+);
+
+CREATE INDEX UPPER_CASE_NAME ON schema_migrations (version);
+
+CREATE INDEX lower_case_name ON schema_migrations (version);
diff --git a/server/sonar-db-core/src/testFixtures/java/org/sonar/db/CoreDbTester.java b/server/sonar-db-core/src/testFixtures/java/org/sonar/db/CoreDbTester.java
index f55338c174a..c4c14117b82 100644
--- a/server/sonar-db-core/src/testFixtures/java/org/sonar/db/CoreDbTester.java
+++ b/server/sonar-db-core/src/testFixtures/java/org/sonar/db/CoreDbTester.java
@@ -34,9 +34,13 @@ public class CoreDbTester extends AbstractDbTester<CoreTestDb> {
   }
 
   public static CoreDbTester createForSchema(Class testClass, String filename) {
+    return createForSchema(testClass, filename, true);
+  }
+
+  public static CoreDbTester createForSchema(Class testClass, String filename, boolean databaseToUpper) {
     String path = StringUtils.replaceChars(testClass.getCanonicalName(), '.', '/');
     String schemaPath = path + "/" + filename;
-    return new CoreDbTester(CoreTestDb.create(schemaPath));
+    return new CoreDbTester(CoreTestDb.create(schemaPath, databaseToUpper));
   }
 
   public static CoreDbTester createEmpty() {
diff --git a/server/sonar-db-core/src/testFixtures/java/org/sonar/db/CoreTestDb.java b/server/sonar-db-core/src/testFixtures/java/org/sonar/db/CoreTestDb.java
index 56d6821f9f7..c1c5c0dfdf8 100644
--- a/server/sonar-db-core/src/testFixtures/java/org/sonar/db/CoreTestDb.java
+++ b/server/sonar-db-core/src/testFixtures/java/org/sonar/db/CoreTestDb.java
@@ -28,10 +28,9 @@ import java.util.function.Consumer;
 import java.util.function.Function;
 import javax.annotation.Nullable;
 import javax.sql.DataSource;
-import org.apache.commons.codec.digest.DigestUtils;
 import org.junit.AssumptionViolatedException;
-import org.sonar.api.config.internal.Settings;
 import org.sonar.api.config.internal.MapSettings;
+import org.sonar.api.config.internal.Settings;
 import org.sonar.api.utils.log.Logger;
 import org.sonar.api.utils.log.Loggers;
 import org.sonar.db.version.SqTables;
@@ -63,16 +62,20 @@ class CoreTestDb implements TestDb {
   }
 
   static CoreTestDb create(String schemaPath) {
+    return create(schemaPath, true);
+  }
+
+  static CoreTestDb create(String schemaPath, boolean databaseToUpper) {
     requireNonNull(schemaPath, "schemaPath can't be null");
 
-    return new CoreTestDb().init(schemaPath);
+    return new CoreTestDb().init(schemaPath, databaseToUpper);
   }
 
   static CoreTestDb createEmpty() {
-    return new CoreTestDb().init(null);
+    return new CoreTestDb().init(null, true);
   }
 
-  private CoreTestDb init(@Nullable String schemaPath) {
+  private CoreTestDb init(@Nullable String schemaPath, boolean databaseToUpper) {
     Consumer<Settings> noExtraSettingsLoaded = settings -> {
     };
     Function<Settings, Database> databaseCreator = settings -> {
@@ -83,7 +86,11 @@ class CoreTestDb implements TestDb {
         throw new AssumptionViolatedException("This test is intended to be run on H2 only");
       }
 
-      return new CoreH2Database("h2Tests-" + (schemaPath == null ? "empty" : DigestUtils.md5Hex(schemaPath)));
+      String name = "\"h2Tests-\" + (schemaPath == null ? \"empty\" : DigestUtils.md5Hex(schemaPath))";
+      if (!databaseToUpper) {
+        name = name + ";DATABASE_TO_UPPER=FALSE";
+      }
+      return new CoreH2Database(name);
     };
     Consumer<Database> databaseInitializer = database -> {
       if (schemaPath == null) {