    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_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    }