@Test @DisplayName("Retrieve view table usage from INFORMATION_SCHEMA") public void viewTableUsage(final Connection connection) throws Exception { int viewCount; final Collection<Table> tables = catalog.getTables(); viewCount = 0; assertThat(tables, hasSize(19)); for (final Table table : tables) { if (!(table instanceof View)) { continue; } viewCount = viewCount + 1; final View view = (View) table; assertThat(view.getTableUsage(), is(empty())); } assertThat(viewCount, is(1)); final InformationSchemaViews informationSchemaViews = InformationSchemaViewsBuilder.builder() .withSql( VIEW_TABLE_USAGE, "SELECT " + "VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, " + "TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME " + "FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE") .toOptions(); final SchemaRetrievalOptionsBuilder schemaRetrievalOptionsBuilder = SchemaRetrievalOptionsBuilder.builder(); schemaRetrievalOptionsBuilder.withInformationSchemaViews(informationSchemaViews); final SchemaRetrievalOptions schemaRetrievalOptions = schemaRetrievalOptionsBuilder.toOptions(); final RetrieverConnection retrieverConnection = new RetrieverConnection(connection, schemaRetrievalOptions); final SchemaCrawlerOptions options = SchemaCrawlerOptionsBuilder.newSchemaCrawlerOptions(); final TableExtRetriever tableExtRetriever = new TableExtRetriever(retrieverConnection, catalog, options); tableExtRetriever.retrieveViewTableUsage(); viewCount = 0; assertThat(tables, hasSize(19)); for (final Table table : tables) { if (!(table instanceof View)) { continue; } viewCount = viewCount + 1; final View view = (View) table; assertThat(view.getTableUsage(), is(not(empty()))); } assertThat(viewCount, is(1)); }
void retrieveViewTableUsage() throws SQLException { final InformationSchemaViews informationSchemaViews = getRetrieverConnection().getInformationSchemaViews(); if (!informationSchemaViews.hasQuery(VIEW_TABLE_USAGE)) { LOGGER.log( Level.INFO, "Not retrieving additional view table usage, since this was not requested"); LOGGER.log(Level.FINE, "View table usage SQL statement was not provided"); return; } LOGGER.log(Level.INFO, "Retrieving view table usage"); final Query viewTableUsageSql = informationSchemaViews.getQuery(VIEW_TABLE_USAGE); final Connection connection = getDatabaseConnection(); try (final Statement statement = connection.createStatement(); final MetadataResultSet results = new MetadataResultSet(viewTableUsageSql, statement, getSchemaInclusionRule())) { while (results.next()) { final String catalogName = normalizeCatalogName(results.getString("VIEW_CATALOG")); final String schemaName = normalizeSchemaName(results.getString("VIEW_SCHEMA")); final String viewName = results.getString("VIEW_NAME"); final Optional<MutableTable> viewOptional = lookupTable(catalogName, schemaName, viewName); if (!viewOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat("Cannot find view <%s.%s.%s>", catalogName, schemaName, viewName)); continue; } final MutableView view = (MutableView) viewOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving view information <%s>", viewName)); final String tableCatalogName = normalizeCatalogName(results.getString("TABLE_CATALOG")); final String tableSchemaName = normalizeSchemaName(results.getString("TABLE_SCHEMA")); final String tableName = results.getString("TABLE_NAME"); final Optional<MutableTable> tableOptional = lookupTable(tableCatalogName, tableSchemaName, tableName); if (!tableOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat( "Cannot find table <%s.%s.%s>", tableCatalogName, tableSchemaName, tableName)); continue; } final MutableTable table = tableOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving table information <%s>", tableName)); view.addTableUsage(table); } } catch (final Exception e) { LOGGER.log(Level.WARNING, "Could not retrieve table usage for views", e); } }
TableExtRetriever extends AbstractRetriever { void retrieveViewTableUsage() throws SQLException { final InformationSchemaViews informationSchemaViews = getRetrieverConnection().getInformationSchemaViews(); if (!informationSchemaViews.hasQuery(VIEW_TABLE_USAGE)) { LOGGER.log( Level.INFO, "Not retrieving additional view table usage, since this was not requested"); LOGGER.log(Level.FINE, "View table usage SQL statement was not provided"); return; } LOGGER.log(Level.INFO, "Retrieving view table usage"); final Query viewTableUsageSql = informationSchemaViews.getQuery(VIEW_TABLE_USAGE); final Connection connection = getDatabaseConnection(); try (final Statement statement = connection.createStatement(); final MetadataResultSet results = new MetadataResultSet(viewTableUsageSql, statement, getSchemaInclusionRule())) { while (results.next()) { final String catalogName = normalizeCatalogName(results.getString("VIEW_CATALOG")); final String schemaName = normalizeSchemaName(results.getString("VIEW_SCHEMA")); final String viewName = results.getString("VIEW_NAME"); final Optional<MutableTable> viewOptional = lookupTable(catalogName, schemaName, viewName); if (!viewOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat("Cannot find view <%s.%s.%s>", catalogName, schemaName, viewName)); continue; } final MutableView view = (MutableView) viewOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving view information <%s>", viewName)); final String tableCatalogName = normalizeCatalogName(results.getString("TABLE_CATALOG")); final String tableSchemaName = normalizeSchemaName(results.getString("TABLE_SCHEMA")); final String tableName = results.getString("TABLE_NAME"); final Optional<MutableTable> tableOptional = lookupTable(tableCatalogName, tableSchemaName, tableName); if (!tableOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat( "Cannot find table <%s.%s.%s>", tableCatalogName, tableSchemaName, tableName)); continue; } final MutableTable table = tableOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving table information <%s>", tableName)); view.addTableUsage(table); } } catch (final Exception e) { LOGGER.log(Level.WARNING, "Could not retrieve table usage for views", e); } } }
TableExtRetriever extends AbstractRetriever { void retrieveViewTableUsage() throws SQLException { final InformationSchemaViews informationSchemaViews = getRetrieverConnection().getInformationSchemaViews(); if (!informationSchemaViews.hasQuery(VIEW_TABLE_USAGE)) { LOGGER.log( Level.INFO, "Not retrieving additional view table usage, since this was not requested"); LOGGER.log(Level.FINE, "View table usage SQL statement was not provided"); return; } LOGGER.log(Level.INFO, "Retrieving view table usage"); final Query viewTableUsageSql = informationSchemaViews.getQuery(VIEW_TABLE_USAGE); final Connection connection = getDatabaseConnection(); try (final Statement statement = connection.createStatement(); final MetadataResultSet results = new MetadataResultSet(viewTableUsageSql, statement, getSchemaInclusionRule())) { while (results.next()) { final String catalogName = normalizeCatalogName(results.getString("VIEW_CATALOG")); final String schemaName = normalizeSchemaName(results.getString("VIEW_SCHEMA")); final String viewName = results.getString("VIEW_NAME"); final Optional<MutableTable> viewOptional = lookupTable(catalogName, schemaName, viewName); if (!viewOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat("Cannot find view <%s.%s.%s>", catalogName, schemaName, viewName)); continue; } final MutableView view = (MutableView) viewOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving view information <%s>", viewName)); final String tableCatalogName = normalizeCatalogName(results.getString("TABLE_CATALOG")); final String tableSchemaName = normalizeSchemaName(results.getString("TABLE_SCHEMA")); final String tableName = results.getString("TABLE_NAME"); final Optional<MutableTable> tableOptional = lookupTable(tableCatalogName, tableSchemaName, tableName); if (!tableOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat( "Cannot find table <%s.%s.%s>", tableCatalogName, tableSchemaName, tableName)); continue; } final MutableTable table = tableOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving table information <%s>", tableName)); view.addTableUsage(table); } } catch (final Exception e) { LOGGER.log(Level.WARNING, "Could not retrieve table usage for views", e); } } TableExtRetriever(
      final RetrieverConnection retrieverConnection,
      final MutableCatalog catalog,
      final SchemaCrawlerOptions options); }
TableExtRetriever extends AbstractRetriever { void retrieveViewTableUsage() throws SQLException { final InformationSchemaViews informationSchemaViews = getRetrieverConnection().getInformationSchemaViews(); if (!informationSchemaViews.hasQuery(VIEW_TABLE_USAGE)) { LOGGER.log( Level.INFO, "Not retrieving additional view table usage, since this was not requested"); LOGGER.log(Level.FINE, "View table usage SQL statement was not provided"); return; } LOGGER.log(Level.INFO, "Retrieving view table usage"); final Query viewTableUsageSql = informationSchemaViews.getQuery(VIEW_TABLE_USAGE); final Connection connection = getDatabaseConnection(); try (final Statement statement = connection.createStatement(); final MetadataResultSet results = new MetadataResultSet(viewTableUsageSql, statement, getSchemaInclusionRule())) { while (results.next()) { final String catalogName = normalizeCatalogName(results.getString("VIEW_CATALOG")); final String schemaName = normalizeSchemaName(results.getString("VIEW_SCHEMA")); final String viewName = results.getString("VIEW_NAME"); final Optional<MutableTable> viewOptional = lookupTable(catalogName, schemaName, viewName); if (!viewOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat("Cannot find view <%s.%s.%s>", catalogName, schemaName, viewName)); continue; } final MutableView view = (MutableView) viewOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving view information <%s>", viewName)); final String tableCatalogName = normalizeCatalogName(results.getString("TABLE_CATALOG")); final String tableSchemaName = normalizeSchemaName(results.getString("TABLE_SCHEMA")); final String tableName = results.getString("TABLE_NAME"); final Optional<MutableTable> tableOptional = lookupTable(tableCatalogName, tableSchemaName, tableName); if (!tableOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat( "Cannot find table <%s.%s.%s>", tableCatalogName, tableSchemaName, tableName)); continue; } final MutableTable table = tableOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving table information <%s>", tableName)); view.addTableUsage(table); } } catch (final Exception e) { LOGGER.log(Level.WARNING, "Could not retrieve table usage for views", e); } } TableExtRetriever(
      final RetrieverConnection retrieverConnection,
      final MutableCatalog catalog,
      final SchemaCrawlerOptions options);  }
TableExtRetriever extends AbstractRetriever { void retrieveViewTableUsage() throws SQLException { final InformationSchemaViews informationSchemaViews = getRetrieverConnection().getInformationSchemaViews(); if (!informationSchemaViews.hasQuery(VIEW_TABLE_USAGE)) { LOGGER.log( Level.INFO, "Not retrieving additional view table usage, since this was not requested"); LOGGER.log(Level.FINE, "View table usage SQL statement was not provided"); return; } LOGGER.log(Level.INFO, "Retrieving view table usage"); final Query viewTableUsageSql = informationSchemaViews.getQuery(VIEW_TABLE_USAGE); final Connection connection = getDatabaseConnection(); try (final Statement statement = connection.createStatement(); final MetadataResultSet results = new MetadataResultSet(viewTableUsageSql, statement, getSchemaInclusionRule())) { while (results.next()) { final String catalogName = normalizeCatalogName(results.getString("VIEW_CATALOG")); final String schemaName = normalizeSchemaName(results.getString("VIEW_SCHEMA")); final String viewName = results.getString("VIEW_NAME"); final Optional<MutableTable> viewOptional = lookupTable(catalogName, schemaName, viewName); if (!viewOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat("Cannot find view <%s.%s.%s>", catalogName, schemaName, viewName)); continue; } final MutableView view = (MutableView) viewOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving view information <%s>", viewName)); final String tableCatalogName = normalizeCatalogName(results.getString("TABLE_CATALOG")); final String tableSchemaName = normalizeSchemaName(results.getString("TABLE_SCHEMA")); final String tableName = results.getString("TABLE_NAME"); final Optional<MutableTable> tableOptional = lookupTable(tableCatalogName, tableSchemaName, tableName); if (!tableOptional.isPresent()) { LOGGER.log( Level.FINE, new StringFormat( "Cannot find table <%s.%s.%s>", tableCatalogName, tableSchemaName, tableName)); continue; } final MutableTable table = tableOptional.get(); LOGGER.log(Level.FINER, new StringFormat("Retrieving table information <%s>", tableName)); view.addTableUsage(table); } } catch (final Exception e) { LOGGER.log(Level.WARNING, "Could not retrieve table usage for views", e); } } TableExtRetriever(
      final RetrieverConnection retrieverConnection,
      final MutableCatalog catalog,
      final SchemaCrawlerOptions options);   }