From 7a810baf64a44c619bef510836f15b89ea2a5666 Mon Sep 17 00:00:00 2001 From: Zipeng WU Date: Mon, 26 Apr 2021 11:33:52 +0200 Subject: [PATCH] SONAR-14685 installation fails on Oracle with quoted schema name --- .../main/java/org/sonar/db/DatabaseUtils.java | 48 ++++++++- .../java/org/sonar/db/DatabaseUtilsTest.java | 97 +++++++++++++++++++ 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 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 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 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); } } @@ -375,6 +407,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 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 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 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 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 foundIndex = findExistingIndex(indexName, schema, resultSet, false); + assertThat(foundIndex).hasValue(indexName); + } + + + private Optional 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); } -- 2.39.5