private Element createStoredProcedureElement(final Document dom, final Routine sp) { final Schema schema = sp.getSchema(); final Element eStoredProcedure = dom.createElement("storedprocedure"); final boolean isFunction = sp.getRoutineType() == RoutineType.function; eStoredProcedure.setAttribute("type", isFunction ? "function" : "procedure"); eStoredProcedure.setAttribute("schema", schema.getName()); eStoredProcedure.setAttribute("catalog", sp.getSchema().getCatalogName()); // System.out.printf ("%s> [%s] [%s]%n", sp.getName (), sp.getDefinition (), // sp.getRoutineBodyType ()); final String storedProcedureName = getPhysicalName(sp.getName()); eStoredProcedure.setAttribute("name", getLogicalName(storedProcedureName)); eStoredProcedure.setAttribute("physicalname", storedProcedureName); final List<? extends RoutineColumn<? extends Routine>> parameters = sp.getColumns(); for (final RoutineColumn<? extends Routine> parameter : parameters) { if (shouldIncludeParameter(sp, parameter)) { final Element eParameter = createParameterElement(dom, parameter); eStoredProcedure.appendChild(eParameter); } } return eStoredProcedure; }
private static void crawlColumnDataTypes( final MutableCatalog catalog, final RetrieverConnection retrieverConnection, final SchemaCrawlerOptions options) throws SchemaCrawlerException { try { LOGGER.log(Level.INFO, "Crawling column data types"); final StopWatch stopWatch = new StopWatch("crawlColumnDataTypes"); final SchemaInfoLevel infoLevel = options.getSchemaInfoLevel(); final DatabaseInfoRetriever retriever = new DatabaseInfoRetriever(retrieverConnection, catalog); stopWatch.time( "retrieveSystemColumnDataTypes", () -> { if (infoLevel.isRetrieveColumnDataTypes()) { LOGGER.log(Level.INFO, "Retrieving system column data types"); retriever.retrieveSystemColumnDataTypes(); } else { LOGGER.log( Level.INFO, "Not retrieving system column data types, since this was not requested"); } return null; }); stopWatch.time( "retrieveUserDefinedColumnDataTypes", () -> { if (infoLevel.isRetrieveUserDefinedColumnDataTypes()) { LOGGER.log(Level.INFO, "Retrieving user column data types"); for (final Schema schema : retriever.getSchemas()) { retriever.retrieveUserDefinedColumnDataTypes( schema.getCatalogName(), schema.getName()); } } else { LOGGER.log( Level.INFO, "Not retrieving user column data types, since this was not requested"); } return null; }); LOGGER.log(Level.INFO, stopWatch.toString()); } catch (final Exception e) { if (e instanceof SchemaCrawlerSQLException) { throw new SchemaCrawlerException(e.getMessage(), e.getCause()); } else if (e instanceof SchemaCrawlerException) { throw (SchemaCrawlerException) e; } else { throw new SchemaCrawlerException("Exception retrieving column data type information", e); } } }
@Test public void testSchema() throws Exception { final SchemaCrawlerOptions schemaCrawlerOptions = (SchemaCrawlerOptions) appContext.getBean("schemaCrawlerOptions"); final Schema schema = testUtility.getSchema(schemaCrawlerOptions, "PUBLIC"); assertNotNull("Could not obtain schema", schema); assertEquals("Unexpected number of tables in the schema", 6, schema.getTables().length); }
@Test public void tableEquals() { final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions(); schemaCrawlerOptions.setShowStoredProcedures(true); final Catalog catalog = testUtility.getCatalog(schemaCrawlerOptions); assertNotNull("Could not obtain catalog", catalog); assertTrue("Could not find any schemas", catalog.getSchemas().length > 0); final Schema schema = catalog.getSchema("PUBLIC"); assertNotNull("Could not obtain schema", schema); assertTrue("Could not find any tables", schema.getTables().length > 0); assertTrue("Could not find any procedures", schema.getProcedures().length > 0); // Try negative test final Table table0 = schema.getTables()[0]; assertTrue("Could not find any columns", table0.getColumns().length > 0); final MutableTable table1 = new MutableTable(table0.getCatalogName(), table0.getSchemaName(), "Test Table 1"); final MutableTable table2 = new MutableTable(table0.getCatalogName(), table0.getSchemaName(), "Test Table 2"); final PrimaryKey primaryKey = table0.getPrimaryKey(); table1.setPrimaryKey(primaryKey); table2.setPrimaryKey(primaryKey); for (final Column column : table0.getColumns()) { table1.addColumn((MutableColumn) column); table2.addColumn((MutableColumn) column); } for (final Index index : table0.getIndices()) { table1.addIndex((MutableIndex) index); table2.addIndex((MutableIndex) index); } for (final ForeignKey fk : table0.getForeignKeys()) { table1.addForeignKey((MutableForeignKey) fk); table2.addForeignKey((MutableForeignKey) fk); } for (final Trigger trigger : table0.getTriggers()) { table1.addTrigger((MutableTrigger) trigger); table2.addTrigger((MutableTrigger) trigger); } for (final Privilege privilege : table0.getPrivileges()) { table1.addPrivilege((MutablePrivilege) privilege); table2.addPrivilege((MutablePrivilege) privilege); } for (final CheckConstraint checkConstraint : table0.getCheckConstraints()) { table1.addCheckConstraint((MutableCheckConstraint) checkConstraint); table2.addCheckConstraint((MutableCheckConstraint) checkConstraint); } assertFalse("Tables should not be equal", table1.equals(table2)); }
private void buildFullName() { if (fullName == null) { final StringBuilder buffer = new StringBuilder(); if (schema != null && !Utility.isBlank(schema.getFullName())) { buffer.append(schema.getFullName()).append("."); } if (!Utility.isBlank(getName())) { buffer.append(getName()); } fullName = buffer.toString(); } }
@Test public void testSchema() { final SchemaCrawlerOptions schemaCrawlerOptions = (SchemaCrawlerOptions) appContext.getBean("schemaCrawlerOptions"); final Catalog catalog = testUtility.getCatalog(schemaCrawlerOptions); assertNotNull("Could not obtain catalog", catalog); assertTrue("Could not find any schemas", catalog.getSchemas().length > 0); final Schema schema = catalog.getSchema("PUBLIC"); assertNotNull("Could not obtain schema", schema); assertEquals(6, schema.getTables().length); }
private void buildHashCode() { if (hashCode == 0) { final int prime = 31; int result = super.hashCode(); result = prime * result + (schema == null ? 0 : schema.hashCode()); result = prime * result + super.hashCode(); hashCode = result; } }
private void tables(final String dataSourceName, final Schema schema) throws Exception { final String[] tableNames = { "CUSTOMER", "CUSTOMERLIST", "INVOICE", "ITEM", "PRODUCT", "SUPPLIER" }; final String[] tableTypes = {"TABLE", "VIEW", "TABLE", "TABLE", "TABLE", "TABLE"}; final Table[] tables = schema.getTables(); assertEquals(dataSourceName + " table count does not match", tableNames.length, tables.length); for (int tableIdx = 0; tableIdx < tables.length; tableIdx++) { final Table table = tables[tableIdx]; assertEquals( dataSourceName + " table name does not match", tableNames[tableIdx], table.getName().toUpperCase()); assertEquals( dataSourceName + " table type does not match", tableTypes[tableIdx], table.getType().toString().toUpperCase()); } }
/** * {@inheritDoc} * * @see java.lang.Object#equals(java.lang.Object) */ @Override public boolean equals(final Object obj) { if (!super.equals(obj)) { return false; } if (this == obj) { return true; } if (obj == null) { return false; } final AbstractDatabaseObject other = (AbstractDatabaseObject) obj; if (schema == null) { if (other.schema != null) { return false; } } else if (!schema.equals(other.schema)) { return false; } return true; }
private void counts(final String dataSourceName, final Schema schema) throws Exception { final int[] tableColumnCounts = {5, 3, 3, 5, 3, 2}; final int[] checkConstraints = {0, 0, 0, 0, 0, 0}; // final int[] indexCounts = { // 0, 0, 2, 4, 0, 2 // }; final int[] fkCounts = {1, 0, 2, 2, 1, 0}; final Table[] tables = schema.getTables(); assertEquals( dataSourceName + " table count does not match", tableColumnCounts.length, tables.length); for (int tableIdx = 0; tableIdx < tables.length; tableIdx++) { final Table table = tables[tableIdx]; assertEquals( String.format( "%s table %s columns count does not match", dataSourceName, table.getFullName()), tableColumnCounts[tableIdx], table.getColumns().length); assertEquals( String.format( "%s table %s check constraints count does not match", dataSourceName, table.getFullName()), checkConstraints[tableIdx], table.getCheckConstraints().length); // assertEquals(String.format("%s table %s index count does not match",dataSourceName, // table // .getFullName()), indexCounts[tableIdx], // table.getIndices().length); assertEquals( String.format( "%s table %s foreign key count does not match", dataSourceName, table.getFullName()), fkCounts[tableIdx], table.getForeignKeys().length); } }
private static void crawlTables( final MutableCatalog catalog, final RetrieverConnection retrieverConnection, final SchemaCrawlerOptions options) throws SchemaCrawlerException { final SchemaInfoLevel infoLevel = options.getSchemaInfoLevel(); final boolean retrieveTables = infoLevel.isRetrieveTables(); if (!retrieveTables) { LOGGER.log(Level.INFO, "Not retrieving tables, since this was not requested"); return; } final StopWatch stopWatch = new StopWatch("crawlTables"); LOGGER.log(Level.INFO, "Crawling tables"); final TableRetriever retriever; final TableColumnRetriever columnRetriever; final ForeignKeyRetriever fkRetriever; final TableExtRetriever retrieverExtra; try { retriever = new TableRetriever(retrieverConnection, catalog); columnRetriever = new TableColumnRetriever(retrieverConnection, catalog); fkRetriever = new ForeignKeyRetriever(retrieverConnection, catalog); retrieverExtra = new TableExtRetriever(retrieverConnection, catalog); stopWatch.time( "retrieveTables", () -> { for (final Schema schema : retriever.getSchemas()) { retriever.retrieveTables( schema.getCatalogName(), schema.getName(), options.getTableNamePattern(), options.getTableTypes(), options.getTableInclusionRule()); } return null; }); final NamedObjectList<MutableTable> allTables = catalog.getAllTables(); stopWatch.time( "retrieveColumns", () -> { if (infoLevel.isRetrieveTableColumns()) { columnRetriever.retrieveColumns(allTables, options.getColumnInclusionRule()); } return null; }); stopWatch.time( "retrieveForeignKeys", () -> { if (infoLevel.isRetrieveForeignKeys()) { if (infoLevel.isRetrieveTableColumns()) { fkRetriever.retrieveForeignKeys(allTables); } } else { LOGGER.log( Level.WARNING, "Foreign-keys are not being retrieved, so tables cannot be sorted using the natural sort order"); } return null; }); stopWatch.time( "filterAndSortTables", () -> { // Filter the list of tables based on grep criteria, and // parent-child relationships final Predicate<Table> tableFilter = tableFilter(options); ((Reducible) catalog).reduce(Table.class, new TablesReducer(options, tableFilter)); // Sort the remaining tables final TablesGraph tablesGraph = new TablesGraph(allTables); tablesGraph.setTablesSortIndexes(); return null; }); stopWatch.time( "retrieveIndexes", () -> { LOGGER.log(Level.INFO, "Retrieving primary keys and indexes"); for (final MutableTable table : allTables) { final boolean isView = table instanceof MutableView; if (!isView && infoLevel.isRetrieveTableColumns()) { retriever.retrievePrimaryKey(table); if (infoLevel.isRetrieveIndexes()) { retriever.retrieveIndexes(table, true); retriever.retrieveIndexes(table, false); // table.replacePrimaryKey(); } } } return null; }); stopWatch.time( "retrieveTableConstraintInformation", () -> { if (infoLevel.isRetrieveTableConstraintInformation()) { retrieverExtra.retrieveTableConstraintInformation(); } return null; }); stopWatch.time( "retrieveTriggerInformation", () -> { if (infoLevel.isRetrieveTriggerInformation()) { retrieverExtra.retrieveTriggerInformation(); } return null; }); stopWatch.time( "retrieveViewInformation", () -> { if (infoLevel.isRetrieveViewInformation()) { retrieverExtra.retrieveViewInformation(); } return null; }); stopWatch.time( "retrieveTableDefinitions", () -> { if (infoLevel.isRetrieveTableDefinitionsInformation()) { retrieverExtra.retrieveTableDefinitions(); } return null; }); stopWatch.time( "retrieveIndexInformation", () -> { if (infoLevel.isRetrieveIndexInformation()) { retrieverExtra.retrieveIndexInformation(); } return null; }); stopWatch.time( "retrieveAdditionalTableAttributes", () -> { if (infoLevel.isRetrieveAdditionalTableAttributes()) { retrieverExtra.retrieveAdditionalTableAttributes(); } return null; }); stopWatch.time( "retrieveTablePrivileges", () -> { if (infoLevel.isRetrieveTablePrivileges()) { retrieverExtra.retrieveTablePrivileges(); } return null; }); stopWatch.time( "retrieveAdditionalColumnAttributes", () -> { if (infoLevel.isRetrieveAdditionalColumnAttributes()) { retrieverExtra.retrieveAdditionalColumnAttributes(); } return null; }); stopWatch.time( "retrieveTableColumnPrivileges", () -> { if (infoLevel.isRetrieveTableColumnPrivileges()) { retrieverExtra.retrieveTableColumnPrivileges(); } return null; }); LOGGER.log(Level.INFO, stopWatch.toString()); } catch (final Exception e) { if (e instanceof SchemaCrawlerSQLException) { throw new SchemaCrawlerException(e.getMessage(), e.getCause()); } else if (e instanceof SchemaCrawlerException) { throw (SchemaCrawlerException) e; } else { throw new SchemaCrawlerException("Exception retrieving table information", e); } } }
private static void crawlRoutines( final MutableCatalog catalog, final RetrieverConnection retrieverConnection, final SchemaCrawlerOptions options) throws SchemaCrawlerException { final StopWatch stopWatch = new StopWatch("crawlRoutines"); final SchemaInfoLevel infoLevel = options.getSchemaInfoLevel(); final boolean retrieveRoutines = infoLevel.isRetrieveRoutines(); if (!retrieveRoutines) { LOGGER.log(Level.INFO, "Not retrieving routines, since this was not requested"); return; } LOGGER.log(Level.INFO, "Crawling routines"); final RoutineRetriever retriever; final RoutineExtRetriever retrieverExtra; try { retriever = new RoutineRetriever(retrieverConnection, catalog); retrieverExtra = new RoutineExtRetriever(retrieverConnection, catalog); final Collection<RoutineType> routineTypes = options.getRoutineTypes(); stopWatch.time( "retrieveRoutines", () -> { for (final Schema schema : retriever.getSchemas()) { if (routineTypes.contains(RoutineType.procedure)) { retriever.retrieveProcedures( schema.getCatalogName(), schema.getName(), options.getRoutineInclusionRule()); } if (routineTypes.contains(RoutineType.function)) { retriever.retrieveFunctions( schema.getCatalogName(), schema.getName(), options.getRoutineInclusionRule()); } } return null; }); final NamedObjectList<MutableRoutine> allRoutines = catalog.getAllRoutines(); stopWatch.time( "retrieveRoutineColumns", () -> { LOGGER.log(Level.INFO, "Retrieving routine columns"); for (final MutableRoutine routine : allRoutines) { if (infoLevel.isRetrieveRoutineColumns()) { if (routine instanceof MutableProcedure && routineTypes.contains(RoutineType.procedure)) { retriever.retrieveProcedureColumns( (MutableProcedure) routine, options.getRoutineColumnInclusionRule()); } if (routine instanceof MutableFunction && routineTypes.contains(RoutineType.function)) { retriever.retrieveFunctionColumns( (MutableFunction) routine, options.getRoutineColumnInclusionRule()); } } } return null; }); stopWatch.time( "filterRoutines", () -> { // Filter the list of routines based on grep criteria final Predicate<Routine> routineFilter = routineFilter(options); ((Reducible) catalog).reduce(Routine.class, new RoutinesReducer(routineFilter)); return null; }); stopWatch.time( "retrieveRoutineInformation", () -> { if (infoLevel.isRetrieveRoutineInformation()) { retrieverExtra.retrieveRoutineInformation(); } return null; }); LOGGER.log(Level.INFO, stopWatch.toString()); } catch (final Exception e) { if (e instanceof SchemaCrawlerSQLException) { throw new SchemaCrawlerException(e.getMessage(), e.getCause()); } else if (e instanceof SchemaCrawlerException) { throw (SchemaCrawlerException) e; } else { throw new SchemaCrawlerException("Exception retrieving routine information", e); } } }
private void analyseSchema() { try { final String jdbcUrl = m_options.m_jdbcUrl; final String schemaName = m_options.m_schemaName; System.out.printf("Analysing schema [%s] at [%s]...%n", schemaName, jdbcUrl); final long msecStart = System.currentTimeMillis(); // Create a database connection final DataSource dataSource = new DatabaseConnectionOptions(jdbcUrl); final Connection connection = dataSource.getConnection(m_options.m_username, m_options.m_password); // Create the options final SchemaCrawlerOptions options = new SchemaCrawlerOptions(); // Set what details are required in the schema - this affects the // time taken to crawl the schema options.setSchemaInfoLevel(SchemaInfoLevel.standard()); options.setSchemaInclusionRule(new RegularExpressionInclusionRule(schemaName)); final InclusionRule tableRule = new RegularExpressionExclusionRule(".+[\\$/].+"); final InclusionRule procRule = s -> { // System.out.println (s); final String[] a = new String[] { ".*" + "\\$" + ".*", ".*" + "_RETRIGGER_EXPRESSION" + ".*", ".*" + "BITOR" + ".*", ".*" + "CENTRED" + ".*", ".*" + "COMPLEMENT" + ".*", ".*" + "D2" + ".*", ".*" + "DDMMYYYYHHMMMSS" + ".*", ".*" + "DOW" + ".*", ".*" + "DSM" + ".*", ".*" + "ENABLE" + ".*", ".*" + "F2" + ".*", ".*" + "GET_LAST_CHANGE_NUMBER" + ".*", ".*" + "GROOM" + ".*", ".*" + "IS_EVEN" + ".*", ".*" + "IS_ODD" + ".*", ".*" + "LEFT_PADDED" + ".*", ".*" + "LOGICAL_" + ".*", ".*" + "M2" + ".*", ".*" + "MD5" + ".*", ".*" + "MERGE_OSS_TRIP_LOCATION" + ".*", ".*" + "MODF" + ".*", ".*" + "NEXT_TIME_ABSOLUTE" + ".*", ".*" + "NEXT_TIME_RELATIVE" + ".*", ".*" + "RESERVE_FARM_STATUS" + ".*", ".*" + "RIGHT_PADDED" + ".*", ".*" + "S2" + ".*", ".*" + "SCHEDULE_JOB" + ".*", ".*" + "SNDF" + ".*", ".*" + "SSM" + ".*", ".*" + "XOR" + ".*", ".*" + "XSD_TIMESTAMP" + ".*", ".*" + "//dummy//" + ".*" }; return !HcUtil.containsWildcard(a, s); }; options.setTableInclusionRule(tableRule); options.setRoutineInclusionRule(procRule); // new ExcludeAll () // Get the schema definition final Catalog catalog = SchemaCrawlerUtility.getCatalog(connection, options); final long msecDuration = System.currentTimeMillis() - msecStart; final Collection<Schema> schemas = catalog.getSchemas(); ThreadContext.assertFault( schemas.size() == 1, "Expected one schema, got %s [%s]", schemas.size(), schemas.size()); final Schema[] a = schemas.toArray(new Schema[schemas.size()]); final Schema schema = a[0]; final DatabaseInfo di = catalog.getDatabaseInfo(); System.out.printf( " catalog[%s] database[%s] version[%s]%n", schema.getFullName(), di.getProductName(), di.getProductVersion()); System.out.printf(" %s tables%n", catalog.getTables(schema).size()); System.out.printf(" %s stored procedures%n", catalog.getRoutines(schema).size()); System.out.printf("...analysed in %s seconds%n", msecDuration / 1_000L); outputSchemaXml(catalog, schema); System.out.printf("Schema file [%s] created.%n", m_options.m_outputFilename); } catch (final SchemaCrawlerException | SQLException e) { // Propagate exception as unchecked fault up to the fault barrier. ThreadContext.throwFault(e); } }
void retrieveTables( final Schema schema, final String tableNamePattern, final Collection<String> tableTypes, final InclusionRule tableInclusionRule) throws SQLException { requireNonNull(schema, "No schema provided"); final InclusionRuleFilter<Table> tableFilter = new InclusionRuleFilter<>(tableInclusionRule, false); if (tableFilter.isExcludeAll()) { LOGGER.log(Level.INFO, "Not retrieving tables, since this was not requested"); return; } final Optional<Schema> schemaOptional = catalog.lookupSchema(schema.getFullName()); if (!schemaOptional.isPresent()) { LOGGER.log( Level.INFO, new StringFormat( "Cannot locate schema, so not retrieving tables for schema: %s", schema)); return; } LOGGER.log(Level.INFO, new StringFormat("Retrieving tables for schema: %s", schema)); final TableTypes supportedTableTypes = getRetrieverConnection().getTableTypes(); final String[] filteredTableTypes = supportedTableTypes.filterUnknown(tableTypes); LOGGER.log( Level.FINER, new StringFormat( "Retrieving table types: %s", filteredTableTypes == null ? "<<all>>" : Arrays.asList(filteredTableTypes))); final String catalogName = schema.getCatalogName(); final String schemaName = schema.getName(); try (final MetadataResultSet results = new MetadataResultSet( getMetaData() .getTables( unquotedName(catalogName), unquotedName(schemaName), tableNamePattern, filteredTableTypes)); ) { results.setDescription("retrieveTables"); while (results.next()) { // "TABLE_CAT", "TABLE_SCHEM" final String tableName = quotedName(results.getString("TABLE_NAME")); LOGGER.log(Level.FINE, String.format("Retrieving table: %s.%s", schema, tableName)); final String tableTypeString = results.getString("TABLE_TYPE"); final String remarks = results.getString("REMARKS"); final TableType tableType = supportedTableTypes.lookupTableType(tableTypeString).orElse(TableType.UNKNOWN); if (tableType.equals(TableType.UNKNOWN)) { LOGGER.log( Level.FINE, new StringFormat( "Unknown table type, %s, for %s.%s", tableTypeString, schema, tableName)); } final MutableTable table; if (tableType.isView()) { table = new MutableView(schema, tableName); } else { table = new MutableTable(schema, tableName); } if (tableFilter.test(table)) { table.setTableType(tableType); table.setRemarks(remarks); catalog.addTable(table); } } } }