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