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