SONAR-14685 installation fails on Oracle with quoted schema name

2 files changed, 142 insertions, 3 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 2d566ee3ede..d170870b844 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,8 +44,11 @@ import java.util.Set;
 import java.util.function.Consumer;
 import java.util.function.IntFunction;
 import java.util.function.Supplier;
+import java.util.regex.Pattern;
 import javax.annotation.CheckForNull;
+import javax.annotation.Nonnull;
 import javax.annotation.Nullable;
+import org.apache.commons.lang.StringUtils;
 import org.sonar.api.utils.log.Logger;
 import org.sonar.api.utils.log.Loggers;
 
@@ -54,8 +57,10 @@ import static com.google.common.collect.Lists.newArrayList;
 import static java.lang.String.format;
 
 public class DatabaseUtils {
-
+  private static final String TABLE_NOT_EXIST_MESSAGE = "Can not check that table %s exists";
   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}_$#@]*");
 
   /**
    * @see DatabaseMetaData#getTableTypes()
@@ -307,7 +312,7 @@ public class DatabaseUtils {
       }
       return false;
     } catch (SQLException e) {
-      throw wrapSqlException(e, "Can not check that table %s exists", table);
+      throw wrapSqlException(e, TABLE_NOT_EXIST_MESSAGE, table);
     }
   }
 
@@ -338,6 +343,18 @@ public class DatabaseUtils {
   private static Optional<String> findIndex(Connection connection, String tableName, String indexName) {
     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 getIndex(connection, tableName, indexName, schema);
+  }
+
+  private static Optional<String> getIndex(Connection connection, String tableName, String indexName, @Nullable String schema) {
     try (ResultSet rs = connection.getMetaData().getIndexInfo(connection.getCatalog(), schema, tableName, false, true)) {
       while (rs.next()) {
         String idx = rs.getString("INDEX_NAME");
@@ -347,7 +364,22 @@ public class DatabaseUtils {
       }
       return Optional.empty();
     } catch (SQLException e) {
-      throw wrapSqlException(e, "Can not check that table %s exists", tableName);
+      throw wrapSqlException(e, TABLE_NOT_EXIST_MESSAGE, tableName);
+    }
+  }
+
+  private static Optional<String> getOracleIndex(Connection connection, String tableName, String indexName, @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)) {
+          return Optional.of(idx);
+        }
+      }
+      return Optional.empty();
+    } catch (SQLException e) {
+      throw wrapSqlException(e, TABLE_NOT_EXIST_MESSAGE, tableName);
     }
   }
 
@@ -376,6 +408,16 @@ public class DatabaseUtils {
   }
 
   @CheckForNull
+  static String getDriver(Connection connection) {
+    try {
+      return connection.getMetaData().getDriverName();
+    } catch (SQLException e) {
+      Loggers.get(DatabaseUtils.class).warn("Fail to determine database driver.", e);
+      return null;
+    }
+  }
+
+  @CheckForNull
   private static String getSchema(Connection connection) {
     String schema = 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 21ae0cbba43..1bf15ffb479 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
@@ -32,6 +32,7 @@ import java.util.HashSet;
 import java.util.List;
 import java.util.Locale;
 import java.util.Objects;
+import java.util.Optional;
 import javax.annotation.Nullable;
 import org.junit.Rule;
 import org.junit.Test;
@@ -45,17 +46,23 @@ 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;
 import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyBoolean;
+import static org.mockito.ArgumentMatchers.anyString;
 import static org.mockito.ArgumentMatchers.eq;
 import static org.mockito.Mockito.doReturn;
 import static org.mockito.Mockito.doThrow;
 import static org.mockito.Mockito.mock;
 import static org.mockito.Mockito.spy;
 import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+import static org.sonar.db.DatabaseUtils.ORACLE_DRIVER_NAME;
 import static org.sonar.db.DatabaseUtils.toUniqueAndSortedList;
 
 public class DatabaseUtilsTest {
+  private static final String DEFAULT_SCHEMA = "public";
 
   @Rule
   public CoreDbTester dbTester = CoreDbTester.createForSchema(DatabaseUtilsTest.class, "sql.sql", false);
@@ -230,6 +237,96 @@ public class DatabaseUtilsTest {
         myComparable(-1), myComparable(2), myComparable(4), myComparable(5), myComparable(10));
   }
 
+  @Test
+  public void can_not_determine_database_driver() throws SQLException {
+    Connection connection = mock(Connection.class);
+    DatabaseMetaData metaData = mock(DatabaseMetaData.class);
+    when(connection.getMetaData()).thenReturn(metaData);
+    when(metaData.getDriverName()).thenThrow(new SQLException());
+    DatabaseUtils.getDriver(connection);
+    assertThat(logTester.logs(LoggerLevel.WARN)).contains("Fail to determine database driver.");
+  }
+
+  @Test
+  public void result_set_throw_exception() throws SQLException {
+    String indexName = "idx";
+    String schema = "TEST-SONAR";
+    ResultSet resultSet = mock(ResultSet.class);
+    when(resultSet.next()).thenThrow(new SQLException());
+    assertThatThrownBy(() -> findExistingIndex(indexName, schema, resultSet, true))
+      .isInstanceOf(IllegalStateException.class)
+      .hasMessage("Can not check that table test_table exists");
+  }
+
+  @Test
+  public void find_existing_index_on_oracle_double_quoted_schema() throws SQLException {
+    String indexName = "idx";
+    String schema = "TEST-SONAR";
+    ResultSet resultSet = newResultSet(true, indexName, schema);
+    Optional<String> foundIndex = findExistingIndex(indexName, schema, resultSet, true);
+    assertThat(foundIndex).hasValue(indexName);
+  }
+
+  @Test
+  public void find_existing_index_on_oracle_standard_schema() throws SQLException {
+    String indexName = "idx";
+    String schema = DEFAULT_SCHEMA;
+    ResultSet resultSet = newResultSet(true, indexName, schema);
+    Optional<String> foundIndex = findExistingIndex(indexName, schema, resultSet, true);
+    assertThat(foundIndex).hasValue(indexName);
+  }
+
+  @Test
+  public void no_existing_index_on_oracle_double_quoted_schema() throws SQLException {
+    String indexName = "idx";
+    String schema = "TEST-SONAR";
+    ResultSet resultSet = newResultSet(false, null, null);
+    Optional<String> foundIndex = findExistingIndex(indexName, schema, resultSet, true);
+    assertThat(foundIndex).isEmpty();
+  }
+
+  @Test
+  public void no_matching_index_on_oracle_double_quoted_schema() throws SQLException {
+    String indexName = "idx";
+    String schema = "TEST-SONAR";
+    ResultSet resultSet = newResultSet(true, "different", "different");
+    Optional<String> foundIndex = findExistingIndex(indexName, schema, resultSet, true);
+    assertThat(foundIndex).isEmpty();
+  }
+
+  @Test
+  public void find_existing_index_on_default_schema() throws SQLException {
+    String indexName = "idx";
+    String schema = DEFAULT_SCHEMA;
+    ResultSet resultSet = newResultSet(true, indexName, schema);
+    Optional<String> foundIndex = findExistingIndex(indexName, schema, resultSet, false);
+    assertThat(foundIndex).hasValue(indexName);
+  }
+
+
+  private Optional<String> findExistingIndex(String indexName, String schema, ResultSet resultSet, boolean isOracle) throws SQLException {
+    Connection connection = mock(Connection.class);
+    DatabaseMetaData metaData = mock(DatabaseMetaData.class);
+    if (isOracle) {
+      when(metaData.getDriverName()).thenReturn(ORACLE_DRIVER_NAME);
+    }
+    when(metaData.getIndexInfo(anyString(), eq(DEFAULT_SCHEMA.equals(schema) ? schema : null), anyString(), anyBoolean(), anyBoolean())).thenReturn(resultSet);
+    when(connection.getMetaData()).thenReturn(metaData);
+    when(connection.getSchema()).thenReturn(schema);
+    when(connection.getCatalog()).thenReturn("catalog");
+
+    return DatabaseUtils.findExistingIndex(connection, "test_table", indexName);
+  }
+
+  private ResultSet newResultSet(boolean hasNext, String indexName, String schema) throws SQLException {
+    ResultSet resultSet = mock(ResultSet.class);
+    when(resultSet.next()).thenReturn(hasNext).thenReturn(false);
+    when(resultSet.getString("INDEX_NAME")).thenReturn(indexName);
+    when(resultSet.getString("TABLE_SCHEM")).thenReturn(schema);
+    return resultSet;
+  }
+
+
   private static DatabaseUtilsTest.MyComparable myComparable(int ordinal) {
     return new DatabaseUtilsTest.MyComparable(ordinal);
   }