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 "spatial". 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 }