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