02 PostgreSQL

Below are the steps required to integrate Postgres with uPortal

This was written for version 2.4.2 but should work on 2.5.* and earlier version of uPortal as well.

Download the latest Postgres http://www.postgresql.org/.
Make the postgresql executable, ensuring that you use the --with-java parameter to generate the postgresql.jar.
Create the database according to the postgres documents. Assume the name 'portal' was chosen for the following instructions.
Copy the postgresql.jar to the lib directory of your portal development directory.

Edit rdbm.properties and uncommment the lines for postgres. Modify the URL, user and password as appropriate:

jdbcDriver=org.postgresql.Driver
jdbcUrl=jdbc:postgresql://localhost:5432/portal
jdbcUser=postgres
jdbcPassword=

If you are using PersonDirs.xml, edit the file and enter:

<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql://localhost:5432/portal</url>
<logonid>postgres</logonid>
<logonpassword></logonpassword>

Start Postgres.

In your portal development directory, issue the command

ant dbtest

If it works correct you should see something like


% ant dbtest
Buildfile: build.xml

prepare:

compile:
     [UPC:copy] Copying 2 files to C:\portal\build\WEB-INF\classes\properties

dbtest:
     [UPC:echo] Invoking DbTest
     [UPC:java] Database name: 'PostgreSQL'
     [UPC:java] Database version: '7.4.5'
     [UPC:java] Driver name: 'PostgreSQL Native Driver'
     [UPC:java] Driver version: 'PostgreSQL 7.4.5 JDBC3 with SSL (build 215)'
     [UPC:java] Driver class: 'org.postgresql.Driver'
     [UPC:java] Connection URL: 'jdbc:postgresql://localhost:5432/test'
     [UPC:java] User: 'postgres'
     [UPC:java] supportsANSI92EntryLevelSQL: true
     [UPC:java] supportsANSI92FullSQL: false
     [UPC:java] supportsCoreSQLGrammar: false
     [UPC:java] supportsExtendedSQLGrammar: false
     [UPC:java] supportsTransactions: true
     [UPC:java] supportsMultipleTransactions: true
     [UPC:java] supportsOpenCursorsAcrossCommit: false
     [UPC:java] supportsOpenCursorsAcrossRollback: false
     [UPC:java] supportsOpenStatementsAcrossCommit: true
     [UPC:java] supportsOpenStatementsAcrossRollback: true
     [UPC:java] supportsStoredProcedures: false
     [UPC:java] supportsOuterJoins: true
     [UPC:java] supportsFullOuterJoins: true
     [UPC:java] supportsLimitedOuterJoins: true
     [UPC:java] supportsBatchUpdates: true
     [UPC:java] supportsColumnAliasing: true
     [UPC:java] supportsExpressionsInOrderBy: true
     [UPC:java] supportsOrderByUnrelated: true
     [UPC:java] supportsPositionedDelete: false
     [UPC:java] supportsSelectForUpdate: true
     [UPC:java] supportsUnion: true
     [UPC:java] supportsUnionAll: true

     [UPC:java] getMaxColumnNameLength: 63
     [UPC:java] getMaxColumnsInIndex: 32
     [UPC:java] getMaxColumnsInOrderBy: 0
     [UPC:java] getMaxColumnsInSelect: 0
     [UPC:java] getMaxColumnsInTable: 1600
     [UPC:java] getMaxConnections: 8192
     [UPC:java] getMaxCursorNameLength: 63
     [UPC:java] getMaxIndexLength: 0
     [UPC:java] getMaxRowSize: 1073741824
     [UPC:java] getMaxStatements: 1
     [UPC:java] getMaxTableNameLength: 63
     [UPC:java] getMaxTablesInSelect: 0
     [UPC:java] getMaxUserNameLength: 63
     [UPC:java] getSearchStringEscape: \\

     [UPC:java] Table Types: INDEX,SEQUENCE,SYSTEM INDEX,SYSTEM TABLE,SYSTEM TOAST I
NDEX,SYSTEM TOAST TABLE,SYSTEM VIEW,TABLE,TEMPORARY INDEX,TEMPORARY TABLE,VIEW
     [UPC:java] SQL Types: bool,bytea,char,name,int8,int2,int2vector,int4,regproc,te
xt,oid,tid,xid,cid,oidvector,SET,pg_type,pg_attribute,pg_proc,pg_class,pg_shadow
,pg_group,pg_database,smgr,point,lseg,path,box,polygon,line,_line,float4,float8,
abstime,reltime,tinterval,unknown,circle,_circle,money,_money,macaddr,inet,cidr,
_bool,_bytea,_char,_name,_int2,_int2vector,_int4,_regproc,_text,_oid,_tid,_xid,_
...............

Edit properties/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.

Execute
```
ant db
```
to build the database tables
Execute
```
ant deploy
```
to copy files to your servlet container
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.