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