    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/io/datastore/sql/generic/GenericSQLWhereBuilder.java $
    002    /*----------------------------------------------------------------------------
    003     This file is part of deegree, http://deegree.org/
    004     Copyright (C) 2001-2009 by:
    005     Department of Geography, University of Bonn
    006     and
    007     lat/lon GmbH
    008
    009     This library is free software; you can redistribute it and/or modify it under
    010     the terms of the GNU Lesser General Public License as published by the Free
    011     Software Foundation; either version 2.1 of the License, or (at your option)
    012     any later version.
    013     This library is distributed in the hope that it will be useful, but WITHOUT
    014     ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
    015     FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
    016     details.
    017     You should have received a copy of the GNU Lesser General Public License
    018     along with this library; if not, write to the Free Software Foundation, Inc.,
    019     59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
    020
    021     Contact information:
    022
    023     lat/lon GmbH
    024     Aennchenstr. 19, 53177 Bonn
    025     Germany
    026     http://lat-lon.de/
    027
    028     Department of Geography, University of Bonn
    029     Prof. Dr. Klaus Greve
    030     Postfach 1147, 53001 Bonn
    031     Germany
    032     http://www.geographie.uni-bonn.de/deegree/
    033
    034     e-mail: info@deegree.org
    035     ----------------------------------------------------------------------------*/
    036    package org.deegree.io.datastore.sql.generic;
    037
    038    import java.sql.Types;
    039    import java.util.ArrayList;
    040    import java.util.List;
    041
    042    import org.deegree.framework.log.ILogger;
    043    import org.deegree.framework.log.LoggerFactory;
    044    import org.deegree.i18n.Messages;
    045    import org.deegree.io.JDBCConnection;
    046    import org.deegree.io.datastore.DatastoreException;
    047    import org.deegree.io.datastore.schema.MappedFeatureType;
    048    import org.deegree.io.datastore.schema.content.MappingField;
    049    import org.deegree.io.datastore.sql.StatementBuffer;
    050    import org.deegree.io.datastore.sql.TableAliasGenerator;
    051    import org.deegree.io.datastore.sql.VirtualContentProvider;
    052    import org.deegree.io.datastore.sql.wherebuilder.SpecialCharString;
    053    import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
    054    import org.deegree.io.quadtree.DBQuadtree;
    055    import org.deegree.io.quadtree.DBQuadtreeManager;
    056    import org.deegree.io.quadtree.IndexException;
    057    import org.deegree.io.quadtree.Quadtree;
    058    import org.deegree.model.filterencoding.DBFunction;
    059    import org.deegree.model.filterencoding.Expression;
    060    import org.deegree.model.filterencoding.Filter;
    061    import org.deegree.model.filterencoding.FilterEvaluationException;
    062    import org.deegree.model.filterencoding.Function;
    063    import org.deegree.model.filterencoding.OperationDefines;
    064    import org.deegree.model.filterencoding.PropertyIsLikeOperation;
    065    import org.deegree.model.filterencoding.SpatialOperation;
    066    import org.deegree.model.spatialschema.Envelope;
    067    import org.deegree.ogcbase.SortProperty;
    068
    069    /**
    070     * {@link WhereBuilder} implementation for the {@link GenericSQLDatastore}.
    071     * <p>
    072     * Uses the {@link Quadtree} to speed up BBOX queries.
    073     *
    074     * @see org.deegree.io.quadtree
    075     *
    076     * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
    077     * @author last edited by: $Author: mschneider $
    078     *
    079     * @version $Revision: 21848 $, $Date: 2010-01-11 14:00:48 +0100 (Mo, 11. Jan 2010) $
    080     */
    081    class GenericSQLWhereBuilder extends WhereBuilder {
    082
    083        private static final ILogger LOG = LoggerFactory.getLogger( GenericSQLWhereBuilder.class );
    084
    085        private final static String SQL_TRUE = "1=1";
    086
    087        private final static String SQL_FALSE = "1!=1";
    088
    089        private JDBCConnection jdbc;
    090
    091        /**
    092         * Creates a new instance of <code>GenericSQLWhereBuilder</code> from the given parameters.
    093         *
    094         * @param rootFts
    095         *            selected feature types, more than one type means that the types are joined
    096         * @param aliases
    097         *            aliases for the feature types, may be null (must have same length as rootFts otherwise)
    098         * @param filter
    099         *            filter that restricts the matched features
    100         * @param sortProperties
    101         *            sort criteria for the result, may be null or empty
    102         * @param aliasGenerator
    103         *            used to generate unique table aliases
    104         * @param vcProvider
    105         * @param jdbc
    106         * @throws DatastoreException
    107         */
    108        public GenericSQLWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
    109                                       SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
    110                                       VirtualContentProvider vcProvider, JDBCConnection jdbc ) throws DatastoreException {
    111            super( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
    112            this.jdbc = jdbc;
    113        }
    114
    115        /**
    116         * Appends an SQL fragment for the given object to the given sql statement.
    117         *
    118         * NOTE: Currently, the method uses a quirk and appends the generated argument inline, i.e. not using
    119         * query.addArgument(). Works around an SQL error on HSQLDB that would occur otherwise.
    120         *
    121         * @param query
    122         * @param operation
    123         * @throws FilterEvaluationException
    124         */
    125        @Override
    126        protected void appendPropertyIsLikeOperationAsSQL( StatementBuffer query, PropertyIsLikeOperation operation )
    127                                throws FilterEvaluationException {
    128
    129            String literal = operation.getLiteral().getValue();
    130            String escape = "" + operation.getEscapeChar();
    131            String wildCard = "" + operation.getWildCard();
    132            String singleChar = "" + operation.getSingleChar();
    133
    134            SpecialCharString specialString = new SpecialCharString( literal, wildCard, singleChar, escape );
    135            String sqlEncoded = specialString.toSQLStyle( !operation.isMatchCase() );
    136
    137            int targetSqlType = getPropertyNameSQLType( operation.getPropertyName() );
    138
    139            // if isMatchCase == false surround first argument with LOWER (...) and convert characters
    140            // in second argument to lower case
    141            if ( operation.isMatchCase() ) {
    142                appendPropertyNameAsSQL( query, operation.getPropertyName() );
    143            } else {
    144                List<Expression> list = new ArrayList<Expression>();
    145                list.add( operation.getPropertyName() );
    146                Function func = new DBFunction( getFunctionName( "LOWER" ), list );
    147                appendFunctionAsSQL( query, func, targetSqlType );
    148            }
    149
    150            query.append( " LIKE '" );
    151            query.append( sqlEncoded );
    152            query.append( "'" );
    153        }
    154
    155
    156        /**
    157         * Generates an SQL-fragment for the given object.
    158         *
    159         * TODO: Implement BBOX faster using explicit B0X-constructor
    160         *
    161         * @throws DatastoreException
    162         */
    163        @Override
    164        protected void appendSpatialOperationAsSQL( StatementBuffer query, SpatialOperation operation )
    165                                throws DatastoreException {
    166
    167            switch ( operation.getOperatorId() ) {
    168            case OperationDefines.BBOX: {
    169                appendBBOXOperationAsSQL( query, operation );
    170                break;
    171            }
    172            case OperationDefines.DISJOINT:
    173            case OperationDefines.CROSSES:
    174            case OperationDefines.EQUALS:
    175            case OperationDefines.WITHIN:
    176            case OperationDefines.OVERLAPS:
    177            case OperationDefines.TOUCHES:
    178            case OperationDefines.CONTAINS:
    179            case OperationDefines.INTERSECTS:
    180            case OperationDefines.DWITHIN:
    181            case OperationDefines.BEYOND: {
    182                query.append( SQL_TRUE );
    183                break;
    184            }
    185            default: {
    186                String msg = Messages.getMessage( "DATASTORE_UNKNOWN_SPATIAL_OPERATOR",
    187                                                  OperationDefines.getNameById( operation.getOperatorId() ) );
    188                throw new DatastoreException( msg );
    189            }
    190            }
    191        }
    192
    193        /**
    194         * Appends a constraint (FEATURE_ID IN (...)) to the given {@link StatementBuffer} which is generated by using the
    195         * associated {@link DBQuadtree} index.
    196         *
    197         * @param query
    198         * @param operation
    199         * @throws DatastoreException
    200         */
    201        private void appendBBOXOperationAsSQL( StatementBuffer query, SpatialOperation operation )
    202                                throws DatastoreException {
    203
    204            Envelope env = operation.getGeometry().getEnvelope();
    205
    206            DBQuadtreeManager<?> qtm = null;
    207            try {
    208                qtm = new DBQuadtreeManager<Object>( jdbc, this.rootFts[0].getTable(), "geometry", null, Integer.MIN_VALUE );
    209                Object type = qtm.determineQuattreeType();
    210                int dataType = Types.VARCHAR;
    211                if ( type instanceof Integer ) {
    212                    LOG.logDebug( "The elements of the quadtree are of type Integer." );
    213                    qtm = new DBQuadtreeManager<Integer>( jdbc, this.rootFts[0].getTable(), "geometry", null, Types.INTEGER );
    214                    dataType = Types.INTEGER;
    215                } else if ( type instanceof String ) {
    216                    LOG.logDebug( "The elements of the quadtree are of type String." );
    217                    qtm = new DBQuadtreeManager<String>( jdbc, this.rootFts[0].getTable(), "geometry", null, Types.INTEGER );
    218                }
    219
    220                Envelope qtEnv = qtm.getQuadtree().getRootBoundingBox();
    221                if ( qtEnv.intersects( env ) ) {
    222                    // check if features within this bbox are available
    223                    // if not -> return an empty list
    224                    List<?> ids = qtm.getQuadtree().query( env );
    225                    if ( ids.size() > 0 ) {
    226
    227                        MappingField[] idFields = this.rootFts[0].getGMLId().getIdFields();
    228                        if ( idFields.length > 1 ) {
    229                            String msg = "GenericSQLDatastore cannot handle composite feature ids.";
    230                            throw new DatastoreException( msg );
    231                        }
    232
    233                        query.append( getRootTableAlias( 0 ) + '.' + idFields[0].getField() + " IN (" );
    234                        for ( int i = 0; i < ids.size() - 1; i++ ) {
    235                            query.append( "?," );
    236                            if ( dataType == Types.VARCHAR ) {
    237                                query.addArgument( ( "" + ids.get( i ) ).trim(), Types.VARCHAR );
    238                            } else {
    239                                query.addArgument( ids.get( i ), Types.INTEGER );
    240                            }
    241                        }
    242                        if ( dataType == Types.VARCHAR ) {
    243                            query.addArgument( ( "" + ids.get( ids.size() - 1 ) ).trim(), Types.VARCHAR );
    244                        } else {
    245                            query.addArgument( ids.get( ids.size() - 1 ), Types.INTEGER );
    246                        }
    247                        query.append( "?)" );
    248                    } else {
    249                        query.append( SQL_FALSE );
    250                    }
    251                } else {
    252                    query.append( SQL_FALSE );
    253                }
    254            } catch ( IndexException e ) {
    255                LOG.logError( e.getMessage(), e );
    256                throw new DatastoreException(
    257                                              "Could not append bbox operation as sql into the Quadtree: " + e.getMessage(),
    258                                              e );
    259            } finally {
    260                if ( qtm != null ) {
    261                    qtm.release();
    262                }
    263            }
    264        }
    265    }