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