src/java/org/apache/ddlutils/platform/oracle/Oracle8ModelReader.java - ddlutils - Git at Google

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