    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_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    }