001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_testing/src/org/deegree/io/quadtree/DBNode.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.security.InvalidParameterException;
039 import java.sql.Connection;
040 import java.sql.PreparedStatement;
041 import java.sql.ResultSet;
042 import java.sql.SQLException;
043 import java.sql.Statement;
044 import java.util.ArrayList;
045 import java.util.List;
046
047 import org.deegree.framework.log.ILogger;
048 import org.deegree.framework.log.LoggerFactory;
049 import org.deegree.io.DBConnectionPool;
050 import org.deegree.io.DBPoolException;
051 import org.deegree.io.JDBCConnection;
052 import org.deegree.io.quadtree.DBQuadtree.SupportedVersions;
053 import org.deegree.model.spatialschema.Envelope;
054 import org.deegree.model.spatialschema.GeometryFactory;
055 import org.deegree.model.spatialschema.Position;
056
057 /**
058 * Represents a node of a {@link DBQuadtree}. Nodes contain items which have a spatial extent
059 * corresponding to the node's position in the quadtree.
060 *
061 *
062 * @version $Revision: 21181 $
063 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
064 * @author last edited by: $Author: aschmitz $
065 *
066 * @version 1.0. $Revision: 21181 $, $Date: 2009-12-02 16:47:53 +0100 (Mi, 02 Dez 2009) $
067 *
068 * @since 2.0
069 */
070 class DBNode<T> implements Node<T> {
071
072 private static ILogger LOG = LoggerFactory.getLogger( DBNode.class );
073
074 private String id = null;
075
076 private int level;
077
078 private String[] fk_subnode = new String[4];
079
080 private Envelope envelope = null;
081
082 private JDBCConnection jdbc = null;
083
084 private DBQuadtree<T> qt = null;
085
086 private String indexTable = null;
087
088 private SupportedVersions version;
089
090 private String indexItemTable;
091
092 /**
093 * A constructor which reads the envelope from the database. Using an old Quadtree layout.
094 *
095 * @param id
096 * @param qt
097 * @param indexTable
098 * @param jdbc
099 * @param level
100 * @throws IndexException
101 * if the node with given id could not be read from the db.
102 */
103 // public DBNode( String id, DBQuadtree<T> qt, String indexTable, JDBCConnection jdbc, int level
104 // )
105 // throws IndexException {
106 // this( id, null, qt, indexTable, jdbc, level, SupportedVersions.ONE );
107 // }
108 /**
109 *
110 * @param id
111 * @param env
112 * @param qt
113 * @param indexTable
114 * @param jdbc
115 * @param level
116 * @param version
117 * of the quadtree layout to use
118 * @throws IndexException
119 * if the node with given id could not be read from the db.
120 */
121 public DBNode( String id, Envelope env, DBQuadtree<T> qt, String indexTable, JDBCConnection jdbc, int level,
122 SupportedVersions version ) throws IndexException {
123 this.id = id;
124 this.envelope = env;
125 if ( jdbc == null ) {
126 throw new InvalidParameterException( "The JDBCConnection reference parameter 'jdbc' may not be null." );
127 }
128 this.jdbc = jdbc;
129
130 if ( qt == null ) {
131 throw new InvalidParameterException( "The quadtree reference parameter 'qt' may not be null." );
132 }
133 this.qt = qt;
134
135 if ( level < 1 ) {
136 level = 1;
137 }
138 this.level = level;
139 if ( indexTable == null || "".equals( indexTable.trim() ) ) {
140 throw new InvalidParameterException(
141 "The Table reference String 'indexTable' may neither be null nor an empty string." );
142 }
143 this.indexTable = indexTable.trim();
144 this.indexItemTable = this.indexTable + "_ITEM ";
145 this.version = version;
146 if ( !loadNodeFromDB() ) {
147 addNodeToDB();
148 }
149 for ( int i = 0; i < fk_subnode.length; ++i ) {
150 if ( fk_subnode[i] == null || "null".equalsIgnoreCase( fk_subnode[i].trim() ) ) {
151 fk_subnode[i] = "";
152 }
153 }
154 qt.addToCache( this );
155 }
156
157 /**
158 * A constructor which reads the envelope from the database.
159 *
160 * @param id
161 * @param qt
162 * @param indexTable
163 * @param jdbc
164 * @param level
165 * @param version
166 * of the quadtree layout to use.
167 * @throws IndexException
168 * if the node with given id could not be read from the db.
169 */
170 public DBNode( String id, DBQuadtree<T> qt, String indexTable, JDBCConnection jdbc, int level,
171 SupportedVersions version ) throws IndexException {
172 this( id, null, qt, indexTable, jdbc, level, version );
173 }
174
175 public boolean insert( T itemKey, Envelope itemEnv )
176 throws IndexException {
177 if ( version == SupportedVersions.ONE ) {
178 return insertWithLayoutOne( itemKey, itemEnv );
179 } else if ( version == SupportedVersions.TWO ) {
180 return insertWithLayoutTwo( itemKey, itemEnv );
181 }
182 return false;
183 }
184
185 public void deleteRange( Envelope envelope ) {
186 if ( level == qt.getDepth() ) {
187 // TODO delete a range from the bottomlevel
188 } else {
189 // TODO delete a range smaller then the depth
190 }
191 }
192
193 /**
194 * @throws UnsupportedOperationException
195 * if the version of this quadtree(node) is not 2.0.0 or higher.
196 */
197 public boolean delete( T itemKey, Envelope itemEnv )
198 throws IndexException {
199 Connection dbConnection = null;
200 DBConnectionPool pool = null;
201 try {
202 if ( version != SupportedVersions.TWO ) {
203 String msg = "Deleting of items is only supported for the quadtree structure with version '2.0.0' or higher";
204 LOG.logError( msg );
205 throw new UnsupportedOperationException( msg );
206 }
207 pool = DBConnectionPool.getInstance();
208 dbConnection = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
209 boolean result = deleteWithLayoutTwo( itemKey, itemEnv, null, dbConnection );
210
211 return result;
212 } catch ( DBPoolException e ) {
213 throw new IndexException( e );
214 } finally {
215 try {
216 if ( pool != null && dbConnection != null ) {
217 pool.releaseConnection( dbConnection, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(),
218 jdbc.getPassword() );
219 }
220 } catch ( DBPoolException e ) {
221 LOG.logError( "Could not release the db Connection after deletion in the quadtree because: "
222 + e.getMessage() );
223 }
224 }
225
226 }
227
228 public boolean update( T itemKey, Envelope newBBox ) {
229 // nottin yet
230 return true;
231 }
232
233 public List<T> query( Envelope searchEnv, List<T> visitor, int currentLevel )
234 throws IndexException {
235 if ( version == SupportedVersions.ONE ) {
236 LOG.logDebug( "Performing query with layout 1" );
237 queryWithLayoutOne( searchEnv, visitor, currentLevel );
238 } else if ( version == SupportedVersions.TWO ) {
239 LOG.logDebug( "Performing query with layout 2" );
240 queryWithLayoutTwo( searchEnv, visitor );
241 }
242 return visitor;
243 }
244
245 public String getId() {
246 return id;
247 }
248
249 /**
250 * @return the envelope (bbox) of this dbNode.
251 */
252 public Envelope getEnvelope() {
253 return envelope;
254 }
255
256 /**
257 * creates a new node with current ID and envelope
258 *
259 * @throws IndexException
260 */
261 private void addNodeToDB()
262 throws IndexException {
263 Connection con = null;
264 DBConnectionPool pool = null;
265 try {
266 pool = DBConnectionPool.getInstance();
267 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
268
269 StringBuilder sb = new StringBuilder( 100 );
270 sb.append( "INSERT INTO " ).append( indexTable );
271 sb.append( " ( ID, MINX, MINY, MAXX , MAXY ) " );
272 sb.append( "VALUES ( ?, ?, ?, ?, ? ) " );
273 PreparedStatement stmt = con.prepareStatement( sb.toString() );
274 stmt.setString( 1, id );
275 stmt.setFloat( 2, (float) envelope.getMin().getX() );
276 stmt.setFloat( 3, (float) envelope.getMin().getY() );
277 stmt.setFloat( 4, (float) envelope.getMax().getX() );
278 stmt.setFloat( 5, (float) envelope.getMax().getY() );
279 stmt.execute();
280 stmt.close();
281 } catch ( Exception e ) {
282 LOG.logError( e.getMessage(), e );
283 throw new IndexException( "could not create node definition at database", e );
284 } finally {
285 try {
286 if ( pool != null && con != null ) {
287 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
288 }
289 } catch ( Exception e1 ) {
290 e1.printStackTrace();
291 }
292 }
293 }
294
295 /**
296 * assignes an item to a node by creating a new row in the JT_QTNODE_ITEM table
297 *
298 */
299 private void assignItem( T itemKey )
300 throws IndexException {
301 Connection con = null;
302 DBConnectionPool pool = null;
303 try {
304 pool = DBConnectionPool.getInstance();
305 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
306
307 StringBuilder sb = new StringBuilder( 100 );
308 sb.append( "INSERT INTO " ).append( indexItemTable );
309 sb.append( "( FK_QTNODE, FK_ITEM ) " ).append( "VALUES ( ?, ? ) " );
310 PreparedStatement stmt = con.prepareStatement( sb.toString() );
311 stmt.setString( 1, id );
312 if ( itemKey instanceof Integer ) {
313 stmt.setInt( 2, ( (Integer) itemKey ).intValue() );
314 } else {
315 stmt.setString( 2, itemKey.toString() );
316 }
317 stmt.execute();
318 stmt.close();
319 } catch ( Exception e ) {
320 LOG.logError( e.getMessage(), e );
321 throw new IndexException( "could not create node definition at database", e );
322 } finally {
323 try {
324 if ( pool != null && con != null ) {
325 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
326 }
327 } catch ( Exception e1 ) {
328 e1.printStackTrace();
329 }
330 }
331 }
332
333 /**
334 * This method inserts the given item into the quadtree. The difference between layout ONE and TWO is, that in
335 * version TWO all nodes which lie inside the item's bbox possess a reference to the item, allthough the idx_table
336 * will be larger, the retrieval of multiple items inside a requested bbox will be a lot faster.
337 *
338 * @param itemKey
339 * to be inserted
340 * @param itemEnv
341 * bbox of the item to be inserted.
342 * @return true if the item was successfully inserted into the quadtree false otherwise.
343 * @throws IndexException
344 * if the item could not be inserted because of an <code>db</code> or <code>sql</code> error.
345 */
346 private boolean insertWithLayoutTwo( T itemKey, Envelope itemEnv )
347 throws IndexException {
348 if ( level != qt.getDepth() ) {
349 if ( !envelope.intersects( itemEnv ) ) {
350 String msg = "The item's envelope: " + itemEnv
351 + " does not intersect with the quadtrees' boundinbox envelope: " + envelope;
352 LOG.logError( msg );
353 throw new IndexException( msg );
354 }
355 // split the envelope of this node into four equal sized quarters
356 Envelope[] envs = split();
357 boolean needsUpdate = false;
358 boolean nodeInserted = false;
359 for ( int i = 0; i < envs.length; i++ ) {
360 if ( envs[i].intersects( itemEnv ) ) {
361 // check which subnodes are intersected by the
362 // items envelope; only these nodes are considered for futher processing
363 if ( "".equals( fk_subnode[i].trim() ) ) {
364 needsUpdate = true;
365 fk_subnode[i] = id + '_' + i;
366 }
367 DBNode<T> node = qt.getFromCache( fk_subnode[i] );
368 if ( node == null ) {
369 node = new DBNode<T>( fk_subnode[i], envs[i], qt, indexTable, jdbc, level + 1, version );
370 }
371 nodeInserted = node.insertWithLayoutTwo( itemKey, itemEnv );
372 }
373 }
374 // if any child node inserted the item, the parent must know about it too, this enhances
375 // the speed of area
376 // querying.
377 if ( nodeInserted ) {
378 assignItem( itemKey );
379 }
380 if ( needsUpdate ) {
381 updateNodeInDB();
382 }
383 return nodeInserted;
384 }
385 assignItem( itemKey );
386 return true;
387 }
388
389 /**
390 * @param itemEnv
391 * the items envelope
392 * @return true if this nodes envelope lies completely inside the items envelope.
393 */
394 private boolean liesWithIn( Envelope itemEnv ) {
395 Position minThis = envelope.getMin();
396 Position maxThis = envelope.getMax();
397 Position minThat = itemEnv.getMin();
398 Position maxThat = itemEnv.getMax();
399 return ( minThis.getX() >= minThat.getX() && maxThis.getX() <= maxThat.getX()
400 && minThis.getY() > minThat.getY() && maxThis.getY() < maxThat.getY() );
401
402 }
403
404 private boolean insertWithLayoutOne( T itemKey, Envelope itemEnv )
405 throws IndexException {
406 if ( level != qt.getDepth() ) {
407 if ( !envelope.intersects( itemEnv ) ) {
408 String msg = "The item's envelope: " + itemEnv
409 + " does not intersect with the quadtrees' boundinbox envelope: " + envelope;
410 LOG.logError( msg );
411 throw new IndexException( msg );
412 }
413 // split the envelope of this node into four equal sized quarters
414 Envelope[] envs = split();
415 boolean needsUpdate = false;
416 int numberOfInsertedSons = 0;
417 for ( int i = 0; i < envs.length; i++ ) {
418 if ( envs[i].intersects( itemEnv ) ) {
419 numberOfInsertedSons++;
420 // check which subnodes are intersected by the
421 // items envelope; only these nodes are considered for futher processing
422 if ( "".equals( fk_subnode[i].trim() ) ) {
423 needsUpdate = true;
424 fk_subnode[i] = id + '_' + i;
425 }
426 DBNode<T> node = qt.getFromCache( fk_subnode[i] );
427 if ( node == null ) {
428 node = new DBNode<T>( fk_subnode[i], envs[i], qt, indexTable, jdbc, level + 1, version );
429 }
430 node.insertWithLayoutOne( itemKey, itemEnv );
431 }
432 }
433 if ( numberOfInsertedSons == 4 ) {
434 assignItem( itemKey );
435 }
436 if ( needsUpdate ) {
437 updateNodeInDB();
438 }
439 } else {
440 assignItem( itemKey );
441 }
442 return true;
443 }
444
445 /**
446 * The enhancement of the second layout is, that every node knows about the items inserted in it's sons. This
447 * information can be used if the bbox of this node lies totally within the requested area, all items of the sons of
448 * this node are added automatically.
449 *
450 * @param searchEnv
451 * the area to get all items for.
452 * @param visitor
453 * the list inwhich the items keys will be added.
454 * @throws IndexException
455 * if a connection to the db failed.
456 */
457 private void queryWithLayoutTwo( Envelope searchEnv, List<T> visitor )
458 throws IndexException {
459 if ( liesWithIn( searchEnv ) || level == qt.getDepth() ) {
460 getAssignedItems( visitor );
461 } else {
462 Envelope[] envs = split();
463 for ( int i = 0; i < envs.length; i++ ) {
464 if ( !"".equals( fk_subnode[i] ) && envs[i].intersects( searchEnv ) ) {
465 // check which subnodes are intersected by the
466 // items envelope; just this nodes
467 // are considered for futher processing
468 DBNode<T> node = new DBNode<T>( fk_subnode[i], envs[i], qt, indexTable, jdbc, level + 1, version );
469 node.queryWithLayoutTwo( searchEnv, visitor );
470 }
471 }
472 }
473
474 }
475
476 private void queryWithLayoutOne( Envelope searchEnv, List<T> visitor, int level )
477 throws IndexException {
478 /*
479 * if ( level == qt.getDepth() || (searchEnv.getWidth() > envelope.getWidth() || searchEnv.getHeight() >
480 * envelope.getHeight()) ) { addAssignedItems( visitor ); } else {
481 */
482 getAssignedItems( visitor );
483 if ( level != qt.getDepth() ) {
484 Envelope[] envs = split();
485 for ( int i = 0; i < envs.length; i++ ) {
486 if ( !"".equals( fk_subnode[i] ) && envs[i].intersects( searchEnv ) ) {
487 // check which subnodes are intersected by the
488 // items envelope; just this nodes
489 // are considered for futher processing
490 DBNode<T> node = new DBNode<T>( fk_subnode[i], envs[i], qt, indexTable, jdbc, level + 1, version );
491 node.queryWithLayoutOne( searchEnv, visitor, level + 1 );
492 }
493 }
494 }
495 }
496
497 /**
498 * The delete method which can handle the layout with version two.
499 *
500 * @param itemKey
501 * the key to be deleted.
502 * @param itemEnv
503 * the bbox of the item.
504 * @param parent
505 * the parent node of this qt-node or <code>null</code> if this is the root node.
506 * @throws IndexException
507 * if somehow the connection to the db is lost or an error occurred while executing the deletion.
508 */
509 private boolean deleteWithLayoutTwo( T itemKey, Envelope itemEnv, DBNode<T> parent, Connection dbConnection )
510 throws IndexException {
511 List<T> items = new ArrayList<T>();
512 // first get all items assigned to this node, and remove the fitting item from the idx-item
513 // join table.
514 getAssignedItems( items );
515 if ( items.contains( itemKey ) ) {
516 if ( deleteItemFromDB( itemKey, dbConnection ) ) {
517 items.remove( itemKey );
518 }
519 }
520 if ( level != qt.getDepth() ) {
521 Envelope[] envs = split();
522 for ( int i = 0; i < fk_subnode.length; ++i ) {
523 if ( !"".equals( fk_subnode[i] ) && envs[i].intersects( itemEnv ) ) {
524 DBNode<T> node = new DBNode<T>( fk_subnode[i], envs[i], qt, indexTable, jdbc, level + 1, version );
525 node.deleteWithLayoutTwo( itemKey, itemEnv, this, dbConnection );
526 }
527 }
528 }
529 // If this is not the root of the quadtree, delete this node from the parent if it is a leaf
530 // and has no items.
531 if ( parent != null ) {
532 if ( items.isEmpty() && !hasSons() ) {
533 if ( deletNodeFromDB( dbConnection ) ) {
534 if ( parent.deleteSon( id, dbConnection ) ) {
535 qt.addToCache( parent );
536 qt.removeFromCache( this );
537 }
538 }
539 }
540 }
541 return true;
542 }
543
544 /**
545 * @param dbConnection
546 * a live connection to the database
547 * @return true if the node has been successfully removed from the database.
548 */
549 private boolean deletNodeFromDB( Connection dbConnection ) {
550 if ( hasSons() ) {
551 LOG.logError( "Trying to delete a node (with id: '" + id + "') which still has sons, this may not happen!" );
552 return false;
553 }
554 StringBuilder deleteStatement = new StringBuilder( 400 );
555 deleteStatement.append( "DELETE FROM " ).append( indexTable );
556 deleteStatement.append( " WHERE ID = '" ).append( id ).append( "'" );
557 LOG.logDebug( "Trying to delete dbnode with id='" + id + "'. The sql statement is as follows:\n"
558 + deleteStatement.toString() );
559 Statement stmt = null;
560 ResultSet rs = null;
561 try {
562 stmt = dbConnection.createStatement();
563 rs = stmt.executeQuery( deleteStatement.toString() );
564 } catch ( SQLException e ) {
565 LOG.logDebug( "Could not delete the dbNode with id='" + id + "' because: " + e.getMessage() );
566 return false;
567 } finally {
568 try {
569 if ( rs != null ) {
570 rs.close();
571 }
572 if ( stmt != null ) {
573 stmt.close();
574 }
575 } catch ( SQLException e ) {
576 LOG.logDebug( "An error occurred while trying to close statement and/or resultset because: "
577 + e.getMessage() );
578 }
579
580 }
581 return true;
582 }
583
584 /**
585 * @return true if this node has a son (e.g. one of the fk_subnodes is not empty ).
586 */
587 private boolean hasSons() {
588 return !( "".equals( fk_subnode[0] ) && "".equals( fk_subnode[1] ) && "".equals( fk_subnode[2] ) && "".equals( fk_subnode[3] ) );
589
590 }
591
592 /**
593 * Deletes the specifies item from the quadtree, e.g. removing it from the idx_table_item join table, where the
594 * FK_QTNODE equals this.id and the FK_ITEM = itemKey.
595 *
596 * @param itemKey
597 * to be deleted
598 * @param dbConnection
599 * used to execute the query.
600 * @return true if the item was deleted from the database, false otherwhise.
601 */
602 private boolean deleteItemFromDB( T itemKey, Connection dbConnection ) {
603 if ( itemKey == null ) {
604 LOG.logDebug( "Trying to delete an itemkey which is null, this may not be (current node id='" + id + "')" );
605 return false;
606 }
607 StringBuilder delItemStatement = new StringBuilder( 400 );
608 delItemStatement.append( "DELETE FROM " ).append( indexItemTable );
609 delItemStatement.append( " WHERE FK_QTNODE = '" ).append( id ).append( "'" );
610 delItemStatement.append( " AND FK_ITEM = " );
611 if ( itemKey instanceof String ) {
612 delItemStatement.append( "'" );
613 }
614 delItemStatement.append( itemKey );
615 if ( itemKey instanceof String ) {
616 delItemStatement.append( "'" );
617 }
618 LOG.logDebug( "Trying to delete item with key='" + itemKey + "' from node with id='" + id
619 + "'. The sql statement is as follows:\n" + delItemStatement.toString() );
620 Statement stmt = null;
621 ResultSet rs = null;
622 try {
623 stmt = dbConnection.createStatement();
624 rs = stmt.executeQuery( delItemStatement.toString() );
625 } catch ( SQLException e ) {
626 LOG.logDebug( "Could not delete the item with key='" + itemKey + "' from node with id='" + id
627 + "' because: " + e.getMessage() );
628 return false;
629 } finally {
630 try {
631 if ( rs != null ) {
632 rs.close();
633 }
634 if ( stmt != null ) {
635 stmt.close();
636 }
637 } catch ( SQLException e ) {
638 LOG.logDebug( "An error occurred while trying to close statement and/or resultset because: "
639 + e.getMessage() );
640 }
641
642 }
643
644 return true;
645
646 }
647
648 /**
649 * Deletes a son from the database, e.g. perform an update to null on the idx_table. And if successfull also sets
650 * the fk_subnode to an empty string.
651 *
652 * @param sonsID
653 * which must be set to null.
654 * @param dbConnection
655 * which will be used to perform the query.
656 * @return true if the son was deleted.
657 */
658 private boolean deleteSon( String sonsID, Connection dbConnection ) {
659 if ( sonsID == null || "".equals( sonsID.trim() ) ) {
660 LOG.logDebug( "Trying to delete a son with an id which is null, this may not be (parent node id='" + id
661 + "')" );
662 return false;
663 }
664 for ( int i = 0; i < fk_subnode.length; ++i ) {
665 if ( fk_subnode[i].trim().equals( sonsID.trim() ) ) {
666 StringBuilder delSonStatement = new StringBuilder( 400 );
667 delSonStatement.append( "UPDATE " ).append( indexTable );
668 delSonStatement.append( " SET FK_SUBNODE" ).append( ( i + 1 ) ).append( " = 'null'" );
669 delSonStatement.append( " WHERE ID = '" ).append( id ).append( "'" );
670
671 LOG.logDebug( "Trying to delete son with id='" + sonsID + "' from (parent) node with id='" + this.id
672 + "'. The sql statement is as follows:\n" + delSonStatement.toString() );
673 Statement stmt = null;
674 ResultSet rs = null;
675 try {
676 stmt = dbConnection.createStatement();
677 rs = stmt.executeQuery( delSonStatement.toString() );
678 } catch ( SQLException e ) {
679 LOG.logDebug( "Could not delete son with id='" + sonsID + "' from (parent) node with id='"
680 + this.id + "' because: " + e.getMessage() );
681 return false;
682 } finally {
683 try {
684 if ( rs != null ) {
685 rs.close();
686 }
687 if ( stmt != null ) {
688 stmt.close();
689 }
690 } catch ( SQLException e ) {
691 LOG.logDebug( "An error occurred while trying to close statement and/or resultset because: "
692 + e.getMessage() );
693 }
694
695 }
696 fk_subnode[i] = "";
697 return true;
698 }
699
700 }
701
702 LOG.logDebug( "It seems this (parent) node with id='" + id + "' has no son with id='" + sonsID
703 + "', all sons of this node are:\n " + "- fk_subnode[0]='" + fk_subnode[0] + "'\n "
704 + "- fk_subnode[1]='" + fk_subnode[1] + "'\n " + "- fk_subnode[2]='" + fk_subnode[2] + "'\n "
705 + "- fk_subnode[3]='" + fk_subnode[3] + "'" );
706 return false;
707 }
708
709 /**
710 * load all Node parameters from the database.
711 *
712 * @return true if a node with current ID is already available from the database and the parameters have been read
713 * successfully.
714 *
715 */
716 private boolean loadNodeFromDB()
717 throws IndexException {
718 Connection con = null;
719 DBConnectionPool pool = null;
720 boolean available = true;
721 try {
722 pool = DBConnectionPool.getInstance();
723 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
724
725 StringBuilder sb = new StringBuilder( 100 );
726 sb.append( "Select * from " ).append( indexTable );
727 sb.append( " where ID = '" ).append( id ).append( "'" );
728
729 Statement stmt = con.createStatement();
730 ResultSet rs = stmt.executeQuery( sb.toString() );
731 if ( rs.next() ) {
732 double minx = rs.getFloat( "MINX" );
733 double miny = rs.getFloat( "MINY" );
734 double maxx = rs.getFloat( "MAXX" );
735 double maxy = rs.getFloat( "MAXY" );
736 envelope = GeometryFactory.createEnvelope( minx, miny, maxx, maxy, null );
737 fk_subnode[0] = rs.getString( "FK_SUBNODE1" );
738 fk_subnode[1] = rs.getString( "FK_SUBNODE2" );
739 fk_subnode[2] = rs.getString( "FK_SUBNODE3" );
740 fk_subnode[3] = rs.getString( "FK_SUBNODE4" );
741 } else {
742 available = false;
743 }
744 rs.close();
745 stmt.close();
746 } catch ( Exception e ) {
747 LOG.logError( e.getMessage(), e );
748 throw new IndexException( "could not load node definition from database", e );
749 } finally {
750 try {
751 if ( con != null && pool != null ) {
752 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
753 }
754 } catch ( Exception e1 ) {
755 e1.printStackTrace();
756 }
757 }
758 return available;
759 }
760
761 /**
762 * updates the database representation of the current node
763 *
764 * @throws IndexException
765 */
766 private void updateNodeInDB()
767 throws IndexException {
768 Connection con = null;
769 DBConnectionPool pool = null;
770 try {
771 pool = DBConnectionPool.getInstance();
772 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
773
774 StringBuilder sb = new StringBuilder( 250 );
775 sb.append( "UPDATE " ).append( indexTable ).append( " set " );
776 boolean submit = false;
777 for ( int i = 0; i < fk_subnode.length; i++ ) {
778 if ( !"".equals( fk_subnode[i] ) ) {
779 sb.append( " FK_SUBNODE" ).append( i + 1 ).append( "='" );
780 sb.append( fk_subnode[i] ).append( "' ," );
781 submit = true;
782 }
783 }
784 if ( submit ) {
785 // just execute update if at least one sub node != null
786 sb = new StringBuilder( sb.substring( 0, sb.length() - 1 ) );
787 sb.append( " where ID = '" ).append( id ).append( "'" );
788 Statement stmt = con.createStatement();
789 stmt.execute( sb.toString() );
790 stmt.close();
791 }
792 } catch ( Exception e ) {
793 LOG.logError( e.getMessage(), e );
794 throw new IndexException( "could not update node definition at database " + "for node: " + id, e );
795 } finally {
796 try {
797 if ( pool != null && con != null ) {
798 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
799 }
800 } catch ( Exception e1 ) {
801 e1.printStackTrace();
802 }
803 }
804 qt.addToCache( this );
805 }
806
807 /**
808 * Get the item (IDs) assigned to this node and stores them in the given list
809 *
810 * @param visitor
811 * @throws IndexException
812 */
813 private void getAssignedItems( List<T> visitor )
814 throws IndexException {
815
816 Connection con = null;
817 DBConnectionPool pool = null;
818 try {
819 pool = DBConnectionPool.getInstance();
820 con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
821
822 StringBuilder sb = new StringBuilder( 100 );
823 sb.append( "SELECT DISTINCT FK_ITEM from " ).append( indexTable ).append( "_ITEM" );
824 sb.append( " where " ).append( "FK_QTNODE = '" ).append( id ).append( "'" );
825 Statement stmt = con.createStatement();
826 ResultSet rs = stmt.executeQuery( sb.toString() );
827
828 while ( rs.next() ) {
829 Object result = rs.getObject( 1 );
830 if ( result != null ) {
831 T s = (T) result;
832 if ( !visitor.contains( s ) ) {
833 visitor.add( s );
834 }
835 } else {
836 LOG.logDebug( "Found a node (id='" + id + "') with a null value." );
837 }
838 }
839 stmt.close();
840 } catch ( DBPoolException e ) {
841 throw new IndexException( "Database QuadTree could not acquire sons of a node because: " + e.getMessage() );
842 } catch ( SQLException e ) {
843 throw new IndexException( "Database QuadTree could not acquire sons of a node because: " + e.getMessage() );
844 } finally {
845 try {
846 if ( pool != null && con != null ) {
847 pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
848 }
849 } catch ( Exception e1 ) {
850 e1.printStackTrace();
851 }
852 }
853 }
854
855 private Envelope[] split() {
856 Envelope[] envs = new Envelope[4];
857 double nW = envelope.getWidth() / 2d;
858 double nH = envelope.getHeight() / 2d;
859
860 envs[0] = GeometryFactory.createEnvelope( envelope.getMin().getX(), envelope.getMin().getY(),
861 envelope.getMin().getX() + nW, envelope.getMin().getY() + nH, null );
862 envs[1] = GeometryFactory.createEnvelope( envelope.getMin().getX() + nW, envelope.getMin().getY(),
863 envelope.getMin().getX() + ( 2 * nW ), envelope.getMin().getY() + nH,
864 null );
865 envs[2] = GeometryFactory.createEnvelope( envelope.getMin().getX() + nW, envelope.getMin().getY() + nH,
866 envelope.getMin().getX() + ( 2 * nW ), envelope.getMin().getY()
867 + ( 2 * nH ), null );
868 envs[3] = GeometryFactory.createEnvelope( envelope.getMin().getX(), envelope.getMin().getY() + nH,
869 envelope.getMin().getX() + nW, envelope.getMin().getY() + ( 2 * nH ),
870 null );
871
872 return envs;
873 }
874
875 // /**
876 // * @param itemKey
877 // * the key of the item to get the bbox for.
878 // * @return the envelope of item with itemKey, currently stored in the quadtree.
879 // */
880 // public Envelope getBoundingBoxForItem( T itemKey ) {
881 // return null;
882 // }
883
884 }