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