001    //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_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    }