    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/io/datastore/sql/generic/GenericSQLDatastore.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.io.BufferedReader;
    039    import java.io.InputStream;
    040    import java.io.InputStreamReader;
    041    import java.io.Reader;
    042    import java.io.StringReader;
    043    import java.sql.Connection;
    044    import java.sql.SQLException;
    045
    046    import org.deegree.framework.log.ILogger;
    047    import org.deegree.framework.log.LoggerFactory;
    048    import org.deegree.framework.util.StringTools;
    049    import org.deegree.i18n.Messages;
    050    import org.deegree.io.JDBCConnection;
    051    import org.deegree.io.datastore.Datastore;
    052    import org.deegree.io.datastore.DatastoreException;
    053    import org.deegree.io.datastore.schema.MappedFeatureType;
    054    import org.deegree.io.datastore.sql.AbstractSQLDatastore;
    055    import org.deegree.io.datastore.sql.QueryHandler;
    056    import org.deegree.io.datastore.sql.SQLDatastoreConfiguration;
    057    import org.deegree.io.datastore.sql.TableAliasGenerator;
    058    import org.deegree.io.datastore.sql.VirtualContentProvider;
    059    import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
    060    import org.deegree.model.crs.CRSFactory;
    061    import org.deegree.model.crs.CoordinateSystem;
    062    import org.deegree.model.feature.Feature;
    063    import org.deegree.model.feature.FeatureCollection;
    064    import org.deegree.model.filterencoding.Filter;
    065    import org.deegree.model.filterencoding.FilterEvaluationException;
    066    import org.deegree.model.spatialschema.GMLGeometryAdapter;
    067    import org.deegree.model.spatialschema.Geometry;
    068    import org.deegree.model.spatialschema.GeometryException;
    069    import org.deegree.model.spatialschema.GeometryImpl;
    070    import org.deegree.ogcbase.SortProperty;
    071    import org.deegree.ogcwebservices.wfs.operation.Query;
    072
    073    /**
    074     * {@link Datastore} implementation for any SQL database that can be accessed through a jdbc connection (even the
    075     * odbc-jdbc bridge is supported) and that supports the storing of BLOBs.
    076     * <p>
    077     * The spatial information is assumed to be stored in a BLOB field as a serialized deegree geometry. It also will be
    078     * assumed that a spatial index exists and that it has been created using deegree's quadtree api.
    079     *
    080     * @see org.deegree.io.quadtree
    081     *
    082     * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
    083     * @author last edited by: $Author: mschneider $
    084     *
    085     * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $
    086     */
    087    public class GenericSQLDatastore extends AbstractSQLDatastore {
    088
    089        private static final ILogger LOG = LoggerFactory.getLogger( GenericSQLDatastore.class );
    090
    091        @Override
    092        public WhereBuilder getWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
    093                                             SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
    094                                             VirtualContentProvider vcProvider )
    095                                throws DatastoreException {
    096            JDBCConnection jdbc = ( (SQLDatastoreConfiguration) getConfiguration() ).getJDBCConnection();
    097            return new GenericSQLWhereBuilder( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider, jdbc );
    098        }
    099
    100        @Override
    101        protected FeatureCollection performQuery( Query query, MappedFeatureType[] rootFts, Connection conn )
    102                                throws DatastoreException {
    103
    104            Query transformedQuery = transformQuery( query );
    105
    106            FeatureCollection result = null;
    107            try {
    108                QueryHandler queryHandler = new QueryHandler( this, new TableAliasGenerator(), conn, rootFts, query );
    109                result = queryHandler.performQuery();
    110            } catch ( SQLException e ) {
    111                String msg = "SQL error while performing query: " + e.getMessage();
    112                LOG.logError( msg, e );
    113                throw new DatastoreException( msg, e );
    114            } catch ( Exception e ) {
    115                LOG.logError( e.getMessage(), e );
    116                throw new DatastoreException( e );
    117            }
    118
    119            if ( transformedQuery.getFilter() != null ) {
    120                try {
    121                    LOG.logDebug( "Features (before refinement): " + result.size() );
    122                    result = filterCollection( result, transformedQuery.getFilter() );
    123                    LOG.logDebug( "Features (after refinement): " + result.size() );
    124                } catch ( Exception e ) {
    125                    LOG.logError( e.getMessage(), e );
    126                    throw new DatastoreException( e.getMessage(), e );
    127                }
    128            }
    129
    130            result = transformResult( result, transformedQuery.getSrsName() );
    131            return result;
    132        }
    133
    134        /**
    135         * Filters the feature collection using the given filter.
    136         * <p>
    137         * This is required because spatial filtering performed in the {@link GenericSQLWhereBuilder} just considers the
    138         * BBOX and non-spatial filter conditions.
    139         *
    140         * TODO remove BBOX + non-spatial conditions from the filter to speed up evaluation
    141         *
    142         * @param fc
    143         * @param filter
    144         * @return filtered feature collection
    145         */
    146        private FeatureCollection filterCollection( FeatureCollection fc, Filter filter )
    147                                throws FilterEvaluationException {
    148            for ( int i = fc.size() - 1; i >= 0; i-- ) {
    149                Feature feat = fc.getFeature( i );
    150                if ( !filter.evaluate( feat ) ) {
    151                    fc.remove( i );
    152                }
    153            }
    154            return fc;
    155        }
    156
    157        @Override
    158        public Geometry convertDBToDeegreeGeometry( Object value, CoordinateSystem targetCS, Connection conn )
    159                                throws SQLException {
    160
    161            Geometry geometry = null;
    162            if ( value != null ) {
    163                try {
    164                    if ( targetCS == null ) {
    165                        targetCS = CRSFactory.create( "EPSG:4326" );
    166                    }
    167                    if ( value instanceof String ) {
    168                        geometry = GMLGeometryAdapter.wrap( (String) value, null );
    169                    } else if ( value instanceof InputStream ) {
    170                        StringBuffer sb = new StringBuffer( 10000 );
    171                        BufferedReader br = new BufferedReader( new InputStreamReader( (InputStream) value ) );
    172                        String line = null;
    173                        while ( ( line = br.readLine() ) != null ) {
    174                            sb.append( line );
    175                        }
    176                        geometry = GMLGeometryAdapter.wrap( sb.toString(), null );
    177                    } else if ( value instanceof Reader ) {
    178                        StringBuffer sb = new StringBuffer( 10000 );
    179                        BufferedReader br = new BufferedReader( (Reader) value );
    180                        String line = null;
    181                        while ( ( line = br.readLine() ) != null ) {
    182                            sb.append( line );
    183                        }
    184                        geometry = GMLGeometryAdapter.wrap( sb.toString(), null );
    185                    } else {
    186                        geometry = GMLGeometryAdapter.wrap( new String( (byte[]) value ), null );
    187                    }
    188                    ( (GeometryImpl) geometry ).setCoordinateSystem( targetCS );
    189                } catch ( Exception e ) {
    190                    String msg = Messages.getMessage( "DATASTORE_GENERICSQL_GEOM_READ_ERROR" );
    191                    LOG.logError( msg, e );
    192                    throw new SQLException( msg + e.getMessage() );
    193                }
    194            }
    195            return geometry;
    196        }
    197
    198        @Override
    199        public Object convertDeegreeToDBGeometry( Geometry geometry, int nativeSRSCode, Connection conn )
    200                                throws DatastoreException {
    201            String sqlObject = null;
    202            try {
    203                sqlObject = GMLGeometryAdapter.export( geometry ).toString();
    204                sqlObject = StringTools.replace( sqlObject, ">", " xmlns:gml=\"http://www.opengis.net/gml\">", false );
    205            } catch ( GeometryException e ) {
    206                LOG.logError( e.getMessage(), e );
    207                throw new DatastoreException( e.getMessage(), e );
    208            }
    209            JDBCConnection jdbc = ( (SQLDatastoreConfiguration) getConfiguration() ).getJDBCConnection();
    210            Object result = null;
    211            // concrete from depends on backend. At the moment special mapping just is defined
    212            // for HSQLDB, Postgres und INGRES. Default will work for MS SQLSERVER
    213            if ( jdbc.getDriver().toLowerCase().indexOf( "ingres" ) > -1
    214                 || jdbc.getDriver().equalsIgnoreCase( "ca.edbc.jdbc.EdbcDriver" ) ) {
    215                result = new StringReader( sqlObject );
    216            } else if ( jdbc.getDriver().toLowerCase().indexOf( "hsqldb" ) > -1
    217                        || jdbc.getDriver().toLowerCase().indexOf( "postgres" ) > -1 ) {
    218                // only postgres is able to handle large strings as they are
    219                result = sqlObject;
    220            } else {
    221                result = sqlObject.getBytes();
    222            }
    223            return result;
    224        }
    225
    226        @Override
    227        protected GenericSQLTransaction createTransaction()
    228                                throws DatastoreException {
    229
    230            return new GenericSQLTransaction( this, new TableAliasGenerator(), acquireConnection() );
    231        }
    232    }