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.
...
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.
Code Block | ||||
---|---|---|---|---|
| ||||
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>8.2-507.jdbc3</version>
</dependency>
|
Opening /uPortal/pom.xml there is a section about 90 lines down that reads:
Code Block | ||||
---|---|---|---|---|
| ||||
<jdbc.groupId>postgresql</jdbc.groupId>
<jdbc.artifactId>postgresql</jdbc.artifactId>
<jdbc.version>8.2-507.jdbc3</jdbc.version>
|
We will add the PostgreSQL driver here.
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:
Code Block |
---|
...
##### PostgreSQL - example hibernate.connection.driver_class=org.postgresql.Driver |
...
hibernate.connection.url=jdbc:postgresql://localhost |
...
Code Block |
---|
<driver>org.postgresql.Driver</driver>
<url>jdbc:postgresql://localhost:5432/portal</url>
<logonid>postgres</logonid>
<logonpassword></logonpassword>
|
...
/up3theme
hibernate.connection.username=uportal
hibernate.connection.password=mypass
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
|
The /uPortal/uportal-impl/src/main/resources/properties/dbloader.xml properties file may also need to be modified. This file is used by the DbLoader tool to create the uPortal database tables and populate the database. It contains several sample entries which create db-type-mappings for different databases. Find the tags for an PostgreSQL database and modify the db-version, driver-name, and driver-version as necessary. For example:
Code Block | ||||
---|---|---|---|---|
| ||||
<db-type-mapping>
<db-name>PostgreSQL</db-name>
<db-version>8.2.5</db-version>
<driver-name>PostgreSQL Native Driver</driver-name>
<driver-version>PostgreSQL 8.2 JDBC3 with SSL (build 507)</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 using a different version of PostgreSQL you will need to modify there strings for db-version and driver-version
Testing The Configuration
Start Postgres and then in your portal development directory, issue the command:
...
No Format |
---|
ant dbtest
|
If it works
...
correctly you should see something like
...
No Format |
---|
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.jar to /home/edalquist/.m2/repository/org/jasig/portal/uportal-impl/3.0.0-SNAPSHOT/uportal-impl-3.0.0-SNAPSHOT.jar [echo] Invoking DbTest [java] INFO [main] spring.PortalApplicationContextLocator Mar/20 15:30:56.271 - Creating new lazily initialized GenericApplicationContext for the portal [java] INFO [main] jpa.HibernateJpaVendorAdapter Mar/20 15:30:58.634 - Setting CacheProvider 'org.jasig.portal.utils.cache.hibernate.EhCacheProvider@1779885' on ThreadLocal [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.naming.factory.url.pkgs=tyrex.naming, java.naming.provider.url=, java.naming.factory.initial=org.jasig.portal.jndi.DisposableMemoryContextFactory}' [java] INFO [main] properties.PropertiesManager Mar/20 15:31:00.710 - Property [org.jasig.portal.car.CarResources.directory] was requested but not found. [java] INFO [main] car.CarResources Mar/20 15:31:00.714 - CAR directory property 'org.jasig.portal.car.CarResources.directory' not specified. 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 GenericApplicationContext for the portal in 4497ms [java] Parsing jar:file:/home/edalquist/JavaClasses/workspace_33/uPortal_trunk/uportal-impl/target/uportal-impl-3.0.0-SNAPSHOT.jar!/properties/db/dbloader.xml... [java] WARN [main] rdbm.DatabaseMetaDataImpl Mar/20 15:31:00.897 - The uPortal database is not initialized, the database tests will not be performed. [java] INFO [main] rdbm.DatabaseMetaDataImpl Mar/20 15:31:00.897 - PostgreSQL (8.2.6) / PostgreSQL Native Driver (PostgreSQL 8.2 JDBC3 with SSL (build 507)) database/driver [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=INTEGER,local=integer], 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,now,quarter,second,week,year,timestampadd [java] Table Types: INDEX,SEQUENCE,SYSTEM INDEX,SYSTEM TABLE,SYSTEM TOAST |
...
INDEX,SYSTEM TOAST TABLE,SYSTEM VIEW,TABLE,TEMPORARY INDEX,TEMPORARY TABLE,VIEW [ |
...
java] SQL Types: bool,bytea,char,name,int8,bigserial,int2,int2vector,int4,serial,regproc, |
...
text,oid,tid,xid,cid,oidvector |
...
,pg_type,pg_attribute,pg_proc,pg_class, |
...
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,_cid,_oidvector,_bpchar,_varchar,_int8,_point,_lseg,_path,_box,_float4,_float8,_abstime,_reltime,_tinterval,_polygon,aclitem,_aclitem,_macaddr,_inet,_cidr,bpchar,varchar,date,time,timestamp,_timestamp,_date,_time,timestamptz,_timestamptz,interval,_interval,_numeric,timetz,_timetz,bit,_bit,varbit,_varbit,numeric,refcursor,_refcursor,regprocedure,regoper,regoperator,regclass,regtype,_regprocedure,_regoper,_regoperator,_regclass,_regtype,record,cstring,any,anyarray,void,trigger,language_handler,internal,opaque,anyelement,pg_autovacuum, |
...
Code Block |
---|
<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>
|
...
Code Block |
---|
ant db |
...
Code Block |
---|
ant deploy |
...
pg_attrdef,pg_constraint,pg_inherits,pg_index,pg_operator,pg_opclass,pg_am,pg_amop,pg_amproc,pg_language,pg_largeobject,pg_aggregate,pg_statistic,pg_rewrite,pg_trigger,pg_listener,pg_description,pg_cast,pg_namespace,pg_conversion,pg_depend,pg_database,pg_tablespace,pg_pltemplate,pg_authid,pg_auth_members,pg_shdepend,pg_shdescription,pg_toast_2604,pg_toast_2606,pg_toast_2609,pg_toast_1255,pg_toast_2618,pg_toast_2619,pg_toast_1260,pg_toast_1262,pg_toast_2396,pg_roles,pg_shadow,pg_group,pg_user,pg_rules,pg_views,pg_tables,pg_indexes,pg_stats,pg_locks,pg_cursors,pg_prepared_xacts,pg_prepared_statements,pg_settings,pg_timezone_abbrevs,pg_timezone_names,pg_stat_all_tables,pg_stat_sys_tables,pg_stat_user_tables,pg_statio_all_tables,pg_statio_sys_tables,pg_statio_user_tables,pg_stat_all_indexes,pg_stat_sys_indexes,pg_stat_user_indexes,pg_statio_all_indexes,pg_statio_sys_indexes,pg_statio_user_indexes,pg_statio_all_sequences,pg_statio_sys_sequences,pg_statio_user_sequences,pg_stat_activity,pg_stat_database,views,data_type_privileges,element_types,applicable_roles,cardinal_number,character_data,sql_identifier,information_schema_catalog_name,time_stamp,administrable_role_authorizations,attributes,check_constraint_routine_usage,check_constraints,column_domain_usage,column_privileges,column_udt_usage,columns,constraint_column_usage,constraint_table_usage,domain_constraints,domain_udt_usage,domains,enabled_roles,key_column_usage,parameters,referential_constraints,role_column_grants,role_routine_grants,role_table_grants,role_usage_grants,routine_privileges,routines,schemata,sequences,sql_features,pg_toast_10737,sql_implementation_info,pg_toast_10742,sql_languages,pg_toast_10747,sql_packages,pg_toast_10752,sql_parts,pg_toast_10757,sql_sizing,pg_toast_10762,sql_sizing_profiles,pg_toast_10767,table_constraints,table_privileges,tables,triggered_update_columns,triggers,usage_privileges,view_column_usage,view_routine_usage,view_table_usage
BUILD SUCCESSFUL
Total time: 10 seconds
|
Verify the values on the Database name, Database version, Driver name, Driver version match those entered in dbloader.xml exactly.