[opencms-dev] SOLVED (?) Separate database with OpenCms

[Enrico Ballarin Dolfin]

Hi

Thanks to the people that suggested me several solutions about how to access a 
separate database from OpenCms.

Unfortunately the suggestions were not really satisfiying for me in the sense 
that I had to make configuration changes in Tomcat etc. My impression was 
that the suggestions were not well integrating with OpenCms.

Analyzing the java sources and trying a little bit I could find a possible 
solution about how to integrate well a separate database with OpenCms.
This way you can use a separate database pool to access a separate database.

WARNING: this suggested POSSIBLE solution is still to be discussed and I don't 
take any responsibilty for possible damages caused by using it. If you use 
it, you do it alone under your own responsibility.

Any feedback in form of comment or suggestion is welcome.

Enrico

=================================================================================
CREATE THE SEPARATE DATABASE

1) Create a new mysql database e.g. appdb.

2) Create a new mysql user e.g. appuser with password **********

3) Add a table and a few data e.g.

CREATE TABLE people (
  id int(11) NOT NULL,
  name char(32) NOT NULL,
  PRIMARY KEY  (id)
);

INSERT INTO people VALUES (1,'John');
INSERT INTO people VALUES (2,'Tom');
INSERT INTO people VALUES (3,'Mary');
INSERT INTO people VALUES (4,'Susan');
INSERT INTO people VALUES (5,'Paul');

=================================================================================
EDIT THE CONFIGURATION FILE opencms.properties

1) Add a second database pool "application" in db.pools

2) Copy the "Configuration of the default database pool" block and change it
   according to the separate database.

3) Copy the entries in "Configuration for statement pooling" and change in the
   copied lines 'default' to 'application'. 

---------------------------------------------------------------------------------

#
# Declaration of database pools
#################################################################################
db.pools=default,application

#
# Configuration of the default database pool
#################################################################################
# name of the JDBC driver
db.pool.default.jdbcDriver=org.gjt.mm.mysql.Driver

# URL of the JDBC driver
db.pool.default.jdbcUrl=jdbc:mysql://your-host:3306/opencms

...
...
...

# connections will be validated before they are borrowed from the pool
db.pool.default.testOnBorrow=false

#
# Configuration of the application database pool
#################################################################################
# name of the JDBC driver
db.pool.application.jdbcDriver=org.gjt.mm.mysql.Driver

# URL of the JDBC driver
db.pool.application.jdbcUrl=jdbc:mysql://your-host:3306/appdb

# optional parameters for the URL of the JDBC driver
db.pool.application.jdbcUrl.params=?useUnicode\=true&characterEncoding\=UTF-8

# user name to connect to the database
db.pool.application.user=appuser

# password to connect to the database
db.pool.application.password=**********

# the URL to make the JDBC DriverManager return connections from the DBCP pool
db.pool.application.poolUrl=opencms:application

# the maximum number of objects that can be borrowed from the pool
db.pool.application.maxActive=25

# the maximum amount of time before throwing an exception when the pool is 
exhausted
db.pool.application.maxWait=2000

# the maximum number of objects that can sit idled in the pool
db.pool.application.maxIdle=10

# the query to validate connections
db.pool.application.testQuery=

# action to take when the pool is exhausted {grow|block|fail}
db.pool.application.whenExhaustedAction=block

# connections will be validated before they are borrowed from the pool
db.pool.application.testOnBorrow=false

#
# Configuration for statement pooling
#################################################################################
db.statements.default.pooling=true
db.statements.default.maxActive=200
db.statements.default.maxWait=2000
db.statements.default.maxIdle=100
db.statements.default.whenExhaustedAction=block

db.statements.application.pooling=true
db.statements.application.maxActive=200
db.statements.application.maxWait=2000
db.statements.application.maxIdle=100
db.statements.application.whenExhaustedAction=block

=================================================================================
CREATE A TEST JSP FILE AND TEST THE RESULT

1) Create in OpenCms the file dbtest.jsp with this content: 

---------------------------------------------------------------------------------

<%@page import="java.sql.*,org.opencms.db.*,org.opencms.main.*"%>
<%
java.sql.Connection con = 
OpenCms.getSqlManager().getConnection("application");

Statement stmt = con.createStatement();
ResultSet rs;
rs = stmt.executeQuery("select id, name from people");

while (rs.next()) {
  %><%= rs.getInt(1) %> <%= rs.getString(2) %><br><%
}
rs.close();
stmt.close();

con.close();
%>

---------------------------------------------------------------------------------

2) Restart tomcat and look with the browser at the file dbtest.jsp, that
   should display a page with this content:

1 John
2 Tom
3 Mary
4 Susan
5 Paul

=================================================================================