View Javadoc
1   /*
2    *
3    * The DbUnit Database Testing Framework
4    * Copyright (C)2002-2008, DbUnit.org
5    *
6    * This library is free software; you can redistribute it and/or
7    * modify it under the terms of the GNU Lesser General Public
8    * License as published by the Free Software Foundation; either
9    * version 2.1 of the License, or (at your option) any later version.
10   *
11   * This library is distributed in the hope that it will be useful,
12   * but WITHOUT ANY WARRANTY; without even the implied warranty of
13   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
14   * Lesser General Public License for more details.
15   *
16   * You should have received a copy of the GNU Lesser General Public
17   * License along with this library; if not, write to the Free Software
18   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
19   *
20   */
21  
22  package org.dbunit.ext.oracle;
23  
24  import java.math.BigDecimal;
25  import java.sql.Connection;
26  import java.sql.PreparedStatement;
27  import java.sql.ResultSet;
28  import java.sql.SQLException;
29  import java.sql.Types;
30  import java.util.regex.Matcher;
31  import java.util.regex.Pattern;
32  
33  import oracle.jdbc.OracleResultSet;
34  import oracle.jdbc.OraclePreparedStatement;
35  import oracle.sql.ORAData;
36  
37  import org.dbunit.dataset.datatype.AbstractDataType;
38  import org.dbunit.dataset.datatype.TypeCastException;
39  import org.dbunit.dataset.ITable;
40  
41  import org.slf4j.Logger;
42  import org.slf4j.LoggerFactory;
43  
44  
45  /**
46   * This class implements DataType for Oracle SDO_GEOMETRY type used in Oracle Spatial. 
47   * See the Oracle Spatial Developer's Guide for details on SDO_GEOMETRY.  This class
48   * handles values similar to:
49   * <ul>
50   * <li>SDO_GEOMETRY(NULL, NULL, NULL, NULL, NULL)</li>
51   * <li>NULL</li>
52   * <li>SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(71.2988, 42.8052, NULL), NULL, NULL)</li>
53   * <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>
54   * </ul>
55   *
56   * <p>
57   * For more information on oracle spatial support go to http://tahiti.oracle.com
58   * and search for &quot;spatial&quot;.  The developers guide is available at
59   * http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28400/toc.htm
60   * </p>
61   *
62   * <p>
63   * example table:
64   * <code>
65   *   CREATE TABLE cola_markets (
66   *     mkt_id NUMBER PRIMARY KEY,
67   *     name VARCHAR2(32),
68   *     shape SDO_GEOMETRY);
69   * </code>
70   * </p>
71   *
72   * <p>
73   * example insert:
74   * <code>
75   *   INSERT INTO cola_markets VALUES(
76   *     2,
77   *     'cola_b',
78   *     SDO_GEOMETRY(
79   *       2003,  -- two-dimensional polygon
80   *       NULL,
81   *       NULL,
82   *       SDO_ELEM_INFO_ARRAY(1,1003,1), -- one polygon (exterior polygon ring)
83   *       SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)
84   *     )
85   *    );
86   * </code>
87   * </p>
88   *
89   * <p>
90   * This class uses the following objects which were rendered using oracle jpub and then
91   * slightly customized to work with dbunit:
92   * <ul>
93   * <li>OracleSdoGeometry - corresponds to oracle SDO_GEOMETRY data type</li>
94   * <li>OracleSdoPointType - corresponds to oracle SDO_POINT_TYPE data type</li>
95   * <li>OracleSdoElemInfoArray - corresponds to oracle SDO_ELEM_INFO_ARRAY data type</li>
96   * <li>OracleSdoOridinateArray - corresponds to oracle SDO_ORDINATE_ARRAY data type</li>
97   * </ul>
98   * These classes were rendered via jpub
99   * (http://download.oracle.com/otn/utilities_drivers/jdbc/10201/jpub_102.zip)
100  * with the following command syntax:
101  * <code>
102  * ./jpub -user=YOUR_USER_ID/YOUR_PASSWORD -url=YOUR_JDBC_URL
103  *      -sql mdsys.sdo_geometry:OracleSdoGeometry,
104  *            mdsys.sdo_point_type:OracleSdoPointType,
105  *            mdsys.sdo_elem_info_array:OracleSdoElemInfoArray,
106  *            mdsys.sdo_ordinate_array:OracleSdoOrdinateArray
107  *      -dir=output_dir -methods=none -package=org.dbunit.ext.oracle -tostring=true
108  * </code>
109  * The equals and hashCode methods were then added so that the objects could be compared
110  * in test cases. Note that I did have to bash the jpub startup script (change classpath)
111  * because it assumes oracle 10g database but I ran it with 11g.  Theoretically, this
112  * process can be repeated for other custom oracle object data types.
113  * </p>
114  *
115  * @author clucas@e-miles.com
116  * @author Last changed by: $Author$
117  * @version $Revision$ $Date$
118  * @since <dbunit-version>
119  */
120 public class OracleSdoGeometryDataType extends AbstractDataType
121 {
122     /**
123      * Logger for this class
124      */
125     private static final Logger logger = LoggerFactory.getLogger(OracleSdoGeometryDataType.class);
126 
127     private static final String NULL = "NULL";
128     private static final String SDO_GEOMETRY = "SDO_GEOMETRY";
129 
130     // patterns for parsing out the various pieces of the string
131     // representation of an sdo_geometry object
132     private static final Pattern sdoGeometryPattern = Pattern.compile(
133         "^(?:MDSYS\\.)?SDO_GEOMETRY\\s*\\(\\s*([^,\\s]+)\\s*,\\s*([^,\\s]+)\\s*,\\s*");
134     private static final Pattern sdoPointTypePattern = Pattern.compile(
135         "^(?:(?:(?:MDSYS\\.)?SDO_POINT_TYPE\\s*\\(\\s*([^,\\s]+)\\s*,\\s*([^,\\s]+)\\s*,\\s*([^,\\s\\)]+)\\s*\\))|(NULL))\\s*,\\s*");
136     private static final Pattern sdoElemInfoArrayPattern = Pattern.compile(
137         "^(?:(?:(?:(?:MDSYS\\.)?SDO_ELEM_INFO_ARRAY\\s*\\(([^\\)]*)\\))|(NULL)))\\s*,\\s*");
138     private static final Pattern sdoOrdinateArrayPattern = Pattern.compile(
139         "^(?:(?:(?:(?:MDSYS\\.)?SDO_ORDINATE_ARRAY\\s*\\(([^\\)]*)\\))|(NULL)))\\s*\\)\\s*");
140 
141     OracleSdoGeometryDataType ()
142     {
143         super(SDO_GEOMETRY, Types.STRUCT, OracleSdoGeometry.class, false);
144     }
145 
146     public Object typeCast(Object value) throws TypeCastException
147     {
148         logger.debug("typeCast(value={}) - start", value);
149 
150         if (value == null || value == ITable.NO_VALUE)
151         {
152             return null;
153         }
154 
155 
156         if (value instanceof OracleSdoGeometry)
157         {
158             return (OracleSdoGeometry) value;
159         }
160 
161         if (value instanceof String)
162         {
163             // attempt to parse the SDO_GEOMETRY
164             try
165             {
166                 // all upper case for parse purposes
167                 String upperVal = ((String) value).toUpperCase().trim();
168                 if (NULL.equals(upperVal))
169                 {
170                     return null;
171                 }
172 
173                 // parse out sdo_geometry
174                 Matcher sdoGeometryMatcher = sdoGeometryPattern.matcher(upperVal);
175                 if (! sdoGeometryMatcher.find())
176                 {
177                     throw new TypeCastException(value, this);
178                 }
179                 BigDecimal gtype = NULL.equals(sdoGeometryMatcher.group(1)) ?
180                     null : new BigDecimal(sdoGeometryMatcher.group(1));
181                 BigDecimal srid = NULL.equals(sdoGeometryMatcher.group(2)) ?
182                     null : new BigDecimal(sdoGeometryMatcher.group(2));
183 
184                 // parse out sdo_point_type
185                 upperVal = upperVal.substring(sdoGeometryMatcher.end());
186                 Matcher sdoPointTypeMatcher = sdoPointTypePattern.matcher(upperVal);
187                 if (! sdoPointTypeMatcher.find())
188                 {
189                     throw new TypeCastException(value, this);
190                 }
191 
192                 OracleSdoPointType sdoPoint;
193                 if (NULL.equals(sdoPointTypeMatcher.group(4)))
194                 {
195                     sdoPoint = null;
196                 }
197                 else
198                 {
199                     sdoPoint = new OracleSdoPointType(
200                         NULL.equals(sdoPointTypeMatcher.group(1)) ? null :
201                             new BigDecimal(sdoPointTypeMatcher.group(1)),
202                         NULL.equals(sdoPointTypeMatcher.group(2)) ? null :
203                             new BigDecimal(sdoPointTypeMatcher.group(2)),
204                         NULL.equals(sdoPointTypeMatcher.group(3)) ? null :
205                             new BigDecimal(sdoPointTypeMatcher.group(3)));
206                 }
207 
208                 // parse out sdo_elem_info_array
209                 upperVal = upperVal.substring(sdoPointTypeMatcher.end());
210                 Matcher sdoElemInfoArrayMatcher = sdoElemInfoArrayPattern.matcher(upperVal);
211                 if (! sdoElemInfoArrayMatcher.find())
212                 {
213                     throw new TypeCastException(value, this);
214                 }
215 
216                 OracleSdoElemInfoArray sdoElemInfoArray;
217                 if (NULL.equals(sdoElemInfoArrayMatcher.group(2)))
218                 {
219                     sdoElemInfoArray = null;
220                 }
221                 else
222                 {
223                     String [] elemInfoStrings = sdoElemInfoArrayMatcher.group(1).
224                         trim().split("\\s*,\\s*");
225                     if (elemInfoStrings.length == 1 && "".equals(elemInfoStrings[0]))
226                     {
227                         sdoElemInfoArray = new OracleSdoElemInfoArray();
228                     }
229                     else
230                     {
231                         BigDecimal [] elemInfos = new BigDecimal[elemInfoStrings.length];
232                         for (int index = 0; index < elemInfoStrings.length; index++)
233                         {
234                             elemInfos[index] = NULL.equals(elemInfoStrings[index]) ?
235                                 null : new BigDecimal(elemInfoStrings[index]);
236                         }
237                         sdoElemInfoArray = new OracleSdoElemInfoArray(elemInfos);
238                     }
239                 }
240 
241                 // parse out sdo_ordinate_array
242                 upperVal = upperVal.substring(sdoElemInfoArrayMatcher.end());
243                 Matcher sdoOrdinateArrayMatcher = sdoOrdinateArrayPattern.matcher(upperVal);
244                 if (! sdoOrdinateArrayMatcher.find())
245                 {
246                     throw new TypeCastException(value, this);
247                 }
248 
249                 OracleSdoOrdinateArray sdoOrdinateArray;
250                 if (NULL.equals(sdoOrdinateArrayMatcher.group(2)))
251                 {
252                     sdoOrdinateArray = null;
253                 }
254                 else
255                 {
256                     String [] ordinateStrings = sdoOrdinateArrayMatcher.group(1).
257                         trim().split("\\s*,\\s*");
258                     if (ordinateStrings.length == 1 && "".equals(ordinateStrings[0]))
259                     {
260                         sdoOrdinateArray = new OracleSdoOrdinateArray();
261                     }
262                     else
263                     {
264                         BigDecimal [] ordinates = new BigDecimal[ordinateStrings.length];
265                         for (int index = 0; index < ordinateStrings.length; index++)
266                         {
267                             ordinates[index] = NULL.equals(ordinateStrings[index]) ?
268                                 null : new BigDecimal(ordinateStrings[index]);
269                         }
270                         sdoOrdinateArray = new OracleSdoOrdinateArray(ordinates);
271                     }
272                 }
273 
274                 OracleSdoGeometry sdoGeometry = new OracleSdoGeometry(
275                     gtype, srid, sdoPoint, sdoElemInfoArray, sdoOrdinateArray);
276 
277                 return sdoGeometry;
278             }
279             catch (SQLException e)
280             {
281                 throw new TypeCastException(value, this, e);
282             }
283             catch (NumberFormatException e)
284             {
285                 throw new TypeCastException(value, this, e);
286             }
287         }
288 
289         throw new TypeCastException(value, this);
290     }
291 
292 
293     public Object getSqlValue(int column, ResultSet resultSet)
294         throws SQLException, TypeCastException
295     {
296         if(logger.isDebugEnabled())
297             logger.debug("getSqlValue(column={}, resultSet={}) - start",
298             new Integer(column), resultSet);
299 
300         Object data = null;
301         try
302         {
303             data =  ((OracleResultSet) resultSet).
304                 getORAData(column, OracleSdoGeometry.getORADataFactory());
305 
306             // It would be preferable to return the actual object, but there are
307             // a few dbunit issues with this:
308             //
309             // 1. Dbunit does not support nulls for user defined types (at least
310             //    with oracle.)  PreparedStatement.setNull(int, int) is always used
311             //    but PreparedStatement.setNull(int, int, String) is required
312             //    for sdo_geometry (and other similar custom object types).
313             //
314             // 2. Dbunit does not support rendering custom objects (such as
315             //    OracleSdoGeometry) as strings.
316             //
317             // So, instead we return the object as a String or "NULL".
318 
319             // return data;
320 
321             if (data != null)
322             {
323                 return data.toString();
324             }
325             else
326             {
327                 // return a string instead of null so that it can be interpreted
328                 // in typeCast.  DBUnit does not handle PreparedStatement.setNull
329                 // for user defined types.
330                 return NULL;
331             }
332 
333         }
334         catch (SQLException e)
335         {
336             throw new TypeCastException(data, this, e);
337         }
338     }
339 
340     public void setSqlValue(Object value, int column, PreparedStatement statement)
341         throws SQLException, TypeCastException
342     {
343         Object castValue = typeCast(value);
344         if (castValue == null)
345         {
346             statement.setNull(column, OracleSdoGeometry._SQL_TYPECODE,
347                 OracleSdoGeometry._SQL_NAME);
348         }
349         else
350         {
351             ((OraclePreparedStatement) statement).setORAData(column, (ORAData) castValue);
352         }
353     }
354 
355     /**
356      * This method is copied from AbstractDataType and customized to call equals
357      * after the typeCast because OracleSdoGeometry objects are not Comparables
358      * but can test for equality (via equals method.)  It is needed for test
359      * cases that check for equality between data in xml files and data read
360      * from the database.
361      */
362     public int compare(Object o1, Object o2) throws TypeCastException
363     {
364         logger.debug("compare(o1={}, o2={}) - start", o1, o2);
365 
366         try
367         {
368             // New in 2.3: Object level check for equality - should give massive performance improvements
369             // in the most cases because the typecast can be avoided (null values and equal objects)
370             if(areObjectsEqual(o1, o2))
371             {
372                 return 0;
373             }
374 
375 
376             // Comparable check based on the results of method "typeCast"
377             Object value1 = typeCast(o1);
378             Object value2 = typeCast(o2);
379 
380             // Check for "null"s again because typeCast can produce them
381 
382             if (value1 == null && value2 == null)
383             {
384                 return 0;
385             }
386 
387             if (value1 == null && value2 != null)
388             {
389                 return -1;
390             }
391 
392             if (value1 != null && value2 == null)
393             {
394                 return 1;
395             }
396 
397             if (value1.equals(value2))
398             {
399                 return 0;
400             }
401 
402             return compareNonNulls(value1, value2);
403 
404         }
405         catch (ClassCastException e)
406         {
407             throw new TypeCastException(e);
408         }
409     }
410 
411 }