001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_testing/src/org/deegree/io/datastore/sql/generic/GenericSQLTransaction.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
037 package org.deegree.io.datastore.sql.generic;
038
039 import java.sql.Connection;
040 import java.sql.ResultSet;
041 import java.sql.ResultSetMetaData;
042 import java.sql.SQLException;
043 import java.sql.Statement;
044 import java.util.HashMap;
045 import java.util.List;
046 import java.util.Map;
047
048 import org.deegree.framework.log.ILogger;
049 import org.deegree.framework.log.LoggerFactory;
050 import org.deegree.i18n.Messages;
051 import org.deegree.io.DBConnectionPool;
052 import org.deegree.io.DBPoolException;
053 import org.deegree.io.JDBCConnection;
054 import org.deegree.io.datastore.DatastoreException;
055 import org.deegree.io.datastore.FeatureId;
056 import org.deegree.io.datastore.schema.MappedFeatureType;
057 import org.deegree.io.datastore.sql.AbstractSQLDatastore;
058 import org.deegree.io.datastore.sql.SQLDatastoreConfiguration;
059 import org.deegree.io.datastore.sql.TableAliasGenerator;
060 import org.deegree.io.datastore.sql.transaction.SQLTransaction;
061 import org.deegree.io.quadtree.DBQuadtree;
062 import org.deegree.io.quadtree.IndexException;
063 import org.deegree.model.feature.Feature;
064 import org.deegree.model.feature.FeatureProperty;
065 import org.deegree.model.filterencoding.Filter;
066 import org.deegree.model.spatialschema.Envelope;
067 import org.deegree.model.spatialschema.GeometryException;
068 import org.deegree.ogcbase.PropertyPath;
069
070 /**
071 * Special transaction implementation for the {@link GenericSQLDatastore}.
072 * <p>
073 * Please note that the quadtree management isn't finished yet.
074 * </p><p>
075 * What should work:
076 * <ul>
077 * <li>inserting of new features</li>
078 * <li>deleting of features</li>
079 * <li>updating features (unless the geometry property is changed)</li>
080 * </ul>
081 * </p><p>
082 * What definitely won't work:
083 * <ul>
084 * <li>updating geometry properties will most probably break the index</li>
085 * </ul>
086 * </p>
087 *
088 * @see org.deegree.io.quadtree
089 *
090 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
091 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider</a>
092 * @author <a href="mailto:bezema@lat-lon.de">Rutger Bezema</a>
093 * @author last edited by: $Author: awietschke $
094 *
095 * @version $Revision: 18836 $, $Date: 2009-07-31 12:28:01 +0200 (Fr, 31 Jul 2009) $
096 */
097 public class GenericSQLTransaction extends SQLTransaction {
098
099 private static final ILogger LOG = LoggerFactory.getLogger( GenericSQLTransaction.class );
100
101 private String indexName;
102
103 /**
104 * saves versioninfos about the different quadtrees.
105 */
106 public static HashMap<String, String> quadTreeVersionInfo = new HashMap<String, String>();
107
108 /**
109 *
110 * @param ds
111 * @param aliasGenerator
112 * @param conn
113 * @throws DatastoreException
114 */
115 GenericSQLTransaction( AbstractSQLDatastore ds, TableAliasGenerator aliasGenerator, Connection conn )
116 throws DatastoreException {
117 super( ds, aliasGenerator, conn );
118 }
119
120 @Override
121 public int performDelete( MappedFeatureType mappedFeatureType, Filter filter, String lockId )
122 throws DatastoreException {
123 assert !mappedFeatureType.isAbstract();
124
125 if ( !mappedFeatureType.isDeletable() ) {
126 String msg = Messages.getMessage( "DATASTORE_FT_NOT_DELETABLE", mappedFeatureType.getName() );
127 throw new DatastoreException( msg );
128 }
129 // QualifiedName qName = mappedFeatureType.getName();
130 // if ( qName == null ) {
131 // LOG.logDebug( "The mappedfeauterType's qname is null, this cannot be!" );
132 // }
133 // Query q = Query.create( mappedFeatureType.getName(), filter );
134 // if ( q == null ) {
135 // LOG.logDebug( "The query created from the qname and filter is null, this cannot be!" );
136 // }
137 // FeatureCollection fc = null;
138 // try {
139 // fc = getDatastore().performQuery( q, new MappedFeatureType[] { mappedFeatureType } );
140 // } catch ( UnknownCRSException e ) {
141 // throw new DatastoreException( e );
142 // }
143
144 List<FeatureId> featureList = determineAffectedAndModifiableFIDs( mappedFeatureType, filter, lockId );
145
146 SQLDatastoreConfiguration config = (SQLDatastoreConfiguration) getDatastore().getConfiguration();
147 JDBCConnection jdbc = config.getJDBCConnection();
148 String table = mappedFeatureType.getTable();
149 String version = getQTVersion( table, jdbc );
150 LOG.logDebug( "Found quadtree version: " + version );
151 try {
152 for ( FeatureId fID : featureList ) {
153 int fk_index = loadIndexMetadata( jdbc, table );
154 Object rawId = FeatureId.removeFIDPrefix( fID.getAsString(), mappedFeatureType.getGMLId() );
155 if ( rawId instanceof Integer ) {
156 DBQuadtree<Integer> qt = null;
157 try {
158 qt = new DBQuadtree<Integer>( fk_index, indexName, jdbc, version );
159 qt.deleteItem( (Integer) rawId );
160 } finally {
161 if ( qt != null ) {
162 qt.releaseConnection();
163 }
164 }
165 } else if ( rawId instanceof String ) {
166 DBQuadtree<String> qt = null;
167 try {
168 qt = new DBQuadtree<String>( fk_index, indexName, jdbc, version );
169 qt.deleteItem( (String) rawId );
170 } finally {
171 if ( qt != null ) {
172 qt.releaseConnection();
173 }
174 }
175 }
176 }
177 } catch ( IndexException e ) {
178 LOG.logError( e.getMessage(), e );
179 throw new DatastoreException( e.getMessage(), e );
180 }
181
182 super.performDelete( mappedFeatureType, filter, lockId );
183
184 return featureList.size();
185 }
186
187 @Override
188 public List<FeatureId> performInsert( List<Feature> features )
189 throws DatastoreException {
190 List<FeatureId> fids = super.performInsert( features );
191
192 // update index
193 try {
194 SQLDatastoreConfiguration config = (SQLDatastoreConfiguration) getDatastore().getConfiguration();
195 JDBCConnection jdbc = config.getJDBCConnection();
196 for ( int i = 0; i < features.size(); i++ ) {
197 Envelope env = features.get( i ).getBoundedBy();
198 if ( env != null ) {
199 MappedFeatureType mft = datastore.getFeatureType( features.get( i ).getFeatureType().getName() );
200
201 String table = mft.getTable();
202 String version = getQTVersion( table, jdbc );
203 int fk_index = loadIndexMetadata( jdbc, table );
204 Object rawId = FeatureId.removeFIDPrefix( fids.get( i ).getAsString(), mft.getGMLId() );
205 if ( rawId instanceof String ) {
206 DBQuadtree<String> qt = null;
207 try {
208 qt = new DBQuadtree<String>( fk_index, indexName, jdbc, version );
209 qt.insert( (String) rawId, env );
210 } finally {
211 if ( qt != null ) {
212 qt.releaseConnection();
213 }
214 }
215 } else if ( rawId instanceof Integer ) {
216 DBQuadtree<Integer> qt = null;
217 try {
218 qt = new DBQuadtree<Integer>( fk_index, indexName, jdbc, version );
219 qt.insert( (Integer) rawId, env );
220 } finally {
221 if ( qt != null ) {
222 qt.releaseConnection();
223 }
224 }
225
226 }
227 }
228 }
229 } catch ( IndexException e ) {
230 LOG.logError( e.getMessage(), e );
231 throw new DatastoreException( e.getMessage(), e );
232 } catch ( GeometryException e ) {
233 LOG.logError( e.getMessage(), e );
234 throw new DatastoreException( e.getMessage(), e );
235 }
236 return fids;
237 }
238
239 /**
240 * @param table
241 * to open a quadtree for.
242 * @return the version of the quadtree used.
243 */
244 private String getQTVersion( String table, JDBCConnection jdbc ) {
245 String version = "1.0.0";
246 if ( quadTreeVersionInfo.containsKey( table ) && quadTreeVersionInfo.get( table ) != null ) {
247 LOG.logDebug( "Retrieved the quatdree version info for table: " + table + " from cache." );
248 version = quadTreeVersionInfo.get( table );
249 } else {
250 Connection con = null;
251 DBConnectionPool pool = null;
252 Statement stmt = null;
253 ResultSet rs = null;
254 pool = DBConnectionPool.getInstance();
255 StringBuilder sb = new StringBuilder( 400 );
256 sb.append( "SELECT fk_indextree FROM tab_deegree_idx WHERE " );
257 sb.append( "column_name = 'geometry' AND " );
258 sb.append( "table_name = '" ).append( table.toLowerCase() ).append( "'" );
259
260 LOG.logDebug( "Get Index Metadata sql statement:\n", sb );
261 try {
262 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
263 stmt = con.createStatement();
264 rs = stmt.executeQuery( sb.toString() );
265 String tableID = null;
266 if ( rs.next() ) {
267 tableID = rs.getString( 1 );
268 }
269 if ( tableID != null ) {
270 sb = new StringBuilder( 400 );
271 sb.append( "SELECT * FROM tab_quadtree WHERE " );
272 sb.append( "fk_root = '" ).append( tableID.trim() ).append( "'" );
273 if ( rs != null ) {
274 rs.close();
275 }
276 if ( stmt != null ) {
277 stmt.close();
278 }
279 stmt = con.createStatement();
280 rs = stmt.executeQuery( sb.toString() );
281 if ( rs.next() ) {
282 boolean hasVersion = false;
283 ResultSetMetaData md = rs.getMetaData();
284 int numberOfColumns = md.getColumnCount();
285 LOG.logDebug ("Column count: " + numberOfColumns);
286 for ( int i = 1; i <= numberOfColumns && !hasVersion; i++ ) {
287 String tmp = md.getColumnName( i );
288 LOG.logDebug( "Found columnname: " + tmp );
289 if ( tmp != null ) {
290 if ( "version".equalsIgnoreCase( tmp.trim() ) ) {
291 hasVersion = true;
292 version = rs.getString( i );
293 LOG.logDebug( "Found a version column, setting version to: " + rs.getString( i ) );
294 }
295 }
296 }
297 if ( !hasVersion ) {
298 try {
299 LOG.logInfo( "Found no Version Column in the TAB_QUADTREE table, assuming version 1.0.0, and adding the version column." );
300 if ( rs != null ) {
301 rs.close();
302 }
303 if ( stmt != null ) {
304 stmt.close();
305 }
306 stmt = con.createStatement();
307 rs = stmt.executeQuery( "ALTER TABLE TAB_QUADTREE ADD version VARCHAR(15)" );
308 rs.close();
309 stmt.close();
310 } catch ( SQLException e ) {
311 if ( rs != null ) {
312 rs.close();
313 }
314 if ( stmt != null ) {
315 stmt.close();
316 }
317 LOG.logError( "An error occurred while trying to insert a new 'version' column in the database: " + e.getMessage(),
318 e );
319 }
320 }
321 }
322 } else {
323 LOG.logError( "Could not find the foreign key (fk_root) of the table: '" + table
324 + "'. Is your database set up correct?" );
325 }
326 } catch ( SQLException e ) {
327 LOG.logError( "An error occurred while determening version of quadtree, therefore setting version to '1.0.0'. Errormessage: " + e.getMessage(),
328 e );
329 } catch ( DBPoolException e ) {
330 LOG.logError( "An error occurred while acquiring connection to the database to determine version of quadtree, therefore setting version to '1.0.0'. Errormessage: " + e.getMessage(),
331 e );
332 } finally {
333 quadTreeVersionInfo.put( table, version );
334 try {
335 if ( rs != null ) {
336 rs.close();
337 }
338 if ( stmt != null ) {
339 stmt.close();
340 }
341 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
342 } catch ( SQLException e ) {
343 LOG.logError( "Could not close ResultSet or Statement because: " + e.getMessage() );
344 } catch ( DBPoolException e ) {
345 LOG.logError( "Could not release connection because: " + e.getMessage() );
346 }
347 }
348 }
349
350 // } catch ( SQLException e ) {
351 // String msg = e.getMessage();
352 // if( msg != null && msg.contains( " )
353 // }
354
355 return version;
356 }
357
358 @Override
359 public int performUpdate( MappedFeatureType mappedFeatureType, Feature replacementFeature, Filter filter,
360 String lockId )
361 throws DatastoreException {
362 int cnt = super.performUpdate( mappedFeatureType, replacementFeature, filter, lockId );
363
364 // update index
365
366 return cnt;
367 }
368
369 @Override
370 public int performUpdate( MappedFeatureType mappedFeatureType, Map<PropertyPath, FeatureProperty> replacementProps,
371 Filter filter, String lockId )
372 throws DatastoreException {
373
374 int cnt = super.performUpdate( mappedFeatureType, replacementProps, filter, lockId );
375
376 // update index
377
378 return cnt;
379 }
380
381 /**
382 * loads the metadata of an Index from the TAB_DEEGREE_IDX table
383 *
384 * @param jdbc
385 * database connection information
386 * @param table
387 * name of the table containing a featuretypes data
388 *
389 * @return FK to the index
390 * @throws IndexException
391 */
392 private int loadIndexMetadata( JDBCConnection jdbc, String table )
393 throws IndexException {
394 int fk_indexTree = -1;
395 Connection con = null;
396 DBConnectionPool pool = null;
397 Statement stmt = null;
398 ResultSet rs = null;
399 try {
400 pool = DBConnectionPool.getInstance();
401 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
402
403 StringBuilder sb = new StringBuilder( 400 );
404 sb.append( "Select INDEX_NAME, FK_INDEXTREE from TAB_DEEGREE_IDX where " );
405 sb.append( "column_name = 'geometry' AND " );
406 sb.append( "table_name = '" ).append( table.toLowerCase() ).append( "'" );
407
408 LOG.logDebug( "Get Index Metadata sql statement:\n", sb );
409
410 stmt = con.createStatement();
411 rs = stmt.executeQuery( sb.toString() );
412
413 if ( rs.next() ) {
414 indexName = rs.getString( "INDEX_NAME" );
415 fk_indexTree = rs.getInt( "FK_INDEXTREE" );
416 } else {
417 throw new IndexException( "could not read index metadata" );
418 }
419 } catch ( DBPoolException e ) {
420 throw new IndexException( "could not load quadtree definition from database", e );
421 } catch ( SQLException e ) {
422 throw new IndexException( "could not load quadtree definition from database", e );
423 } finally {
424 try {
425 if ( rs != null ) {
426 rs.close();
427 }
428 if ( stmt != null ) {
429 stmt.close();
430 }
431 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
432 } catch ( SQLException e ) {
433 LOG.logError( "Could not close Set or Statement because: " + e.getMessage() );
434 } catch ( DBPoolException e ) {
435 LOG.logError( "Could not reslease connection because: " + e.getMessage() );
436 }
437 }
438 return fk_indexTree;
439 }
440 }