howto-database.txt

JAVA做的J2EE下CA认证系统 基于EJB开发

Set up a database for EJBCA in JBoss
====================================

The following databases have in some stage been tested with EJBCA:

* Hypersoniq (hsqldb) (default in JBoss)
* PostegreSQL 7.2 and 8.0 (http://www.postgresql.org/)
* MySQL (http://www.mysql.com/)
* Oracle 8i and 9i (http://www.oracle.com/)
* Sybase

Unless you are adventurous and know what you're doing, it is recommended to define
another datasource for ejbca and not reconfigure DefaultDS with another database
as you run the risk to jump into mapping bugs in the JBoss configuration and mess
up your server default configuration.

If all is well the automatic deployment script of EJBCA will configure all that for
you and you will be up and running in 30 seconds.
Configuration is done in ejbca.properties.

In a production environment you should use something else than the default Hypersonic database that comes 
with JBoss for the reasons:
1. Hypersonic database is in-memory, which means that over time it will consume more memory. If a large
numer of certificates is issued, it will become an issue after a while.
2. Hypersonic does not support full SQL, in particular ALTER statements. When a new version of EJBCA is 
released we can not create scripts that updates the database if some tables changed. This will make 
upgrades much much harder.

If you will be running a huuuge system you will probably need to run some analisis tools to perfect indexes
etc in the database, at least in the long run. Examples of such tools are:
http://sourceforge.net/projects/mysqa/

NOW ON TO THE HOWTO!

Troubleshooting database problems:
---------------------------------
* Have you (ejbca automatically does it) configured a <database-ds.xml> in JBOSS_HOME/server/default/deploy?
  Configuration is done in ejbca.properties.
* Do you have the correct username/password configured in <database-ds.xml>?
  Configuration is done in ejbca.properties.
* Is the JDBC driver installed in JBOSS_HOME/server/default/lib?
* Does you database accept incoming connection from the network, not only localhost or vice versa?
* Do you have a firewall blocking connections to the database?
* Can you connect to the database from another machine?
* Use ip-address instead of hostname in <database-ds.xml> (DNS problems?)

Configuring EJBCA
-----------------

0. Use 'ant clean' to clean up your repository if not already done.

1. Edit 'ejbca.properties' and follow the instructions to change the default values.
   Specify the 'datasource.jndi-name' to the one you chose.
   For example: 'EjbcaDS'

   There is a .ejbca.properties.sample that is heavily commented.

2. Use 'ant deploy', it will configure automatically all deployment descriptors such
   as ejb-jar.xml, jbosscmp-jdbc.xml, according to the database/jndi-name you chose before.

  It will also copy ejbca-ds.xml to ${jboss.home}/server/default/deploy.
  This file defines your database configuration such as the driver, url, username, password.
  All other parameters are optional, so don't bother with them unless you know what you're doing.
  Of course this ejbca-ds.xml should define the jndi-name as 'EjbcaDS' (or whatever name you chose
  in the previous step)

  If something is wrong, you should see it in the logs of your application server

XDoclet merge files for database specific table and column configurations are in src/deploy/ejb/merge/<database name>.

Configuring JBoss
-----------------

NOTE:

If you are using the default database HSQLDB: Nothing should be done, just install
JBoss and run.

The following is only if you want to replace the default database with another.

The description given here is for JBoss 3.x. Consider consulting the
official JBoss documentation.

Database configuration files in JBoss are located in the
<jboss-home>/server/default/deploy.

1. Install and setup the database your database.

2. Create a database for EJBCA (that is obviouslly the one you defined in .ejbca.properties)
Check your database documentation for more info (I know it is boring).

Typically for postgres: 'createdb -U postgres ejbca "database for ejbca"' will create
a database named 'ejbca' with description "database for ejbca" and with user postgres.

Typically for mysql: 'mysqladmin create ejbca' will create the database. 
Start 'mysql -u root mysql' and create the user with "grant all on ejbca.* to ejbca@'<host>' identified by '<pwd>'".

3. Put the JDBC driver for the database in <jboss-home>/server/default/lib/ (or replace default if you are running 
   another JBoss target, perhaps all?).

4. DONE! Start JBoss. Run tests with 'ant test:run'.
   Use your favorite database graphic editor to look at the beautiful database tables.


MySQL specifics
---------------
EJBCA have been tested with MySQL 3 and 4.

JDBC driver: mysql-connector-java-3.0.x.jar (preferably 3.0.9 or higher).

Download JDBC driver for mySQL from http://www.mysql.com/

Sometimes there can be problems with MySQL related to case sensitivity of database tables.
If you run into this see:
http://mysqld.active-venture.com/Name_case_sensitivity.html
http://dev.mysql.com/doc/refman/4.1/en/name-case-sensitivity.html

PostgreSQL specifics
--------------------
EJBCA have been tested with PostgreSQL 7.2 and 8.0.

JDBC driver: the 7.4 driver (development) is needed to support the JDBC used by
JBoss.

Download JDBC driver for PostgreSQL from http://jdbc.postgresql.org/.

Oracle specifics
----------------
Note that the default Oracle JDBC driver for Oracle 8 and 9 is seriously flawed and will not update 
BLOBS larger than a certain size. EJBCA uses some BLOBs, so unforturnately is doesn't work.
The driver from Oracle 10 works fine and can be downloaded from:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

The JDBC-driver from http://www.datadirect.com/ also works fine, but it's not for free.

JDBC driver: ojdbc14.zip 

Use latest driver from at least Oracle 10 that can be downloaded from http://www.oracle.com/.
We also had reports that some version of the Oracle 10 driver does not work, but that the driver 
from Oracle 9.2i works. Since oracles JDBC driver seems to be of such bad quality, we have to 
recommend that you try different versions until you find one that works...or use another database.
Version 10.1.0.4.0 is confirmed to work.

MS-SQL specifics
----------------
Microsofts JDBC driver:
http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/MSDN-FILES/027/001/779/msdncompositedoc.xml&frame=true

There is also an open source JDBC driver at:
http://jtds.sourceforge.net/
This driver is not tested by us (yet), but it has received very good reviews as beeing much better than Microsofts
driver, so I suggest you take it for a spin.