001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_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: rbezema $
076 *
077 * @version $Revision: 12183 $
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 //nottin
238 }finally {
239 try {
240 if ( stmt != null ) {
241 stmt.close();
242 }
243 } catch ( Exception exc2 ) {
244 //nothing todo?
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 if ( this.query.getFilter() instanceof ComplexFilter ) {
300 // There is NO chance, to make a new SeCoordinateReference equal to the existing crs
301 // of the requested layer.
302 // So, we give it a chance, by passing the layer definitions (and its associated
303 // crs) to the whereBuilder method
304 List<SeLayer> layers = getConnection().getConnection().getLayers();
305 SeFilter[] spatialFilter = whereBuilder.buildSpatialFilter( (ComplexFilter) this.query.getFilter(),
306 layers );
307 if ( null != spatialFilter && 0 < spatialFilter.length ) {
308 query.setSpatialConstraints( SeQuery.SE_OPTIMIZE, false, spatialFilter );
309 }
310 }
311 query.prepareQuery();
312 } catch ( Exception e ) {
313 e.printStackTrace();
314 }
315
316 // append sort criteria (simple implementation)
317 // TODO implement sort as comparison operator in feature collection
318 // because fetching of subfeatures can not be handled here
319 // sort only provides ascendig sorting at the moment!
320 /*
321 * TODO!!!! SortProperty[] sortProperties = query.getSortProperties(); if (null != sortProperties && 0 <
322 * sortProperties.length) { String[] sortColumns = new String[sortProperties.length]; for ( int i = 0; i <
323 * sortProperties.length; i++ ) { PropertyPath pp = sortProperties[i].getSortProperty(); PropertyPath npp =
324 * PropertyPathResolver.normalizePropertyPath( rootFeatureType, pp ); QualifiedName propertyName = npp.getStep(
325 * 1 ).getPropertyName(); PropertyType property = rootFeatureType.getProperty( propertyName ); PropertyContent[]
326 * contents = ( (MappedPropertyType) property ).getContents(); if ( contents[0] instanceof SimplePropertyContent ) {
327 * sortColumns[i] = ( (SimplePropertyContent) contents[0] ).getMappingField().getField(); } } querybuf.append("
328 * ORDER BY "); appendColumnsList( querybuf, sortColumns ); }
329 */
330 return query;
331 }
332
333 /**
334 * Builds a SELECT statement to fetch features / properties that are stored in a related table.
335 *
336 * @param columns
337 * table column names to fetch
338 * @param relations
339 * table relations that lead to the table where the property is stored
340 * @param resultValues
341 * all retrieved columns from one result set row
342 * @param mappingFieldMap
343 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
344 * @return the statement or null if the keys in resultValues contain NULL values
345 */
346 private SeQuery buildSubsequentSelect( String[] columns, TableRelation[] relations, Object[] resultValues,
347 Map<MappingField, Integer> mappingFieldMap ) {
348 SeQuery query = null;
349 try {
350 StringBuffer whereCondition = new StringBuffer();
351
352 // joins can't be used in versioned SDEs (so only one join level possible)
353
354 // append key constraints
355 MappingField[] fromFields = relations[0].getFromFields();
356 MappingField[] toFields = relations[0].getToFields();
357 for ( int i = 0; i < fromFields.length; i++ ) {
358 Integer resultPos = mappingFieldMap.get( fromFields[i] );
359 Object keyValue = resultValues[resultPos.intValue()];
360 if ( keyValue == null ) {
361 return null;
362 }
363 whereCondition.append( toFields[i].getField() );
364 whereCondition.append( "='" + keyValue.toString() + "'" );
365 if ( i != fromFields.length - 1 ) {
366 whereCondition.append( " AND " );
367 }
368 }
369 SeSqlConstruct constr = new SeSqlConstruct( relations[0].getToTable(), whereCondition.toString() );
370
371 query = new SeQuery( getConnection().getConnection(), columns, constr );
372 query.setState( getConnection().getState().getId(), new SeObjectId( SeState.SE_NULL_STATE_ID ),
373 SeState.SE_STATE_DIFF_NOCHECK );
374 query.prepareQuery();
375 } catch ( Exception e ) {
376 e.printStackTrace();
377 }
378 return query;
379 }
380
381 /**
382 * Extracts a feature from the values of a result set row.
383 *
384 * @param fid
385 * feature id of the feature
386 * @param featureType
387 * feature type of the feature to be extracted
388 * @param requestedPropertyMap
389 * requested <code>MappedPropertyType</code>s mapped to <code>Collection</code> of
390 * <code>PropertyPath</code>s
391 * @param mappingFieldsMap
392 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
393 * @param resultValues
394 * all retrieved columns from one result set row
395 * @return the extracted feature
396 * @throws DatastoreException
397 */
398 protected Feature extractFeature( FeatureId fid, MappedFeatureType featureType,
399 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropertyMap,
400 Map<MappingField,Integer> mappingFieldsMap, Object[] resultValues )
401 throws DatastoreException {
402
403 this.featuresInGeneration.add( fid );
404
405 // extract the requested properties of the feature
406 List<FeatureProperty> propertyList = new ArrayList<FeatureProperty>();
407 Iterator<MappedPropertyType> propertyIter = requestedPropertyMap.keySet().iterator();
408 while ( propertyIter.hasNext() ) {
409 MappedPropertyType requestedProperty = propertyIter.next();
410 // PropertyPath[] requestingPaths = PropertyPathResolver.determineSubPropertyPaths
411 // (requestedProperty, propertyPaths);
412 Collection<FeatureProperty> props = extractProperties( requestedProperty, mappingFieldsMap, resultValues );
413 propertyList.addAll( props );
414 }
415 FeatureProperty[] properties = propertyList.toArray( new FeatureProperty[propertyList.size()] );
416 Feature feature = FeatureFactory.createFeature( fid.getAsString(), featureType, properties );
417
418 this.featureMap.put( fid.getAsString(), feature );
419 return feature;
420 }
421
422 /**
423 * Extracts the feature id from the values of a result set row.
424 *
425 * @param ft
426 * feature type for which the id shall be extracted
427 * @param mappingFieldMap
428 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
429 * @param resultValues
430 * all retrieved columns from one result set row
431 * @return the feature id
432 */
433 protected FeatureId extractFeatureId( MappedFeatureType ft, Map<MappingField, Integer> mappingFieldMap, Object[] resultValues ) {
434 MappingField[] idFields = ft.getGMLId().getIdFields();
435 Object[] idValues = new Object[idFields.length];
436 for ( int i = 0; i < idFields.length; i++ ) {
437 Integer resultPos = mappingFieldMap.get( idFields[i] );
438 idValues[i] = resultValues[resultPos.intValue()];
439 }
440 return new FeatureId( ft, idValues );
441 }
442
443 /**
444 * Extracts the properties of the given property type from the values of a result set row. If the property is stored
445 * in related table, only the key values are present in the result set row and more SELECTs are built and executed
446 * to build the property.
447 * <p>
448 * FYI: If the property is not stored in a related table, only one FeatureProperty is built, otherwise the number of
449 * properties depends on the multiplicity of the relation.
450 *
451 * @param propertyType
452 * the mapped property type to be extracted
453 * @param mappingFieldMap
454 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
455 * @param resultValues
456 * all retrieved columns from one result set row
457 * @return Collection of FeatureProperty instances
458 *
459 * @throws DatastoreException
460 */
461 private Collection<FeatureProperty> extractProperties( MappedPropertyType propertyType,
462 Map<MappingField, Integer> mappingFieldMap,
463 Object[] resultValues )
464 throws DatastoreException {
465 Collection<FeatureProperty> result = null;
466
467 TableRelation[] tableRelations = propertyType.getTableRelations();
468 if ( tableRelations != null && tableRelations.length != 0 ) {
469 LOG.logDebug( "Fetching related properties: '" + propertyType.getName() + "'..." );
470 result = fetchRelatedProperties( propertyType.getName(), propertyType, mappingFieldMap, resultValues );
471 } else {
472 Object propertyValue = null;
473 if ( propertyType instanceof MappedSimplePropertyType ) {
474 SimpleContent content = ( (MappedSimplePropertyType) propertyType ).getContent();
475 if ( content instanceof MappingField ) {
476 MappingField field = (MappingField) content;
477 Integer resultPos = mappingFieldMap.get( field );
478 propertyValue = resultValues[resultPos.intValue()];
479 }
480 } else if ( propertyType instanceof MappedGeometryPropertyType ) {
481 MappingField field = ( (MappedGeometryPropertyType) propertyType ).getMappingField();
482 Integer resultPos = mappingFieldMap.get( field );
483 propertyValue = resultValues[resultPos.intValue()];
484 propertyValue = this.datastore.convertDBToDegreeGeometry( propertyValue );
485 } else {
486 String msg = "Unsupported property type: '" + propertyType.getClass().getName()
487 + "' in QueryHandler.extractProperties(). ";
488 LOG.logError( msg );
489 throw new IllegalArgumentException( msg );
490 }
491 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyType.getName(), propertyValue );
492 result = new ArrayList<FeatureProperty>();
493 result.add( property );
494 }
495 return result;
496 }
497
498 /**
499 *
500 * @param propertyName
501 * @param pt
502 * @param mappingFieldMap
503 * key class: MappingField, value class: Integer (this is the associated index in resultValues)
504 * @param resultValues
505 * all retrieved columns from one result set row
506 * @return Collection of FeatureProperty instances
507 */
508 private Collection<FeatureProperty> fetchRelatedProperties( QualifiedName propertyName, MappedPropertyType pt,
509 Map<MappingField, Integer> mappingFieldMap, Object[] resultValues ) {
510 Collection<FeatureProperty> result = new ArrayList<FeatureProperty>( 100 );
511 SeQuery stmt = null;
512 SeRow row = null;
513 try {
514 if ( pt instanceof MappedSimplePropertyType ) {
515
516 SimpleContent content = ( (MappedSimplePropertyType) pt ).getContent();
517 if ( content instanceof MappingField ) {
518 String column = ( (MappingField) content ).getField();
519 stmt = buildSubsequentSelect( new String[] { column }, pt.getTableRelations(), resultValues,
520 mappingFieldMap );
521 if ( stmt != null ) {
522 stmt.execute();
523 for ( ;; ) {
524 try {
525 row = stmt.fetch();
526 } catch ( Exception e ) {
527 row = null;
528 }
529 if ( null == row )
530 break;
531 Object propertyValue = row.getObject( 0 );
532 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyName,
533 propertyValue );
534 result.add( property );
535 }
536 }
537 }
538 } else if ( pt instanceof MappedGeometryPropertyType ) {
539 String column = ( (MappedGeometryPropertyType) pt ).getMappingField().getField();
540
541 stmt = buildSubsequentSelect( new String[] { column }, pt.getTableRelations(), resultValues,
542 mappingFieldMap );
543 if ( stmt != null ) {
544 stmt.execute();
545 for ( ;; ) {
546 try {
547 row = stmt.fetch();
548 } catch ( Exception e ) {
549 row = null;
550 }
551 if ( null == row )
552 break;
553 Object value = row.getObject( 0 );
554 Geometry geometry = this.datastore.convertDBToDegreeGeometry( value );
555 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyName, geometry );
556 result.add( property );
557 }
558 }
559 } else {
560 String msg = "Unsupported content type: '" + pt.getClass().getName()
561 + "' in QueryHandler.fetchRelatedProperties().";
562 LOG.logError( msg );
563 throw new DatastoreException( msg );
564 }
565 } catch ( Exception exc ) {
566 //notting?
567 } finally {
568 try {
569 if ( stmt != null ) {
570 stmt.close();
571 }
572 } catch ( Exception exc2 ) {
573 //and again nothing!
574 }
575 }
576 return result;
577 }
578 }