Search in sources :

Example 1 with ResultsColumn

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;
}
Also used : SchemaInfoLevel(schemacrawler.schemacrawler.SchemaInfoLevel) SQLException(java.sql.SQLException) Statement(java.sql.Statement) Connection(java.sql.Connection) IOMessageImpl(eu.esdihumboldt.hale.common.core.io.report.impl.IOMessageImpl) JDBCSchemaReaderAdvisor(eu.esdihumboldt.hale.io.jdbc.extension.JDBCSchemaReaderAdvisor) Text(eu.esdihumboldt.hale.common.core.io.Text) JDBCUtil.determineQuoteString(eu.esdihumboldt.hale.io.jdbc.JDBCUtil.determineQuoteString) SchemaCrawlerOptions(schemacrawler.schemacrawler.SchemaCrawlerOptions) URI(java.net.URI) IOProviderConfigurationException(eu.esdihumboldt.hale.common.core.io.IOProviderConfigurationException) SQLException(java.sql.SQLException) IOException(java.io.IOException) Catalog(schemacrawler.schema.Catalog) DefaultTypeDefinition(eu.esdihumboldt.hale.common.schema.model.impl.DefaultTypeDefinition) TypeDefinition(eu.esdihumboldt.hale.common.schema.model.TypeDefinition) ResultsColumns(schemacrawler.schema.ResultsColumns) IOProviderConfigurationException(eu.esdihumboldt.hale.common.core.io.IOProviderConfigurationException) DefaultSchema(eu.esdihumboldt.hale.common.schema.model.impl.DefaultSchema) ResultSet(java.sql.ResultSet) ResultsColumn(schemacrawler.schema.ResultsColumn)

Example 2 with ResultsColumn

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;
}
Also used : DefaultPropertyDefinition(eu.esdihumboldt.hale.common.schema.model.impl.DefaultPropertyDefinition) SchemaInfoLevel(schemacrawler.schemacrawler.SchemaInfoLevel) RegularExpressionInclusionRule(schemacrawler.schemacrawler.RegularExpressionInclusionRule) SQLException(java.sql.SQLException) IOMessageImpl(eu.esdihumboldt.hale.common.core.io.report.impl.IOMessageImpl) ArrayList(java.util.ArrayList) URI(java.net.URI) DefaultTypeDefinition(eu.esdihumboldt.hale.common.schema.model.impl.DefaultTypeDefinition) TypeDefinition(eu.esdihumboldt.hale.common.schema.model.TypeDefinition) ResultsColumn(schemacrawler.schema.ResultsColumn) Column(schemacrawler.schema.Column) BaseColumn(schemacrawler.schema.BaseColumn) IndexColumn(schemacrawler.schema.IndexColumn) SchemaCrawlerException(schemacrawler.schemacrawler.SchemaCrawlerException) DefaultSchema(eu.esdihumboldt.hale.common.schema.model.impl.DefaultSchema) ResultSet(java.sql.ResultSet) ResultsColumn(schemacrawler.schema.ResultsColumn) Table(schemacrawler.schema.Table) DatabaseTable(eu.esdihumboldt.hale.io.jdbc.constraints.DatabaseTable) Statement(java.sql.Statement) Connection(java.sql.Connection) JDBCSchemaReaderAdvisor(eu.esdihumboldt.hale.io.jdbc.extension.JDBCSchemaReaderAdvisor) SchemaCrawlerOptions(schemacrawler.schemacrawler.SchemaCrawlerOptions) IOProviderConfigurationException(eu.esdihumboldt.hale.common.core.io.IOProviderConfigurationException) SchemaCrawlerException(schemacrawler.schemacrawler.SchemaCrawlerException) SQLException(java.sql.SQLException) IOException(java.io.IOException) Catalog(schemacrawler.schema.Catalog) ResultsColumns(schemacrawler.schema.ResultsColumns) IOProviderConfigurationException(eu.esdihumboldt.hale.common.core.io.IOProviderConfigurationException)

Aggregations

IOProviderConfigurationException (eu.esdihumboldt.hale.common.core.io.IOProviderConfigurationException)2 IOMessageImpl (eu.esdihumboldt.hale.common.core.io.report.impl.IOMessageImpl)2 TypeDefinition (eu.esdihumboldt.hale.common.schema.model.TypeDefinition)2 DefaultSchema (eu.esdihumboldt.hale.common.schema.model.impl.DefaultSchema)2 DefaultTypeDefinition (eu.esdihumboldt.hale.common.schema.model.impl.DefaultTypeDefinition)2 JDBCSchemaReaderAdvisor (eu.esdihumboldt.hale.io.jdbc.extension.JDBCSchemaReaderAdvisor)2 IOException (java.io.IOException)2 URI (java.net.URI)2 Connection (java.sql.Connection)2 ResultSet (java.sql.ResultSet)2 SQLException (java.sql.SQLException)2 Statement (java.sql.Statement)2 Catalog (schemacrawler.schema.Catalog)2 ResultsColumn (schemacrawler.schema.ResultsColumn)2 ResultsColumns (schemacrawler.schema.ResultsColumns)2 SchemaCrawlerOptions (schemacrawler.schemacrawler.SchemaCrawlerOptions)2 SchemaInfoLevel (schemacrawler.schemacrawler.SchemaInfoLevel)2 Text (eu.esdihumboldt.hale.common.core.io.Text)1 DefaultPropertyDefinition (eu.esdihumboldt.hale.common.schema.model.impl.DefaultPropertyDefinition)1 JDBCUtil.determineQuoteString (eu.esdihumboldt.hale.io.jdbc.JDBCUtil.determineQuoteString)1