001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_testing/src/org/deegree/io/datastore/sql/wherebuilder/WhereBuilder.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.wherebuilder;
037
038 import static java.sql.Types.DOUBLE;
039 import static java.sql.Types.INTEGER;
040 import static java.sql.Types.SMALLINT;
041 import static org.deegree.model.filterencoding.ExpressionDefines.ADD;
042 import static org.deegree.model.filterencoding.ExpressionDefines.DIV;
043 import static org.deegree.model.filterencoding.ExpressionDefines.MUL;
044 import static org.deegree.model.filterencoding.ExpressionDefines.SUB;
045 import static org.deegree.model.filterencoding.OperationDefines.BBOX;
046
047 import java.sql.Types;
048 import java.util.ArrayList;
049 import java.util.Hashtable;
050 import java.util.Iterator;
051 import java.util.List;
052 import java.util.Stack;
053
054 import org.deegree.framework.log.ILogger;
055 import org.deegree.framework.log.LoggerFactory;
056 import org.deegree.i18n.Messages;
057 import org.deegree.io.datastore.DatastoreException;
058 import org.deegree.io.datastore.PropertyPathResolvingException;
059 import org.deegree.io.datastore.schema.MappedFeaturePropertyType;
060 import org.deegree.io.datastore.schema.MappedFeatureType;
061 import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
062 import org.deegree.io.datastore.schema.MappedPropertyType;
063 import org.deegree.io.datastore.schema.MappedSimplePropertyType;
064 import org.deegree.io.datastore.schema.TableRelation;
065 import org.deegree.io.datastore.schema.content.ConstantContent;
066 import org.deegree.io.datastore.schema.content.MappingField;
067 import org.deegree.io.datastore.schema.content.SQLFunctionCall;
068 import org.deegree.io.datastore.schema.content.SimpleContent;
069 import org.deegree.io.datastore.sql.StatementBuffer;
070 import org.deegree.io.datastore.sql.TableAliasGenerator;
071 import org.deegree.io.datastore.sql.VirtualContentProvider;
072 import org.deegree.model.feature.schema.FeatureType;
073 import org.deegree.model.filterencoding.ArithmeticExpression;
074 import org.deegree.model.filterencoding.ComparisonOperation;
075 import org.deegree.model.filterencoding.ComplexFilter;
076 import org.deegree.model.filterencoding.DBFunction;
077 import org.deegree.model.filterencoding.Expression;
078 import org.deegree.model.filterencoding.ExpressionDefines;
079 import org.deegree.model.filterencoding.FeatureFilter;
080 import org.deegree.model.filterencoding.Filter;
081 import org.deegree.model.filterencoding.FilterEvaluationException;
082 import org.deegree.model.filterencoding.Function;
083 import org.deegree.model.filterencoding.Literal;
084 import org.deegree.model.filterencoding.LogicalOperation;
085 import org.deegree.model.filterencoding.Operation;
086 import org.deegree.model.filterencoding.OperationDefines;
087 import org.deegree.model.filterencoding.PropertyIsBetweenOperation;
088 import org.deegree.model.filterencoding.PropertyIsCOMPOperation;
089 import org.deegree.model.filterencoding.PropertyIsLikeOperation;
090 import org.deegree.model.filterencoding.PropertyIsNullOperation;
091 import org.deegree.model.filterencoding.PropertyName;
092 import org.deegree.model.filterencoding.SpatialOperation;
093 import org.deegree.ogcbase.PropertyPath;
094 import org.deegree.ogcbase.SortProperty;
095
096 /**
097 * Creates SQL-WHERE clauses from OGC filter expressions (to restrict SQL statements to all stored features that match a
098 * given filter).
099 * <p>
100 * Also handles the creation of ORDER-BY clauses.
101 *
102 * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a>
103 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
104 * @author last edited by: $Author: aschmitz $
105 *
106 * @version $Revision: 23585 $, $Date: 2010-04-13 14:07:18 +0200 (Di, 13 Apr 2010) $
107 */
108 public class WhereBuilder {
109
110 private static final ILogger LOG = LoggerFactory.getLogger( WhereBuilder.class );
111
112 // database specific SRS code for unspecified SRS
113 protected static final int SRS_UNDEFINED = -1;
114
115 /** Targeted feature types. */
116 protected MappedFeatureType[] rootFts;
117
118 /** {@link Filter} for which the corresponding WHERE-clause will be generated. */
119 protected Filter filter;
120
121 protected SortProperty[] sortProperties;
122
123 protected VirtualContentProvider vcProvider;
124
125 protected QueryTableTree queryTableTree;
126
127 protected List<PropertyPath> filterPropertyPaths = new ArrayList<PropertyPath>();
128
129 protected List<PropertyPath> sortPropertyPaths = new ArrayList<PropertyPath>();
130
131 private Hashtable<String, String> functionMap = new Hashtable<String, String>();
132
133 /**
134 * Creates a new <code>WhereBuilder</code> instance.
135 *
136 * @param rootFts
137 * selected feature types, more than one type means that the types are joined
138 * @param aliases
139 * aliases for the feature types, may be null (must have same length as rootFts otherwise)
140 * @param filter
141 * @param sortProperties
142 * @param aliasGenerator
143 * aliasGenerator to be used to generate table aliases, may be null
144 * @param vcProvider
145 * @throws DatastoreException
146 */
147 public WhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter, SortProperty[] sortProperties,
148 TableAliasGenerator aliasGenerator, VirtualContentProvider vcProvider )
149 throws DatastoreException {
150
151 this.rootFts = rootFts;
152 this.queryTableTree = new QueryTableTree( rootFts, aliases, aliasGenerator );
153
154 // add filter properties to the QueryTableTree
155 this.filter = filter;
156 if ( filter != null ) {
157 assert filter instanceof ComplexFilter || filter instanceof FeatureFilter;
158 buildFilterPropertyNameMap();
159 for ( PropertyPath property : this.filterPropertyPaths ) {
160 this.queryTableTree.addFilterProperty( property );
161 }
162 fillFunctionNameMap();
163 }
164
165 // add sort properties to the QueryTableTree
166 this.sortProperties = sortProperties;
167 if ( sortProperties != null ) {
168 for ( SortProperty property : sortProperties ) {
169 this.sortPropertyPaths.add( property.getSortProperty() );
170 this.queryTableTree.addSortProperty( property.getSortProperty() );
171 }
172 }
173
174 this.vcProvider = vcProvider;
175
176 if ( LOG.getLevel() == ILogger.LOG_DEBUG ) {
177 LOG.logDebug( "QueryTableTree:\n" + this.queryTableTree );
178 }
179 }
180
181 /**
182 * Returns the table alias used for the specified root feature type.
183 *
184 * @param i
185 * index of the requested root feature type
186 * @return the alias used for the root table
187 */
188 public String getRootTableAlias( int i ) {
189 return this.queryTableTree.getRootNodes()[i].getTableAlias();
190 }
191
192 /**
193 * Returns the associated <code>Filter</code> instance.
194 *
195 * @return the associated <code>Filter</code> instance
196 */
197 public Filter getFilter() {
198 return this.filter;
199 }
200
201 protected MappedGeometryPropertyType getGeometryProperty( PropertyName propName ) {
202 PropertyPath propertyPath = propName.getValue();
203 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
204 assert propertyNode != null;
205 assert propertyNode instanceof GeometryPropertyNode;
206 LOG.logDebug( "Found geometry property with path: " + propertyPath + " and propName: "
207 + propertyNode.getProperty().getName() );
208 return (MappedGeometryPropertyType) propertyNode.getProperty();
209 }
210
211 // /**
212 // * Returns the SRS of the {@link MappedGeometryPropertyType} that is identified by the given
213 // * {@link PropertyPath}.
214 // *
215 // * @param propertyPath
216 // * @return the default SRS of the geometry property type
217 // */
218 // protected String getSrs( PropertyPath propertyPath ) {
219 // PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
220 // assert propertyNode != null;
221 // assert propertyNode instanceof GeometryPropertyNode;
222 // MappedGeometryPropertyType geoProp = (MappedGeometryPropertyType) propertyNode.getProperty();
223 // return geoProp.getSRS().toString();
224 // }
225 //
226 // /**
227 // * Returns the internal Srs of the {@link MappedGeometryPropertyType} that is identified by
228 // the
229 // * given {@link PropertyPath}.
230 // *
231 // * @param propertyPath
232 // * @return the default SRS of the geometry property type
233 // */
234 // protected int getInternalSrsCode( PropertyPath propertyPath ) {
235 // PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
236 // assert propertyNode != null;
237 // assert propertyNode instanceof GeometryPropertyNode;
238 // MappedGeometryPropertyType geoProp = (MappedGeometryPropertyType) propertyNode.getProperty();
239 // return geoProp.getMappingField().getSRS();
240 // }
241
242 protected int getPropertyNameSQLType( PropertyName propertyName ) {
243
244 PropertyPath propertyPath = propertyName.getValue();
245 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
246 assert propertyNode != null;
247
248 // can be useful if the assertions are not enabled
249 if ( propertyNode == null ) {
250 LOG.logDebug( "Null propertyNode for propertyName: " + propertyName + " with queryTable: "
251 + this.queryTableTree );
252 }
253
254 MappedPropertyType propertyType = propertyNode.getProperty();
255 if ( !( propertyType instanceof MappedSimplePropertyType ) ) {
256 String msg = "Error in WhereBuilder: cannot compare against properties of type '" + propertyType.getClass()
257 + "'.";
258 LOG.logError( msg );
259 throw new RuntimeException( msg );
260 }
261
262 SimpleContent content = ( (MappedSimplePropertyType) propertyType ).getContent();
263 if ( !( content instanceof MappingField ) ) {
264 String msg = "Virtual properties are currently ignored in WhereBuilder#getPropertyNameSQLType(PropertyName).";
265 LOG.logWarning( msg );
266 return Types.VARCHAR;
267 }
268
269 int targetSqlType = ( (MappingField) content ).getType();
270 return targetSqlType;
271 }
272
273 protected void buildFilterPropertyNameMap()
274 throws PropertyPathResolvingException {
275 if ( this.filter instanceof ComplexFilter ) {
276 buildPropertyNameMapFromOperation( ( (ComplexFilter) this.filter ).getOperation() );
277 } else if ( this.filter instanceof FeatureFilter ) {
278 // TODO
279 // throw new PropertyPathResolvingException( "FeatureFilter not implemented yet." );
280 }
281 }
282
283 private void buildPropertyNameMapFromOperation( Operation operation )
284 throws PropertyPathResolvingException {
285 switch ( OperationDefines.getTypeById( operation.getOperatorId() ) ) {
286 case OperationDefines.TYPE_SPATIAL: {
287 PropertyName name = ( (SpatialOperation) operation ).getPropertyName();
288
289 // possible in case of BBOX
290 if ( name == null && operation.getOperatorId() == BBOX ) {
291 // use first ft and first geom property
292 FeatureType ft = queryTableTree.getRootNodes()[0].getFeatureType();
293 name = new PropertyName( ft.getGeometryProperties()[0].getName() );
294 // modify the operation, since the property name is used later on from the datastores' where builders
295 ( (SpatialOperation) operation ).setPropertyName( name );
296 }
297 registerPropertyName( name );
298 break;
299 }
300 case OperationDefines.TYPE_COMPARISON: {
301 buildPropertyNameMap( (ComparisonOperation) operation );
302 break;
303 }
304 case OperationDefines.TYPE_LOGICAL: {
305 buildPropertyNameMap( (LogicalOperation) operation );
306 break;
307 }
308 default: {
309 break;
310 }
311 }
312 }
313
314 private void buildPropertyNameMap( ComparisonOperation operation )
315 throws PropertyPathResolvingException {
316 switch ( operation.getOperatorId() ) {
317 case OperationDefines.PROPERTYISEQUALTO:
318 case OperationDefines.PROPERTYISNOTEQUALTO:
319 case OperationDefines.PROPERTYISLESSTHAN:
320 case OperationDefines.PROPERTYISGREATERTHAN:
321 case OperationDefines.PROPERTYISLESSTHANOREQUALTO:
322 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
323 buildPropertyNameMap( ( (PropertyIsCOMPOperation) operation ).getFirstExpression() );
324 buildPropertyNameMap( ( (PropertyIsCOMPOperation) operation ).getSecondExpression() );
325 break;
326 }
327 case OperationDefines.PROPERTYISLIKE: {
328 registerPropertyName( ( (PropertyIsLikeOperation) operation ).getPropertyName() );
329 break;
330 }
331 case OperationDefines.PROPERTYISNULL: {
332 buildPropertyNameMap( ( (PropertyIsNullOperation) operation ).getPropertyName() );
333 break;
334 }
335 case OperationDefines.PROPERTYISBETWEEN: {
336 buildPropertyNameMap( ( (PropertyIsBetweenOperation) operation ).getLowerBoundary() );
337 buildPropertyNameMap( ( (PropertyIsBetweenOperation) operation ).getUpperBoundary() );
338 registerPropertyName( ( (PropertyIsBetweenOperation) operation ).getPropertyName() );
339 break;
340 }
341 default: {
342 break;
343 }
344 }
345 }
346
347 private void buildPropertyNameMap( LogicalOperation operation )
348 throws PropertyPathResolvingException {
349 List<?> operationList = operation.getArguments();
350 Iterator<?> it = operationList.iterator();
351 while ( it.hasNext() ) {
352 buildPropertyNameMapFromOperation( (Operation) it.next() );
353 }
354 }
355
356 private void buildPropertyNameMap( Expression expression )
357 throws PropertyPathResolvingException {
358 switch ( expression.getExpressionId() ) {
359 case ExpressionDefines.PROPERTYNAME: {
360 registerPropertyName( (PropertyName) expression );
361 break;
362 }
363 case ExpressionDefines.ADD:
364 case ExpressionDefines.SUB:
365 case ExpressionDefines.MUL:
366 case ExpressionDefines.DIV: {
367 buildPropertyNameMap( ( (ArithmeticExpression) expression ).getFirstExpression() );
368 buildPropertyNameMap( ( (ArithmeticExpression) expression ).getSecondExpression() );
369 break;
370 }
371 case ExpressionDefines.FUNCTION: {
372 // TODO: What about PropertyNames used here?
373 break;
374 }
375 case ExpressionDefines.EXPRESSION:
376 case ExpressionDefines.LITERAL: {
377 break;
378 }
379 }
380 }
381
382 private void registerPropertyName( PropertyName propertyName ) {
383 this.filterPropertyPaths.add( propertyName.getValue() );
384 }
385
386 /**
387 * Appends the alias-qualified, comma separated list of all tables to be joined in order to represent the associated
388 * filter expression (and possibly feature type joins).
389 * <p>
390 * The list consist of left outer joins ("x LEFT OUTER JOIN y") and cross-product joins ("x,y"):
391 * <ul>
392 * <li>left outer joins are generated for each join that is necessary, because of filter expressions that target
393 * properties stored in related tables (condition joins)
394 * <li>cross-product joins are generated for all feature type root tables (feature type joins) that have not joined
395 * by filter expression joins before</li>
396 * </ul>
397 *
398 * @param query
399 * the list is appended to this <code>SQLStatement</code>
400 */
401 public void appendJoinTableList( StatementBuffer query ) {
402
403 FeatureTypeNode[] rootNodes = this.queryTableTree.getRootNodes();
404 appendOuterJoinTableList( query, rootNodes[0] );
405 for ( int i = 1; i < rootNodes.length; i++ ) {
406 query.append( ',' );
407 appendOuterJoinTableList( query, rootNodes[i] );
408 }
409 }
410
411 /**
412 * Appends the alias-qualified, comma separated list of tables to be joined (for one root feature type node). This
413 * includes the join conditions (necessary for the filter conditions), which are generated in ANSI-SQL left outer
414 * join style.
415 *
416 * @param query
417 * the list is appended to this <code>SQLStatement</code>
418 * @param rootNode
419 * one root feature type node in the <code>QueryTableTree</code>
420 */
421 private void appendOuterJoinTableList( StatementBuffer query, FeatureTypeNode rootNode ) {
422
423 query.append( rootNode.getTable() );
424 query.append( ' ' );
425 query.append( rootNode.getTableAlias() );
426 Stack<PropertyNode> propertyNodeStack = new Stack<PropertyNode>();
427 PropertyNode[] propertyNodes = rootNode.getPropertyNodes();
428 for ( int i = 0; i < propertyNodes.length; i++ ) {
429 propertyNodeStack.push( propertyNodes[i] );
430 }
431
432 while ( !propertyNodeStack.isEmpty() ) {
433 PropertyNode currentNode = propertyNodeStack.pop();
434 String fromAlias = currentNode.getParent().getTableAlias();
435 TableRelation[] tableRelations = currentNode.getPathFromParent();
436 if ( tableRelations != null && tableRelations.length != 0 ) {
437 String[] toAliases = currentNode.getTableAliases();
438 appendOuterJoins( tableRelations, fromAlias, toAliases, query );
439
440 }
441 if ( currentNode instanceof FeaturePropertyNode ) {
442 FeaturePropertyNode featurePropertyNode = (FeaturePropertyNode) currentNode;
443 FeatureTypeNode[] childNodes = ( (FeaturePropertyNode) currentNode ).getFeatureTypeNodes();
444 for ( int i = 0; i < childNodes.length; i++ ) {
445 // TODO is this way of skipping root tables o.k.?
446 if ( childNodes[i].getFtAlias() != null ) {
447 continue;
448 }
449 String toTable = childNodes[i].getTable();
450 String toAlias = childNodes[i].getTableAlias();
451 String[] pathAliases = featurePropertyNode.getTableAliases();
452 if ( pathAliases.length == 0 ) {
453 fromAlias = featurePropertyNode.getParent().getTableAlias();
454 } else {
455 fromAlias = pathAliases[pathAliases.length - 1];
456 }
457 MappedFeaturePropertyType content = (MappedFeaturePropertyType) featurePropertyNode.getProperty();
458 TableRelation[] relations = content.getTableRelations();
459 TableRelation relation = relations[relations.length - 1];
460 appendOuterJoin( relation, fromAlias, toAlias, toTable, query );
461 propertyNodes = childNodes[i].getPropertyNodes();
462 for ( int j = 0; j < propertyNodes.length; j++ ) {
463 propertyNodeStack.push( propertyNodes[j] );
464 }
465 }
466 }
467 }
468 }
469
470 private void appendOuterJoins( TableRelation[] tableRelation, String fromAlias, String[] toAliases,
471 StatementBuffer query ) {
472 for ( int i = 0; i < toAliases.length; i++ ) {
473 String toAlias = toAliases[i];
474 appendOuterJoin( tableRelation[i], fromAlias, toAlias, query );
475 fromAlias = toAlias;
476 }
477 }
478
479 private void appendOuterJoin( TableRelation tableRelation, String fromAlias, String toAlias, StatementBuffer query ) {
480
481 query.append( " LEFT OUTER JOIN " );
482 query.append( tableRelation.getToTable() );
483 query.append( " " );
484 query.append( toAlias );
485 query.append( " ON " );
486
487 MappingField[] fromFields = tableRelation.getFromFields();
488 MappingField[] toFields = tableRelation.getToFields();
489 for ( int i = 0; i < fromFields.length; i++ ) {
490 if ( toAlias.equals( "" ) ) {
491 toAlias = tableRelation.getToTable();
492 }
493 query.append( toAlias );
494 query.append( "." );
495 query.append( toFields[i].getField() );
496 query.append( "=" );
497 if ( fromAlias.equals( "" ) ) {
498 fromAlias = tableRelation.getFromTable();
499 }
500 query.append( fromAlias );
501 query.append( "." );
502 query.append( fromFields[i].getField() );
503 if ( i != fromFields.length - 1 ) {
504 query.append( " AND " );
505 }
506 }
507 }
508
509 private void appendOuterJoin( TableRelation tableRelation, String fromAlias, String toAlias, String toTable,
510 StatementBuffer query ) {
511
512 query.append( " LEFT OUTER JOIN " );
513 query.append( toTable );
514 query.append( " " );
515 query.append( toAlias );
516 query.append( " ON " );
517
518 MappingField[] fromFields = tableRelation.getFromFields();
519 MappingField[] toFields = tableRelation.getToFields();
520 for ( int i = 0; i < fromFields.length; i++ ) {
521 if ( toAlias.equals( "" ) ) {
522 toAlias = toTable;
523 }
524 query.append( toAlias );
525 query.append( "." );
526 query.append( toFields[i].getField() );
527 query.append( "=" );
528 if ( fromAlias.equals( "" ) ) {
529 fromAlias = tableRelation.getFromTable();
530 }
531 query.append( fromAlias );
532 query.append( "." );
533 query.append( fromFields[i].getField() );
534 if ( i != fromFields.length - 1 ) {
535 query.append( " AND " );
536 }
537 }
538 }
539
540 /**
541 * Appends an SQL WHERE-condition corresponding to the <code>Filter</code> to the given SQL statement.
542 *
543 * @param query
544 * @throws DatastoreException
545 */
546 public final void appendWhereCondition( StatementBuffer query )
547 throws DatastoreException {
548 if ( filter instanceof ComplexFilter ) {
549 query.append( " WHERE " );
550 appendComplexFilterAsSQL( query, (ComplexFilter) filter );
551 } else if ( filter instanceof FeatureFilter ) {
552 FeatureFilter featureFilter = (FeatureFilter) filter;
553 if ( featureFilter.getFeatureIds().size() > 0 ) {
554 query.append( " WHERE " );
555 appendFeatureFilterAsSQL( query, featureFilter );
556 }
557 } else if ( filter != null ) {
558 assert false : "Unexpected filter type: " + filter.getClass();
559 }
560 }
561
562 /**
563 * Appends an SQL "ORDER BY"-condition that corresponds to the sort properties of the query to the given SQL
564 * statement.
565 *
566 * @param query
567 * @throws DatastoreException
568 */
569 public void appendOrderByCondition( StatementBuffer query )
570 throws DatastoreException {
571
572 // ignore properties that are unsuitable as sort criteria (like constant properties)
573 List<SortProperty> sortProps = new ArrayList<SortProperty>();
574
575 if ( this.sortProperties != null && this.sortProperties.length != 0 ) {
576 for ( int i = 0; i < this.sortProperties.length; i++ ) {
577 SortProperty sortProperty = this.sortProperties[i];
578 PropertyPath path = sortProperty.getSortProperty();
579 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( path );
580 MappedPropertyType pt = propertyNode.getProperty();
581 if ( !( pt instanceof MappedSimplePropertyType ) ) {
582 String msg = Messages.getMessage( "DATASTORE_INVALID_SORT_PROPERTY", pt.getName() );
583 throw new DatastoreException( msg );
584 }
585 SimpleContent content = ( (MappedSimplePropertyType) pt ).getContent();
586 if ( content.isSortable() ) {
587 sortProps.add( sortProperty );
588 } else {
589 String msg = "Ignoring sort criterion - property '" + path.getAsString()
590 + "' is not suitable for sorting.";
591 LOG.logDebug( msg );
592 }
593 }
594 }
595
596 if ( sortProps.size() > 0 ) {
597 query.append( " ORDER BY " );
598 }
599
600 for ( int i = 0; i < sortProps.size(); i++ ) {
601 SortProperty sortProperty = sortProps.get( i );
602 PropertyPath path = sortProperty.getSortProperty();
603 appendPropertyPathAsSQL( query, path );
604 if ( !sortProperty.getSortOrder() ) {
605 query.append( " DESC" );
606 }
607 if ( i != sortProps.size() - 1 ) {
608 query.append( ',' );
609 }
610 }
611 }
612
613 /**
614 * Appends an SQL fragment for the given object.
615 *
616 * @param query
617 * @param filter
618 * @throws DatastoreException
619 */
620 protected void appendComplexFilterAsSQL( StatementBuffer query, ComplexFilter filter )
621 throws DatastoreException {
622 appendOperationAsSQL( query, filter.getOperation() );
623 }
624
625 /**
626 * Appends an SQL fragment for the given object to the given sql statement.
627 *
628 * @param query
629 * @param operation
630 * @throws DatastoreException
631 */
632 protected void appendOperationAsSQL( StatementBuffer query, Operation operation )
633 throws DatastoreException {
634
635 switch ( OperationDefines.getTypeById( operation.getOperatorId() ) ) {
636 case OperationDefines.TYPE_SPATIAL: {
637 appendSpatialOperationAsSQL( query, (SpatialOperation) operation );
638 break;
639 }
640 case OperationDefines.TYPE_COMPARISON: {
641 try {
642 appendComparisonOperationAsSQL( query, (ComparisonOperation) operation );
643 } catch ( FilterEvaluationException e ) {
644 LOG.logDebug( "Stack trace of eaten exception: ", e );
645 // TODO unknown what will break if this exception is actually thrown
646 // new DatastoreException( e.getMessage(), e );
647 }
648 break;
649 }
650 case OperationDefines.TYPE_LOGICAL: {
651 appendLogicalOperationAsSQL( query, (LogicalOperation) operation );
652 break;
653 }
654 default: {
655 break;
656 }
657 }
658 }
659
660 /**
661 * Appends an SQL fragment for the given object to the given sql statement.
662 *
663 * @param query
664 * @param operation
665 * @throws FilterEvaluationException
666 */
667 protected void appendComparisonOperationAsSQL( StatementBuffer query, ComparisonOperation operation )
668 throws FilterEvaluationException {
669 switch ( operation.getOperatorId() ) {
670 case OperationDefines.PROPERTYISEQUALTO:
671 case OperationDefines.PROPERTYISNOTEQUALTO:
672 case OperationDefines.PROPERTYISLESSTHAN:
673 case OperationDefines.PROPERTYISGREATERTHAN:
674 case OperationDefines.PROPERTYISLESSTHANOREQUALTO:
675 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
676 appendPropertyIsCOMPOperationAsSQL( query, (PropertyIsCOMPOperation) operation );
677 break;
678 }
679 case OperationDefines.PROPERTYISLIKE: {
680 appendPropertyIsLikeOperationAsSQL( query, (PropertyIsLikeOperation) operation );
681 break;
682 }
683 case OperationDefines.PROPERTYISNULL: {
684 appendPropertyIsNullOperationAsSQL( query, (PropertyIsNullOperation) operation );
685 break;
686 }
687 case OperationDefines.PROPERTYISBETWEEN: {
688 appendPropertyIsBetweenOperationAsSQL( query, (PropertyIsBetweenOperation) operation );
689 break;
690 }
691 }
692 }
693
694 /**
695 * Appends an SQL fragment for the given object to the given sql statement.
696 *
697 * @param query
698 * @param operation
699 * @throws FilterEvaluationException
700 */
701 protected void appendPropertyIsCOMPOperationAsSQL( StatementBuffer query, PropertyIsCOMPOperation operation )
702 throws FilterEvaluationException {
703 Expression firstExpr = operation.getFirstExpression();
704 if ( !( firstExpr instanceof PropertyName ) ) {
705 throw new IllegalArgumentException( "First expression in a comparison must "
706 + "always be a 'PropertyName' element." );
707 }
708 int targetSqlType = getPropertyNameSQLType( (PropertyName) firstExpr );
709 if ( operation.isMatchCase() ) {
710 appendExpressionAsSQL( query, firstExpr, targetSqlType );
711 } else {
712 List<Expression> list = new ArrayList<Expression>();
713 list.add( firstExpr );
714 Function func = new DBFunction( getFunctionName( "LOWER" ), list );
715 appendFunctionAsSQL( query, func, targetSqlType );
716 }
717 switch ( operation.getOperatorId() ) {
718 case OperationDefines.PROPERTYISEQUALTO: {
719 query.append( " = " );
720 break;
721 }
722 case OperationDefines.PROPERTYISNOTEQUALTO: {
723 query.append( " <> " );
724 break;
725 }
726 case OperationDefines.PROPERTYISLESSTHAN: {
727 query.append( " < " );
728 break;
729 }
730 case OperationDefines.PROPERTYISGREATERTHAN: {
731 query.append( " > " );
732 break;
733 }
734 case OperationDefines.PROPERTYISLESSTHANOREQUALTO: {
735 query.append( " <= " );
736 break;
737 }
738 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
739 query.append( " >= " );
740 break;
741 }
742 }
743 if ( operation.isMatchCase() ) {
744 appendExpressionAsSQL( query, operation.getSecondExpression(), targetSqlType );
745 } else {
746 List<Expression> list = new ArrayList<Expression>();
747 list.add( operation.getSecondExpression() );
748 Function func = new DBFunction( getFunctionName( "LOWER" ), list );
749 appendFunctionAsSQL( query, func, targetSqlType );
750 }
751 }
752
753 /**
754 * Appends an SQL fragment for the given object to the given sql statement.
755 *
756 * @param query
757 * @param operation
758 * @throws FilterEvaluationException
759 */
760 protected void appendPropertyIsLikeOperationAsSQL( StatementBuffer query, PropertyIsLikeOperation operation )
761 throws FilterEvaluationException {
762
763 String literal = operation.getLiteral().getValue();
764 String escape = "" + operation.getEscapeChar();
765 String wildCard = "" + operation.getWildCard();
766 String singleChar = "" + operation.getSingleChar();
767
768 SpecialCharString specialString = new SpecialCharString( literal, wildCard, singleChar, escape );
769 String sqlEncoded = specialString.toSQLStyle( !operation.isMatchCase() );
770
771 int targetSqlType = getPropertyNameSQLType( operation.getPropertyName() );
772
773 // if isMatchCase == false surround first argument with LOWER (...) and convert characters
774 // in second argument to lower case
775 if ( operation.isMatchCase() ) {
776 appendPropertyNameAsSQL( query, operation.getPropertyName() );
777 } else {
778 List<Expression> list = new ArrayList<Expression>();
779 list.add( operation.getPropertyName() );
780 Function func = new DBFunction( getFunctionName( "LOWER" ), list );
781 appendFunctionAsSQL( query, func, targetSqlType );
782 }
783
784 query.append( " LIKE ? ESCAPE ?" );
785 query.addArgument( sqlEncoded, Types.VARCHAR );
786 query.addArgument( "\\", Types.VARCHAR );
787 }
788
789 /**
790 * Appends an SQL fragment for the given object to the given sql statement.
791 *
792 * @param query
793 * @param operation
794 */
795 protected void appendPropertyIsNullOperationAsSQL( StatementBuffer query, PropertyIsNullOperation operation ) {
796 appendPropertyNameAsSQL( query, operation.getPropertyName() );
797 query.append( " IS NULL" );
798 }
799
800 /**
801 * Appends an SQL fragment for the given object to the given sql statement.
802 *
803 * @param query
804 * @param operation
805 * @throws FilterEvaluationException
806 */
807 protected void appendPropertyIsBetweenOperationAsSQL( StatementBuffer query, PropertyIsBetweenOperation operation )
808 throws FilterEvaluationException {
809
810 PropertyName propertyName = operation.getPropertyName();
811 int targetSqlType = getPropertyNameSQLType( propertyName );
812 appendExpressionAsSQL( query, operation.getLowerBoundary(), targetSqlType );
813 query.append( " <= " );
814 appendPropertyNameAsSQL( query, propertyName );
815 query.append( " AND " );
816 appendPropertyNameAsSQL( query, propertyName );
817 query.append( " <= " );
818 appendExpressionAsSQL( query, operation.getUpperBoundary(), targetSqlType );
819 }
820
821 /**
822 * Appends an SQL fragment for the given object to the given sql statement.
823 *
824 * @param query
825 * @param expression
826 * @param targetSqlType
827 * sql type code to be used for literals at the bottom of the expression tree
828 * @throws FilterEvaluationException
829 */
830 protected void appendExpressionAsSQL( StatementBuffer query, Expression expression, int targetSqlType )
831 throws FilterEvaluationException {
832 switch ( expression.getExpressionId() ) {
833 case ExpressionDefines.PROPERTYNAME: {
834 appendPropertyNameAsSQL( query, (PropertyName) expression );
835 break;
836 }
837 case ExpressionDefines.LITERAL: {
838 appendLiteralAsSQL( query, (Literal) expression, targetSqlType );
839 break;
840 }
841 case ExpressionDefines.FUNCTION: {
842 Function function = (Function) expression;
843 appendFunctionAsSQL( query, function, targetSqlType );
844 break;
845 }
846 case ExpressionDefines.ADD:
847 case ExpressionDefines.SUB:
848 case ExpressionDefines.MUL:
849 case ExpressionDefines.DIV: {
850 appendArithmeticExpressionAsSQL( query, (ArithmeticExpression) expression, targetSqlType );
851 break;
852 }
853 case ExpressionDefines.EXPRESSION:
854 default: {
855 throw new IllegalArgumentException( "Unexpected expression type: " + expression.getExpressionName() );
856 }
857 }
858 }
859
860 /**
861 * Appends an SQL fragment for the given object to the given sql statement.
862 *
863 * @param query
864 * @param literal
865 * @param targetSqlType
866 */
867 protected void appendLiteralAsSQL( StatementBuffer query, Literal literal, int targetSqlType ) {
868 query.append( '?' );
869 query.addArgument( literal.getValue(), targetSqlType );
870 }
871
872 /**
873 * Appends an SQL fragment for the given object to the given sql statement.
874 *
875 * @param query
876 * @param propertyName
877 */
878 protected void appendPropertyNameAsSQL( StatementBuffer query, PropertyName propertyName ) {
879
880 PropertyPath propertyPath = propertyName.getValue();
881 appendPropertyPathAsSQL( query, propertyPath );
882 }
883
884 /**
885 * Appends an SQL fragment for the given object to the given sql statement.
886 *
887 * @param query
888 * @param propertyPath
889 */
890 protected void appendPropertyPathAsSQL( StatementBuffer query, PropertyPath propertyPath ) {
891
892 LOG.logDebug( "Looking up '" + propertyPath + "' in the query table tree." );
893 MappingField mappingField = null;
894 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
895 assert ( propertyNode != null );
896 if ( propertyNode instanceof SimplePropertyNode ) {
897 SimpleContent content = ( (MappedSimplePropertyType) ( propertyNode.getProperty() ) ).getContent();
898 if ( !( content instanceof MappingField ) ) {
899 if ( content instanceof ConstantContent ) {
900 query.append( "'" + ( (ConstantContent) content ).getValue() + "'" );
901 return;
902 } else if ( content instanceof SQLFunctionCall ) {
903 SQLFunctionCall call = (SQLFunctionCall) content;
904 String tableAlias = null;
905 String[] tableAliases = propertyNode.getTableAliases();
906 if ( tableAliases == null || tableAliases.length == 0 ) {
907 tableAlias = propertyNode.getParent().getTableAlias();
908 } else {
909 tableAlias = tableAliases[tableAliases.length - 1];
910 }
911 this.vcProvider.appendSQLFunctionCall( query, tableAlias, call );
912 return;
913 }
914 String msg = "Virtual properties are currently ignored in WhereBuilder#appendPropertyPathAsSQL(StatementBuffer,PropertyPath).";
915 LOG.logWarning( msg );
916 assert false;
917 }
918 mappingField = (MappingField) content;
919 } else if ( propertyNode instanceof GeometryPropertyNode ) {
920 mappingField = ( (MappedGeometryPropertyType) propertyNode.getProperty() ).getMappingField();
921 } else {
922 String msg = "Internal error in WhereBuilder: unhandled PropertyNode type: '"
923 + propertyNode.getClass().getName() + "'.";
924 LOG.logError( msg );
925 throw new RuntimeException( msg );
926 }
927 String tableAlias = null;
928 String[] tableAliases = propertyNode.getTableAliases();
929 if ( tableAliases == null || tableAliases.length == 0 ) {
930 tableAlias = propertyNode.getParent().getTableAlias();
931 } else {
932 tableAlias = tableAliases[tableAliases.length - 1];
933 }
934 if ( tableAlias != "" ) {
935 query.append( tableAlias );
936 query.append( '.' );
937 } else {
938 query.append( mappingField.getTable() );
939 query.append( '.' );
940 }
941 query.append( mappingField.getField() );
942 }
943
944 /**
945 * Appends an SQL fragment for the given object to the given sql statement.
946 *
947 * @param query
948 * @param expression
949 * @param targetSqlType
950 * @throws FilterEvaluationException
951 */
952 protected void appendArithmeticExpressionAsSQL( StatementBuffer query, ArithmeticExpression expression,
953 int targetSqlType )
954 throws FilterEvaluationException {
955
956 // quirk to enable SQL-level type conversions of numbers. We really should use numbers as literals in the SQL
957 // string.
958 switch ( targetSqlType ) {
959 case INTEGER:
960 case SMALLINT: {
961 targetSqlType = DOUBLE;
962 }
963 }
964
965 query.append( '(' );
966 appendExpressionAsSQL( query, expression.getFirstExpression(), targetSqlType );
967 switch ( expression.getExpressionId() ) {
968 case ADD: {
969 query.append( '+' );
970 break;
971 }
972 case SUB: {
973 query.append( '-' );
974 break;
975 }
976 case MUL: {
977 query.append( '*' );
978 break;
979 }
980 case DIV: {
981 query.append( '/' );
982 break;
983 }
984 }
985 appendExpressionAsSQL( query, expression.getSecondExpression(), targetSqlType );
986 query.append( ')' );
987 }
988
989 /**
990 * Appends an SQL fragment for the given object to the given sql statement.
991 *
992 * @param query
993 * @param function
994 * @param targetSqlType
995 * @throws FilterEvaluationException
996 */
997 protected void appendFunctionAsSQL( StatementBuffer query, Function function, int targetSqlType )
998 throws FilterEvaluationException {
999 if ( function instanceof DBFunction ) {
1000 query.append( function.getName() );
1001 query.append( " (" );
1002 List<?> list = function.getArguments();
1003 for ( int i = 0; i < list.size(); i++ ) {
1004 Expression expression = (Expression) list.get( i );
1005 appendExpressionAsSQL( query, expression, targetSqlType );
1006 if ( i != list.size() - 1 )
1007 query.append( ", " );
1008 }
1009 query.append( ")" );
1010 } else {
1011 Object o = function.evaluate( null );
1012 if ( o != null ) {
1013 Literal literal = new Literal( o.toString() );
1014 appendExpressionAsSQL( query, literal, targetSqlType );
1015 }
1016 }
1017 }
1018
1019 /**
1020 * Appends an SQL fragment for the given object to the given sql statement.
1021 *
1022 * @param query
1023 * @param operation
1024 * @throws DatastoreException
1025 */
1026 protected void appendLogicalOperationAsSQL( StatementBuffer query, LogicalOperation operation )
1027 throws DatastoreException {
1028 List<?> argumentList = operation.getArguments();
1029 switch ( operation.getOperatorId() ) {
1030 case OperationDefines.AND: {
1031 for ( int i = 0; i < argumentList.size(); i++ ) {
1032 Operation argument = (Operation) argumentList.get( i );
1033 query.append( '(' );
1034 appendOperationAsSQL( query, argument );
1035 query.append( ')' );
1036 if ( i != argumentList.size() - 1 )
1037 query.append( " AND " );
1038 }
1039 break;
1040 }
1041 case OperationDefines.OR: {
1042 for ( int i = 0; i < argumentList.size(); i++ ) {
1043 Operation argument = (Operation) argumentList.get( i );
1044 query.append( '(' );
1045 appendOperationAsSQL( query, argument );
1046 query.append( ')' );
1047 if ( i != argumentList.size() - 1 )
1048 query.append( " OR " );
1049 }
1050 break;
1051 }
1052 case OperationDefines.NOT: {
1053 Operation argument = (Operation) argumentList.get( 0 );
1054 query.append( "NOT (" );
1055 appendOperationAsSQL( query, argument );
1056 query.append( ')' );
1057 break;
1058 }
1059 }
1060 }
1061
1062 /**
1063 * Appends an SQL fragment for the given object to the given sql statement.
1064 *
1065 * TODO Handle compound primary keys correctly.
1066 *
1067 * @param query
1068 * @param filter
1069 * @throws DatastoreException
1070 */
1071 protected void appendFeatureFilterAsSQL( StatementBuffer query, FeatureFilter filter )
1072 throws DatastoreException {
1073
1074 // List list = filter.getFeatureIds();
1075 // Iterator it = list.iterator();
1076 // while (it.hasNext()) {
1077 // FeatureId fid = (FeatureId) it.next();
1078 // MappingField mapping = null;
1079 // DatastoreMapping mapping = featureType.getFidDefinition().getFidFields()[0];
1080 // query.append( ' ' );
1081 // query.append( this.joinTableTree.getAlias() );
1082 // query.append( "." );
1083 // query.append( mapping.getField() );
1084 // query.append( "=?" );
1085 // query.addArgument( fid.getValue() );
1086 // if ( it.hasNext() ) {
1087 // query.append( " OR" );
1088 // }
1089 // }
1090
1091 if ( this.rootFts.length > 1 ) {
1092 String msg = Messages.getMessage( "DATASTORE_FEATURE_QUERY_MORE_THAN_FEATURE_TYPE" );
1093 throw new DatastoreException( msg );
1094 }
1095
1096 MappedFeatureType rootFt = this.rootFts[0];
1097 MappingField[] idFields = rootFt.getGMLId().getIdFields();
1098 MappingField idField = null;
1099 String tbl = getRootTableAlias( 0 );
1100 ArrayList<?> list = filter.getFeatureIds();
1101 try {
1102 for ( int i = 0; i < idFields.length; i++ ) {
1103 idField = idFields[i];
1104 if ( i == 0 ) {
1105 query.append( ' ' );
1106 } else {
1107 query.append( " AND " );
1108 }
1109 if ( null != tbl && 0 < tbl.length() ) {
1110 query.append( tbl );
1111 query.append( "." );
1112 }
1113 query.append( idField.getField() );
1114 for ( int j = 0; j < list.size(); j++ ) {
1115 if ( j == 0 ) {
1116 query.append( " IN (?" );
1117 } else {
1118 query.append( ",?" );
1119 }
1120 if ( j == list.size() - 1 ) {
1121 query.append( ")" );
1122 }
1123 String fid = ( (org.deegree.model.filterencoding.FeatureId) list.get( j ) ).getValue();
1124 Object fidValue = org.deegree.io.datastore.FeatureId.removeFIDPrefix( fid, rootFt.getGMLId() );
1125 if ( idFields.length > 1 ) { // Equal to: if ( fidValue instanceof Object[] ) {
1126 fidValue = ( (Object[]) fidValue )[i];
1127 }
1128 query.addArgument( fidValue, idField.getType() );
1129 }
1130 }
1131 } catch ( Exception e ) {
1132 LOG.logError( "Error converting feature id", e );
1133 }
1134 }
1135
1136 /**
1137 * Appends an SQL fragment for the given object to the given sql statement. As this depends on the handling of
1138 * geometry data by the concrete database in use, this method must be overwritten by any datastore implementation
1139 * that has spatial capabilities.
1140 *
1141 * @param query
1142 * @param operation
1143 * @throws DatastoreException
1144 */
1145 protected void appendSpatialOperationAsSQL( @SuppressWarnings("unused") StatementBuffer query,
1146 @SuppressWarnings("unused") SpatialOperation operation )
1147 throws DatastoreException {
1148 String msg = "Spatial operations are not supported by the WhereBuilder implementation in use: '" + getClass()
1149 + "'";
1150 throw new DatastoreException( msg );
1151 }
1152
1153 /**
1154 * Prepares the function map for functions with implementation specific names, e.g. upper case conversion in ORACLE
1155 * = UPPER(string); POSTGRES = UPPER(string), and MS Access = UCase(string). Default SQL-function name map function
1156 * 'UPPER' is 'UPPER'. If this function shall be used with user databases e.g. SQLServer a specialized WhereBuilder
1157 * must override this method.
1158 */
1159 protected void fillFunctionNameMap() {
1160 functionMap.clear();
1161 functionMap.put( "LOWER", "LOWER" );
1162 }
1163
1164 /**
1165 * Get the function with the specified name.
1166 *
1167 * @param name
1168 * the function name
1169 * @return the mapped function name
1170 */
1171 protected String getFunctionName( String name ) {
1172 String f = functionMap.get( name );
1173 if ( null == f )
1174 f = name;
1175 return f;
1176 }
1177 }