001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/tags/2.1/src/org/deegree/io/datastore/sde/SDEWhereBuilder.java $
002 /*---------------- FILE HEADER ------------------------------------------
003
004 This file is part of deegree.
005 Copyright (C) 2006 by: M.O.S.S. Computer Grafik Systeme GmbH
006 Hohenbrunner Weg 13
007 D-82024 Taufkirchen
008 http://www.moss.de/
009
010 This library is free software; you can redistribute it and/or
011 modify it under the terms of the GNU Lesser General Public
012 License as published by the Free Software Foundation; either
013 version 2.1 of the License, or (at your option) any later version.
014
015 This library is distributed in the hope that it will be useful,
016 but WITHOUT ANY WARRANTY; without even the implied warranty of
017 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
018 Lesser General Public License for more details.
019
020 You should have received a copy of the GNU Lesser General Public
021 License along with this library; if not, write to the Free Software
022 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
023
024 ---------------------------------------------------------------------------*/
025 package org.deegree.io.datastore.sde;
026
027 import java.sql.Types;
028 import java.util.ArrayList;
029 import java.util.Iterator;
030 import java.util.List;
031
032 import org.deegree.framework.log.ILogger;
033 import org.deegree.framework.log.LoggerFactory;
034 import org.deegree.io.datastore.DatastoreException;
035 import org.deegree.io.datastore.PropertyPathResolvingException;
036 import org.deegree.io.datastore.schema.MappedFeatureType;
037 import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
038 import org.deegree.io.datastore.schema.MappedPropertyType;
039 import org.deegree.io.datastore.schema.MappedSimplePropertyType;
040 import org.deegree.io.datastore.schema.content.MappingField;
041 import org.deegree.io.datastore.schema.content.MappingGeometryField;
042 import org.deegree.io.datastore.schema.content.SimpleContent;
043 import org.deegree.io.datastore.sql.TableAliasGenerator;
044 import org.deegree.io.datastore.sql.wherebuilder.GeometryPropertyNode;
045 import org.deegree.io.datastore.sql.wherebuilder.PropertyNode;
046 import org.deegree.io.datastore.sql.wherebuilder.QueryTableTree;
047 import org.deegree.io.datastore.sql.wherebuilder.SimplePropertyNode;
048 import org.deegree.io.sdeapi.SDEAdapter;
049 import org.deegree.model.filterencoding.ArithmeticExpression;
050 import org.deegree.model.filterencoding.ComparisonOperation;
051 import org.deegree.model.filterencoding.ComplexFilter;
052 import org.deegree.model.filterencoding.Expression;
053 import org.deegree.model.filterencoding.ExpressionDefines;
054 import org.deegree.model.filterencoding.FeatureFilter;
055 import org.deegree.model.filterencoding.Filter;
056 import org.deegree.model.filterencoding.FilterTools;
057 import org.deegree.model.filterencoding.Function;
058 import org.deegree.model.filterencoding.Literal;
059 import org.deegree.model.filterencoding.LogicalOperation;
060 import org.deegree.model.filterencoding.Operation;
061 import org.deegree.model.filterencoding.OperationDefines;
062 import org.deegree.model.filterencoding.PropertyIsBetweenOperation;
063 import org.deegree.model.filterencoding.PropertyIsCOMPOperation;
064 import org.deegree.model.filterencoding.PropertyIsLikeOperation;
065 import org.deegree.model.filterencoding.PropertyIsNullOperation;
066 import org.deegree.model.filterencoding.PropertyName;
067 import org.deegree.model.filterencoding.SpatialOperation;
068 import org.deegree.ogcbase.PropertyPath;
069
070 import com.esri.sde.sdk.client.SeCoordinateReference;
071 import com.esri.sde.sdk.client.SeFilter;
072 import com.esri.sde.sdk.client.SeLayer;
073 import com.esri.sde.sdk.client.SeShape;
074 import com.esri.sde.sdk.client.SeShapeFilter;
075
076 /**
077 * <code>WhereBuilder</code> implementation for ArcSDE.
078 *
079 * @author <a href="mailto:cpollmann@moss.de">Christoph Pollmann</a>
080 * @author last edited by: $Author: apoth $
081 *
082 * @version $Revision: 7844 $, $Date: 2007-07-25 09:45:07 +0200 (Mi, 25 Jul 2007) $
083 */
084 public class SDEWhereBuilder {
085
086 protected static final ILogger LOG = LoggerFactory.getLogger( SDEWhereBuilder.class );
087
088 protected MappedFeatureType rootFeatureType;
089
090 protected Filter filter;
091
092 protected QueryTableTree queryTableTree;
093
094 protected List<PropertyPath> filterPropertyPaths = new ArrayList<PropertyPath>();
095
096 /**
097 * Creates a new instance of <code>SDEWhereBuilder</code> for the given parameters.
098 *
099 * @param rootFts
100 * selected feature types, more than one type means that the types are joined
101 * @param aliases
102 * aliases for the feature types, may be null (must have same length as rootFts
103 * otherwise)
104 * @param filter
105 * @param aliasGenerator
106 * @throws DatastoreException
107 */
108 public SDEWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
109 TableAliasGenerator aliasGenerator ) throws DatastoreException {
110
111 this.rootFeatureType = rootFts [0];
112 this.queryTableTree = new QueryTableTree( rootFts, aliases, aliasGenerator );
113 this.filter = filter;
114 if ( filter != null ) {
115 if ( !( filter instanceof ComplexFilter || filter instanceof FeatureFilter ) ) {
116 throw new DatastoreException( "Invalid filter type: '" + filter.getClass()
117 + "'. Filter must be a ComplexFilter or a FeatureFilter." );
118 }
119 buildFilterPropertyNameMap();
120 for ( PropertyPath property : this.filterPropertyPaths ) {
121 this.queryTableTree.addFilterProperty( property );
122 }
123 }
124 }
125
126 /**
127 * @return
128 */
129 public String getRootTableAlias() {
130 return this.queryTableTree.getRootAlias();
131 }
132
133 /**
134 * @return filter
135 */
136 public Filter getFilter() {
137 return this.filter;
138 }
139
140 /**
141 * Returns the internal (database specific) SRS code used in the geometry field of the given
142 * <code>SpatialOperation</code>.
143 *
144 * @param operation
145 * <code>SpatialOperation</code> for which the internal SRS is needed
146 * @return the internal (database specific) SRS code.
147 */
148 protected int getInternalSRS( SpatialOperation operation ) {
149 PropertyPath propertyPath = operation.getPropertyName().getValue();
150 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
151 if ( propertyNode == null ) {
152 String msg = "Internal error in WhereBuilder: no PropertyNode for path '" + propertyPath
153 + "' in QueryTableTree.";
154 LOG.logError( msg );
155 throw new RuntimeException( msg );
156 } else if ( !( propertyNode instanceof GeometryPropertyNode ) ) {
157 String msg = "Internal error in WhereBuilder: unexpected PropertyNode type: '"
158 + propertyNode.getClass().getName() + "'. Must be a GeometryPropertyNode.";
159 LOG.logError( msg );
160 throw new RuntimeException( msg );
161 }
162 MappedGeometryPropertyType gpc = (MappedGeometryPropertyType) propertyNode.getProperty();
163 MappingGeometryField field = gpc.getMappingField();
164 return field.getSRS();
165 }
166
167 /**
168 * @param propertyName
169 * @return
170 */
171 protected int getPropertyNameSQLType( PropertyName propertyName ) {
172 PropertyPath propertyPath = propertyName.getValue();
173 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
174 if ( propertyNode == null ) {
175 String msg = "Internal error in WhereBuilder: no PropertyNode for path '" + propertyPath
176 + "' in QueryTableTree.";
177 LOG.logError( msg );
178 throw new RuntimeException( msg );
179 }
180 MappedPropertyType propertyType = propertyNode.getProperty();
181 if ( !( propertyType instanceof MappedSimplePropertyType ) ) {
182 String msg = "Error in WhereBuilder: cannot compare against properties of type '" + propertyType.getClass()
183 + "'.";
184 LOG.logError( msg );
185 throw new RuntimeException( msg );
186 }
187
188 SimpleContent content = ( (MappedSimplePropertyType) propertyType ).getContent();
189 if ( !( content instanceof MappingField ) ) {
190 String msg = "Virtual properties are currently ignored in SDEWhereBuilder#getPropertyNameSQLType(PropertyName).";
191 LOG.logError( msg );
192 return Types.VARCHAR;
193 }
194
195 int targetSqlType = ( (MappingField) content ).getType();
196 return targetSqlType;
197 }
198
199 /**
200 * @throws PropertyPathResolvingException
201 */
202 protected void buildFilterPropertyNameMap()
203 throws PropertyPathResolvingException {
204 if ( this.filter instanceof ComplexFilter ) {
205 buildPropertyNameMapFromOperation( ( (ComplexFilter) this.filter ).getOperation() );
206 } else if ( this.filter instanceof FeatureFilter ) {
207 // FeatureFilter doesn't have real properties, so we don't have to add them here
208 // maybe for join tables and table aliases we need some auxiliary constructions???
209 // throw new PropertyPathResolvingException( "FeatureFilter not implemented yet." );
210 }
211 }
212
213 private void buildPropertyNameMapFromOperation( Operation operation )
214 throws PropertyPathResolvingException {
215 switch ( OperationDefines.getTypeById( operation.getOperatorId() ) ) {
216 case OperationDefines.TYPE_SPATIAL: {
217 registerPropertyName( ( (SpatialOperation) operation ).getPropertyName() );
218 break;
219 }
220 case OperationDefines.TYPE_COMPARISON: {
221 buildPropertyNameMap( (ComparisonOperation) operation );
222 break;
223 }
224 case OperationDefines.TYPE_LOGICAL: {
225 buildPropertyNameMap( (LogicalOperation) operation );
226 break;
227 }
228 default: {
229 break;
230 }
231 }
232 }
233
234 private void buildPropertyNameMap( ComparisonOperation operation )
235 throws PropertyPathResolvingException {
236 switch ( operation.getOperatorId() ) {
237 case OperationDefines.PROPERTYISEQUALTO:
238 case OperationDefines.PROPERTYISLESSTHAN:
239 case OperationDefines.PROPERTYISGREATERTHAN:
240 case OperationDefines.PROPERTYISLESSTHANOREQUALTO:
241 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
242 buildPropertyNameMap( ( (PropertyIsCOMPOperation) operation ).getFirstExpression() );
243 buildPropertyNameMap( ( (PropertyIsCOMPOperation) operation ).getSecondExpression() );
244 break;
245 }
246 case OperationDefines.PROPERTYISLIKE: {
247 registerPropertyName( ( (PropertyIsLikeOperation) operation ).getPropertyName() );
248 break;
249 }
250 case OperationDefines.PROPERTYISNULL: {
251 buildPropertyNameMap( ( (PropertyIsNullOperation) operation ).getPropertyName() );
252 break;
253 }
254 case OperationDefines.PROPERTYISBETWEEN: {
255 buildPropertyNameMap( ( (PropertyIsBetweenOperation) operation ).getLowerBoundary() );
256 buildPropertyNameMap( ( (PropertyIsBetweenOperation) operation ).getUpperBoundary() );
257 registerPropertyName( ( (PropertyIsBetweenOperation) operation ).getPropertyName() );
258 break;
259 }
260 default: {
261 break;
262 }
263 }
264 }
265
266 private void buildPropertyNameMap( LogicalOperation operation )
267 throws PropertyPathResolvingException {
268 List operationList = operation.getArguments();
269 Iterator it = operationList.iterator();
270 while ( it.hasNext() ) {
271 buildPropertyNameMapFromOperation( (Operation) it.next() );
272 }
273 }
274
275 private void buildPropertyNameMap( Expression expression )
276 throws PropertyPathResolvingException {
277 switch ( expression.getExpressionId() ) {
278 case ExpressionDefines.PROPERTYNAME: {
279 registerPropertyName( (PropertyName) expression );
280 break;
281 }
282 case ExpressionDefines.ADD:
283 case ExpressionDefines.SUB:
284 case ExpressionDefines.MUL:
285 case ExpressionDefines.DIV: {
286 buildPropertyNameMap( ( (ArithmeticExpression) expression ).getFirstExpression() );
287 buildPropertyNameMap( ( (ArithmeticExpression) expression ).getSecondExpression() );
288 break;
289 }
290 case ExpressionDefines.FUNCTION: {
291 // TODO: What about PropertyNames used here?
292 break;
293 }
294 case ExpressionDefines.EXPRESSION:
295 case ExpressionDefines.LITERAL: {
296 break;
297 }
298 }
299 }
300
301 private void registerPropertyName( PropertyName propertyName ) {
302 this.filterPropertyPaths.add( propertyName.getValue() );
303 }
304
305 /*
306 * appendJoinTableList => String[] der Tabellennamen (mehrfach vorkommende Namen nicht erlaubt)
307 * appendOuterJoins => mit SDE bei versionierten Tabellen realisierbar???
308 */
309
310 /**
311 * Appends the SQL condition from the <code>Filter</code> to the given sql statement.
312 *
313 * @param whereCondition
314 */
315 public final void appendWhereCondition( StringBuffer whereCondition ) {
316 if ( filter instanceof ComplexFilter ) {
317 appendComplexFilterAsSQL( whereCondition, (ComplexFilter) filter );
318 } else if ( filter instanceof FeatureFilter ) {
319 FeatureFilter featureFilter = (FeatureFilter) filter;
320 if ( featureFilter.getFeatureIds().size() > 0 ) {
321 appendFeatureFilterAsSQL( whereCondition, featureFilter );
322 }
323 } else {
324 // assert false : "Unexpected filter type.";
325 }
326 }
327
328 /**
329 * Appends an SQL fragment for the given object.
330 *
331 * @param query
332 * @param filter
333 */
334 protected void appendComplexFilterAsSQL( StringBuffer query, ComplexFilter filter ) {
335 appendOperationAsSQL( query, filter.getOperation() );
336 }
337
338 /**
339 * Appends an SQL fragment for the given object to the given sql statement.
340 *
341 * @param query
342 * @param operation
343 */
344 protected void appendOperationAsSQL( StringBuffer query, Operation operation ) {
345
346 switch ( OperationDefines.getTypeById( operation.getOperatorId() ) ) {
347 case OperationDefines.TYPE_SPATIAL: {
348 // handled seperately with buildSpatialFilter()
349 break;
350 }
351 case OperationDefines.TYPE_COMPARISON: {
352 appendComparisonOperationAsSQL( query, (ComparisonOperation) operation );
353 break;
354 }
355 case OperationDefines.TYPE_LOGICAL: {
356 appendLogicalOperationAsSQL( query, (LogicalOperation) operation );
357 break;
358 }
359 default: {
360 break;
361 }
362 }
363 }
364
365 /**
366 * Appends an SQL fragment for the given object to the given sql statement.
367 *
368 * @param query
369 * @param operation
370 */
371 protected void appendComparisonOperationAsSQL( StringBuffer query, ComparisonOperation operation ) {
372 switch ( operation.getOperatorId() ) {
373 case OperationDefines.PROPERTYISEQUALTO:
374 case OperationDefines.PROPERTYISLESSTHAN:
375 case OperationDefines.PROPERTYISGREATERTHAN:
376 case OperationDefines.PROPERTYISLESSTHANOREQUALTO:
377 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
378 appendPropertyIsCOMPOperationAsSQL( query, (PropertyIsCOMPOperation) operation );
379 break;
380 }
381 case OperationDefines.PROPERTYISLIKE: {
382 appendPropertyIsLikeOperationAsSQL( query, (PropertyIsLikeOperation) operation );
383 break;
384 }
385 case OperationDefines.PROPERTYISNULL: {
386 appendPropertyIsNullOperationAsSQL( query, (PropertyIsNullOperation) operation );
387 break;
388 }
389 case OperationDefines.PROPERTYISBETWEEN: {
390 appendPropertyIsBetweenOperationAsSQL( query, (PropertyIsBetweenOperation) operation );
391 break;
392 }
393 }
394 }
395
396 /**
397 * Appends an SQL fragment for the given object to the given sql statement.
398 *
399 * @param query
400 * @param operation
401 */
402 protected void appendPropertyIsCOMPOperationAsSQL( StringBuffer query, PropertyIsCOMPOperation operation ) {
403 Expression firstExpr = operation.getFirstExpression();
404 if ( !( firstExpr instanceof PropertyName ) ) {
405 throw new IllegalArgumentException( "First expression in a comparison must "
406 + "always be a 'PropertyName' element." );
407 }
408 int targetSqlType = getPropertyNameSQLType( (PropertyName) firstExpr );
409 if ( operation.isMatchCase() ) {
410 appendExpressionAsSQL( query, firstExpr, targetSqlType );
411 } else {
412 List<Expression> list = new ArrayList<Expression>();
413 list.add( firstExpr );
414 Function func = new Function( "LOWER", list );
415 appendFunctionAsSQL( query, func, targetSqlType );
416 }
417 switch ( operation.getOperatorId() ) {
418 case OperationDefines.PROPERTYISEQUALTO: {
419 query.append( " = " );
420 break;
421 }
422 case OperationDefines.PROPERTYISLESSTHAN: {
423 query.append( " < " );
424 break;
425 }
426 case OperationDefines.PROPERTYISGREATERTHAN: {
427 query.append( " > " );
428 break;
429 }
430 case OperationDefines.PROPERTYISLESSTHANOREQUALTO: {
431 query.append( " <= " );
432 break;
433 }
434 case OperationDefines.PROPERTYISGREATERTHANOREQUALTO: {
435 query.append( " >= " );
436 break;
437 }
438 }
439 if ( operation.isMatchCase() ) {
440 appendExpressionAsSQL( query, operation.getSecondExpression(), targetSqlType );
441 } else {
442 List<Expression> list = new ArrayList<Expression>();
443 list.add( operation.getSecondExpression() );
444 Function func = new Function( "LOWER", list );
445 appendFunctionAsSQL( query, func, targetSqlType );
446 }
447 }
448
449 /**
450 * Appends an SQL fragment for the given object to the given sql statement. Replacing and escape
451 * handling is based on a finite automaton with 2 states:
452 * <p>
453 * (escapeMode)
454 * <ul>
455 * <li>' is appended as \', \ is appended as \\</li>
456 * <li>every character (including the escapeChar) is simply appended</li>
457 * <li>- unset escapeMode</li>
458 * (escapeMode is false)
459 * </ul>
460 * <ul>
461 * <li>' is appended as \', \ is appended as \\</li>
462 * <li>escapeChar means: skip char, set escapeMode</li>
463 * <li>wildCard means: append %</li>
464 * <li>singleChar means: append ?</li>
465 * </ul>
466 * </p>
467 *
468 * NOTE: Currently, the method uses a quirk and appends the generated argument inline, i.e. not
469 * using query.addArgument(). This is because of a problem that occurred for example in
470 * Postgresql; the execution of the inline version is *much* faster (at least with version 8.0).
471 *
472 * @param query
473 * @param operation
474 */
475 protected void appendPropertyIsLikeOperationAsSQL( StringBuffer query, PropertyIsLikeOperation operation ) {
476
477 String literal = operation.getLiteral().getValue();
478 char escapeChar = operation.getEscapeChar();
479 char wildCard = operation.getWildCard();
480 char singleChar = operation.getSingleChar();
481 boolean escapeMode = false;
482 int length = literal.length();
483 int targetSqlType = getPropertyNameSQLType( operation.getPropertyName() );
484 if ( operation.isMatchCase() ) {
485 appendPropertyNameAsSQL( query, operation.getPropertyName() );
486 } else {
487 List<PropertyName> list = new ArrayList<PropertyName>();
488 list.add( operation.getPropertyName() );
489 Function func = new Function( "LOWER", list );
490 appendFunctionAsSQL( query, func, targetSqlType );
491 }
492 query.append( " LIKE '" );
493 StringBuffer parameter = new StringBuffer();
494 for ( int i = 0; i < length; i++ ) {
495 char c = literal.charAt( i );
496 if ( escapeMode ) {
497 // ' must (even in escapeMode) be converted to \'
498 if ( c == '\'' )
499 parameter.append( "\'" );
500 // \ must (even in escapeMode) be converted to \\
501 else if ( c == '\\' )
502 parameter.append( "\\\\" );
503 else
504 parameter.append( c );
505 escapeMode = false;
506 } else {
507 // escapeChar means: switch to escapeMode
508 if ( c == escapeChar )
509 escapeMode = true;
510 // wildCard must be converted to %
511 else if ( c == wildCard )
512 parameter.append( '%' );
513 // singleChar must be converted to ?
514 else if ( c == singleChar )
515 parameter.append( '?' );
516 // ' must be converted to \'
517 else if ( c == '\'' )
518 parameter.append( "$'$" );
519 // % must be converted to \'
520 else if ( c == '%' )
521 parameter.append( "$%$" );
522 // ? must be converted to \'
523 // else if (c == '?') sb.append("$?$");
524 // \ must (even in escapeMode) be converted to \\
525 else if ( c == '\\' )
526 parameter.append( "\\\\" );
527 else
528 parameter.append( c );
529 }
530 }
531 if ( operation.isMatchCase() ) {
532 query.append( parameter );
533 } else {
534 query.append( parameter.toString().toLowerCase() );
535 }
536 query.append( '\'' );
537 // query.addArgument( parameter.toString() );
538 }
539
540 /**
541 * Appends an SQL fragment for the given object to the given sql statement.
542 *
543 * @param query
544 * @param operation
545 */
546 protected void appendPropertyIsNullOperationAsSQL( StringBuffer query, PropertyIsNullOperation operation ) {
547 appendPropertyNameAsSQL( query, operation.getPropertyName() );
548 query.append( " IS NULL" );
549 }
550
551 /**
552 * Appends an SQL fragment for the given object to the given sql statement.
553 *
554 * @param query
555 * @param operation
556 */
557 protected void appendPropertyIsBetweenOperationAsSQL( StringBuffer query, PropertyIsBetweenOperation operation ) {
558
559 PropertyName propertyName = operation.getPropertyName();
560 int targetSqlType = getPropertyNameSQLType( propertyName );
561 appendExpressionAsSQL( query, operation.getLowerBoundary(), targetSqlType );
562 query.append( " <= " );
563 appendPropertyNameAsSQL( query, propertyName );
564 query.append( " AND " );
565 appendPropertyNameAsSQL( query, propertyName );
566 query.append( " <= " );
567 appendExpressionAsSQL( query, operation.getUpperBoundary(), targetSqlType );
568 }
569
570 /**
571 * Appends an SQL fragment for the given object to the given sql statement.
572 *
573 * @param query
574 * @param expression
575 * @param targetSqlType
576 * sql type code to be used for literals at the bottom of the expression tree
577 */
578 protected void appendExpressionAsSQL( StringBuffer query, Expression expression, int targetSqlType ) {
579 switch ( expression.getExpressionId() ) {
580 case ExpressionDefines.PROPERTYNAME: {
581 appendPropertyNameAsSQL( query, (PropertyName) expression );
582 break;
583 }
584 case ExpressionDefines.LITERAL: {
585 appendLiteralAsSQL( query, (Literal) expression, targetSqlType );
586 break;
587 }
588 case ExpressionDefines.FUNCTION: {
589 Function function = (Function) expression;
590 appendFunctionAsSQL( query, function, targetSqlType );
591 break;
592 }
593 case ExpressionDefines.ADD:
594 case ExpressionDefines.SUB:
595 case ExpressionDefines.MUL:
596 case ExpressionDefines.DIV: {
597 appendArithmeticExpressionAsSQL( query, (ArithmeticExpression) expression, targetSqlType );
598 break;
599 }
600 case ExpressionDefines.EXPRESSION:
601 default: {
602 throw new IllegalArgumentException( "Unexpected expression type: " + expression.getExpressionName() );
603 }
604 }
605 }
606
607 /**
608 * Appends an SQL fragment for the given object to the given sql statement.
609 *
610 * @param query
611 * @param literal
612 * @param targetSqlType
613 */
614 protected void appendLiteralAsSQL( StringBuffer query, Literal literal, int targetSqlType ) {
615 switch ( targetSqlType ) {
616 case java.sql.Types.DECIMAL:
617 case java.sql.Types.DOUBLE:
618 case java.sql.Types.FLOAT:
619 case java.sql.Types.INTEGER:
620 case java.sql.Types.NUMERIC:
621 case java.sql.Types.REAL:
622 case java.sql.Types.SMALLINT:
623 case java.sql.Types.TINYINT:
624 query.append( literal.getValue() );
625 break;
626 default:
627 query.append( "'" + literal.getValue() + "'" );
628 break;
629 }
630 }
631
632 /**
633 * Appends an SQL fragment for the given object to the given sql statement.
634 *
635 * @param propertyName
636 * @return
637 */
638 protected MappingField getPropertyNameMapping( PropertyName propertyName ) {
639
640 PropertyPath propertyPath = propertyName.getValue();
641 LOG.logDebug( "Looking up '" + propertyPath + "' in the query table tree." );
642 MappingField mappingField = null;
643 PropertyNode propertyNode = this.queryTableTree.getPropertyNode( propertyPath );
644 if ( propertyNode == null ) {
645 String msg = "Internal error in WhereBuilder: no PropertyNode for path '" + propertyPath
646 + "' in QueryTableTree.";
647 LOG.logError( msg );
648 throw new RuntimeException( msg );
649 } else if ( propertyNode instanceof SimplePropertyNode ) {
650 SimpleContent content = ( (MappedSimplePropertyType) ( propertyNode.getProperty() ) ).getContent();
651 if ( !( content instanceof MappingField ) ) {
652 String msg = "Virtual properties are currently ignored in WhereBuilder#appendPropertyPathAsSQL(StatementBuffer,PropertyPath).";
653 LOG.logError( msg );
654 throw new RuntimeException( msg );
655 }
656 mappingField = (MappingField) content;
657 } else if ( propertyNode instanceof GeometryPropertyNode ) {
658 mappingField = ( (MappedGeometryPropertyType) propertyNode.getProperty() ).getMappingField();
659 } else {
660 String msg = "Internal error in WhereBuilder: unhandled PropertyNode type: '"
661 + propertyNode.getClass().getName() + "'.";
662 LOG.logError( msg );
663 throw new RuntimeException( msg );
664 }
665 return mappingField;
666 }
667
668 /**
669 * Appends an SQL fragment for the given object to the given sql statement.
670 *
671 * @param query
672 * @param propertyName
673 */
674 protected void appendPropertyNameAsSQL( StringBuffer query, PropertyName propertyName ) {
675
676 MappingField mappingField = getPropertyNameMapping( propertyName );
677 // with ArcSDE because of versioning not applicable
678 // query.append( mappingField.getTable() );
679 // query.append( '.' );
680 query.append( mappingField.getField() );
681 }
682
683 /**
684 * Appends an SQL fragment for the given object to the given sql statement.
685 *
686 * @param query
687 * @param expression
688 * @param targetSqlType
689 */
690 protected void appendArithmeticExpressionAsSQL( StringBuffer query, ArithmeticExpression expression,
691 int targetSqlType ) {
692 query.append( '(' );
693 appendExpressionAsSQL( query, expression.getFirstExpression(), targetSqlType );
694 switch ( expression.getExpressionId() ) {
695 case ExpressionDefines.ADD: {
696 query.append( '+' );
697 break;
698 }
699 case ExpressionDefines.SUB: {
700 query.append( '-' );
701 break;
702 }
703 case ExpressionDefines.MUL: {
704 query.append( '*' );
705 break;
706 }
707 case ExpressionDefines.DIV: {
708 query.append( '/' );
709 break;
710 }
711 }
712 appendExpressionAsSQL( query, expression.getSecondExpression(), targetSqlType );
713 query.append( ')' );
714 }
715
716 /**
717 * Appends an SQL fragment for the given object to the given sql statement.
718 *
719 * @param query
720 * @param function
721 * @param targetSqlType
722 */
723 protected void appendFunctionAsSQL( StringBuffer query, Function function, int targetSqlType ) {
724 query.append( function.getName() );
725 query.append( " (" );
726 List list = function.getArguments();
727 for ( int i = 0; i < list.size(); i++ ) {
728 Expression expression = (Expression) list.get( i );
729 appendExpressionAsSQL( query, expression, targetSqlType );
730 if ( i != list.size() - 1 )
731 query.append( ", " );
732 }
733 query.append( ")" );
734 }
735
736 /**
737 * Appends an SQL fragment for the given object to the given sql statement.
738 *
739 * @param query
740 * @param operation
741 */
742 protected void appendLogicalOperationAsSQL( StringBuffer query, LogicalOperation operation ) {
743 List argumentList = operation.getArguments();
744 switch ( operation.getOperatorId() ) {
745 case OperationDefines.AND: {
746 for ( int i = 0; i < argumentList.size(); i++ ) {
747 Operation argument = (Operation) argumentList.get( i );
748 query.append( '(' );
749 appendOperationAsSQL( query, argument );
750 query.append( ')' );
751 if ( i != argumentList.size() - 1 )
752 query.append( " AND " );
753 }
754 break;
755 }
756 case OperationDefines.OR: {
757 for ( int i = 0; i < argumentList.size(); i++ ) {
758 Operation argument = (Operation) argumentList.get( i );
759 query.append( '(' );
760 appendOperationAsSQL( query, argument );
761 query.append( ')' );
762 if ( i != argumentList.size() - 1 )
763 query.append( " OR " );
764 }
765 break;
766 }
767 case OperationDefines.NOT: {
768 Operation argument = (Operation) argumentList.get( 0 );
769 query.append( "NOT (" );
770 appendOperationAsSQL( query, argument );
771 query.append( ')' );
772 break;
773 }
774 }
775 }
776
777 /**
778 * Appends an SQL fragment for the given object to the given sql statement.
779 *
780 * TODO Handle compound primary keys correctly.
781 *
782 * @param query
783 * @param filter
784 */
785 protected void appendFeatureFilterAsSQL( StringBuffer query, FeatureFilter filter ) {
786 ArrayList list = filter.getFeatureIds();
787 MappingField mapping = rootFeatureType.getGMLId().getIdFields()[0];
788 String quote = "";
789 switch ( mapping.getType() ) {
790 case java.sql.Types.DECIMAL:
791 case java.sql.Types.DOUBLE:
792 case java.sql.Types.FLOAT:
793 case java.sql.Types.INTEGER:
794 case java.sql.Types.NUMERIC:
795 case java.sql.Types.REAL:
796 case java.sql.Types.SMALLINT:
797 case java.sql.Types.TINYINT:
798 break;
799 default:
800 quote = "'";
801 break;
802 }
803 query.append( ' ' );
804 query.append( mapping.getField() );
805 try {
806 for ( int i = 0; i < list.size(); i++ ) {
807 if ( 0 == i )
808 query.append( " IN (" + quote );
809 else
810 query.append( quote + "," + quote );
811 String fid = ( (org.deegree.model.filterencoding.FeatureId) list.get( i ) ).getValue();
812 Object fidValue = org.deegree.io.datastore.FeatureId.removeFIDPrefix( fid, rootFeatureType.getGMLId() );
813 query.append( fidValue.toString() );
814 }
815 } catch ( Exception e ) {
816 LOG.logError( "Error converting feature id", e );
817 }
818 query.append( quote + ")" );
819 }
820
821 /**
822 * Generates an SQL-fragment for the given object.
823 *
824 * @param filter
825 * @param layers
826 * @return
827 *
828 * @throws DatastoreException
829 */
830 protected SeFilter[] buildSpatialFilter( ComplexFilter filter, List layers )
831 throws DatastoreException {
832
833 SpatialOperation[] spatialOps = FilterTools.extractSpatialFilter( filter );
834 if ( null == spatialOps || 0 == spatialOps.length )
835 return null;
836
837 SeFilter[] spatialFilter = new SeFilter[spatialOps.length];
838
839 for ( int i = 0; i < spatialOps.length; i++ ) {
840 try {
841 MappingField mappingField = getPropertyNameMapping( spatialOps[i].getPropertyName() );
842 String filterTable = mappingField.getTable();
843 String filterColumn = mappingField.getField();
844
845 SeCoordinateReference coordRef = null;
846 String[] splitted = filterTable.toUpperCase().split( "\\." );
847 String tmp = splitted[splitted.length - 1];
848 for ( int k = 0; k < layers.size(); k++ ) {
849 SeLayer layer = (SeLayer) layers.get( k );
850 splitted = layer.getName().toUpperCase().split( "\\." );
851 if ( splitted[splitted.length - 1].equals( tmp ) ) {
852 coordRef = layer.getCoordRef();
853 break;
854 }
855 }
856 if ( null == coordRef ) {
857 coordRef = new SeCoordinateReference();
858 }
859
860 int filterMethod = -1;
861 boolean filterTruth = true;
862 switch ( spatialOps[i].getOperatorId() ) {
863 case OperationDefines.CROSSES: {
864 filterMethod = SeFilter.METHOD_LCROSS;
865 break;
866 }
867 case OperationDefines.EQUALS: {
868 filterMethod = SeFilter.METHOD_IDENTICAL;
869 break;
870 }
871 case OperationDefines.WITHIN: {
872 filterMethod = SeFilter.METHOD_SC_NO_ET;
873 break;
874 }
875 case OperationDefines.OVERLAPS: {
876 filterMethod = SeFilter.METHOD_ENVP;
877 break;
878 }
879 case OperationDefines.TOUCHES: {
880 filterMethod = SeFilter.METHOD_ET_OR_AI;
881 break;
882 }
883 case OperationDefines.DISJOINT: {
884 filterMethod = SeFilter.METHOD_SC_NO_ET;
885 filterTruth = false;
886 break;
887 }
888 case OperationDefines.INTERSECTS: {
889 filterMethod = SeFilter.METHOD_AI;
890 break;
891 }
892 case OperationDefines.CONTAINS: {
893 filterMethod = SeFilter.METHOD_AI_OR_ET;
894 break;
895 }
896 case OperationDefines.BBOX: {
897 filterMethod = SeFilter.METHOD_ENVP;
898 break;
899 }
900 case OperationDefines.DWITHIN:
901 case OperationDefines.BEYOND:
902 default: {
903 continue;
904 }
905 }
906 SeShape filterGeom = SDEAdapter.export( spatialOps[i].getGeometry(), coordRef );
907 spatialFilter[i] = new SeShapeFilter( filterTable, filterColumn, filterGeom, filterMethod, filterTruth );
908 } catch ( Exception e ) {
909 e.printStackTrace();
910 throw new DatastoreException( "Error creating spatial filter", e );
911 }
912 }
913 return spatialFilter;
914 }
915 }