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 }