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 &lt; 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 &lt; 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    }