You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by pa...@apache.org on 2021/05/25 03:33:21 UTC
[shardingsphere] branch master updated: fix oracle meta data load
error (#10448)
This is an automated email from the ASF dual-hosted git repository.
panjuan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 8cc26a7 fix oracle meta data load error (#10448)
8cc26a7 is described below
commit 8cc26a728b048176b98679feee755025253a6b01
Author: Zhengqiang Duan <st...@gmail.com>
AuthorDate: Tue May 25 11:32:33 2021 +0800
fix oracle meta data load error (#10448)
* fix oracle jdbc start error
* fix checkstyle
* catch exception clause add final
---
.../scaling/ScalingRegistryServiceTest.java | 2 +-
.../schema/builder/loader/IndexMetaDataLoader.java | 6 +++
.../loader/dialect/OracleTableMetaDataLoader.java | 38 +++++++-------
.../dialect/OracleTableMetaDataLoaderTest.java | 61 ++++++++++++++++++----
4 files changed, 79 insertions(+), 28 deletions(-)
diff --git a/shardingsphere-governance/shardingsphere-governance-core/src/test/java/org/apache/shardingsphere/governance/core/registry/service/scaling/ScalingRegistryServiceTest.java b/shardingsphere-governance/shardingsphere-governance-core/src/test/java/org/apache/shardingsphere/governance/core/registry/service/scaling/ScalingRegistryServiceTest.java
index 3444365..21c00fc 100644
--- a/shardingsphere-governance/shardingsphere-governance-core/src/test/java/org/apache/shardingsphere/governance/core/registry/service/scaling/ScalingRegistryServiceTest.java
+++ b/shardingsphere-governance/shardingsphere-governance-core/src/test/java/org/apache/shardingsphere/governance/core/registry/service/scaling/ScalingRegistryServiceTest.java
@@ -69,7 +69,7 @@ public final class ScalingRegistryServiceTest {
// TODO finish verify
}
- public void assertCacheRuleConfiguration() {
+ public void assertCacheRuleConfiguration() {
// TODO finish test case
}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/IndexMetaDataLoader.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/IndexMetaDataLoader.java
index 39a01cc..df242ed 100644
--- a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/IndexMetaDataLoader.java
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/IndexMetaDataLoader.java
@@ -35,6 +35,8 @@ public final class IndexMetaDataLoader {
private static final String INDEX_NAME = "INDEX_NAME";
+ private static final int ORACLE_VIEW_NOT_APPROPRIATE_VENDOR_CODE = 1702;
+
/**
* Load index meta data list.
* In a few jdbc implementation(eg. oracle), return value of getIndexInfo contains a statistics record that not a index itself and INDEX_NAME is null.
@@ -54,6 +56,10 @@ public final class IndexMetaDataLoader {
result.add(new IndexMetaData(indexName));
}
}
+ } catch (final SQLException ex) {
+ if (ORACLE_VIEW_NOT_APPROPRIATE_VENDOR_CODE != ex.getErrorCode()) {
+ throw ex;
+ }
}
return result;
}
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/dialect/OracleTableMetaDataLoader.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/dialect/OracleTableMetaDataLoader.java
index 0599b21..0d8f029 100644
--- a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/dialect/OracleTableMetaDataLoader.java
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/dialect/OracleTableMetaDataLoader.java
@@ -25,6 +25,7 @@ import org.apache.shardingsphere.infra.metadata.schema.model.TableMetaData;
import javax.sql.DataSource;
import java.sql.Connection;
+import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@@ -41,18 +42,22 @@ import java.util.stream.Collectors;
* Table meta data loader for Oracle.
*/
public final class OracleTableMetaDataLoader implements DialectTableMetaDataLoader {
-
- private static final String TABLE_META_DATA_SQL = "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENTITY_COLUMN, COLLATION FROM ALL_TAB_COLUMNS WHERE TABLE_SCHEMA=?";
+
+ private static final String TABLE_META_DATA_SQL = "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENTITY_COLUMN %s FROM ALL_TAB_COLUMNS WHERE OWNER = ?";
private static final String TABLE_META_DATA_SQL_WITH_EXISTED_TABLES = TABLE_META_DATA_SQL + " AND TABLE_NAME NOT IN (%s)";
- private static final String INDEX_META_DATA_SQL = "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE TABLE_SCHEMA=? and TABLE_NAME IN (%s)";
-
+ private static final String INDEX_META_DATA_SQL = "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE OWNER = ? AND TABLE_NAME IN (%s)";
+
private static final String PRIMARY_KEY_META_DATA_SQL = "SELECT A.OWNER AS TABLE_SCHEMA, A.TABLE_NAME AS TABLE_NAME, B.COLUMN_NAME AS COLUMN_NAME FROM ALL_CONSTRAINTS A INNER JOIN"
- + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND TABLE_SCHEMA=?";
-
- private static final String PRIMARY_KEY_META_DATA_SQL_WITH_EXISTED_TABLES = PRIMARY_KEY_META_DATA_SQL + " AND TABLE_NAME NOT IN (%s)";
-
+ + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND A.OWNER = ?";
+
+ private static final String PRIMARY_KEY_META_DATA_SQL_WITH_EXISTED_TABLES = PRIMARY_KEY_META_DATA_SQL + " AND A.TABLE_NAME NOT IN (%s)";
+
+ private static final int COLLATION_START_MAJOR_VERSION = 12;
+
+ private static final int COLLATION_START_MINOR_VERSION = 2;
+
@Override
public Map<String, TableMetaData> load(final DataSource dataSource, final Collection<String> existedTables) throws SQLException {
return loadTableMetaDataMap(dataSource, existedTables);
@@ -70,9 +75,7 @@ public final class OracleTableMetaDataLoader implements DialectTableMetaDataLoad
private Map<String, Collection<ColumnMetaData>> loadColumnMetaDataMap(final DataSource dataSource, final Collection<String> existedTables) throws SQLException {
Map<String, Collection<ColumnMetaData>> result = new HashMap<>();
- try (
- Connection connection = dataSource.getConnection();
- PreparedStatement preparedStatement = connection.prepareStatement(getTableMetaDataSQL(existedTables))) {
+ try (Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(getTableMetaDataSQL(existedTables, connection.getMetaData()))) {
Map<String, Integer> dataTypes = DataTypeLoader.load(connection.getMetaData());
Map<String, Collection<String>> tablePrimaryKeys = loadTablePrimaryKeys(connection, existedTables);
preparedStatement.setString(1, connection.getCatalog());
@@ -95,21 +98,20 @@ public final class OracleTableMetaDataLoader implements DialectTableMetaDataLoad
String dataType = resultSet.getString("DATA_TYPE");
boolean primaryKey = primaryKeys.contains(columnName);
boolean generated = "YES".equals(resultSet.getString("IDENTITY_COLUMN"));
- String collationName = resultSet.getString("COLLATION_NAME");
+ String collationName = resultSet.getString("COLLATION");
boolean caseSensitive = null != collationName && collationName.endsWith("_CS");
return new ColumnMetaData(columnName, dataTypeMap.get(dataType), primaryKey, generated, caseSensitive);
}
- private String getTableMetaDataSQL(final Collection<String> existedTables) {
- return existedTables.isEmpty() ? TABLE_META_DATA_SQL
- : String.format(TABLE_META_DATA_SQL_WITH_EXISTED_TABLES, existedTables.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
+ private String getTableMetaDataSQL(final Collection<String> existedTables, final DatabaseMetaData metaData) throws SQLException {
+ String collation = metaData.getDatabaseMajorVersion() >= COLLATION_START_MAJOR_VERSION && metaData.getDatabaseMinorVersion() >= COLLATION_START_MINOR_VERSION ? ", COLLATION" : "";
+ return existedTables.isEmpty() ? String.format(TABLE_META_DATA_SQL, collation)
+ : String.format(TABLE_META_DATA_SQL_WITH_EXISTED_TABLES, collation, existedTables.stream().map(each -> String.format("'%s'", each)).collect(Collectors.joining(",")));
}
private Map<String, Collection<IndexMetaData>> loadIndexMetaData(final DataSource dataSource, final Collection<String> tableNames) throws SQLException {
Map<String, Collection<IndexMetaData>> result = new HashMap<>();
- try (
- Connection connection = dataSource.getConnection();
- PreparedStatement preparedStatement = connection.prepareStatement(getIndexMetaDataSQL(tableNames))) {
+ try (Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(getIndexMetaDataSQL(tableNames))) {
preparedStatement.setString(1, connection.getCatalog());
try (ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/dialect/OracleTableMetaDataLoaderTest.java b/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/dialect/OracleTableMetaDataLoaderTest.java
index 78be3ce..b01edaf 100644
--- a/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/dialect/OracleTableMetaDataLoaderTest.java
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/test/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/dialect/OracleTableMetaDataLoaderTest.java
@@ -45,37 +45,80 @@ public final class OracleTableMetaDataLoaderTest {
}
@Test
- public void assertLoadWithoutExistedTables() throws SQLException {
+ public void assertLoadWithoutExistedTablesWithoutCollation() throws SQLException {
DataSource dataSource = mockDataSource();
ResultSet resultSet = mockTableMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENTITY_COLUMN, COLLATION FROM ALL_TAB_COLUMNS WHERE TABLE_SCHEMA=?").executeQuery()).thenReturn(resultSet);
+ "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENTITY_COLUMN FROM ALL_TAB_COLUMNS WHERE OWNER = ?").executeQuery()).thenReturn(resultSet);
ResultSet indexResultSet = mockIndexMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE TABLE_SCHEMA=? and TABLE_NAME IN ('tbl')").executeQuery()).thenReturn(indexResultSet);
+ "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE OWNER = ? AND TABLE_NAME IN ('tbl')").executeQuery()).thenReturn(indexResultSet);
ResultSet primaryKeys = mockPrimaryKeysMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
"SELECT A.OWNER AS TABLE_SCHEMA, A.TABLE_NAME AS TABLE_NAME, B.COLUMN_NAME AS COLUMN_NAME FROM ALL_CONSTRAINTS A INNER JOIN"
- + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND TABLE_SCHEMA=?").executeQuery()).thenReturn(primaryKeys);
+ + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND A.OWNER = ?").executeQuery()).thenReturn(primaryKeys);
+ when(dataSource.getConnection().getMetaData().getDatabaseMajorVersion()).thenReturn(12);
+ when(dataSource.getConnection().getMetaData().getDatabaseMinorVersion()).thenReturn(1);
assertTableMetaDataMap(getTableMetaDataLoader().load(dataSource, Collections.emptyList()));
}
@Test
- public void assertLoadWithExistedTables() throws SQLException {
+ public void assertLoadWithoutExistedTablesWithCollation() throws SQLException {
DataSource dataSource = mockDataSource();
ResultSet resultSet = mockTableMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENTITY_COLUMN, COLLATION FROM ALL_TAB_COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME NOT IN ('existed_tbl')")
+ "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENTITY_COLUMN , COLLATION FROM ALL_TAB_COLUMNS WHERE OWNER = ?").executeQuery()).thenReturn(resultSet);
+ ResultSet indexResultSet = mockIndexMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(
+ "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE OWNER = ? AND TABLE_NAME IN ('tbl')").executeQuery()).thenReturn(indexResultSet);
+ ResultSet primaryKeys = mockPrimaryKeysMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(
+ "SELECT A.OWNER AS TABLE_SCHEMA, A.TABLE_NAME AS TABLE_NAME, B.COLUMN_NAME AS COLUMN_NAME FROM ALL_CONSTRAINTS A INNER JOIN"
+ + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND A.OWNER = ?").executeQuery()).thenReturn(primaryKeys);
+ when(dataSource.getConnection().getMetaData().getDatabaseMajorVersion()).thenReturn(12);
+ when(dataSource.getConnection().getMetaData().getDatabaseMinorVersion()).thenReturn(2);
+ assertTableMetaDataMap(getTableMetaDataLoader().load(dataSource, Collections.emptyList()));
+ }
+
+ @Test
+ public void assertLoadWithExistedTablesWithoutCollation() throws SQLException {
+ DataSource dataSource = mockDataSource();
+ ResultSet resultSet = mockTableMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(
+ "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENTITY_COLUMN FROM ALL_TAB_COLUMNS WHERE OWNER = ? AND TABLE_NAME NOT IN ('existed_tbl')")
+ .executeQuery()).thenReturn(resultSet);
+ ResultSet indexResultSet = mockIndexMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(
+ "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE OWNER = ? AND TABLE_NAME IN ('tbl')")
+ .executeQuery()).thenReturn(indexResultSet);
+ ResultSet primaryKeys = mockPrimaryKeysMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(
+ "SELECT A.OWNER AS TABLE_SCHEMA, A.TABLE_NAME AS TABLE_NAME, B.COLUMN_NAME AS COLUMN_NAME FROM ALL_CONSTRAINTS A INNER JOIN"
+ + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND A.OWNER = ? AND A.TABLE_NAME NOT IN ('existed_tbl')")
+ .executeQuery()).thenReturn(primaryKeys);
+ when(dataSource.getConnection().getMetaData().getDatabaseMajorVersion()).thenReturn(12);
+ when(dataSource.getConnection().getMetaData().getDatabaseMinorVersion()).thenReturn(1);
+ assertTableMetaDataMap(getTableMetaDataLoader().load(dataSource, Collections.singletonList("existed_tbl")));
+ }
+
+ @Test
+ public void assertLoadWithExistedTablesWithCollation() throws SQLException {
+ DataSource dataSource = mockDataSource();
+ ResultSet resultSet = mockTableMetaDataResultSet();
+ when(dataSource.getConnection().prepareStatement(
+ "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IDENTITY_COLUMN , COLLATION FROM ALL_TAB_COLUMNS WHERE OWNER = ? AND TABLE_NAME NOT IN ('existed_tbl')")
.executeQuery()).thenReturn(resultSet);
ResultSet indexResultSet = mockIndexMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
- "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE TABLE_SCHEMA=? and TABLE_NAME IN ('tbl')")
+ "SELECT OWNER AS TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE OWNER = ? AND TABLE_NAME IN ('tbl')")
.executeQuery()).thenReturn(indexResultSet);
ResultSet primaryKeys = mockPrimaryKeysMetaDataResultSet();
when(dataSource.getConnection().prepareStatement(
"SELECT A.OWNER AS TABLE_SCHEMA, A.TABLE_NAME AS TABLE_NAME, B.COLUMN_NAME AS COLUMN_NAME FROM ALL_CONSTRAINTS A INNER JOIN"
- + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND TABLE_SCHEMA=? AND TABLE_NAME NOT IN ('existed_tbl')")
+ + " ALL_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME WHERE CONSTRAINT_TYPE = 'P' AND A.OWNER = ? AND A.TABLE_NAME NOT IN ('existed_tbl')")
.executeQuery()).thenReturn(primaryKeys);
+ when(dataSource.getConnection().getMetaData().getDatabaseMajorVersion()).thenReturn(12);
+ when(dataSource.getConnection().getMetaData().getDatabaseMinorVersion()).thenReturn(2);
assertTableMetaDataMap(getTableMetaDataLoader().load(dataSource, Collections.singletonList("existed_tbl")));
}
@@ -101,7 +144,7 @@ public final class OracleTableMetaDataLoaderTest {
when(result.getString("COLUMN_NAME")).thenReturn("id", "name");
when(result.getString("DATA_TYPE")).thenReturn("int", "varchar");
when(result.getString("IDENTITY_COLUMN")).thenReturn("YES", "NO");
- when(result.getString("COLLATION_NAME")).thenReturn("BINARY_CS", "BINARY_CI");
+ when(result.getString("COLLATION")).thenReturn("BINARY_CS", "BINARY_CI");
return result;
}