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