001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_testing/src/org/deegree/io/datastore/sde/SDEQueryHandler.java $
002 /*---------------- FILE HEADER ------------------------------------------
003
004 This file is part of deegree.
005 Copyright (C) 2006 by: M.O.S.S. Computer Grafik Systeme GmbH
006 Hohenbrunner Weg 13
007 D-82024 Taufkirchen
008 http://www.moss.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 ---------------------------------------------------------------------------*/
025 package org.deegree.io.datastore.sde;
026
027 import java.util.ArrayList;
028 import java.util.Collection;
029 import java.util.HashMap;
030 import java.util.HashSet;
031 import java.util.Iterator;
032 import java.util.List;
033 import java.util.Map;
034 import java.util.Set;
035
036 import org.deegree.datatypes.QualifiedName;
037 import org.deegree.framework.log.ILogger;
038 import org.deegree.framework.log.LoggerFactory;
039 import org.deegree.io.datastore.DatastoreException;
040 import org.deegree.io.datastore.FeatureId;
041 import org.deegree.io.datastore.PropertyPathResolver;
042 import org.deegree.io.datastore.PropertyPathResolvingException;
043 import org.deegree.io.datastore.schema.MappedFeatureType;
044 import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
045 import org.deegree.io.datastore.schema.MappedPropertyType;
046 import org.deegree.io.datastore.schema.MappedSimplePropertyType;
047 import org.deegree.io.datastore.schema.TableRelation;
048 import org.deegree.io.datastore.schema.content.MappingField;
049 import org.deegree.io.datastore.schema.content.SimpleContent;
050 import org.deegree.io.datastore.sql.TableAliasGenerator;
051 import org.deegree.io.sdeapi.SDEConnection;
052 import org.deegree.model.feature.Feature;
053 import org.deegree.model.feature.FeatureCollection;
054 import org.deegree.model.feature.FeatureFactory;
055 import org.deegree.model.feature.FeatureProperty;
056 import org.deegree.model.feature.XLinkedFeatureProperty;
057 import org.deegree.model.filterencoding.ComplexFilter;
058 import org.deegree.model.spatialschema.Geometry;
059 import org.deegree.ogcbase.PropertyPath;
060 import org.deegree.ogcwebservices.wfs.operation.Query;
061 import org.deegree.ogcwebservices.wfs.operation.GetFeature.RESULT_TYPE;
062
063 import com.esri.sde.sdk.client.SeFilter;
064 import com.esri.sde.sdk.client.SeLayer;
065 import com.esri.sde.sdk.client.SeObjectId;
066 import com.esri.sde.sdk.client.SeQuery;
067 import com.esri.sde.sdk.client.SeRow;
068 import com.esri.sde.sdk.client.SeSqlConstruct;
069 import com.esri.sde.sdk.client.SeState;
070
071 /**
072 * Special <code>QueryHandler</code> implementation for the <code>SDEDatastore</code>.
073 *
074 * @author <a href="mailto:cpollmann@moss.de">Christoph Pollmann</a>
075 * @author last edited by: $Author: mschneider $
076 *
077 * @version $Revision: 23718 $
078 */
079 public class SDEQueryHandler extends AbstractSDERequestHandler {
080
081 private static final ILogger LOG = LoggerFactory.getLogger( SDEQueryHandler.class );
082
083 /**
084 * requested feature type
085 */
086 protected MappedFeatureType rootFeatureType;
087
088 /**
089 * requested properties of the feature type
090 */
091 protected PropertyPath[] propertyNames;
092
093 /**
094 * used to build the initial SELECT (especially the WHERE-clause)
095 */
096 protected SDEWhereBuilder whereBuilder;
097
098 /**
099 * key: feature id of features that are generated or are in generation
100 */
101 protected Set<FeatureId> featuresInGeneration = new HashSet<FeatureId>();
102
103 /**
104 * key: feature id value, value: Feature
105 */
106 protected Map<String, Feature> featureMap = new HashMap<String, Feature>( 1000 );
107
108 /**
109 * value: XLinkedFeatureProperty
110 */
111 private Collection<XLinkedFeatureProperty> xlinkProperties = new ArrayList<XLinkedFeatureProperty>();
112
113 private Query query = null;
114
115 /**
116 * Creates a new instance of <code>SDEQueryHandler</code> from the given parameters.
117 *
118 * @param datastore
119 * datastore that spawned this QueryHandler
120 * @param aliasGenerator
121 * used to generate unique aliases for the tables in the SELECT statements
122 * @param conn
123 * SDEConnection to execute the generated SELECT statements against
124 * @param rootFts
125 * queried feature types
126 * @param query
127 * Query to perform
128 * @throws DatastoreException
129 */
130 public SDEQueryHandler( SDEDatastore datastore, TableAliasGenerator aliasGenerator, SDEConnection conn,
131 MappedFeatureType[] rootFts, Query query ) throws DatastoreException {
132 super( datastore, aliasGenerator, conn );
133 this.rootFeatureType = rootFts[0];
134 this.propertyNames = PropertyPathResolver.normalizePropertyPaths( rootFeatureType, null,
135 query.getPropertyNames() );
136 this.whereBuilder = this.datastore.getWhereBuilder( rootFts, query.getAliases(), query.getFilter(),
137 aliasGenerator );
138 this.aliasGenerator = aliasGenerator;
139 this.query = query;
140 }
141
142 /**
143 * Performs the associated <code>Query</code> against the datastore.
144 *
145 * @return collection of requested features
146 * @throws DatastoreException
147 */
148 public FeatureCollection performQuery()
149 throws DatastoreException {
150
151 FeatureCollection result = null;
152 if ( this.query.getResultType() == RESULT_TYPE.HITS ) {
153 // TODO
154 } else {
155 result = performContentQuery();
156 }
157
158 return result;
159 }
160
161 /**
162 * Performs a query for the feature instances that match the query constraints. This corresponds to a query with
163 * resultType=RESULTS.
164 *
165 * @return a feature collection containing the features that match the query constraints
166 * @throws PropertyPathResolvingException
167 * @throws DatastoreException
168 */
169 private FeatureCollection performContentQuery()
170 throws PropertyPathResolvingException, DatastoreException {
171
172 FeatureCollection result = FeatureFactory.createFeatureCollection( "ID", 10000 );
173 String[] columns;
174 // TODO respect alias
175 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropertyMap = PropertyPathResolver.determineFetchProperties(
176 this.rootFeatureType,
177 null,
178 this.propertyNames );
179 MappedPropertyType[] requestedProperties = new MappedPropertyType[requestedPropertyMap.size()];
180 requestedProperties = requestedPropertyMap.keySet().toArray( requestedProperties );
181
182 Map<String, List<MappingField>> columnsMap = buildColumnsMap( this.rootFeatureType, requestedProperties, true );
183 columns = columnsMap.keySet().toArray( new String[columnsMap.size()] );
184 Map<MappingField,Integer> mappingFieldsMap = buildMappingFieldMap( columns, columnsMap );
185
186 SeQuery stmt = buildInitialSelect( columns );
187 Object[] resultValues = new Object[columns.length];
188
189 // necessary to handle that a feature may occur several times in result set
190 Set<FeatureId> rootFeatureIds = new HashSet<FeatureId>();
191
192 try {
193 int maxFeatures = this.query.getMaxFeatures();
194 int startPosition = this.query.getStartPosition();
195 int rowCount = 0;
196 stmt.execute();
197 SeRow row = null;
198 if ( maxFeatures != -1 ) {
199 if ( startPosition < 0 ) {
200 startPosition = 0;
201 }
202 }
203 for ( ;; ) {
204 try {
205 row = stmt.fetch();
206 } catch ( Exception e ) {
207 row = null;
208 }
209 if ( null == row )
210 break;
211 rowCount++;
212 if ( rowCount < startPosition )
213 continue;
214 // collect result values
215 for ( int i = 0; i < resultValues.length; i++ ) {
216 try {
217 resultValues[i] = row.getObject( i );
218 } catch ( Exception e ) {
219 //never happens???
220 }
221 }
222 FeatureId fid = extractFeatureId( this.rootFeatureType, mappingFieldsMap, resultValues );
223
224 // skip it if this root feature has already been fetched
225 if ( !rootFeatureIds.contains( fid ) ) {
226
227 // feature also may have been fetched already as subfeature
228 Feature feature = this.featureMap.get( fid );
229 if ( feature == null ) {
230 feature = extractFeature( fid, this.rootFeatureType, requestedPropertyMap, mappingFieldsMap,
231 resultValues );
232 }
233 result.add( feature );
234 }
235 }
236 } catch( Exception e ){
237 throw new DatastoreException( e );
238 }finally {
239 try {
240 if ( stmt != null ) {
241 stmt.close();
242 }
243 } catch ( Exception exc2 ) {
244 throw new DatastoreException( exc2 );
245 }
246 }
247 resolveXLinks();
248 result.setAttribute( "numberOfFeatures", "" + result.size() );
249 return result;
250 }
251
252 /**
253 * @throws DatastoreException
254 */
255 protected void resolveXLinks()
256 throws DatastoreException {
257 for( XLinkedFeatureProperty property : this.xlinkProperties ){
258 Feature feature = this.featureMap.get( property.getTargetFeatureId() );
259 if ( feature == null ) {
260 throw new DatastoreException( "Internal error in QueryHandler." );
261 }
262 property.setValue( feature );
263 }
264 }
265
266 /**
267 * Builds the initial SELECT statement.
268 * <p>
269 * This statement determines all feature ids that are affected by the filter, but also SELECTs all properties that
270 * are stored in the root feature type's table (to improve efficiency).
271 * </p>
272 * <p>
273 * The statement is structured like this:
274 * <ul>
275 * <li><code>SELECT</code></li>
276 * <li>comma-separated list of selected qualified fields</li>
277 * <li><code>FROM</code></li>
278 * <li>comma-separated list of tables and their aliases (this is needed to constrain the paths to selected
279 * XPath-PropertyNames)</li>
280 * <li><code>WHERE</code></li>
281 * <li>SQL representation of the Filter expression</li>
282 * <li><code>ORDER BY</code></li>
283 * <li>qualified sort criteria fields</li>
284 * </ul>
285 * </p>
286 *
287 * @param columns
288 * @return the initial query
289 */
290 protected SeQuery buildInitialSelect( String[] columns ) {
291 SeQuery query = null;
292 try {
293 StringBuffer whereCondition = new StringBuffer();
294 whereBuilder.appendWhereCondition( whereCondition );
295 SeSqlConstruct constr = new SeSqlConstruct( rootFeatureType.getTable(), whereCondition.toString() );
296 query = new SeQuery( getConnection().getConnection(), columns, constr );
297 query.setState( getConnection().getState().getId(), new SeObjectId( SeState.SE_NULL_STATE_ID ),
298 SeState.SE_STATE_DIFF_NOCHECK );
299
300 query.prepareQuery();
301
302 if ( this.query.getFilter() instanceof ComplexFilter ) {
303 // There is NO chance, to make a new SeCoordinateReference equal to the existing crs
304 // of the requested layer.
305 // So, we give it a chance, by passing the layer definitions (and its associated
306 // crs) to the whereBuilder method
307 List<SeLayer> layers = getConnection().getConnection().getLayers();
308 SeFilter[] spatialFilter = whereBuilder.buildSpatialFilter( (ComplexFilter) this.query.getFilter(),
309 layers );
310 if ( null != spatialFilter && 0 < spatialFilter.length ) {
311 query.setSpatialConstraints( SeQuery.SE_OPTIMIZE, false, spatialFilter );
312 }
313 }
314 } catch ( Exception e ) {
315 e.printStackTrace();
316 }
317
318 // append sort criteria (simple implementation)
319 // TODO implement sort as comparison operator in feature collection
320 // because fetching of subfeatures can not be handled here
321 // sort only provides ascendig sorting at the moment!
322 /*
323 * TODO!!!! SortProperty[] sortProperties = query.getSortProperties(); if (null != sortProperties && 0 <
324 * sortProperties.length) { String[] sortColumns = new String[sortProperties.length]; for ( int i = 0; i <
325 * sortProperties.length; i++ ) { PropertyPath pp = sortProperties[i].getSortProperty(); PropertyPath npp =
326 * PropertyPathResolver.normalizePropertyPath( rootFeatureType, pp ); QualifiedName propertyName = npp.getStep(
327 * 1 ).getPropertyName(); PropertyType property = rootFeatureType.getProperty( propertyName ); PropertyContent[]
328 * contents = ( (MappedPropertyType) property ).getContents(); if ( contents[0] instanceof SimplePropertyContent ) {
329 * sortColumns[i] = ( (SimplePropertyContent) contents[0] ).getMappingField().getField(); } } querybuf.append("
330 * ORDER BY "); appendColumnsList( querybuf, sortColumns ); }
331 */
332 return query;
333 }
334
335 /**
336 * Builds a SELECT statement to fetch features / properties that are stored in a related table.
337 *
338 * @param columns
339 * table column names to fetch
340 * @param relations
341 * table relations that lead to the table where the property is stored
342 * @param resultValues
343 * all retrieved columns from one result set row
344 * @param mappingFieldMap
345 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
346 * @return the statement or null if the keys in resultValues contain NULL values
347 */
348 private SeQuery buildSubsequentSelect( String[] columns, TableRelation[] relations, Object[] resultValues,
349 Map<MappingField, Integer> mappingFieldMap ) {
350 SeQuery query = null;
351 try {
352 StringBuffer whereCondition = new StringBuffer();
353
354 // joins can't be used in versioned SDEs (so only one join level possible)
355
356 // append key constraints
357 MappingField[] fromFields = relations[0].getFromFields();
358 MappingField[] toFields = relations[0].getToFields();
359 for ( int i = 0; i < fromFields.length; i++ ) {
360 Integer resultPos = mappingFieldMap.get( fromFields[i] );
361 Object keyValue = resultValues[resultPos.intValue()];
362 if ( keyValue == null ) {
363 return null;
364 }
365 whereCondition.append( toFields[i].getField() );
366 whereCondition.append( "='" + keyValue.toString() + "'" );
367 if ( i != fromFields.length - 1 ) {
368 whereCondition.append( " AND " );
369 }
370 }
371 SeSqlConstruct constr = new SeSqlConstruct( relations[0].getToTable(), whereCondition.toString() );
372
373 query = new SeQuery( getConnection().getConnection(), columns, constr );
374 query.setState( getConnection().getState().getId(), new SeObjectId( SeState.SE_NULL_STATE_ID ),
375 SeState.SE_STATE_DIFF_NOCHECK );
376 query.prepareQuery();
377 } catch ( Exception e ) {
378 e.printStackTrace();
379 }
380 return query;
381 }
382
383 /**
384 * Extracts a feature from the values of a result set row.
385 *
386 * @param fid
387 * feature id of the feature
388 * @param featureType
389 * feature type of the feature to be extracted
390 * @param requestedPropertyMap
391 * requested <code>MappedPropertyType</code>s mapped to <code>Collection</code> of
392 * <code>PropertyPath</code>s
393 * @param mappingFieldsMap
394 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
395 * @param resultValues
396 * all retrieved columns from one result set row
397 * @return the extracted feature
398 * @throws DatastoreException
399 */
400 protected Feature extractFeature( FeatureId fid, MappedFeatureType featureType,
401 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropertyMap,
402 Map<MappingField,Integer> mappingFieldsMap, Object[] resultValues )
403 throws DatastoreException {
404
405 this.featuresInGeneration.add( fid );
406
407 // extract the requested properties of the feature
408 List<FeatureProperty> propertyList = new ArrayList<FeatureProperty>();
409 Iterator<MappedPropertyType> propertyIter = requestedPropertyMap.keySet().iterator();
410 while ( propertyIter.hasNext() ) {
411 MappedPropertyType requestedProperty = propertyIter.next();
412 // PropertyPath[] requestingPaths = PropertyPathResolver.determineSubPropertyPaths
413 // (requestedProperty, propertyPaths);
414 Collection<FeatureProperty> props = extractProperties( requestedProperty, mappingFieldsMap, resultValues );
415 propertyList.addAll( props );
416 }
417 FeatureProperty[] properties = propertyList.toArray( new FeatureProperty[propertyList.size()] );
418 Feature feature = FeatureFactory.createFeature( fid.getAsString(), featureType, properties );
419
420 this.featureMap.put( fid.getAsString(), feature );
421 return feature;
422 }
423
424 /**
425 * Extracts the feature id from the values of a result set row.
426 *
427 * @param ft
428 * feature type for which the id shall be extracted
429 * @param mappingFieldMap
430 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
431 * @param resultValues
432 * all retrieved columns from one result set row
433 * @return the feature id
434 */
435 protected FeatureId extractFeatureId( MappedFeatureType ft, Map<MappingField, Integer> mappingFieldMap, Object[] resultValues ) {
436 MappingField[] idFields = ft.getGMLId().getIdFields();
437 Object[] idValues = new Object[idFields.length];
438 for ( int i = 0; i < idFields.length; i++ ) {
439 Integer resultPos = mappingFieldMap.get( idFields[i] );
440 idValues[i] = resultValues[resultPos.intValue()];
441 }
442 return new FeatureId( ft, idValues );
443 }
444
445 /**
446 * Extracts the properties of the given property type from the values of a result set row. If the property is stored
447 * in related table, only the key values are present in the result set row and more SELECTs are built and executed
448 * to build the property.
449 * <p>
450 * FYI: If the property is not stored in a related table, only one FeatureProperty is built, otherwise the number of
451 * properties depends on the multiplicity of the relation.
452 *
453 * @param propertyType
454 * the mapped property type to be extracted
455 * @param mappingFieldMap
456 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
457 * @param resultValues
458 * all retrieved columns from one result set row
459 * @return Collection of FeatureProperty instances
460 *
461 * @throws DatastoreException
462 */
463 private Collection<FeatureProperty> extractProperties( MappedPropertyType propertyType,
464 Map<MappingField, Integer> mappingFieldMap,
465 Object[] resultValues )
466 throws DatastoreException {
467 Collection<FeatureProperty> result = null;
468
469 TableRelation[] tableRelations = propertyType.getTableRelations();
470 if ( tableRelations != null && tableRelations.length != 0 ) {
471 LOG.logDebug( "Fetching related properties: '" + propertyType.getName() + "'..." );
472 result = fetchRelatedProperties( propertyType.getName(), propertyType, mappingFieldMap, resultValues );
473 } else {
474 Object propertyValue = null;
475 if ( propertyType instanceof MappedSimplePropertyType ) {
476 SimpleContent content = ( (MappedSimplePropertyType) propertyType ).getContent();
477 if ( content instanceof MappingField ) {
478 MappingField field = (MappingField) content;
479 Integer resultPos = mappingFieldMap.get( field );
480 propertyValue = resultValues[resultPos.intValue()];
481 }
482 } else if ( propertyType instanceof MappedGeometryPropertyType ) {
483 MappedGeometryPropertyType geomPropertyType = (MappedGeometryPropertyType) propertyType;
484 MappingField field = geomPropertyType.getMappingField();
485 Integer resultPos = mappingFieldMap.get( field );
486 propertyValue = resultValues[resultPos.intValue()];
487 propertyValue = this.datastore.convertDBToDegreeGeometry( propertyValue, geomPropertyType.getCS() );
488 } else {
489 String msg = "Unsupported property type: '" + propertyType.getClass().getName()
490 + "' in QueryHandler.extractProperties(). ";
491 LOG.logError( msg );
492 throw new IllegalArgumentException( msg );
493 }
494 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyType.getName(), propertyValue );
495 result = new ArrayList<FeatureProperty>();
496 result.add( property );
497 }
498 return result;
499 }
500
501 /**
502 *
503 * @param propertyName
504 * @param pt
505 * @param mappingFieldMap
506 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
507 * @param resultValues
508 * all retrieved columns from one result set row
509 * @return Collection of FeatureProperty instances
510 * @throws DatastoreException
511 */
512 private Collection<FeatureProperty> fetchRelatedProperties( QualifiedName propertyName, MappedPropertyType pt,
513 Map<MappingField, Integer> mappingFieldMap, Object[] resultValues ) throws DatastoreException {
514 Collection<FeatureProperty> result = new ArrayList<FeatureProperty>( 100 );
515 SeQuery stmt = null;
516 SeRow row = null;
517 try {
518 if ( pt instanceof MappedSimplePropertyType ) {
519
520 SimpleContent content = ( (MappedSimplePropertyType) pt ).getContent();
521 if ( content instanceof MappingField ) {
522 String column = ( (MappingField) content ).getField();
523 stmt = buildSubsequentSelect( new String[] { column }, pt.getTableRelations(), resultValues,
524 mappingFieldMap );
525 if ( stmt != null ) {
526 stmt.execute();
527 for ( ;; ) {
528 try {
529 row = stmt.fetch();
530 } catch ( Exception e ) {
531 row = null;
532 }
533 if ( null == row )
534 break;
535 Object propertyValue = row.getObject( 0 );
536 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyName,
537 propertyValue );
538 result.add( property );
539 }
540 }
541 }
542 } else if ( pt instanceof MappedGeometryPropertyType ) {
543 MappedGeometryPropertyType geomPropertyType = (MappedGeometryPropertyType) pt;
544 String column = geomPropertyType.getMappingField().getField();
545
546 stmt = buildSubsequentSelect( new String[] { column }, pt.getTableRelations(), resultValues,
547 mappingFieldMap );
548 if ( stmt != null ) {
549 stmt.execute();
550 for ( ;; ) {
551 try {
552 row = stmt.fetch();
553 } catch ( Exception e ) {
554 row = null;
555 }
556 if ( null == row )
557 break;
558 Object value = row.getObject( 0 );
559 Geometry geometry = this.datastore.convertDBToDegreeGeometry( value, geomPropertyType.getCS() );
560 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyName, geometry );
561 result.add( property );
562 }
563 }
564 } else {
565 String msg = "Unsupported content type: '" + pt.getClass().getName()
566 + "' in QueryHandler.fetchRelatedProperties().";
567 LOG.logError( msg );
568 throw new DatastoreException( msg );
569 }
570 } catch ( Exception exc ) {
571 throw new DatastoreException( exc );
572 } finally {
573 try {
574 if ( stmt != null ) {
575 stmt.close();
576 }
577 } catch ( Exception exc2 ) {
578 throw new DatastoreException( exc2 );
579 }
580 }
581 return result;
582 }
583 }