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