001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_testing/src/org/deegree/io/quadtree/DBQuadtreeManager.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.quadtree;
037
038 import java.io.IOException;
039 import java.io.StringReader;
040 import java.security.InvalidParameterException;
041 import java.sql.Connection;
042 import java.sql.Date;
043 import java.sql.PreparedStatement;
044 import java.sql.ResultSet;
045 import java.sql.ResultSetMetaData;
046 import java.sql.SQLException;
047 import java.sql.Statement;
048 import java.util.HashMap;
049 import java.util.UUID;
050
051 import org.deegree.datatypes.Types;
052 import org.deegree.framework.log.ILogger;
053 import org.deegree.framework.log.LoggerFactory;
054 import org.deegree.framework.util.StringTools;
055 import org.deegree.io.DBConnectionPool;
056 import org.deegree.io.DBPoolException;
057 import org.deegree.io.JDBCConnection;
058 import org.deegree.io.dbaseapi.DBaseException;
059 import org.deegree.io.shpapi.HasNoDBaseFileException;
060 import org.deegree.io.shpapi.ShapeFile;
061 import org.deegree.model.feature.Feature;
062 import org.deegree.model.feature.schema.FeatureType;
063 import org.deegree.model.feature.schema.PropertyType;
064 import org.deegree.model.spatialschema.Envelope;
065 import org.deegree.model.spatialschema.GMLGeometryAdapter;
066 import org.deegree.model.spatialschema.Geometry;
067 import org.deegree.model.spatialschema.GeometryException;
068 import org.deegree.model.spatialschema.GeometryFactory;
069 import org.deegree.model.spatialschema.Point;
070
071 /**
072 * Access control to a quadtree for managing spatial indizes stored in a usual database.
073 *
074 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
075 * @author last edited by: $Author: aschmitz $
076 *
077 * @version $Revision: 21180 $, $Date: 2009-12-02 16:45:30 +0100 (Mi, 02. Dez 2009) $
078 * @param <T>
079 * the type of the quadtree. If unsure use the determineQuattreType() method to determine
080 * the type. Be carefull though, if you use a wrong generic here (e.g. not Integer or
081 * String) while supplying another types.Type to the constructor there is no way to check
082 * find the correct instance.
083 */
084 public class DBQuadtreeManager<T> {
085
086 private static final ILogger LOG = LoggerFactory.getLogger( DBQuadtreeManager.class );
087
088 protected JDBCConnection jdbc = null;
089
090 protected String table = null;
091
092 protected String column = null;
093
094 protected String owner = null;
095
096 protected String indexName = null;
097
098 protected int maxDepth = 6;
099
100 private DBQuadtree<T> qt = null;
101
102 protected Envelope envelope = null;
103
104 protected String backend = null;
105
106 private int TYPE;
107
108 private static HashMap<String, String> quadTreeVersionInfo = new HashMap<String, String>();
109
110 /**
111 * @param jdbc
112 * database connection info
113 * @param owner
114 * owner of the table (optional, database user will be used if set to null )
115 * @param indexName
116 * this name will be used to create the table that stores the nodes of a specific quadtree
117 * @param table
118 * name of table the index shall be created for
119 * @param column
120 * name of column the index shall be created for
121 * @param maxDepth
122 * max depth of the generated quadtree (default = 6 if a value < 2 will be passed)
123 * @param type
124 * the type of the id of the quadtree, valid are Types.INTEGER and Types.VARCHAR. if unsure use
125 * Intger.MIN_VALUE and call {@link #determineQuattreeType()}, to get an instance of the Type.
126 */
127 public DBQuadtreeManager( JDBCConnection jdbc, String owner, String indexName, String table, String column,
128 int maxDepth, int type ) {
129 TYPE = type;
130 if ( TYPE != Types.INTEGER && TYPE != Types.VARCHAR ) {
131 TYPE = Integer.MIN_VALUE;
132 }
133 if ( jdbc == null ) {
134 throw new InvalidParameterException( "The JDBCConnection reference parameter 'jdbc' may not be null." );
135 }
136 this.jdbc = jdbc;
137 if ( table == null || "".equals( table.trim() ) ) {
138 throw new InvalidParameterException( "The 'table' parameter may not be null or emtpy." );
139 }
140 this.table = table.trim();
141
142 if ( indexName == null || "".equals( indexName.trim() ) || "idx_".equalsIgnoreCase( indexName.trim() ) ) {
143 throw new InvalidParameterException(
144 "The 'indexName' parameter may not be null or emtpy or solumnly exist of idx_." );
145 }
146 this.indexName = indexName.trim();
147
148 if ( column == null || "".equals( column.trim() ) ) {
149 throw new InvalidParameterException( "The 'column' parameter may not be null or emtpy." );
150 }
151 this.column = column.trim();
152
153 this.owner = owner;
154 if ( owner == null ) {
155 String user = jdbc.getUser();
156 if ( user == null || "".equals( user.trim() ) ) {
157 this.owner = "";
158 } else {
159 this.owner = user;
160 }
161 }
162 if ( maxDepth > 1 ) {
163 this.maxDepth = maxDepth;
164 } else {
165 this.maxDepth = 6;
166 }
167
168 String driver = jdbc.getDriver();
169 if ( driver == null || "".equals( driver.trim() ) ) {
170 throw new InvalidParameterException( "The JDBCConnection.driver may not be null or emtpy." );
171 }
172 // find out which database is used
173 if ( driver.toUpperCase().contains( "POSTGRES" ) ) {
174 backend = "POSTGRES";
175 } else if ( driver.toUpperCase().contains( "SQLSERVER" ) ) {
176 backend = "SQLSERVER";
177 } else if ( driver.toUpperCase().contains( "INGRES" ) || driver.equals( "ca.edbc.jdbc.EdbcDriver" ) ) {
178 backend = "INGRES";
179 } else if ( driver.toUpperCase().contains( "HSQLDB" ) ) {
180 backend = "HSQLDB";
181 } else {
182 backend = "GENERICSQL";
183 }
184
185 try {
186 if ( !hasIndexTable() ) {
187 LOG.logDebug( "It seems no indextable with name: '" + indexName
188 + "' exists in the database backend, creating one." );
189 createIndexTable( indexName, "VARCHAR(50)" );
190 }
191 } catch ( IndexException e ) {
192 LOG.logWarning( "Could not create index (does it already exist?): " + e.getMessage() );
193 LOG.logDebug( "Stack trace: " + e.getMessage(), e );
194 }
195 }
196
197 /**
198 *
199 * @param driver
200 * database connection driver
201 * @param logon
202 * database connection logon
203 * @param user
204 * database user
205 * @param password
206 * database user's password
207 * @param encoding
208 * character encoding to be used (if possible)
209 * @param indexName
210 * this name will be used to create the table that stores the nodes of a specific quadtree
211 * @param table
212 * name of table the index shall be created for
213 * @param column
214 * name of column the index shall be created for
215 * @param owner
216 * owner of the table (optional, database user will be used if set to null )
217 * @param maxDepth
218 * max depth of the generated quadtree (default = 6 if a value < 2 will be passed)
219 * @param type
220 * the type of the id of the quadtree, valid are Types.INTEGER and Types.VARCHAR. if unsure use
221 * Intger.MIN_VALUE and call {@link #determineQuattreeType()}, to get an instance of the Type.
222 */
223 public DBQuadtreeManager( String driver, String logon, String user, String password, String encoding,
224 String indexName, String table, String column, String owner, int maxDepth, int type ) {
225 this( new JDBCConnection( driver, logon, user, password, null, encoding, null ), owner, indexName, table,
226 column, maxDepth, type );
227 }
228
229 /**
230 * initializes a QuadtreeManager to access an alread existing Quadtree
231 *
232 * @param jdbc
233 * database connection info
234 * @param table
235 * name of table the index shall be created for
236 * @param column
237 * name of column the index shall be created for
238 * @param owner
239 * owner of the table (optional, database user will be used if set to null )
240 * @param type
241 * the type of the id of the quadtree, valid are Types.INTEGER and Types.VARCHAR. if unsure use
242 * Intger.MIN_VALUE and call {@link #determineQuattreeType()}, to get an instance of the Type.
243 */
244 public DBQuadtreeManager( JDBCConnection jdbc, String table, String column, String owner, int type ) {
245 this( jdbc, owner, "idx_" + table, table, column, 6, type );
246 }
247
248 /**
249 * initializes a QuadtreeManager to access an alread existing Quadtree
250 *
251 * @param driver
252 * database connection driver
253 * @param logon
254 * database connection logon
255 * @param user
256 * database user
257 * @param password
258 * database user's password
259 * @param encoding
260 * character encoding to be used (if possible)
261 * @param table
262 * name of table the index shall be created for
263 * @param column
264 * name of column the index shall be created for
265 * @param owner
266 * owner of the table (optional, database user will be used if set to null )
267 * @param type
268 * the type of the id of the quadtree, valid are Types.INTEGER and Types.VARCHAR. if unsure use
269 * Intger.MIN_VALUE and call {@link #determineQuattreeType()}, to get an instance of the Type.
270 */
271 public DBQuadtreeManager( String driver, String logon, String user, String password, String encoding, String table,
272 String column, String owner, int type ) {
273 this( new JDBCConnection( driver, logon, user, password, null, encoding, null ), owner, "idx_" + table, table,
274 column, 6, type );
275 }
276
277 /**
278 * loads the metadata of a Index from the TAB_DEEGREE_IDX table
279 *
280 * @return FK to the index
281 * @throws IndexException
282 */
283 protected int loadIndexMetadata()
284 throws IndexException {
285 int fk_indexTree = -1;
286 Connection con = null;
287 DBConnectionPool pool = null;
288 try {
289 pool = DBConnectionPool.getInstance();
290 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
291
292 StringBuilder sb = new StringBuilder( 200 );
293 sb.append( "Select INDEX_NAME, FK_INDEXTREE from TAB_DEEGREE_IDX where " );
294 sb.append( "column_name = '" ).append( column ).append( "' AND " );
295 sb.append( "table_name = '" ).append( table ).append( "' AND " );
296 sb.append( "owner = '" ).append( owner ).append( "'" );
297
298 Statement stmt = con.createStatement();
299 ResultSet rs = stmt.executeQuery( sb.toString() );
300
301 if ( rs.next() ) {
302 indexName = rs.getString( "INDEX_NAME" );
303 fk_indexTree = rs.getInt( "FK_INDEXTREE" );
304 } else {
305 throw new IndexException(
306 "Could not read the structure of the quadtree tables from database (did you run the base/scripts/index/quadtree.hsql script, which create the meta-info tables?)." );
307 }
308 rs.close();
309 stmt.close();
310 } catch ( SQLException e ) {
311 throw new IndexException(
312 "Could not load quadtree definition from database (did you run the base/scripts/index/quadtree.hsql script, which create the meta-info tables?). The error message was: "
313 + e.getMessage() );
314 } catch ( DBPoolException e ) {
315 throw new IndexException( "Could not acquire a database connection. The error message was: "
316 + e.getMessage() );
317 } finally {
318 try {
319 if ( pool != null && con != null ) {
320 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
321 }
322 } catch ( Exception e1 ) {
323 LOG.logError( "Could not release the jdbc connection because: " + e1.getMessage() );
324 }
325 }
326 LOG.logDebug( "It seems an indextable with name: '" + indexName + "' allready exists in the database backend." );
327 return fk_indexTree;
328 }
329
330 /**
331 * returns the current Quadtree
332 *
333 * @return the current Quadtree
334 * @throws IndexException
335 */
336 public DBQuadtree<T> getQuadtree()
337 throws IndexException {
338 if ( qt == null ) {
339 qt = loadQuadtree();
340 }
341 return qt;
342 }
343
344 /**
345 * loads an already existing quadtree
346 *
347 * @return the Quadtree structure read from the database
348 * @throws IndexException
349 */
350 private DBQuadtree<T> loadQuadtree()
351 throws IndexException {
352 int fk_index = loadIndexMetadata();
353
354 String version = getQTVersion( table );
355 return new DBQuadtree<T>( fk_index, indexName, jdbc, version );
356 }
357
358 /**
359 * @return an instance of the type of the feature id's stored in the db. Possible instances are
360 * <code>String<code>, <code>Integer</code> or <code>null</code> if the type could not be determined.
361 * @throws IndexException
362 * if the type information could not be retrieved either because no connection was acquired or an error
363 * occurred while executing the select statement.
364 */
365 public Object determineQuattreeType()
366 throws IndexException {
367
368 if ( TYPE == Integer.MIN_VALUE ) {
369 StringBuilder sb = new StringBuilder( 1000 );
370 sb.append( "SELECT FK_ITEM from " ).append( indexName ).append( "_ITEM " );
371 Connection con = null;
372 DBConnectionPool pool = null;
373
374 try {
375 pool = DBConnectionPool.getInstance();
376 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
377
378 PreparedStatement stmt = con.prepareStatement( sb.toString() );
379 ResultSet rs = stmt.executeQuery();
380 ResultSetMetaData metaData = rs.getMetaData();
381
382 if ( metaData != null ) {
383 TYPE = metaData.getColumnType( 1 );
384 LOG.logDebug( "Found type: " + TYPE );
385 }
386 rs.close();
387 stmt.close();
388
389 } catch ( SQLException e ) {
390 throw new IndexException( "Could not get Type information because: " + e.getMessage(), e );
391 } catch ( DBPoolException e ) {
392 throw new IndexException(
393 "Could not acquire a connection to the database to retrieve column information because: "
394 + e.getMessage(), e );
395 } finally {
396 try {
397 if ( pool != null && con != null ) {
398 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
399 jdbc.getPassword() );
400 }
401 } catch ( DBPoolException e ) {
402 LOG.logError( "Could not release JDBC connection because: " + e.getMessage() );
403 }
404 }
405 }
406 Object result = null;
407 switch ( TYPE ) {
408 case Types.VARCHAR:
409 result = "";
410 break;
411 case Types.INTEGER:
412 result = new Integer( 1 );
413 break;
414 default:
415 TYPE = Integer.MAX_VALUE;
416 }
417 return result;
418 }
419
420 /**
421 * @param table
422 * to open a quadtree for.
423 * @return the version of the quadtree used.
424 */
425 /**
426 * @param table
427 */
428 private String getQTVersion( String table ) {
429 String version = "1.0.0";
430 if ( quadTreeVersionInfo.containsKey( table ) && quadTreeVersionInfo.get( table ) != null ) {
431 LOG.logDebug( "Retrieved the quatdree version info for table: " + table + " from cache." );
432 version = quadTreeVersionInfo.get( table );
433 } else {
434 Connection con = null;
435 DBConnectionPool pool = null;
436 Statement stmt = null;
437 ResultSet rs = null;
438 pool = DBConnectionPool.getInstance();
439 StringBuilder sb = new StringBuilder( 400 );
440 sb.append( "SELECT fk_indextree FROM tab_deegree_idx WHERE " );
441 sb.append( "column_name = 'geometry' AND " );
442 sb.append( "table_name = '" ).append( table.toLowerCase() ).append( "'" );
443
444 LOG.logDebug( "Get Index Metadata sql statement:\n", sb );
445 try {
446 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
447 stmt = con.createStatement();
448 rs = stmt.executeQuery( sb.toString() );
449 String tableID = null;
450 if ( rs.next() ) {
451 tableID = rs.getString( 1 );
452 }
453 if ( tableID != null ) {
454 sb = new StringBuilder( 400 );
455 sb.append( "SELECT * FROM tab_quadtree WHERE " );
456 sb.append( "fk_root = '" ).append( tableID.trim() ).append( "'" );
457 rs.close();
458 stmt.close();
459 stmt = con.createStatement();
460 rs = stmt.executeQuery( sb.toString() );
461 if ( rs.next() ) {
462 boolean hasVersion = false;
463 ResultSetMetaData md = rs.getMetaData();
464 int numberOfColumns = md.getColumnCount();
465 System.out.println( "Columnecount: " + numberOfColumns );
466 for ( int i = 1; i <= numberOfColumns && !hasVersion; i++ ) {
467 String tmp = md.getColumnName( i );
468 LOG.logDebug( "Found columnname: " + tmp );
469 if ( tmp != null ) {
470 if ( "version".equalsIgnoreCase( tmp.trim() ) ) {
471 hasVersion = true;
472 version = rs.getString( i );
473 LOG.logDebug( "Found a version column, setting version to: " + rs.getString( i ) );
474 }
475 }
476 }
477 if ( !hasVersion ) {
478 try {
479 LOG.logInfo( "Found no Version Column in the TAB_QUADTREE table, assuming version 1.0.0, and adding the version column." );
480 rs.close();
481 stmt.close();
482 stmt = con.createStatement();
483 rs = stmt.executeQuery( "ALTER TABLE TAB_QUADTREE ADD version VARCHAR(15)" );
484 rs.close();
485 stmt.close();
486 } catch ( SQLException e ) {
487 rs.close();
488 stmt.close();
489 LOG.logError(
490 "An error occurred while trying to insert a new 'version' column in the database: "
491 + e.getMessage(), e );
492 }
493 }
494 }
495 } else {
496 LOG.logError( "Could not find the foreign key (fk_root) of the table: '" + table
497 + "'. Is your database set up correct?" );
498 }
499 } catch ( SQLException e ) {
500 LOG.logError(
501 "An error occurred while determening version of quadtree, therefore setting version to '1.0.0'. Errormessage: "
502 + e.getMessage(), e );
503 } catch ( DBPoolException e ) {
504 LOG.logError(
505 "An error occurred while acquiring connection to the database to determine version of quadtree, therefore setting version to '1.0.0'. Errormessage: "
506 + e.getMessage(), e );
507 } finally {
508 quadTreeVersionInfo.put( table, version );
509 try {
510 if ( rs != null ) {
511 rs.close();
512 }
513 if ( stmt != null ) {
514 stmt.close();
515 }
516 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
517 } catch ( SQLException e ) {
518 LOG.logError( "Could not close ResultSet or Statement because: " + e.getMessage() );
519 } catch ( DBPoolException e ) {
520 LOG.logError( "Could not release connection because: " + e.getMessage() );
521 }
522 }
523 }
524 return version;
525 }
526
527 /**
528 * stores one feature into the defined table
529 *
530 * @param feature
531 * the feature to insert into the 'table'
532 * @param id
533 * of the feature to store in the database, currently String and Integer are supported. If it is neither,
534 * the Object is saved as an object, which may result in inconsitencies.
535 * @param jdbc
536 * the connection to the database.
537 * @throws IndexException
538 * if the feature can not be inserted or a connection error occurrs.
539 */
540 protected void storeFeature( Feature feature, T id, JDBCConnection jdbc )
541 throws IndexException {
542
543 Connection con = null;
544 DBConnectionPool pool = null;
545
546 FeatureType ft = feature.getFeatureType();
547 PropertyType[] ftp = ft.getProperties();
548 try {
549 pool = DBConnectionPool.getInstance();
550 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
551
552 StringBuilder sb = new StringBuilder( 100 );
553 sb.append( "INSERT INTO " ).append( table ).append( '(' );
554 sb.append( "FEATURE_ID," );
555 for ( int i = 0; i < ftp.length; i++ ) {
556 if ( ftp[i].getType() == Types.GEOMETRY ) {
557 sb.append( column ).append( ' ' );
558 } else {
559 sb.append( ftp[i].getName().getLocalName() );
560 }
561 if ( i < ftp.length - 1 ) {
562 sb.append( ", " );
563 }
564 }
565 sb.append( ") VALUES (?," );
566 for ( int i = 0; i < ftp.length; i++ ) {
567 sb.append( '?' );
568 if ( i < ftp.length - 1 ) {
569 sb.append( ", " );
570 }
571 }
572 sb.append( ')' );
573
574 PreparedStatement stmt = con.prepareStatement( sb.toString() );
575 if ( id instanceof String ) {
576 LOG.logDebug( "Setting to id '" + id + "'an instance of String" );
577 stmt.setString( 1, (String) id );
578 } else if ( id instanceof Integer ) {
579 LOG.logDebug( "Setting to id '" + id + "'an instance of integer" );
580 stmt.setInt( 1, ( (Integer) id ).intValue() );
581 } else {
582 LOG.logWarning( "The type of id is uncertain (neiter String nor Integer), adding it as an 'object' to the database." );
583 stmt.setObject( 1, id );
584 }
585
586 for ( int i = 0; i < ftp.length; i++ ) {
587 Object o = null;
588 if ( feature.getProperties( ftp[i].getName() ) != null ) {
589 if ( feature.getProperties( ftp[i].getName() ).length > 0 ) {
590 o = feature.getProperties( ftp[i].getName() )[0].getValue();
591 }
592 }
593 if ( o == null ) {
594 stmt.setNull( i + 2, ftp[i].getType() );
595 } else {
596 switch ( ftp[i].getType() ) {
597 case Types.CHAR:
598 case Types.VARCHAR:
599 stmt.setString( i + 2, o.toString() );
600 break;
601 case Types.SMALLINT:
602 case Types.TINYINT:
603 case Types.INTEGER:
604 case Types.BIGINT:
605 stmt.setInt( i + 2, (int) Double.parseDouble( o.toString() ) );
606 break;
607 case Types.DOUBLE:
608 case Types.FLOAT:
609 case Types.DECIMAL:
610 case Types.NUMERIC:
611 stmt.setFloat( i + 2, Float.parseFloat( o.toString() ) );
612 break;
613 case Types.DATE:
614 case Types.TIME:
615 case Types.TIMESTAMP:
616 stmt.setDate( i + 2, (Date) o );
617 break;
618 case Types.GEOMETRY: {
619 StringBuffer gs = GMLGeometryAdapter.export( (Geometry) o );
620 String s = StringTools.replace( gs.toString(), ">",
621 " xmlns:gml=\"http://www.opengis.net/gml\">", false );
622 if ( backend.equals( "POSTGRES" ) || backend.equals( "HSQLDB" ) ) {
623 LOG.logDebug( "Adding geometry: " + s );
624 stmt.setString( i + 2, s );
625 } else if ( backend.equals( "INGRES" ) ) {
626 stmt.setObject( i + 2, new StringReader( s ) );
627 } else {
628 stmt.setObject( i + 2, s.getBytes() );
629 }
630 break;
631 }
632 default: {
633 LOG.logWarning( "unsupported type: " + ftp[i].getType() );
634 }
635 }
636 }
637 }
638 LOG.logDebug( "SQL statement for insert feature: " + sb );
639 if ( !stmt.execute() ) {
640 LOG.logError( "The insertion of the feature resulted in " + stmt.getUpdateCount() + " updates." );
641 }
642
643 stmt.close();
644 } catch ( SQLException e ) {
645 String msg = "Could not insert feature with id='" + id + "' into the database because: " + e.getMessage();
646 LOG.logError( msg, e );
647 throw new IndexException( msg, e );
648 } catch ( DBPoolException e ) {
649 String msg = "Could not acquire a connection to the database to insert the feature with id: " + id;
650 LOG.logError( msg, e );
651 throw new IndexException( msg, e );
652 } catch ( GeometryException e ) {
653 String msg = "Could not insert feature with id='" + id + "' into the database because: " + e.getMessage();
654 LOG.logError( msg, e );
655 throw new IndexException( msg, e );
656 } finally {
657 try {
658 if ( pool != null && con != null ) {
659 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
660 }
661 } catch ( DBPoolException e ) {
662 LOG.logError( "Could not release JDBC connection because: " + e.getMessage() );
663 }
664 }
665 }
666
667 /**
668 * initializes the root node of the quadtree
669 *
670 * @param fileName
671 * @throws IndexException
672 * @throws IOException
673 *
674 */
675 protected void initRootNode( String fileName )
676 throws IndexException, IOException {
677 LOG.logDebug( "Trying to read shapefile from file: " + fileName );
678 ShapeFile sf = new ShapeFile( fileName );
679 if ( envelope == null ) {
680 envelope = sf.getFileMBR();
681 }
682 envelope = envelope.getBuffer( envelope.getWidth() / 20 );
683 LOG.logInfo( "Bounding box of the root feature: " + envelope );
684 sf.close();
685 // DBQuadtree<T> qtTmp = loadQuadtree();
686 Connection con = null;
687 DBConnectionPool pool = null;
688 try {
689 pool = DBConnectionPool.getInstance();
690 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
691
692 StringBuilder sb = new StringBuilder( 100 );
693 sb.append( "INSERT INTO " ).append( indexName );
694 sb.append( " ( ID, MINX, MINY, MAXX , MAXY ) " );
695 sb.append( "VALUES ( ?, ?, ?, ?, ? ) " );
696 PreparedStatement stmt = con.prepareStatement( sb.toString() );
697 stmt.setString( 1, "1" );
698 stmt.setFloat( 2, (float) envelope.getMin().getX() );
699 stmt.setFloat( 3, (float) envelope.getMin().getY() );
700 stmt.setFloat( 4, (float) envelope.getMax().getX() );
701 stmt.setFloat( 5, (float) envelope.getMax().getY() );
702 stmt.execute();
703 stmt.close();
704 } catch ( Exception e ) {
705 LOG.logError( e.getMessage(), e );
706 throw new IndexException( "could not create root node definition at database", e );
707 } finally {
708 try {
709 if ( pool != null && con != null ) {
710 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
711 }
712 } catch ( Exception e1 ) {
713 e1.printStackTrace();
714 }
715 }
716 }
717
718 /**
719 * before importing a shape a user may set an envelope for the quadtree to bee created that is different from the
720 * one of the shape by calling this method. Notice: calling this method does not have any effect when calling
721 *
722 * @see #appendShape(String) method.
723 * @param envelope
724 */
725 public void setRootEnvelope( Envelope envelope ) {
726 this.envelope = envelope;
727 }
728
729 /**
730 * initializes a new Quadtree by adding a row into table TAB_QUADTREE and into TAB_QTNODE (-> root node)
731 *
732 * @param fileName
733 *
734 * @return the id of the inserted node
735 * @throws IndexException
736 * @throws IOException
737 * if the shape file could not be read.
738 */
739 protected int initQuadtree( String fileName )
740 throws IndexException, IOException {
741
742 initRootNode( fileName );
743 Connection con = null;
744 DBConnectionPool pool = null;
745 int id = -1;
746 try {
747 pool = DBConnectionPool.getInstance();
748 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
749
750 // first check if the version column exists;
751 StringBuilder versionCheck = new StringBuilder( "Select * from TAB_QUADTREE;" );
752 Statement stmt = con.createStatement();
753 ResultSet rs = stmt.executeQuery( versionCheck.toString() );
754 boolean hasVersion = false;
755 try {
756 ResultSetMetaData md = rs.getMetaData();
757 int numberOfColumns = md.getColumnCount();
758
759 for ( int i = 1; i <= numberOfColumns && !hasVersion; i++ ) {
760 String tmp = md.getColumnName( i );
761 if ( tmp != null ) {
762 if ( "version".equalsIgnoreCase( tmp.trim() ) ) {
763 hasVersion = true;
764 }
765 }
766 }
767 if ( !hasVersion ) {
768 LOG.logInfo( "Found no Version Column in the TAB_QUADTREE table, assuming version 2.0.0, and adding the version column." );
769 rs.close();
770 stmt.close();
771 stmt = con.createStatement();
772 rs = stmt.executeQuery( "ALTER TABLE TAB_QUADTREE ADD version VARCHAR(15)" );
773 rs.close();
774 stmt.close();
775 }
776 } catch ( SQLException e ) {
777 LOG.logError( "An error occurred while trying to determine if the database supports versioning: "
778 + e.getMessage() );
779 }
780
781 StringBuilder sb = new StringBuilder( 100 );
782 sb.append( "INSERT INTO TAB_QUADTREE (" );
783 if ( backend.equals( "INGRES" ) || backend.equals( "HSQLDB" ) ) {
784 sb.append( "ID, " );
785 }
786 sb.append( "FK_ROOT, DEPTH, VERSION ) VALUES ( " );
787 if ( backend.equals( "INGRES" ) || backend.equals( "HSQLDB" ) ) {
788 stmt = con.createStatement();
789 rs = stmt.executeQuery( "SELECT MAX(ID) FROM TAB_QUADTREE" );
790 rs.next();
791 int myid = rs.getInt( 1 ) + 1;
792 sb.append( myid + ", " );
793 }
794 sb.append( " '1', ?, '2.0.0' ) " );
795
796 PreparedStatement pstmt = con.prepareStatement( sb.toString() );
797 pstmt.setInt( 1, maxDepth );
798 pstmt.execute();
799 pstmt.close();
800 stmt = con.createStatement();
801 rs = stmt.executeQuery( "select max(ID) from TAB_QUADTREE" );
802 rs.next();
803 id = rs.getInt( 1 );
804 if ( id < 0 ) {
805 throw new IndexException( "could not read ID of quadtree from database." );
806 }
807 } catch ( SQLException e ) {
808 throw new IndexException(
809 "Could not load quadtree definition from database (did you run the base/scripts/index/quadtree.hsql script, which create the meta-info tables?). The error message was: "
810 + e.getMessage() );
811 } catch ( DBPoolException e ) {
812 throw new IndexException(
813 "Could not acquire a connection to the database to initiate the quattree index structure because: "
814 + e.getMessage() );
815 } finally {
816 try {
817 if ( pool != null && con != null ) {
818 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
819 }
820 } catch ( DBPoolException e ) {
821 LOG.logError( "Could not release JDBC connection because: " + e.getMessage() );
822 }
823 }
824 return id;
825 }
826
827 /**
828 * Inserts a row into the quadtree meta data structure 'TAB_DEEGREE_IDX', containing information on the table,
829 * geometry, indexname, owner and the foreign_key to the index table.
830 *
831 * @param fk_indexTree
832 * @throws IndexException
833 */
834 public void insertIndexMetadata( int fk_indexTree )
835 throws IndexException {
836
837 Connection con = null;
838 DBConnectionPool pool = null;
839 try {
840 pool = DBConnectionPool.getInstance();
841 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
842
843 StringBuilder sb = new StringBuilder( 100 );
844 sb.append( "INSERT INTO TAB_DEEGREE_IDX ( " );
845 if ( backend.equals( "INGRES" ) || backend.equals( "HSQLDB" ) ) {
846 sb.append( "ID, " );
847 }
848 sb.append( "column_name, table_name, " );
849 sb.append( "owner, INDEX_NAME, FK_indexTree ) " );
850 sb.append( "VALUES ( " );
851 if ( backend.equals( "INGRES" ) || backend.equals( "HSQLDB" ) ) {
852 Statement stm = con.createStatement();
853 ResultSet rs = stm.executeQuery( "SELECT MAX(ID) FROM TAB_QUADTREE" );
854 rs.next();
855 int myid = rs.getInt( 1 ) + 1;
856 sb.append( myid + ", " );
857 }
858 sb.append( "?, ?, ?, ?, ? ) " );
859 PreparedStatement stmt = con.prepareStatement( sb.toString() );
860 stmt.setString( 1, column );
861 stmt.setString( 2, table );
862 stmt.setString( 3, owner );
863 stmt.setString( 4, indexName );
864 stmt.setInt( 5, fk_indexTree );
865
866 stmt.execute();
867 stmt.close();
868 } catch ( SQLException e ) {
869 throw new IndexException(
870 "Could not insert a new row into the quadtree index metadata table (did you run the base/scripts/index/quadtree.hsql script, which creates the meta-info tables?). The error message was: "
871 + e.getMessage() );
872 } catch ( DBPoolException e ) {
873 throw new IndexException(
874 "Could not acquire a connection to the database to store the quattree index metadata structure because: "
875 + e.getMessage() );
876 } finally {
877 try {
878 if ( pool != null && con != null ) {
879 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
880 }
881 } catch ( DBPoolException e ) {
882 LOG.logError( "Could not release JDBC connection because: " + e.getMessage() );
883 }
884 }
885 }
886
887 /**
888 * creates table the shape data shall be stored
889 *
890 * @param fileName
891 * @param idType
892 * the type of the feature_id column, for example VARCHAR(50) or NUMBER.
893 * @throws IndexException
894 * @throws IOException
895 */
896 protected void createDataTable( String fileName, String idType )
897 throws IndexException, IOException {
898 ShapeFile sf = new ShapeFile( fileName );
899 FeatureType ft = null;
900 try {
901 ft = sf.getFeatureByRecNo( 1 ).getFeatureType();
902 } catch ( HasNoDBaseFileException e ) {
903 throw new IndexException( e );
904 } catch ( DBaseException e ) {
905 throw new IndexException( e );
906 }
907 sf.close();
908 StringBuilder sb = new StringBuilder( 1000 );
909 sb.append( "CREATE TABLE " ).append( table ).append( '(' );
910
911 sb.append( "FEATURE_ID " ).append( idType ).append( "," );
912 PropertyType[] ftp = ft.getProperties();
913 for ( int i = 0; i < ftp.length; i++ ) {
914 if ( ftp[i].getType() == Types.GEOMETRY ) {
915 sb.append( column ).append( ' ' );
916 } else {
917 sb.append( ftp[i].getName().getLocalName() ).append( ' ' );
918 }
919 sb.append( getDatabaseType( ftp[i].getType() ) );
920 if ( i < ftp.length - 1 ) {
921 sb.append( ", " );
922 }
923 }
924 sb.append( ')' );
925
926 Connection con = null;
927 DBConnectionPool pool = null;
928 try {
929 pool = DBConnectionPool.getInstance();
930 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
931
932 Statement stmt = con.createStatement();
933 LOG.logDebug( sb.toString() );
934 stmt.execute( sb.toString() );
935 stmt.close();
936 } catch ( SQLException e ) {
937 throw new IndexException( "Could not create a DataTable: '" + table
938 + "' (which will hold the features from the shapefile: '" + fileName
939 + "'). The error message was: " + e.getMessage(), e );
940 } catch ( DBPoolException e ) {
941 throw new IndexException( "Could not acquire a connection to the database to create a DataTable because: "
942 + e.getMessage(), e );
943 } finally {
944 try {
945 if ( pool != null && con != null ) {
946 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
947 }
948 } catch ( DBPoolException e ) {
949 LOG.logError( "Could not release JDBC connection because: " + e.getMessage() );
950 }
951 }
952 }
953
954 /**
955 * returns the type name for a generic type code as used by SQLServer
956 *
957 * @param dataTypeCode
958 * @return the type name for a generic type code as used by SQLServer
959 */
960 String getDatabaseType( int dataTypeCode ) {
961 String type = null;
962
963 switch ( dataTypeCode ) {
964 case Types.CHAR:
965 case Types.VARCHAR:
966 type = DBQuadtreeDataTypes.getString( backend + ".string" );
967 break;
968 case Types.SMALLINT:
969 case Types.TINYINT:
970 case Types.INTEGER:
971 case Types.BIGINT:
972 type = DBQuadtreeDataTypes.getString( backend + ".integer" );
973 break;
974 case Types.DOUBLE:
975 case Types.FLOAT:
976 case Types.DECIMAL:
977 case Types.NUMERIC:
978 type = DBQuadtreeDataTypes.getString( backend + ".float" );
979 break;
980 case Types.DATE:
981 case Types.TIME:
982 case Types.TIMESTAMP:
983 type = DBQuadtreeDataTypes.getString( backend + ".datetime" );
984 break;
985 case Types.GEOMETRY:
986 type = DBQuadtreeDataTypes.getString( backend + ".geometry" );
987 break;
988 default:
989 throw new InvalidParameterException( "Unknown data type code: " + dataTypeCode );
990 }
991
992 return type;
993 }
994
995 /**
996 * imports a shape into the database and builds a quadtree on it
997 *
998 * @param fileName
999 * of the shapefile.
1000 * @throws IOException
1001 * if the shapefile could not be opened.
1002 * @throws IndexException
1003 * if an error occurred while talking to the jdbc database.
1004 * @throws DBaseException
1005 * if the connection to the shapefile could not be opened.
1006 * @throws HasNoDBaseFileException
1007 * if the feature could not be read from shape file's database file.
1008 */
1009 public void importShape( String fileName )
1010 throws IOException, IndexException, HasNoDBaseFileException, DBaseException {
1011 if ( TYPE == Integer.MIN_VALUE ) {
1012 LOG.logInfo( "You supplied an unknown type to the DBQuadtreeManager, therefore assuming you meant the Types.VARCHAR type" );
1013 TYPE = Types.VARCHAR;
1014 }
1015 StringBuilder typeName = new StringBuilder( 64 );
1016
1017 typeName.append( getDatabaseType( TYPE ) );
1018
1019 createDataTable( fileName, typeName.toString() );
1020
1021 int qtid = initQuadtree( fileName );
1022
1023 insertIndexMetadata( qtid );
1024
1025 qt = new DBQuadtree<T>( qtid, indexName, jdbc );
1026
1027 ShapeFile sf = new ShapeFile( fileName );
1028
1029 double step = 100.0 / sf.getRecordNum();
1030 double counter = 0;
1031 Envelope sfEnv = sf.getFileMBR();
1032
1033 LOG.logDebug( "The shape file read " + sf.getRecordNum() + " number of records" );
1034 for ( int i = 0; i < sf.getRecordNum(); i++ ) {
1035 Feature feat = sf.getFeatureByRecNo( i + 1 );
1036 if ( counter < step * i ) {
1037 if ( step < 1 ) {
1038 counter += 10;
1039 } else {
1040 counter += step;
1041 }
1042 System.out.println( counter + "%" );
1043 }
1044 if ( i % 200 == 0 ) {
1045 System.gc();
1046 }
1047 Envelope env = feat.getDefaultGeometryPropertyValue().getEnvelope();
1048 LOG.logDebug( i + " --- " + env );
1049 if ( env == null ) {
1050 // must be a point geometry
1051 Point point = (Point) feat.getDefaultGeometryPropertyValue();
1052 double w = sfEnv.getWidth() / 1000;
1053 double h = sfEnv.getHeight() / 1000;
1054 env = GeometryFactory.createEnvelope( point.getX() - w / 2d, point.getY() - h / 2d, point.getX() + w
1055 / 2d, point.getY()
1056 + h / 2d,
1057 null );
1058 }
1059 // map to the requested featuretype id's type
1060 T id = getMappedID( i );
1061 LOG.logDebug( "Inserting item : " + i );
1062 qt.insert( id, env );
1063 storeFeature( feat, id, jdbc );
1064 }
1065
1066 if ( "HSQLDB".equals( backend ) ) {
1067 LOG.logInfo( "Because you are using an hsql database, the current thread will wait '10' seconds, this gives the inmemory database time to flush it's tables" );
1068 try {
1069 Thread.sleep( 10000 );
1070 } catch ( InterruptedException e ) {
1071 LOG.logError(
1072 "Exception occurred while waitig for the db-manager to flush it's memory tables. Message: "
1073 + e.getMessage(), e );
1074 }
1075 }
1076 sf.close();
1077 LOG.logInfo( "finished!" );
1078 }
1079
1080 @SuppressWarnings("unchecked")
1081 private T getMappedID( int i ) {
1082 if ( TYPE == Types.VARCHAR ) {
1083 return (T) UUID.randomUUID().toString();
1084 } else if ( TYPE == Types.INTEGER ) {
1085 return (T) new Integer( i );
1086 }
1087 return null;
1088
1089 }
1090
1091 /**
1092 * appends the features of a shape to an existing datatable and inserts references into the assigned quadtree table.
1093 * <p>
1094 * you have to consider that the quadtree is just valid for a defined area. if the features to append exceeds this
1095 * area the quadtree has to be rebuilded.
1096 * </p>
1097 *
1098 * @param fileName
1099 * @throws IOException
1100 * if the shape file cannot be read.
1101 * @throws IndexException
1102 * if the quatree could not be read.
1103 */
1104 public void appendShape( String fileName )
1105 throws IOException, IndexException {
1106
1107 ShapeFile sf = new ShapeFile( fileName );
1108
1109 int b = sf.getRecordNum() / 100;
1110 if ( b == 0 )
1111 b = 1;
1112 int k = 0;
1113 qt = getQuadtree();
1114 Envelope sfEnv = sf.getFileMBR();
1115 int cnt = getMaxIdValue();
1116
1117 for ( int i = 0; i < sf.getRecordNum(); i++ ) {
1118 Feature feat = null;
1119 try {
1120 feat = sf.getFeatureByRecNo( i + 1 );
1121 } catch ( HasNoDBaseFileException e ) {
1122 throw new IndexException( e );
1123 } catch ( DBaseException e ) {
1124 throw new IndexException( e );
1125 }
1126 if ( i % b == 0 ) {
1127 System.out.println( k + "%" );
1128 k++;
1129 }
1130 if ( i % 200 == 0 ) {
1131 System.gc();
1132 }
1133 Envelope env = feat.getDefaultGeometryPropertyValue().getEnvelope();
1134 if ( env == null ) {
1135 // must be a point geometry
1136 Point point = (Point) feat.getDefaultGeometryPropertyValue();
1137 double w = sfEnv.getWidth() / 1000;
1138 double h = sfEnv.getHeight() / 1000;
1139 env = GeometryFactory.createEnvelope( point.getX() - w / 2d, point.getY() - h / 2d, point.getX() + w
1140 / 2d, point.getY()
1141 + h / 2d,
1142 null );
1143 }
1144 // map to the requested featuretype id's type
1145 T id = getMappedID( cnt + i + 1 );
1146 qt.insert( id, env );
1147 storeFeature( feat, id, jdbc );
1148 }
1149 LOG.logInfo( " finished!" );
1150 sf.close();
1151 }
1152
1153 /**
1154 * returns the maximum ID of the data table
1155 *
1156 * @return the maximum ID of the data table
1157 * @throws IndexException
1158 */
1159 private int getMaxIdValue()
1160 throws IndexException {
1161 if ( TYPE != Types.INTEGER ) {
1162 return 0;
1163 }
1164 String sql = "SELECT MAX( FEATURE_ID ) FROM " + table;
1165
1166 Connection con = null;
1167 DBConnectionPool pool = null;
1168 Statement stmt = null;
1169 int maxId = 0;
1170 try {
1171 pool = DBConnectionPool.getInstance();
1172 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
1173
1174 stmt = con.createStatement();
1175 LOG.logDebug( sql );
1176 ResultSet rs = stmt.executeQuery( sql );
1177 if ( rs.next() ) {
1178 maxId = rs.getInt( 1 );
1179 }
1180 } catch ( SQLException e ) {
1181 throw new IndexException(
1182 "Error while executing the sql statement while finding the max( Faeture_Id ) from table: "
1183 + table, e );
1184 } catch ( DBPoolException e ) {
1185 throw new IndexException( "Could not acquire a jdbc connection to read the max( Faeture_Id ) from table: "
1186 + table, e );
1187 } finally {
1188 try {
1189 if ( pool != null && con != null ) {
1190 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
1191 }
1192 } catch ( DBPoolException e ) {
1193 LOG.logError( "Could not release JDBC connection because: " + e.getMessage() );
1194 }
1195 }
1196
1197 return maxId;
1198 }
1199
1200 /**
1201 * Creates actually two tables, an indextable, which will hold the actual quadtree and an index_item table which is
1202 * a join-table between the dbNodes and the feature_ids.
1203 *
1204 * @param indexTable
1205 * name of the index table.
1206 * @param idType
1207 * the type of the feature_id column, for example VARCHAR(50) or NUMBER.
1208 * @throws IndexException
1209 * if the table could not be created.
1210 */
1211 protected void createIndexTable( String indexTable, String idType )
1212 throws IndexException {
1213 StringBuilder sb = new StringBuilder( 2000 );
1214 String qtDataType = getDatabaseType( Types.VARCHAR );
1215 sb.append( "CREATE TABLE " ).append( indexTable ).append( " ( " );
1216 sb.append( "ID " ).append( qtDataType ).append( " NOT NULL," );
1217 sb.append( "minx float NOT NULL," );
1218 sb.append( "miny float NOT NULL," );
1219 sb.append( "maxx float NOT NULL," );
1220 sb.append( "maxy float NOT NULL," );
1221 sb.append( "FK_SUBNODE1 " ).append( qtDataType );
1222 sb.append( ", FK_SUBNODE2 " ).append( qtDataType );
1223 sb.append( ", FK_SUBNODE3 " ).append( qtDataType );
1224 sb.append( ", FK_SUBNODE4 " ).append( qtDataType ).append( ")" );
1225
1226 StringBuilder sb2 = new StringBuilder( 1000 );
1227 sb2.append( "CREATE TABLE " ).append( indexName ).append( "_ITEM ( " );
1228 sb2.append( "FK_QTNODE " ).append( qtDataType ).append( " NOT NULL," );
1229 sb2.append( "FK_ITEM " ).append( idType ).append( " NOT NULL )" );
1230
1231 Connection con = null;
1232 DBConnectionPool pool = null;
1233 try {
1234 pool = DBConnectionPool.getInstance();
1235 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
1236
1237 Statement stmt = con.createStatement();
1238 stmt.execute( sb.toString() );
1239 stmt.close();
1240
1241 stmt = con.createStatement();
1242 stmt.execute( sb2.toString() );
1243 stmt.close();
1244 } catch ( SQLException e ) {
1245 throw new IndexException( "Could not create the indextable: '" + indexTable
1246 + "' and/or the index_item table: '" + indexTable
1247 + "_ITEM'. The error message was: " + e.getMessage(), e );
1248 } catch ( DBPoolException e ) {
1249 throw new IndexException(
1250 "Could not acquire a connection to the database to store create the necessary tables: "
1251 + e.getMessage(), e );
1252 } finally {
1253 try {
1254 if ( pool != null && con != null ) {
1255 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
1256 }
1257 } catch ( DBPoolException e ) {
1258 LOG.logError( "Could not release JDBC connection because: " + e.getMessage() );
1259 }
1260 }
1261 }
1262
1263 /**
1264 * Executes a simple select from indextable, and returns true if no SQL exception occurred.
1265 *
1266 * @return true if a select * from indextable resulted in no exceptions, false otherwise.
1267 */
1268 private boolean hasIndexTable() {
1269 DBConnectionPool pool = DBConnectionPool.getInstance();
1270 Connection con = null;
1271 try {
1272 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
1273 // what a great way to check for an existing table...
1274 Statement stmt = con.createStatement();
1275 stmt.execute( "SELECT * from " + indexName );
1276 stmt.close();
1277 return true;
1278 } catch ( DBPoolException e ) {
1279 LOG.logError( "Could not aqcuire connection to the database backend because: " + e.getMessage(), e );
1280 return false;
1281 } catch ( SQLException e ) {
1282 LOG.logDebug( "Stack trace: ", e );
1283 return false;
1284 } finally {
1285 try {
1286 if ( con != null ) {
1287 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
1288 }
1289 } catch ( Exception e1 ) {
1290 LOG.logError( "Could not release the jdbc connection because: " + e1.getMessage() );
1291 }
1292 }
1293 }
1294
1295 /**
1296 * Releases the db connection.
1297 */
1298 public void release() {
1299 qt.releaseConnection();
1300 }
1301
1302 }