private void executeAndCheckForOutputFile(
final Executable<?> executable, final String outputFilename) throws Exception {
executable.execute(testUtility.getDataSource());
final File outputFile = new File(outputFilename);
assertTrue(outputFile.exists());
assertTrue(outputFile.length() > 0);
if (!outputFile.delete()) {
fail("Cannot delete output file");
}
}
@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 columns() throws SQLException {
final String[] columnNames = {
"PUBLIC.CUSTOMER.FIRSTNAME", "PUBLIC.CUSTOMER.LASTNAME", "ADDRESS", "",
};
final String[] columnDataTypes = {
"VARCHAR", "VARCHAR", "VARCHAR", "DOUBLE",
};
final String sql =
"SELECT "
+ " CUSTOMER.FIRSTNAME, "
+ " CUSTOMER.LASTNAME, "
+ " CUSTOMER.STREET + ', ' + CUSTOMER.CITY AS ADDRESS, "
+ " SUM(INVOICE.TOTAL) "
+ "FROM "
+ " CUSTOMER "
+ " INNER JOIN INVOICE "
+ " ON INVOICE.CUSTOMERID = CUSTOMER.ID "
+ "GROUP BY "
+ " CUSTOMER.FIRSTNAME, "
+ " CUSTOMER.LASTNAME, "
+ " CUSTOMER.STREET, "
+ " CUSTOMER.CITY "
+ "ORDER BY "
+ " SUM(INVOICE.TOTAL) DESC";
final Connection connection = testUtility.getDataSource().getConnection();
final Statement statement = connection.createStatement();
final ResultSet resultSet = statement.executeQuery(sql);
final ResultsColumns resultColumns = DatabaseSchemaCrawler.getResultColumns(resultSet);
connection.close();
assertNotNull("Could not obtain result columns", resultColumns);
final ResultsColumn[] columns = resultColumns.getColumns();
assertEquals("Column count does not match", 4, columns.length);
for (int columnIdx = 0; columnIdx < columns.length; columnIdx++) {
final ResultsColumn column = columns[columnIdx];
LOGGER.log(Level.FINE, column.toString());
assertEquals("Column full name does not match", columnNames[columnIdx], column.getFullName());
assertEquals(
"Column type does not match",
columnDataTypes[columnIdx],
column.getType().getDatabaseSpecificTypeName());
assertEquals(
"Column JDBC type does not match",
columnDataTypes[columnIdx],
column.getType().getTypeName());
}
}
@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);
}
@BeforeClass
public static void beforeAllTests() throws ClassNotFoundException {
TestUtility.setApplicationLogLevel();
testUtility.createMemoryDatabase();
}
@AfterClass
public static void afterAllTests() throws ClassNotFoundException {
testUtility.shutdownDatabase();
}
@BeforeClass
public static void beforeAllTests() {
TestUtility.setApplicationLogLevel();
testUtility.createMemoryDatabase();
}
@AfterClass
public static void afterAllTests() {
testUtility.shutdownDatabase();
}
