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