@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)); }
@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); }
@Test public void schemaCounts() throws Exception { final String[] dataSources = { "MicrosoftSQLServer", "MySQL", "Oracle", "PostgreSQL", "SQLite", }; final int[] catalogCounts = { 4, 4, 1, 1, 1, }; final int[][] schemaCounts = { { 5, 5, 5, 5, }, { 1, 1, 1, 1, }, { 14, }, { 5, }, { 1, }, }; final SchemaCrawlerOptions schemaCrawlerOptions = createOptions(".*", ".*"); final SchemaInfoLevel infoLevel = SchemaInfoLevel.minimum(); infoLevel.setRetrieveTables(false); infoLevel.setRetrieveProcedures(false); schemaCrawlerOptions.setSchemaInfoLevel(infoLevel); for (int i = 0; i < dataSources.length; i++) { final String dataSource = dataSources[i]; final Database database = retrieveDatabase(dataSource, schemaCrawlerOptions); final Catalog[] catalogs = database.getCatalogs(); assertEquals( "Incorrect number of catalogs for " + dataSource, catalogCounts[i], catalogs.length); for (int j = 0; j < catalogs.length; j++) { final Catalog catalog = catalogs[j]; final Schema[] schemas = catalog.getSchemas(); assertEquals( "Incorrect number of schemas for " + dataSource + " catalog #" + j, schemaCounts[i][j], schemas.length); } } }
/** * Retrieves a list of schemas from the database, for the table specified. * * @param tableSort TODO * @param table Catalog for which data is required. * @throws SQLException On a SQL exception */ void retrieveSchemas(final InclusionRule schemaInclusionRule) throws SQLException { final MetadataResultSet results = new MetadataResultSet(getRetrieverConnection().getMetaData().getSchemas()); try { final boolean supportsCatalogs = getRetrieverConnection().isSupportsCatalogs(); while (results.next()) { final String catalogName; if (supportsCatalogs) { catalogName = results.getString("TABLE_CATALOG"); } else { catalogName = null; } final String schemaName = results.getString("TABLE_SCHEM"); LOGGER.log(Level.FINER, String.format("Retrieving schema: %s.%s", catalogName, schemaName)); final MutableCatalog[] catalogs; final MutableCatalog catalog = database.getCatalog(catalogName); if (catalog != null) { catalogs = new MutableCatalog[] {catalog}; } else { final Catalog[] databaseCatalogs = database.getCatalogs(); catalogs = new MutableCatalog[databaseCatalogs.length]; for (int i = 0; i < databaseCatalogs.length; i++) { catalogs[i] = (MutableCatalog) databaseCatalogs[i]; } } for (final MutableCatalog currentCatalog : catalogs) { final MutableSchema schema = new MutableSchema(currentCatalog, schemaName); final String schemaFullName = schema.getFullName(); if (schemaInclusionRule.include(schemaFullName)) { currentCatalog.addSchema(schema); } } } } finally { results.close(); } for (final Catalog catalog : database.getCatalogs()) { if (catalog.getSchemas().length == 0) { final MutableCatalog mutableCatalog = (MutableCatalog) catalog; final MutableSchema schema = new MutableSchema(mutableCatalog, null); mutableCatalog.addSchema(schema); } } }
private void createStoredProcedureElements( final Document dom, final Catalog catalog, final Element eRoot) { final Collection<Routine> storedProcedures = catalog.getRoutines(); for (final Routine sp : storedProcedures) { if (m_options.m_schemaObjectFilterStrategy.passesFilter(sp)) { // For each StoredProcedure object create element and attach it to root. eRoot.appendChild(createStoredProcedureElement(dom, sp)); } // else System.out.println (storedProcedure.getSchemaName ()); } }
private Schema retrieveSchema( final String dataSourceName, final String catalogInclusion, final String schemaInclusion) throws Exception { final SchemaCrawlerOptions schemaCrawlerOptions = createOptions(catalogInclusion, schemaInclusion); final Database database = retrieveDatabase(dataSourceName, schemaCrawlerOptions); final Schema schema; final Catalog[] catalogs = database.getCatalogs(); if (catalogs != null && catalogs.length > 0) { final Catalog catalog = catalogs[0]; if (catalog == null) { throw new NullPointerException("No catalog found for " + dataSourceName); } final Schema[] schemas = catalog.getSchemas(); schema = schemas[0]; } else { schema = null; } return schema; }
private void createTableElements( final Document dom, final Catalog catalog, final Schema schema, final Element eRoot) { final Collection<Table> tables = catalog.getTables(schema); for (final Table table : tables) { // if (table.getName ().equals ("MessageNote")) // { // System.out.println ("Here"); // } if (m_options.m_schemaObjectFilterStrategy.passesFilter(table)) { final TableType tableType = table.getTableType(); final String tableNameUpperCase = tableType.getTableType().toUpperCase(); if (tableType.isView()) { eRoot.appendChild(createViewElement(dom, table)); } else if (tableNameUpperCase.contains("TABLE") || tableNameUpperCase.contains("TEMPORARY")) { // For each Table object create element and attach it to root. eRoot.appendChild(createTableElement(dom, table)); } } // else System.out.println (table.getSchemaName ()); } }
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); } }
public final void traverse() throws SchemaCrawlerException { final Collection<ColumnDataType> columnDataTypes = catalog.getColumnDataTypes(); final Collection<Table> tables = catalog.getTables(); final Collection<Routine> routines = catalog.getRoutines(); final Collection<Synonym> synonyms = catalog.getSynonyms(); final Collection<Sequence> sequences = catalog.getSequences(); handler.begin(); handler.handleHeaderStart(); handler.handle(catalog.getCrawlInfo()); handler.handleHeaderEnd(); if (!tables.isEmpty()) { handler.handleTablesStart(); final List<? extends Table> tablesList = new ArrayList<>(tables); Collections.sort(tablesList, tablesComparator); for (final Table table : tablesList) { handler.handle(table); } handler.handleTablesEnd(); } if (!routines.isEmpty()) { handler.handleRoutinesStart(); final List<? extends Routine> routinesList = new ArrayList<>(routines); Collections.sort(routinesList, routinesComparator); for (final Routine routine : routinesList) { handler.handle(routine); } handler.handleRoutinesEnd(); } if (!sequences.isEmpty()) { handler.handleSequencesStart(); for (final Sequence sequence : sequences) { handler.handle(sequence); } handler.handleSequencesEnd(); } if (!synonyms.isEmpty()) { handler.handleSynonymsStart(); for (final Synonym synonym : synonyms) { handler.handle(synonym); } handler.handleSynonymsEnd(); } if (!columnDataTypes.isEmpty()) { handler.handleColumnDataTypesStart(); for (final ColumnDataType columnDataType : columnDataTypes) { handler.handle(columnDataType); } handler.handleColumnDataTypesEnd(); } handler.handleInfoStart(); handler.handle(catalog.getSchemaCrawlerInfo()); handler.handle(catalog.getDatabaseInfo()); handler.handle(catalog.getJdbcDriverInfo()); handler.handleInfoEnd(); handler.end(); }