001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_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: mschneider $
105 *
106 * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $
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.logError( 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 new DatastoreException( e.getMessage(), e );
645 }
646 break;
647 }
648 case OperationDefines.TYPE_LOGICAL: {
649 appendLogicalOperationAsSQL( query, (LogicalOperation) operation );
650 break;
651 }
652 default: {
653 break;
654 }
655 }
656 }
657
658 /**
659 * Appends an SQL fragment for the given object to the given sql statement.
660 *
661 * @param query
662 * @param operation
663 * @throws FilterEvaluationException
664 */
665 protected void appendComparisonOperationAsSQL( StatementBuffer query, ComparisonOperation operation )
666 throws FilterEvaluationException {
667 switch ( operation.getOperatorId() ) {
668 case OperationDefines.PROPERTYISEQUALTO:
669 case OperationDefines.PROPERTYISNOTEQUALTO:
670 case OperationDefines.PROPERTYISLESSTHAN:
671 case OperationDefines.PROPERTYISGREATERTHAN:
672 case OperationDefines.PROPERTYISLESSTHANOREQUALTO:
673 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
674 appendPropertyIsCOMPOperationAsSQL( query, (PropertyIsCOMPOperation) operation );
675 break;
676 }
677 case OperationDefines.PROPERTYISLIKE: {
678 appendPropertyIsLikeOperationAsSQL( query, (PropertyIsLikeOperation) operation );
679 break;
680 }
681 case OperationDefines.PROPERTYISNULL: {
682 appendPropertyIsNullOperationAsSQL( query, (PropertyIsNullOperation) operation );
683 break;
684 }
685 case OperationDefines.PROPERTYISBETWEEN: {
686 appendPropertyIsBetweenOperationAsSQL( query, (PropertyIsBetweenOperation) operation );
687 break;
688 }
689 }
690 }
691
692 /**
693 * Appends an SQL fragment for the given object to the given sql statement.
694 *
695 * @param query
696 * @param operation
697 * @throws FilterEvaluationException
698 */
699 protected void appendPropertyIsCOMPOperationAsSQL( StatementBuffer query, PropertyIsCOMPOperation operation )
700 throws FilterEvaluationException {
701 Expression firstExpr = operation.getFirstExpression();
702 if ( !( firstExpr instanceof PropertyName ) ) {
703 throw new IllegalArgumentException( "First expression in a comparison must "
704 + "always be a 'PropertyName' element." );
705 }
706 int targetSqlType = getPropertyNameSQLType( (PropertyName) firstExpr );
707 if ( operation.isMatchCase() ) {
708 appendExpressionAsSQL( query, firstExpr, targetSqlType );
709 } else {
710 List<Expression> list = new ArrayList<Expression>();
711 list.add( firstExpr );
712 Function func = new DBFunction( getFunctionName( "LOWER" ), list );
713 appendFunctionAsSQL( query, func, targetSqlType );
714 }
715 switch ( operation.getOperatorId() ) {
716 case OperationDefines.PROPERTYISEQUALTO: {
717 query.append( " = " );
718 break;
719 }
720 case OperationDefines.PROPERTYISNOTEQUALTO: {
721 query.append( " <> " );
722 break;
723 }
724 case OperationDefines.PROPERTYISLESSTHAN: {
725 query.append( " < " );
726 break;
727 }
728 case OperationDefines.PROPERTYISGREATERTHAN: {
729 query.append( " > " );
730 break;
731 }
732 case OperationDefines.PROPERTYISLESSTHANOREQUALTO: {
733 query.append( " <= " );
734 break;
735 }
736 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
737 query.append( " >= " );
738 break;
739 }
740 }
741 if ( operation.isMatchCase() ) {
742 appendExpressionAsSQL( query, operation.getSecondExpression(), targetSqlType );
743 } else {
744 List<Expression> list = new ArrayList<Expression>();
745 list.add( operation.getSecondExpression() );
746 Function func = new DBFunction( getFunctionName( "LOWER" ), list );
747 appendFunctionAsSQL( query, func, targetSqlType );
748 }
749 }
750
751 /**
752 * Appends an SQL fragment for the given object to the given sql statement.
753 *
754 * @param query
755 * @param operation
756 * @throws FilterEvaluationException
757 */
758 protected void appendPropertyIsLikeOperationAsSQL( StatementBuffer query, PropertyIsLikeOperation operation )
759 throws FilterEvaluationException {
760
761 String literal = operation.getLiteral().getValue();
762 String escape = "" + operation.getEscapeChar();
763 String wildCard = "" + operation.getWildCard();
764 String singleChar = "" + operation.getSingleChar();
765
766 SpecialCharString specialString = new SpecialCharString( literal, wildCard, singleChar, escape );
767 String sqlEncoded = specialString.toSQLStyle( !operation.isMatchCase() );
768
769 int targetSqlType = getPropertyNameSQLType( operation.getPropertyName() );
770
771 // if isMatchCase == false surround first argument with LOWER (...) and convert characters
772 // in second argument to lower case
773 if ( operation.isMatchCase() ) {
774 appendPropertyNameAsSQL( query, operation.getPropertyName() );
775 } else {
776 List<Expression> list = new ArrayList<Expression>();
777 list.add( operation.getPropertyName() );
778 Function func = new DBFunction( getFunctionName( "LOWER" ), list );
779 appendFunctionAsSQL( query, func, targetSqlType );
780 }
781
782 query.append( " LIKE ? ESCAPE ?" );
783 query.addArgument( sqlEncoded, Types.VARCHAR );
784 query.addArgument( "\\", Types.VARCHAR );
785 }
786
787 /**
788 * Appends an SQL fragment for the given object to the given sql statement.
789 *
790 * @param query
791 * @param operation
792 */
793 protected void appendPropertyIsNullOperationAsSQL( StatementBuffer query, PropertyIsNullOperation operation ) {
794 appendPropertyNameAsSQL( query, operation.getPropertyName() );
795 query.append( " IS NULL" );
796 }
797
798 /**
799 * Appends an SQL fragment for the given object to the given sql statement.
800 *
801 * @param query
802 * @param operation
803 * @throws FilterEvaluationException
804 */
805 protected void appendPropertyIsBetweenOperationAsSQL( StatementBuffer query, PropertyIsBetweenOperation operation )
806 throws FilterEvaluationException {
807
808 PropertyName propertyName = operation.getPropertyName();
809 int targetSqlType = getPropertyNameSQLType( propertyName );
810 appendExpressionAsSQL( query, operation.getLowerBoundary(), targetSqlType );
811 query.append( " <= " );
812 appendPropertyNameAsSQL( query, propertyName );
813 query.append( " AND " );
814 appendPropertyNameAsSQL( query, propertyName );
815 query.append( " <= " );
816 appendExpressionAsSQL( query, operation.getUpperBoundary(), targetSqlType );
817 }
818
819 /**
820 * Appends an SQL fragment for the given object to the given sql statement.
821 *
822 * @param query
823 * @param expression
824 * @param targetSqlType
825 * sql type code to be used for literals at the bottom of the expression tree
826 * @throws FilterEvaluationException
827 */
828 protected void appendExpressionAsSQL( StatementBuffer query, Expression expression, int targetSqlType )
829 throws FilterEvaluationException {
830 switch ( expression.getExpressionId() ) {
831 case ExpressionDefines.PROPERTYNAME: {
832 appendPropertyNameAsSQL( query, (PropertyName) expression );
833 break;
834 }
835 case ExpressionDefines.LITERAL: {
836 appendLiteralAsSQL( query, (Literal) expression, targetSqlType );
837 break;
838 }
839 case ExpressionDefines.FUNCTION: {
840 Function function = (Function) expression;
841 appendFunctionAsSQL( query, function, targetSqlType );
842 break;
843 }
844 case ExpressionDefines.ADD:
845 case ExpressionDefines.SUB:
846 case ExpressionDefines.MUL:
847 case ExpressionDefines.DIV: {
848 appendArithmeticExpressionAsSQL( query, (ArithmeticExpression) expression, targetSqlType );
849 break;
850 }
851 case ExpressionDefines.EXPRESSION:
852 default: {
853 throw new IllegalArgumentException( "Unexpected expression type: " + expression.getExpressionName() );
854 }
855 }
856 }
857
858 /**
859 * Appends an SQL fragment for the given object to the given sql statement.
860 *
861 * @param query
862 * @param literal
863 * @param targetSqlType
864 */
865 protected void appendLiteralAsSQL( StatementBuffer query, Literal literal, int targetSqlType ) {
866 query.append( '?' );
867 query.addArgument( literal.getValue(), targetSqlType );
868 }
869
870 /**
871 * Appends an SQL fragment for the given object to the given sql statement.
872 *
873 * @param query
874 * @param propertyName
875 */
876 protected void appendPropertyNameAsSQL( StatementBuffer query, PropertyName propertyName ) {
877
878 PropertyPath propertyPath = propertyName.getValue();
879 appendPropertyPathAsSQL( query, propertyPath );
880 }
881
882 /**
883 * Appends an SQL fragment for the given object to the given sql statement.
884 *
885 * @param query
886 * @param propertyPath
887 */
888 protected void appendPropertyPathAsSQL( StatementBuffer query, PropertyPath propertyPath ) {
889
890 LOG.logDebug( "Looking up '" + propertyPath + "' in the query table tree." );
891 MappingField mappingField = null;
892 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
893 assert ( propertyNode != null );
894 if ( propertyNode instanceof SimplePropertyNode ) {
895 SimpleContent content = ( (MappedSimplePropertyType) ( propertyNode.getProperty() ) ).getContent();
896 if ( !( content instanceof MappingField ) ) {
897 if ( content instanceof ConstantContent ) {
898 query.append( "'" + ( (ConstantContent) content ).getValue() + "'" );
899 return;
900 } else if ( content instanceof SQLFunctionCall ) {
901 SQLFunctionCall call = (SQLFunctionCall) content;
902 String tableAlias = null;
903 String[] tableAliases = propertyNode.getTableAliases();
904 if ( tableAliases == null || tableAliases.length == 0 ) {
905 tableAlias = propertyNode.getParent().getTableAlias();
906 } else {
907 tableAlias = tableAliases[tableAliases.length - 1];
908 }
909 this.vcProvider.appendSQLFunctionCall( query, tableAlias, call );
910 return;
911 }
912 String msg = "Virtual properties are currently ignored in WhereBuilder#appendPropertyPathAsSQL(StatementBuffer,PropertyPath).";
913 LOG.logError( msg );
914 assert false;
915 }
916 mappingField = (MappingField) content;
917 } else if ( propertyNode instanceof GeometryPropertyNode ) {
918 mappingField = ( (MappedGeometryPropertyType) propertyNode.getProperty() ).getMappingField();
919 } else {
920 String msg = "Internal error in WhereBuilder: unhandled PropertyNode type: '"
921 + propertyNode.getClass().getName() + "'.";
922 LOG.logError( msg );
923 throw new RuntimeException( msg );
924 }
925 String tableAlias = null;
926 String[] tableAliases = propertyNode.getTableAliases();
927 if ( tableAliases == null || tableAliases.length == 0 ) {
928 tableAlias = propertyNode.getParent().getTableAlias();
929 } else {
930 tableAlias = tableAliases[tableAliases.length - 1];
931 }
932 if ( tableAlias != "" ) {
933 query.append( tableAlias );
934 query.append( '.' );
935 } else {
936 query.append( mappingField.getTable() );
937 query.append( '.' );
938 }
939 query.append( mappingField.getField() );
940 }
941
942 /**
943 * Appends an SQL fragment for the given object to the given sql statement.
944 *
945 * @param query
946 * @param expression
947 * @param targetSqlType
948 * @throws FilterEvaluationException
949 */
950 protected void appendArithmeticExpressionAsSQL( StatementBuffer query, ArithmeticExpression expression,
951 int targetSqlType )
952 throws FilterEvaluationException {
953
954 // quirk to enable SQL-level type conversions of numbers. We really should use numbers as literals in the SQL
955 // string.
956 switch ( targetSqlType ) {
957 case INTEGER:
958 case SMALLINT: {
959 targetSqlType = DOUBLE;
960 }
961 }
962
963 query.append( '(' );
964 appendExpressionAsSQL( query, expression.getFirstExpression(), targetSqlType );
965 switch ( expression.getExpressionId() ) {
966 case ADD: {
967 query.append( '+' );
968 break;
969 }
970 case SUB: {
971 query.append( '-' );
972 break;
973 }
974 case MUL: {
975 query.append( '*' );
976 break;
977 }
978 case DIV: {
979 query.append( '/' );
980 break;
981 }
982 }
983 appendExpressionAsSQL( query, expression.getSecondExpression(), targetSqlType );
984 query.append( ')' );
985 }
986
987 /**
988 * Appends an SQL fragment for the given object to the given sql statement.
989 *
990 * @param query
991 * @param function
992 * @param targetSqlType
993 * @throws FilterEvaluationException
994 */
995 protected void appendFunctionAsSQL( StatementBuffer query, Function function, int targetSqlType )
996 throws FilterEvaluationException {
997 if ( function instanceof DBFunction ) {
998 query.append( function.getName() );
999 query.append( " (" );
1000 List<?> list = function.getArguments();
1001 for ( int i = 0; i < list.size(); i++ ) {
1002 Expression expression = (Expression) list.get( i );
1003 appendExpressionAsSQL( query, expression, targetSqlType );
1004 if ( i != list.size() - 1 )
1005 query.append( ", " );
1006 }
1007 query.append( ")" );
1008 } else {
1009 Object o = function.evaluate( null );
1010 if ( o != null ) {
1011 Literal literal = new Literal( o.toString() );
1012 appendExpressionAsSQL( query, literal, targetSqlType );
1013 }
1014 }
1015 }
1016
1017 /**
1018 * Appends an SQL fragment for the given object to the given sql statement.
1019 *
1020 * @param query
1021 * @param operation
1022 * @throws DatastoreException
1023 */
1024 protected void appendLogicalOperationAsSQL( StatementBuffer query, LogicalOperation operation )
1025 throws DatastoreException {
1026 List<?> argumentList = operation.getArguments();
1027 switch ( operation.getOperatorId() ) {
1028 case OperationDefines.AND: {
1029 for ( int i = 0; i < argumentList.size(); i++ ) {
1030 Operation argument = (Operation) argumentList.get( i );
1031 query.append( '(' );
1032 appendOperationAsSQL( query, argument );
1033 query.append( ')' );
1034 if ( i != argumentList.size() - 1 )
1035 query.append( " AND " );
1036 }
1037 break;
1038 }
1039 case OperationDefines.OR: {
1040 for ( int i = 0; i < argumentList.size(); i++ ) {
1041 Operation argument = (Operation) argumentList.get( i );
1042 query.append( '(' );
1043 appendOperationAsSQL( query, argument );
1044 query.append( ')' );
1045 if ( i != argumentList.size() - 1 )
1046 query.append( " OR " );
1047 }
1048 break;
1049 }
1050 case OperationDefines.NOT: {
1051 Operation argument = (Operation) argumentList.get( 0 );
1052 query.append( "NOT (" );
1053 appendOperationAsSQL( query, argument );
1054 query.append( ')' );
1055 break;
1056 }
1057 }
1058 }
1059
1060 /**
1061 * Appends an SQL fragment for the given object to the given sql statement.
1062 *
1063 * TODO Handle compound primary keys correctly.
1064 *
1065 * @param query
1066 * @param filter
1067 * @throws DatastoreException
1068 */
1069 protected void appendFeatureFilterAsSQL( StatementBuffer query, FeatureFilter filter )
1070 throws DatastoreException {
1071
1072 // List list = filter.getFeatureIds();
1073 // Iterator it = list.iterator();
1074 // while (it.hasNext()) {
1075 // FeatureId fid = (FeatureId) it.next();
1076 // MappingField mapping = null;
1077 // DatastoreMapping mapping = featureType.getFidDefinition().getFidFields()[0];
1078 // query.append( ' ' );
1079 // query.append( this.joinTableTree.getAlias() );
1080 // query.append( "." );
1081 // query.append( mapping.getField() );
1082 // query.append( "=?" );
1083 // query.addArgument( fid.getValue() );
1084 // if ( it.hasNext() ) {
1085 // query.append( " OR" );
1086 // }
1087 // }
1088
1089 if ( this.rootFts.length > 1 ) {
1090 String msg = Messages.getMessage( "DATASTORE_FEATURE_QUERY_MORE_THAN_FEATURE_TYPE" );
1091 throw new DatastoreException( msg );
1092 }
1093
1094 MappedFeatureType rootFt = this.rootFts[0];
1095 MappingField[] idFields = rootFt.getGMLId().getIdFields();
1096 MappingField idField = null;
1097 String tbl = getRootTableAlias( 0 );
1098 ArrayList<?> list = filter.getFeatureIds();
1099 try {
1100 for ( int i = 0; i < idFields.length; i++ ) {
1101 idField = idFields[i];
1102 if ( i == 0 ) {
1103 query.append( ' ' );
1104 } else {
1105 query.append( " AND " );
1106 }
1107 if ( null != tbl && 0 < tbl.length() ) {
1108 query.append( tbl );
1109 query.append( "." );
1110 }
1111 query.append( idField.getField() );
1112 for ( int j = 0; j < list.size(); j++ ) {
1113 if ( j == 0 ) {
1114 query.append( " IN (?" );
1115 } else {
1116 query.append( ",?" );
1117 }
1118 if ( j == list.size() - 1 ) {
1119 query.append( ")" );
1120 }
1121 String fid = ( (org.deegree.model.filterencoding.FeatureId) list.get( j ) ).getValue();
1122 Object fidValue = org.deegree.io.datastore.FeatureId.removeFIDPrefix( fid, rootFt.getGMLId() );
1123 if ( idFields.length > 1 ) { // Equal to: if ( fidValue instanceof Object[] ) {
1124 fidValue = ( (Object[]) fidValue )[i];
1125 }
1126 query.addArgument( fidValue, idField.getType() );
1127 }
1128 }
1129 } catch ( Exception e ) {
1130 LOG.logError( "Error converting feature id", e );
1131 }
1132 }
1133
1134 /**
1135 * Appends an SQL fragment for the given object to the given sql statement. As this depends on the handling of
1136 * geometry data by the concrete database in use, this method must be overwritten by any datastore implementation
1137 * that has spatial capabilities.
1138 *
1139 * @param query
1140 * @param operation
1141 * @throws DatastoreException
1142 */
1143 protected void appendSpatialOperationAsSQL( @SuppressWarnings("unused") StatementBuffer query,
1144 @SuppressWarnings("unused") SpatialOperation operation )
1145 throws DatastoreException {
1146 String msg = "Spatial operations are not supported by the WhereBuilder implementation in use: '" + getClass()
1147 + "'";
1148 throw new DatastoreException( msg );
1149 }
1150
1151 /**
1152 * Prepares the function map for functions with implementation specific names, e.g. upper case conversion in ORACLE
1153 * = UPPER(string); POSTGRES = UPPER(string), and MS Access = UCase(string). Default SQL-function name map function
1154 * 'UPPER' is 'UPPER'. If this function shall be used with user databases e.g. SQLServer a specialized WhereBuilder
1155 * must override this method.
1156 */
1157 protected void fillFunctionNameMap() {
1158 functionMap.clear();
1159 functionMap.put( "LOWER", "LOWER" );
1160 }
1161
1162 /**
1163 * Get the function with the specified name.
1164 *
1165 * @param name
1166 * the function name
1167 * @return the mapped function name
1168 */
1169 protected String getFunctionName( String name ) {
1170 String f = functionMap.get( name );
1171 if ( null == f )
1172 f = name;
1173 return f;
1174 }
1175 }