[patch] Improved usage of Commons DBCP Connection-pooling in JDBCStore

[Martin Kalén <mk...@apache.org>]

Greetings,
  the attached patch will extend the JDBCStore configuration possibilities
to allow setting of all (arbitrary in fact) Commons DBCP configuration
properties and to pass properties directly to the underlying JDBC-driver.

Commons DBCP have a plethora of options that can be studied in detail here:
  http://jakarta.apache.org/commons/dbcp/configuration.html

This patch addresses the following issues:

  1. Cannot set (or control usage of) the DBCP connection validation query

     After applying the patch, any DBCP properties like validationQuery,
     testOnBorrow, testWhileIdle, can be set in the Slide domain configuration
     with a "dbcp." prefix.

     Example nodestore config for Oracle:

         <parameter name="dbcpPooling">true</parameter>
         <parameter name="dbcp.maxWait">5000</parameter>
         <parameter name="dbcp.maxActive">50</parameter>
         <parameter name="dbcp.maxIdle">25</parameter>
         <parameter name="dbcp.validationQuery">SELECT 1 FROM DUAL</parameter>
         <parameter name="dbcp.testOnBorrow">true</parameter>
         <parameter name="dbcp.testOnReturn">false</parameter>
         <parameter name="dbcp.testWhileIdle">false</parameter>


  2. Cannot pass properties to the JDBC-driver

     (With MySQL it was previously possible, but not all drivers support
     extending the JDBC URL with ?property=value. Oracle for one does not.)

     After applying the patch, it is now possible to pass a list of JDBC-driver
     properties from the Slide domain configuration through the
     "dbcp.connectionProperties" setting. The format is semicolon separated,
     eg "defaultRowPrefetch=30;disableDefineColumnType=true" for setting
     Oracle row-prefetching to 30 rows per server rountrip and switching to
     the Oracle-recommended default of disableDefineColumnType=true for
     the Oracle 10 JDBC thin-driver.


  3. Shutting down the Slide JDBCStore does not shutdown DBCP pool

     After applying the patch, the DBCP pool will be decommisioned on
     the disconnect() call on a clean Slide exit (it can be reconnected
     by calling connect() and the isConnected semantics is updated
     so that connectIfNeeded will call connect() if the pool is down).


  4. Synchronization issue when using DBCP in multiple JDBCStore-
     instances within a Domain

     Naming the DBCP connection pool with System.identityHashCode is not
     guaranteed to be unique withing the Domain.

     After applying the patch, the JDBCStore will use the DBPC
     BasicDataSource with a Store-local (protected instance var)
     reference and it will be guaranteed that each Store instance
     uses a unique DBCP DataSource/pool instance.


Some of the DriverManager deadlock issues reported on the list and
in bugzilla should be addressed by #1 (set dbcp.maxWait != -1 to prevent
waiting forever for a new connection) and #4 above (.

If the patch gets applied I will do some more extensive testing with
a newly built Slide from CVS and see if we can close some bugzilla
issues as a result.

I have tested the patch with a real-world multithreaded application
using several Connection instances simultaneously and two different
JDBCStore configurations for different stores in the domain (one
fully JDBC-based and one with descriptors in DB and content in
the ContextTxFileContentStore).

Test were done with Oracle9i RDBMS, Oracle thin JDBC driver
  and JDBCStore (with patch applied) configured with OracleRDBMSAdapter.

A previous patch posted here, but not yet applied, was also applied
on the local source.
(See http://www.mail-archive.com/slide-dev@jakarta.apache.org/msg14543.html)

The patch is in unified format and created against the trunk, updated
from CVS around lunchtime today.

I have also updated the forrest xdoc for the J2EE/JDBC store HOWTO and
the patch includes this change and the resulting generated site-doc HTML.

Regards,
  Martin