Java tutorial
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(); } }