@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);
      }
    }
  }
Beispiel #5
0
 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;
  }
Beispiel #7
0
  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 ());
    }
  }
Beispiel #8
0
  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();
  }