/*
*
* The DbUnit Database Testing Framework
* Copyright (C)2002-2008, DbUnit.org
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*
*/
package org.dbunit.ext.oracle;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ORAData;
import org.dbunit.dataset.datatype.AbstractDataType;
import org.dbunit.dataset.datatype.TypeCastException;
import org.dbunit.dataset.ITable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* This class implements DataType for Oracle SDO_GEOMETRY type used in Oracle Spatial.
* See the Oracle Spatial Developer's Guide for details on SDO_GEOMETRY. This class
* handles values similar to:
* <ul>
* <li>SDO_GEOMETRY(NULL, NULL, NULL, NULL, NULL)</li>
* <li>NULL</li>
* <li>SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(71.2988, 42.8052, NULL), NULL, NULL)</li>
* <li>SDO_GEOMETRY(3302, NULL, SDO_POINT_TYPE(96.8233, 32.5261, NULL), SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(2, 2, 0, 2, 4, 2, 8, 4, 8, 12, 4, 12, 12, 10, NULL, 8, 10, 22, 5, 14, 27))</li>
* </ul>
*
* <p>
* For more information on oracle spatial support go to http://tahiti.oracle.com
* and search for "spatial". The developers guide is available at
* http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28400/toc.htm
* </p>
*
* <p>
* example table:
* <code>
* CREATE TABLE cola_markets (
* mkt_id NUMBER PRIMARY KEY,
* name VARCHAR2(32),
* shape SDO_GEOMETRY);
* </code>
* </p>
*
* <p>
* example insert:
* <code>
* INSERT INTO cola_markets VALUES(
* 2,
* 'cola_b',
* SDO_GEOMETRY(
* 2003, -- two-dimensional polygon
* NULL,
* NULL,
* SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
* SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
* )
* );
* </code>
* </p>
*
* <p>
* This class uses the following objects which were rendered using oracle jpub and then
* slightly customized to work with dbunit:
* <ul>
* <li>OracleSdoGeometry - corresponds to oracle SDO_GEOMETRY data type</li>
* <li>OracleSdoPointType - corresponds to oracle SDO_POINT_TYPE data type</li>
* <li>OracleSdoElemInfoArray - corresponds to oracle SDO_ELEM_INFO_ARRAY data type</li>
* <li>OracleSdoOridinateArray - corresponds to oracle SDO_ORDINATE_ARRAY data type</li>
* </ul>
* These classes were rendered via jpub
* (http://download.oracle.com/otn/utilities_drivers/jdbc/10201/jpub_102.zip)
* with the following command syntax:
* <code>
* ./jpub -user=YOUR_USER_ID/YOUR_PASSWORD -url=YOUR_JDBC_URL
* -sql mdsys.sdo_geometry:OracleSdoGeometry,
* mdsys.sdo_point_type:OracleSdoPointType,
* mdsys.sdo_elem_info_array:OracleSdoElemInfoArray,
* mdsys.sdo_ordinate_array:OracleSdoOrdinateArray
* -dir=output_dir -methods=none -package=org.dbunit.ext.oracle -tostring=true
* </code>
* The equals and hashCode methods were then added so that the objects could be compared
* in test cases. Note that I did have to bash the jpub startup script (change classpath)
* because it assumes oracle 10g database but I ran it with 11g. Theoretically, this
* process can be repeated for other custom oracle object data types.
* </p>
*
* @author clucas@e-miles.com
* @author Last changed by: $Author$
* @version $Revision$ $Date$
* @since <dbunit-version>
*/
public class OracleSdoGeometryDataType extends AbstractDataType
{
/**
* Logger for this class
*/
private static final Logger logger = LoggerFactory.getLogger(OracleSdoGeometryDataType.class);
private static final String NULL = "NULL";
private static final String SDO_GEOMETRY = "SDO_GEOMETRY";
// patterns for parsing out the various pieces of the string
// representation of an sdo_geometry object
private static final Pattern sdoGeometryPattern = Pattern.compile(
"^(?:MDSYS\\.)?SDO_GEOMETRY\\s*\\(\\s*([^,\\s]+)\\s*,\\s*([^,\\s]+)\\s*,\\s*");
private static final Pattern sdoPointTypePattern = Pattern.compile(
"^(?:(?:(?:MDSYS\\.)?SDO_POINT_TYPE\\s*\\(\\s*([^,\\s]+)\\s*,\\s*([^,\\s]+)\\s*,\\s*([^,\\s\\)]+)\\s*\\))|(NULL))\\s*,\\s*");
private static final Pattern sdoElemInfoArrayPattern = Pattern.compile(
"^(?:(?:(?:(?:MDSYS\\.)?SDO_ELEM_INFO_ARRAY\\s*\\(([^\\)]*)\\))|(NULL)))\\s*,\\s*");
private static final Pattern sdoOrdinateArrayPattern = Pattern.compile(
"^(?:(?:(?:(?:MDSYS\\.)?SDO_ORDINATE_ARRAY\\s*\\(([^\\)]*)\\))|(NULL)))\\s*\\)\\s*");
OracleSdoGeometryDataType ()
{
super(SDO_GEOMETRY, Types.STRUCT, OracleSdoGeometry.class, false);
}
public Object typeCast(Object value) throws TypeCastException
{
logger.debug("typeCast(value={}) - start", value);
if (value == null || value == ITable.NO_VALUE)
{
return null;
}
if (value instanceof OracleSdoGeometry)
{
return (OracleSdoGeometry) value;
}
if (value instanceof String)
{
// attempt to parse the SDO_GEOMETRY
try
{
// all upper case for parse purposes
String upperVal = ((String) value).toUpperCase().trim();
if (NULL.equals(upperVal))
{
return null;
}
// parse out sdo_geometry
Matcher sdoGeometryMatcher = sdoGeometryPattern.matcher(upperVal);
if (! sdoGeometryMatcher.find())
{
throw new TypeCastException(value, this);
}
BigDecimal gtype = NULL.equals(sdoGeometryMatcher.group(1)) ?
null : new BigDecimal(sdoGeometryMatcher.group(1));
BigDecimal srid = NULL.equals(sdoGeometryMatcher.group(2)) ?
null : new BigDecimal(sdoGeometryMatcher.group(2));
// parse out sdo_point_type
upperVal = upperVal.substring(sdoGeometryMatcher.end());
Matcher sdoPointTypeMatcher = sdoPointTypePattern.matcher(upperVal);
if (! sdoPointTypeMatcher.find())
{
throw new TypeCastException(value, this);
}
OracleSdoPointType sdoPoint;
if (NULL.equals(sdoPointTypeMatcher.group(4)))
{
sdoPoint = null;
}
else
{
sdoPoint = new OracleSdoPointType(
NULL.equals(sdoPointTypeMatcher.group(1)) ? null :
new BigDecimal(sdoPointTypeMatcher.group(1)),
NULL.equals(sdoPointTypeMatcher.group(2)) ? null :
new BigDecimal(sdoPointTypeMatcher.group(2)),
NULL.equals(sdoPointTypeMatcher.group(3)) ? null :
new BigDecimal(sdoPointTypeMatcher.group(3)));
}
// parse out sdo_elem_info_array
upperVal = upperVal.substring(sdoPointTypeMatcher.end());
Matcher sdoElemInfoArrayMatcher = sdoElemInfoArrayPattern.matcher(upperVal);
if (! sdoElemInfoArrayMatcher.find())
{
throw new TypeCastException(value, this);
}
OracleSdoElemInfoArray sdoElemInfoArray;
if (NULL.equals(sdoElemInfoArrayMatcher.group(2)))
{
sdoElemInfoArray = null;
}
else
{
String [] elemInfoStrings = sdoElemInfoArrayMatcher.group(1).
trim().split("\\s*,\\s*");
if (elemInfoStrings.length == 1 && "".equals(elemInfoStrings[0]))
{
sdoElemInfoArray = new OracleSdoElemInfoArray();
}
else
{
BigDecimal [] elemInfos = new BigDecimal[elemInfoStrings.length];
for (int index = 0; index < elemInfoStrings.length; index++)
{
elemInfos[index] = NULL.equals(elemInfoStrings[index]) ?
null : new BigDecimal(elemInfoStrings[index]);
}
sdoElemInfoArray = new OracleSdoElemInfoArray(elemInfos);
}
}
// parse out sdo_ordinate_array
upperVal = upperVal.substring(sdoElemInfoArrayMatcher.end());
Matcher sdoOrdinateArrayMatcher = sdoOrdinateArrayPattern.matcher(upperVal);
if (! sdoOrdinateArrayMatcher.find())
{
throw new TypeCastException(value, this);
}
OracleSdoOrdinateArray sdoOrdinateArray;
if (NULL.equals(sdoOrdinateArrayMatcher.group(2)))
{
sdoOrdinateArray = null;
}
else
{
String [] ordinateStrings = sdoOrdinateArrayMatcher.group(1).
trim().split("\\s*,\\s*");
if (ordinateStrings.length == 1 && "".equals(ordinateStrings[0]))
{
sdoOrdinateArray = new OracleSdoOrdinateArray();
}
else
{
BigDecimal [] ordinates = new BigDecimal[ordinateStrings.length];
for (int index = 0; index < ordinateStrings.length; index++)
{
ordinates[index] = NULL.equals(ordinateStrings[index]) ?
null : new BigDecimal(ordinateStrings[index]);
}
sdoOrdinateArray = new OracleSdoOrdinateArray(ordinates);
}
}
OracleSdoGeometry sdoGeometry = new OracleSdoGeometry(
gtype, srid, sdoPoint, sdoElemInfoArray, sdoOrdinateArray);
return sdoGeometry;
}
catch (SQLException e)
{
throw new TypeCastException(value, this, e);
}
catch (NumberFormatException e)
{
throw new TypeCastException(value, this, e);
}
}
throw new TypeCastException(value, this);
}
public Object getSqlValue(int column, ResultSet resultSet)
throws SQLException, TypeCastException
{
if(logger.isDebugEnabled())
logger.debug("getSqlValue(column={}, resultSet={}) - start",
new Integer(column), resultSet);
Object data = null;
try
{
data = ((OracleResultSet) resultSet).
getORAData(column, OracleSdoGeometry.getORADataFactory());
// It would be preferable to return the actual object, but there are
// a few dbunit issues with this:
//
// 1. Dbunit does not support nulls for user defined types (at least
// with oracle.) PreparedStatement.setNull(int, int) is always used
// but PreparedStatement.setNull(int, int, String) is required
// for sdo_geometry (and other similar custom object types).
//
// 2. Dbunit does not support rendering custom objects (such as
// OracleSdoGeometry) as strings.
//
// So, instead we return the object as a String or "NULL".
// return data;
if (data != null)
{
return data.toString();
}
else
{
// return a string instead of null so that it can be interpreted
// in typeCast. DBUnit does not handle PreparedStatement.setNull
// for user defined types.
return NULL;
}
}
catch (SQLException e)
{
throw new TypeCastException(data, this, e);
}
}
public void setSqlValue(Object value, int column, PreparedStatement statement)
throws SQLException, TypeCastException
{
Object castValue = typeCast(value);
if (castValue == null)
{
statement.setNull(column, OracleSdoGeometry._SQL_TYPECODE,
OracleSdoGeometry._SQL_NAME);
}
else
{
((OraclePreparedStatement) statement).setORAData(column, (ORAData) castValue);
}
}
/**
* This method is copied from AbstractDataType and customized to call equals
* after the typeCast because OracleSdoGeometry objects are not Comparables
* but can test for equality (via equals method.) It is needed for test
* cases that check for equality between data in xml files and data read
* from the database.
*/
public int compare(Object o1, Object o2) throws TypeCastException
{
logger.debug("compare(o1={}, o2={}) - start", o1, o2);
try
{
// New in 2.3: Object level check for equality - should give massive performance improvements
// in the most cases because the typecast can be avoided (null values and equal objects)
if(areObjectsEqual(o1, o2))
{
return 0;
}
// Comparable check based on the results of method "typeCast"
Object value1 = typeCast(o1);
Object value2 = typeCast(o2);
// Check for "null"s again because typeCast can produce them
if (value1 == null && value2 == null)
{
return 0;
}
if (value1 == null && value2 != null)
{
return -1;
}
if (value1 != null && value2 == null)
{
return 1;
}
if (value1.equals(value2))
{
return 0;
}
return compareNonNulls(value1, value2);
}
catch (ClassCastException e)
{
throw new TypeCastException(e);
}
}
}