PostgreSQL

Below are the steps required to integrate Postgres with uPortal

Step 1: Add the Dependency

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.

From the uPortal source directory, open the uPortal/pom.xml file and add the following dependency with your compatible version:

<dependency>
    <groupId>postgresql</groupId>  <!-- The groupId is org.postgresql for version 9.2 and later -->
    <artifactId>postgresql</artifactId>
    <version>8.2-507.jdbc3</version>
</dependency>

2. Also, edit the jdbc setting in the pom.xml file to reflect your database

<jdbc.groupId>postgresql</jdbc.groupId>  <!-- The groupId is org.postgresql for version 9.2 and later -->
<jdbc.artifactId>postgresql</jdbc.artifactId>
<jdbc.version>8.2-507.jdbc3</jdbc.version>

Note: groupId for Postgres JDBC drivers changed for versions 9.2+ from "postgresql" to "org.postgresql".

Step 2: Configure the Database Filter

1. In the filters folder, locate the default local.properties file under uPortal-4.0.x/filters/local.properties and configure the Database Connection Settings

uPortal/filters/local.properties

# HSQL Configuration
environment.build.hsql.port=8887

# Database Connection Settings (Uncomment the Maven Filters section in rdbm.properties)
environment.build.hibernate.connection.driver_class=org.postgresql.Driver
environment.build.hibernate.connection.url=jdbc:postgresql://my.school.edu/uPortal
environment.build.hibernate.connection.username=user
environment.build.hibernate.connection.password=password
environment.build.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
environment.build.hibernate.connection.validationQuery=select 1

Step 3: Test the Configuration

Start Postgres and then in your portal development directory, issue the command:

ant dbtest

If it works correctly you should see something like

Buildfile: build.xml

dbtest:

install-parent-pom:
[artifact:install] [INFO] Installing /home/edalquist/JavaClasses/workspace_33/uPortal_trunk/pom.xml to
/home/edalquist/.m2/repository/org/jasig/portal/uportal-parent/3.0.0-SNAPSHOT/uportal-parent-3.0.0-SNAPSHOT.pom
    [touch] Creating /tmp/uportal-parent.pom-179737927-marker
[artifact:dependencies] Downloading: javax/script/script-api/1.0/script-api-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: javax/script/script-api/1.0/script-api-1.0.pom from central
[artifact:dependencies] Downloading: groovy/groovy-all/1.0/groovy-all-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: groovy/groovy-all/1.0/groovy-all-1.0.pom from central
[artifact:dependencies] Downloading: javax/script/groovy-engine/1.0/groovy-engine-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: javax/script/groovy-engine/1.0/groovy-engine-1.0.pom from central
     [echo] Artifact '/home/edalquist/JavaClasses/workspace_33/uPortal_trunk/uportal-impl/target/uportal-impl-3.0.0-SNAPSHOT.jar' is up-to-date
[artifact:install] [INFO] Installing /home/edalquist/JavaClasses/workspace_33/uPortal_trunk/uportal-impl/target/uportal-impl-3.0.0-SNAPSHOT...
     [echo] Invoking DbTest
     [java]  INFO [main] spring.PortalApplicationContextLocator Mar/20 15:30:56.271 - Creating new lazily initialized GenericApplication...
     [java]  INFO [main] jpa.HibernateJpaVendorAdapter Mar/20 15:30:58.634 - Setting CacheProvider 'org.jasig.portal.utils.cache....
     [java] AbandonedObjectPool is used (org.apache.commons.dbcp.AbandonedObjectPool@1dcc4cd)
     [java]    LogAbandoned: true
     [java]    RemoveAbandoned: true
     [java]    RemoveAbandonedTimeout: 300
     [java]  INFO [main] jndi.DisposableMemoryContextFactory Mar/20 15:31:00.683 - Created new MemoryContext with environment...
     [java]  INFO [main] properties.PropertiesManager Mar/20 15:31:00.710 - Property [org.jasig.portal.car.CarResources.directory]...
     [java]  INFO [main] car.CarResources Mar/20 15:31:00.714 - CAR directory property 'org.jasig.portal.car.CarResources.directory'...
Defaulting to well-known directory '/WEB-INF/cars'.
     [java]  INFO [main] jndi.JndiManagerImpl Mar/20 15:31:00.744 - Initialized portal JNDI context
     [java]  INFO [main] spring.PortalApplicationContextLocator Mar/20 15:31:00.770 - Created new lazily initialized GenericApplication...
     [java] Parsing jar:file:/home/edalquist/JavaClasses/workspace_33/uPortal_trunk/uportal-impl/target/uportal-impl-3.0.0-SNAPSHOT.jar!/...
     [java]  WARN [main] rdbm.DatabaseMetaDataImpl Mar/20 15:31:00.897 - The uPortal database is not initialized, the database tests...
     [java]  INFO [main] rdbm.DatabaseMetaDataImpl Mar/20 15:31:00.897 - PostgreSQL (8.2.6) / PostgreSQL Native Driver (PostgreSQL 8.2...
     [java]     Connected To: jdbc:postgresql://localhost/jasig
     [java]     WARNING: uPortal tables do no exist, not all meta-data tests were executed.

     [java] Database name:    'PostgreSQL'
     [java] Database version: '8.2.6'
     [java] Driver name:      'PostgreSQL Native Driver'
     [java] Driver version:   'PostgreSQL 8.2 JDBC3 with SSL (build 507)'
     [java] Driver class:     'PostgreSQL Native Driver'
     [java] Connection URL:   'jdbc:postgresql://localhost/jasig'
     [java] User:             'up3dev'

     [java] Type Mappings: [Type[genericType=VARBINARY,local=BYTEA], Type[genericType=LONGVARBINARY,local=BYTEA], Type[genericType...
Type[genericType=VARCHAR,local=VARCHAR],Type[genericType=LONGVARCHAR,local=TEXT]]

     [java] supportsANSI92EntryLevelSQL: true
     [java] supportsANSI92FullSQL:       false
     [java] supportsCoreSQLGrammar:     false
     [java] supportsExtendedSQLGrammar: false

     [java] supportsTransactions:         true
     [java] supportsMultipleTransactions: true
     [java] supportsOpenCursorsAcrossCommit:      false
     [java] supportsOpenCursorsAcrossRollback:    false
     [java] supportsOpenStatementsAcrossCommit:   true
     [java] supportsOpenStatementsAcrossRollback: true

     [java] supportsStoredProcedures:     true
     [java] supportsOuterJoins:           true
     [java] supportsFullOuterJoins:       true
     [java] supportsLimitedOuterJoins:    true
     [java] supportsBatchUpdates:         true
     [java] supportsColumnAliasing:       true
     [java] supportsExpressionsInOrderBy: true
     [java] supportsOrderByUnrelated:     true
     [java] supportsPositionedDelete:     false
     [java] supportsSelectForUpdate:      true
     [java] supportsUnion:                true
     [java] supportsUnionAll:             true

     [java] getMaxColumnNameLength: 63
     [java] getMaxColumnsInIndex:   32
     [java] getMaxColumnsInOrderBy: 0
     [java] getMaxColumnsInSelect:  0
     [java] getMaxColumnsInTable:   1600
     [java] getMaxConnections:      8192
     [java] getMaxCursorNameLength: 63
     [java] getMaxIndexLength:      0
     [java] getMaxRowSize:          1073741824
     [java] getMaxStatements:       0
     [java] getMaxTableNameLength:  63
     [java] getMaxTablesInSelect:   0
     [java] getMaxUserNameLength:   63
     [java] getSearchStringEscape:  \
     [java] getStringFunctions:     ascii,char,concat,lcase,left,length,ltrim,repeat,rtrim,space,substring,ucase,replace
     [java] getSystemFunctions:     database,ifnull,user
     [java] getTimeDateFunctions:   curdate,curtime,dayname,dayofmonth,dayofweek,dayofyear,hour,minute,month,monthname,...

     [java] Table Types: INDEX,SEQUENCE,SYSTEM INDEX,SYSTEM TABLE,SYSTEM TOAST INDEX,SYSTEM TOAST TABLE,SYSTEM VIEW,TABLE,...
     [java] SQL Types:   bool,bytea,char,name,int8,bigserial,int2,int2vector,int4,serial,regproc,text,oid,tid,xid,cid,oidvector,...
BUILD SUCCESSFUL
Total time: 10 seconds

Step 4: Build and Deploy

Following a successful test, you can execute the command below to build the database tables and copy files to your servlet container.

Note: Executing the command "ant clean initportal" will drop and recreate the database tables and all existing data will be lost. This will result in a clean uPortal database structure. If you want to keep the contents of your existing database, use "ant clean deploy-war"

ant clean initportal

Step 5: Restart Tomcat

Having problems with these instructions?

Please send us feedback at uportal-user@lists.ja-sig.org