001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_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: mschneider $
095 *
096 * @version $Revision: 20611 $, $Date: 2009-11-05 18:38:29 +0100 (Do, 05. Nov 2009) $
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 private static final int SRS_UNDEFINED = -1;
107
108 // used for identifying the last active connection (if changed, Reijer's JGeometry store workaround patch is
109 // applied)
110 private static Connection lastStoreConnection;
111
112 static {
113 try {
114 initSRSCodeMap();
115 } catch ( IOException e ) {
116 String msg = "Cannot load native srs code file '" + SRS_CODE_PROP_FILE + "'.";
117 LOG.logError( msg, e );
118 }
119 }
120
121 /**
122 * @param code
123 * an EPSG code
124 * @return the oracle code as stored in srs_codes_oracle.properties
125 */
126 public static int getOracleSRIDCode( String code ) {
127 Integer res = nativeSrsCodeMap.get( code );
128 if ( res != null ) {
129 return res.intValue();
130 }
131
132 // only in Oracle 10, but what else to do?
133 return Integer.parseInt( code.split( ":" )[1] );
134 }
135
136 /**
137 * @param srid
138 * @return an EPSG code or "-1", if none was found
139 */
140 public static String fromOracleSRIDCode( int srid ) {
141 for ( String k : nativeSrsCodeMap.keySet() ) {
142 if ( nativeSrsCodeMap.get( k ).intValue() == srid ) {
143 return k;
144 }
145 }
146
147 return "-1";
148 }
149
150 /**
151 * Returns a specific {@link WhereBuilder} implementation for Oracle Spatial.
152 *
153 * @param rootFts
154 * involved (requested) feature types
155 * @param aliases
156 * aliases for the feature types, may be null
157 * @param filter
158 * filter that restricts the matched features
159 * @param sortProperties
160 * sort criteria for the result, may be null or empty
161 * @param aliasGenerator
162 * used to generate unique table aliases
163 * @param vcProvider
164 * @return <code>WhereBuilder</code> implementation for Oracle Spatial
165 * @throws DatastoreException
166 */
167 @Override
168 public WhereBuilder getWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
169 SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
170 VirtualContentProvider vcProvider )
171 throws DatastoreException {
172 return new OracleSpatialWhereBuilder( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
173 }
174
175 /**
176 * Converts an Oracle specific geometry <code>Object</code> from the <code>ResultSet</code> to a deegree
177 * <code>Geometry</code>.
178 *
179 * @param value
180 * @param targetCS
181 * @param conn
182 * @return corresponding deegree geometry
183 * @throws SQLException
184 */
185 @Override
186 public Geometry convertDBToDeegreeGeometry( Object value, CoordinateSystem targetCS, Connection conn )
187 throws SQLException {
188 Geometry geometry = null;
189 if ( value != null ) {
190 LOG.logDebug( "Converting STRUCT to JGeometry." );
191 JGeometry jGeometry = JGeometry.load( (STRUCT) value );
192 try {
193 LOG.logDebug( "Converting JGeometry to deegree geometry ('" + targetCS + "')" );
194 geometry = JGeometryAdapter.wrap( jGeometry, targetCS );
195 } catch ( Exception e ) {
196 LOG.logError( "Error while converting STRUCT to Geometry: ", e );
197 throw new SQLException( "Error converting STRUCT to Geometry: " + e.getMessage() );
198 }
199 }
200 return geometry;
201 }
202
203 /**
204 * Converts a deegree <code>Geometry</code> to an Oracle specific geometry object.
205 *
206 * @param geometry
207 * @param nativeSRSCode
208 * @param conn
209 * @return corresponding Oracle specific geometry object
210 * @throws DatastoreException
211 */
212 @Override
213 public STRUCT convertDeegreeToDBGeometry( Geometry geometry, int nativeSRSCode, Connection conn )
214 throws DatastoreException {
215
216 JGeometry jGeometry = null;
217 LOG.logDebug( "Converting deegree geometry to JGeometry." );
218 try {
219 jGeometry = JGeometryAdapter.export( geometry, nativeSRSCode );
220 } catch ( GeometryException e ) {
221 throw new DatastoreException( "Error converting deegree geometry to JGeometry: " + e.getMessage(), e );
222 }
223
224 LOG.logDebug( "Converting JGeometry to STRUCT." );
225 STRUCT struct = null;
226 try {
227 struct = storeGeometryWithMultiConnHack( jGeometry, conn );
228 } catch ( SQLException e ) {
229 throw new DatastoreException( "Error converting JGeometry to STRUCT: " + e.getMessage(), e );
230 }
231 return struct;
232 }
233
234 /**
235 * Workaround for a known Oracle JDBC driver problem.
236 * <p>
237 * JGeometry#store() isn't working when invoked successively using different connections. This method applies a
238 * workaround (based on undocumented behaviour of the Oracle driver) to solve this problem.
239 * http://forums.oracle.com/forums/thread.jspa?messageID=1273670
240 * </p>
241 *
242 * @param geometry
243 * geometry to be stored
244 * @param connection
245 * jdbc connection
246 * @return a {@link STRUCT} to be used as query parameter
247 * @throws SQLException
248 */
249 private synchronized STRUCT storeGeometryWithMultiConnHack( JGeometry geometry, Connection connection )
250 throws SQLException {
251 if ( lastStoreConnection != null && lastStoreConnection != connection ) {
252 LOG.logDebug( "JGeometry#store(...) workaround (lastStoreConnection != connection)" );
253 try {
254 Field geomDesc = JGeometry.class.getDeclaredField( "geomDesc" );
255 geomDesc.setAccessible( true );
256 geomDesc.set( null, null );
257 } catch ( Exception e ) {
258 LOG.logWarning( "Exception caught applying JGeometr#store(...) workaround: " + e.getMessage(), e );
259 }
260 }
261 lastStoreConnection = connection;
262 return JGeometry.store( geometry, connection );
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 }