001    //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_testing/src/org/deegree/ogcwebservices/csw/manager/HarvestRepository.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.ogcwebservices.csw.manager;
037    
038    import java.io.IOException;
039    import java.io.InputStream;
040    import java.net.URI;
041    import java.net.URISyntaxException;
042    import java.net.URL;
043    import java.sql.Connection;
044    import java.sql.PreparedStatement;
045    import java.sql.ResultSet;
046    import java.sql.SQLException;
047    import java.sql.Statement;
048    import java.sql.Timestamp;
049    import java.util.ArrayList;
050    import java.util.Date;
051    import java.util.Iterator;
052    import java.util.List;
053    import java.util.Properties;
054    
055    import org.deegree.datatypes.time.TimeDuration;
056    import org.deegree.framework.log.ILogger;
057    import org.deegree.framework.log.LoggerFactory;
058    import org.deegree.io.DBConnectionPool;
059    import org.deegree.io.DBPoolException;
060    import org.deegree.io.JDBCConnection;
061    
062    /**
063     * A harvest repository is a database that stores harvest requests and that caches basic record
064     * informations to optimizes harvesting of large sources (e.g. other catalogues). This class
065     * encapsulates access to this database.
066     *
067     *
068     * @version $Revision: 19482 $
069     * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
070     * @author last edited by: $Author: lbuesching $
071     *
072     * @version $Revision: 19482 $, $Date: 2009-09-03 10:06:22 +0200 (Do, 03 Sep 2009) $
073     */
074    class HarvestRepository {
075    
076        private static final ILogger LOG = LoggerFactory.getLogger( HarvestRepository.class );
077    
078        private static URL url = null;
079        static {
080            url = HarvestRepository.class.getResource( "/harvestrepository.properties" );
081            if ( url == null ) {
082                url = HarvestRepository.class.getResource( "harvestrepository.properties" );
083            }
084        }
085    
086        private static HarvestRepository repository = null;
087    
088        private static DBConnectionPool pool = DBConnectionPool.getInstance();
089    
090        private JDBCConnection jdbc = null;
091    
092        private Properties prop = null;
093    
094        // possible metadata source types
095        static enum ResourceType {
096            /**
097             *
098             */
099            catalogue, /**
100                         *
101                         */
102            service, /**
103                         *
104                         */
105            csw_profile, /**
106                             *
107                             */
108            FGDC, /**
109                     *
110                     */
111            dublincore, /**
112                         *
113                         */
114            unknown
115        }
116    
117        /**
118         * returns an instance of a <code>HarvestRepository</code>
119         *
120         * @return an instance of a <code>HarvestRepository</code>
121         * @throws IOException
122         */
123        static HarvestRepository getInstance()
124                                throws IOException {
125            if ( repository == null ) {
126                repository = new HarvestRepository();
127            }
128            return repository;
129        }
130    
131        /**
132         *
133         */
134        private HarvestRepository() throws IOException {
135            prop = new Properties();
136            InputStream is = url.openStream();
137            prop.load( is );
138            is.close();
139            jdbc = new JDBCConnection( prop.getProperty( "harvester.Driver" ), prop.getProperty( "harvester.Url" ),
140                                       prop.getProperty( "harvester.User" ), prop.getProperty( "harvester.Password" ),
141                                       null, null, null );
142        }
143    
144        /**
145         * stores a harvest request
146         *
147         * @param request
148         * @throws DBPoolException
149         * @throws SQLException
150         */
151        synchronized void storeRequest( Harvest request )
152                                throws DBPoolException, SQLException {
153    
154            LOG.logDebug( "storing harvest request into harvest repository ..." );
155    
156            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
157            try {
158                con.setAutoCommit( false );
159            } catch ( Exception ignore ) {
160                // it's ignored
161            }
162    
163            try {
164                // insert into harvestsource table
165                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.storeRequest1" ) );
166                ps.setString( 1, request.getSource().toASCIIString() );
167                TimeDuration td = request.getHarvestInterval();
168                if ( td != null ) {
169                    ps.setLong( 2, td.getAsMilliSeconds() / 1000 );
170                } else {
171                    ps.setLong( 2, -1 );
172                }
173                ps.setTimestamp( 3, new Timestamp( request.getStartTimestamp().getTime() ) );
174                ps.setBoolean( 4, false );
175                if ( request.getResourceType() == null ) {
176                    ps.setString( 5, "unknown" );
177                } else {
178                    ps.setString( 5, request.getResourceType().toASCIIString() );
179                }
180                ps.execute();
181                ps.close();
182                Statement stmt = con.createStatement();
183                ResultSet rs = stmt.executeQuery( "select max(id) from harvestsource" );
184                rs.next();
185                int id1 = rs.getInt( 1 );
186                rs.close();
187                stmt.close();
188    
189                // insert into responsehandler table and assigns to harvestsource by
190                // performing an insert into jt_source_responsehandler
191                List<URI> list = request.getResponseHandler();
192                for ( Iterator<URI> iter = list.iterator(); iter.hasNext(); ) {
193                    URI handler = iter.next();
194                    ps = con.prepareStatement( prop.getProperty( "harvester.storeRequest2" ) );
195                    ps.setString( 1, handler.toASCIIString() );
196                    ps.setBoolean( 2, handler.toASCIIString().toLowerCase().startsWith( "mailto:" ) );
197                    ps.execute();
198                    ps.close();
199    
200                    stmt = con.createStatement();
201                    rs = stmt.executeQuery( "select max(id) from responsehandler" );
202                    rs.next();
203                    int id2 = rs.getInt( 1 );
204                    rs.close();
205                    stmt.close();
206    
207                    ps = con.prepareStatement( prop.getProperty( "harvester.storeRequest3" ) );
208                    ps.setInt( 1, id1 );
209                    ps.setInt( 2, id2 );
210                    ps.execute();
211                    ps.close();
212                }
213    
214                con.commit();
215            } catch ( SQLException e ) {
216                con.rollback();
217                e.printStackTrace();
218                throw new SQLException( getClass().getName() + " storeRequest(..) " + e.getMessage() );
219            } catch ( Exception e ) {
220                con.rollback();
221                e.printStackTrace();
222                throw new SQLException( getClass().getName() + " storeRequest(..) "
223                                        + "could not insert harvest request into repository: " + e.getMessage() );
224            } finally {
225                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
226            }
227        }
228    
229        /**
230         * drops a request from the backend
231         *
232         * @param source
233         * @throws DBPoolException
234         * @throws SQLException
235         */
236        synchronized void dropRequest( URI source )
237                                throws DBPoolException, SQLException {
238    
239            LOG.logDebug( "dropping harvest request from harvest repository ..." );
240    
241            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
242            try {
243                con.setAutoCommit( false );
244            } catch ( Exception ignore ) {
245                // it's ignored
246            }
247    
248            try {
249                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.dropRequest1" ) );
250                ps.setString( 1, source.toASCIIString() );
251                ResultSet rs = ps.executeQuery();
252                rs.next();
253                int id1 = rs.getInt( 1 );
254                rs.close();
255                ps = con.prepareStatement( prop.getProperty( "harvester.dropRequest2" ) );
256                ps.setInt( 1, id1 );
257                rs = ps.executeQuery();
258                List<Integer> handlers = new ArrayList<Integer>();
259                while ( rs.next() ) {
260                    handlers.add( rs.getInt( 1 ) );
261                }
262                rs.close();
263                ps.close();
264                // remove assigned entries from jointable
265                ps = con.prepareStatement( prop.getProperty( "harvester.dropRequest3" ) );
266                ps.setInt( 1, id1 );
267                ps.execute();
268                // remove assigend entries from reponse handler table
269                for ( int i = 0; i < handlers.size(); i++ ) {
270                    Integer id = handlers.get( i );
271                    ps = con.prepareStatement( prop.getProperty( "harvester.dropRequest4" ) );
272                    ps.setInt( 1, id.intValue() );
273                    ps.execute();
274                    ps.close();
275                }
276                // remove records from cache table
277                ps = con.prepareStatement( prop.getProperty( "harvester.dropRequest5" ) );
278                ps.setInt( 1, id1 );
279                ps.execute();
280                ps.close();
281    
282                // remove root from harvest source table
283                ps = con.prepareStatement( prop.getProperty( "harvester.dropRequest6" ) );
284                ps.setInt( 1, id1 );
285                ps.execute();
286                ps.close();
287    
288                con.commit();
289    
290            } catch ( SQLException e ) {
291                con.rollback();
292                throw e;
293            } catch ( Exception e ) {
294                con.rollback();
295                throw new SQLException( "could not frop request from repository: " + e.getMessage() );
296            } finally {
297                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
298            }
299    
300        }
301    
302        /**
303         * returns all sources registered to a harvest process
304         *
305         * @return all sources registered to a harvest process
306         * @throws DBPoolException
307         * @throws SQLException
308         * @throws URISyntaxException
309         */
310        synchronized List<URI> getSources()
311                                throws DBPoolException, SQLException, URISyntaxException {
312    
313            LOG.logDebug( "reading sources from harvest repository ..." );
314    
315            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
316            List<URI> sources = new ArrayList<URI>();
317            try {
318                Statement stmt = con.createStatement();
319                ResultSet rs = stmt.executeQuery( prop.getProperty( "harvester.getSources" ) );
320                while ( rs.next() ) {
321                    sources.add( new URI( rs.getString( 1 ) ) );
322                }
323                rs.close();
324                stmt.close();
325            } catch ( SQLException e ) {
326                throw e;
327            } catch ( URISyntaxException e ) {
328                throw e;
329            } finally {
330                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
331            }
332    
333            return sources;
334        }
335    
336        /**
337         * returns the type of the passed source
338         *
339         * @param source
340         * @return the type of the passed source
341         * @throws DBPoolException
342         * @throws SQLException
343         */
344        synchronized ResourceType getSourceType( URI source )
345                                throws DBPoolException, SQLException {
346    
347            LOG.logDebug( "reading sources type for source: " + source );
348    
349            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
350            String s = null;
351    
352            try {
353                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getSourceType" ) );
354                ps.setString( 1, source.toASCIIString() );
355                ResultSet rs = ps.executeQuery();
356                rs.next();
357                s = rs.getString( 1 );
358                rs.close();
359                ps.close();
360            } catch ( SQLException e ) {
361                throw e;
362            } finally {
363                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
364            }
365    
366            ResourceType st = ResourceType.unknown;
367    
368            if ( "csw:profile".equals( s ) ) {
369                st = ResourceType.csw_profile;
370            } else if ( "dublincore".equals( s ) ) {
371                st = ResourceType.dublincore;
372            } else if ( "FGDC".equals( s ) ) {
373                st = ResourceType.FGDC;
374            } else if ( "service".equals( s ) ) {
375                st = ResourceType.service;
376            } else if ( "catalogue".equals( s ) ) {
377                st = ResourceType.catalogue;
378            } else  if ( s.equals( "http://www.isotc211.org/schemas/2005/gmd" ) ) {
379                st = ResourceType.csw_profile;
380            }
381    
382            return st;
383        }
384    
385        /**
386         * returns true if last harvesting iteration for the passed source has been successful
387         *
388         * @param source
389         *
390         * @return <code>true</code> if last harvesting iteration for the passed source has been
391         *         successful
392         * @throws DBPoolException
393         * @throws SQLException
394         */
395        synchronized boolean getStatus( URI source )
396                                throws DBPoolException, SQLException {
397    
398            LOG.logDebug( "reading sources status for source: " + source );
399    
400            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
401            boolean status = false;
402    
403            try {
404                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getStatus" ) );
405                ps.setString( 1, source.toASCIIString() );
406                ResultSet rs = ps.executeQuery();
407                rs.next();
408                status = rs.getBoolean( 1 );
409                rs.close();
410                ps.close();
411            } catch ( SQLException e ) {
412                throw e;
413            } finally {
414                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
415            }
416    
417            return status;
418        }
419    
420        /**
421         * returns the <code>Date</code> a source has been harvested successful the last time
422         *
423         * @param source
424         * @return the <code>Date</code> a source has been harvested successful the last time
425         * @throws DBPoolException
426         * @throws SQLException
427         */
428        synchronized Date getLastHarvestingTimestamp( URI source )
429                                throws DBPoolException, SQLException {
430    
431            LOG.logDebug( "reading sources last harvesting timestamp for source: " + source );
432    
433            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
434            Date date = null;
435            try {
436                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getLastHarvestingTimestamp" ) );
437                ps.setString( 1, source.toASCIIString() );
438                ResultSet rs = ps.executeQuery();
439                rs.next();
440                Timestamp ts = rs.getTimestamp( 1 );
441                rs.close();
442                ps.close();
443                if ( ts != null ) {
444                    date = new Date( ts.getTime() );
445                }
446            } catch ( SQLException e ) {
447                throw e;
448            } finally {
449                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
450            }
451            return date;
452        }
453    
454        /**
455         * sets the timestamp when a source has been harvested successfully for the last time
456         *
457         * @param source
458         * @param date
459         * @throws DBPoolException
460         * @throws SQLException
461         */
462        synchronized void setLastHarvestingTimestamp( URI source, Date date )
463                                throws DBPoolException, SQLException {
464    
465            LOG.logDebug( "set timestamp for source: " + source + " last harvesting" );
466    
467            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
468            try {
469                con.setAutoCommit( false );
470            } catch ( Exception ignore ) {
471                // it's ignored
472            }
473            try {
474                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.setLastHarvestingTimestamp" ) );
475                ps.setTimestamp( 1, new Timestamp( date.getTime() ) );
476                ps.setString( 2, source.toASCIIString() );
477                ps.execute();
478                ps.close();
479                con.commit();
480            } catch ( SQLException e ) {
481                con.rollback();
482                throw e;
483            } finally {
484                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
485            }
486        }
487    
488        /**
489         * returns the next Date a source shall be harvested
490         *
491         * @param source
492         * @return the next Date a source shall be harvested
493         * @throws DBPoolException
494         * @throws SQLException
495         */
496        synchronized Date getNextHarvestingTimestamp( URI source )
497                                throws DBPoolException, SQLException {
498            LOG.logDebug( "reading timestamp for source: " + source + " next harvesting" );
499    
500            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
501            Date date = null;
502            try {
503                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getNextHarvestingTimestamp" ) );
504                ps.setString( 1, source.toASCIIString() );
505                ResultSet rs = ps.executeQuery();
506                rs.next();
507                Timestamp ts = rs.getTimestamp( 1 );
508                rs.close();
509                ps.close();
510                date = new Date( ts.getTime() );
511            } catch ( SQLException e ) {
512                throw e;
513            } finally {
514                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
515            }
516            return date;
517        }
518    
519        /**
520         * sets the next date a source shall be harvested
521         *
522         * @param source
523         * @param date
524         * @throws DBPoolException
525         * @throws SQLException
526         */
527        synchronized void setNextHarvestingTimestamp( URI source, Date date )
528                                throws DBPoolException, SQLException {
529    
530            LOG.logDebug( "set timestamp for source: " + source + " last harvesting" );
531    
532            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
533            try {
534                con.setAutoCommit( false );
535            } catch ( Exception ignore ) {
536                // it's ignored
537            }
538            try {
539                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.setNextHarvestingTimestamp" ) );
540                ps.setTimestamp( 1, new Timestamp( date.getTime() ) );
541                ps.setString( 2, source.toASCIIString() );
542                ps.execute();
543                ps.close();
544                con.commit();
545            } catch ( SQLException e ) {
546                con.rollback();
547            } finally {
548                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
549            }
550    
551        }
552    
553        /**
554         * returns the interval in
555         *
556         * @param source
557         * @return the interval
558         * @throws DBPoolException
559         * @throws SQLException
560         */
561        synchronized long getHarvestInterval( URI source )
562                                throws DBPoolException, SQLException {
563    
564            LOG.logDebug( "reading harvest interval for source: " + source );
565    
566            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
567            long interval = 0;
568            try {
569                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getHarvestInterval" ) );
570                ps.setString( 1, source.toASCIIString() );
571                ResultSet rs = ps.executeQuery();
572                rs.next();
573                interval = rs.getLong( 1 ) * 1000l;
574                rs.close();
575                ps.close();
576            } catch ( SQLException e ) {
577                throw e;
578            } finally {
579                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
580            }
581    
582            return interval;
583        }
584    
585        /**
586         * returns a list
587         *
588         * @param source
589         * @return the list
590         * @throws DBPoolException
591         * @throws SQLException
592         * @throws URISyntaxException
593         */
594        synchronized List<ResponseHandler> getResponseHandlers( URI source )
595                                throws DBPoolException, SQLException, URISyntaxException {
596    
597            LOG.logDebug( "reading response handler for source: " + source );
598    
599            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
600            List<ResponseHandler> list = new ArrayList<ResponseHandler>();
601            try {
602                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getResponseHandlers1" ) );
603                ps.setString( 1, source.toASCIIString() );
604                ResultSet rs = ps.executeQuery();
605                rs.next();
606                int id1 = rs.getInt( 1 );
607                rs.close();
608                ps.close();
609    
610                ps = con.prepareStatement( prop.getProperty( "harvester.getResponseHandlers2" ) );
611                ps.setInt( 1, id1 );
612                rs = ps.executeQuery();
613                StringBuffer sb = new StringBuffer( " (" );
614                int kk = 0;
615                while ( rs.next() ) {
616                    kk++;
617                    sb.append( rs.getInt( 1 ) ).append( ',' );
618                }
619                rs.close();
620                ps.close();
621    
622                if ( kk > 0 ) {
623                    // just access response handler informations if available
624                    String s = sb.substring( 0, sb.length() - 1 ) + ')';
625                    ps = con.prepareStatement( prop.getProperty( "harvester.getResponseHandlers3" ) + s );
626                    rs = ps.executeQuery();
627    
628                    while ( rs.next() ) {
629                        String addr = rs.getString( 1 );
630                        boolean isMail = rs.getBoolean( 2 );
631                        list.add( new ResponseHandler( new URI( addr ), isMail ) );
632                    }
633                    rs.close();
634                    ps.close();
635                }
636            } catch ( SQLException e ) {
637                throw e;
638            } catch ( URISyntaxException e ) {
639                throw e;
640            } finally {
641                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
642            }
643    
644            return list;
645        }
646    
647        /**
648         * returns a <code>Record</code> from the harvesters cache. A instance of a
649         * <code>Record</code> includes its fileIdentifier,the datestamp when it has been changed for
650         * the last time and the source it belongs too.
651         *
652         * @param source
653         * @param fileIdentifier
654         * @return a <code>Record</code> from the harvesters cache
655         * @throws DBPoolException
656         * @throws SQLException
657         */
658        synchronized Record getRecordByID( URI source, String fileIdentifier )
659                                throws DBPoolException, SQLException {
660    
661            LOG.logDebug( "reading record: " + fileIdentifier + " from harvest cache" );
662    
663            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
664            Record record = null;
665            try {
666                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getRecordByID1" ) );
667                ps.setString( 1, source.toASCIIString() );
668                ResultSet rs = ps.executeQuery();
669                rs.next();
670                int id = rs.getInt( 1 );
671                rs.close();
672                ps.close();
673    
674                ps = con.prepareStatement( prop.getProperty( "harvester.getRecordByID2" ) );
675                ps.setInt( 1, id );
676                ps.setString( 2, fileIdentifier );
677                rs = ps.executeQuery();
678    
679                if ( rs.next() ) {
680                    Date date = rs.getDate( 1 );
681                    record = new Record( id, date, fileIdentifier, source );
682                }
683                rs.close();
684                ps.close();
685            } catch ( SQLException e ) {
686                throw e;
687            } finally {
688                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
689            }
690    
691            return record;
692        }
693    
694        /**
695         * stores a record into the cache table used by the harvester
696         *
697         * @param record
698         * @throws DBPoolException
699         * @throws SQLException
700         */
701        synchronized void storeRecord( Record record )
702                                throws DBPoolException, SQLException {
703    
704            LOG.logDebug( "storing record in cache; fileIdentifier: " + record.getFileIdentifier() );
705    
706            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
707            try {
708                con.setAutoCommit( false );
709            } catch ( Exception ignore ) {
710                // it's ignored
711            }
712            try {
713                String fid = record.getFileIdentifier();
714                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.storeRecord1" ) );
715                ps.setString( 1, fid );
716                ResultSet rs = ps.executeQuery();
717                rs.next();
718                int count = rs.getInt( 1 );
719                if ( count == 0 ) {
720                    ps = con.prepareStatement( prop.getProperty( "harvester.storeRecord2" ) );
721                    ps.setInt( 1, getSourceID( record.getSource() ) );
722                    ps.setString( 2, fid );
723                    ps.setTimestamp( 3, new Timestamp( record.getDatestamp().getTime() ) );
724                    ps.execute();
725                    ps.close();
726    
727                    con.commit();
728                }
729    
730            } catch ( SQLException e ) {
731                con.rollback();
732                throw e;
733            } catch ( Exception e ) {
734                con.rollback();
735                throw new SQLException( "could not insert harvest request " + "into repository: " + e.getMessage() );
736            } finally {
737                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
738            }
739    
740        }
741    
742        /**
743         * updates a record within the cache table used by the harvester
744         *
745         * @param record
746         * @throws DBPoolException
747         * @throws SQLException
748         */
749        synchronized void updateRecord( Record record )
750                                throws DBPoolException, SQLException {
751    
752            LOG.logDebug( "updating record in cache; fileIdentifier: " + record.getFileIdentifier() );
753    
754            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
755            try {
756                con.setAutoCommit( false );
757            } catch ( Exception ignore ) {
758                // it's ignored
759            }
760            try {
761                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.updateRecord" ) );
762                ps.setDate( 1, new java.sql.Date( record.getDatestamp().getTime() ) );
763                ps.setString( 2, record.getFileIdentifier() );
764                ps.setInt( 3, record.getSourceId() );
765                ps.execute();
766                ps.close();
767    
768                con.commit();
769    
770            } catch ( SQLException e ) {
771                con.rollback();
772                throw e;
773            } catch ( Exception e ) {
774                con.rollback();
775                throw new SQLException( "could not insert harvest request " + "into repository: " + e.getMessage() );
776            } finally {
777                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
778            }
779    
780        }
781    
782        /**
783         * drops a record from the cache table used by the harvester
784         *
785         * @param record
786         * @throws DBPoolException
787         * @throws SQLException
788         */
789        synchronized void dropRecord( Record record )
790                                throws DBPoolException, SQLException {
791    
792            LOG.logDebug( "deleting record from cache; fileIdentifier: " + record.getFileIdentifier() );
793    
794            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
795            try {
796                con.setAutoCommit( false );
797            } catch ( Exception ignore ) {
798                // it's ignored
799            }
800            try {
801    
802                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.dropRecord" ) );
803                ps.setString( 1, record.getFileIdentifier() );
804                ps.setInt( 2, record.getSourceId() );
805                ps.execute();
806                ps.close();
807    
808                con.commit();
809    
810            } catch ( SQLException e ) {
811                con.rollback();
812                throw e;
813            } catch ( Exception e ) {
814                con.rollback();
815                throw new SQLException( "could not insert harvest request " + "into repository: " + e.getMessage() );
816            } finally {
817                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
818            }
819    
820        }
821    
822        /**
823         * returns fileidentifiers of all records assigend to a source from the harvest cache
824         *
825         * @param source
826         * @return fileidentifiers of all records assigend to a source from the harvest cache
827         * @throws DBPoolException
828         * @throws SQLException
829         */
830        synchronized List<String> getAllRecords( URI source )
831                                throws DBPoolException, SQLException {
832    
833            LOG.logDebug( "getting list of all record fileidentifiers for source: " + source + " from cache" );
834    
835            List<String> fileIds = new ArrayList<String>( 10000 );
836    
837            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
838            try {
839                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getAllRecords1" ) );
840                ps.setString( 1, source.toASCIIString() );
841                ResultSet rs = ps.executeQuery();
842                rs.next();
843                int id = rs.getInt( 1 );
844                rs.close();
845                ps.close();
846    
847                ps = con.prepareStatement( prop.getProperty( "harvester.getAllRecords2" ) );
848                ps.setInt( 1, id );
849                rs = ps.executeQuery();
850                while ( rs.next() ) {
851                    fileIds.add( rs.getString( 1 ) );
852                }
853                rs.close();
854                ps.close();
855            } catch ( SQLException e ) {
856                throw e;
857            } finally {
858                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
859            }
860    
861            return fileIds;
862        }
863    
864        /**
865         * returns the row ID of the passed source
866         *
867         * @param source
868         * @return the row ID of the passed source
869         * @throws DBPoolException
870         * @throws SQLException
871         */
872        synchronized int getSourceID( URI source )
873                                throws DBPoolException, SQLException {
874            LOG.logDebug( "reading row ID of source: " + source );
875    
876            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
877            int id = -1;
878            try {
879                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.getSourceID" ) );
880                ps.setString( 1, source.toASCIIString() );
881                ResultSet rs = ps.executeQuery();
882                rs.next();
883                id = rs.getInt( 1 );
884                rs.close();
885                ps.close();
886            } catch ( SQLException e ) {
887                throw e;
888            } finally {
889                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
890            }
891    
892            return id;
893        }
894    
895        /**
896         * returs true is a harvesting shall be forced outside the regular harvesting interval
897         *
898         * @param source
899         * @return true if a CSW shall be harvested outside the regular harvesting interval
900         * @throws DBPoolException
901         */
902        synchronized boolean shallForceHarvesting( URI source )
903                                throws DBPoolException {
904            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
905            boolean force = false;
906            try {
907                PreparedStatement ps = con.prepareStatement( prop.getProperty( "harvester.forceHarvesting" ) );
908                ps.setString( 1, source.toASCIIString() );
909                ResultSet rs = ps.executeQuery();
910                rs.next();
911                force = rs.getInt( 1 ) == 1;
912                rs.close();
913                ps.close();
914            } catch ( Exception e ) {
915                // TODO
916                // this is because downward compliance; older CSW does not know the requested field
917                // harvestsource.forceharvesting
918                // throw e;
919            } finally {
920                pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
921            }
922            return force;
923        }
924    
925        /**
926         * inner class for encapsulating response handler informations
927         *
928         * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
929         * @author last edited by: $Author: lbuesching $
930         *
931         * @version $Revision: 19482 $, $Date: 2009-09-03 10:06:22 +0200 (Do, 03 Sep 2009) $
932         */
933        class ResponseHandler {
934    
935            private URI uri = null;
936    
937            private boolean isMailAddress = false;
938    
939            /**
940             * @param uri
941             * @param isMailAddress
942             */
943            ResponseHandler( URI uri, boolean isMailAddress ) {
944                this.uri = uri;
945                this.isMailAddress = isMailAddress;
946            }
947    
948            /**
949             * @return true, if it is
950             */
951            boolean isMailAddress() {
952                return isMailAddress;
953            }
954    
955            /**
956             * @return uri
957             */
958            URI getUri() {
959                return uri;
960            }
961    
962        }
963    
964        /**
965         *
966         *
967         *
968         * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
969         * @author last edited by: $Author: lbuesching $
970         *
971         * @version $Revision: 19482 $, $Date: 2009-09-03 10:06:22 +0200 (Do, 03 Sep 2009) $
972         *
973         */
974        public class Record {
975    
976            private Date datestamp = null;
977    
978            private String fileIdentifier = null;
979    
980            private URI source = null;
981    
982            private int sourceId;
983    
984            /**
985             * @param sourceId
986             * @param datestamp
987             * @param fileIdentifier
988             * @param source
989             */
990            public Record( int sourceId, Date datestamp, String fileIdentifier, URI source ) {
991                this.datestamp = datestamp;
992                this.fileIdentifier = fileIdentifier;
993                this.source = source;
994                this.sourceId = sourceId;
995            }
996    
997            /**
998             * @return datestamp
999             */
1000            public Date getDatestamp() {
1001                return datestamp;
1002            }
1003    
1004            /**
1005             * @return fileIdentifier
1006             */
1007            public String getFileIdentifier() {
1008                return fileIdentifier;
1009            }
1010    
1011            /**
1012             * @return source
1013             */
1014            public URI getSource() {
1015                return source;
1016            }
1017    
1018            /**
1019             * @return sourceId
1020             */
1021            public int getSourceId() {
1022                return sourceId;
1023            }
1024    
1025        }
1026    
1027    }