001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/io/datastore/sql/oracle/OracleDatastore.java $
002 /*----------------------------------------------------------------------------
003 This file is part of deegree, http://deegree.org/
004 Copyright (C) 2001-2009 by:
005 Department of Geography, University of Bonn
006 and
007 lat/lon GmbH
008
009 This library is free software; you can redistribute it and/or modify it under
010 the terms of the GNU Lesser General Public License as published by the Free
011 Software Foundation; either version 2.1 of the License, or (at your option)
012 any later version.
013 This library is distributed in the hope that it will be useful, but WITHOUT
014 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
015 FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
016 details.
017 You should have received a copy of the GNU Lesser General Public License
018 along with this library; if not, write to the Free Software Foundation, Inc.,
019 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020
021 Contact information:
022
023 lat/lon GmbH
024 Aennchenstr. 19, 53177 Bonn
025 Germany
026 http://lat-lon.de/
027
028 Department of Geography, University of Bonn
029 Prof. Dr. Klaus Greve
030 Postfach 1147, 53001 Bonn
031 Germany
032 http://www.geographie.uni-bonn.de/deegree/
033
034 e-mail: info@deegree.org
035 ----------------------------------------------------------------------------*/
036
037 package org.deegree.io.datastore.sql.oracle;
038
039 import java.io.IOException;
040 import java.io.InputStream;
041 import java.lang.reflect.Field;
042 import java.sql.Connection;
043 import java.sql.PreparedStatement;
044 import java.sql.ResultSet;
045 import java.sql.SQLException;
046 import java.sql.Statement;
047 import java.util.Date;
048 import java.util.HashMap;
049 import java.util.Iterator;
050 import java.util.Map;
051 import java.util.Properties;
052
053 import oracle.spatial.geometry.JGeometry;
054 import oracle.sql.STRUCT;
055 import oracle.sql.TIMESTAMP;
056
057 import org.deegree.datatypes.Types;
058 import org.deegree.datatypes.UnknownTypeException;
059 import org.deegree.framework.log.ILogger;
060 import org.deegree.framework.log.LoggerFactory;
061 import org.deegree.framework.util.TimeTools;
062 import org.deegree.i18n.Messages;
063 import org.deegree.io.datastore.Datastore;
064 import org.deegree.io.datastore.DatastoreException;
065 import org.deegree.io.datastore.schema.MappedFeatureType;
066 import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
067 import org.deegree.io.datastore.schema.TableRelation;
068 import org.deegree.io.datastore.schema.content.ConstantContent;
069 import org.deegree.io.datastore.schema.content.FieldContent;
070 import org.deegree.io.datastore.schema.content.FunctionParam;
071 import org.deegree.io.datastore.schema.content.MappingGeometryField;
072 import org.deegree.io.datastore.schema.content.SQLFunctionCall;
073 import org.deegree.io.datastore.sql.AbstractSQLDatastore;
074 import org.deegree.io.datastore.sql.StatementBuffer;
075 import org.deegree.io.datastore.sql.TableAliasGenerator;
076 import org.deegree.io.datastore.sql.VirtualContentProvider;
077 import org.deegree.io.datastore.sql.StatementBuffer.StatementArgument;
078 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
079 import org.deegree.model.crs.CoordinateSystem;
080 import org.deegree.model.feature.FeatureCollection;
081 import org.deegree.model.filterencoding.Filter;
082 import org.deegree.model.spatialschema.Geometry;
083 import org.deegree.model.spatialschema.GeometryException;
084 import org.deegree.ogcbase.SortProperty;
085 import org.deegree.ogcwebservices.wfs.operation.Query;
086
087 /**
088 * {@link Datastore} implementation for Oracle Spatial database systems. Supports Oracle Spatial for Oracle 10g.
089 *
090 * TODO Which Oracle spatial versions are supported exactly?
091 *
092 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
093 * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a>
094 * @author last edited by: $Author: hrubach $
095 *
096 * @version $Revision: 23693 $, $Date: 2010-04-20 14:33:55 +0200 (Di, 20. Apr 2010) $
097 */
098 public class OracleDatastore extends AbstractSQLDatastore {
099
100 private static final ILogger LOG = LoggerFactory.getLogger( OracleDatastore.class );
101
102 private static final String SRS_CODE_PROP_FILE = "srs_codes_oracle.properties";
103
104 private static Map<String, Integer> nativeSrsCodeMap = new HashMap<String, Integer>();
105
106 // used for identifying the last active connection (if changed, Reijer's JGeometry store workaround patch is
107 // applied)
108 private static Connection lastStoreConnection;
109
110 static {
111 try {
112 initSRSCodeMap();
113 } catch ( IOException e ) {
114 String msg = "Cannot load native srs code file '" + SRS_CODE_PROP_FILE + "'.";
115 LOG.logError( msg, e );
116 }
117 }
118
119 /**
120 * @param code
121 * an EPSG code
122 * @return the oracle code as stored in srs_codes_oracle.properties
123 */
124 public static int getOracleSRIDCode( String code ) {
125 Integer res = nativeSrsCodeMap.get( code );
126 if ( res != null ) {
127 return res.intValue();
128 }
129
130 // only in Oracle 10, but what else to do?
131 return Integer.parseInt( code.split( ":" )[1] );
132 }
133
134 /**
135 * @param srid
136 * @return an EPSG code or "-1", if none was found
137 */
138 public static String fromOracleSRIDCode( int srid ) {
139 for ( String k : nativeSrsCodeMap.keySet() ) {
140 if ( nativeSrsCodeMap.get( k ).intValue() == srid ) {
141 return k;
142 }
143 }
144
145 return "-1";
146 }
147
148 /**
149 * Returns a specific {@link WhereBuilder} implementation for Oracle Spatial.
150 *
151 * @param rootFts
152 * involved (requested) feature types
153 * @param aliases
154 * aliases for the feature types, may be null
155 * @param filter
156 * filter that restricts the matched features
157 * @param sortProperties
158 * sort criteria for the result, may be null or empty
159 * @param aliasGenerator
160 * used to generate unique table aliases
161 * @param vcProvider
162 * @return <code>WhereBuilder</code> implementation for Oracle Spatial
163 * @throws DatastoreException
164 */
165 @Override
166 public WhereBuilder getWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
167 SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
168 VirtualContentProvider vcProvider )
169 throws DatastoreException {
170 return new OracleSpatialWhereBuilder( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
171 }
172
173 /**
174 * Converts an Oracle specific geometry <code>Object</code> from the <code>ResultSet</code> to a deegree
175 * <code>Geometry</code>.
176 *
177 * @param value
178 * @param targetCS
179 * @param conn
180 * @return corresponding deegree geometry
181 * @throws SQLException
182 */
183 @Override
184 public Geometry convertDBToDeegreeGeometry( Object value, CoordinateSystem targetCS, Connection conn )
185 throws SQLException {
186 Geometry geometry = null;
187 if ( value != null ) {
188 LOG.logDebug( "Converting STRUCT to JGeometry." );
189 JGeometry jGeometry = JGeometry.load( (STRUCT) value );
190 try {
191 LOG.logDebug( "Converting JGeometry to deegree geometry ('" + targetCS + "')" );
192 geometry = JGeometryAdapter.wrap( jGeometry, targetCS );
193 } catch ( Exception e ) {
194 LOG.logError( "Error while converting STRUCT to Geometry: ", e );
195 throw new SQLException( "Error converting STRUCT to Geometry: " + e.getMessage() );
196 }
197 }
198 return geometry;
199 }
200
201 /**
202 * Converts a deegree <code>Geometry</code> to an Oracle specific geometry object.
203 *
204 * @param geometry
205 * @param nativeSRSCode
206 * @param conn
207 * @return corresponding Oracle specific geometry object
208 * @throws DatastoreException
209 */
210 @Override
211 public STRUCT convertDeegreeToDBGeometry( Geometry geometry, int nativeSRSCode, Connection conn )
212 throws DatastoreException {
213
214 JGeometry jGeometry = null;
215 LOG.logDebug( "Converting deegree geometry to JGeometry." );
216 try {
217 jGeometry = JGeometryAdapter.export( geometry, nativeSRSCode );
218 } catch ( GeometryException e ) {
219 throw new DatastoreException( "Error converting deegree geometry to JGeometry: " + e.getMessage(), e );
220 }
221
222 LOG.logDebug( "Converting JGeometry to STRUCT." );
223 STRUCT struct = null;
224 try {
225 struct = storeGeometryWithMultiConnHack( jGeometry, conn );
226 } catch ( SQLException e ) {
227 throw new DatastoreException( "Error converting JGeometry to STRUCT: " + e.getMessage(), e );
228 }
229 return struct;
230 }
231
232 /**
233 * Workaround for a known Oracle JDBC driver problem.
234 * <p>
235 * JGeometry#store() isn't working when invoked successively using different connections. This method applies a
236 * workaround (based on undocumented behaviour of the Oracle driver) to solve this problem.
237 * http://forums.oracle.com/forums/thread.jspa?messageID=1273670
238 * </p>
239 *
240 * @param geometry
241 * geometry to be stored
242 * @param connection
243 * jdbc connection
244 * @return a {@link STRUCT} to be used as query parameter
245 * @throws SQLException
246 */
247 private STRUCT storeGeometryWithMultiConnHack( JGeometry geometry, Connection connection )
248 throws SQLException {
249 synchronized ( JGeometry.class ) {
250 if ( lastStoreConnection != null && lastStoreConnection != connection ) {
251 LOG.logDebug( "JGeometry#store(...) workaround (lastStoreConnection != connection)" );
252 try {
253 Field geomDesc = JGeometry.class.getDeclaredField( "geomDesc" );
254 geomDesc.setAccessible( true );
255 geomDesc.set( null, null );
256 } catch ( Exception e ) {
257 LOG.logWarning( "Exception caught applying JGeometr#store(...) workaround: " + e.getMessage(), e );
258 }
259 }
260 lastStoreConnection = connection;
261 return JGeometry.store( geometry, connection );
262 }
263 }
264
265 /**
266 * Converts the given object from a <code>java.sql.ResultSet</code> column to the common type to be used as a
267 * feature property.
268 * <p>
269 * NOTE: String- and boolean-valued results have a special conversion handling:
270 * <ul>
271 * <li><code>Strings:</code> because we encountered difficulties when inserting empty strings "" into String-type
272 * columns with NOT NULL constraints (for example in VARCHAR2 fields), "$EMPTY_STRING$" is used to mark them.</li>
273 * <li><code>Boolean:<code>because Oracle has no special boolean type, it is assumed that a CHAR(1) column is used
274 * instead (with values 'Y'=true and 'N'=false)</li>
275 * </ul>
276 *
277 * @param rsObject
278 * @param sqlTypeCode
279 * @return an object that is suitable for a table column of the specified SQL type
280 * @throws DatastoreException
281 */
282 @Override
283 public Object convertFromDBType( Object rsObject, int sqlTypeCode )
284 throws DatastoreException {
285 Object propertyValue = rsObject;
286 try {
287 if ( rsObject instanceof TIMESTAMP ) {
288 propertyValue = ( (TIMESTAMP) rsObject ).timestampValue();
289 }
290 if ( rsObject instanceof String ) {
291 if ( rsObject.equals( "$EMPTY_STRING$" ) ) {
292 propertyValue = "";
293 }
294 if ( sqlTypeCode == Types.BOOLEAN ) {
295 String val = rsObject.toString();
296
297 if ( val.length() == 1 && val.charAt( 0 ) == 'Y' ) {
298 propertyValue = Boolean.TRUE;
299 }
300 if ( val.length() == 1 && val.charAt( 0 ) == 'N' ) {
301 propertyValue = Boolean.FALSE;
302 }
303 }
304 }
305 } catch ( SQLException e ) {
306 throw new DatastoreException( e.getMessage(), e );
307 }
308 return propertyValue;
309 }
310
311 /**
312 * Returns the next value of the given SQL sequence.
313 *
314 * @param conn
315 * JDBC connection to be used.
316 * @param sequence
317 * name of the SQL sequence
318 * @return next value of the given SQL sequence
319 * @throws DatastoreException
320 * if the value could not be retrieved
321 */
322 @Override
323 public Object getSequenceNextVal( Connection conn, String sequence )
324 throws DatastoreException {
325
326 Object nextVal = null;
327 Statement stmt = null;
328 ResultSet rs = null;
329
330 try {
331 try {
332 stmt = conn.createStatement();
333 rs = stmt.executeQuery( "SELECT " + sequence + ".nextval FROM dual" );
334 if ( rs.next() ) {
335 nextVal = rs.getObject( 1 );
336 }
337 } finally {
338 try {
339 if ( rs != null ) {
340 rs.close();
341 }
342 } finally {
343 if ( stmt != null ) {
344 stmt.close();
345 }
346 }
347 }
348 } catch ( SQLException e ) {
349 String msg = "Could not retrieve value for sequence '" + sequence + "': " + e.getMessage();
350 throw new DatastoreException( msg, e );
351 }
352 return nextVal;
353 }
354
355 /**
356 * Returns the current value (plus an offset) of the given SQL sequence.
357 *
358 * @param conn
359 * JDBC connection to be used.
360 * @param sequence
361 * name of the SQL sequence
362 * @param offset
363 * offset added to the sequence value
364 * @return current value (plus offset) of the given SQL sequence
365 * @throws DatastoreException
366 * if the value could not be retrieved
367 */
368 @Override
369 public Object getSequenceCurrValPlusOffset( Connection conn, String sequence, int offset )
370 throws DatastoreException {
371
372 Object nextVal = null;
373 Statement stmt = null;
374 ResultSet rs = null;
375
376 try {
377 try {
378 stmt = conn.createStatement();
379 rs = stmt.executeQuery( "SELECT " + sequence + ".currval + " + offset + " FROM dual" );
380 if ( rs.next() ) {
381 nextVal = rs.getObject( 1 );
382 }
383 } finally {
384 try {
385 if ( rs != null ) {
386 rs.close();
387 }
388 } finally {
389 if ( stmt != null ) {
390 stmt.close();
391 }
392 }
393 }
394 } catch ( SQLException e ) {
395 String msg = "Could not retrieve value for sequence '" + sequence + "': " + e.getMessage();
396 throw new DatastoreException( msg, e );
397 }
398 return nextVal;
399 }
400
401 /**
402 * Converts the {@link StatementBuffer} into a {@link PreparedStatement}, which is initialized and ready to be
403 * performed.
404 *
405 * TODO remove this method (use super class method instead), change handling of JGeometry NOTE: String- and
406 * boolean-valued results have a special conversion handling:
407 * <ul>
408 * <li><code>Strings:</code> because we encountered difficulties when inserting empty strings "" into String-type
409 * columns with NOT NULL constraints (for example in VARCHAR2 fields), "$EMPTY_STRING$" is used to mark them.</li>
410 * <li><code>Boolean:<code>because Oracle has no special boolean type, it is assumed that a CHAR(1) column is used
411 * instead (with values 'Y'=true and 'N'=false)</li>
412 * </ul>
413 *
414 * @param conn
415 * connection to be used to create the <code>PreparedStatement</code>
416 * @param statementBuffer
417 * @return the <code>PreparedStatment</code>, ready to be performed
418 * @throws SQLException
419 * if a JDBC related error occurs
420 */
421 @Override
422 public PreparedStatement prepareStatement( Connection conn, StatementBuffer statementBuffer )
423 throws SQLException {
424 LOG.logDebug( "Preparing statement: " + statementBuffer.getQueryString() );
425
426 PreparedStatement preparedStatement = conn.prepareStatement( statementBuffer.getQueryString() );
427
428 Iterator<StatementArgument> it = statementBuffer.getArgumentsIterator();
429 int i = 1;
430 while ( it.hasNext() ) {
431 StatementArgument argument = it.next();
432 Object parameter = argument.getArgument();
433 int targetSqlType = argument.getTypeCode();
434 if ( parameter != null ) {
435 if ( targetSqlType == Types.DATE ) {
436 if ( parameter instanceof String ) {
437 parameter = TimeTools.createCalendar( (String) parameter ).getTime();
438 }
439 parameter = new java.sql.Date( ( (Date) parameter ).getTime() );
440 } else if ( targetSqlType == Types.TIMESTAMP ) {
441 if ( parameter instanceof String ) {
442 parameter = TimeTools.createCalendar( (String) parameter ).getTime();
443 }
444 parameter = new java.sql.Timestamp( ( (Date) parameter ).getTime() );
445 } else if ( parameter != null && parameter instanceof JGeometry ) {
446 parameter = storeGeometryWithMultiConnHack( (JGeometry) parameter, conn );
447 } else if ( targetSqlType == Types.INTEGER || targetSqlType == Types.SMALLINT
448 || targetSqlType == Types.TINYINT ) {
449 parameter = Integer.parseInt( parameter.toString() );
450 } else if ( targetSqlType == Types.DECIMAL || targetSqlType == Types.DOUBLE
451 || targetSqlType == Types.REAL || targetSqlType == Types.FLOAT ) {
452 parameter = Double.parseDouble( parameter.toString() );
453 } else if ( targetSqlType == Types.NUMERIC ) {
454 try {
455 parameter = Integer.parseInt( parameter.toString() );
456 } catch ( Exception e ) {
457 parameter = Double.parseDouble( parameter.toString() );
458 }
459 } else if ( targetSqlType == Types.BOOLEAN ) {
460 // Oracle does not have a BOOLEAN datatype
461 // default maping to column of type CHAR(1)
462 // http://thinkoracle.blogspot.com/2005/07/oracle-boolean.html
463 targetSqlType = Types.CHAR;
464 if ( Boolean.parseBoolean( parameter.toString() ) ) {
465 parameter = "Y";
466 } else {
467 parameter = "N";
468 }
469 } else if ( parameter instanceof String ) {
470 // Using the empty string ("") for NOT NULL columns fails
471 // (at least using PreparedStatements)
472 // TODO implement a proper solution
473 if ( ( (String) parameter ).length() == 0 ) {
474 parameter = "$EMPTY_STRING$";
475 }
476 }
477 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
478 try {
479 String typeName = Types.getTypeNameForSQLTypeCode( targetSqlType );
480 LOG.logDebug( "Setting argument " + i + ": type=" + typeName + ", value class="
481 + parameter.getClass() );
482 if ( parameter instanceof String || parameter instanceof Number
483 || parameter instanceof java.sql.Date ) {
484 LOG.logDebug( "Value: '" + parameter + "'" );
485 }
486 } catch ( UnknownTypeException e ) {
487 throw new SQLException( e.getMessage() );
488 }
489 }
490 preparedStatement.setObject( i, parameter, targetSqlType );
491 } else {
492 setNullValue( preparedStatement, i, targetSqlType );
493 }
494 i++;
495 }
496 return preparedStatement;
497 }
498
499 /**
500 * Transforms the incoming {@link Query} so that the {@link CoordinateSystem} of all spatial arguments (BBOX, etc.)
501 * in the {@link Filter} match the SRS of the targeted {@link MappingGeometryField}s.
502 * <p>
503 * NOTE: If this transformation can be performed by the backend (e.g. by Oracle Spatial), this method should be
504 * overwritten to return the original input {@link Query}.
505 *
506 * @param query
507 * query to be transformed
508 * @return query with spatial arguments transformed to target SRS
509 */
510 @Override
511 protected Query transformQuery( Query query ) {
512 return query;
513 }
514
515 /**
516 * Transforms the {@link FeatureCollection} so that the geometries of all contained geometry properties use the
517 * requested SRS.
518 *
519 * @param fc
520 * feature collection to be transformed
521 * @param targetSRS
522 * requested SRS
523 * @return transformed FeatureCollection
524 */
525 @Override
526 protected FeatureCollection transformResult( FeatureCollection fc, String targetSRS ) {
527 return fc;
528 }
529
530 /**
531 * Returns whether the datastore is capable of performing a native coordinate transformation (using an SQL function
532 * call for example) into the given SRS.
533 *
534 * @param targetSRS
535 * target spatial reference system (usually "EPSG:XYZ")
536 * @return true, if the datastore can perform the coordinate transformation, false otherwise
537 */
538 @Override
539 protected boolean canTransformTo( String targetSRS ) {
540 return getNativeSRSCode( targetSRS ) != SRS_UNDEFINED;
541 }
542
543 /**
544 * Returns an {@link SQLFunctionCall} that refers to the given {@link MappingGeometryField} in the specified target
545 * SRS using a database specific SQL function.
546 *
547 * @param geoProperty
548 * geometry property
549 * @param targetSRS
550 * target spatial reference system (usually "EPSG:XYZ")
551 * @return an {@link SQLFunctionCall} that refers to the geometry in the specified srs
552 * @throws DatastoreException
553 */
554 @Override
555 public SQLFunctionCall buildSRSTransformCall( MappedGeometryPropertyType geoProperty, String targetSRS )
556 throws DatastoreException {
557
558 int nativeSRSCode = getNativeSRSCode( targetSRS );
559 if ( nativeSRSCode == SRS_UNDEFINED ) {
560 String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS", this.getClass().getName(),
561 targetSRS );
562 throw new DatastoreException( msg );
563 }
564
565 MappingGeometryField field = geoProperty.getMappingField();
566 FunctionParam param1 = new FieldContent( field, new TableRelation[0] );
567 FunctionParam param2 = new ConstantContent( "" + nativeSRSCode );
568
569 SQLFunctionCall transformCall = new SQLFunctionCall( "SDO_CS.TRANSFORM($1,$2)", field.getType(), param1, param2 );
570 return transformCall;
571 }
572
573 @Override
574 public String buildSRSTransformCall( String geomIdentifier, int nativeSRSCode )
575 throws DatastoreException {
576 String call = "SDO_CS.TRANSFORM(" + geomIdentifier + "," + nativeSRSCode + ")";
577 return call;
578 }
579
580 @Override
581 public int getNativeSRSCode( String srsName ) {
582 Integer nativeSRSCode = nativeSrsCodeMap.get( srsName );
583 if ( nativeSRSCode == null ) {
584 return SRS_UNDEFINED;
585 }
586 return nativeSRSCode;
587 }
588
589 private void setNullValue( PreparedStatement preparedStatement, int i, int targetSqlType )
590 throws SQLException {
591 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
592 try {
593 String typeName = Types.getTypeNameForSQLTypeCode( targetSqlType );
594 LOG.logDebug( "Setting argument " + i + ": type=" + typeName );
595 LOG.logDebug( "Value: null" );
596 } catch ( UnknownTypeException e ) {
597 throw new SQLException( e.getMessage() );
598 }
599 }
600 preparedStatement.setNull( i, targetSqlType );
601 }
602
603 private static void initSRSCodeMap()
604 throws IOException {
605 InputStream is = OracleDatastore.class.getResourceAsStream( SRS_CODE_PROP_FILE );
606 Properties props = new Properties();
607 props.load( is );
608 for ( Object key : props.keySet() ) {
609 String nativeCodeStr = props.getProperty( (String) key ).trim();
610 try {
611 int nativeCode = Integer.parseInt( nativeCodeStr );
612 nativeSrsCodeMap.put( (String) key, nativeCode );
613 } catch ( NumberFormatException e ) {
614 String msg = Messages.getMessage( "DATASTORE_SRS_CODE_INVALID", SRS_CODE_PROP_FILE, nativeCodeStr, key );
615 throw new IOException( msg );
616 }
617 }
618 }
619 }