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