001/*
002 * #%L
003 * Netarchivesuite - harvester
004 * %%
005 * Copyright (C) 2005 - 2014 The Royal Danish Library, the Danish State and University Library,
006 *             the National Library of France and the Austrian National Library.
007 * %%
008 * This program is free software: you can redistribute it and/or modify
009 * it under the terms of the GNU Lesser General Public License as
010 * published by the Free Software Foundation, either version 2.1 of the
011 * License, or (at your option) any later version.
012 * 
013 * This program is distributed in the hope that it will be useful,
014 * but WITHOUT ANY WARRANTY; without even the implied warranty of
015 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
016 * GNU General Lesser Public License for more details.
017 * 
018 * You should have received a copy of the GNU General Lesser Public
019 * License along with this program.  If not, see
020 * <http://www.gnu.org/licenses/lgpl-2.1.html>.
021 * #L%
022 */
023
024package dk.netarkivet.harvester.datamodel;
025
026import java.beans.PropertyVetoException;
027import java.sql.Connection;
028import java.sql.SQLException;
029
030import org.slf4j.Logger;
031import org.slf4j.LoggerFactory;
032
033import com.mchange.v2.c3p0.ComboPooledDataSource;
034
035import dk.netarkivet.common.CommonSettings;
036import dk.netarkivet.common.exceptions.ArgumentNotValid;
037import dk.netarkivet.common.exceptions.IOFailure;
038import dk.netarkivet.common.utils.DBUtils;
039import dk.netarkivet.common.utils.ExceptionUtils;
040import dk.netarkivet.common.utils.Settings;
041
042/**
043 * This class handles connections to the harvest definition database, and also defines basic logic for checking versions
044 * of tables.
045 * <p>
046 * The statements to create the tables are located in:
047 * <ul>
048 * <li><em>Derby:</em> scripts/sql/createfullhddb.sql</li>
049 * <li><em>MySQL:</em> scripts/sql/createfullhddb.mysql</li>
050 * <li><em>PostgreSQL:</em> scripts/postgresql/netarchivesuite_init.sql</li>
051 * </ul>
052 * <p>
053 * The implementation relies on a connection pool. Once acquired through the get() method, a connection must be
054 * explicitly returned to the pool by calling the release(Connection) method.
055 * <p>
056 * THis class is intended to be used statically, and hence cannot be instantiated and is final.
057 */
058public final class HarvestDBConnection {
059
060    private static final Logger log = LoggerFactory.getLogger(HarvestDBConnection.class);
061
062    /** The c3p0 pooled datasource backing this implementation. */
063    private static ComboPooledDataSource dataSource = null;
064
065    /**
066     * Makes sure that the class can't be instantiated, as it is designed to be used statically.
067     */
068    private HarvestDBConnection() {
069
070    }
071
072    /**
073     * Get a connection to the harvest definition database from the pool. The pool is configured via the following
074     * configuration properties:
075     * <ul>
076     * <li>@see {@link CommonSettings#DB_POOL_MIN_SIZE}</li>
077     * <li>@see {@link CommonSettings#DB_POOL_MAX_SIZE}</li>
078     * <li>@see {@link CommonSettings#DB_POOL_ACQ_INC}</li>
079     * </ul>
080     * Note that the connection obtained must be returned to the pool by calling {@link #release(Connection)}.
081     *
082     * @return a connection to the harvest definition database
083     * @throws IOFailure if we cannot connect to the database (or find the driver).
084     */
085    public static synchronized Connection get() {
086        DBSpecifics dbSpec = DBSpecifics.getInstance();
087        String jdbcUrl = getDBUrl();
088
089        try {
090            if (dataSource == null) {
091                initDataSource(dbSpec, jdbcUrl);
092                // this is only done for embedded database!
093                // For external databases, use the HarvestdatabaseUpdateApplication tool
094                if (dbSpec instanceof DerbyEmbeddedSpecifics) {
095                    dbSpec.updateTables();
096                }
097            }
098
099            return dataSource.getConnection();
100        } catch (SQLException e) {
101            final String message = "Can't connect to database with DBurl: '" + jdbcUrl + "' using driver '"
102                    + dbSpec.getDriverClassName() + "'" + "\n" + ExceptionUtils.getSQLExceptionCause(e);
103            log.warn(message, e);
104            throw new IOFailure(message, e);
105        }
106
107    }
108
109    /**
110     * Update a table by executing all the statements in the updates String array. If newVersion=1 then the table is
111     * created. Note that this method does not make any checks that the SQL statements in the updates parameter actually
112     * update or create the correct table.
113     *
114     * @param table The table to update
115     * @param newVersion The version that the table should end up at
116     * @param updates The SQL update statements that makes the necessary updates.
117     * @throws IOFailure in case of problems in interacting with the database
118     */
119    protected static void updateTable(final String table, final int newVersion, final String... updates) {
120
121        Connection c = get();
122        updateTable(c, table, newVersion, updates);
123    }
124
125    public static void updateTable(Connection c, final String table, final int newVersion, final String... updates) {
126        log.info("Updating table '{}' to version {}", table, newVersion);
127
128        String[] sqlStatements = new String[updates.length + 1];
129        String updateSchemaversionSql = null;
130        if (newVersion == 1) {
131            updateSchemaversionSql = "INSERT INTO schemaversions(tablename, version) VALUES ('" + table + "', 1)";
132        } else {
133            updateSchemaversionSql = "UPDATE schemaversions SET version = " + newVersion + " WHERE tablename = '"
134                    + table + "'";
135        }
136        System.arraycopy(updates, 0, sqlStatements, 0, updates.length);
137        sqlStatements[updates.length] = updateSchemaversionSql;
138
139        try {
140            DBUtils.executeSQL(c, sqlStatements);
141        } finally {
142            release(c);
143        }
144    }
145
146    /**
147     * Method for retrieving the url for the harvest definition database. This url will be constructed from the
148     * base-url, the machine, the port and the directory. If the database is internal, then only the base-url should
149     * have a value.
150     *
151     * @return The url for the harvest definition database.
152     */
153    public static String getDBUrl() {
154        StringBuilder res = new StringBuilder();
155        res.append(Settings.get(CommonSettings.DB_BASE_URL));
156
157        // append the machine part of the url, if it exists.
158        String tmp = Settings.get(CommonSettings.DB_MACHINE);
159        if (!tmp.isEmpty()) {
160            res.append("://");
161            res.append(tmp);
162        }
163
164        // append the machine part of the url, if it exists.
165        tmp = Settings.get(CommonSettings.DB_PORT);
166        if (!tmp.isEmpty()) {
167            res.append(":");
168            res.append(tmp);
169        }
170
171        // append the machine part of the url, if it exists.
172        tmp = Settings.get(CommonSettings.DB_DIR);
173        if (!tmp.isEmpty()) {
174            res.append("/");
175            res.append(tmp);
176        }
177
178        return res.toString();
179    }
180
181    /**
182     * Closes the underlying data source.
183     */
184    public static synchronized void cleanup() {
185        if (dataSource == null) {
186            return;
187        }
188
189        try {
190            // Unclosed connections are not supposed to be found.
191            // Anyway log if there are some.
192            int numUnclosedConn = dataSource.getNumBusyConnections();
193            if (numUnclosedConn > 0) {
194                log.error("There are {} unclosed connections!", numUnclosedConn);
195            }
196        } catch (SQLException e) {
197            log.warn("Could not query pool status", e);
198        }
199        if (dataSource != null) {
200            dataSource.close();
201            dataSource = null;
202        }
203    }
204
205    /**
206     * Helper method to return a connection to the pool.
207     *
208     * @param connection a connection
209     */
210    public static synchronized void release(Connection connection) {
211        ArgumentNotValid.checkNotNull(connection, "connection");
212        try {
213            connection.close();
214        } catch (SQLException e) {
215            log.error("Failed to close connection", e);
216        }
217    }
218
219    /**
220     * Initializes the connection pool.
221     *
222     * @param dbSpec the object representing the chosen DB target system.
223     * @param jdbcUrl the JDBC URL to connect to.
224     * @throws SQLException
225     */
226    private static void initDataSource(DBSpecifics dbSpec, String jdbcUrl) throws SQLException {
227        dataSource = new ComboPooledDataSource();
228        try {
229            dataSource.setDriverClass(dbSpec.getDriverClassName());
230        } catch (PropertyVetoException e) {
231            final String message = "Failed to set datasource JDBC driver class '" + dbSpec.getDriverClassName() + "'"
232                    + "\n";
233            throw new IOFailure(message, e);
234        }
235        dataSource.setJdbcUrl(jdbcUrl);
236        String username = Settings.get(CommonSettings.DB_USERNAME);
237        if (!username.isEmpty()) {
238            dataSource.setUser(username);
239        }
240        String password = Settings.get(CommonSettings.DB_PASSWORD);
241        if (!password.isEmpty()) {
242            dataSource.setPassword(password);
243        }
244        // Configure pool size
245        dataSource.setMinPoolSize(Settings.getInt(CommonSettings.DB_POOL_MIN_SIZE));
246        dataSource.setMaxPoolSize(Settings.getInt(CommonSettings.DB_POOL_MAX_SIZE));
247        dataSource.setAcquireIncrement(Settings.getInt(CommonSettings.DB_POOL_ACQ_INC));
248
249        // Configure idle connection testing
250        int testPeriod = Settings.getInt(CommonSettings.DB_POOL_IDLE_CONN_TEST_PERIOD);
251        // TODO This looks odd. Why is checkin-testing inside this if statement?
252        if (testPeriod > 0) {
253            dataSource.setIdleConnectionTestPeriod(testPeriod);
254            dataSource
255                    .setTestConnectionOnCheckin(Settings.getBoolean(CommonSettings.DB_POOL_IDLE_CONN_TEST_ON_CHECKIN));
256            String testQuery = Settings.get(CommonSettings.DB_POOL_IDLE_CONN_TEST_QUERY);
257            if (!testQuery.isEmpty()) {
258                dataSource.setPreferredTestQuery(testQuery);
259            }
260        }
261
262        // Configure statement pooling
263        dataSource.setMaxStatements(Settings.getInt(CommonSettings.DB_POOL_MAX_STM));
264        dataSource.setMaxStatementsPerConnection(Settings.getInt(CommonSettings.DB_POOL_MAX_STM_PER_CONN));
265
266        // dataSource.setTestConnectionOnCheckout(true);
267        // dataSource.setBreakAfterAcquireFailure(false);
268        // dataSource.setAcquireRetryAttempts(10000);
269        // dataSource.setAcquireRetryDelay(10);
270
271        if (log.isInfoEnabled()) {
272            log.info("Connection pool initialized with the following values:\n" + "- minPoolSize={}\n"
273                    + "- maxPoolSize={}\n" + "- acquireIncrement={}\n" + "- maxStatements={}\n"
274                    + "- maxStatementsPerConnection={}\n" + "- idleConnTestPeriod={}\n" + "- idleConnTestQuery='{}'\n"
275                    + "- idleConnTestOnCheckin={}", dataSource.getMinPoolSize(), dataSource.getMaxPoolSize(),
276                    dataSource.getAcquireIncrement(), dataSource.getMaxStatements(),
277                    dataSource.getMaxStatementsPerConnection(), dataSource.getIdleConnectionTestPeriod(),
278                    dataSource.getPreferredTestQuery(), dataSource.isTestConnectionOnCheckin());
279        }
280    }
281}