This is an automated email from the ASF dual-hosted git repository. dschneider pushed a commit to branch feature/GEODE-6156 in repository https://gitbox.apache.org/repos/asf/geode.git
commit 410dcfebd97179eeb07a5da66f2fb4ddeae055ce Author: Darrel Schneider <[email protected]> AuthorDate: Thu Dec 6 16:40:13 2018 -0800 TableMetaDataManager now supports a configured primary key ids parameter. --- .../TableMetaDataManagerIntegrationTest.java | 50 ++++++-- .../geode/connectors/jdbc/internal/SqlHandler.java | 4 +- .../jdbc/internal/TableMetaDataManager.java | 40 +++++- .../connectors/jdbc/internal/SqlHandlerTest.java | 2 +- .../jdbc/internal/TableMetaDataManagerTest.java | 139 +++++++++++++++++---- 5 files changed, 191 insertions(+), 44 deletions(-) diff --git a/geode-connectors/src/acceptanceTest/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManagerIntegrationTest.java b/geode-connectors/src/acceptanceTest/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManagerIntegrationTest.java index 2136f4a..399229c 100644 --- a/geode-connectors/src/acceptanceTest/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManagerIntegrationTest.java +++ b/geode-connectors/src/acceptanceTest/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManagerIntegrationTest.java @@ -42,7 +42,6 @@ public abstract class TableMetaDataManagerIntegrationTest { public void setup() throws Exception { connection = getConnection(); statement = connection.createStatement(); - createTable(); manager = new TableMetaDataManager(); } @@ -71,12 +70,20 @@ public abstract class TableMetaDataManagerIntegrationTest { statement.execute("CREATE TABLE " + REGION_TABLE_NAME + " (" + quote + "id" + quote + " VARCHAR(10) primary key not null," + quote + "name" + quote + " VARCHAR(10)," + quote + "age" + quote + " int)"); + } + protected void createTableWithNoPrimaryKey() throws SQLException { + DatabaseMetaData metaData = connection.getMetaData(); + String quote = metaData.getIdentifierQuoteString(); + statement.execute("CREATE TABLE " + REGION_TABLE_NAME + " (" + quote + "nonprimaryid" + quote + + " VARCHAR(10)," + quote + "name" + quote + " VARCHAR(10)," + quote + + "age" + quote + " int)"); } @Test - public void validateKeyColumnName() { - TableMetaDataView metaData = manager.getTableMetaDataView(connection, REGION_TABLE_NAME); + public void validateKeyColumnName() throws SQLException { + createTable(); + TableMetaDataView metaData = manager.getTableMetaDataView(connection, REGION_TABLE_NAME, null); String keyColumnName = metaData.getKeyColumnName(); @@ -84,8 +91,31 @@ public abstract class TableMetaDataManagerIntegrationTest { } @Test - public void validateColumnDataTypeForName() { - TableMetaDataView metaData = manager.getTableMetaDataView(connection, REGION_TABLE_NAME); + public void validateKeyColumnNameOnNonPrimaryKey() throws SQLException { + createTableWithNoPrimaryKey(); + TableMetaDataView metaData = + manager.getTableMetaDataView(connection, REGION_TABLE_NAME, "nonprimaryid"); + + String keyColumnName = metaData.getKeyColumnName(); + + assertThat(keyColumnName).isEqualTo("nonprimaryid"); + } + + @Test + public void validateKeyColumnNameOnNonPrimaryKeyWithInExactMatch() throws SQLException { + createTableWithNoPrimaryKey(); + TableMetaDataView metaData = + manager.getTableMetaDataView(connection, REGION_TABLE_NAME, "NonPrimaryId"); + + String keyColumnName = metaData.getKeyColumnName(); + + assertThat(keyColumnName).isEqualTo("NonPrimaryId"); + } + + @Test + public void validateColumnDataTypeForName() throws SQLException { + createTable(); + TableMetaDataView metaData = manager.getTableMetaDataView(connection, REGION_TABLE_NAME, null); int nameDataType = metaData.getColumnDataType("name"); @@ -93,8 +123,9 @@ public abstract class TableMetaDataManagerIntegrationTest { } @Test - public void validateColumnDataTypeForId() { - TableMetaDataView metaData = manager.getTableMetaDataView(connection, REGION_TABLE_NAME); + public void validateColumnDataTypeForId() throws SQLException { + createTable(); + TableMetaDataView metaData = manager.getTableMetaDataView(connection, REGION_TABLE_NAME, null); int nameDataType = metaData.getColumnDataType("id"); @@ -102,8 +133,9 @@ public abstract class TableMetaDataManagerIntegrationTest { } @Test - public void validateColumnDataTypeForAge() { - TableMetaDataView metaData = manager.getTableMetaDataView(connection, REGION_TABLE_NAME); + public void validateColumnDataTypeForAge() throws SQLException { + createTable(); + TableMetaDataView metaData = manager.getTableMetaDataView(connection, REGION_TABLE_NAME, null); int nameDataType = metaData.getColumnDataType("age"); diff --git a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlHandler.java b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlHandler.java index 5d46e73..0b2622c 100644 --- a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlHandler.java +++ b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlHandler.java @@ -76,7 +76,7 @@ public class SqlHandler { PdxInstance result; try (Connection connection = getConnection(regionMapping.getDataSourceName())) { TableMetaDataView tableMetaData = this.tableMetaDataManager.getTableMetaDataView(connection, - regionMapping.getRegionToTableName()); + regionMapping.getRegionToTableName(), null); EntryColumnData entryColumnData = getEntryColumnData(tableMetaData, regionMapping, key, null, Operation.GET); try (PreparedStatement statement = @@ -163,7 +163,7 @@ public class SqlHandler { try (Connection connection = getConnection(regionMapping.getDataSourceName())) { TableMetaDataView tableMetaData = this.tableMetaDataManager.getTableMetaDataView(connection, - regionMapping.getRegionToTableName()); + regionMapping.getRegionToTableName(), null); EntryColumnData entryColumnData = getEntryColumnData(tableMetaData, regionMapping, key, value, operation); int updateCount = 0; diff --git a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManager.java b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManager.java index f743caa..a454d3e 100644 --- a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManager.java +++ b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManager.java @@ -34,18 +34,20 @@ public class TableMetaDataManager { private final ConcurrentMap<String, TableMetaDataView> tableToMetaDataMap = new ConcurrentHashMap<>(); - public TableMetaDataView getTableMetaDataView(Connection connection, String tableName) { + public TableMetaDataView getTableMetaDataView(Connection connection, String tableName, + String ids) { return tableToMetaDataMap.computeIfAbsent(tableName, - k -> computeTableMetaDataView(connection, k)); + k -> computeTableMetaDataView(connection, k, ids)); } - private TableMetaDataView computeTableMetaDataView(Connection connection, String tableName) { + private TableMetaDataView computeTableMetaDataView(Connection connection, String tableName, + String ids) { TableMetaData result; try { DatabaseMetaData metaData = connection.getMetaData(); try (ResultSet tables = metaData.getTables(null, null, "%", null)) { String realTableName = getTableNameFromMetaData(tableName, tables); - String key = getPrimaryKeyColumnNameFromMetaData(realTableName, metaData); + String key = getPrimaryKeyColumnNameFromMetaData(realTableName, metaData, ids); String quoteString = metaData.getIdentifierQuoteString(); if (quoteString == null) { quoteString = ""; @@ -83,8 +85,16 @@ public class TableMetaDataManager { return result; } - private String getPrimaryKeyColumnNameFromMetaData(String tableName, DatabaseMetaData metaData) + private String getPrimaryKeyColumnNameFromMetaData(String tableName, DatabaseMetaData metaData, + String ids) throws SQLException { + if (ids != null && !ids.isEmpty()) { + if (!doesColumnExistInTable(tableName, metaData, ids)) { + throw new JdbcConnectorException( + "The table " + tableName + " does not have a column named " + ids); + } + return ids; + } try (ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName)) { if (!primaryKeys.next()) { throw new JdbcConnectorException( @@ -109,4 +119,24 @@ public class TableMetaDataManager { } } } + + private boolean doesColumnExistInTable(String tableName, DatabaseMetaData metaData, + String columnName) throws SQLException { + int caseInsensitiveMatches = 0; + try (ResultSet columnData = metaData.getColumns(null, null, tableName, "%")) { + while (columnData.next()) { + String realColumnName = columnData.getString("COLUMN_NAME"); + if (columnName.equals(realColumnName)) { + return true; + } else if (columnName.equalsIgnoreCase(realColumnName)) { + caseInsensitiveMatches++; + } + } + } + if (caseInsensitiveMatches > 1) { + throw new JdbcConnectorException( + "The table " + tableName + " has more than one column that matches " + columnName); + } + return caseInsensitiveMatches != 0; + } } diff --git a/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlHandlerTest.java b/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlHandlerTest.java index 4600886..0c89519 100644 --- a/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlHandlerTest.java +++ b/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlHandlerTest.java @@ -91,7 +91,7 @@ public class SqlHandlerTest { tableMetaDataView = mock(TableMetaDataView.class); when(tableMetaDataView.getTableName()).thenReturn(TABLE_NAME); when(tableMetaDataView.getKeyColumnName()).thenReturn(KEY_COLUMN); - when(tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME)) + when(tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null)) .thenReturn(tableMetaDataView); connectorService = mock(JdbcConnectorService.class); dataSourceFactory = mock(DataSourceFactory.class); diff --git a/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManagerTest.java b/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManagerTest.java index 9390263..caca0ba 100644 --- a/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManagerTest.java +++ b/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/TableMetaDataManagerTest.java @@ -68,17 +68,88 @@ public class TableMetaDataManagerTest { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(true).thenReturn(false); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, ""); assertThat(data.getKeyColumnName()).isEqualTo(KEY_COLUMN); verify(connection).getMetaData(); } @Test + public void givenNoColumnsAndNonNullIdsThenExpectException() throws Exception { + when(tablesResultSet.next()).thenReturn(true).thenReturn(false); + when(tablesResultSet.getString("TABLE_NAME")).thenReturn(TABLE_NAME); + when(columnResultSet.next()).thenReturn(false); + + assertThatThrownBy( + () -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, "nonExistentId")) + .isInstanceOf(JdbcConnectorException.class) + .hasMessageContaining("The table testTable does not have a column named nonExistentId"); + } + + @Test + public void givenOneColumnAndNonNullIdsThatDoesNotMatchThenExpectException() throws Exception { + when(tablesResultSet.next()).thenReturn(true).thenReturn(false); + when(tablesResultSet.getString("TABLE_NAME")).thenReturn(TABLE_NAME); + when(columnResultSet.next()).thenReturn(true).thenReturn(false); + when(columnResultSet.getString("COLUMN_NAME")).thenReturn("existingColumn"); + + assertThatThrownBy( + () -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, "nonExistentId")) + .isInstanceOf(JdbcConnectorException.class) + .hasMessageContaining("The table testTable does not have a column named nonExistentId"); + } + + @Test + public void givenTwoColumnsAndNonNullIdsThatDoesNotExactlyMatchThenExpectException() + throws Exception { + when(tablesResultSet.next()).thenReturn(true).thenReturn(false); + when(tablesResultSet.getString("TABLE_NAME")).thenReturn(TABLE_NAME); + when(columnResultSet.next()).thenReturn(true).thenReturn(true).thenReturn(false); + when(columnResultSet.getString("COLUMN_NAME")).thenReturn("nonexistentid") + .thenReturn("NONEXISTENTID"); + + assertThatThrownBy( + () -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, "nonExistentId")) + .isInstanceOf(JdbcConnectorException.class).hasMessageContaining( + "The table testTable has more than one column that matches nonExistentId"); + } + + @Test + public void givenThreeColumnsAndNonNullIdsThatDoesExactlyMatchThenKeyColumnNameIsReturned() + throws Exception { + when(tablesResultSet.next()).thenReturn(true).thenReturn(false); + when(tablesResultSet.getString("TABLE_NAME")).thenReturn(TABLE_NAME); + when(columnResultSet.next()).thenReturn(true).thenReturn(true).thenReturn(true) + .thenReturn(false); + when(columnResultSet.getString("COLUMN_NAME")).thenReturn("existentid").thenReturn("EXISTENTID") + .thenReturn("ExistentId"); + + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, "ExistentId"); + + assertThat(data.getKeyColumnName()).isEqualTo("ExistentId"); + } + + @Test + public void givenColumnAndNonNullIdsThatDoesInExactlyMatchThenKeyColumnNameIsReturned() + throws Exception { + when(tablesResultSet.next()).thenReturn(true).thenReturn(false); + when(tablesResultSet.getString("TABLE_NAME")).thenReturn(TABLE_NAME); + when(columnResultSet.next()).thenReturn(true).thenReturn(false); + when(columnResultSet.getString("COLUMN_NAME")).thenReturn("existentid"); + + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, "ExistentId"); + + assertThat(data.getKeyColumnName()).isEqualTo("ExistentId"); + } + + @Test public void returnsDefaultQuoteString() throws Exception { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(true).thenReturn(false); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); assertThat(data.getIdentifierQuoteString()).isEqualTo(""); verify(connection).getMetaData(); @@ -91,7 +162,8 @@ public class TableMetaDataManagerTest { String expectedQuoteString = "123"; when(databaseMetaData.getIdentifierQuoteString()).thenReturn(expectedQuoteString); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); assertThat(data.getIdentifierQuoteString()).isEqualTo(expectedQuoteString); verify(connection).getMetaData(); @@ -102,8 +174,8 @@ public class TableMetaDataManagerTest { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(true).thenReturn(false); - tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); - tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); verify(connection).getMetaData(); } @@ -112,8 +184,9 @@ public class TableMetaDataManagerTest { SQLException cause = new SQLException("sql message"); when(connection.getMetaData()).thenThrow(cause); - assertThatThrownBy(() -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME)) - .isInstanceOf(JdbcConnectorException.class).hasMessageContaining("sql message"); + assertThatThrownBy( + () -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null)) + .isInstanceOf(JdbcConnectorException.class).hasMessageContaining("sql message"); } @Test @@ -121,9 +194,10 @@ public class TableMetaDataManagerTest { when(tablesResultSet.next()).thenReturn(true).thenReturn(false); when(tablesResultSet.getString("TABLE_NAME")).thenReturn("otherTable"); - assertThatThrownBy(() -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME)) - .isInstanceOf(JdbcConnectorException.class) - .hasMessage("no table was found that matches testTable"); + assertThatThrownBy( + () -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null)) + .isInstanceOf(JdbcConnectorException.class) + .hasMessage("no table was found that matches testTable"); } @Test @@ -131,9 +205,10 @@ public class TableMetaDataManagerTest { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(true); - assertThatThrownBy(() -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME)) - .isInstanceOf(JdbcConnectorException.class) - .hasMessage("The table " + TABLE_NAME + " has more than one primary key column."); + assertThatThrownBy( + () -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null)) + .isInstanceOf(JdbcConnectorException.class) + .hasMessage("The table " + TABLE_NAME + " has more than one primary key column."); } @Test @@ -144,7 +219,8 @@ public class TableMetaDataManagerTest { when(tablesResultSet.getString("TABLE_NAME")).thenReturn(TABLE_NAME.toUpperCase()) .thenReturn(TABLE_NAME); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); assertThat(data.getTableName()).isEqualTo(TABLE_NAME); } @@ -158,7 +234,8 @@ public class TableMetaDataManagerTest { when(tablesResultSet.next()).thenReturn(true).thenReturn(false); when(tablesResultSet.getString("TABLE_NAME")).thenReturn(TABLE_NAME.toUpperCase()); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); assertThat(data.getTableName()).isEqualTo(TABLE_NAME.toUpperCase()); } @@ -172,9 +249,10 @@ public class TableMetaDataManagerTest { when(tablesResultSet.getString("TABLE_NAME")).thenReturn(TABLE_NAME.toLowerCase()) .thenReturn(TABLE_NAME.toUpperCase()); - assertThatThrownBy(() -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME)) - .isInstanceOf(JdbcConnectorException.class) - .hasMessage("Duplicate tables that match region name"); + assertThatThrownBy( + () -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null)) + .isInstanceOf(JdbcConnectorException.class) + .hasMessage("Duplicate tables that match region name"); } @Test @@ -182,9 +260,10 @@ public class TableMetaDataManagerTest { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(false); - assertThatThrownBy(() -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME)) - .isInstanceOf(JdbcConnectorException.class) - .hasMessage("The table " + TABLE_NAME + " does not have a primary key column."); + assertThatThrownBy( + () -> tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null)) + .isInstanceOf(JdbcConnectorException.class) + .hasMessage("The table " + TABLE_NAME + " does not have a primary key column."); } @Test @@ -192,7 +271,8 @@ public class TableMetaDataManagerTest { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(true).thenReturn(false); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); int dataType = data.getColumnDataType("unknownColumn"); assertThat(dataType).isEqualTo(0); @@ -211,7 +291,8 @@ public class TableMetaDataManagerTest { when(columnResultSet.getInt("DATA_TYPE")).thenReturn(columnDataType1) .thenReturn(columnDataType2); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); int dataType1 = data.getColumnDataType(columnName1); int dataType2 = data.getColumnDataType(columnName2); @@ -235,7 +316,8 @@ public class TableMetaDataManagerTest { .thenReturn(columnDataType2); Set<String> expectedColumnNames = new HashSet<>(Arrays.asList(columnName1, columnName2)); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); Set<String> columnNames = data.getColumnNames(); assertThat(columnNames).isEqualTo(expectedColumnNames); @@ -247,7 +329,8 @@ public class TableMetaDataManagerTest { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(true).thenReturn(false); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); verify(primaryKeysResultSet).close(); } @@ -258,7 +341,8 @@ public class TableMetaDataManagerTest { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(true).thenReturn(false); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); verify(columnResultSet).close(); } @@ -268,7 +352,8 @@ public class TableMetaDataManagerTest { setupPrimaryKeysMetaData(); when(primaryKeysResultSet.next()).thenReturn(true).thenReturn(false); - TableMetaDataView data = tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME); + TableMetaDataView data = + tableMetaDataManager.getTableMetaDataView(connection, TABLE_NAME, null); assertThat(data.getTableName()).isEqualTo(TABLE_NAME); }
