001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_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: 21181 $, $Date: 2009-12-02 16:47:53 +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 }