/*
* SchemaCrawler
* Copyright (c) 2000-2013, Sualeh Fatehi.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package schemacrawler.test;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
import java.io.File;
import java.io.PrintWriter;
import java.util.Arrays;
import java.util.List;
import java.util.Map.Entry;
import java.util.Random;
import java.util.SortedMap;
import java.util.TreeMap;
import org.junit.Test;
import schemacrawler.schema.CheckConstraint;
import schemacrawler.schema.Column;
import schemacrawler.schema.Database;
import schemacrawler.schema.EventManipulationType;
import schemacrawler.schema.Routine;
import schemacrawler.schema.Schema;
import schemacrawler.schema.SchemaReference;
import schemacrawler.schema.Synonym;
import schemacrawler.schema.Table;
import schemacrawler.schema.TableRelationshipType;
import schemacrawler.schema.Trigger;
import schemacrawler.schema.View;
import schemacrawler.schemacrawler.Config;
import schemacrawler.schemacrawler.InclusionRule;
import schemacrawler.schemacrawler.InformationSchemaViews;
import schemacrawler.schemacrawler.SchemaCrawlerOptions;
import schemacrawler.schemacrawler.SchemaInfoLevel;
import schemacrawler.test.utility.BaseDatabaseTest;
import schemacrawler.test.utility.TestUtility;
import schemacrawler.test.utility.TestWriter;
import schemacrawler.utility.NamedObjectSort;
import sf.util.Utility;
public class SchemaCrawlerTest
extends BaseDatabaseTest
{
private static final String METADATA_OUTPUT = "metadata/";
@Test
public void checkConstraints()
throws Exception
{
final TestWriter out = new TestWriter();
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 (final Schema schema: schemas)
{
out.println("schema: " + schema.getFullName());
final Table[] tables = database.getTables(schema).toArray(new Table[0]);
for (final Table table: tables)
{
out.println(" table: " + table.getFullName());
final CheckConstraint[] checkConstraints = table.getCheckConstraints()
.toArray(new CheckConstraint[0]);
for (final CheckConstraint checkConstraint: checkConstraints)
{
out.println(" constraint: " + checkConstraint.getName());
}
}
}
out.close();
out.assertEquals(TestUtility.currentMethodFullName());
}
@Test
public void columnLookup()
throws Exception
{
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
final Database database = getDatabase(schemaCrawlerOptions);
assertNotNull(database);
final Schema schema = database.getSchema("PUBLIC.BOOKS");
assertNotNull(schema);
final Table table = database.getTable(schema, "AUTHORS");
assertNotNull(table);
assertNull(table.getColumn(null));
assertNull(table.getColumn(""));
assertNull(table.getColumn("NO_COLUMN"));
assertNotNull(table.getColumn("ID"));
}
@Test
public void counts()
throws Exception
{
final TestWriter out = new TestWriter();
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 (final Schema schema: schemas)
{
out.println("schema: " + schema.getFullName());
final Table[] tables = database.getTables(schema).toArray(new Table[0]);
Arrays.sort(tables, NamedObjectSort.alphabetical);
for (final Table table: tables)
{
out.println(" table: " + table.getFullName());
out.println(" # columns: " + table.getColumns().size());
out.println(" # constraints: " + table.getCheckConstraints().size());
out.println(" # indices: " + table.getIndices().size());
out.println(" # foreign keys: " + table.getForeignKeys().size());
out.println(" # imported foreign keys: "
+ table.getExportedForeignKeys().size());
out.println(" # exported: " + table.getImportedForeignKeys().size());
out.println(" # privileges: " + table.getPrivileges().size());
}
}
out.close();
out.assertEquals(TestUtility.currentMethodFullName());
}
@Test
public void relatedTables()
throws Exception
{
final TestWriter out = new TestWriter();
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.standard());
schemaCrawlerOptions
.setSchemaInclusionRule(new InclusionRule(InclusionRule.ALL,
".*\\.FOR_LINT"));
final Database database = getDatabase(schemaCrawlerOptions);
final Table[] tables = database.getTables().toArray(new Table[0]);
assertEquals("Table count does not match", 8, tables.length);
Arrays.sort(tables, NamedObjectSort.alphabetical);
for (final Table table: tables)
{
out.println(" table: " + table.getFullName());
out.println(" # columns: " + table.getColumns().size());
out.println(" # child tables: "
+ table.getRelatedTables(TableRelationshipType.child));
out.println(" # parent tables: "
+ table.getRelatedTables(TableRelationshipType.parent));
}
out.close();
out.assertEquals(TestUtility.currentMethodFullName());
}
@Test
public void relatedTablesWithTableRestriction()
throws Exception
{
final TestWriter out = new TestWriter();
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.standard());
schemaCrawlerOptions
.setTableInclusionRule(new InclusionRule(".*\\.AUTHORS",
InclusionRule.NONE));
final Database database = getDatabase(schemaCrawlerOptions);
final Table[] tables = database.getTables().toArray(new Table[0]);
assertEquals("Table count does not match", 1, tables.length);
Arrays.sort(tables, NamedObjectSort.alphabetical);
for (final Table table: tables)
{
out.println(" table: " + table.getFullName());
out.println(" # columns: " + table.getColumns().size());
out.println(" # child tables: "
+ table.getRelatedTables(TableRelationshipType.child));
out.println(" # parent tables: "
+ table.getRelatedTables(TableRelationshipType.parent));
}
out.close();
out.assertEquals(TestUtility.currentMethodFullName());
}
@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));
}
@Test
public void synonyms()
throws Exception
{
final TestWriter out = new TestWriter();
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 (final Synonym synonym: synonyms)
{
assertNotNull(synonym);
out.println("synonym: " + synonym.getName());
out.println(" class: "
+ synonym.getReferencedObject().getClass().getSimpleName());
}
out.close();
out.assertEquals(TestUtility.currentMethodFullName());
}
@Test
public void tables()
throws Exception
{
final String referenceFile = "tables.txt";
final File testOutputFile = File.createTempFile("schemacrawler."
+ referenceFile + ".",
".test");
testOutputFile.delete();
try (final PrintWriter writer = new PrintWriter(testOutputFile, "UTF-8");)
{
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<>(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]);
Arrays.sort(columns);
for (final Column column: columns)
{
writer.println(String.format(" o--> %s [%s]",
column.getFullName(),
column.getColumnDataType()));
final SortedMap<String, Object> columnAttributes = new TreeMap<>(column
.getAttributes());
for (final Entry<String, Object> columnAttribute: columnAttributes
.entrySet())
{
writer.println(String.format(" ~ %s=%s",
columnAttribute.getKey(),
columnAttribute.getValue()));
}
}
}
}
}
final List<String> failures = TestUtility
.compareOutput(METADATA_OUTPUT + referenceFile, testOutputFile);
if (failures.size() > 0)
{
fail(failures.toString());
}
}
@Test
public void tablesSort()
throws Exception
{
final String[] tableNames = {
"AUTHORS",
"BOOKS",
"\"Global Counts\"",
"PUBLISHERS",
"BOOKAUTHORS",
"AUTHORSLIST",
};
final Random rnd = new Random();
final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions();
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);
final Schema schema = schemas[0];
for (int i = 0; i < tableNames.length; i++)
{
final String tableName1 = tableNames[i];
for (int j = 0; j < tableNames.length; j++)
{
final String tableName2 = tableNames[j];
assertEquals(tableName1 + " <--> " + tableName2,
Math.signum(database.getTable(schema, tableName1)
.compareTo(database.getTable(schema, tableName2))),
Math.signum(i - j),
1e-100);
}
}
final Table[] tables = database.getTables(schema).toArray(new Table[0]);
for (int i = 0; i < 10; i++)
{
for (int tableIdx = 0; tableIdx < tables.length; tableIdx++)
{
final Table table = tables[tableIdx];
assertEquals("Table name does not match in iteration " + i,
tableNames[tableIdx],
table.getName());
}
// Shuffle array, and sort it again
for (int k = tables.length; k > 1; k--)
{
final int i1 = k - 1;
final int i2 = rnd.nextInt(k);
final Table tmp = tables[i1];
tables[i1] = tables[i2];
tables[i2] = tmp;
}
Arrays.sort(tables);
}
}
@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 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(""));
}
}