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