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