Source code

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.io.IOException;
028import java.io.InputStream;
029import java.sql.Connection;
030import java.sql.SQLException;
031import java.util.List;
032import java.util.Map;
033
034import org.apache.commons.io.IOUtils;
035import org.slf4j.Logger;
036import org.slf4j.LoggerFactory;
037
038import com.antiaction.raptor.sql.ExecuteSqlFile;
039import com.mchange.v2.c3p0.ComboPooledDataSource;
040
041import dk.netarkivet.common.CommonSettings;
042import dk.netarkivet.common.exceptions.ArgumentNotValid;
043import dk.netarkivet.common.exceptions.IOFailure;
044import dk.netarkivet.common.utils.DBUtils;
045import dk.netarkivet.common.utils.ExceptionUtils;
046import dk.netarkivet.common.utils.Settings;
047
048/**
049 * This class handles connections to the harvest definition database, and also defines basic logic for checking versions
050 * of tables.
051 * <p>
052 * The statements to create the tables are located in:
053 * <ul>
054 * <li><em>Derby:</em> scripts/sql/createfullhddb.sql</li>
055 * <li><em>MySQL:</em> scripts/sql/createfullhddb.mysql</li>
056 * <li><em>PostgreSQL:</em> scripts/postgresql/netarchivesuite_init.sql</li>
057 * </ul>
058 * <p>
059 * The implementation relies on a connection pool. Once acquired through the get() method, a connection must be
060 * explicitly returned to the pool by calling the release(Connection) method.
061 * <p>
062 * THis class is intended to be used statically, and hence cannot be instantiated and is final.
063 */
064public final class HarvestDBConnection {
065
066    private static final Logger log = LoggerFactory.getLogger(HarvestDBConnection.class);
067
068    /** The c3p0 pooled datasource backing this implementation. */
069    private static ComboPooledDataSource dataSource = null;
070
071    /**
072     * Makes sure that the class can't be instantiated, as it is designed to be used statically.
073     */
074    private HarvestDBConnection() {
075
076    }
077
078    /**
079     * Get a connection to the harvest definition database from the pool. The pool is configured via the following
080     * configuration properties:
081     * <ul>
082     * <li>@see {@link CommonSettings#DB_POOL_MIN_SIZE}</li>
083     * <li>@see {@link CommonSettings#DB_POOL_MAX_SIZE}</li>
084     * <li>@see {@link CommonSettings#DB_POOL_ACQ_INC}</li>
085     * </ul>
086     * Note that the connection obtained must be returned to the pool by calling {@link #release(Connection)}.
087     *
088     * @return a connection to the harvest definition database
089     * @throws IOFailure if we cannot connect to the database (or find the driver).
090     */
091    public static synchronized Connection get() {
092        DBSpecifics dbSpec = DBSpecifics.getInstance();
093        String jdbcUrl = getDBUrl();
094
095        try {
096            if (dataSource == null) {
097                initDataSource(dbSpec, jdbcUrl);
098                // this is only done for embedded database!
099                // For external databases, use the HarvestdatabaseUpdateApplication tool
100                if (dbSpec instanceof DerbyEmbeddedSpecifics) {
101                    dbSpec.updateTables();
102                }
103            }
104
105            return dataSource.getConnection();
106        } catch (SQLException e) {
107            final String message = "Can't connect to database with DBurl: '" + jdbcUrl + "' using driver '"
108                    + dbSpec.getDriverClassName() + "'" + "\n" + ExceptionUtils.getSQLExceptionCause(e);
109            log.warn(message, e);
110            throw new IOFailure(message, e);
111        }
112
113    }
114
115    /**
116     * Update a table by executing all the statements in the updates String array. If newVersion=1 then the table is
117     * created. Note that this method does not make any checks that the SQL statements in the updates parameter actually
118     * update or create the correct table.
119     *
120     * @param table The table to update
121     * @param newVersion The version that the table should end up at
122     * @param updates The SQL update statements that makes the necessary updates.
123     * @throws IOFailure in case of problems in interacting with the database
124     */
125    protected static void updateTable(final String table, final int newVersion, final String... updates) {
126        Connection c = get();
127        updateTable(c, table, newVersion, updates);
128    }
129
130    public static void updateTable(Connection c, final String table, final int newVersion, final String... updates) {
131        log.info("Updating table '{}' to version {}", table, newVersion);
132
133        String[] sqlStatements = new String[updates.length + 1];
134        String updateSchemaversionSql = null;
135        if (newVersion == 1) {
136            updateSchemaversionSql = "INSERT INTO schemaversions(tablename, version) VALUES ('" + table + "', 1)";
137        } else {
138            updateSchemaversionSql = "UPDATE schemaversions SET version = " + newVersion + " WHERE tablename = '"
139                    + table + "'";
140        }
141        System.arraycopy(updates, 0, sqlStatements, 0, updates.length);
142        sqlStatements[updates.length] = updateSchemaversionSql;
143
144        try {
145            DBUtils.executeSQL(c, sqlStatements);
146        } finally {
147            release(c);
148        }
149    }
150
151    protected static void updateTableVersion(final String table, final int newVersion, final String... updates) {
152        Connection c = get();
153        updateTableVersion(c, table, newVersion);
154    }
155
156    public static void updateTableVersion(Connection c, final String table, final int newVersion) {
157        log.info("Updating table '{}' to version {}", table, newVersion);
158        String updateSchemaversionSql = null;
159        if (newVersion == 1) {
160            updateSchemaversionSql = "INSERT INTO schemaversions(tablename, version) VALUES ('" + table + "', 1)";
161        } else {
162            updateSchemaversionSql = "UPDATE schemaversions SET version = " + newVersion + " WHERE tablename = '"
163                    + table + "'";
164        }
165        try {
166            DBUtils.executeSQL(c, updateSchemaversionSql);
167        } finally {
168            release(c);
169        }
170    }
171
172    /**
173     * Method for retrieving the url for the harvest definition database. This url will be constructed from the
174     * base-url, the machine, the port and the directory. If the database is internal, then only the base-url should
175     * have a value.
176     *
177     * @return The url for the harvest definition database.
178     */
179    public static String getDBUrl() {
180        StringBuilder res = new StringBuilder();
181        res.append(Settings.get(CommonSettings.DB_BASE_URL));
182
183        // append the machine part of the url, if it exists.
184        String tmp = Settings.get(CommonSettings.DB_MACHINE);
185        if (!tmp.isEmpty()) {
186            res.append("://");
187            res.append(tmp);
188        }
189
190        // append the machine part of the url, if it exists.
191        tmp = Settings.get(CommonSettings.DB_PORT);
192        if (!tmp.isEmpty()) {
193            res.append(":");
194            res.append(tmp);
195        }
196
197        // append the machine part of the url, if it exists.
198        tmp = Settings.get(CommonSettings.DB_DIR);
199        if (!tmp.isEmpty()) {
200            res.append("/");
201            res.append(tmp);
202        }
203
204        return res.toString();
205    }
206
207    /**
208     * Closes the underlying data source.
209     */
210    public static synchronized void cleanup() {
211        if (dataSource == null) {
212            return;
213        }
214
215        try {
216            // Unclosed connections are not supposed to be found.
217            // Anyway log if there are some.
218            int numUnclosedConn = dataSource.getNumBusyConnections();
219            if (numUnclosedConn > 0) {
220                log.error("There are {} unclosed connections!", numUnclosedConn);
221            }
222        } catch (SQLException e) {
223            log.warn("Could not query pool status", e);
224        }
225        if (dataSource != null) {
226            dataSource.close();
227            dataSource = null;
228        }
229    }
230
231    /**
232     * Helper method to return a connection to the pool.
233     *
234     * @param connection a connection
235     */
236    public static synchronized void release(Connection connection) {
237        ArgumentNotValid.checkNotNull(connection, "connection");
238        try {
239            connection.close();
240        } catch (SQLException e) {
241            log.error("Failed to close connection", e);
242        }
243    }
244
245    /**
246     * Initializes the connection pool.
247     *
248     * @param dbSpec the object representing the chosen DB target system.
249     * @param jdbcUrl the JDBC URL to connect to.
250     * @throws SQLException
251     */
252    private static void initDataSource(DBSpecifics dbSpec, String jdbcUrl) throws SQLException {
253        dataSource = new ComboPooledDataSource();
254        try {
255            dataSource.setDriverClass(dbSpec.getDriverClassName());
256        } catch (PropertyVetoException e) {
257            final String message = "Failed to set datasource JDBC driver class '" + dbSpec.getDriverClassName() + "'"
258                    + "\n";
259            throw new IOFailure(message, e);
260        }
261        dataSource.setJdbcUrl(jdbcUrl);
262        String username = Settings.get(CommonSettings.DB_USERNAME);
263        if (!username.isEmpty()) {
264            dataSource.setUser(username);
265        }
266        String password = Settings.get(CommonSettings.DB_PASSWORD);
267        if (!password.isEmpty()) {
268            dataSource.setPassword(password);
269        }
270        // Configure pool size
271        dataSource.setMinPoolSize(Settings.getInt(CommonSettings.DB_POOL_MIN_SIZE));
272        dataSource.setMaxPoolSize(Settings.getInt(CommonSettings.DB_POOL_MAX_SIZE));
273        dataSource.setAcquireIncrement(Settings.getInt(CommonSettings.DB_POOL_ACQ_INC));
274        dataSource.setMaxConnectionAge(Settings.getInt(CommonSettings.DB_POOL_MAX_CONNECTION_AGE));
275
276        // Configure idle connection testing
277        int testPeriod = Settings.getInt(CommonSettings.DB_POOL_IDLE_CONN_TEST_PERIOD);
278        // TODO This looks odd. Why is checkin-testing inside this if statement?
279        if (testPeriod > 0) {
280            dataSource.setIdleConnectionTestPeriod(testPeriod);
281            dataSource
282                    .setTestConnectionOnCheckin(Settings.getBoolean(CommonSettings.DB_POOL_IDLE_CONN_TEST_ON_CHECKIN));
283            String testQuery = Settings.get(CommonSettings.DB_POOL_IDLE_CONN_TEST_QUERY);
284            if (!testQuery.isEmpty()) {
285                dataSource.setPreferredTestQuery(testQuery);
286            }
287        }
288
289        // Configure statement pooling
290        dataSource.setMaxStatements(Settings.getInt(CommonSettings.DB_POOL_MAX_STM));
291        dataSource.setMaxStatementsPerConnection(Settings.getInt(CommonSettings.DB_POOL_MAX_STM_PER_CONN));
292
293        // dataSource.setTestConnectionOnCheckout(true);
294        // dataSource.setBreakAfterAcquireFailure(false);
295        // dataSource.setAcquireRetryAttempts(10000);
296        // dataSource.setAcquireRetryDelay(10);
297
298        if (log.isInfoEnabled()) {
299            log.info("Connection pool initialized with the following values:\n" + "- minPoolSize={}\n"
300                    + "- maxPoolSize={}\n" + "- acquireIncrement={}\n" + "- maxStatements={}\n"
301                    + "- maxStatementsPerConnection={}\n" + "- idleConnTestPeriod={}\n" + "- idleConnTestQuery='{}'\n"
302                    + "- idleConnTestOnCheckin={}", dataSource.getMinPoolSize(), dataSource.getMaxPoolSize(),
303                    dataSource.getAcquireIncrement(), dataSource.getMaxStatements(),
304                    dataSource.getMaxStatementsPerConnection(), dataSource.getIdleConnectionTestPeriod(),
305                    dataSource.getPreferredTestQuery(), dataSource.isTestConnectionOnCheckin());
306        }
307    }
308
309    /**
310     * Execute the sql to update a given table to a given version.  
311     * The necessary scripts are bundled into the root of the harvester-core.jar in the directory sql-migration.
312     * The source of these scripts are in one of these directories:
313     * 
314     *  $BASEDIR/deploy/deploy-core/scripts/postgresql/migration/
315     *  $BASEDIR/deploy/deploy-core/scripts/mysql/migration/
316     *  $BASEDIR/deploy/deploy-core/scripts/derby/migration/
317     *   
318     *  To allow the user to update table 'eav_attribute' in postgresql to version 1
319     *  the file $BASEDIR/deploy/deploy-core/scripts/postgresql/migration/eav_attribute.1.sql must exist
320     *  The postgresql files are during the build-phase put into the sql-migration/postgresql directory
321     *  The same holds for mysql and derby.
322     *  
323     * @param dbm the type of DBMS (mysql, postgresql,derby)  
324     * @param tableName The given table to update
325     * @param version The table version to update to
326     */
327    public static void executeSql(String dbm, String tableName, int version) {
328        Connection conn = HarvestDBConnection.get();
329        executeSql(conn, dbm, tableName, version);
330        HarvestDBConnection.release(conn);
331        conn = null;
332    }
333
334    /**
335     * Look for the file sql-migration/${dbm}/${tableName}.${version}.sql inside in the harvester-core.jar file.
336     * 
337     * @param conn a valid database connection
338     * @param dbm the name of the DBMS used
339     * @param tableName the name of the table being updated
340     * @param version The new version of the table
341     */
342    public static void executeSql(Connection conn, String dbm, String tableName, int version) {
343        String resource = "sql-migration/" + dbm + "/" + tableName + "." + version + ".sql";
344        log.info("Fetching resource {} to update table '{}' to version {} using databasetype {}", resource, tableName, version, dbm);
345        InputStream in = HarvestDBConnection.class.getClassLoader().getResourceAsStream(resource);
346        try {
347                List<Map.Entry<String, String>> statements = ExecuteSqlFile.splitSql(in, "UTF-8", 8192);
348                in.close();
349                in = null;
350            ExecuteSqlFile.executeStatements(conn, statements);
351            // Update the schemaversions with the new version for this table
352            HarvestDBConnection.updateTable(conn, tableName, version);
353        } catch (IOException e) {
354                throw new IOFailure("Unable to update the table '" + tableName 
355                        + "' to version '" + version + "' using database '" + dbm + "'", e);
356        } catch (SQLException e) {
357            throw new IOFailure("Unable to update the table '" + tableName 
358            + "' to version '" + version + "' using database '" + dbm + "':\n" + ExceptionUtils.getSQLExceptionCause(e), e);
359        } finally {
360            IOUtils.closeQuietly(in);
361        }
362    }
363
364}