AppFuseOnPostgreSQL

This page describes my the steps required and issues associated with running AppFuse on PostgreSQL. If you're developing on Windows or OS X, click here for install packages.

This howto assumes you've already installed PostgreSQL and your DBA username/password combination is postgres/postgres.

Table of Contents

• [1] Setup JDBC Driver
• [2] Basic Configuration Tweaks
• [3] Run JUnit Tests
• [4] Configure Tomcat to talk to PostgreSQL
• [5] Other issues
• [6] Sample CREATE DATABASE script

Setup JDBC Driver [#1]

Edit your build.properties file to reflect your new database of choice. MySQL settings are the defaults specified in properties.xml. Here is a sample for PostgreSQL:

PostgreSQL:

database.jar=${postgresql.jar}
database.type=postgresql
database.name=appfuse
database.host=localhost

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
database.driver_class=org.postgresql.Driver
database.url=jdbc:${database.type}://${database.host}/${database.name}
database.username=test
database.password=test

Code/Configuration Tweaks [#2]

Using Hibernate's generator-class="native" for id's in PostgreSQL fails. This is likely due to the fact that data is being inserted using DBUnit and the sequences get out of wack. Changing all id's to "increment" (example below) and seems to fix the problem. This worked on all the databases tested (MySQL, PostgreSQL and DB2).

Run JUnit Tests [#3]

No changes are needed to make AppFuse properly run the supplied dao tests.

Configure Tomcat to talk to PostgreSQL [#4]

Since AppFuse 1.3 the database-specific attributes in tomcat-context.xml and hibernate.cfg.xml are replaced at build-time. This means that nothing special is needed to get Tomcat to talk to PostgreSQL. For users with older versions of AppFuse see the note on the DB2 version of this page for more information.

Other issues [#5]

Turn off Hibernate Batch Processing for PostgreSQL
When an exception is thrown while performing batch processing in Hibernate on PostgreSQL the cause of the exception is never displayed in the stack trace. By turning off the batch processing exceptions get reported immediately and completely. This can be done by adding an entry into applicationContext-hibernate.xml. Within the element sessionFactory, locate the property hibernateProperties and make it look like this:

        <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">@HIBERNATE-DIALECT@</prop>

            <!-- turn off batch updated for PostgreSQL to get nicer exception messages -->
	    <prop key="hibernate.jdbc.batch_size">0</prop>
        </props>
        </property>

This will have an effect on the performance of the database, so consider the tradeoff of having better error messages.

Sample CREATE DATABASE script [#6]

-- create the test user
create user test password 'test';

-- create the database
create database appfuse owner test;

You can find it on the sub-directory metadata/sql, with the name postgresql-create.sql. One example, suppose your project's name is felini:

	$gilberto@dev/filine: psql -d template1 -U postgres
Password:
Welcome to psql 8.1.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# \imetadata/sql/postgresql-create.sql
DROP DATABASE
CREATE USER
CREATE DATABASE
template1=#