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.jdbc3</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.jdbc3</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://localhost/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:
<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:
ant dbtest
If it works correctly you should see something like
[java] INFO [main] rdbm.DatabaseMetaDataImpl Feb/04 17:14:22.313 - PostgreSQL (8.2.5) / PostgreSQL Native Driver (PostgreSQL 8.2 JDBC3 with SSL (build 506)) database/driver
[java] Connected To: jdbc:postgresql://localhost/up3theme
[java] Supports:
[java] Prepared Statements: true
[java] Outer Joins: true
[java] Transactions: true
[java] {ts metasyntax: true
[java] TO_DATE(): false
[java] Database name: 'PostgreSQL'
[java] Database version: '8.2.5'
[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/up3theme'
[java] User: 'uportal'
[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] 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,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,up_versions,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,up_user,up_user_param,up_user_locale,up_person_dir,up_permission,up_user_layout,up_layout_struct,up_layout_struct_locale,up_layout_param,pg_toast_50609,up_channel,up_channel_param,up_chan_type,pg_toast_50620,up_sequence,up_user_ua_map,up_user_profile,up_user_profile_locale,up_ss_user_parm,up_ss_user_atts,up_ss_theme,pg_toast_50645,up_ss_theme_parm,up_ss_struct,pg_toast_50656,up_ss_struct_par,up_ss_map,up_mime_type,pg_toast_50669,up_group,up_entity_type,up_group_membership,upc_perm_mgr,pg_toast_50688,up_entity_cache_invalidation,up_entity_lock,up_entity_prop,up_portlet_def,up_portlet_ent,up_portlet_pref,pg_toast_50719,up_portlet_prefs,up_portlet_pref_values,pg_toast_50730,hibernate_sequence,up_chan_type_mdata,pg_toast_50766,up_channel_mdata,up_layout_struct_mdata,up_user_profile_mdata,up_user_layout_mdata,up_user_mdata
BUILD SUCCESSFUL
Verify the values on the Database name, Database version, Driver name, Driver version match those entered in dbloader.xml exactly.