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}