02 PostgreSQL

Below are the steps required to integrate Postgres with uPortal

JAR Configuration

The first step is to determine the correct information to use in the pom. Using a Maven Artifact Search Site and searching for "postgres" we find the following Maven dependency declaration here.

<dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>8.2-507.jdbc4</version>
</dependency>

Opening /uPortal/uportal-impl/pom.xml there is a section about 20 lines down that reads:

<!-- ***** Portal JDBC Driver *****
 | Add your JDBC Driver dependency here. If you are not using hsqldb you must change the scope
 | to test instead of just removing it as the driver is required for unit tests. 
 +-->
<dependency>
    <groupId>hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>${hsqldb.version}</version>
    <scope>compile</scope>
</dependency>

We will add the PostgreSQL driver here and do as the comment says and change the <scope> tag of the hsqldb driver to test.

<!-- ***** Portal JDBC Driver *****
 | Add your JDBC Driver dependency here. If you are not using hsqldb you must change the scope
 | to test instead of just removing it as the driver is required for unit tests. 
 +-->
<dependency>
    <groupId>postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>8.2-507.jdbc4</version>
</dependency>

<dependency>
    <groupId>hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>${hsqldb.version}</version>
    <scope>test</scope>
</dependency>

JDBC Configuration

Edit /uPortal/uportal-impl/src/main/resources/properties/rdbm.properties and uncommment the lines for postgres. Modify the URL, username and password as appropriate:

##### PostgreSQL - example
hibernate.connection.driver_class=org.postgresql.Driver
hibernate.connection.url=jdbc:postgresql://my.school.edu/portal
hibernate.connection.username=test
hibernate.connection.password=mypass
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect

Testing The Configuration

Start Postgres.
In your portal development directory, issue the command

ant dbtest

If it works correct you should see something like

TODO

1. Edit /uPortal/uportal-impl/src/main/resources/properties/db/dbloader.xml and verify that there is an entry just like the database version you displayed in the last step:

     <db-type-mapping>
       <db-name>PostgreSQL</db-name>
       <db-version>7.4.5</db-version>
       <driver-name>PostgreSQL Native Driver</driver-name>
       <driver-version>PostgreSQL 7.4.5 JDBC3 with SSL (build 215)</driver-version>
       <type><generic>LONGVARCHAR</generic><local>TEXT</local></type>
       <type><generic>VARCHAR</generic><local>VARCHAR</local></type>
       <type><generic>LONGVARBINARY</generic><local>BYTEA</local></type>
       <type><generic>VARBINARY</generic><local>BYTEA</local></type>
       <type><generic>BLOB</generic><local>OID</local></type>
     </db-type-mapping>
   

   If there is not an exact match, then add it.
2. Execute

   ant db

   to build the database tables
3. Execute

   ant deploy

   to copy files to your servlet container
4. Start your servlet container.

If you upgrade Postgres, you should replace postgresql.jar and update entries in dbloader.xml. The jar file may
also be placed in the Tomcat common/lib directory.