001    //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_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    }