001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_testing/src/org/deegree/io/datastore/sql/FeatureFetcher.java $ 002 /*---------------------------------------------------------------------------- 003 This file is part of deegree, http://deegree.org/ 004 Copyright (C) 2001-2009 by: 005 Department of Geography, University of Bonn 006 and 007 lat/lon GmbH 008 009 This library is free software; you can redistribute it and/or modify it under 010 the terms of the GNU Lesser General Public License as published by the Free 011 Software Foundation; either version 2.1 of the License, or (at your option) 012 any later version. 013 This library is distributed in the hope that it will be useful, but WITHOUT 014 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS 015 FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more 016 details. 017 You should have received a copy of the GNU Lesser General Public License 018 along with this library; if not, write to the Free Software Foundation, Inc., 019 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA 020 021 Contact information: 022 023 lat/lon GmbH 024 Aennchenstr. 19, 53177 Bonn 025 Germany 026 http://lat-lon.de/ 027 028 Department of Geography, University of Bonn 029 Prof. Dr. Klaus Greve 030 Postfach 1147, 53001 Bonn 031 Germany 032 http://www.geographie.uni-bonn.de/deegree/ 033 034 e-mail: info@deegree.org 035 ----------------------------------------------------------------------------*/ 036 package org.deegree.io.datastore.sql; 037 038 import static org.deegree.io.datastore.PropertyPathResolver.determineFetchProperties; 039 import static org.deegree.model.feature.FeatureFactory.createFeatureProperty; 040 041 import java.net.MalformedURLException; 042 import java.net.URL; 043 import java.sql.Connection; 044 import java.sql.PreparedStatement; 045 import java.sql.ResultSet; 046 import java.sql.SQLException; 047 import java.util.ArrayList; 048 import java.util.Collection; 049 import java.util.Collections; 050 import java.util.HashMap; 051 import java.util.HashSet; 052 import java.util.List; 053 import java.util.Map; 054 import java.util.Set; 055 056 import org.deegree.datatypes.QualifiedName; 057 import org.deegree.framework.log.ILogger; 058 import org.deegree.framework.log.LoggerFactory; 059 import org.deegree.framework.util.StringTools; 060 import org.deegree.i18n.Messages; 061 import org.deegree.io.datastore.DatastoreException; 062 import org.deegree.io.datastore.FeatureId; 063 import org.deegree.io.datastore.PropertyPathResolver; 064 import org.deegree.io.datastore.schema.MappedFeaturePropertyType; 065 import org.deegree.io.datastore.schema.MappedFeatureType; 066 import org.deegree.io.datastore.schema.MappedGMLId; 067 import org.deegree.io.datastore.schema.MappedGeometryPropertyType; 068 import org.deegree.io.datastore.schema.MappedPropertyType; 069 import org.deegree.io.datastore.schema.MappedSimplePropertyType; 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.MappingField; 073 import org.deegree.io.datastore.schema.content.MappingGeometryField; 074 import org.deegree.io.datastore.schema.content.SQLFunctionCall; 075 import org.deegree.io.datastore.schema.content.SimpleContent; 076 import org.deegree.model.crs.CRSFactory; 077 import org.deegree.model.crs.CoordinateSystem; 078 import org.deegree.model.crs.UnknownCRSException; 079 import org.deegree.model.feature.Feature; 080 import org.deegree.model.feature.FeatureFactory; 081 import org.deegree.model.feature.FeatureProperty; 082 import org.deegree.model.feature.schema.PropertyType; 083 import org.deegree.model.spatialschema.Geometry; 084 import org.deegree.ogcbase.PropertyPath; 085 import org.deegree.ogcwebservices.wfs.operation.Query; 086 087 /** 088 * The only implementation of this abstract class is the {@link QueryHandler} class. 089 * <p> 090 * While the {@link QueryHandler} class performs the initial SELECT, {@link FeatureFetcher} is responsible for any 091 * subsequent SELECTs that may be necessary. 092 * 093 * @see QueryHandler 094 * 095 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider</a> 096 * @author last edited by: $Author: mschneider $ 097 * 098 * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $ 099 */ 100 abstract class FeatureFetcher extends AbstractRequestHandler { 101 102 private static final ILogger LOG = LoggerFactory.getLogger( FeatureFetcher.class ); 103 104 // key: feature id of features that are generated or are in generation 105 protected Set<FeatureId> featuresInGeneration = new HashSet<FeatureId>(); 106 107 // key: feature id value, value: Feature 108 protected Map<FeatureId, Feature> featureMap = new HashMap<FeatureId, Feature>( 1000 ); 109 110 // key: feature id value, value: property instances that contain the feature 111 protected Map<FeatureId, List<FeatureProperty>> fidToPropertyMap = new HashMap<FeatureId, List<FeatureProperty>>(); 112 113 // provides virtual content (constants, sql functions, ...) 114 protected VirtualContentProvider vcProvider; 115 116 protected Query query; 117 118 private CoordinateSystem queryCS; 119 120 // key: geometry field, value: function call that transforms it to the queried CS 121 private Map<MappingGeometryField, SQLFunctionCall> fieldToTransformCall = new HashMap<MappingGeometryField, SQLFunctionCall>(); 122 123 FeatureFetcher( AbstractSQLDatastore datastore, TableAliasGenerator aliasGenerator, Connection conn, Query query ) 124 throws DatastoreException { 125 super( datastore, aliasGenerator, conn ); 126 this.query = query; 127 if ( this.query.getSrsName() != null ) { 128 try { 129 this.queryCS = CRSFactory.create( this.query.getSrsName() ); 130 } catch ( UnknownCRSException e ) { 131 throw new DatastoreException( e.getMessage(), e ); 132 } 133 } 134 } 135 136 /** 137 * Builds a SELECT statement to fetch features / properties that are stored in a related table. 138 * 139 * @param fetchContents 140 * table columns / functions to fetch 141 * @param relations 142 * table relations that lead to the table where the property is stored 143 * @param resultValues 144 * all retrieved columns from one result set row 145 * @param resultPosMap 146 * key class: SimpleContent, value class: Integer (this is the associated index in resultValues) 147 * @return the statement or null if the keys in resultValues contain NULL values 148 */ 149 private StatementBuffer buildSubsequentSelect( List<List<SimpleContent>> fetchContents, TableRelation[] relations, 150 Object[] resultValues, Map<SimpleContent, Integer> resultPosMap ) { 151 152 this.aliasGenerator.reset(); 153 String[] tableAliases = this.aliasGenerator.generateUniqueAliases( relations.length ); 154 155 StatementBuffer query = new StatementBuffer(); 156 query.append( "SELECT " ); 157 appendQualifiedContentList( query, tableAliases[tableAliases.length - 1], fetchContents ); 158 query.append( " FROM " ); 159 query.append( relations[0].getToTable() ); 160 query.append( " " ); 161 query.append( tableAliases[0] ); 162 163 // append joins 164 for ( int i = 1; i < relations.length; i++ ) { 165 query.append( " JOIN " ); 166 query.append( relations[i].getToTable() ); 167 query.append( " " ); 168 query.append( tableAliases[i] ); 169 query.append( " ON " ); 170 MappingField[] fromFields = relations[i].getFromFields(); 171 MappingField[] toFields = relations[i].getToFields(); 172 for ( int j = 0; j < fromFields.length; j++ ) { 173 query.append( tableAliases[i - 1] ); 174 query.append( '.' ); 175 query.append( fromFields[j].getField() ); 176 query.append( '=' ); 177 query.append( tableAliases[i] ); 178 query.append( '.' ); 179 query.append( toFields[j].getField() ); 180 } 181 } 182 183 // append key constraints 184 query.append( " WHERE " ); 185 MappingField[] fromFields = relations[0].getFromFields(); 186 MappingField[] toFields = relations[0].getToFields(); 187 for ( int i = 0; i < fromFields.length; i++ ) { 188 int resultPos = resultPosMap.get( fromFields[i] ); 189 Object keyValue = resultValues[resultPos]; 190 if ( keyValue == null ) { 191 return null; 192 } 193 query.append( tableAliases[0] ); 194 query.append( '.' ); 195 query.append( toFields[i].getField() ); 196 query.append( "=?" ); 197 query.addArgument( keyValue, toFields[i].getType() ); 198 if ( i != fromFields.length - 1 ) { 199 query.append( " AND " ); 200 } 201 } 202 return query; 203 } 204 205 /** 206 * Builds a SELECT statement to fetch the feature ids and the (concrete) feature types of feature properties that 207 * are stored in a related table (currently limited to *one* join table). 208 * <p> 209 * This is only necessary for feature properties that contain feature types with more than one possible 210 * substitution. 211 * 212 * @param relation1 213 * first table relation that leads to the join table 214 * @param relation2 215 * second table relation that leads to the table where the property is stored 216 * @param resultValues 217 * all retrieved columns from one result set row 218 * @param mappingFieldMap 219 * key class: MappingField, value class: Integer (this is the associated index in resultValues) 220 * @return the statement or null if the keys in resultValues contain NULL values 221 */ 222 private StatementBuffer buildFeatureTypeSelect( TableRelation relation1, TableRelation relation2, 223 Object[] resultValues, Map<?, ?> mappingFieldMap ) { 224 StatementBuffer query = new StatementBuffer(); 225 query.append( "SELECT " ); 226 // append feature type column 227 query.append( FT_COLUMN ); 228 // append feature id columns 229 MappingField[] fidFields = relation2.getFromFields(); 230 for ( int i = 0; i < fidFields.length; i++ ) { 231 query.append( ',' ); 232 query.append( fidFields[i].getField() ); 233 } 234 query.append( " FROM " ); 235 query.append( relation1.getToTable() ); 236 query.append( " WHERE " ); 237 // append key constraints 238 MappingField[] fromFields = relation1.getFromFields(); 239 MappingField[] toFields = relation1.getToFields(); 240 for ( int i = 0; i < fromFields.length; i++ ) { 241 Integer resultPos = (Integer) mappingFieldMap.get( fromFields[i] ); 242 Object keyValue = resultValues[resultPos.intValue()]; 243 if ( keyValue == null ) { 244 return null; 245 } 246 query.append( toFields[i].getField() ); 247 query.append( "=?" ); 248 query.addArgument( keyValue, toFields[i].getType() ); 249 if ( i != fromFields.length - 1 ) { 250 query.append( " AND " ); 251 } 252 } 253 return query; 254 } 255 256 /** 257 * Builds a SELECT statement to fetch the feature id and the (concrete) feature type of a feature property that is 258 * stored in a related table (with the fk in the current table). 259 * <p> 260 * This is only necessary for feature properties that contain feature types with more than one possible 261 * substitution. 262 * 263 * TODO: Select the FT_ column beforehand. 264 * 265 * @param relation 266 * table relation that leads to the subfeature table 267 * @param resultValues 268 * all retrieved columns from one result set row 269 * @param mappingFieldMap 270 * key class: MappingField, value class: Integer (this is the associated index in resultValues) 271 * @return the statement or null if the keys in resultValues contain NULL values 272 */ 273 private StatementBuffer buildFeatureTypeSelect( TableRelation relation, Object[] resultValues, 274 Map<?, ?> mappingFieldMap ) { 275 StatementBuffer query = new StatementBuffer(); 276 query.append( "SELECT DISTINCT " ); 277 // append feature type column 278 query.append( FT_PREFIX + relation.getFromFields()[0].getField() ); 279 // append feature id columns 280 MappingField[] fidFields = relation.getFromFields(); 281 for ( int i = 0; i < fidFields.length; i++ ) { 282 query.append( ',' ); 283 query.append( fidFields[i].getField() ); 284 } 285 query.append( " FROM " ); 286 query.append( relation.getFromTable() ); 287 query.append( " WHERE " ); 288 // append key constraints 289 MappingField[] fromFields = relation.getFromFields(); 290 for ( int i = 0; i < fromFields.length; i++ ) { 291 Integer resultPos = (Integer) mappingFieldMap.get( fromFields[i] ); 292 Object keyValue = resultValues[resultPos.intValue()]; 293 if ( keyValue == null ) { 294 return null; 295 } 296 query.append( fromFields[i].getField() ); 297 query.append( "=?" ); 298 query.addArgument( keyValue, fromFields[i].getType() ); 299 if ( i != fromFields.length - 1 ) { 300 query.append( " AND " ); 301 } 302 } 303 return query; 304 } 305 306 /** 307 * Builds a SELECT statement that fetches one feature and it's properties. 308 * 309 * @param fid 310 * id of the feature to fetch 311 * @param table 312 * root table of the feature 313 * @param fetchContents 314 * @return the statement or null if the keys in resultValues contain NULL values 315 */ 316 private StatementBuffer buildFeatureSelect( FeatureId fid, String table, List<List<SimpleContent>> fetchContents ) { 317 318 StatementBuffer query = new StatementBuffer(); 319 query.append( "SELECT " ); 320 appendQualifiedContentList( query, table, fetchContents ); 321 query.append( " FROM " ); 322 query.append( table ); 323 query.append( " WHERE " ); 324 325 // append feature id constraints 326 MappingField[] fidFields = fid.getFidDefinition().getIdFields(); 327 for ( int i = 0; i < fidFields.length; i++ ) { 328 query.append( fidFields[i].getField() ); 329 query.append( "=?" ); 330 query.addArgument( fid.getValue( i ), fidFields[i].getType() ); 331 if ( i != fidFields.length - 1 ) { 332 query.append( " AND " ); 333 } 334 } 335 return query; 336 } 337 338 /** 339 * Extracts a feature from the values of a result set row. 340 * 341 * @param fid 342 * feature id of the feature 343 * @param requestedPropertyMap 344 * requested <code>MappedPropertyType</code>s mapped to <code>Collection</code> of 345 * <code>PropertyPath</code>s 346 * @param resultPosMap 347 * key class: MappingField, value class: Integer (this is the associated index in resultValues) 348 * @param resultValues 349 * all retrieved columns from one result set row 350 * @return the extracted feature 351 * @throws SQLException 352 * if a JDBC related error occurs 353 * @throws DatastoreException 354 * @throws UnknownCRSException 355 */ 356 protected Feature extractFeature( FeatureId fid, 357 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropertyMap, 358 Map<SimpleContent, Integer> resultPosMap, Object[] resultValues ) 359 throws SQLException, DatastoreException, UnknownCRSException { 360 361 LOG.logDebug( "id = " + fid.getAsString() ); 362 363 this.featuresInGeneration.add( fid ); 364 365 // extract the requested properties of the feature 366 List<FeatureProperty> propertyList = new ArrayList<FeatureProperty>(); 367 for ( MappedPropertyType requestedProperty : requestedPropertyMap.keySet() ) { 368 Collection<PropertyPath> propertyPaths = requestedPropertyMap.get( requestedProperty ); 369 // PropertyPath[] requestingPaths = PropertyPathResolver.determineSubPropertyPaths 370 // (requestedProperty, propertyPaths); 371 Collection<FeatureProperty> props = extractProperties( requestedProperty, propertyPaths, resultPosMap, 372 resultValues ); 373 propertyList.addAll( props ); 374 } 375 FeatureProperty[] properties = propertyList.toArray( new FeatureProperty[propertyList.size()] ); 376 Feature feature = FeatureFactory.createFeature( fid.getAsString(), fid.getFeatureType(), properties ); 377 378 this.featureMap.put( fid, feature ); 379 return feature; 380 } 381 382 /** 383 * Extracts the feature id from the values of a result set row. 384 * 385 * @param ft 386 * feature type for which the id shall be extracted 387 * @param mfMap 388 * key class: MappingField, value class: Integer (this is the associated index in resultValues) 389 * @param resultValues 390 * all retrieved columns from one result set row 391 * @return the feature id 392 * @throws DatastoreException 393 */ 394 protected FeatureId extractFeatureId( MappedFeatureType ft, Map<SimpleContent, Integer> mfMap, Object[] resultValues ) 395 throws DatastoreException { 396 MappingField[] idFields = ft.getGMLId().getIdFields(); 397 Object[] idValues = new Object[idFields.length]; 398 for ( int i = 0; i < idFields.length; i++ ) { 399 Integer resultPos = mfMap.get( idFields[i] ); 400 Object idValue = resultValues[resultPos.intValue()]; 401 if ( idValue == null ) { 402 String msg = Messages.getMessage( "DATASTORE_FEATURE_ID_NULL", ft.getTable(), ft.getName(), 403 idFields[i].getField() ); 404 throw new DatastoreException( msg ); 405 } 406 idValues[i] = idValue; 407 } 408 return new FeatureId( ft, idValues ); 409 } 410 411 /** 412 * Extracts the properties of the given property type from the values of a result set row. If the property is stored 413 * in related table, only the key values are present in the result set row and more SELECTs are built and executed 414 * to build the property. 415 * <p> 416 * NOTE: If the property is not stored in a related table, only one FeatureProperty is returned, otherwise the 417 * number of properties depends on the multiplicity of the relation. 418 * 419 * @param pt 420 * the mapped property type to be extracted 421 * @param propertyPaths 422 * property paths that refer to the property to be extracted 423 * @param resultPosMap 424 * key class: SimpleContent, value class: Integer (this is the associated index in resultValues) 425 * @param resultValues 426 * all retrieved columns from one result set row 427 * @return Collection of FeatureProperty instances 428 * @throws SQLException 429 * if a JDBC related error occurs 430 * @throws DatastoreException 431 * @throws UnknownCRSException 432 */ 433 private Collection<FeatureProperty> extractProperties( MappedPropertyType pt, 434 Collection<PropertyPath> propertyPaths, 435 Map<SimpleContent, Integer> resultPosMap, 436 Object[] resultValues ) 437 throws SQLException, DatastoreException, UnknownCRSException { 438 439 Collection<FeatureProperty> result = null; 440 441 TableRelation[] tableRelations = pt.getTableRelations(); 442 if ( tableRelations != null && tableRelations.length != 0 ) { 443 LOG.logDebug( "Fetching related properties: '" + pt.getName() + "'..." ); 444 result = fetchRelatedProperties( pt.getName(), pt, propertyPaths, resultPosMap, resultValues ); 445 } else { 446 Object propertyValue = null; 447 if ( pt instanceof MappedSimplePropertyType ) { 448 SimpleContent content = ( (MappedSimplePropertyType) pt ).getContent(); 449 if ( content instanceof MappingField ) { 450 Integer resultPos = resultPosMap.get( content ); 451 propertyValue = datastore.convertFromDBType( resultValues[resultPos.intValue()], pt.getType() ); 452 } else if ( content instanceof ConstantContent ) { 453 propertyValue = ( (ConstantContent) content ).getValue(); 454 } else if ( content instanceof SQLFunctionCall ) { 455 Integer resultPos = resultPosMap.get( content ); 456 propertyValue = resultValues[resultPos.intValue()]; 457 } 458 } else if ( pt instanceof MappedGeometryPropertyType ) { 459 MappingGeometryField field = ( (MappedGeometryPropertyType) pt ).getMappingField(); 460 Integer resultPos = null; 461 SQLFunctionCall transformCall = this.fieldToTransformCall.get( field ); 462 if ( transformCall != null ) { 463 resultPos = resultPosMap.get( transformCall ); 464 } else { 465 resultPos = resultPosMap.get( field ); 466 } 467 propertyValue = resultValues[resultPos.intValue()]; 468 469 CoordinateSystem cs = ( (MappedGeometryPropertyType) pt ).getCS(); 470 if ( this.queryCS != null ) { 471 cs = this.queryCS; 472 } 473 propertyValue = this.datastore.convertDBToDeegreeGeometry( propertyValue, cs, conn ); 474 } else { 475 String msg = "Unsupported property type: '" + pt.getClass().getName() 476 + "' in QueryHandler.extractProperties(). "; 477 LOG.logError( msg ); 478 throw new IllegalArgumentException( msg ); 479 } 480 FeatureProperty property = FeatureFactory.createFeatureProperty( pt.getName(), propertyValue ); 481 result = new ArrayList<FeatureProperty>(); 482 result.add( property ); 483 } 484 return result; 485 } 486 487 /** 488 * Extracts a {@link FeatureId} from one result set row. 489 * 490 * @param ft 491 * @param rs 492 * @param startIndex 493 * @return feature id from result set row 494 * @throws SQLException 495 */ 496 private FeatureId extractFeatureId( MappedFeatureType ft, ResultSet rs, int startIndex ) 497 throws SQLException { 498 MappedGMLId gmlId = ft.getGMLId(); 499 MappingField[] idFields = gmlId.getIdFields(); 500 501 Object[] idValues = new Object[idFields.length]; 502 for ( int i = 0; i < idValues.length; i++ ) { 503 idValues[i] = rs.getObject( i + startIndex ); 504 } 505 return new FeatureId( ft, idValues ); 506 } 507 508 /** 509 * Determines the columns / functions that have to be fetched from the table of the given {@link MappedFeatureType} 510 * and associates identical columns / functions to avoid that the same column / function is SELECTed more than once. 511 * <p> 512 * Identical columns are put into the same (inner) list. 513 * <p> 514 * The following {@link SimpleContent} instances of the {@link MappedFeatureType}s annotation are used to build the 515 * list: 516 * <ul> 517 * <li>MappingFields from the wfs:gmlId - annotation element of the feature type definition</li> 518 * <li>MappingFields in the annotations of the property element definitions; if the property's content is stored in 519 * a related table, the MappingFields used in the first wfs:Relation element's wfs:From element are considered</li> 520 * <li>SQLFunctionCalls in the annotations of the property element definitions; if the property's (derived) content 521 * is stored in a related table, the MappingFields used in the first wfs:Relation element's wfs:From element are 522 * considered</li> 523 * </ul> 524 * 525 * @param ft 526 * feature type for which the content list is built 527 * @param requestedProps 528 * requested properties 529 * @return List of Lists (that contains SimpleContent instance that refer the same column) 530 * @throws DatastoreException 531 */ 532 protected List<List<SimpleContent>> determineFetchContents( MappedFeatureType ft, PropertyType[] requestedProps ) 533 throws DatastoreException { 534 535 List<List<SimpleContent>> fetchList = new ArrayList<List<SimpleContent>>(); 536 537 // helper lookup map (column names -> referencing MappingField instances) 538 Map<String, List<SimpleContent>> columnsMap = new HashMap<String, List<SimpleContent>>(); 539 540 // add table columns that are necessary to build the feature's gml id 541 MappingField[] idFields = ft.getGMLId().getIdFields(); 542 for ( int i = 0; i < idFields.length; i++ ) { 543 List<SimpleContent> mappingFieldList = columnsMap.get( idFields[i].getField() ); 544 if ( mappingFieldList == null ) { 545 mappingFieldList = new ArrayList<SimpleContent>(); 546 } 547 mappingFieldList.add( idFields[i] ); 548 columnsMap.put( idFields[i].getField(), mappingFieldList ); 549 } 550 551 // add columns that are necessary to build the requested feature properties 552 for ( int i = 0; i < requestedProps.length; i++ ) { 553 MappedPropertyType pt = (MappedPropertyType) requestedProps[i]; 554 TableRelation[] tableRelations = pt.getTableRelations(); 555 556 if ( pt instanceof MappedFeaturePropertyType && ( (MappedFeaturePropertyType) pt ).externalLinksAllowed() ) { 557 MappingField fld = pt.getTableRelations()[0].getFromFields()[0]; 558 MappingField newFld = new MappingField( fld.getTable(), fld.getField() + "_external", fld.getType() ); 559 columnsMap.put( fld.getField() + "_external", Collections.<SimpleContent> singletonList( newFld ) ); 560 } 561 562 if ( tableRelations != null && tableRelations.length != 0 ) { 563 // if property is not stored in feature type's table, retrieve key fields of 564 // the first relation's 'From' element 565 MappingField[] fields = tableRelations[0].getFromFields(); 566 for ( int k = 0; k < fields.length; k++ ) { 567 List<SimpleContent> list = columnsMap.get( fields[k].getField() ); 568 if ( list == null ) { 569 list = new ArrayList<SimpleContent>(); 570 } 571 list.add( fields[k] ); 572 columnsMap.put( fields[k].getField(), list ); 573 } 574 // if (content instanceof FeaturePropertyContent) { 575 // if (tableRelations.length == 1) { 576 // // if feature property contains an abstract feature type, retrieve 577 // // feature type as well (stored in column named "FT_fk") 578 // MappedFeatureType subFeatureType = ( (FeaturePropertyContent) content ) 579 // .getFeatureTypeReference().getFeatureType(); 580 // if (subFeatureType.isAbstract()) { 581 // String typeColumn = FT_PREFIX + fields [0].getField(); 582 // columnsMap.put (typeColumn, new ArrayList ()); 583 // } 584 // } 585 // } 586 } else { 587 String column = null; 588 SimpleContent content = null; 589 if ( pt instanceof MappedSimplePropertyType ) { 590 content = ( (MappedSimplePropertyType) pt ).getContent(); 591 if ( content instanceof MappingField ) { 592 column = ( (MappingField) content ).getField(); 593 } else { 594 // ignore virtual properties here (handled below) 595 continue; 596 } 597 } else if ( pt instanceof MappedGeometryPropertyType ) { 598 content = determineFetchContent( (MappedGeometryPropertyType) pt ); 599 column = ( (MappedGeometryPropertyType) pt ).getMappingField().getField(); 600 } else { 601 assert false; 602 } 603 List<SimpleContent> contentList = columnsMap.get( column ); 604 if ( contentList == null ) { 605 contentList = new ArrayList<SimpleContent>(); 606 } 607 contentList.add( content ); 608 columnsMap.put( column, contentList ); 609 } 610 } 611 612 fetchList.addAll( columnsMap.values() ); 613 614 // add functions that are necessary to build the requested feature properties 615 for ( int i = 0; i < requestedProps.length; i++ ) { 616 MappedPropertyType pt = (MappedPropertyType) requestedProps[i]; 617 TableRelation[] tableRelations = pt.getTableRelations(); 618 if ( tableRelations == null || tableRelations.length == 0 ) { 619 if ( pt instanceof MappedSimplePropertyType ) { 620 SimpleContent content = ( (MappedSimplePropertyType) pt ).getContent(); 621 if ( content instanceof SQLFunctionCall ) { 622 List<SimpleContent> functionCallList = new ArrayList<SimpleContent>( 1 ); 623 functionCallList.add( content ); 624 fetchList.add( functionCallList ); 625 } else { 626 // ignore other content types here 627 continue; 628 } 629 } 630 } 631 } 632 return fetchList; 633 } 634 635 /** 636 * Determines a {@link SimpleContent} object that represents the queried GeometryProperty in the requested SRS. 637 * <p> 638 * <ul> 639 * <li>If the query SRS is identical to the geometry field's SRS (and thus the SRS of the stored geometry, the 640 * corresponding {@link MappingGeometryField} is returned.</li> 641 * <li>If the query SRS differs from the geometry field's SRS (and thus the SRS of the stored geometry, an 642 * {@link SQLFunctionCall} is returned that refers to the stored geometry, but transforms it to the queried SRS.</li> 643 * </ul> 644 * 645 * @param pt 646 * geometry property 647 * @return a <code>SimpleContent</code> instance that represents the queried geometry property 648 * @throws DatastoreException 649 * if the transform call cannot be generated 650 */ 651 private SimpleContent determineFetchContent( MappedGeometryPropertyType pt ) 652 throws DatastoreException { 653 654 MappingGeometryField field = pt.getMappingField(); 655 SimpleContent content = field; 656 657 String queriedSRS = this.datastore.checkTransformation( pt, this.query.getSrsName() ); 658 if ( queriedSRS != null ) { 659 content = this.fieldToTransformCall.get( field ); 660 if ( content == null ) { 661 try { 662 queriedSRS = CRSFactory.create( queriedSRS ).getCRS().getIdentifier(); 663 } catch ( UnknownCRSException e ) { 664 // this should not be possible anyway 665 throw new DatastoreException( e ); 666 } 667 content = this.datastore.buildSRSTransformCall( pt, queriedSRS ); 668 this.fieldToTransformCall.put( field, (SQLFunctionCall) content ); 669 } 670 } 671 return content; 672 } 673 674 /** 675 * Retrieves the feature with the given feature id. 676 * 677 * @param fid 678 * @param requestedPaths 679 * @return the feature with the given type and feature id, may be null 680 * @throws SQLException 681 * @throws DatastoreException 682 * @throws UnknownCRSException 683 */ 684 private Feature fetchFeature( FeatureId fid, PropertyPath[] requestedPaths ) 685 throws SQLException, DatastoreException, UnknownCRSException { 686 687 Feature feature = null; 688 MappedFeatureType ft = fid.getFeatureType(); 689 // TODO what about aliases here? 690 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropMap = determineFetchProperties( ft, null, 691 requestedPaths ); 692 MappedPropertyType[] requestedProps = requestedPropMap.keySet().toArray( 693 new MappedPropertyType[requestedPropMap.size()] ); 694 695 if ( requestedProps.length > 0 ) { 696 697 // determine contents (fields / functions) that must be SELECTed from root table 698 List<List<SimpleContent>> fetchContents = determineFetchContents( ft, requestedProps ); 699 Map<SimpleContent, Integer> resultPosMap = buildResultPosMap( fetchContents ); 700 701 // build feature query 702 StatementBuffer query = buildFeatureSelect( fid, ft.getTable(), fetchContents ); 703 LOG.logDebug( "Feature query: '" + query + "'" ); 704 Object[] resultValues = new Object[fetchContents.size()]; 705 PreparedStatement stmt = null; 706 ResultSet rs = null; 707 try { 708 stmt = this.datastore.prepareStatement( this.conn, query ); 709 rs = stmt.executeQuery(); 710 711 if ( rs.next() ) { 712 // collect result values 713 for ( int i = 0; i < resultValues.length; i++ ) { 714 resultValues[i] = rs.getObject( i + 1 ); 715 } 716 feature = extractFeature( fid, requestedPropMap, resultPosMap, resultValues ); 717 } else { 718 String msg = Messages.getMessage( "DATASTORE_FEATURE_QUERY_NO_RESULT", query.getQueryString() ); 719 LOG.logError( msg ); 720 throw new DatastoreException( msg ); 721 } 722 if ( rs.next() ) { 723 String msg = Messages.getMessage( "DATASTORE_FEATURE_QUERY_MORE_THAN_ONE_RESULT", 724 query.getQueryString() ); 725 LOG.logError( msg ); 726 throw new DatastoreException( msg ); 727 } 728 } finally { 729 try { 730 if ( rs != null ) { 731 rs.close(); 732 } 733 } finally { 734 if ( stmt != null ) { 735 stmt.close(); 736 } 737 } 738 } 739 } 740 return feature; 741 } 742 743 /** 744 * 745 * @param propertyName 746 * @param pt 747 * @param propertyPaths 748 * property paths that refer to the property to be extracted 749 * @param resultPosMap 750 * key class: MappingField, value class: Integer (this is the associated index in resultValues) 751 * @param resultValues 752 * all retrieved columns from one result set row 753 * @return Collection of FeatureProperty instances 754 * @throws SQLException 755 * if a JDBC related error occurs 756 * @throws DatastoreException 757 * @throws UnknownCRSException 758 */ 759 private Collection<FeatureProperty> fetchRelatedProperties( QualifiedName propertyName, MappedPropertyType pt, 760 Collection<PropertyPath> propertyPaths, 761 Map<SimpleContent, Integer> resultPosMap, 762 Object[] resultValues ) 763 throws SQLException, DatastoreException, UnknownCRSException { 764 765 Collection<FeatureProperty> result = new ArrayList<FeatureProperty>( 100 ); 766 PreparedStatement stmt = null; 767 ResultSet rs = null; 768 try { 769 if ( pt instanceof MappedSimplePropertyType ) { 770 SimpleContent content = ( (MappedSimplePropertyType) pt ).getContent(); 771 772 // TODO check for invalid content types 773 List<SimpleContent> fetchContents = new ArrayList<SimpleContent>( 1 ); 774 List<List<SimpleContent>> fetchContentsList = new ArrayList<List<SimpleContent>>( 1 ); 775 fetchContents.add( content ); 776 fetchContentsList.add( fetchContents ); 777 778 StatementBuffer query = buildSubsequentSelect( fetchContentsList, pt.getTableRelations(), resultValues, 779 resultPosMap ); 780 LOG.logDebug( "Subsequent query: '" + query + "'" ); 781 if ( query != null ) { 782 stmt = this.datastore.prepareStatement( this.conn, query ); 783 rs = stmt.executeQuery(); 784 while ( rs.next() ) { 785 Object propertyValue = datastore.convertFromDBType( rs.getObject( 1 ), pt.getType() ); 786 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyName, propertyValue ); 787 result.add( property ); 788 } 789 } 790 } else if ( pt instanceof MappedGeometryPropertyType ) { 791 SimpleContent content = ( (MappedGeometryPropertyType) pt ).getMappingField(); 792 CoordinateSystem cs = ( (MappedGeometryPropertyType) pt ).getCS(); 793 794 if ( this.queryCS != null ) { 795 cs = this.queryCS; 796 } 797 content = determineFetchContent( (MappedGeometryPropertyType) pt ); 798 799 List<SimpleContent> fetchContents = new ArrayList<SimpleContent>( 1 ); 800 List<List<SimpleContent>> fetchContentsList = new ArrayList<List<SimpleContent>>( 1 ); 801 fetchContents.add( content ); 802 fetchContentsList.add( fetchContents ); 803 804 StatementBuffer query = buildSubsequentSelect( fetchContentsList, pt.getTableRelations(), resultValues, 805 resultPosMap ); 806 LOG.logDebug( "Subsequent query: '" + query + "'" ); 807 if ( query != null ) { 808 stmt = this.datastore.prepareStatement( this.conn, query ); 809 rs = stmt.executeQuery(); 810 while ( rs.next() ) { 811 Object value = rs.getObject( 1 ); 812 Geometry geometry = this.datastore.convertDBToDeegreeGeometry( value, cs, this.conn ); 813 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyName, geometry ); 814 result.add( property ); 815 } 816 } 817 } else if ( pt instanceof MappedFeaturePropertyType ) { 818 MappedFeatureType ft = ( (MappedFeaturePropertyType) pt ).getFeatureTypeReference().getFeatureType(); 819 820 if ( ( (MappedFeaturePropertyType) pt ).externalLinksAllowed() ) { 821 MappingField fld = pt.getTableRelations()[0].getFromFields()[0]; 822 String ref = fld.getField() + "_external"; 823 MappingField key = new MappingField( fld.getTable(), ref, fld.getType() ); 824 for ( SimpleContent f : resultPosMap.keySet() ) { 825 if ( f.equals( key ) ) { 826 Object url = resultValues[resultPosMap.get( f )]; 827 if ( url != null ) { 828 URL u = new URL( (String) url ); 829 result.add( createFeatureProperty( propertyName, u ) ); 830 } 831 } 832 } 833 834 } 835 836 MappedFeatureType[] substitutions = ft.getConcreteSubstitutions(); 837 if ( substitutions.length > 1 ) { 838 // if feature type has more than one concrete substitution, determine concrete 839 // feature type first 840 String msg = StringTools.concat( 200, "FeatureType '", ft.getName(), 841 "' has more than one concrete ", 842 "substitution. Need to determine feature type table first." ); 843 LOG.logDebug( msg ); 844 LOG.logDebug( "Property: " + pt.getName() ); 845 TableRelation[] tableRelations = pt.getTableRelations(); 846 if ( tableRelations.length == 2 ) { 847 StatementBuffer query = buildFeatureTypeSelect( tableRelations[0], tableRelations[1], 848 resultValues, resultPosMap ); 849 LOG.logDebug( "Feature type (and id) query: '" + query + "'" ); 850 if ( query != null ) { 851 stmt = this.datastore.prepareStatement( this.conn, query ); 852 rs = stmt.executeQuery(); 853 while ( rs.next() ) { 854 String featureTypeName = rs.getString( 1 ); 855 MappedFeatureType concreteFeatureType = ft.getGMLSchema().getFeatureType( 856 featureTypeName ); 857 if ( concreteFeatureType == null ) { 858 msg = StringTools.concat( 200, "Lookup of concrete feature type '", 859 featureTypeName, "' failed: ", 860 " Inconsistent featuretype column!?" ); 861 LOG.logError( msg ); 862 throw new DatastoreException( msg ); 863 } 864 FeatureId fid = extractFeatureId( concreteFeatureType, rs, 2 ); 865 msg = StringTools.concat( 200, "Subfeature '", fid.getAsString(), 866 "' has concrete feature type '", 867 concreteFeatureType.getName(), "'." ); 868 LOG.logDebug( msg ); 869 870 if ( !this.featuresInGeneration.contains( fid ) ) { 871 PropertyPath[] subPropertyPaths = PropertyPathResolver.determineSubPropertyPaths( 872 concreteFeatureType, 873 propertyPaths ); 874 Feature feature = fetchFeature( fid, subPropertyPaths ); 875 if ( feature != null ) { 876 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyName, 877 feature ); 878 result.add( property ); 879 } 880 } else { 881 FeatureProperty property = FeatureFactory.createFeatureProperty( propertyName, null ); 882 addToFidToPropertyMap( fid, property ); 883 result.add( property ); 884 } 885 } 886 } 887 } else if ( tableRelations.length == 1 ) { 888 StatementBuffer query = buildFeatureTypeSelect( tableRelations[0], resultValues, resultPosMap ); 889 LOG.logDebug( "Feature type (and id) query: '" + query + "'" ); 890 if ( query != null ) { 891 stmt = this.datastore.prepareStatement( this.conn, query ); 892 rs = stmt.executeQuery(); 893 while ( rs.next() ) { 894 String featureTypeName = rs.getString( 1 ); 895 MappedFeatureType concreteFeatureType = ft.getGMLSchema().getFeatureType( 896 featureTypeName ); 897 if ( concreteFeatureType == null ) { 898 msg = StringTools.concat( 200, "Lookup of concrete feature type '", 899 featureTypeName, "' failed: ", 900 " Inconsistent featuretype column!?" ); 901 LOG.logError( msg ); 902 throw new DatastoreException( msg ); 903 } 904 905 FeatureId fid = extractFeatureId( concreteFeatureType, rs, 2 ); 906 907 msg = StringTools.concat( 200, "Subfeature '", fid.getAsString(), 908 "' has concrete feature type '", 909 concreteFeatureType.getName(), "'." ); 910 LOG.logDebug( msg ); 911 912 FeatureProperty property = null; 913 if ( !this.featuresInGeneration.contains( fid ) ) { 914 PropertyPath[] subPropertyPaths = PropertyPathResolver.determineSubPropertyPaths( 915 concreteFeatureType, 916 propertyPaths ); 917 Feature feature = fetchFeature( fid, subPropertyPaths ); 918 if ( feature != null ) { 919 property = FeatureFactory.createFeatureProperty( propertyName, feature ); 920 result.add( property ); 921 } 922 923 } else { 924 property = FeatureFactory.createFeatureProperty( propertyName, null ); 925 addToFidToPropertyMap( fid, property ); 926 result.add( property ); 927 } 928 } 929 } 930 } else { 931 msg = StringTools.concat( 200, "Querying of feature properties ", 932 "with a content type with more than one ", 933 "concrete substitution is not implemented for ", 934 tableRelations.length, " TableRelations." ); 935 throw new DatastoreException( msg ); 936 } 937 } else { 938 // feature type is the only substitutable concrete feature type 939 PropertyPath[] subPropertyPaths = PropertyPathResolver.determineSubPropertyPaths( ft, propertyPaths ); 940 // TODO aliases? 941 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropertiesMap = PropertyPathResolver.determineFetchProperties( 942 ft, 943 null, 944 subPropertyPaths ); 945 MappedPropertyType[] requestedProperties = requestedPropertiesMap.keySet().toArray( 946 new MappedPropertyType[requestedPropertiesMap.size()] ); 947 948 // determine contents (fields / functions) that needs to be SELECTed from 949 // current table 950 List<List<SimpleContent>> fetchContents = determineFetchContents( ft, requestedProperties ); 951 Map<SimpleContent, Integer> newResultPosMap = buildResultPosMap( fetchContents ); 952 953 StatementBuffer query = buildSubsequentSelect( fetchContents, pt.getTableRelations(), resultValues, 954 resultPosMap ); 955 LOG.logDebug( "Subsequent query: '" + query + "'" ); 956 957 if ( query != null ) { 958 Object[] newResultValues = new Object[fetchContents.size()]; 959 stmt = this.datastore.prepareStatement( this.conn, query ); 960 rs = stmt.executeQuery(); 961 while ( rs.next() ) { 962 // cache result values 963 for ( int i = 0; i < newResultValues.length; i++ ) { 964 newResultValues[i] = rs.getObject( i + 1 ); 965 } 966 FeatureId fid = extractFeatureId( ft, newResultPosMap, newResultValues ); 967 FeatureProperty property = null; 968 if ( !this.featuresInGeneration.contains( fid ) ) { 969 Feature feature = extractFeature( fid, requestedPropertiesMap, newResultPosMap, 970 newResultValues ); 971 property = FeatureFactory.createFeatureProperty( propertyName, feature ); 972 } else { 973 property = FeatureFactory.createFeatureProperty( propertyName, null ); 974 addToFidToPropertyMap( fid, property ); 975 } 976 result.add( property ); 977 } 978 } 979 } 980 } else { 981 String msg = "Unsupported content type: '" + pt.getClass().getName() 982 + "' in QueryHandler.fetchRelatedProperties()."; 983 throw new IllegalArgumentException( msg ); 984 } 985 } catch ( MalformedURLException e ) { 986 LOG.logError( "Unknown error", e ); 987 } finally { 988 try { 989 if ( rs != null ) { 990 rs.close(); 991 } 992 if ( stmt != null ) { 993 stmt.close(); 994 } 995 } finally { 996 if ( stmt != null ) { 997 stmt.close(); 998 } 999 } 1000 } 1001 return result; 1002 } 1003 1004 private void addToFidToPropertyMap( FeatureId fid, FeatureProperty property ) { 1005 List<FeatureProperty> properties = this.fidToPropertyMap.get( fid ); 1006 if ( properties == null ) { 1007 properties = new ArrayList<FeatureProperty>(); 1008 this.fidToPropertyMap.put( fid, properties ); 1009 } 1010 properties.add( property ); 1011 } 1012 1013 protected void appendQualifiedContentList( StatementBuffer query, String tableAlias, 1014 List<List<SimpleContent>> fetchContents ) { 1015 1016 for ( int i = 0; i < fetchContents.size(); i++ ) { 1017 SimpleContent content = fetchContents.get( i ).get( 0 ); 1018 if ( content instanceof MappingField ) { 1019 if ( content instanceof MappingGeometryField ) { 1020 datastore.appendGeometryColumnGet( query, tableAlias, ( (MappingField) content ).getField() ); 1021 } else { 1022 appendQualifiedColumn( query, tableAlias, ( (MappingField) content ).getField() ); 1023 } 1024 } else if ( content instanceof SQLFunctionCall ) { 1025 this.vcProvider.appendSQLFunctionCall( query, tableAlias, (SQLFunctionCall) content ); 1026 } else { 1027 assert false; 1028 } 1029 if ( i != fetchContents.size() - 1 ) { 1030 query.append( "," ); 1031 } 1032 } 1033 } 1034 1035 /** 1036 * Builds a lookup map that allows to find the index (position in the {@link ResultSet}) by the 1037 * {@link SimpleContent} instance that makes it necessary to fetch it. 1038 * 1039 * @param fetchContents 1040 * @return key: SimpleContent, value: Integer (position in ResultSet) 1041 */ 1042 protected Map<SimpleContent, Integer> buildResultPosMap( List<List<SimpleContent>> fetchContents ) { 1043 1044 Map<SimpleContent, Integer> resultPosMap = new HashMap<SimpleContent, Integer>(); 1045 for ( int i = 0; i < fetchContents.size(); i++ ) { 1046 for ( SimpleContent content : fetchContents.get( i ) ) { 1047 resultPosMap.put( content, i ); 1048 } 1049 } 1050 return resultPosMap; 1051 } 1052 }