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);
}
}
}
}
