org.apache.ddlutils.platform.oracle.Oracle8ModelReader.java Source code

Java tutorial

Introduction

Here is the source code for org.apache.ddlutils.platform.oracle.Oracle8ModelReader.java

Source

package org.apache.ddlutils.platform.oracle;

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */

import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.ddlutils.DdlUtilsException;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.model.Column;
import org.apache.ddlutils.model.Table;
import org.apache.ddlutils.model.TypeMap;
import org.apache.ddlutils.platform.DatabaseMetaDataWrapper;
import org.apache.ddlutils.platform.JdbcModelReader;

/**
 * Reads a database model from an Oracle 8 database.
 *
 * @version $Revision: $
 */
public class Oracle8ModelReader extends JdbcModelReader {
    /** The regular expression pattern for the Oracle conversion of ISO dates. */
    private Pattern _oracleIsoDatePattern;
    /** The regular expression pattern for the Oracle conversion of ISO times. */
    private Pattern _oracleIsoTimePattern;
    /** The regular expression pattern for the Oracle conversion of ISO timestamps. */
    private Pattern _oracleIsoTimestampPattern;

    /**
      * Creates a new model reader for Oracle 8 databases.
      * 
      * @param platform The platform that this model reader belongs to
      */
    public Oracle8ModelReader(Platform platform) {
        super(platform);
        setDefaultCatalogPattern(null);
        setDefaultSchemaPattern(null);
        setDefaultTablePattern("%");

        try {
            _oracleIsoDatePattern = Pattern.compile("TO_DATE\\('([^']*)'\\, 'YYYY\\-MM\\-DD'\\)");
            _oracleIsoTimePattern = Pattern.compile("TO_DATE\\('([^']*)'\\, 'HH24:MI:SS'\\)");
            _oracleIsoTimestampPattern = Pattern.compile("TO_DATE\\('([^']*)'\\, 'YYYY\\-MM\\-DD HH24:MI:SS'\\)");
        } catch (PatternSyntaxException ex) {
            throw new DdlUtilsException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    protected Table readTable(DatabaseMetaDataWrapper metaData, Map values) throws SQLException {
        String tableName = (String) values.get("TABLE_NAME");

        // system table ?
        if (tableName.indexOf('$') > 0) {
            return null;
        }

        Table table = super.readTable(metaData, values);

        if (table != null) {
            determineAutoIncrementColumns(table);
        }

        return table;
    }

    /**
      * {@inheritDoc}
      */
    protected Column readColumn(DatabaseMetaDataWrapper metaData, Map values) throws SQLException {
        Column column = super.readColumn(metaData, values);

        if (column.getDefaultValue() != null) {
            // Oracle pads the default value with spaces
            column.setDefaultValue(column.getDefaultValue().trim());
        }
        if (column.getTypeCode() == Types.DECIMAL) {
            // We're back-mapping the NUMBER columns returned by Oracle
            // Note that the JDBC driver returns DECIMAL for these NUMBER columns
            switch (column.getSizeAsInt()) {
            case 1:
                if (column.getScale() == 0) {
                    column.setTypeCode(Types.BIT);
                }
                break;
            case 3:
                if (column.getScale() == 0) {
                    column.setTypeCode(Types.TINYINT);
                }
                break;
            case 5:
                if (column.getScale() == 0) {
                    column.setTypeCode(Types.SMALLINT);
                }
                break;
            case 18:
                column.setTypeCode(Types.REAL);
                break;
            case 22:
                if (column.getScale() == 0) {
                    column.setTypeCode(Types.INTEGER);
                }
                break;
            case 38:
                if (column.getScale() == 0) {
                    column.setTypeCode(Types.BIGINT);
                } else {
                    column.setTypeCode(Types.DOUBLE);
                }
                break;
            }
        } else if (column.getTypeCode() == Types.FLOAT) {
            // Same for REAL, FLOAT, DOUBLE PRECISION, which all back-map to FLOAT but with
            // different sizes (63 for REAL, 126 for FLOAT/DOUBLE PRECISION)
            switch (column.getSizeAsInt()) {
            case 63:
                column.setTypeCode(Types.REAL);
                break;
            case 126:
                column.setTypeCode(Types.DOUBLE);
                break;
            }
        } else if ((column.getTypeCode() == Types.DATE) || (column.getTypeCode() == Types.TIMESTAMP)) {
            // Oracle has only one DATE/TIME type, so we can't know which it is and thus map
            // it back to TIMESTAMP
            column.setTypeCode(Types.TIMESTAMP);

            // we also reverse the ISO-format adaptation, and adjust the default value to timestamp
            if (column.getDefaultValue() != null) {
                Matcher matcher = _oracleIsoTimestampPattern.matcher(column.getDefaultValue());
                Timestamp timestamp = null;

                if (matcher.matches()) {
                    String timestampVal = matcher.group(1);

                    timestamp = Timestamp.valueOf(timestampVal);
                } else {
                    matcher = _oracleIsoDatePattern.matcher(column.getDefaultValue());
                    if (matcher.matches()) {
                        String dateVal = matcher.group(1);

                        timestamp = new Timestamp(Date.valueOf(dateVal).getTime());
                    } else {
                        matcher = _oracleIsoTimePattern.matcher(column.getDefaultValue());
                        if (matcher.matches()) {
                            String timeVal = matcher.group(1);

                            timestamp = new Timestamp(Time.valueOf(timeVal).getTime());
                        }
                    }
                }
                if (timestamp != null) {
                    column.setDefaultValue(timestamp.toString());
                }
            }
        } else if (TypeMap.isTextType(column.getTypeCode())) {
            column.setDefaultValue(unescape(column.getDefaultValue(), "'", "''"));
        }
        return column;
    }

    /**
     * Helper method that determines the auto increment status using Firebird's system tables.
     *
     * @param table The table
     */
    protected void determineAutoIncrementColumns(Table table) throws SQLException {
        Column[] columns = table.getColumns();

        for (int idx = 0; idx < columns.length; idx++) {
            columns[idx].setAutoIncrement(isAutoIncrement(table, columns[idx]));
        }
    }

    /**
     * Tries to determine whether the given column is an identity column.
     * 
     * @param table  The table
     * @param column The column
     * @return <code>true</code> if the column is an identity column
     */
    protected boolean isAutoIncrement(Table table, Column column) throws SQLException {
        // TODO: For now, we only check whether there is a sequence & trigger as generated by DdlUtils
        //       But once sequence/trigger support is in place, it might be possible to 'parse' the
        //       trigger body (via SELECT trigger_name, trigger_body FROM user_triggers) in order to
        //       determine whether it fits our auto-increment definition
        final String triggerQuery = "SELECT * FROM user_triggers WHERE trigger_name = ?";
        final String sequenceQuery = "SELECT * FROM user_sequences WHERE sequence_name = ?";

        PreparedStatement prepStmt = null;
        String triggerName = getPlatform().getSqlBuilder().getConstraintName("trg", table, column.getName(), null);
        String seqName = getPlatform().getSqlBuilder().getConstraintName("seq", table, column.getName(), null);

        if (!getPlatform().isDelimitedIdentifierModeOn()) {
            triggerName = triggerName.toUpperCase();
            seqName = seqName.toUpperCase();
        }
        try {
            prepStmt = getConnection().prepareStatement(triggerQuery);
            prepStmt.setString(1, triggerName);

            ResultSet resultSet = prepStmt.executeQuery();

            if (!resultSet.next()) {
                return false;
            }
            // we have a trigger, so lets check the sequence
            closeStatement(prepStmt);

            prepStmt = getConnection().prepareStatement(sequenceQuery);
            prepStmt.setString(1, seqName);

            resultSet = prepStmt.executeQuery();
            return resultSet.next();
        } finally {
            closeStatement(prepStmt);
        }
    }

    /**
     * {@inheritDoc}
     */
    protected Collection readIndices(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException {
        // Oracle has a bug in the DatabaseMetaData#getIndexInfo method which fails when
        // delimited identifiers are being used
        // Therefore, we're rather accessing the user_indexes table which contains the same info
        // This also allows us to filter system-generated indices which are identified by either
        // having GENERATED='Y' in the query result, or by their index names being equal to the
        // name of the primary key of the table

        final String query = "SELECT a.INDEX_NAME, a.INDEX_TYPE, a.UNIQUENESS, b.COLUMN_NAME, b.COLUMN_POSITION FROM USER_INDEXES a, USER_IND_COLUMNS b WHERE "
                + "a.TABLE_NAME=? AND a.GENERATED=? AND a.TABLE_TYPE=? AND a.TABLE_NAME=b.TABLE_NAME AND a.INDEX_NAME=b.INDEX_NAME AND "
                + "a.INDEX_NAME NOT IN (SELECT DISTINCT c.CONSTRAINT_NAME FROM USER_CONSTRAINTS c WHERE c.CONSTRAINT_TYPE=? AND c.TABLE_NAME=a.TABLE_NAME)";
        final String queryWithSchema = query.substring(0, query.length() - 1)
                + " AND c.OWNER LIKE ?) AND a.TABLE_OWNER LIKE ?";

        Map indices = new ListOrderedMap();
        PreparedStatement stmt = null;

        try {
            stmt = getConnection().prepareStatement(metaData.getSchemaPattern() == null ? query : queryWithSchema);
            stmt.setString(1, getPlatform().isDelimitedIdentifierModeOn() ? tableName : tableName.toUpperCase());
            stmt.setString(2, "N");
            stmt.setString(3, "TABLE");
            stmt.setString(4, "P");
            if (metaData.getSchemaPattern() != null) {
                stmt.setString(5, metaData.getSchemaPattern().toUpperCase());
                stmt.setString(6, metaData.getSchemaPattern().toUpperCase());
            }

            ResultSet rs = stmt.executeQuery();
            Map values = new HashMap();

            while (rs.next()) {
                values.put("INDEX_NAME", rs.getString(1));
                values.put("INDEX_TYPE", new Short(DatabaseMetaData.tableIndexOther));
                values.put("NON_UNIQUE", "UNIQUE".equalsIgnoreCase(rs.getString(3)) ? Boolean.FALSE : Boolean.TRUE);
                values.put("COLUMN_NAME", rs.getString(4));
                values.put("ORDINAL_POSITION", new Short(rs.getShort(5)));

                readIndex(metaData, values, indices);
            }
        } finally {
            closeStatement(stmt);
        }
        return indices.values();
    }
}