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