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