/**
* Looks up a column in the database. If the column and table are not found, they are created, and
* added to the schema. This is prevent foreign key relationships from having a null pointer.
*/
private Column lookupOrCreateColumn(
final String catalogName,
final String schemaName,
final String tableName,
final String columnName) {
Column column = null;
final SchemaReference schema = new SchemaReference(catalogName, schemaName);
final Optional<MutableTable> tableOptional = catalog.lookupTable(schema, tableName);
if (tableOptional.isPresent()) {
final Table table = tableOptional.get();
final Optional<? extends Column> columnOptional = table.lookupColumn(columnName);
if (columnOptional.isPresent()) {
column = columnOptional.get();
}
}
if (column == null) {
// Create the table and column, but do not add it to the schema
final Table table = new TablePartial(schema, tableName);
column = new ColumnPartial(table, columnName);
((TablePartial) table).addColumn(column);
LOGGER.log(
Level.FINER,
String.format(
"Creating column reference for a column that is referenced by a foreign key: %s",
column.getFullName()));
}
return column;
}
@Override
public boolean equals(final Object obj) {
if (this == obj) {
return true;
}
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
final WeakAssociation other = (WeakAssociation) obj;
if (foreignKeyColumn == null) {
if (other.foreignKeyColumn != null) {
return false;
}
} else if (!foreignKeyColumn.equals(other.foreignKeyColumn)) {
return false;
}
if (primaryKeyColumn == null) {
if (other.primaryKeyColumn != null) {
return false;
}
} else if (!primaryKeyColumn.equals(other.primaryKeyColumn)) {
return false;
}
return true;
}
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() + ")");
}
}
}
}
/**
* The lint that does the job
*
* @param table table
* @param connection connection
* @throws SchemaCrawlerException SchemaCrawlerException
*/
@Override
protected void lint(final Table table, final Connection connection)
throws SchemaCrawlerException {
try (Statement stmt = connection.createStatement()) {
String sql;
List<Column> columns = table.getColumns();
for (Column column : columns) {
if (LintUtils.isSqlTypeTextBased(
column.getColumnDataType().getJavaSqlType().getJavaSqlType())) {
sql = "select " + column.getName() + " from " + table.getName();
LOGGER.log(Level.INFO, "SQL : {0}", sql);
ResultSet rs = stmt.executeQuery(sql);
boolean found = false;
while (rs.next() && !found) {
String data = rs.getString(column.getName());
if (JSonUtils.isJsonContent(data)) {
LOGGER.log(
Level.INFO, "Adding lint as data is JSON but column type is not JSONB or JSON.");
addLint(table, getDescription(), column.getFullName());
found = true;
}
}
}
}
} catch (SQLException ex) {
LOGGER.severe(ex.getMessage());
}
}
/**
* {@inheritDoc}
*
* <p>Note: Since indexes are not always explicitly named in databases, the sorting routine orders
* the indexes by the names of the columns in the index.
*/
@Override
public int compareTo(final NamedObject obj) {
if (obj == null) {
return -1;
}
final Index other = (Index) obj;
int comparison = 0;
final Column[] thisColumns = getColumns();
final Column[] otherColumns = other.getColumns();
if (comparison == 0) {
comparison = thisColumns.length - otherColumns.length;
}
if (comparison == 0) {
for (int i = 0; i < thisColumns.length; i++) {
final Column thisColumn = thisColumns[i];
final Column otherColumn = otherColumns[i];
if (comparison == 0) {
comparison = thisColumn.compareTo(otherColumn);
} else {
break;
}
}
}
if (comparison == 0) {
comparison = super.compareTo(other);
}
return comparison;
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + (foreignKeyColumn == null ? 0 : foreignKeyColumn.hashCode());
result = prime * result + (primaryKeyColumn == null ? 0 : primaryKeyColumn.hashCode());
return result;
}
public int compareTo(final ColumnMap o) {
int compare = 0;
if (compare == 0) {
compare = primaryKeyColumn.compareTo(o.getPrimaryKeyColumn());
}
if (compare == 0) {
compare = foreignKeyColumn.compareTo(o.getForeignKeyColumn());
}
return compare;
}
private List<String> findColumnsWithQuotedNames(final Column[] columns) {
final List<String> columnsWithQuotedNames = new ArrayList<String>();
for (final Column column : columns) {
final String columnName = column.getName();
if (isQuotedName(columnName)) {
columnsWithQuotedNames.add(columnName);
}
}
return columnsWithQuotedNames;
}
@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 static Element createForeignKeyColumnElement(
final Document dom, final ForeignKeyColumnReference fkcm) {
final Element eForeignKey = dom.createElement("column");
final Column referencingColumn = fkcm.getForeignKeyColumn();
final Column referencedColumn = fkcm.getPrimaryKeyColumn();
eForeignKey.setAttribute("name", referencingColumn.getName());
eForeignKey.setAttribute("referencedtable", referencedColumn.getParent().getName());
eForeignKey.setAttribute("referencedcolumn", referencedColumn.getName());
return eForeignKey;
}
private Multimap<String, IncrementingColumn> findIncrementingColumns(final Column[] columns) {
if (columns == null || columns.length <= 1) {
return new Multimap<String, IncrementingColumn>();
}
final Pattern pattern = Pattern.compile("([^0-9]*)([0-9]+)");
final Map<String, Integer> incrementingColumnsMap = new HashMap<String, Integer>();
for (final Column column : columns) {
final String columnName = Utility.convertForComparison(column.getName());
incrementingColumnsMap.put(columnName, 1);
final Matcher matcher = pattern.matcher(columnName);
if (matcher.matches()) {
final String columnNameBase = matcher.group(1);
if (incrementingColumnsMap.containsKey(columnNameBase)) {
incrementingColumnsMap.put(
columnNameBase, incrementingColumnsMap.get(columnNameBase) + 1);
} else {
incrementingColumnsMap.put(columnNameBase, 1);
}
}
}
final Set<String> columnNameBases = new HashSet<String>(incrementingColumnsMap.keySet());
for (final String columnNameBase : columnNameBases) {
if (incrementingColumnsMap.get(columnNameBase) == 1) {
incrementingColumnsMap.remove(columnNameBase);
}
}
final Multimap<String, IncrementingColumn> incrementingColumns =
new Multimap<String, IncrementingColumn>();
for (final Column column : columns) {
final String columnName = Utility.convertForComparison(column.getName());
if (incrementingColumnsMap.containsKey(columnName)) {
incrementingColumns.add(columnName, new IncrementingColumn(columnName, "0", column));
}
final Matcher matcher = pattern.matcher(columnName);
if (matcher.matches()) {
final String columnNameBase = matcher.group(1);
final String columnIncrement = matcher.group(2);
if (incrementingColumnsMap.containsKey(columnNameBase)) {
incrementingColumns.add(
columnNameBase, new IncrementingColumn(columnNameBase, columnIncrement, column));
}
}
}
return incrementingColumns;
}
private static boolean isOutgoingForeignKey(final Table table, final ForeignKey foreignKey) {
final List<ForeignKeyColumnReference> fkcms = foreignKey.getColumnReferences();
ThreadContext.assertFault(
fkcms.size() > 0,
"Table [%s] reports a foreign key [%s] with no columns",
table.getName(),
foreignKey.getName());
final ForeignKeyColumnReference fkcm = fkcms.get(0);
final Column referencedColumn = fkcm.getPrimaryKeyColumn();
final String referencedtableName = referencedColumn.getParent().getName();
return !referencedtableName.equals(table.getName());
}
@Override
public Column getColumn(final String name) {
if (column.getName().equals(name)) {
return column;
} else {
return null;
}
}
private boolean hasUselessSurrogateKey(final Table table) {
if (!(table instanceof View) && table.getPrimaryKey() != null) {
boolean hasUselessSurrogateKey = true;
for (final Column column : table.getColumns()) {
if (column.isPartOfPrimaryKey()) {
continue;
}
if (!column.isPartOfForeignKey()) {
hasUselessSurrogateKey = false;
break;
}
}
return hasUselessSurrogateKey;
}
return false;
}
private Element createColumnElement(final Document dom, final Column column) {
final Element eColumn = dom.createElement("column");
final String columnName = getPhysicalName(column.getName());
eColumn.setAttribute("name", getLogicalName(columnName));
eColumn.setAttribute("physicalname", columnName);
setColumnTypeAttributes(eColumn, column, "table");
String requirement = null;
if (column.isPartOfPrimaryKey()) {
requirement = "primarykey";
} else {
requirement = column.isNullable() ? "optional" : "mandatory";
}
eColumn.setAttribute("requirement", requirement);
return eColumn;
}
public static Connectivity getConnectivity(final Column fkColumn) {
if (fkColumn == null) {
return Connectivity.unknown;
}
boolean isColumnReference;
try {
fkColumn.getColumnDataType();
isColumnReference = false;
} catch (final Exception e) {
isColumnReference = true;
}
if (isColumnReference) {
return Connectivity.unknown;
}
if (fkColumn.isPartOfPrimaryKey() || fkColumn.isPartOfUniqueIndex()) {
return Connectivity.zero_one;
} else {
return Connectivity.zero_many;
}
}
ColumnPartial(final Column column) {
this(requireNonNull(column, "No column provided").getParent(), column.getName());
addAttributes(column.getAttributes());
}
private void createForeignKeys(
final MetadataResultSet results, final NamedObjectList<MutableForeignKey> foreignKeys)
throws SQLException {
try {
while (results.next()) {
String foreignKeyName = quotedName(results.getString("FK_NAME"));
LOGGER.log(Level.FINER, "Retrieving foreign key: " + foreignKeyName);
final String pkTableCatalogName = quotedName(results.getString("PKTABLE_CAT"));
final String pkTableSchemaName = quotedName(results.getString("PKTABLE_SCHEM"));
final String pkTableName = quotedName(results.getString("PKTABLE_NAME"));
final String pkColumnName = quotedName(results.getString("PKCOLUMN_NAME"));
final String fkTableCatalogName = quotedName(results.getString("FKTABLE_CAT"));
final String fkTableSchemaName = quotedName(results.getString("FKTABLE_SCHEM"));
final String fkTableName = quotedName(results.getString("FKTABLE_NAME"));
final String fkColumnName = quotedName(results.getString("FKCOLUMN_NAME"));
final int keySequence = results.getInt("KEY_SEQ", 0);
final int updateRule = results.getInt("UPDATE_RULE", ForeignKeyUpdateRule.unknown.getId());
final int deleteRule = results.getInt("DELETE_RULE", ForeignKeyUpdateRule.unknown.getId());
final int deferrability =
results.getInt("DEFERRABILITY", ForeignKeyDeferrability.unknown.getId());
final Column pkColumn =
lookupOrCreateColumn(pkTableCatalogName, pkTableSchemaName, pkTableName, pkColumnName);
final Column fkColumn =
lookupOrCreateColumn(fkTableCatalogName, fkTableSchemaName, fkTableName, fkColumnName);
final boolean isPkColumnPartial = pkColumn instanceof ColumnPartial;
final boolean isFkColumnPartial = fkColumn instanceof ColumnPartial;
if (pkColumn == null || fkColumn == null || (isFkColumnPartial && isPkColumnPartial)) {
continue;
}
if (Utility.isBlank(foreignKeyName)) {
foreignKeyName = MetaDataUtility.constructForeignKeyName(pkColumn, fkColumn);
}
final Optional<MutableForeignKey> foreignKeyOptional = foreignKeys.lookup(foreignKeyName);
final MutableForeignKey foreignKey;
if (foreignKeyOptional.isPresent()) {
foreignKey = foreignKeyOptional.get();
} else {
foreignKey = new MutableForeignKey(foreignKeyName);
foreignKeys.add(foreignKey);
}
foreignKey.addColumnReference(keySequence, pkColumn, fkColumn);
foreignKey.setUpdateRule(ForeignKeyUpdateRule.valueOf(updateRule));
foreignKey.setDeleteRule(ForeignKeyUpdateRule.valueOf(deleteRule));
foreignKey.setDeferrability(ForeignKeyDeferrability.valueOf(deferrability));
foreignKey.addAttributes(results.getAttributes());
if (fkColumn instanceof MutableColumn) {
((MutableColumn) fkColumn).setReferencedColumn(pkColumn);
((MutableTable) fkColumn.getParent()).addForeignKey(foreignKey);
} else if (isFkColumnPartial) {
((ColumnPartial) fkColumn).setReferencedColumn(pkColumn);
((TablePartial) fkColumn.getParent()).addForeignKey(foreignKey);
}
if (pkColumn instanceof MutableColumn) {
((MutableTable) pkColumn.getParent()).addForeignKey(foreignKey);
} else if (isPkColumnPartial) {
((TablePartial) pkColumn.getParent()).addForeignKey(foreignKey);
}
}
} finally {
results.close();
}
}