@Test
public void synonyms() throws Exception {
final String[] classes = {
"MutableTable", "MutableTable", "MutableTable", "", "", "",
};
final String[] synonymNames = {
"AUTHORS", "BOOKAUTHORS", "BOOKS", "\"Global Counts\"", "No_Columns", "PUBLICATIONS",
};
final InformationSchemaViews informationSchemaViews = new InformationSchemaViews();
informationSchemaViews.setSynonymSql(
"SELECT LIMIT 1 3 \n"
+ " TABLE_CATALOG AS SYNONYM_CATALOG, \n"
+ " TABLE_SCHEMA AS SYNONYM_SCHEMA, \n"
+ " TABLE_NAME AS SYNONYM_NAME, \n"
+ " TABLE_CATALOG AS REFERENCED_OBJECT_CATALOG, \n"
+ " TABLE_SCHEMA AS REFERENCED_OBJECT_SCHEMA, \n"
+ " TABLE_NAME AS REFERENCED_OBJECT_NAME \n"
+ "FROM \n"
+ " INFORMATION_SCHEMA.TABLES \n"
+ "WHERE \n"
+ " TABLE_SCHEMA = 'BOOKS' \n"
+ "UNION \n"
+ "SELECT LIMIT 1 3 \n"
+ " 'PUBLIC' AS SYNONYM_CATALOG, \n"
+ " 'BOOKS' AS SYNONYM_SCHEMA, \n"
+ " TABLE_NAME AS SYNONYM_NAME, \n"
+ " TABLE_CATALOG AS REFERENCED_OBJECT_CATALOG, \n"
+ " TABLE_SCHEMA AS REFERENCED_OBJECT_SCHEMA, \n"
+ " TABLE_NAME + '1' AS REFERENCED_OBJECT_NAME \n"
+ "FROM \n"
+ " INFORMATION_SCHEMA.TABLES \n"
+ "WHERE \n"
+ " TABLE_SCHEMA != 'BOOKS' ");
final SchemaInfoLevel minimum = SchemaInfoLevel.minimum();
minimum.setRetrieveSynonymInformation(true);
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setSchemaInfoLevel(minimum);
schemaCrawlerOptions.setInformationSchemaViews(informationSchemaViews);
schemaCrawlerOptions.setSynonymInclusionRule(InclusionRule.INCLUDE_ALL);
final Database database = getDatabase(schemaCrawlerOptions);
final Schema schema = database.getSchema("PUBLIC.BOOKS");
assertNotNull("BOOKS Schema not found", schema);
final Synonym[] synonyms = database.getSynonyms(schema).toArray(new Synonym[0]);
assertEquals("Synonym count does not match", 6, synonyms.length);
for (int i = 0; i < synonyms.length; i++) {
final Synonym synonym = synonyms[i];
assertNotNull(synonym);
assertEquals(
"Wrong referenced object class - " + synonym.getReferencedObject().getClass(),
classes[i],
synonym.getReferencedObject().getClass().getSimpleName());
assertEquals("", synonymNames[i], synonym.getName());
}
}
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 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);
}
}
}
@Test
public void triggers() throws Exception {
// Set up information schema properties
final InformationSchemaViews informationSchemaViews = new InformationSchemaViews();
informationSchemaViews.setTriggersSql("SELECT * FROM INFORMATION_SCHEMA.TRIGGERS");
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setInformationSchemaViews(informationSchemaViews);
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
final Database database = getDatabase(schemaCrawlerOptions);
final Schema schema = new SchemaReference("PUBLIC", "BOOKS");
final Table[] tables = database.getTables(schema).toArray(new Table[0]);
boolean foundTrigger = false;
for (final Table table : tables) {
for (final Trigger trigger : table.getTriggers()) {
foundTrigger = true;
assertEquals(
"Triggers full name does not match",
"PUBLIC.BOOKS.AUTHORS.TRG_AUTHORS",
trigger.getFullName());
assertEquals(
"Trigger EventManipulationType does not match",
EventManipulationType.delete,
trigger.getEventManipulationType());
}
}
assertTrue("No triggers found", foundTrigger);
}
public static void main(final String[] args) throws Exception {
// Create a database connection
final DataSource dataSource =
new DatabaseConnectionOptions(
"org.hsqldb.jdbcDriver", "jdbc:hsqldb:hsql://localhost:9001/schemacrawler");
final Connection connection = dataSource.getConnection("sa", "");
// 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.setRoutineInclusionRule(new InclusionRule(InclusionRule.NONE, InclusionRule.ALL));
options.setSchemaInclusionRule(new InclusionRule("PUBLIC.BOOKS", InclusionRule.NONE));
// Get the schema definition
final Database database = SchemaCrawlerUtility.getDatabase(connection, options);
for (final Schema schema : database.getSchemas()) {
System.out.println(schema);
for (final Table table : database.getTables(schema)) {
System.out.print("o--> " + table);
if (table instanceof View) {
System.out.println(" (VIEW)");
} else {
System.out.println();
}
for (final Column column : table.getColumns()) {
System.out.println(" o--> " + column + " (" + column.getColumnDataType() + ")");
}
}
}
}
private static void crawlSequences(
final MutableCatalog catalog,
final RetrieverConnection retrieverConnection,
final SchemaCrawlerOptions options)
throws SchemaCrawlerException {
final StopWatch stopWatch = new StopWatch("crawlSequences");
final SchemaInfoLevel infoLevel = options.getSchemaInfoLevel();
final boolean retrieveSequences = infoLevel.isRetrieveSequenceInformation();
if (!retrieveSequences) {
LOGGER.log(Level.INFO, "Not retrieving sequences, since this was not requested");
return;
}
LOGGER.log(Level.INFO, "Crawling sequences");
final SequenceRetriever retrieverExtra;
try {
retrieverExtra = new SequenceRetriever(retrieverConnection, catalog);
stopWatch.time(
"retrieveSequenceInformation",
() -> {
retrieverExtra.retrieveSequenceInformation(options.getSequenceInclusionRule());
return null;
});
stopWatch.time(
"sortAndFilterSequences",
() -> {
((Reducible) catalog).reduce(Sequence.class, new SequencesReducer(options));
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 sequence information", e);
}
}
}
@Test
public void tables() throws Exception {
final String referenceFile = "tables.txt";
final File testOutputFile =
File.createTempFile("schemacrawler." + referenceFile + ".", ".test");
testOutputFile.delete();
final PrintWriter writer = new PrintWriter(new BufferedWriter(new FileWriter(testOutputFile)));
final Config config = Config.loadResource("/hsqldb.INFORMATION_SCHEMA.config.properties");
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions(config);
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
schemaCrawlerOptions.setSchemaInclusionRule(
new InclusionRule(InclusionRule.ALL, ".*\\.FOR_LINT"));
final Database database = getDatabase(schemaCrawlerOptions);
final Schema[] schemas = database.getSchemas().toArray(new Schema[0]);
assertEquals("Schema count does not match", 5, schemas.length);
for (final Schema schema : schemas) {
final Table[] tables = database.getTables(schema).toArray(new Table[0]);
Arrays.sort(tables, NamedObjectSort.alphabetical);
for (final Table table : tables) {
writer.println(String.format("o--> %s [%s]", table.getFullName(), table.getTableType()));
final SortedMap<String, Object> tableAttributes =
new TreeMap<String, Object>(table.getAttributes());
for (final Entry<String, Object> tableAttribute : tableAttributes.entrySet()) {
writer.println(
String.format(" ~ %s=%s", tableAttribute.getKey(), tableAttribute.getValue()));
}
final Column[] columns = table.getColumns().toArray(new Column[0]);
for (final Column column : columns) {
writer.println(
String.format(" o--> %s [%s]", column.getFullName(), column.getColumnDataType()));
final SortedMap<String, Object> columnAttributes =
new TreeMap<String, Object>(column.getAttributes());
for (final Entry<String, Object> columnAttribute : columnAttributes.entrySet()) {
writer.println(
String.format(
" ~ %s=%s", columnAttribute.getKey(), columnAttribute.getValue()));
}
}
}
}
writer.flush();
writer.close();
final List<String> failures =
TestUtility.compareOutput(METADATA_OUTPUT + referenceFile, testOutputFile);
if (failures.size() > 0) {
fail(failures.toString());
}
}
private SchemaCrawlerOptions createOptions(
final String catalogInclusion, final String schemaInclusion) {
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
if (catalogInclusion != null) {
schemaCrawlerOptions.setCatalogInclusionRule(
new InclusionRule(catalogInclusion, InclusionRule.NONE));
}
if (schemaInclusion != null) {
schemaCrawlerOptions.setSchemaInclusionRule(
new InclusionRule(schemaInclusion, InclusionRule.NONE));
}
return schemaCrawlerOptions;
}
@Test
public void viewDefinitions() throws Exception {
final InformationSchemaViews informationSchemaViews = new InformationSchemaViews();
informationSchemaViews.setViewsSql("SELECT * FROM INFORMATION_SCHEMA.VIEWS");
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setTableTypes("VIEW");
schemaCrawlerOptions.setInformationSchemaViews(informationSchemaViews);
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
final Database database = getDatabase(schemaCrawlerOptions);
final Schema schema = new SchemaReference("PUBLIC", "BOOKS");
final View view = (View) database.getTable(schema, "AUTHORSLIST");
assertNotNull("View not found", view);
assertNotNull("View definition not found", view.getDefinition());
assertFalse("View definition not found", view.getDefinition().trim().equals(""));
}
@Test
public void routineDefinitions() throws Exception {
final InformationSchemaViews informationSchemaViews = new InformationSchemaViews();
informationSchemaViews.setRoutinesSql("SELECT * FROM INFORMATION_SCHEMA.ROUTINES");
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setInformationSchemaViews(informationSchemaViews);
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
final Database database = getDatabase(schemaCrawlerOptions);
final Schema schema = new SchemaReference("PUBLIC", "BOOKS");
final Routine[] routines = database.getRoutines(schema).toArray(new Routine[0]);
assertEquals("Wrong number of routines", 4, routines.length);
for (final Routine routine : routines) {
assertFalse(
"Routine definition not found, for " + routine, Utility.isBlank(routine.getDefinition()));
}
}
@Test
public void schemaEquals() throws Exception {
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.detailed());
final Database database = getDatabase(schemaCrawlerOptions);
final Schema schema1 = new SchemaReference("PUBLIC", "BOOKS");
assertTrue("Could not find any tables", database.getTables(schema1).size() > 0);
assertEquals("Wrong number of routines", 4, database.getRoutines(schema1).size());
final Schema schema2 = new SchemaReference("PUBLIC", "BOOKS");
assertEquals("Schema not not match", schema1, schema2);
assertEquals("Tables do not match", database.getTables(schema1), database.getTables(schema2));
assertEquals(
"Routines do not match", database.getRoutines(schema1), database.getRoutines(schema2));
// Try negative test
final Table table1 = database.getTables(schema1).toArray(new Table[0])[0];
final Table table2 = database.getTables(schema1).toArray(new Table[0])[1];
assertFalse("Tables should not be equal", table1.equals(table2));
}
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 static void crawlDatabaseInfo(
final MutableCatalog catalog,
final RetrieverConnection retrieverConnection,
final SchemaCrawlerOptions options)
throws SchemaCrawlerException {
try {
final StopWatch stopWatch = new StopWatch("crawlDatabaseInfo");
final SchemaInfoLevel infoLevel = options.getSchemaInfoLevel();
final DatabaseInfoRetriever retriever =
new DatabaseInfoRetriever(retrieverConnection, catalog);
LOGGER.log(Level.INFO, "Crawling SchemaCrawler information");
LOGGER.log(Level.INFO, "Retrieving database information");
stopWatch.time(
"retrieveDatabaseInfo",
() -> {
retriever.retrieveDatabaseInfo();
return null;
});
stopWatch.time(
"retrieveAdditionalDatabaseInfo",
() -> {
if (infoLevel.isRetrieveAdditionalDatabaseInfo()) {
retriever.retrieveAdditionalDatabaseInfo();
} else {
LOGGER.log(
Level.INFO,
"Not retrieving additional database information, since this was not requested");
}
return null;
});
LOGGER.log(Level.INFO, "Retrieving JDBC driver information");
stopWatch.time(
"retrieveJdbcDriverInfo",
() -> {
retriever.retrieveJdbcDriverInfo();
return null;
});
stopWatch.time(
"retrieveAdditionalJdbcDriverInfo",
() -> {
if (infoLevel.isRetrieveAdditionalJdbcDriverInfo()) {
retriever.retrieveAdditionalJdbcDriverInfo();
} else {
LOGGER.log(
Level.INFO,
"Not retrieving additional JDBC driver information, since this was not requested");
}
return null;
});
LOGGER.log(Level.INFO, "Retrieving SchemaCrawler crawl information");
stopWatch.time(
"retrieveCrawlHeaderInfo",
() -> {
retriever.retrieveCrawlHeaderInfo(options.getTitle());
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 database 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);
}
}
@Test
public void counts() throws Exception {
final int[] tableCounts = {
6, 0, 0, 2, 0,
};
final int[][] tableColumnCounts = {
{9, 3, 3, 6, 1, 2}, {}, {}, {4, 5}, {},
};
final int[][] checkConstraints = {
{4, 0, 2, 3, 0, 1, 0}, {}, {}, {4, 2}, {},
};
final int[][] indexCounts = {
{
3, 0, 3, 1, 0, 1,
},
{},
{},
{1, 1},
{},
};
final int[][] fkCounts = {
{
1, 0, 2, 1, 0, 0,
},
{},
{},
{1, 1},
{},
};
final int[][] exportedFkCounts = {
{
1, 0, 0, 1, 0, 0,
},
{},
{},
{1, 0},
{},
};
final int[][] importedFkCounts = {
{
0, 0, 2, 0, 0, 0,
},
{},
{},
{0, 1},
{},
};
final int[][] tablePrivilegesCounts = {
{6, 6, 6, 6, 6, 6, 6},
{},
{},
{
6, 6,
},
{},
};
final InformationSchemaViews informationSchemaViews = new InformationSchemaViews();
informationSchemaViews.setTableConstraintsSql(
"SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS");
informationSchemaViews.setCheckConstraintsSql(
"SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS");
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
schemaCrawlerOptions.setInformationSchemaViews(informationSchemaViews);
schemaCrawlerOptions.setSchemaInclusionRule(
new InclusionRule(InclusionRule.ALL, ".*\\.FOR_LINT"));
final Database database = getDatabase(schemaCrawlerOptions);
final Schema[] schemas = database.getSchemas().toArray(new Schema[0]);
assertEquals("Schema count does not match", 5, schemas.length);
for (int schemaIdx = 0; schemaIdx < schemas.length; schemaIdx++) {
final Schema schema = schemas[schemaIdx];
final Table[] tables = database.getTables(schema).toArray(new Table[0]);
assertEquals("Table count does not match", tableCounts[schemaIdx], tables.length);
Arrays.sort(tables, NamedObjectSort.alphabetical);
for (int tableIdx = 0; tableIdx < tables.length; tableIdx++) {
final Table table = tables[tableIdx];
assertEquals(
String.format(
"Table [%d][%d] %s columns count does not match",
schemaIdx, tableIdx, table.getFullName()),
tableColumnCounts[schemaIdx][tableIdx],
table.getColumns().size());
assertEquals(
String.format(
"Table [%d][%d] %s check constraints count does not match",
schemaIdx, tableIdx, table.getFullName()),
checkConstraints[schemaIdx][tableIdx],
table.getCheckConstraints().size());
assertEquals(
String.format(
"Table [%d][%d] %s index count does not match",
schemaIdx, tableIdx, table.getFullName()),
indexCounts[schemaIdx][tableIdx],
table.getIndices().size());
assertEquals(
String.format(
"Table [%d][%d] %s foreign key count does not match",
schemaIdx, tableIdx, table.getFullName()),
fkCounts[schemaIdx][tableIdx],
table.getForeignKeys().size());
assertEquals(
String.format(
"Table [%d][%d] %s exported foreign key count does not match",
schemaIdx, tableIdx, table.getFullName()),
exportedFkCounts[schemaIdx][tableIdx],
table.getExportedForeignKeys().size());
assertEquals(
String.format(
"Table [%d][%d] %s imported foreign key count does not match",
schemaIdx, tableIdx, table.getFullName()),
importedFkCounts[schemaIdx][tableIdx],
table.getImportedForeignKeys().size());
assertEquals(
String.format(
"Table [%d][%d] %s privileges count does not match",
schemaIdx, tableIdx, table.getFullName()),
tablePrivilegesCounts[schemaIdx][tableIdx],
table.getPrivileges().size());
}
}
}
@Test
public void compareCompositeOutput() throws Exception {
final String queryCommand1 = "all_tables";
final Config queriesConfig = new Config();
queriesConfig.put(queryCommand1, "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_TABLES");
final String queryCommand2 = "dump_tables";
queriesConfig.put(
queryCommand2, "SELECT ${orderbycolumns} FROM ${table} ORDER BY ${orderbycolumns}");
final String[] commands =
new String[] {
SchemaTextDetailType.details + "," + Operation.count + "," + Operation.dump,
SchemaTextDetailType.list + "," + Operation.count,
queryCommand1
+ ","
+ queryCommand2
+ ","
+ Operation.count
+ ","
+ SchemaTextDetailType.list,
};
final List<String> failures = new ArrayList<String>();
for (final OutputFormat outputFormat : OutputFormat.values()) {
for (final String command : commands) {
final String referenceFile = command + "." + outputFormat.name();
final File testOutputFile =
File.createTempFile("schemacrawler." + referenceFile + ".", ".test");
testOutputFile.delete();
final OutputOptions outputOptions = new OutputOptions(outputFormat.name(), testOutputFile);
outputOptions.setNoInfo(false);
outputOptions.setNoHeader(false);
outputOptions.setNoFooter(false);
final Config config =
Config.load(
SchemaCrawlerOutputTest.class.getResourceAsStream(
"/hsqldb.INFORMATION_SCHEMA.config.properties"));
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions(config);
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
final DatabaseConnectionOptions connectionOptions =
testUtility.getDatabaseConnectionOptions();
final SchemaCrawlerExecutable executable = new SchemaCrawlerExecutable(command);
executable.setSchemaCrawlerOptions(schemaCrawlerOptions);
executable.setOutputOptions(outputOptions);
executable.setAdditionalConfiguration(queriesConfig);
executable.execute(connectionOptions.getConnection());
failures.addAll(
TestUtility.compareOutput(
COMPOSITE_OUTPUT + referenceFile, testOutputFile, outputFormat));
}
}
if (failures.size() > 0) {
fail(failures.toString());
}
}
@Test
public void checkConstraints() throws Exception {
final int[] tableCounts = {6, 5, 0, 0, 2, 0};
final int[][] checkConstraintCounts = {
{4, 0, 2, 3, 0, 1, 0},
{
3, 0, 0, 3, 6,
},
{},
{},
{4, 2},
{}
};
final String[][][] checkConstraintNames = {
{
{"CHECK_UPPERCASE_STATE", "SYS_CT_10068", "SYS_CT_10069", "SYS_CT_10070"},
{},
{"SYS_CT_10083", "SYS_CT_10084"},
{"SYS_CT_10076", "SYS_CT_10077", "SYS_CT_10078"},
{},
{"SYS_CT_10065"},
{}
},
{
{"SYS_CT_10144", "SYS_CT_10145", "SYS_CT_10146"},
{},
{},
{
"SYS_CT_10133", "SYS_CT_10134", "SYS_CT_10135",
},
{
"CHECK_UPPERCASE_STATE",
"SYS_CT_10121",
"SYS_CT_10122",
"SYS_CT_10123",
"SYS_CT_10124",
"SYS_CT_10125",
},
},
{},
{},
{
{"SYS_CT_10105", "SYS_CT_10106", "SYS_CT_10107", "SYS_CT_10108"},
{"SYS_CT_10114", "SYS_CT_10115"}
},
{}
};
final InformationSchemaViews informationSchemaViews = new InformationSchemaViews();
informationSchemaViews.setTableConstraintsSql(
"SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS");
informationSchemaViews.setCheckConstraintsSql(
"SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS");
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
schemaCrawlerOptions.setInformationSchemaViews(informationSchemaViews);
final Database database = getDatabase(schemaCrawlerOptions);
final Schema[] schemas = database.getSchemas().toArray(new Schema[0]);
assertEquals("Schema count does not match", 6, schemas.length);
for (int schemaIdx = 0; schemaIdx < schemas.length; schemaIdx++) {
final Schema schema = schemas[schemaIdx];
final Table[] tables = database.getTables(schema).toArray(new Table[0]);
assertEquals("Table count does not match", tableCounts[schemaIdx], tables.length);
for (int tableIdx = 0; tableIdx < tables.length; tableIdx++) {
final Table table = tables[tableIdx];
final CheckConstraint[] checkConstraints =
table.getCheckConstraints().toArray(new CheckConstraint[0]);
assertEquals(
String.format(
"Table [%d][%d] %s check constraints count does not match",
schemaIdx, tableIdx, table.getFullName()),
checkConstraintCounts[schemaIdx][tableIdx],
checkConstraints.length);
for (int i = 0; i < checkConstraints.length; i++) {
final CheckConstraint checkConstraint = checkConstraints[i];
assertEquals(
"Check constraint name does not match for table " + table,
checkConstraintNames[schemaIdx][tableIdx][i],
checkConstraint.getName());
}
}
}
}