001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/branches/2.2_testing/src/org/deegree/io/datastore/sql/QueryHandler.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.Collection;
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.io.datastore.DatastoreException;
059 import org.deegree.io.datastore.FeatureId;
060 import org.deegree.io.datastore.PropertyPathResolvingException;
061 import org.deegree.io.datastore.schema.MappedFeatureType;
062 import org.deegree.io.datastore.schema.MappedPropertyType;
063 import org.deegree.io.datastore.schema.content.SimpleContent;
064 import org.deegree.io.datastore.sql.wherebuilder.QueryTableTree;
065 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
066 import org.deegree.model.crs.UnknownCRSException;
067 import org.deegree.model.feature.Feature;
068 import org.deegree.model.feature.FeatureCollection;
069 import org.deegree.model.feature.FeatureFactory;
070 import org.deegree.model.feature.FeatureProperty;
071 import org.deegree.model.feature.FeatureTupleCollection;
072 import org.deegree.ogcbase.PropertyPath;
073 import org.deegree.ogcwebservices.wfs.operation.Query;
074 import org.deegree.ogcwebservices.wfs.operation.GetFeature.RESULT_TYPE;
075
076 /**
077 * Handles {@link Query} requests to SQL backed datastores.
078 *
079 * @see FeatureFetcher
080 * @see AbstractSQLDatastore
081 * @see QueryTableTree
082 *
083 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
084 * @author last edited by: $Author: apoth $
085 *
086 * @version $Revision: 9342 $, $Date: 2007-12-27 13:32:57 +0100 (Do, 27 Dez 2007) $
087 */
088 public class QueryHandler extends FeatureFetcher {
089
090 private static final ILogger LOG = LoggerFactory.getLogger( QueryHandler.class );
091
092 private Query query;
093
094 // targeted feature types, more than one implies that a join of feature types is requested
095 private MappedFeatureType[] rootFts;
096
097 // used to build the initial SELECT (especially the WHERE-clause)
098 private WhereBuilder whereBuilder;
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 }
264 result.add( feature );
265 }
266 }
267 return result;
268 }
269
270 private FeatureTupleCollection performJoinResultsQuery( ResultSet rs, SelectManager selectManager )
271 throws DatastoreException, SQLException, UnknownCRSException {
272
273 List<Feature[]> resultTuples = new ArrayList<Feature[]>();
274
275 // used to handle that a feature may occur several times in result set
276 Set<String> rootFeatureIds = new HashSet<String>( 1000 );
277
278 Object[] resultValues = new Object[selectManager.getFetchContentCount()];
279 int maxFeatures = this.query.getMaxFeatures();
280
281 int[] resultFtIdx = selectManager.getIncludedFtIdx();
282 for ( int i = 0; i < resultFtIdx.length; i++ ) {
283 LOG.logDebug ("Included in result set: " + resultFtIdx [i]);
284 }
285
286 while ( rs.next() ) {
287
288 Feature[] resultTuple = new Feature[resultFtIdx.length];
289
290 // already maxFeature features extracted?
291 if ( maxFeatures != -1 && resultTuples.size() == maxFeatures ) {
292 break;
293 }
294
295 // collect result values
296 for ( int i = 0; i < resultValues.length; i++ ) {
297 resultValues[i] = rs.getObject( i + 1 );
298 }
299
300 FeatureId[] fids = new FeatureId[resultFtIdx.length];
301 StringBuffer combinedFid = new StringBuffer();
302
303 // build combined fid to identify unique "features" (actually these are feature tuples)
304 for ( int i = 0; i < resultFtIdx.length; i++ ) {
305 int idx = resultFtIdx[i];
306 MappedFeatureType rootFt = this.rootFts[idx];
307 Map<SimpleContent, Integer> resultPosMap = selectManager.getResultPosMaps()[idx];
308 fids[i] = extractFeatureId( rootFt, resultPosMap, resultValues );
309 combinedFid.append( fids[i].getAsString() );
310 }
311 LOG.logDebug ("CombinedFID: " + combinedFid);
312
313 // if tuple has not been added to result yet, extract and add it
314 if ( !rootFeatureIds.contains( combinedFid.toString () ) ) {
315 for ( int i = 0; i < resultFtIdx.length; i++ ) {
316 int ftIdx = resultFtIdx[i];
317 FeatureId fid = fids[i];
318 Map<MappedPropertyType, Collection<PropertyPath>> requestedPropertyMap = selectManager.getAllFetchProps()[ftIdx];
319 Map<SimpleContent, Integer> resultPosMap = selectManager.getResultPosMaps()[ftIdx];
320
321 // feature may have been fetched already
322 Feature feature = this.featureMap.get( fid );
323 if ( feature == null ) {
324 feature = extractFeature( fid, requestedPropertyMap, resultPosMap, resultValues );
325 }
326 resultTuple[i] = ( feature );
327 }
328 resultTuples.add( resultTuple );
329 rootFeatureIds.add( combinedFid.toString() );
330 }
331 }
332
333 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
334 for ( int i = 0; i < resultTuples.size(); i++ ) {
335 Feature[] resultTuple = resultTuples.get( i );
336 StringBuffer sb = new StringBuffer();
337 for ( int j = 0; j < resultFtIdx.length; j++ ) {
338 int idx = resultFtIdx[j];
339 sb.append( resultTuple[j].getId() );
340 if ( idx != this.rootFts.length - 1 ) {
341 sb.append( ',' );
342 }
343 }
344 LOG.logDebug( sb.toString() );
345 }
346 }
347
348 FeatureTupleCollection result = FeatureFactory.createFeatureCollection( "id", resultTuples, this.rootFts.length );
349 return result;
350 }
351
352 private void resolveFeatureReferences() {
353 for ( FeatureId fid : this.fidToPropertyMap.keySet() ) {
354 Feature feature = this.featureMap.get( fid );
355 assert feature != null;
356 for ( FeatureProperty property : this.fidToPropertyMap.get( fid ) ) {
357 property.setValue( feature );
358 }
359 }
360 }
361
362 /**
363 * Performs a query for the number feature instances that match the query constraints. This corresponds to a query
364 * with resultType=HITS.
365 *
366 * @return a feature collection containing number of features that match the query constraints
367 * @throws SQLException
368 * @throws DatastoreException
369 */
370 private FeatureCollection performHitsQuery()
371 throws SQLException, DatastoreException {
372
373 FeatureCollection result = FeatureFactory.createFeatureCollection( "ID", 2 );
374
375 String tableAlias = this.whereBuilder.getRootTableAlias( 0 );
376 String field = this.rootFts[0].getGMLId().getIdFields()[0].getField();
377 StatementBuffer query = new StatementBuffer();
378 query.append( "SELECT COUNT( DISTINCT " );
379 query.append( tableAlias + '.' + field );
380 query.append( ") FROM " );
381
382 whereBuilder.appendJoinTableList( query );
383 whereBuilder.appendWhereCondition( query );
384 LOG.logDebug( "Count query: '" + query + "'" );
385
386 ResultSet rs = null;
387 PreparedStatement stmt = this.datastore.prepareStatement( this.conn, query );
388 try {
389 rs = stmt.executeQuery();
390 if ( rs.next() ) {
391 result.setAttribute( "numberOfFeatures", rs.getObject( 1 ).toString() );
392 } else {
393 LOG.logError( "Internal error. Count result is empty (no rows)." );
394 throw new SQLException();
395 }
396 } catch ( SQLException e ) {
397 LOG.logError( e.getMessage(), e );
398 throw new SQLException( "Error performing count (HITS) query: " + query );
399 } finally {
400 try {
401 if ( rs != null ) {
402 rs.close();
403 }
404 } finally {
405 if ( stmt != null ) {
406 stmt.close();
407 }
408 }
409 }
410 return result;
411 }
412
413 /**
414 * Builds the initial SELECT statement.
415 * <p>
416 * This statement determines all feature ids that are affected by the filter, but also SELECTs all properties that
417 * are stored in the root feature types' tables (to improve efficiency).
418 * </p>
419 * <p>
420 * The statement is structured like this:
421 * <ul>
422 * <li><code>SELECT</code></li>
423 * <li>comma-separated list of qualified columns/functions to fetch from root tables</li>
424 * <li><code>FROM</code></li>
425 * <li>comma-separated list of tables and their aliases (this is needed to constrain the paths to selected
426 * XPath-PropertyNames)</li>
427 * <li><code>WHERE</code></li>
428 * <li>SQL representation of the filter expression</li>
429 * <li><code>ORDER BY</code></li>
430 * <li>qualified sort criteria columns/functions</li>
431 * </ul>
432 * </p>
433 *
434 * @param selectManager
435 * associated <code>SelectManager</code>
436 * @return initial select statement
437 * @throws DatastoreException
438 */
439 protected StatementBuffer buildInitialSelect( SelectManager selectManager )
440 throws DatastoreException {
441
442 List<List<SimpleContent>>[] fetchContents = selectManager.getAllFetchContents();
443 StatementBuffer stmt = new StatementBuffer();
444
445 stmt.append( "SELECT " );
446
447 String tableAlias = this.whereBuilder.getRootTableAlias( 0 );
448 List<List<SimpleContent>> ftFetchContents = fetchContents[0];
449 appendQualifiedContentList( stmt, tableAlias, ftFetchContents );
450
451 boolean first = ftFetchContents.size() == 0;
452 for ( int i = 1; i < this.rootFts.length; i++ ) {
453 ftFetchContents = fetchContents[i];
454 if ( ftFetchContents.size() > 0 ) {
455 if (!first) {
456 stmt.append( ',' );
457 first = false;
458 }
459 tableAlias = this.whereBuilder.getRootTableAlias( i );
460 appendQualifiedContentList( stmt, tableAlias, ftFetchContents );
461 }
462 }
463
464 stmt.append( " FROM " );
465
466 whereBuilder.appendJoinTableList( stmt );
467 whereBuilder.appendWhereCondition( stmt );
468 whereBuilder.appendOrderByCondition( stmt );
469
470 return stmt;
471 }
472 }