use of schemacrawler.schema.ResultsColumn in project hale by halestudio.
the class SQLSchemaReader method loadFromSource.
@Override
protected Schema loadFromSource(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException {
DefaultSchema typeIndex = null;
String query = null;
Text text = getParameter(PARAM_SQL).as(Text.class);
if (text != null) {
query = text.getText();
}
if (query == null) {
query = getParameter(PARAM_SQL).as(String.class);
}
if (query == null) {
reporter.setSuccess(false);
reporter.setSummary("No SQL query specified");
return null;
}
String typename = getParameter(PARAM_TYPE_NAME).as(String.class);
if (typename == null) {
reporter.setSuccess(false);
reporter.setSummary("Name of the type that the SQL query should be represented as must be specified");
return null;
}
progress.begin("Read SQL query schema", ProgressIndicator.UNKNOWN);
Connection connection = null;
try {
// connect to the database
try {
connection = getConnection();
} catch (Exception e) {
reporter.error(new IOMessageImpl(e.getLocalizedMessage(), e));
reporter.setSuccess(false);
reporter.setSummary("Failed to connect to database.");
return null;
}
// connection has been created), report a warning message instead
try {
connection.setReadOnly(true);
} catch (SQLException e) {
// ignore
// reporter.warn(new IOMessageImpl(e.getLocalizedMessage(), e));
}
connection.setAutoCommit(false);
// get advisor
JDBCSchemaReaderAdvisor advisor = SchemaReaderAdvisorExtension.getInstance().getAdvisor(connection);
// determine quotes character
@SuppressWarnings("unused") String quotes = determineQuoteString(connection);
// FIXME not actually used here or in JDBC schema reader
URI jdbcURI = getSource().getLocation();
String dbNamespace = determineNamespace(jdbcURI, advisor);
String namespace = NAMESPACE;
SchemaCrawlerOptions options = new SchemaCrawlerOptions();
SchemaInfoLevel level = new SchemaInfoLevel();
level.setTag("hale");
// these are enabled by default, we don't need them (yet)
level.setRetrieveSchemaCrawlerInfo(false);
level.setRetrieveJdbcDriverInfo(false);
level.setRetrieveDatabaseInfo(false);
level.setRetrieveTables(false);
level.setRetrieveTableColumns(false);
level.setRetrieveForeignKeys(false);
// set what we need
level.setRetrieveColumnDataTypes(true);
level.setRetrieveUserDefinedColumnDataTypes(true);
options.setSchemaInfoLevel(level);
if (advisor != null) {
advisor.configureSchemaCrawler(options);
}
final Catalog database = SchemaCrawlerUtility.getCatalog(connection, options);
// create the type index
typeIndex = new DefaultSchema(dbNamespace, jdbcURI);
Statement st = null;
try {
st = JDBCUtil.createReadStatement(connection, 1);
// support project variables
String processedQuery = JDBCUtil.replaceVariables(query, getServiceProvider());
ResultSet result = st.executeQuery(processedQuery);
// the query represents a type
// get the type definition
TypeDefinition type = addTableType(query, namespace, typeIndex, connection, reporter, typename);
ResultsColumns additionalInfo = SchemaCrawlerUtility.getResultColumns(result);
for (final ResultsColumn column : additionalInfo.getColumns()) {
getOrCreateProperty(type, column, namespace, typeIndex, connection, reporter, database);
}
} finally {
if (st != null) {
st.close();
}
}
reporter.setSuccess(true);
} catch (Exception e) {
throw new IOProviderConfigurationException("Failed to read database schema", e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// ignore
}
}
progress.end();
}
return typeIndex;
}
use of schemacrawler.schema.ResultsColumn in project hale by halestudio.
the class JDBCSchemaReader method loadFromSource.
@Override
protected Schema loadFromSource(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException {
DefaultSchema typeIndex = null;
progress.begin("Read database schema", ProgressIndicator.UNKNOWN);
Connection connection = null;
try {
// connect to the database
try {
connection = getConnection();
} catch (Exception e) {
reporter.error(new IOMessageImpl(e.getLocalizedMessage(), e));
reporter.setSuccess(false);
reporter.setSummary("Failed to connect to database.");
return null;
}
// connection has been created), report a warning message instead
try {
connection.setReadOnly(true);
} catch (SQLException e) {
// ignore
// reporter.warn(new IOMessageImpl(e.getLocalizedMessage(), e));
}
URI jdbcURI = getSource().getLocation();
final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
SchemaInfoLevel level = new SchemaInfoLevel();
level.setTag("hale");
// these are enabled by default, we don't need them (yet)
level.setRetrieveSchemaCrawlerInfo(false);
level.setRetrieveJdbcDriverInfo(false);
level.setRetrieveDatabaseInfo(false);
// set what we need
level.setRetrieveTables(true);
level.setRetrieveColumnDataTypes(true);
level.setRetrieveUserDefinedColumnDataTypes(true);
// to get table columns
level.setRetrieveTableColumns(true);
// information, also
// includes primary key
// to get linking information
level.setRetrieveForeignKeys(true);
// level.setRetrieveIndices(true); // to get info about UNIQUE indices for validation
// XXX For some advanced info / DBMS specific info we'll need a
// properties file. See Config & InformationSchemaViews.
level.setTag("hale");
if (getParameter(SCHEMAS).as(String.class) != null) {
String schemas = getParameter(SCHEMAS).as(String.class).replace(',', '|');
options.setSchemaInclusionRule(new RegularExpressionInclusionRule(schemas));
}
if (SchemaSpaceID.SOURCE.equals(getSchemaSpace())) {
// show views and tables
final List<String> tableTypesWanted = Arrays.asList("TABLE", "VIEW", "MATERIALIZED VIEW");
// try to determine table types supported by the JDBC connection
final List<String> tableTypeSupported = new ArrayList<>();
try {
ResultSet rs = connection.getMetaData().getTableTypes();
while (rs.next()) {
String tableType = rs.getString(1);
tableTypeSupported.add(tableType);
}
} catch (Throwable t) {
// Ignore, try with wanted list
reporter.warn(new IOMessageImpl(MessageFormat.format("Could not determine supported table types for connection: {0}", t.getMessage()), t));
tableTypeSupported.addAll(tableTypesWanted);
}
options.setTableTypes(tableTypesWanted.stream().filter(tt -> tableTypeSupported.contains(tt)).collect(Collectors.toList()));
} else {
// only show tables
options.setTableTypes(Arrays.asList("TABLE"));
}
options.setSchemaInfoLevel(level);
// get advisor
// XXX should be created once, and used in other places if
// applicable
JDBCSchemaReaderAdvisor advisor = SchemaReaderAdvisorExtension.getInstance().getAdvisor(connection);
if (advisor != null) {
advisor.configureSchemaCrawler(options);
}
final Catalog database = SchemaCrawlerUtility.getCatalog(connection, options);
@SuppressWarnings("unused") String quotes = JDBCUtil.determineQuoteString(connection);
// FIXME not actually used here or in SQL schema reader
String overallNamespace = JDBCUtil.determineNamespace(jdbcURI, advisor);
// create the type index
typeIndex = new DefaultSchema(overallNamespace, jdbcURI);
for (final schemacrawler.schema.Schema schema : database.getSchemas()) {
// each schema represents a namespace
String namespace;
if (overallNamespace.isEmpty()) {
namespace = unquote(schema.getName());
} else {
namespace = overallNamespace;
if (schema.getName() != null) {
namespace += ":" + unquote(schema.getName());
}
}
for (final Table table : database.getTables(schema)) {
// each table is a type
// get the type definition
TypeDefinition type = getOrCreateTableType(schema, table, overallNamespace, namespace, typeIndex, connection, reporter, database);
// get ResultSetMetaData for extra info about columns (e. g.
// auto increment)
ResultsColumns additionalInfo = null;
Statement stmt = null;
try {
stmt = connection.createStatement();
// get if in table name, quotation required or not.
String fullTableName = getQuotedValue(table.getName());
if (schema.getName() != null) {
fullTableName = getQuotedValue(schema.getName()) + "." + fullTableName;
}
ResultSet rs = stmt.executeQuery("SELECT * FROM " + fullTableName + " WHERE 1 = 0");
additionalInfo = SchemaCrawlerUtility.getResultColumns(rs);
} catch (SQLException sqle) {
reporter.warn(new IOMessageImpl("Couldn't retrieve additional column meta data.", sqle));
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException e) {
// ignore
}
}
// create property definitions for each column
for (final Column column : table.getColumns()) {
DefaultPropertyDefinition property = getOrCreateProperty(schema, type, column, overallNamespace, namespace, typeIndex, connection, reporter, database);
// XXX does not work for example for PostgreSQL
if (additionalInfo != null) {
// ResultColumns does not quote the column namen in
// contrast to every other place
ResultsColumn rc = additionalInfo.getColumn(unquote(column.getName()));
if (rc != null && rc.isAutoIncrement())
property.setConstraint(AutoIncrementFlag.get(true));
}
}
}
}
reporter.setSuccess(true);
} catch (SchemaCrawlerException e) {
throw new IOProviderConfigurationException("Failed to read database schema", e);
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// ignore
}
}
progress.end();
}
return typeIndex;
}
Aggregations