Java tutorial
package org.jumpmind.db.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 static org.apache.commons.lang.StringUtils.isNotBlank; import java.sql.Connection; 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.LinkedHashMap; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.commons.lang.StringUtils; import org.jumpmind.db.model.Column; import org.jumpmind.db.model.ColumnTypes; import org.jumpmind.db.model.IIndex; import org.jumpmind.db.model.Table; import org.jumpmind.db.model.TypeMap; import org.jumpmind.db.platform.AbstractJdbcDdlReader; import org.jumpmind.db.platform.DatabaseMetaDataWrapper; import org.jumpmind.db.platform.IDatabasePlatform; import org.jumpmind.db.platform.IDdlBuilder; /* * Reads a database model from an Oracle 8 database. */ public class OracleDdlReader extends AbstractJdbcDdlReader { /* 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; public OracleDdlReader(IDatabasePlatform platform) { super(platform); setDefaultCatalogPattern(null); setDefaultSchemaPattern(null); setDefaultTablePattern("%"); 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'\\)"); } @Override protected Table readTable(Connection connection, DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { /* * Oracle 10 added the recycle bin which contains dropped database * objects not yet purged Since we don't want entries from the recycle * bin, we filter them out */ boolean tableHasBeenDeleted = isTableInRecycleBin(connection, values); /* * System tables are in the system schema */ String schema = (String) values.get(getResultSetSchemaName()); if (!tableHasBeenDeleted && !"SYSTEM".equals(schema)) { Table table = super.readTable(connection, metaData, values); if (table != null) { determineAutoIncrementColumns(connection, table); } return table; } else { return null; } } protected boolean isTableInRecycleBin(Connection connection, Map<String, Object> values) throws SQLException { String tablename = (String) values.get("TABLE_NAME"); return StringUtils.isNotBlank(tablename) && tablename.toLowerCase().startsWith("bin$"); } @Override protected Integer mapUnknownJdbcTypeForColumn(Map<String, Object> values) { String typeName = (String) values.get("TYPE_NAME"); if (typeName != null && typeName.startsWith("DATE")) { return Types.DATE; } else if (typeName != null && typeName.startsWith("TIMESTAMP") && !typeName.endsWith("TIME ZONE")) { // This is for Oracle's TIMESTAMP(9) return Types.TIMESTAMP; } else if (typeName != null && typeName.startsWith("TIMESTAMP") && typeName.endsWith("WITH TIME ZONE")) { return ColumnTypes.ORACLE_TIMESTAMPTZ; } else if (typeName != null && typeName.startsWith("TIMESTAMP") && typeName.endsWith("WITH LOCAL TIME ZONE")) { return ColumnTypes.ORACLE_TIMESTAMPLTZ; } else if (typeName != null && typeName.startsWith("NVARCHAR")) { // This is for Oracle's NVARCHAR type return Types.VARCHAR; } else if (typeName != null && typeName.startsWith("LONGNVARCHAR")) { return Types.LONGVARCHAR; } else if (typeName != null && typeName.startsWith("NCHAR")) { return Types.CHAR; } else if (typeName != null && typeName.startsWith("XML")) { return Types.LONGVARCHAR; } else if (typeName != null && typeName.endsWith("CLOB")) { return Types.LONGVARCHAR; } else if (typeName != null && typeName.startsWith("BINARY_FLOAT")) { return Types.FLOAT; } else if (typeName != null && typeName.startsWith("BINARY_DOUBLE")) { return Types.DOUBLE; } else if (typeName != null && typeName.startsWith("BFILE")) { return Types.VARCHAR; } else { return super.mapUnknownJdbcTypeForColumn(values); } } @Override protected Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { Column column = super.readColumn(metaData, values); if (column.getMappedTypeCode() == Types.DECIMAL) { // We're back-mapping the NUMBER columns returned by Oracle // Note that the JDBC driver returns DECIMAL for these NUMBER // columns if (column.getScale() <= -127 || column.getScale() >= 127) { if (column.getSizeAsInt() == 0) { /* * Latest oracle jdbc drivers for 11g return (0,-127) for * types defined as integer resulting in bad mappings. * NUMBER without scale or precision looks the same as * INTEGER in the jdbc driver. We must check the Oracle * meta data to see if type is an INTEGER. */ if (isColumnInteger((String) values.get("TABLE_NAME"), (String) values.get("COLUMN_NAME"))) { column.setMappedTypeCode(Types.BIGINT); } } else if (column.getSizeAsInt() <= 63) { column.setMappedTypeCode(Types.REAL); } else { column.setMappedTypeCode(Types.DOUBLE); } } } else if (column.getMappedTypeCode() == 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.setMappedTypeCode(Types.REAL); break; case 126: column.setMappedTypeCode(Types.DOUBLE); break; } } else if ((column.getMappedTypeCode() == Types.DATE) || (column.getMappedTypeCode() == Types.TIMESTAMP)) { // we also reverse the ISO-format adaptation, and adjust the default // value to timestamp if (column.getDefaultValue() != null) { Timestamp timestamp = null; Matcher matcher = oracleIsoTimestampPattern.matcher(column.getDefaultValue()); 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.getMappedTypeCode())) { String defaultValue = column.getDefaultValue(); if (isNotBlank(defaultValue) && defaultValue.startsWith("('") && defaultValue.endsWith("')")) { defaultValue = defaultValue.substring(2, defaultValue.length() - 2); } column.setDefaultValue(unescape(defaultValue, "'", "''")); } return column; } private boolean isColumnInteger(String tableName, String columnName) { return (platform.getSqlTemplate() .queryForInt( "select case when data_precision is null and data_scale=0 then 1 else 0 end " + "from all_tab_columns where table_name=? and column_name=?", tableName, columnName) == 1); } /* * Helper method that determines the auto increment status using Firebird's * system tables. * * @param table The table */ protected void determineAutoIncrementColumns(Connection connection, Table table) throws SQLException { Column[] columns = table.getColumns(); for (int idx = 0; idx < columns.length; idx++) { columns[idx].setAutoIncrement(isAutoIncrement(connection, 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(Connection connection, 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 PreparedStatement prepStmt = null; IDdlBuilder builder = getPlatform().getDdlBuilder(); String triggerName = builder.getConstraintName(OracleDdlBuilder.PREFIX_TRIGGER, table, column.getName(), null); String seqName = builder.getConstraintName(OracleDdlBuilder.PREFIX_SEQUENCE, table, column.getName(), null); if (!getPlatform().getDdlBuilder().isDelimitedIdentifierModeOn()) { triggerName = triggerName.toUpperCase(); seqName = seqName.toUpperCase(); } try { prepStmt = connection.prepareStatement("SELECT * FROM user_triggers WHERE trigger_name = ?"); prepStmt.setString(1, triggerName); ResultSet resultSet = prepStmt.executeQuery(); if (!resultSet.next()) { resultSet.close(); return false; } // we have a trigger, so lets check the sequence prepStmt.close(); prepStmt = connection.prepareStatement("SELECT * FROM user_sequences WHERE sequence_name = ?"); prepStmt.setString(1, seqName); resultSet = prepStmt.executeQuery(); boolean resultFound = resultSet.next(); resultSet.close(); return resultFound; } finally { if (prepStmt != null) { prepStmt.close(); } } } @Override protected Collection<IIndex> readIndices(Connection connection, DatabaseMetaDataWrapper metaData, String tableName) throws SQLException { // Oracle bug 4999817 causes a table analyze to execute in response to a // call to // DatabaseMetaData#getIndexInfo. // The bug is fixed in driver version 10.2.0.4. The bug is present in at // least // driver versions 10.2.0.1.0, 10.1.0.2.0, and 9.2.0.5. // To avoid this bug, we will access user_indexes view. // 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 StringBuilder query = new StringBuilder(); query.append( "SELECT a.INDEX_NAME, a.INDEX_TYPE, a.UNIQUENESS, b.COLUMN_NAME, b.COLUMN_POSITION FROM USER_INDEXES a, USER_IND_COLUMNS b WHERE "); query.append( "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 "); query.append( "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"); if (metaData.getSchemaPattern() != null) { query.append(" AND c.OWNER LIKE ?) AND a.TABLE_OWNER LIKE ?"); } else { query.append(")"); } Map<String, IIndex> indices = new LinkedHashMap<String, IIndex>(); PreparedStatement stmt = null; try { stmt = connection.prepareStatement(query.toString()); stmt.setString(1, getPlatform().getDdlBuilder().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<String, Object> values = new HashMap<String, Object>(); while (rs.next()) { String name = rs.getString(1); String type = rs.getString(2); // Only read in normal oracle indexes if (type.startsWith("NORMAL")) { values.put("INDEX_TYPE", new Short(DatabaseMetaData.tableIndexOther)); values.put("INDEX_NAME", name); 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); } else if (log.isDebugEnabled()) { log.debug("Skipping index " + name + " of type " + type); } } rs.close(); } finally { if (stmt != null) { stmt.close(); } } return indices.values(); } }