001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/branches/2.2_testing/src/org/deegree/io/datastore/sql/AbstractRequestHandler.java $
002 /*---------------- FILE HEADER ------------------------------------------
003
004 This file is part of deegree.
005 Copyright (C) 2001-2008 by:
006 EXSE, Department of Geography, University of Bonn
007 http://www.giub.uni-bonn.de/deegree/
008 lat/lon GmbH
009 http://www.lat-lon.de
010
011 This library is free software; you can redistribute it and/or
012 modify it under the terms of the GNU Lesser General Public
013 License as published by the Free Software Foundation; either
014 version 2.1 of the License, or (at your option) any later version.
015
016 This library is distributed in the hope that it will be useful,
017 but WITHOUT ANY WARRANTY; without even the implied warranty of
018 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
019 Lesser General Public License for more details.
020
021 You should have received a copy of the GNU Lesser General Public
022 License along with this library; if not, write to the Free Software
023 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
024
025 Contact:
026
027 Andreas Poth
028 lat/lon GmbH
029 Aennchenstraße 19
030 53177 Bonn
031 Germany
032 E-Mail: poth@lat-lon.de
033
034 Prof. Dr. Klaus Greve
035 Department of Geography
036 University of Bonn
037 Meckenheimer Allee 166
038 53115 Bonn
039 Germany
040 E-Mail: greve@giub.uni-bonn.de
041
042 ---------------------------------------------------------------------------*/
043 package org.deegree.io.datastore.sql;
044
045 import java.sql.Connection;
046 import java.sql.PreparedStatement;
047 import java.sql.ResultSet;
048 import java.sql.SQLException;
049 import java.util.ArrayList;
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.framework.log.ILogger;
057 import org.deegree.framework.log.LoggerFactory;
058 import org.deegree.i18n.Messages;
059 import org.deegree.io.datastore.DatastoreException;
060 import org.deegree.io.datastore.FeatureId;
061 import org.deegree.io.datastore.LockManager;
062 import org.deegree.io.datastore.schema.MappedFeaturePropertyType;
063 import org.deegree.io.datastore.schema.MappedFeatureType;
064 import org.deegree.io.datastore.schema.MappedGMLId;
065 import org.deegree.io.datastore.schema.MappedGMLSchema;
066 import org.deegree.io.datastore.schema.MappedPropertyType;
067 import org.deegree.io.datastore.schema.TableRelation;
068 import org.deegree.io.datastore.schema.content.MappingField;
069 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
070 import org.deegree.model.feature.schema.FeatureType;
071 import org.deegree.model.feature.schema.PropertyType;
072 import org.deegree.model.filterencoding.Filter;
073
074 /**
075 * This abstract class implements some common SQL functionality needed by request handlers for SQL based datastores.
076 *
077 * @see QueryHandler
078 *
079 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
080 * @author last edited by: $Author: apoth $
081 *
082 * @version $Revision: 9342 $, $Date: 2007-12-27 13:32:57 +0100 (Do, 27 Dez 2007) $
083 */
084 public class AbstractRequestHandler {
085
086 private static final ILogger LOG = LoggerFactory.getLogger( AbstractRequestHandler.class );
087
088 /**
089 * Column used for disambiguation of feature properties that contain features that have more than one concrete type.
090 */
091 protected static final String FT_COLUMN = "featuretype";
092
093 /**
094 * Column prefix used for disambiguation of feature properties that contain features that have more than one
095 * concrete type.
096 */
097 protected static final String FT_PREFIX = "FT_";
098
099 protected AbstractSQLDatastore datastore;
100
101 protected TableAliasGenerator aliasGenerator;
102
103 protected Connection conn;
104
105 /**
106 * Creates a new instance of <code>AbstractRequestHandler</code> from the given parameters.
107 *
108 * @param ds
109 * @param aliasGenerator
110 * @param conn
111 */
112 public AbstractRequestHandler( AbstractSQLDatastore ds, TableAliasGenerator aliasGenerator, Connection conn ) {
113 this.datastore = ds;
114 this.aliasGenerator = aliasGenerator;
115 this.conn = conn;
116 }
117
118 /**
119 * Determines the feature ids that are matched by the given filter.
120 *
121 * @param ft
122 * non-abstract feature type
123 * @param filter
124 * constraints the feature instances
125 * @return the feature ids that are matched by the given filter
126 * @throws DatastoreException
127 */
128 public List<FeatureId> determineAffectedFIDs( MappedFeatureType ft, Filter filter )
129 throws DatastoreException {
130
131 assert !ft.isAbstract();
132
133 TableAliasGenerator aliasGenerator = new TableAliasGenerator();
134 VirtualContentProvider vcProvider = new VirtualContentProvider( filter, this.datastore, this.conn );
135 WhereBuilder whereBuilder = this.datastore.getWhereBuilder( new MappedFeatureType[] { ft }, null, filter, null,
136 aliasGenerator, vcProvider );
137
138 // if no filter is given
139 StatementBuffer query = buildInitialFIDSelect( ft, whereBuilder );
140 LOG.logDebug( "Determine affected feature id query: '" + query + "'" );
141
142 List<FeatureId> fids = null;
143 PreparedStatement stmt = null;
144 ResultSet rs = null;
145 try {
146 stmt = this.datastore.prepareStatement( conn, query );
147 rs = stmt.executeQuery();
148 fids = extractFeatureIds( rs, ft );
149 } catch ( SQLException e ) {
150 throw new DatastoreException( "Error while determining affected features of type: '" + ft.getName() + "': "
151 + e.getMessage() );
152 } finally {
153 try {
154 if ( rs != null ) {
155 try {
156 rs.close();
157 } catch ( SQLException e ) {
158 LOG.logError( "Error closing result set: '" + e.getMessage() + "'.", e );
159 }
160 }
161 } finally {
162 if ( stmt != null ) {
163 try {
164 stmt.close();
165 } catch ( SQLException e ) {
166 LOG.logError( "Error closing statement: '" + e.getMessage() + "'.", e );
167 }
168 }
169 }
170 }
171 return fids;
172 }
173
174 /**
175 * Determines the feature ids that are matched by the given filter and that are either not locked or locked by the
176 * specified lockId.
177 *
178 * @param ft
179 * non-abstract feature type
180 * @param filter
181 * constraints the feature instances
182 * @param lockId
183 * optional id of associated lock (may be null)
184 * @return the feature ids that are matched by the given filter
185 * @throws DatastoreException
186 */
187 public List<FeatureId> determineAffectedAndModifiableFIDs( MappedFeatureType ft, Filter filter, String lockId )
188 throws DatastoreException {
189
190 List<FeatureId> affectedFids = determineAffectedFIDs( ft, filter );
191 List<FeatureId> modifiableFids = new ArrayList<FeatureId>( affectedFids.size() );
192 for ( FeatureId fid : affectedFids ) {
193 String lockedBy = LockManager.getInstance().getLockId( fid );
194 if ( lockedBy != null && !lockedBy.equals( lockId ) ) {
195 String msg = Messages.getMessage( "DATASTORE_FEATURE_NOT_MODIFIABLE", fid, lockedBy );
196 LOG.logInfo( msg );
197 } else {
198 modifiableFids.add( fid );
199 }
200 }
201 return modifiableFids;
202 }
203
204 /**
205 * Determines all complex properties and contained subfeature ids for a certain feature.
206 *
207 * @param fid
208 * id of the feature
209 * @return all complex properties and contained subfeature ids of the feature
210 * @throws DatastoreException
211 */
212 public Map<MappedFeaturePropertyType, List<FeatureId>> determineSubFeatures( FeatureId fid )
213 throws DatastoreException {
214
215 LOG.logDebug( "Determining sub features of feature '" + fid + "'..." );
216 Map<MappedFeaturePropertyType, List<FeatureId>> ptToSubFids = new HashMap<MappedFeaturePropertyType, List<FeatureId>>();
217 PropertyType[] properties = fid.getFeatureType().getProperties();
218 for ( PropertyType property : properties ) {
219 MappedPropertyType pt = (MappedPropertyType) property;
220 if ( pt instanceof MappedFeaturePropertyType ) {
221 LOG.logDebug( "Complex property '" + pt.getName() + "'..." );
222 MappedFeaturePropertyType fPt = (MappedFeaturePropertyType) pt;
223 List<FeatureId> subFids = determineSubFIDs( fid, fPt );
224 ptToSubFids.put( fPt, subFids );
225 }
226 }
227 return ptToSubFids;
228 }
229
230 /**
231 * Determines the {@link FeatureId}s of the subfeatures contained in a specified feature's property.
232 *
233 * @param fid
234 * id of the feature (for which the subfeatures will be determined)
235 * @param pt
236 * property type of the feature (that contains the subfeatures)
237 * @return the matched subfeature's ids (with concrete feature types)
238 * @throws DatastoreException
239 */
240 private List<FeatureId> determineSubFIDs( FeatureId fid, MappedFeaturePropertyType pt )
241 throws DatastoreException {
242
243 LOG.logDebug( "Determining sub feature ids for feature: " + fid + " and property " + pt.getName() );
244
245 List<FeatureId> subFids = null;
246 MappedFeatureType containedFt = pt.getFeatureTypeReference().getFeatureType();
247 MappedFeatureType[] concreteFts = containedFt.getConcreteSubstitutions();
248 if ( concreteFts.length > 1 ) {
249 subFids = determineSubFIDs( fid, pt, concreteFts );
250 } else {
251 subFids = determineSubFIDs( fid, pt, containedFt );
252 }
253 return subFids;
254 }
255
256 /**
257 * Determines all super features (as {@link FeatureId} instances) for a certain feature.
258 *
259 * @param fid
260 * id of the feature
261 * @return all super feature ids of the feature
262 * @throws DatastoreException
263 */
264 public Set<FeatureId> determineSuperFeatures( FeatureId fid )
265 throws DatastoreException {
266
267 LOG.logDebug( "Determining super features of feature " + fid.getAsString() );
268 Set<FeatureId> superFeatures = new HashSet<FeatureId>();
269 MappedFeatureType subFt = fid.getFeatureType();
270 Set<FeatureType> substitutableFts = subFt.getGMLSchema().getSubstitutables( subFt );
271 Set<MappedFeatureType> superFts = determineSuperFeatureTypes( substitutableFts );
272
273 for ( MappedFeatureType superFt : superFts ) {
274 List<MappedFeaturePropertyType> featureProps = determineProperties( superFt, subFt );
275 for ( MappedFeaturePropertyType featureProp : featureProps ) {
276 superFeatures.addAll( determineSuperFids( superFt, featureProp, fid ) );
277 }
278 }
279 return superFeatures;
280 }
281
282 /**
283 * Determines all concrete feature types that can contain one or more of the given feature types inside a property.
284 *
285 * @param subFts
286 * @return all concrete feature types that can contain the given feature type
287 */
288 private Set<MappedFeatureType> determineSuperFeatureTypes( Set<FeatureType> subFts ) {
289 Set<MappedFeatureType> superFts = new HashSet<MappedFeatureType>();
290 for ( FeatureType subFt : subFts ) {
291 superFts.addAll( determineSuperFeatureTypes( (MappedFeatureType) subFt ) );
292 }
293 return superFts;
294 }
295
296 /**
297 * Determines all concrete feature types that can contain the given feature type inside a property.
298 *
299 * @param subFt
300 * @return all concrete feature types that can contain the given feature type
301 */
302 private Set<MappedFeatureType> determineSuperFeatureTypes( MappedFeatureType subFt ) {
303 Set<MappedFeatureType> superFts = new HashSet<MappedFeatureType>();
304 MappedGMLSchema schema = subFt.getGMLSchema();
305 FeatureType[] fts = schema.getFeatureTypes();
306 for ( int i = 0; i < fts.length; i++ ) {
307 MappedFeatureType ft = (MappedFeatureType) fts[i];
308 if ( !ft.isAbstract() ) {
309 PropertyType[] properties = ft.getProperties();
310 for ( int j = 0; j < properties.length; j++ ) {
311 MappedPropertyType property = (MappedPropertyType) properties[j];
312 if ( property instanceof MappedFeaturePropertyType ) {
313 MappedFeaturePropertyType ftProperty = (MappedFeaturePropertyType) property;
314 if ( ftProperty.getFeatureTypeReference().getName().equals( subFt.getName() ) ) {
315 superFts.add( ft );
316 }
317 }
318 }
319 }
320
321 }
322 return superFts;
323 }
324
325 /**
326 * Determines all {@link MappedFeaturePropertyType} instances that the super feature type has and which contain
327 * features that may be substituted for features of the given sub feature type.
328 *
329 * @param superFt
330 * @param subFt
331 * @return corresponding property types
332 */
333 private List<MappedFeaturePropertyType> determineProperties( MappedFeatureType superFt, MappedFeatureType subFt ) {
334 List<MappedFeaturePropertyType> featureProps = new ArrayList<MappedFeaturePropertyType>();
335 PropertyType[] properties = superFt.getProperties();
336 for ( PropertyType property : properties ) {
337 if ( property instanceof MappedFeaturePropertyType ) {
338 MappedFeaturePropertyType featureProperty = (MappedFeaturePropertyType) property;
339 MappedFeatureType containedFt = featureProperty.getFeatureTypeReference().getFeatureType();
340 if ( subFt.getGMLSchema().isValidSubstitution( containedFt, subFt ) ) {
341 featureProps.add( featureProperty );
342 }
343 }
344 }
345 return featureProps;
346 }
347
348 /**
349 * Determines all features (as {@link FeatureId}s) of the super feature type which contain the given feature
350 * instance in the also specified property.
351 *
352 * @param superFt
353 * @param featureProp
354 * @param subFid
355 * @return corresponding <code>DeleteNodes</code>
356 */
357 private List<FeatureId> determineSuperFids( MappedFeatureType superFt, MappedFeaturePropertyType featureProp,
358 FeatureId subFid )
359 throws DatastoreException {
360 this.aliasGenerator.reset();
361 TableRelation[] relations = featureProp.getTableRelations();
362
363 String superFtAlias = this.aliasGenerator.generateUniqueAlias();
364 String[] joinTableAliases = this.aliasGenerator.generateUniqueAliases( relations.length );
365 String subFtAlias = joinTableAliases[joinTableAliases.length - 1];
366
367 StatementBuffer query = new StatementBuffer();
368 query.append( "SELECT DISTINCT " );
369 appendFeatureIdColumns( superFt, superFtAlias, query );
370 query.append( " FROM " );
371 query.append( superFt.getTable() );
372 query.append( " " );
373 query.append( superFtAlias );
374 String fromAlias = superFtAlias;
375 for ( int i = 0; i < relations.length; i++ ) {
376 String toAlias = joinTableAliases[i];
377 query.append( " JOIN " );
378 if ( i == relations.length - 1 ) {
379 query.append( subFid.getFeatureType().getTable() );
380 } else {
381 query.append( relations[i].getToTable() );
382 }
383 query.append( " " );
384 query.append( toAlias );
385 query.append( " ON " );
386 appendJoinCondition( relations[i], fromAlias, toAlias, query );
387 fromAlias = toAlias;
388 }
389
390 query.append( " WHERE " );
391 MappedGMLId gmlId = subFid.getFidDefinition();
392 MappingField[] idFields = gmlId.getIdFields();
393 for ( int i = 0; i < idFields.length; i++ ) {
394 query.append( subFtAlias );
395 query.append( '.' );
396 query.append( idFields[i].getField() );
397 query.append( "=?" );
398 query.addArgument( subFid.getValue( i ), idFields[i].getType() );
399 if ( i != idFields.length - 1 ) {
400 query.append( " AND " );
401 }
402 }
403
404 List<FeatureId> fids = null;
405 PreparedStatement stmt = null;
406 ResultSet rs = null;
407 try {
408 stmt = this.datastore.prepareStatement( conn, query );
409 LOG.logDebug( "Performing: " + query );
410 rs = stmt.executeQuery();
411 fids = extractFeatureIds( rs, superFt );
412 } catch ( SQLException e ) {
413 LOG.logInfo( e.getMessage(), e );
414 throw new DatastoreException( "Error in determineSuperFeatures(): " + e.getMessage() );
415 } finally {
416 try {
417 if ( rs != null ) {
418 try {
419 rs.close();
420 } catch ( SQLException e ) {
421 LOG.logError( "Error closing result set: '" + e.getMessage() + "'.", e );
422 }
423 }
424 } finally {
425 if ( stmt != null ) {
426 try {
427 stmt.close();
428 } catch ( SQLException e ) {
429 LOG.logError( "Error closing statement: '" + e.getMessage() + "'.", e );
430 }
431 }
432 }
433 }
434 return fids;
435 }
436
437 /**
438 * Determines the {@link FeatureId}s of the subfeatures contained in the given feature property.
439 *
440 * @param fid
441 * id of the feature
442 * @param pt
443 * table relation from the feature table to the subfeature table
444 * @param concreteFt
445 * concrete (non-abstract) type that is contained in the feature property
446 * @return the <code>FeatureId</code> or null (if there is no such subfeature)
447 * @throws DatastoreException
448 */
449 private List<FeatureId> determineSubFIDs( FeatureId fid, MappedFeaturePropertyType pt, MappedFeatureType concreteFt )
450 throws DatastoreException {
451
452 TableRelation[] relations = pt.getTableRelations();
453
454 this.aliasGenerator.reset();
455 String[] aliases = this.aliasGenerator.generateUniqueAliases( relations.length + 1 );
456
457 StatementBuffer query = new StatementBuffer();
458 query.append( "SELECT " );
459 appendFeatureIdColumns( concreteFt, aliases[aliases.length - 1], query );
460 query.append( " FROM " );
461 query.append( relations[0].getFromTable() );
462 query.append( " " );
463 query.append( aliases[0] );
464
465 // append JOINs
466 String fromAlias = aliases[0];
467 for ( int i = 0; i < relations.length; i++ ) {
468 String toAlias = aliases[i + 1];
469 query.append( " JOIN " );
470 if ( i == relations.length - 1 ) {
471 query.append( concreteFt.getTable() );
472 } else {
473 query.append( relations[i].getToTable() );
474 }
475 query.append( " " );
476 query.append( toAlias );
477 query.append( " ON " );
478 appendJoinCondition( relations[i], fromAlias, toAlias, query );
479 fromAlias = toAlias;
480 }
481
482 query.append( " WHERE " );
483 appendFeatureIdConstraint( query, fid, aliases[0] );
484
485 List<FeatureId> subFids = null;
486 PreparedStatement stmt = null;
487 ResultSet rs = null;
488 try {
489 stmt = this.datastore.prepareStatement( conn, query );
490 LOG.logDebug( "Determining subfeature ids: " + query );
491 rs = stmt.executeQuery();
492 subFids = extractFeatureIds( rs, concreteFt );
493 } catch ( SQLException e ) {
494 LOG.logDebug( e.getMessage(), e );
495 throw new DatastoreException( "Error in #determineSubFIDs(): " + e.getMessage() );
496 } finally {
497 try {
498 if ( rs != null ) {
499 try {
500 rs.close();
501 } catch ( SQLException e ) {
502 LOG.logError( "Error closing result set: '" + e.getMessage() + "'.", e );
503 }
504 }
505 } finally {
506 if ( stmt != null ) {
507 try {
508 stmt.close();
509 } catch ( SQLException e ) {
510 LOG.logError( "Error closing statement: '" + e.getMessage() + "'.", e );
511 }
512 }
513 }
514 }
515 return subFids;
516 }
517
518 /**
519 * Determines the feature ids of the subfeatures contained in the given feature property (that may contain features
520 * of different concrete types).
521 *
522 * @param fid
523 * id of the feature
524 * @param pt
525 * complex property that contains the subfeatures
526 * @param concreteSubFts
527 * all possible non-abstract feature types of the subfeatures
528 * @return the ids of the subfeatures
529 * @throws DatastoreException
530 */
531 private List<FeatureId> determineSubFIDs( FeatureId fid, MappedFeaturePropertyType pt,
532 MappedFeatureType[] concreteSubFts )
533 throws DatastoreException {
534
535 List<FeatureId> subFids = null;
536
537 TableRelation[] relations = pt.getTableRelations();
538 LOG.logDebug( "Determining sub feature ids for feature " + fid + ": relations.length: " + relations.length );
539
540 switch ( relations.length ) {
541 case 1: {
542 // subfeature disambiguator in feature table (only zero or one subfeatures)
543 MappedFeatureType concreteSubFt = determineSubFt( fid, pt, concreteSubFts );
544 subFids = new ArrayList<FeatureId>( 1 );
545 if ( concreteSubFt != null ) {
546 FeatureId subFid = determineSubFID( fid, relations[0], concreteSubFt );
547 if ( subFid != null ) {
548 subFids.add( subFid );
549 }
550 }
551 break;
552 }
553 case 2: {
554 // subfeature disambiguator in join table (any number of subfeatures)
555 subFids = determineSubFIDs( fid, pt, concreteSubFts, relations );
556 break;
557 }
558 default: {
559 String msg = Messages.getMessage( "DATASTORE_SUBFT_TOO_MANY_RELATIONS", fid.getFeatureType().getName(),
560 pt.getName() );
561 throw new DatastoreException( msg );
562 }
563 }
564 return subFids;
565 }
566
567 /**
568 * Determine the concrete type of the subfeature that is stored in the specified property of a certain feature.
569 * <p>
570 * The relation to the sub feature table must be specified via a single step (join).
571 *
572 * @param fid
573 * id of the feature for which the concrete subfeature type is needed
574 * @param pt
575 * property of the feature that contains the subfeature
576 * @param concreteSubFts
577 * concrete types that may be contained in the property
578 * @return concrete type of the subfeature, or null if feature has no such property
579 * @throws DatastoreException
580 */
581 private MappedFeatureType determineSubFt( FeatureId fid, MappedFeaturePropertyType pt,
582 MappedFeatureType[] concreteSubFts )
583 throws DatastoreException {
584
585 assert ( pt.getTableRelations().length == 1 );
586 TableRelation relation = pt.getTableRelations()[0];
587
588 assert ( relation.getFromFields().length == 1 );
589 String fkColumn = relation.getFromFields()[0].getField();
590 String subFtColumn = FT_PREFIX + fkColumn;
591
592 StatementBuffer query = new StatementBuffer();
593 query.append( "SELECT " );
594 query.append( subFtColumn );
595 query.append( " FROM " );
596 query.append( fid.getFeatureType().getTable() );
597 query.append( " WHERE " );
598 appendFeatureIdConstraint( query, fid );
599
600 String localSubFtName = null;
601 PreparedStatement stmt = null;
602 ResultSet rs = null;
603 try {
604 stmt = this.datastore.prepareStatement( conn, query );
605 LOG.logDebug( "Determining concrete subfeature type: " + query );
606 rs = stmt.executeQuery();
607 rs.next();
608 localSubFtName = rs.getString( 1 );
609 } catch ( SQLException e ) {
610 LOG.logDebug( e.getMessage(), e );
611 throw new DatastoreException( "Error in determineConcreteSubFt() " + e.getMessage() );
612 } finally {
613 try {
614 if ( rs != null ) {
615 try {
616 rs.close();
617 } catch ( SQLException e ) {
618 LOG.logError( "Error closing result set: '" + e.getMessage() + "'.", e );
619 }
620 }
621 } finally {
622 if ( stmt != null ) {
623 try {
624 stmt.close();
625 } catch ( SQLException e ) {
626 LOG.logError( "Error closing statement: '" + e.getMessage() + "'.", e );
627 }
628 }
629 }
630 }
631
632 MappedFeatureType concreteSubFt = null;
633
634 if ( localSubFtName != null ) {
635 for ( MappedFeatureType type : concreteSubFts ) {
636 if ( type.getName().getLocalName().equals( localSubFtName ) ) {
637 concreteSubFt = fid.getFeatureType().getGMLSchema().getFeatureType( localSubFtName );
638 break;
639 }
640 }
641 if ( concreteSubFt == null ) {
642 String msg = Messages.getMessage( "DATASTORE_FEATURE_TYPE_INFO_INCONSISTENT", pt.getName(), fid,
643 subFtColumn, localSubFtName, pt.getFeatureTypeReference().getName() );
644 throw new DatastoreException( msg );
645 }
646 }
647
648 return concreteSubFt;
649 }
650
651 /**
652 * Determines the {@link FeatureId} of the subfeature contained in the given feature property (if the feature has
653 * such a subfeature).
654 *
655 * @param fid
656 * id of the feature
657 * @param relation
658 * table relation from the feature table to the subfeature table
659 * @param concreteFt
660 * concrete (non-abstract) type that is contained in the feature property
661 * @return the <code>FeatureId</code> or null (if there is no such subfeature)
662 * @throws DatastoreException
663 */
664 private FeatureId determineSubFID( FeatureId fid, TableRelation relation, MappedFeatureType concreteFt )
665 throws DatastoreException {
666
667 this.aliasGenerator.reset();
668 String fromAlias = this.aliasGenerator.generateUniqueAlias();
669 String toAlias = this.aliasGenerator.generateUniqueAlias();
670
671 StatementBuffer query = new StatementBuffer();
672 query.append( "SELECT " );
673 appendFeatureIdColumns( concreteFt, toAlias, query );
674 query.append( " FROM " );
675 query.append( relation.getFromTable() );
676 query.append( " " );
677 query.append( fromAlias );
678 query.append( " JOIN " );
679 query.append( concreteFt.getTable() );
680 query.append( " " );
681 query.append( toAlias );
682 query.append( " ON " );
683 appendJoinCondition( relation, fromAlias, toAlias, query );
684 query.append( " WHERE " );
685 appendFeatureIdConstraint( query, fid, fromAlias );
686
687 FeatureId subFid = null;
688 PreparedStatement stmt = null;
689 ResultSet rs = null;
690 try {
691 stmt = this.datastore.prepareStatement( conn, query );
692 LOG.logDebug( "Determining subfeature id: " + query );
693 rs = stmt.executeQuery();
694 if ( rs.next() ) {
695 subFid = extractFeatureId( rs, concreteFt );
696 }
697 } catch ( SQLException e ) {
698 LOG.logDebug( e.getMessage(), e );
699 throw new DatastoreException( "Error in #determineSubFID(): " + e.getMessage() );
700 } finally {
701 try {
702 if ( rs != null ) {
703 try {
704 rs.close();
705 } catch ( SQLException e ) {
706 LOG.logError( "Error closing result set: '" + e.getMessage() + "'.", e );
707 }
708 }
709 } finally {
710 if ( stmt != null ) {
711 try {
712 stmt.close();
713 } catch ( SQLException e ) {
714 LOG.logError( "Error closing statement: '" + e.getMessage() + "'.", e );
715 }
716 }
717 }
718 }
719 return subFid;
720 }
721
722 /**
723 * Determines the feature ids of the subfeatures contained in the given feature property (that may contain features
724 * of different concrete types and is connected via a join table with feature type disambiguation column).
725 *
726 * @param fid
727 * @param pt
728 * @return the matched subfeatures' ids
729 * @throws DatastoreException
730 */
731 private List<FeatureId> determineSubFIDs( FeatureId fid, MappedFeaturePropertyType pt,
732 MappedFeatureType[] concreteSubFts, TableRelation[] relations )
733 throws DatastoreException {
734 this.aliasGenerator.reset();
735 String fromAlias = this.aliasGenerator.generateUniqueAlias();
736 String jtAlias = this.aliasGenerator.generateUniqueAlias();
737
738 StatementBuffer query = new StatementBuffer();
739 query.append( "SELECT " );
740
741 // select feature type disambiguation column and from fields of second table relation
742 appendQualifiedColumn( query, jtAlias, FT_COLUMN );
743 MappingField[] fromFields = relations[1].getFromFields();
744 for ( int i = 0; i < fromFields.length; i++ ) {
745 query.append( ',' );
746 appendQualifiedColumn( query, jtAlias, fromFields[i].getField() );
747 }
748
749 query.append( " FROM " );
750 query.append( relations[0].getFromTable() );
751 query.append( " " );
752 query.append( fromAlias );
753 query.append( " JOIN " );
754 query.append( relations[0].getToTable() );
755 query.append( " " );
756 query.append( jtAlias );
757 query.append( " ON " );
758 appendJoinCondition( relations[0], fromAlias, jtAlias, query );
759 query.append( " WHERE " );
760 appendFeatureIdConstraint( query, fid, fromAlias );
761
762 List<FeatureId> subFids = new ArrayList<FeatureId>();
763 PreparedStatement stmt = null;
764 ResultSet rs = null;
765 try {
766 stmt = this.datastore.prepareStatement( conn, query );
767 LOG.logDebug( "Determining concrete subfeature types and join keys: " + query );
768 rs = stmt.executeQuery();
769 Object[] keyComponents = new Object[relations[1].getFromFields().length];
770 while ( rs.next() ) {
771 String localSubFtName = rs.getString( 1 );
772 for ( int i = 0; i < keyComponents.length; i++ ) {
773 keyComponents[i] = rs.getObject( i + 2 );
774 }
775 MappedFeatureType concreteSubFt = null;
776 for ( MappedFeatureType type : concreteSubFts ) {
777 if ( type.getName().getLocalName().equals( localSubFtName ) ) {
778 concreteSubFt = fid.getFeatureType().getGMLSchema().getFeatureType( localSubFtName );
779 break;
780 }
781 }
782 if ( concreteSubFt == null ) {
783 String msg = Messages.getMessage( "DATASTORE_FEATURE_TYPE_INFO_INCONSISTENT", pt.getName(), fid,
784 FT_COLUMN, localSubFtName, pt.getFeatureTypeReference().getName() );
785 throw new DatastoreException( msg );
786 }
787
788 subFids.add( determineSubFID( concreteSubFt, relations[1], keyComponents ) );
789 }
790 } catch ( SQLException e ) {
791 LOG.logDebug( e.getMessage(), e );
792 throw new DatastoreException( "Error in #determineSubFIDs(): " + e.getMessage() );
793 } finally {
794 try {
795 if ( rs != null ) {
796 try {
797 rs.close();
798 } catch ( SQLException e ) {
799 LOG.logError( "Error closing result set: '" + e.getMessage() + "'.", e );
800 }
801 }
802 } finally {
803 if ( stmt != null ) {
804 try {
805 stmt.close();
806 } catch ( SQLException e ) {
807 LOG.logError( "Error closing statement: '" + e.getMessage() + "'.", e );
808 }
809 }
810 }
811 }
812 return subFids;
813 }
814
815 /**
816 * Determines the {@link FeatureId} of the subfeature referenced by the given {@link TableRelation}.
817 *
818 * @param concreteFt
819 * conrete (non-abstract) type that is contained in the feature property
820 * @param relation
821 * table relation from the join table to the subfeature table
822 * @param keyComponents
823 * @return the <code>FeatureId</code> or null (if there is no such subfeature)
824 * @throws DatastoreException
825 */
826 private FeatureId determineSubFID( MappedFeatureType concreteSubFt, TableRelation relation, Object[] keyComponents )
827 throws DatastoreException {
828 this.aliasGenerator.reset();
829 String fromAlias = this.aliasGenerator.generateUniqueAlias();
830 String toAlias = this.aliasGenerator.generateUniqueAlias();
831
832 StatementBuffer query = new StatementBuffer();
833 query.append( "SELECT " );
834 appendFeatureIdColumns( concreteSubFt, toAlias, query );
835 query.append( " FROM " );
836 query.append( relation.getFromTable() );
837 query.append( " " );
838 query.append( fromAlias );
839 query.append( " JOIN " );
840 query.append( concreteSubFt.getTable() );
841 query.append( " " );
842 query.append( toAlias );
843 query.append( " ON " );
844 appendJoinCondition( relation, fromAlias, toAlias, query );
845 query.append( " WHERE " );
846 for ( int i = 0; i < keyComponents.length; i++ ) {
847 appendQualifiedColumn( query, fromAlias, relation.getFromFields()[i].getField() );
848 query.append( "=?" );
849 query.addArgument( keyComponents[i], relation.getFromFields()[i].getType() );
850 if ( i != keyComponents.length - 1 ) {
851 query.append( " AND " );
852 }
853 }
854 FeatureId subFid = null;
855 PreparedStatement stmt = null;
856 ResultSet rs = null;
857 try {
858 stmt = this.datastore.prepareStatement( conn, query );
859 LOG.logDebug( "Determining subfeature id: " + query );
860 rs = stmt.executeQuery();
861 if ( rs.next() ) {
862 subFid = extractFeatureId( rs, concreteSubFt );
863 }
864 } catch ( SQLException e ) {
865 LOG.logDebug( e.getMessage(), e );
866 throw new DatastoreException( "Error in #determineSubFID(): " + e.getMessage() );
867 } finally {
868 try {
869 if ( rs != null ) {
870 try {
871 rs.close();
872 } catch ( SQLException e ) {
873 LOG.logError( "Error closing result set: '" + e.getMessage() + "'.", e );
874 }
875 }
876 } finally {
877 if ( stmt != null ) {
878 try {
879 stmt.close();
880 } catch ( SQLException e ) {
881 LOG.logError( "Error closing statement: '" + e.getMessage() + "'.", e );
882 }
883 }
884 }
885 }
886 return subFid;
887 }
888
889 /**
890 * Builds the initial SELECT statement that retrieves the feature ids that are matched by the given
891 * <code>WhereBuilder</code>.
892 * <p>
893 * The statement is structured like this:
894 * <ul>
895 * <li><code>SELECT</code></li>
896 * <li>comma-separated list of qualified fid fields</li>
897 * <li><code>FROM</code></li>
898 * <li>comma-separated list of tables and their aliases (this is needed to constrain the paths to selected
899 * XPath-PropertyNames)</li>
900 * <li><code>WHERE</code></li>
901 * <li>SQL representation of the Filter expression</li>
902 * </ul>
903 *
904 * @param rootFt
905 * @param whereBuilder
906 * @return initial SELECT statement to retrieve the feature ids
907 * @throws DatastoreException
908 */
909 private StatementBuffer buildInitialFIDSelect( MappedFeatureType rootFt, WhereBuilder whereBuilder )
910 throws DatastoreException {
911
912 String tableAlias = whereBuilder.getRootTableAlias( 0 );
913 StatementBuffer query = new StatementBuffer();
914 query.append( "SELECT " );
915 appendFeatureIdColumns( rootFt, tableAlias, query );
916 query.append( " FROM " );
917 whereBuilder.appendJoinTableList( query );
918 whereBuilder.appendWhereCondition( query );
919 return query;
920 }
921
922 /**
923 * Appends the alias qualified columns that make up the feature id to the given query.
924 *
925 * @param featureType
926 * @param tableAlias
927 * @param query
928 */
929 protected void appendFeatureIdColumns( MappedFeatureType featureType, String tableAlias, StatementBuffer query ) {
930 MappingField[] fidFields = featureType.getGMLId().getIdFields();
931 for ( int i = 0; i < fidFields.length; i++ ) {
932 query.append( tableAlias );
933 query.append( '.' );
934 query.append( fidFields[i].getField() );
935 if ( i != fidFields.length - 1 ) {
936 query.append( ',' );
937 }
938 }
939 }
940
941 /**
942 * Extracts the {@FeatureId} in the current row of the given {@link ResultSet}.
943 *
944 * @param rs
945 * @param ft
946 * feature type (may not be abstract)
947 * @return feature ids
948 * @throws SQLException
949 * @throws DatastoreException
950 */
951 protected FeatureId extractFeatureId( ResultSet rs, MappedFeatureType ft )
952 throws SQLException, DatastoreException {
953
954 MappedGMLId gmlId = ft.getGMLId();
955 MappingField[] idFields = gmlId.getIdFields();
956
957 Object[] idValues = new Object[idFields.length];
958 for ( int i = 0; i < idValues.length; i++ ) {
959 Object idValue = rs.getObject( i + 1 );
960 if ( idValue == null ) {
961 String msg = Messages.getMessage( "DATASTORE_FEATURE_ID_NULL", ft.getTable(), ft.getName(),
962 idFields[i].getField() );
963 throw new DatastoreException( msg );
964 }
965 idValues[i] = idValue;
966 }
967
968 return new FeatureId( ft, idValues );
969 }
970
971 /**
972 * Extracts the feature ids in the given {@link ResultSet} as a {@List} of {@FeatureId}s.
973 * <p>
974 * If the given feature type is abstract, it is expected that the first column of the result set contains the local
975 * name of the feature type.
976 *
977 * @param rs
978 * @param ft
979 * feature type (may be abstract)
980 * @return feature ids
981 * @throws SQLException
982 * @throws DatastoreException
983 */
984 protected List<FeatureId> extractFeatureIds( ResultSet rs, MappedFeatureType ft )
985 throws SQLException, DatastoreException {
986 List<FeatureId> featureIdList = new ArrayList<FeatureId>();
987 MappedGMLId gmlId = ft.getGMLId();
988 MappingField[] idFields = gmlId.getIdFields();
989
990 boolean needsDisambiguation = ft.hasSeveralImplementations();
991
992 while ( rs.next() ) {
993 int offset = 1;
994 if ( needsDisambiguation ) {
995 String localFtName = rs.getString( 1 );
996 ft = ft.getGMLSchema().getFeatureType( localFtName );
997 gmlId = ft.getGMLId();
998 idFields = gmlId.getIdFields();
999 offset = 2;
1000 }
1001 Object[] idValues = new Object[idFields.length];
1002 for ( int i = 0; i < idValues.length; i++ ) {
1003 Object idValue = rs.getObject( i + offset );
1004 if ( idValue == null ) {
1005 String msg = Messages.getMessage( "DATASTORE_FEATURE_ID_NULL", ft.getTable(), ft.getName(),
1006 idFields[i].getField() );
1007 throw new DatastoreException( msg );
1008 }
1009 idValues[i] = idValue;
1010 }
1011 featureIdList.add( new FeatureId( ft, idValues ) );
1012 }
1013 return featureIdList;
1014 }
1015
1016 protected void appendJoins( TableRelation[] tableRelation, String fromAlias, String[] toAliases,
1017 StatementBuffer query ) {
1018 for ( int i = 0; i < toAliases.length; i++ ) {
1019 String toAlias = toAliases[i];
1020 appendJoin( tableRelation[i], fromAlias, toAlias, query );
1021 fromAlias = toAlias;
1022 }
1023 }
1024
1025 private void appendJoin( TableRelation tableRelation, String fromAlias, String toAlias, StatementBuffer query ) {
1026 query.append( " JOIN " );
1027 query.append( tableRelation.getToTable() );
1028 query.append( " " );
1029 query.append( toAlias );
1030 query.append( " ON " );
1031 appendJoinCondition( tableRelation, fromAlias, toAlias, query );
1032 }
1033
1034 protected void appendJoinCondition( TableRelation tableRelation, String fromAlias, String toAlias,
1035 StatementBuffer query ) {
1036
1037 MappingField[] fromFields = tableRelation.getFromFields();
1038 MappingField[] toFields = tableRelation.getToFields();
1039 for ( int i = 0; i < fromFields.length; i++ ) {
1040 query.append( toAlias );
1041 query.append( "." );
1042 query.append( toFields[i].getField() );
1043 query.append( "=" );
1044 query.append( fromAlias );
1045 query.append( "." );
1046 query.append( fromFields[i].getField() );
1047 if ( i != fromFields.length - 1 ) {
1048 query.append( " AND " );
1049 }
1050 }
1051 }
1052
1053 protected void appendFeatureIdConstraint( StatementBuffer query, FeatureId fid ) {
1054 MappingField[] idFields = fid.getFidDefinition().getIdFields();
1055 for ( int i = 0; i < idFields.length; i++ ) {
1056 query.append( idFields[i].getField() );
1057 query.append( "=?" );
1058 query.addArgument( fid.getValue( i ), idFields[i].getType() );
1059 if ( i < idFields.length - 1 ) {
1060 query.append( " AND " );
1061 }
1062 }
1063 }
1064
1065 protected void appendFeatureIdConstraint( StatementBuffer query, FeatureId fid, String tableAlias ) {
1066 MappingField[] idFields = fid.getFidDefinition().getIdFields();
1067 for ( int i = 0; i < idFields.length; i++ ) {
1068 query.append( tableAlias );
1069 query.append( '.' );
1070 query.append( idFields[i].getField() );
1071 query.append( "=?" );
1072 query.addArgument( fid.getValue( i ), idFields[i].getType() );
1073 if ( i < idFields.length - 1 ) {
1074 query.append( " AND " );
1075 }
1076 }
1077 }
1078
1079 /**
1080 * Appends the specified columns as a comma-separated list to the given query.
1081 *
1082 * @param query
1083 * StatementBuffer that the list is appended to
1084 * @param columns
1085 * array of column names
1086 */
1087 public void appendColumnsList( StatementBuffer query, String[] columns ) {
1088 for ( int i = 0; i < columns.length; i++ ) {
1089 if ( columns[i].indexOf( '$' ) != -1 ) {
1090 // function call
1091 String column = columns[i];
1092 column = column.replaceAll( "\\$\\.", "" );
1093 query.append( column );
1094
1095 } else {
1096 query.append( columns[i] );
1097 }
1098
1099 if ( i != columns.length - 1 ) {
1100 query.append( ',' );
1101 }
1102 }
1103 }
1104
1105 /**
1106 * Appends the specified columns as alias-qualified, comma-separated list to the given query.
1107 *
1108 * @param query
1109 * StatementBuffer that the list is appended to
1110 * @param tableAlias
1111 * alias to use as qualifier (alias.field)
1112 * @param columns
1113 * array of column names
1114 */
1115 public void appendQualifiedColumnsList( StatementBuffer query, String tableAlias, String[] columns ) {
1116 for ( int i = 0; i < columns.length; i++ ) {
1117 appendQualifiedColumn( query, tableAlias, columns[i] );
1118 if ( i != columns.length - 1 ) {
1119 query.append( ',' );
1120 }
1121 }
1122 }
1123
1124 /**
1125 * Appends the specified column to the given query.
1126 *
1127 * @param query
1128 * StatementBuffer that the list is appended to
1129 * @param tableAlias
1130 * alias to use as qualifier (alias.field)
1131 * @param column
1132 * column name
1133 */
1134 public void appendQualifiedColumn( StatementBuffer query, String tableAlias, String column ) {
1135 query.append( tableAlias );
1136 query.append( '.' );
1137 query.append( column );
1138 }
1139 }