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