001/*
002 * This library is part of OpenCms -
003 * the Open Source Content Management System
004 *
005 * Copyright (c) Alkacon Software GmbH & Co. KG (http://www.alkacon.com)
006 *
007 * This library is free software; you can redistribute it and/or
008 * modify it under the terms of the GNU Lesser General Public
009 * License as published by the Free Software Foundation; either
010 * version 2.1 of the License, or (at your option) any later version.
011 *
012 * This library is distributed in the hope that it will be useful,
013 * but WITHOUT ANY WARRANTY; without even the implied warranty of
014 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015 * Lesser General Public License for more details.
016 *
017 * For further information about Alkacon Software GmbH & Co. KG, please see the
018 * company website: http://www.alkacon.com
019 *
020 * For further information about OpenCms, please see the
021 * project website: http://www.opencms.org
022 *
023 * You should have received a copy of the GNU Lesser General Public
024 * License along with this library; if not, write to the Free Software
025 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
026 */
027
028package org.opencms.db;
029
030import org.opencms.configuration.CmsParameterConfiguration;
031import org.opencms.main.CmsLog;
032import org.opencms.main.OpenCms;
033import org.opencms.security.I_CmsCredentialsResolver;
034import org.opencms.util.CmsStringUtil;
035
036import java.sql.Connection;
037import java.util.ArrayList;
038import java.util.List;
039import java.util.Properties;
040
041import org.apache.commons.dbcp.ConnectionFactory;
042import org.apache.commons.dbcp.DriverManagerConnectionFactory;
043import org.apache.commons.dbcp.PoolableConnectionFactory;
044import org.apache.commons.dbcp.PoolingDriver;
045import org.apache.commons.pool.impl.GenericKeyedObjectPool;
046import org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory;
047import org.apache.commons.pool.impl.GenericObjectPool;
048
049/**
050 * Various methods to create DBCP pools.<p>
051 *
052 * Only JDBC Driver based pools are supported currently. JNDI DataSource
053 * based pools might be added probably later.<p>
054 *
055 * <b>Please note:</b> This class is subject to change in later versions.
056 * To obtain information about the connections, please use the
057 * {@link org.opencms.db.CmsSqlManager}.<p>
058 *
059 * @since 6.0.0
060 */
061public final class CmsDbPool {
062
063    /** This prefix is required to make the JDBC DriverManager return pooled DBCP connections. */
064    public static final String DBCP_JDBC_URL_PREFIX = "jdbc:apache:commons:dbcp:";
065
066    /** Key for number of connection attempts. */
067    public static final String KEY_CONNECT_ATTEMTS = "connects";
068
069    /** Key for connection waiting. */
070    public static final String KEY_CONNECT_WAITS = "wait";
071
072    /** Prefix for database keys. */
073    public static final String KEY_DATABASE = "db.";
074
075    /** Key for the database name. */
076    public static final String KEY_DATABASE_NAME = KEY_DATABASE + "name";
077
078    /** Key for the pool id. */
079    public static final String KEY_DATABASE_POOL = KEY_DATABASE + "pool";
080
081    /** Key for statement pooling. */
082    public static final String KEY_DATABASE_STATEMENTS = KEY_DATABASE + "statements";
083
084    /** Key for the entity manager pool size. */
085    public static final String KEY_ENTITY_MANAGER_POOL_SIZE = "entityMangerPoolSize";
086
087    /** Key for jdbc driver. */
088    public static final String KEY_CONNECTION_PROPERTIES = "connectionProperties";
089
090    /** Key for jdbc driver. */
091    public static final String KEY_JDBC_DRIVER = "jdbcDriver";
092
093    /** Key for jdbc url. */
094    public static final String KEY_JDBC_URL = "jdbcUrl";
095
096    /** Key for jdbc url params. */
097    public static final String KEY_JDBC_URL_PARAMS = KEY_JDBC_URL + ".params";
098
099    /** Key for maximum active connections. */
100    public static final String KEY_MAX_ACTIVE = "maxActive";
101
102    /** Key for maximum idle connections. */
103    public static final String KEY_MAX_IDLE = "maxIdle";
104
105    /** Key for maximum wait time. */
106    public static final String KEY_MAX_WAIT = "maxWait";
107
108    /** Key for minimum idle time before a connection is subject to an eviction test. */
109    public static final String KEY_MIN_EVICTABLE_IDLE_TIME = "minEvictableIdleTime";
110
111    /** Key for minimum number of connections kept open. */
112    public static final String KEY_MIN_IDLE = "minIdle";
113
114    /** Key for number of tested connections per run. */
115    public static final String KEY_NUM_TESTS_PER_EVICTION_RUN = "numTestsPerEvictionRun";
116
117    /** Key for database password. */
118    public static final String KEY_PASSWORD = "password";
119
120    /** Key for default. */
121    public static final String KEY_POOL_DEFAULT = "default";
122
123    /** Key for pool url. */
124    public static final String KEY_POOL_URL = "poolUrl";
125
126    /** Key for pool user. */
127    public static final String KEY_POOL_USER = "user";
128
129    /** Key for vfs pool. */
130    public static final String KEY_POOL_VFS = "vfs";
131
132    /** Key for pooling flag. */
133    public static final String KEY_POOLING = "pooling";
134
135    /** Key for test on borrow flag. */
136    public static final String KEY_TEST_ON_BORROW = "testOnBorrow";
137
138    /** Key for test query. */
139    public static final String KEY_TEST_QUERY = "testQuery";
140
141    /** Key for test while idle flag. */
142    public static final String KEY_TEST_WHILE_IDLE = "testWhileIdle";
143
144    /** Key for time between two eviction runs. */
145    public static final String KEY_TIME_BETWEEN_EVICTION_RUNS = "timeBetweenEvictionRuns";
146
147    /** Key for user name. */
148    public static final String KEY_USERNAME = "user";
149
150    /** Key for "when pool exhausted" action. */
151    public static final String KEY_WHEN_EXHAUSTED_ACTION = "whenExhaustedAction";
152
153    /** The name of the opencms default pool. */
154    public static final String OPENCMS_DEFAULT_POOL_NAME = "default";
155
156    /** The default OpenCms JDBC pool URL. */
157    public static final String OPENCMS_DEFAULT_POOL_URL = "opencms:default";
158
159    /** The prefix used for opencms JDBC pools. */
160    public static final String OPENCMS_URL_PREFIX = "opencms:";
161
162    /**
163     * Default constructor.<p>
164     *
165     * Nobody is allowed to create an instance of this class!
166     */
167    private CmsDbPool() {
168
169        super();
170    }
171
172    /**
173     * Creates a JDBC DriverManager based DBCP connection pool.<p>
174     *
175     * @param config the configuration (opencms.properties)
176     * @param key the key of the database pool in the configuration
177     * @return String the URL to access the created DBCP pool
178     * @throws Exception if the pool could not be initialized
179     */
180    public static PoolingDriver createDriverManagerConnectionPool(CmsParameterConfiguration config, String key)
181    throws Exception {
182
183        // read the values of the pool configuration specified by the given key
184        String jdbcDriver = config.get(KEY_DATABASE_POOL + '.' + key + '.' + KEY_JDBC_DRIVER);
185        String jdbcUrl = config.get(KEY_DATABASE_POOL + '.' + key + '.' + KEY_JDBC_URL);
186        String jdbcUrlParams = config.get(KEY_DATABASE_POOL + '.' + key + '.' + KEY_JDBC_URL_PARAMS);
187        int maxActive = config.getInteger(KEY_DATABASE_POOL + '.' + key + '.' + KEY_MAX_ACTIVE, 10);
188        int maxWait = config.getInteger(KEY_DATABASE_POOL + '.' + key + '.' + KEY_MAX_WAIT, 2000);
189        int maxIdle = config.getInteger(KEY_DATABASE_POOL + '.' + key + '.' + KEY_MAX_IDLE, 5);
190        int minEvictableIdleTime = config.getInteger(
191            KEY_DATABASE_POOL + '.' + key + '.' + KEY_MIN_EVICTABLE_IDLE_TIME,
192            1800000);
193        int minIdle = config.getInteger(KEY_DATABASE_POOL + '.' + key + '.' + KEY_MIN_IDLE, 0);
194        int numTestsPerEvictionRun = config.getInteger(
195            KEY_DATABASE_POOL + '.' + key + '.' + KEY_NUM_TESTS_PER_EVICTION_RUN,
196            3);
197        int timeBetweenEvictionRuns = config.getInteger(
198            KEY_DATABASE_POOL + '.' + key + '.' + KEY_TIME_BETWEEN_EVICTION_RUNS,
199            3600000);
200        String testQuery = config.get(KEY_DATABASE_POOL + '.' + key + '.' + KEY_TEST_QUERY);
201        String username = config.get(KEY_DATABASE_POOL + '.' + key + '.' + KEY_USERNAME);
202        username = OpenCms.getCredentialsResolver().resolveCredential(I_CmsCredentialsResolver.DB_USER, username);
203        String password = config.get(KEY_DATABASE_POOL + '.' + key + '.' + KEY_PASSWORD);
204        password = OpenCms.getCredentialsResolver().resolveCredential(I_CmsCredentialsResolver.DB_PASSWORD, password);
205        String poolUrl = config.get(KEY_DATABASE_POOL + '.' + key + '.' + KEY_POOL_URL);
206        String whenExhaustedActionValue = config.get(
207            KEY_DATABASE_POOL + '.' + key + '.' + KEY_WHEN_EXHAUSTED_ACTION).trim();
208        byte whenExhaustedAction = 0;
209        boolean testOnBorrow = Boolean.valueOf(
210            config.getString(KEY_DATABASE_POOL + '.' + key + '.' + KEY_TEST_ON_BORROW, "false").trim()).booleanValue();
211        boolean testWhileIdle = Boolean.valueOf(
212            config.getString(KEY_DATABASE_POOL + '.' + key + '.' + KEY_TEST_WHILE_IDLE, "false").trim()).booleanValue();
213
214        if ("block".equalsIgnoreCase(whenExhaustedActionValue)) {
215            whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_BLOCK;
216        } else if ("fail".equalsIgnoreCase(whenExhaustedActionValue)) {
217            whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_FAIL;
218        } else if ("grow".equalsIgnoreCase(whenExhaustedActionValue)) {
219            whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_GROW;
220        } else {
221            whenExhaustedAction = GenericObjectPool.DEFAULT_WHEN_EXHAUSTED_ACTION;
222        }
223
224        if ("".equals(testQuery)) {
225            testQuery = null;
226        }
227
228        if (username == null) {
229            username = "";
230        }
231
232        if (password == null) {
233            password = "";
234        }
235
236        // read the values of the statement pool configuration specified by the given key
237        boolean poolingStmts = Boolean.valueOf(
238            config.getString(
239                KEY_DATABASE_STATEMENTS + '.' + key + '.' + KEY_POOLING,
240                CmsStringUtil.TRUE).trim()).booleanValue();
241        int maxActiveStmts = config.getInteger(KEY_DATABASE_STATEMENTS + '.' + key + '.' + KEY_MAX_ACTIVE, 25);
242        int maxWaitStmts = config.getInteger(KEY_DATABASE_STATEMENTS + '.' + key + '.' + KEY_MAX_WAIT, 250);
243        int maxIdleStmts = config.getInteger(KEY_DATABASE_STATEMENTS + '.' + key + '.' + KEY_MAX_IDLE, 15);
244        String whenStmtsExhaustedActionValue = config.get(
245            KEY_DATABASE_STATEMENTS + '.' + key + '.' + KEY_WHEN_EXHAUSTED_ACTION);
246        byte whenStmtsExhaustedAction = GenericKeyedObjectPool.WHEN_EXHAUSTED_GROW;
247        if (whenStmtsExhaustedActionValue != null) {
248            whenStmtsExhaustedActionValue = whenStmtsExhaustedActionValue.trim();
249            whenStmtsExhaustedAction = ("block".equalsIgnoreCase(whenStmtsExhaustedActionValue))
250            ? GenericKeyedObjectPool.WHEN_EXHAUSTED_BLOCK
251            : ("fail".equalsIgnoreCase(whenStmtsExhaustedActionValue))
252            ? GenericKeyedObjectPool.WHEN_EXHAUSTED_FAIL
253            : GenericKeyedObjectPool.WHEN_EXHAUSTED_GROW;
254        }
255
256        int connectionAttempts = config.getInteger(KEY_DATABASE_POOL + '.' + key + '.' + KEY_CONNECT_ATTEMTS, 10);
257        int connetionsWait = config.getInteger(KEY_DATABASE_POOL + '.' + key + '.' + KEY_CONNECT_WAITS, 5000);
258
259        // create an instance of the JDBC driver
260        Class.forName(jdbcDriver).newInstance();
261
262        // initialize a keyed object pool to store connections
263        GenericObjectPool connectionPool = new GenericObjectPool(null);
264
265        /* Abandoned pool configuration:
266         *
267         * In case the systems encounters "pool exhaustion" (runs out of connections),
268         * comment the above line with "new GenericObjectPool(null)" and uncomment the
269         * 5 lines below. This will generate an "abandoned pool" configuration that logs
270         * abandoned connections to the System.out. Unfortunatly this code is deprecated,
271         * so to avoid code warnings it's also disabled here.
272         * Tested with commons-pool v 1.2.
273         */
274
275        //        AbandonedConfig abandonedConfig = new AbandonedConfig();
276        //        abandonedConfig.setLogAbandoned(true);
277        //        abandonedConfig.setRemoveAbandoned(true);
278        //        abandonedConfig.setRemoveAbandonedTimeout(5);
279        //        GenericObjectPool connectionPool = new AbandonedObjectPool(null, abandonedConfig);
280        // initialize an object pool to store connections
281        connectionPool.setMaxActive(maxActive);
282        connectionPool.setMaxIdle(maxIdle);
283        connectionPool.setMinIdle(minIdle);
284        connectionPool.setMaxWait(maxWait);
285        connectionPool.setWhenExhaustedAction(whenExhaustedAction);
286
287        if (testQuery != null) {
288            connectionPool.setTestOnBorrow(testOnBorrow);
289            connectionPool.setTestWhileIdle(testWhileIdle);
290            connectionPool.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRuns);
291            connectionPool.setNumTestsPerEvictionRun(numTestsPerEvictionRun);
292            connectionPool.setMinEvictableIdleTimeMillis(minEvictableIdleTime);
293        }
294
295        // initialize a connection factory to make the DriverManager taking connections from the pool
296        if (jdbcUrlParams != null) {
297            jdbcUrl += jdbcUrlParams;
298        }
299
300        Properties connectionProperties = config.getPrefixedProperties(
301            KEY_DATABASE_POOL + '.' + key + '.' + KEY_CONNECTION_PROPERTIES);
302        connectionProperties.put(KEY_USERNAME, username);
303        connectionProperties.put(KEY_PASSWORD, password);
304        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(jdbcUrl, connectionProperties);
305
306        // Set up statement pool, if desired
307        GenericKeyedObjectPoolFactory statementFactory = null;
308        if (poolingStmts) {
309            statementFactory = new GenericKeyedObjectPoolFactory(
310                null,
311                maxActiveStmts,
312                whenStmtsExhaustedAction,
313                maxWaitStmts,
314                maxIdleStmts);
315        }
316
317        // initialize a factory to obtain pooled connections and prepared statements
318        new PoolableConnectionFactory(connectionFactory, connectionPool, statementFactory, testQuery, false, true);
319
320        // initialize a new pooling driver using the pool
321        PoolingDriver driver = new PoolingDriver();
322        driver.registerPool(poolUrl, connectionPool);
323
324        Connection con = null;
325        boolean connect = false;
326        int connectionTests = 0;
327
328        // try to connect once to the database to ensure it can be connected to at all
329        // if the conection cannot be established, multiple attempts will be done to connect
330        // just in cast the database was not fast enough to start before OpenCms was started
331
332        do {
333            try {
334                // try to connect
335                con = connectionFactory.createConnection();
336                connect = true;
337            } catch (Exception e) {
338                // connection failed, increase attempts, sleept for some seconds and log a message
339                connectionTests++;
340                if (CmsLog.INIT.isInfoEnabled()) {
341                    CmsLog.INIT.info(
342                        Messages.get().getBundle().key(
343                            Messages.INIT_WAIT_FOR_DB_4,
344                            new Object[] {
345                                poolUrl,
346                                jdbcUrl,
347                                new Integer(connectionTests),
348                                new Integer(connetionsWait)}));
349                }
350                Thread.sleep(connetionsWait);
351            } finally {
352                if (con != null) {
353                    con.close();
354                }
355            }
356        } while (!connect && (connectionTests < connectionAttempts));
357
358        if (CmsLog.INIT.isInfoEnabled()) {
359            CmsLog.INIT.info(Messages.get().getBundle().key(Messages.INIT_JDBC_POOL_2, poolUrl, jdbcUrl));
360        }
361        return driver;
362    }
363
364    /**
365     * Returns the database pool name for a given configuration key.<p>
366     *
367     * @param configuration the configuration
368     * @param key a db pool configuration key
369     * @return the database pool name
370     */
371    public static String getDbPoolName(CmsParameterConfiguration configuration, String key) {
372
373        return configuration.get(KEY_DATABASE_POOL + '.' + key + '.' + KEY_POOL_URL);
374    }
375
376    /**
377     * Returns a list of available database pool names.<p>
378     *
379     * @param configuration the configuration to read the pool names from
380     *
381     * @return a list of database pool names
382     */
383    public static List<String> getDbPoolUrls(CmsParameterConfiguration configuration) {
384
385        List<String> dbPoolNames = new ArrayList<String>();
386        List<String> driverPoolNames = configuration.getList(CmsDriverManager.CONFIGURATION_DB + ".pools");
387
388        for (String driverPoolName : driverPoolNames) {
389            dbPoolNames.add(getDbPoolName(configuration, driverPoolName));
390        }
391        return dbPoolNames;
392    }
393
394    /**
395     * Returns the name of the default database connection pool.<p>
396     *
397     * @return the name of the default database connection pool
398     */
399    public static String getDefaultDbPoolName() {
400
401        return OPENCMS_DEFAULT_POOL_NAME;
402    }
403}