001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_testing/src/org/deegree/io/datastore/sql/generic/GenericSQLWhereBuilder.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.generic;
037
038 import java.sql.Types;
039 import java.util.ArrayList;
040 import java.util.List;
041
042 import org.deegree.framework.log.ILogger;
043 import org.deegree.framework.log.LoggerFactory;
044 import org.deegree.i18n.Messages;
045 import org.deegree.io.JDBCConnection;
046 import org.deegree.io.datastore.DatastoreException;
047 import org.deegree.io.datastore.schema.MappedFeatureType;
048 import org.deegree.io.datastore.schema.content.MappingField;
049 import org.deegree.io.datastore.sql.StatementBuffer;
050 import org.deegree.io.datastore.sql.TableAliasGenerator;
051 import org.deegree.io.datastore.sql.VirtualContentProvider;
052 import org.deegree.io.datastore.sql.wherebuilder.SpecialCharString;
053 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
054 import org.deegree.io.quadtree.DBQuadtree;
055 import org.deegree.io.quadtree.DBQuadtreeManager;
056 import org.deegree.io.quadtree.IndexException;
057 import org.deegree.io.quadtree.Quadtree;
058 import org.deegree.model.filterencoding.DBFunction;
059 import org.deegree.model.filterencoding.Expression;
060 import org.deegree.model.filterencoding.Filter;
061 import org.deegree.model.filterencoding.FilterEvaluationException;
062 import org.deegree.model.filterencoding.Function;
063 import org.deegree.model.filterencoding.OperationDefines;
064 import org.deegree.model.filterencoding.PropertyIsLikeOperation;
065 import org.deegree.model.filterencoding.SpatialOperation;
066 import org.deegree.model.spatialschema.Envelope;
067 import org.deegree.ogcbase.SortProperty;
068
069 /**
070 * {@link WhereBuilder} implementation for the {@link GenericSQLDatastore}.
071 * <p>
072 * Uses the {@link Quadtree} to speed up BBOX queries.
073 *
074 * @see org.deegree.io.quadtree
075 *
076 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
077 * @author last edited by: $Author: mschneider $
078 *
079 * @version $Revision: 21843 $, $Date: 2010-01-11 13:52:33 +0100 (Mo, 11. Jan 2010) $
080 */
081 class GenericSQLWhereBuilder extends WhereBuilder {
082
083 private static final ILogger LOG = LoggerFactory.getLogger( GenericSQLWhereBuilder.class );
084
085 private final static String SQL_TRUE = "1=1";
086
087 private final static String SQL_FALSE = "1!=1";
088
089 private JDBCConnection jdbc;
090
091 /**
092 * Creates a new instance of <code>GenericSQLWhereBuilder</code> from the given parameters.
093 *
094 * @param rootFts
095 * selected feature types, more than one type means that the types are joined
096 * @param aliases
097 * aliases for the feature types, may be null (must have same length as rootFts otherwise)
098 * @param filter
099 * filter that restricts the matched features
100 * @param sortProperties
101 * sort criteria for the result, may be null or empty
102 * @param aliasGenerator
103 * used to generate unique table aliases
104 * @param vcProvider
105 * @param jdbc
106 * @throws DatastoreException
107 */
108 public GenericSQLWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
109 SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
110 VirtualContentProvider vcProvider, JDBCConnection jdbc ) throws DatastoreException {
111 super( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
112 this.jdbc = jdbc;
113 }
114
115 /**
116 * Appends an SQL fragment for the given object to the given sql statement.
117 *
118 * NOTE: Currently, the method uses a quirk and appends the generated argument inline, i.e. not using
119 * query.addArgument(). Works around an SQL error on HSQLDB that would occur otherwise.
120 *
121 * @param query
122 * @param operation
123 * @throws FilterEvaluationException
124 */
125 @Override
126 protected void appendPropertyIsLikeOperationAsSQL( StatementBuffer query, PropertyIsLikeOperation operation )
127 throws FilterEvaluationException {
128
129 String literal = operation.getLiteral().getValue();
130 String escape = "" + operation.getEscapeChar();
131 String wildCard = "" + operation.getWildCard();
132 String singleChar = "" + operation.getSingleChar();
133
134 SpecialCharString specialString = new SpecialCharString( literal, wildCard, singleChar, escape );
135 String sqlEncoded = specialString.toSQLStyle( !operation.isMatchCase() );
136
137 int targetSqlType = getPropertyNameSQLType( operation.getPropertyName() );
138
139 // if isMatchCase == false surround first argument with LOWER (...) and convert characters
140 // in second argument to lower case
141 if ( operation.isMatchCase() ) {
142 appendPropertyNameAsSQL( query, operation.getPropertyName() );
143 } else {
144 List<Expression> list = new ArrayList<Expression>();
145 list.add( operation.getPropertyName() );
146 Function func = new DBFunction( getFunctionName( "LOWER" ), list );
147 appendFunctionAsSQL( query, func, targetSqlType );
148 }
149
150 query.append( " LIKE '" );
151 query.append( sqlEncoded );
152 query.append( "'" );
153 }
154
155
156 /**
157 * Generates an SQL-fragment for the given object.
158 *
159 * TODO: Implement BBOX faster using explicit B0X-constructor
160 *
161 * @throws DatastoreException
162 */
163 @Override
164 protected void appendSpatialOperationAsSQL( StatementBuffer query, SpatialOperation operation )
165 throws DatastoreException {
166
167 switch ( operation.getOperatorId() ) {
168 case OperationDefines.BBOX: {
169 appendBBOXOperationAsSQL( query, operation );
170 break;
171 }
172 case OperationDefines.DISJOINT:
173 case OperationDefines.CROSSES:
174 case OperationDefines.EQUALS:
175 case OperationDefines.WITHIN:
176 case OperationDefines.OVERLAPS:
177 case OperationDefines.TOUCHES:
178 case OperationDefines.CONTAINS:
179 case OperationDefines.INTERSECTS:
180 case OperationDefines.DWITHIN:
181 case OperationDefines.BEYOND: {
182 query.append( SQL_TRUE );
183 break;
184 }
185 default: {
186 String msg = Messages.getMessage( "DATASTORE_UNKNOWN_SPATIAL_OPERATOR",
187 OperationDefines.getNameById( operation.getOperatorId() ) );
188 throw new DatastoreException( msg );
189 }
190 }
191 }
192
193 /**
194 * Appends a constraint (FEATURE_ID IN (...)) to the given {@link StatementBuffer} which is generated by using the
195 * associated {@link DBQuadtree} index.
196 *
197 * @param query
198 * @param operation
199 * @throws DatastoreException
200 */
201 private void appendBBOXOperationAsSQL( StatementBuffer query, SpatialOperation operation )
202 throws DatastoreException {
203
204 Envelope env = operation.getGeometry().getEnvelope();
205
206 DBQuadtreeManager<?> qtm = null;
207 try {
208 qtm = new DBQuadtreeManager<Object>( jdbc, this.rootFts[0].getTable(), "geometry", null, Integer.MIN_VALUE );
209 Object type = qtm.determineQuattreeType();
210 int dataType = Types.VARCHAR;
211 if ( type instanceof Integer ) {
212 LOG.logDebug( "The elements of the quadtree are of type Integer." );
213 qtm = new DBQuadtreeManager<Integer>( jdbc, this.rootFts[0].getTable(), "geometry", null, Types.INTEGER );
214 dataType = Types.INTEGER;
215 } else if ( type instanceof String ) {
216 LOG.logDebug( "The elements of the quadtree are of type String." );
217 qtm = new DBQuadtreeManager<String>( jdbc, this.rootFts[0].getTable(), "geometry", null, Types.INTEGER );
218 }
219
220 Envelope qtEnv = qtm.getQuadtree().getRootBoundingBox();
221 if ( qtEnv.intersects( env ) ) {
222 // check if features within this bbox are available
223 // if not -> return an empty list
224 List<?> ids = qtm.getQuadtree().query( env );
225 if ( ids.size() > 0 ) {
226
227 MappingField[] idFields = this.rootFts[0].getGMLId().getIdFields();
228 if ( idFields.length > 1 ) {
229 String msg = "GenericSQLDatastore cannot handle composite feature ids.";
230 throw new DatastoreException( msg );
231 }
232
233 query.append( getRootTableAlias( 0 ) + '.' + idFields[0].getField() + " IN (" );
234 for ( int i = 0; i < ids.size() - 1; i++ ) {
235 query.append( "?," );
236 if ( dataType == Types.VARCHAR ) {
237 query.addArgument( ( "" + ids.get( i ) ).trim(), Types.VARCHAR );
238 } else {
239 query.addArgument( ids.get( i ), Types.INTEGER );
240 }
241 }
242 if ( dataType == Types.VARCHAR ) {
243 query.addArgument( ( "" + ids.get( ids.size() - 1 ) ).trim(), Types.VARCHAR );
244 } else {
245 query.addArgument( ids.get( ids.size() - 1 ), Types.INTEGER );
246 }
247 query.append( "?)" );
248 } else {
249 query.append( SQL_FALSE );
250 }
251 } else {
252 query.append( SQL_FALSE );
253 }
254 } catch ( IndexException e ) {
255 LOG.logError( e.getMessage(), e );
256 throw new DatastoreException(
257 "Could not append bbox operation as sql into the Quadtree: " + e.getMessage(),
258 e );
259 } finally {
260 if ( qtm != null ) {
261 qtm.release();
262 }
263 }
264 }
265 }