Search in sources :

Example 1 with Table

use of schemacrawler.schema.Table 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)1 IOMessageImpl (eu.esdihumboldt.hale.common.core.io.report.impl.IOMessageImpl)1 TypeDefinition (eu.esdihumboldt.hale.common.schema.model.TypeDefinition)1 DefaultPropertyDefinition (eu.esdihumboldt.hale.common.schema.model.impl.DefaultPropertyDefinition)1 DefaultSchema (eu.esdihumboldt.hale.common.schema.model.impl.DefaultSchema)1 DefaultTypeDefinition (eu.esdihumboldt.hale.common.schema.model.impl.DefaultTypeDefinition)1 DatabaseTable (eu.esdihumboldt.hale.io.jdbc.constraints.DatabaseTable)1 JDBCSchemaReaderAdvisor (eu.esdihumboldt.hale.io.jdbc.extension.JDBCSchemaReaderAdvisor)1 IOException (java.io.IOException)1 URI (java.net.URI)1 Connection (java.sql.Connection)1 ResultSet (java.sql.ResultSet)1 SQLException (java.sql.SQLException)1 Statement (java.sql.Statement)1 ArrayList (java.util.ArrayList)1 BaseColumn (schemacrawler.schema.BaseColumn)1 Catalog (schemacrawler.schema.Catalog)1 Column (schemacrawler.schema.Column)1 IndexColumn (schemacrawler.schema.IndexColumn)1 ResultsColumn (schemacrawler.schema.ResultsColumn)1