001 //$HeadURL: http://svn.wald.intevation.org/svn/deegree/base/trunk/src/org/deegree/io/datastore/sql/QueryHandler.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.Collection;
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.datatypes.QualifiedName;
050 import org.deegree.framework.log.ILogger;
051 import org.deegree.framework.log.LoggerFactory;
052 import org.deegree.io.datastore.DatastoreException;
053 import org.deegree.io.datastore.FeatureId;
054 import org.deegree.io.datastore.PropertyPathResolvingException;
055 import org.deegree.io.datastore.schema.MappedFeatureType;
056 import org.deegree.io.datastore.schema.MappedPropertyType;
057 import org.deegree.io.datastore.schema.content.SimpleContent;
058 import org.deegree.io.datastore.sql.wherebuilder.QueryTableTree;
059 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
060 import org.deegree.model.crs.UnknownCRSException;
061 import org.deegree.model.feature.Feature;
062 import org.deegree.model.feature.FeatureCollection;
063 import org.deegree.model.feature.FeatureFactory;
064 import org.deegree.model.feature.FeatureProperty;
065 import org.deegree.model.feature.FeatureTupleCollection;
066 import org.deegree.model.filterencoding.PropertyName;
067 import org.deegree.model.spatialschema.Envelope;
068 import org.deegree.model.spatialschema.GeometryException;
069 import org.deegree.ogcbase.PropertyPath;
070 import org.deegree.ogcwebservices.wfs.operation.Query;
071 import org.deegree.ogcwebservices.wfs.operation.GetFeature.RESULT_TYPE;
072
073 /**
074 * Handles {@link Query} requests to SQL backed datastores.
075 *
076 * @see FeatureFetcher
077 * @see AbstractSQLDatastore
078 * @see QueryTableTree
079 *
080 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
081 * @author last edited by: $Author: apoth $
082 *
083 * @version $Revision: 29956 $, $Date: 2011-03-09 14:51:48 +0100 (Wed, 09 Mar 2011) $
084 */
085 public class QueryHandler extends FeatureFetcher {
086
087 private static final ILogger LOG = LoggerFactory.getLogger( QueryHandler.class );
088
089 private Query query;
090
091 // targeted feature types, more than one implies that a join of feature types is requested
092 private MappedFeatureType[] rootFts;
093
094 // used to build the initial SELECT (especially the WHERE-clause)
095 private WhereBuilder whereBuilder;
096
097 // TODO hack for making gml:boundedBy available even if the geometry properties are not queried
098 private List<PropertyName> augmentedGeoProps;
099
100 /**
101 * Creates a new instance of <code>QueryHandler</code> from the given parameters.
102 *
103 * @param ds
104 * datastore that spawned this QueryHandler
105 * @param aliasGenerator
106 * used to generate unique aliases for the tables in the SELECT statements
107 * @param conn
108 * JDBCConnection to execute the generated SELECT statements against
109 * @param rootFts
110 * the root feature types that are queried, more than one type means that the types are joined
111 * @param query
112 * query to perform
113 * @throws DatastoreException
114 */
115 public QueryHandler( AbstractSQLDatastore ds, TableAliasGenerator aliasGenerator, Connection conn,
116 MappedFeatureType[] rootFts, Query query ) throws DatastoreException {
117
118 super( ds, aliasGenerator, conn, query );
119
120 this.query = query;
121 this.rootFts = rootFts;
122 this.vcProvider = new VirtualContentProvider( query.getFilter(), ds, conn );
123 this.whereBuilder = this.datastore.getWhereBuilder( rootFts, query.getAliases(), query.getFilter(),
124 query.getSortProperties(), aliasGenerator, this.vcProvider );
125 this.aliasGenerator = aliasGenerator;
126 }
127
128 /**
129 * Performs the associated {@link Query} against the datastore.
130 *
131 * @return collection of requested features
132 * @throws SQLException
133 * if a JDBC error occurs
134 * @throws DatastoreException
135 * @throws UnknownCRSException
136 */
137 public FeatureCollection performQuery()
138 throws SQLException, DatastoreException, UnknownCRSException {
139
140 long start = -1;
141 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
142 start = System.currentTimeMillis();
143 }
144
145 FeatureCollection result = null;
146
147 if ( this.query.getResultType() == RESULT_TYPE.HITS ) {
148 result = performHitsQuery();
149 } else {
150 result = performResultsQuery();
151 }
152
153 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
154 long elapsed = System.currentTimeMillis() - start;
155 LOG.logDebug( "Performing of query took " + elapsed + " milliseconds." );
156 }
157
158 return result;
159 }
160
161 /**
162 * Performs a query for the feature instances that match the filter constraints. This corresponds to a query with
163 * resultType=RESULTS.
164 *
165 * @return collection of requested features
166 * @throws PropertyPathResolvingException
167 * @throws SQLException
168 * @throws DatastoreException
169 * @throws UnknownCRSException
170 */
171 private FeatureCollection performResultsQuery()
172 throws PropertyPathResolvingException, SQLException, DatastoreException,
173 UnknownCRSException {
174
175 FeatureCollection result = null;
176
177 SelectManager selectManager = new SelectManager( query, this.rootFts, this );
178 LOG.logDebug( "SelectManager: " + selectManager );
179
180 // build initial SQL query
181 StatementBuffer querybuf = buildInitialSelect( selectManager );
182 LOG.logDebug( "Initial query: '" + querybuf + "'" );
183
184 PreparedStatement stmt = null;
185 ResultSet rs = null;
186
187 try {
188 stmt = this.datastore.prepareStatement( this.conn, querybuf );
189 rs = stmt.executeQuery();
190 if ( this.rootFts.length == 1 ) {
191 result = performSimpleResultsQuery( rs, selectManager );
192 } else {
193 result = performJoinResultsQuery( rs, selectManager );
194 }
195 } finally {
196 try {
197 if ( rs != null ) {
198 rs.close();
199 }
200 } finally {
201 if ( stmt != null ) {
202 stmt.close();
203 }
204 }
205 }
206
207 resolveFeatureReferences();
208 result.setAttribute( "numberOfFeatures", "" + result.size() );
209 return result;
210 }
211
212 private FeatureCollection performSimpleResultsQuery( ResultSet rs, SelectManager selectManager )
213 throws DatastoreException, SQLException, UnknownCRSException {
214
215 MappedFeatureType rootFt = this.rootFts[0];
216 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropertyMap = selectManager.getAllFetchProps()[0];
217 Map<SimpleContent, Integer> resultPosMap = selectManager.getResultPosMaps()[0];
218
219 FeatureCollection result = FeatureFactory.createFeatureCollection( "ID", 10000 );
220 Object[] resultValues = new Object[selectManager.getFetchContentCount()];
221
222 // used to handle that a feature may occur several times in result set
223 Set<FeatureId> rootFeatureIds = new HashSet<FeatureId>();
224
225 // skip features in resultSet (startPosition is first feature to be included)
226 int startPosition = this.query.getStartPosition();
227 Set<FeatureId> skippedFeatures = new HashSet<FeatureId>();
228 while ( skippedFeatures.size() < startPosition - 1 && rs.next() ) {
229 LOG.logDebug( "Skipping result row." );
230 // collect result values
231 for ( int i = 0; i < resultValues.length; i++ ) {
232 resultValues[i] = rs.getObject( i + 1 );
233 }
234 FeatureId fid = extractFeatureId( rootFt, selectManager.getResultPosMaps()[0], resultValues );
235 skippedFeatures.add( fid );
236 }
237
238 int maxFeatures = this.query.getMaxFeatures();
239 while ( rs.next() ) {
240
241 // already maxFeature features extracted?
242 if ( maxFeatures != -1 && rootFeatureIds.size() == maxFeatures ) {
243 break;
244 }
245
246 // collect result values
247 for ( int i = 0; i < resultValues.length; i++ ) {
248 resultValues[i] = rs.getObject( i + 1 );
249 }
250
251 FeatureId fid = extractFeatureId( rootFt, resultPosMap, resultValues );
252
253 // skip it if this root feature has already been fetched or if it is a feature
254 // (again) that has been skipped
255 if ( !rootFeatureIds.contains( fid ) && !skippedFeatures.contains( fid ) ) {
256
257 rootFeatureIds.add( fid );
258
259 // feature may have been fetched as a subfeature already
260 Feature feature = this.featureMap.get( fid );
261 if ( feature == null ) {
262 feature = extractFeature( fid, requestedPropertyMap, resultPosMap, resultValues );
263 // hack that ensures that the boundedBy information is correct
264 if ( !selectManager.augmentedGeometryProps.isEmpty() ) {
265 try {
266 Envelope boundedBy = feature.getBoundedBy();
267 for ( PropertyPath unqueriedGeoProp : selectManager.augmentedGeometryProps ) {
268 LOG.logDebug( "Removing " + unqueriedGeoProp + " from feature instance." );
269 feature.removeProperty( unqueriedGeoProp.getStep( 1 ).getPropertyName() );
270 }
271 feature.setProperty(
272 FeatureFactory.createFeatureProperty(
273 new QualifiedName( "boundedBy" ),
274 boundedBy ), 1 );
275 } catch ( GeometryException e ) {
276 // TODO Auto-generated catch block
277 e.printStackTrace();
278 }
279 }
280
281 }
282 result.add( feature );
283 }
284 }
285 return result;
286 }
287
288 private FeatureTupleCollection performJoinResultsQuery( ResultSet rs, SelectManager selectManager )
289 throws DatastoreException, SQLException, UnknownCRSException {
290
291 List<Feature[]> resultTuples = new ArrayList<Feature[]>();
292
293 // used to handle that a feature may occur several times in result set
294 Set<String> rootFeatureIds = new HashSet<String>( 1000 );
295
296 Object[] resultValues = new Object[selectManager.getFetchContentCount()];
297 int maxFeatures = this.query.getMaxFeatures();
298
299 int[] resultFtIdx = selectManager.getIncludedFtIdx();
300 for ( int i = 0; i < resultFtIdx.length; i++ ) {
301 LOG.logDebug( "Included in result set: " + resultFtIdx[i] );
302 }
303
304 while ( rs.next() ) {
305
306 Feature[] resultTuple = new Feature[resultFtIdx.length];
307
308 // already maxFeature features extracted?
309 if ( maxFeatures != -1 && resultTuples.size() == maxFeatures ) {
310 break;
311 }
312
313 // collect result values
314 for ( int i = 0; i < resultValues.length; i++ ) {
315 resultValues[i] = rs.getObject( i + 1 );
316 }
317
318 FeatureId[] fids = new FeatureId[resultFtIdx.length];
319 StringBuffer combinedFid = new StringBuffer();
320
321 // build combined fid to identify unique "features" (actually these are feature tuples)
322 for ( int i = 0; i < resultFtIdx.length; i++ ) {
323 int idx = resultFtIdx[i];
324 MappedFeatureType rootFt = this.rootFts[idx];
325 Map<SimpleContent, Integer> resultPosMap = selectManager.getResultPosMaps()[idx];
326 fids[i] = extractFeatureId( rootFt, resultPosMap, resultValues );
327 combinedFid.append( fids[i].getAsString() );
328 }
329 LOG.logDebug( "CombinedFID: " + combinedFid );
330
331 // if tuple has not been added to result yet, extract and add it
332 if ( !rootFeatureIds.contains( combinedFid.toString() ) ) {
333 for ( int i = 0; i < resultFtIdx.length; i++ ) {
334 int ftIdx = resultFtIdx[i];
335 FeatureId fid = fids[i];
336 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropertyMap = selectManager.getAllFetchProps()[ftIdx];
337 Map<SimpleContent, Integer> resultPosMap = selectManager.getResultPosMaps()[ftIdx];
338
339 // feature may have been fetched already
340 Feature feature = this.featureMap.get( fid );
341 if ( feature == null ) {
342 feature = extractFeature( fid, requestedPropertyMap, resultPosMap, resultValues );
343 }
344 resultTuple[i] = ( feature );
345 }
346 resultTuples.add( resultTuple );
347 rootFeatureIds.add( combinedFid.toString() );
348 }
349 }
350
351 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
352 for ( int i = 0; i < resultTuples.size(); i++ ) {
353 Feature[] resultTuple = resultTuples.get( i );
354 StringBuffer sb = new StringBuffer();
355 for ( int j = 0; j < resultFtIdx.length; j++ ) {
356 int idx = resultFtIdx[j];
357 sb.append( resultTuple[j].getId() );
358 if ( idx != this.rootFts.length - 1 ) {
359 sb.append( ',' );
360 }
361 }
362 LOG.logDebug( sb.toString() );
363 }
364 }
365
366 FeatureTupleCollection result = FeatureFactory.createFeatureCollection( "id", resultTuples, this.rootFts.length );
367 return result;
368 }
369
370 private void resolveFeatureReferences() {
371 for ( FeatureId fid : this.fidToPropertyMap.keySet() ) {
372 Feature feature = this.featureMap.get( fid );
373 assert feature != null;
374 for ( FeatureProperty property : this.fidToPropertyMap.get( fid ) ) {
375 property.setValue( feature );
376 }
377 }
378 }
379
380 /**
381 * Performs a query for the number feature instances that match the query constraints. This corresponds to a query
382 * with resultType=HITS.
383 *
384 * @return a feature collection containing number of features that match the query constraints
385 * @throws SQLException
386 * @throws DatastoreException
387 */
388 private FeatureCollection performHitsQuery()
389 throws SQLException, DatastoreException {
390
391 FeatureCollection result = FeatureFactory.createFeatureCollection( "ID", 2 );
392
393 String tableAlias = this.whereBuilder.getRootTableAlias( 0 );
394 String field = this.rootFts[0].getGMLId().getIdFields()[0].getField();
395 StatementBuffer query = new StatementBuffer();
396 query.append( "SELECT COUNT( DISTINCT " );
397 query.append( tableAlias + '.' + field );
398 query.append( ") FROM " );
399
400 whereBuilder.appendJoinTableList( query );
401 whereBuilder.appendWhereCondition( query );
402 LOG.logDebug( "Count query: '" + query + "'" );
403
404 ResultSet rs = null;
405 PreparedStatement stmt = this.datastore.prepareStatement( this.conn, query );
406 try {
407 rs = stmt.executeQuery();
408 if ( rs.next() ) {
409 result.setAttribute( "numberOfFeatures", rs.getObject( 1 ).toString() );
410 } else {
411 LOG.logError( "Internal error. Count result is empty (no rows)." );
412 throw new SQLException();
413 }
414 } catch ( SQLException e ) {
415 LOG.logError( e.getMessage(), e );
416 throw new SQLException( "Error performing count (HITS) query: " + query );
417 } finally {
418 try {
419 if ( rs != null ) {
420 rs.close();
421 }
422 } finally {
423 if ( stmt != null ) {
424 stmt.close();
425 }
426 }
427 }
428 return result;
429 }
430
431 /**
432 * Builds the initial SELECT statement.
433 * <p>
434 * This statement determines all feature ids that are affected by the filter, but also SELECTs all properties that
435 * are stored in the root feature types' tables (to improve efficiency).
436 * </p>
437 * <p>
438 * The statement is structured like this:
439 * <ul>
440 * <li><code>SELECT</code></li>
441 * <li>comma-separated list of qualified columns/functions to fetch from root tables</li>
442 * <li><code>FROM</code></li>
443 * <li>comma-separated list of tables and their aliases (this is needed to constrain the paths to selected
444 * XPath-PropertyNames)</li>
445 * <li><code>WHERE</code></li>
446 * <li>SQL representation of the filter expression</li>
447 * <li><code>ORDER BY</code></li>
448 * <li>qualified sort criteria columns/functions</li>
449 * </ul>
450 * </p>
451 *
452 * @param selectManager
453 * associated <code>SelectManager</code>
454 * @return initial select statement
455 * @throws DatastoreException
456 */
457 protected StatementBuffer buildInitialSelect( SelectManager selectManager )
458 throws DatastoreException {
459
460 List<List<SimpleContent>>[] fetchContents = selectManager.getAllFetchContents();
461 StatementBuffer stmt = new StatementBuffer();
462
463 stmt.append( "SELECT " );
464
465 String tableAlias = this.whereBuilder.getRootTableAlias( 0 );
466 List<List<SimpleContent>> ftFetchContents = fetchContents[0];
467 appendQualifiedContentList( stmt, tableAlias, ftFetchContents );
468
469 boolean first = ftFetchContents.size() == 0;
470 for ( int i = 1; i < this.rootFts.length; i++ ) {
471 ftFetchContents = fetchContents[i];
472 if ( ftFetchContents.size() > 0 ) {
473 if ( !first ) {
474 stmt.append( ',' );
475 first = false;
476 }
477 tableAlias = this.whereBuilder.getRootTableAlias( i );
478 appendQualifiedContentList( stmt, tableAlias, ftFetchContents );
479 }
480 }
481
482 stmt.append( " FROM " );
483
484 whereBuilder.appendJoinTableList( stmt );
485 whereBuilder.appendWhereCondition( stmt );
486 whereBuilder.appendOrderByCondition( stmt );
487
488 return stmt;
489 }
490 }