001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/tags/2.1/src/org/deegree/io/datastore/sql/oracle/OracleDatastore.java $
002 /*---------------- FILE HEADER ------------------------------------------
003 This file is part of deegree.
004 Copyright (C) 2001-2006 by:
005 Department of Geography, University of Bonn
006 http://www.giub.uni-bonn.de/deegree/
007 lat/lon GmbH
008 http://www.lat-lon.de
009
010 This library is free software; you can redistribute it and/or
011 modify it under the terms of the GNU Lesser General Public
012 License as published by the Free Software Foundation; either
013 version 2.1 of the License, or (at your option) any later version.
014
015 This library is distributed in the hope that it will be useful,
016 but WITHOUT ANY WARRANTY; without even the implied warranty of
017 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
018 Lesser General Public License for more details.
019
020 You should have received a copy of the GNU Lesser General Public
021 License along with this library; if not, write to the Free Software
022 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
023
024 Contact:
025
026 Andreas Poth
027 lat/lon GmbH
028 Aennchenstraße 19
029 53177 Bonn
030 Germany
031 E-Mail: poth@lat-lon.de
032
033 Jens Fitzke
034 lat/lon GmbH
035 Aennchenstraße 19
036 53177 Bonn
037 Germany
038 E-Mail: jens.fitzke@uni-bonn.de
039 ---------------------------------------------------------------------------*/
040
041 package org.deegree.io.datastore.sql.oracle;
042
043 import java.io.IOException;
044 import java.io.InputStream;
045 import java.sql.Connection;
046 import java.sql.PreparedStatement;
047 import java.sql.ResultSet;
048 import java.sql.SQLException;
049 import java.sql.Statement;
050 import java.util.Date;
051 import java.util.HashMap;
052 import java.util.Iterator;
053 import java.util.Map;
054 import java.util.Properties;
055
056 import oracle.spatial.geometry.JGeometry;
057 import oracle.sql.STRUCT;
058 import oracle.sql.TIMESTAMP;
059
060 import org.deegree.datatypes.Types;
061 import org.deegree.datatypes.UnknownTypeException;
062 import org.deegree.framework.log.ILogger;
063 import org.deegree.framework.log.LoggerFactory;
064 import org.deegree.framework.util.TimeTools;
065 import org.deegree.i18n.Messages;
066 import org.deegree.io.datastore.Datastore;
067 import org.deegree.io.datastore.DatastoreException;
068 import org.deegree.io.datastore.schema.MappedFeatureType;
069 import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
070 import org.deegree.io.datastore.schema.TableRelation;
071 import org.deegree.io.datastore.schema.content.ConstantContent;
072 import org.deegree.io.datastore.schema.content.FieldContent;
073 import org.deegree.io.datastore.schema.content.FunctionParam;
074 import org.deegree.io.datastore.schema.content.MappingGeometryField;
075 import org.deegree.io.datastore.schema.content.SQLFunctionCall;
076 import org.deegree.io.datastore.sql.AbstractSQLDatastore;
077 import org.deegree.io.datastore.sql.StatementBuffer;
078 import org.deegree.io.datastore.sql.TableAliasGenerator;
079 import org.deegree.io.datastore.sql.VirtualContentProvider;
080 import org.deegree.io.datastore.sql.StatementBuffer.StatementArgument;
081 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
082 import org.deegree.model.crs.CoordinateSystem;
083 import org.deegree.model.feature.FeatureCollection;
084 import org.deegree.model.filterencoding.Filter;
085 import org.deegree.model.spatialschema.Geometry;
086 import org.deegree.model.spatialschema.GeometryException;
087 import org.deegree.ogcbase.SortProperty;
088 import org.deegree.ogcwebservices.wfs.operation.Query;
089
090 /**
091 * {@link Datastore} implementation for Oracle Spatial database systems. Supports Oracle Spatial for Oracle 10g.
092 *
093 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
094 * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a>
095 * @author last edited by: $Author: mschneider $
096 *
097 * @version $Revision: 7782 $, $Date: 2007-07-17 18:40:42 +0200 (Di, 17 Jul 2007) $
098 */
099 public class OracleDatastore extends AbstractSQLDatastore {
100
101 protected static final ILogger LOG = LoggerFactory.getLogger( OracleDatastore.class );
102
103 private static final String SRS_CODE_PROP_FILE = "srs_codes_oracle.properties";
104
105 private static Map<String, Integer> nativeSrsCodeMap = new HashMap<String, Integer>();
106
107 private static final int SRS_UNDEFINED = -1;
108
109 static {
110 try {
111 initSRSCodeMap();
112 } catch ( IOException e ) {
113 String msg = "Cannot load native srs code file '" + SRS_CODE_PROP_FILE + "'.";
114 LOG.logError( msg, e );
115 }
116 }
117
118 /**
119 * Returns a specific {@link WhereBuilder} implementation for Oracle Spatial.
120 *
121 * @param rootFts
122 * involved (requested) feature types
123 * @param aliases
124 * aliases for the feature types, may be null
125 * @param filter
126 * filter that restricts the matched features
127 * @param sortProperties
128 * sort criteria for the result, may be null or empty
129 * @param aliasGenerator
130 * used to generate unique table aliases
131 * @param vcProvider
132 * @return <code>WhereBuilder</code> implementation for Oracle Spatial
133 * @throws DatastoreException
134 */
135 @Override
136 public WhereBuilder getWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
137 SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
138 VirtualContentProvider vcProvider )
139 throws DatastoreException {
140 return new OracleSpatialWhereBuilder( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
141 }
142
143 /**
144 * Converts an Oracle specific geometry <code>Object</code> from the <code>ResultSet</code> to a deegree
145 * <code>Geometry</code>.
146 *
147 * @param value
148 * @param targetCS
149 * @param conn
150 * @return corresponding deegree geometry
151 * @throws SQLException
152 */
153 @Override
154 public Geometry convertDBToDeegreeGeometry( Object value, CoordinateSystem targetCS, Connection conn )
155 throws SQLException {
156 Geometry geometry = null;
157 if ( value != null ) {
158 LOG.logDebug( "Converting STRUCT to JGeometry." );
159 JGeometry jGeometry = JGeometry.load( (STRUCT) value );
160 try {
161 LOG.logDebug( "Converting JGeometry to deegree geometry ('" + targetCS + "')" );
162 geometry = JGeometryAdapter.wrap( jGeometry, targetCS );
163 } catch ( Exception e ) {
164 throw new SQLException( "Error converting STRUCT to Geometry: " + e.getMessage() );
165 }
166 }
167 return geometry;
168 }
169
170 /**
171 * Converts a deegree <code>Geometry</code> to an Oracle specific geometry object.
172 *
173 * @param geometry
174 * @param nativeSRSCode
175 * @param conn
176 * @return corresponding Oracle specific geometry object
177 * @throws DatastoreException
178 */
179 @Override
180 public STRUCT convertDeegreeToDBGeometry( Geometry geometry, int nativeSRSCode, Connection conn )
181 throws DatastoreException {
182
183 JGeometry jGeometry = null;
184 LOG.logDebug( "Converting deegree geometry to JGeometry." );
185 try {
186 jGeometry = JGeometryAdapter.export( geometry, nativeSRSCode );
187 } catch ( GeometryException e ) {
188 throw new DatastoreException( "Error converting deegree geometry to JGeometry: " + e.getMessage(), e );
189 }
190
191 LOG.logDebug( "Converting JGeometry to STRUCT." );
192 STRUCT struct = null;
193 try {
194 struct = JGeometry.store( jGeometry, conn );
195 } catch ( SQLException e ) {
196 throw new DatastoreException( "Error converting JGeometry to STRUCT: " + e.getMessage(), e );
197 }
198 return struct;
199 }
200
201 /**
202 * Converts the given object from a <code>java.sql.ResultSet</code> column to the common type to be used as a
203 * feature property.
204 *
205 * @param rsObject
206 * @param sqlTypeCode
207 * @return an object that is suitable for a table column of the specified SQL type
208 * @throws DatastoreException
209 */
210 @Override
211 public Object convertFromDBType( Object rsObject, int sqlTypeCode )
212 throws DatastoreException {
213 Object propertyValue = rsObject;
214 try {
215 if ( rsObject instanceof TIMESTAMP ) {
216 propertyValue = ( (TIMESTAMP) rsObject ).timestampValue();
217 }
218 } catch ( SQLException e ) {
219 throw new DatastoreException( e.getMessage(), e );
220 }
221 return propertyValue;
222 }
223
224 /**
225 * Returns the next value of the given SQL sequence.
226 *
227 * @param conn
228 * JDBC connection to be used.
229 * @param sequence
230 * name of the SQL sequence
231 * @return next value of the given SQL sequence
232 * @throws DatastoreException
233 * if the value could not be retrieved
234 */
235 @Override
236 public Object getSequenceNextVal( Connection conn, String sequence )
237 throws DatastoreException {
238
239 Object nextVal = null;
240 Statement stmt = null;
241 ResultSet rs = null;
242
243 try {
244 try {
245 stmt = conn.createStatement();
246 rs = stmt.executeQuery( "SELECT " + sequence + ".nextval FROM dual" );
247 if ( rs.next() ) {
248 nextVal = rs.getObject( 1 );
249 }
250 } finally {
251 try {
252 if ( rs != null ) {
253 rs.close();
254 }
255 } finally {
256 if ( stmt != null ) {
257 stmt.close();
258 }
259 }
260 }
261 } catch ( SQLException e ) {
262 String msg = "Could not retrieve value for sequence '" + sequence + "': " + e.getMessage();
263 throw new DatastoreException( msg, e );
264 }
265 return nextVal;
266 }
267
268 /**
269 * Converts the {@link StatementBuffer} into a {@link PreparedStatement}, which is initialized and ready to be
270 * performed.
271 *
272 * TODO remove this method (use super class method instead), change handling of JGeometry
273 *
274 * @param conn
275 * connection to be used to create the <code>PreparedStatement</code>
276 * @param statementBuffer
277 * @return the <code>PreparedStatment</code>, ready to be performed
278 * @throws SQLException
279 * if a JDBC related error occurs
280 */
281 @Override
282 public PreparedStatement prepareStatement( Connection conn, StatementBuffer statementBuffer )
283 throws SQLException {
284 LOG.logDebug( "Preparing statement: " + statementBuffer.getQueryString() );
285
286 PreparedStatement preparedStatement = conn.prepareStatement( statementBuffer.getQueryString() );
287
288 Iterator it = statementBuffer.getArgumentsIterator();
289 int i = 1;
290 while ( it.hasNext() ) {
291 StatementArgument argument = (StatementArgument) it.next();
292 Object parameter = argument.getArgument();
293 int targetSqlType = argument.getTypeCode();
294 if ( parameter != null ) {
295 if ( targetSqlType == Types.DATE ) {
296 if ( parameter instanceof String ) {
297 parameter = TimeTools.createCalendar( (String) parameter ).getTime();
298 }
299 parameter = new java.sql.Date( ( (Date) parameter ).getTime() );
300 } else if ( targetSqlType == Types.TIMESTAMP ) {
301 if ( parameter instanceof String ) {
302 parameter = TimeTools.createCalendar( (String) parameter ).getTime();
303 }
304 parameter = new java.sql.Timestamp( ( (Date) parameter ).getTime() );
305 } else if ( parameter != null && parameter instanceof JGeometry ) {
306 parameter = JGeometry.store( (JGeometry) parameter, conn );
307 } else if ( targetSqlType == Types.INTEGER || targetSqlType == Types.SMALLINT
308 || targetSqlType == Types.TINYINT ) {
309 parameter = Integer.parseInt( parameter.toString() );
310 } else if ( targetSqlType == Types.DECIMAL || targetSqlType == Types.DOUBLE
311 || targetSqlType == Types.REAL || targetSqlType == Types.FLOAT ) {
312 parameter = Double.parseDouble( parameter.toString() );
313 } else if ( targetSqlType == Types.NUMERIC ) {
314 try {
315 parameter = Integer.parseInt( parameter.toString() );
316 } catch ( Exception e ) {
317 parameter = Double.parseDouble( parameter.toString() );
318 }
319 }
320 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
321 try {
322 String typeName = Types.getTypeNameForSQLTypeCode( targetSqlType );
323 LOG.logDebug( "Setting argument " + i + ": type=" + typeName + ", value class="
324 + parameter.getClass() );
325 if ( parameter instanceof String || parameter instanceof Number
326 || parameter instanceof java.sql.Date ) {
327 LOG.logDebug( "Value: " + parameter );
328 }
329 } catch ( UnknownTypeException e ) {
330 throw new SQLException( e.getMessage() );
331 }
332 }
333 preparedStatement.setObject( i, parameter, targetSqlType );
334 } else {
335 setNullValue( preparedStatement, i, targetSqlType );
336 }
337 i++;
338 }
339 return preparedStatement;
340 }
341
342 /**
343 * Transforms the incoming {@link Query} so that the {@link CoordinateSystem} of all spatial arguments (BBOX, etc.)
344 * in the {@link Filter} match the SRS of the targeted {@link MappingGeometryField}s.
345 * <p>
346 * NOTE: If this transformation can be performed by the backend (e.g. by Oracle Spatial), this method should be
347 * overwritten to return the original input {@link Query}.
348 *
349 * @param query
350 * query to be transformed
351 * @return query with spatial arguments transformed to target SRS
352 */
353 @Override
354 protected Query transformQuery( Query query ) {
355 return query;
356 }
357
358 /**
359 * Transforms the {@link FeatureCollection} so that the geometries of all contained geometry properties use the
360 * requested SRS.
361 *
362 * @param fc
363 * feature collection to be transformed
364 * @param targetSRS
365 * requested SRS
366 * @return transformed FeatureCollection
367 */
368 @Override
369 protected FeatureCollection transformResult( FeatureCollection fc, String targetSRS ) {
370 return fc;
371 }
372
373 /**
374 * Returns whether the datastore is capable of performing a native coordinate transformation (using an SQL function
375 * call for example) into the given SRS.
376 *
377 * @param targetSRS
378 * target spatial reference system (usually "EPSG:XYZ")
379 * @return true, if the datastore can perform the coordinate transformation, false otherwise
380 */
381 @Override
382 protected boolean canTransformTo( String targetSRS ) {
383 return getNativeSRSCode( targetSRS ) != SRS_UNDEFINED;
384 }
385
386 /**
387 * Returns an {@link SQLFunctionCall} that refers to the given {@link MappingGeometryField} in the specified target
388 * SRS using a database specific SQL function.
389 *
390 * @param geoProperty
391 * geometry property
392 * @param targetSRS
393 * target spatial reference system (usually "EPSG:XYZ")
394 * @return an {@link SQLFunctionCall} that refers to the geometry in the specified srs
395 * @throws DatastoreException
396 */
397 @Override
398 public SQLFunctionCall buildSRSTransformCall( MappedGeometryPropertyType geoProperty, String targetSRS )
399 throws DatastoreException {
400
401 int nativeSRSCode = getNativeSRSCode( targetSRS );
402 if ( nativeSRSCode == SRS_UNDEFINED ) {
403 String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS", this.getClass().getName(),
404 targetSRS );
405 throw new DatastoreException( msg );
406 }
407
408 MappingGeometryField field = geoProperty.getMappingField();
409 FunctionParam param1 = new FieldContent( field, new TableRelation[0] );
410 FunctionParam param2 = new ConstantContent( "" + nativeSRSCode );
411
412 SQLFunctionCall transformCall = new SQLFunctionCall( "SDO_CS.TRANSFORM($1,$2)", field.getType(), param1, param2 );
413 return transformCall;
414 }
415
416 @Override
417 public String buildSRSTransformCall( String geomIdentifier, int nativeSRSCode )
418 throws DatastoreException {
419 String call = "SDO_CS.TRANSFORM(" + geomIdentifier + "," + nativeSRSCode + ")";
420 return call;
421 }
422
423 @Override
424 public int getNativeSRSCode( String srsName ) {
425 Integer nativeSRSCode = nativeSrsCodeMap.get( srsName );
426 if ( nativeSRSCode == null ) {
427 return SRS_UNDEFINED;
428 }
429 return nativeSRSCode;
430 }
431
432 private void setNullValue( PreparedStatement preparedStatement, int i, int targetSqlType )
433 throws SQLException {
434 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
435 try {
436 String typeName = Types.getTypeNameForSQLTypeCode( targetSqlType );
437 LOG.logDebug( "Setting argument " + i + ": type=" + typeName );
438 LOG.logDebug( "Value: null" );
439 } catch ( UnknownTypeException e ) {
440 throw new SQLException( e.getMessage() );
441 }
442 }
443 preparedStatement.setNull( i, targetSqlType );
444 }
445
446 private static void initSRSCodeMap()
447 throws IOException {
448 InputStream is = OracleDatastore.class.getResourceAsStream( SRS_CODE_PROP_FILE );
449 Properties props = new Properties();
450 props.load( is );
451 for ( Object key : props.keySet() ) {
452 String nativeCodeStr = props.getProperty( (String) key ).trim();
453 try {
454 int nativeCode = Integer.parseInt( nativeCodeStr );
455 nativeSrsCodeMap.put( (String) key, nativeCode );
456 } catch ( NumberFormatException e ) {
457 String msg = Messages.getMessage( "DATASTORE_SRS_CODE_INVALID", SRS_CODE_PROP_FILE, nativeCodeStr, key );
458 throw new IOException( msg );
459 }
460 }
461 }
462 }