From Ingres Community Wiki

Contents 1 Preamble - Liferay Portal and Ingres Database 2 Installation and Configuration 2.1 Ingres Database 2.1.1 Installation 2.1.2 Configuration 2.2 Liferay Portal 2.2.1 Installation 2.2.2 Configuration 3 The installation Bundle 4 The DBUtil class 4.1 Open issues 5 A portlet creation tutorial 6 Attachments 7 Update (to include tomcat 5, 6 and Ingres 10 consideration) 7.1 Installation 7.2 Configuration

Preamble - Liferay Portal and Ingres Database

Liferay Portal is a popular open source web platform for delivering a variety of business and collaboration solutions, and has been proven in Fortune 500 customers. Enterprise Application Integration (EAI), revenue-generating social network platforms, knowledge sharing workspaces, and self-service portals are just some of the many potential use cases.

This page gives information how to use Liferay Portal 5.2.x and the Ingres Database (versions 9.3 and 10) to create a robust and reliable combination to meet your business critical needs. Ingres is committed to having our products work with leading open source applications, and this is one example of how we are trying to help make things easier for our customers. Community input and support is important, so please post tips, ideas, comments and corrections that will make this even more useful for the Ingres and Liferay communities.

Please be aware that there are two versions of Liveray 5.2.x , one bundled with Tomcat 5.5 and one bundled with Tomcat 6.0.18. There are some setup differences related to Tomcat versions which are explained below.

Please note that we are in the process of testing Liferay 6.x with Ingres, and a new document will be released on that topic in the near future.

Installation and Configuration

Ingres Database

Liferay was tested with Ingres Database 9.3 b149 on Linux and Windows. Later builds of Ingres will work as well. Because Liferay needs features like 'LIKE ON CLOB' it is not possible to use an Ingres version earlier than Ingres Database 9.3 .

Installation

I recommend to install Ingres on Linux by using the 'ingbuid' installation tool. The advantage is that your server host does not need a graphical user interface. However, also the installation as RPM or with a graphical user interface would work. Further details can be found in the Ingres documentation.

Here the recommend steps for the 'ingbuild' installation.

Note: Steps 1-6 must be performed as user 'root'.

Preparations:

1. Create a user named 'ingres': useradd ingres
2. Assign a password to this user: passwd ingres
3. Create the folder /opt/Ingres: cd /opt; mkdir Ingres
4. Create a subfolder named by your installation identifier. In our case we name it 'LR': cd /opt/Ingres; mkdir LR
5. Change the owner of the directories to user 'ingres': chown -R ingres /opt/Ingres
6. Switch the user to 'ingres': su - ingres
   
7. Create a script which sets the installation environment inside the folder '/opt/Ingres/LR'. Name the script 'loadLRenv.bash'. Add the following lines:

#!/bin/bash
export II_SYSTEM=/opt/Ingres/LR
export PATH=/bin:/usr/bin:$II_SYSTEM/ingres/bin:$II_SYSTEM/ingres/utility
export LD_LIBRARY_PATH=/lib:/usr/lib:$II_SYSTEM/ingres/lib
export II_INSTALLATION=LR

Note: The following steps must be performed logged in as user 'ingres'.

Installing Ingres:

1. Load the installation environment : source /opt/Ingres/LR/loadLRenv.bash
2. Extract the file 'ingres.tar' from the archive 'ingres-x.x.x-yyy-NTPL-com-pc-linux-ingbuild-i386.tgz' that you downloaded from http://esd.ingres.com. (Where 'x' stands for the Ingres version and 'y' for the build number.) : gunzip ingres-x.x.x-yyy-NTPL-com-pc-linux-ingbuild-i386.tgz; tar -xvf ingres-x.x.x-yyy-NTPL-com-pc-linux-ingbuild-i386.tgz
3. Copy the file 'ingres.tar' to the Ingres installation folder: cp ingres.tar $II_SYSTEM/
4. Change the directory to the Ingres installation directory: cd $II_SYSTEM
5. Create a subfolder named 'ingres': mkdir ingres
6. Extract the 'ingbuild' tool from 'ingres.tar': tar -xvf ingres.tar install
7. Move the folder 'install' to it's final destination: mv install ingres/
8. Run the 'ingbuild' installation tool: $II_SYSTEM/ingres/install/ingbuild -express $II_SYSTEM/ingres.tar
9. Remove the temporarily created file 'ingres.tar': rm ingres.tar

Note: This sets up all products that are available in your distribution of Ingres. 'ingbuild' can also be used to install only the required features. Please read the Ingres documentation for further details.

Check your installation succeeded:

1. Change to user ingres: su - ingres
2. Load the environment: source /opt/Ingres/LR/loadLRenv.bash
3. Start Ingres, and qualify all services are starting up without problems: ingstart
4. Stop Ingres, and qualify all services are shutting down without problems: ingstop

Note: Find more about the Ingres configuration basics in the next chapter.

Configuration

Now you have installed Ingres. To be able to use it you have to load the 'loadLRenv.bash' script which was mentioned above. Therefore the Linux command 'source' can be used with this script.

The Ingres configuration tool is named 'cbf' (Configuration by Forms). Find further information regarding the usage in the Ingres documentation. Basic configuration steps on the DBMS for Liferay are:

1. Resize the transaction log file to 512 MB
2. Increase the number of allowed connections to 128
3. For heavy loads (like imports) you should set the readlock mode to 'no lock', the default is 'shared'
4. The table_auto_structure feature should be enabled
5. The number of locks per transaction should be increased to 2500, especially if you want to import data (e.G. LDAP syncronization)
6. 2500 locks per transaction may cause that the derived parameter of the total number of locks has a high value assigned. You can limit it 25000 .

Further tasks are:

1. As user 'root': Execute the 'mkvalidpw' command to enable remote authentication. Do not forget to restart the DBMS afterwards.
2. Create a system user named 'liferay' and use the 'accessdb' tool to add him as DBMS user and give him the rights to create databases and to be an operator
3. As user 'liferay' create a database for Liferay Portal: createdb lportal
4. Also as user 'liferay' load the Liferay database by using the script 'portal-ingres.sql': sql lportal < portal-ingres.sql > /tmp/portal-ingres.log

Note: Don't worry, an installation bundle will be available which performs those configuration steps.

Liferay Portal

Installation

You can download the current version of Liferay Portal (which means Liferay Portal 5.2.3) from SourceForge. To install it please perform the following steps

1. Create a folder /opt/Liferay
2. Change the owner of this folder to be the user 'liferay'
3. As user 'liferay' extract the just downloaded zip-file to /opt/Liferay. Use the command 'unzip' therefore

"Note:" Sun's Java >1.5 is required to start the Tomcat application server.

Configuration

The following steps are required to configure Liferay to use an Ingres database:

1. Copy the Ingres JDBC driver, which means the file $II_SYSTEM/ingres/lib/iijdbc.jar, to Tomcat's lib folder ($CATALINA_HOME/lib/ext)!
2. Replace the hibernate3.jar file in $CATALINA_HOME/webapps/ROOT/WEB-INF/lib with the one which is attached to this article because there is a known bug in the Ingres Dialect file of that particular version of Hibernate. See Ingres Hibernate and Ingres Hibernate Dialects for more information.

NB Check the version of Hibernate that the Liferay distribution is using and is compatible with Hibernate 3.3.1 before replacing with the hibernate3.jar attached to this article.

1. Edit the configuration file 'portal-ext.properties' which can be found at $CATALINA_HOME/webapps/ROOT/WEB-INF/classes/portal-ext.properties . It should as the following:

#
# Database connection
# 
jdbc.default.jndi.name=
jdbc.default.driverClassName=com.ingres.jdbc.IngresDriver
jdbc.default.url=jdbc:ingres://localhost:LR7/lportal;TZ=$TIMEZONE
jdbc.default.username=$USER	
jdbc.default.password=$PASSWORD

#
# Hibernate settings
#

hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N' 
hibernate.dialect=org.hibernate.dialect.IngresDialect			
hibernate.isolation=2
hibernate.show_sql=true

#
# Liferay settings
#

index.on.startup=true
session.timeout.auto.extend=true

It's important to set the time zone to the same value as your Ingres installation is using. Use the tool 'ingprenv' to get that value.

The installation Bundle

There is a bundle available but the versions of Ingres Database and Liferay Portal are no longer up to date. An updated bundle will be available soon.

The DBUtil class

The database system vendor specific source code is part of the package 'com.liferay.portal.tools.sql'. You can browse the source code by using the following link: com.liferay.portal.tools.sql. The classes you can find here are responsible to deploy the database schema. For Ingres a new class IngresUtil was created. Also some changes at the DBUtil class were required to get Ingres supported.

There are 2 ways to get the database schema deployed by using these classes:

• Point Liferay Portal to an empty database
• Build Liferay Portal and you get the database schema related SQL scripts as part of the build result.

I prefer the second way because it allows to make changes at the SQL (e.G. regarding the index structures) before you load the schema to the database. The SQL scripts for Liferay Portal 5.2.2 and Liferay Portal 5.2.3 are attached to this article.

Open issues

• In Ingres a single quote is escaped by another single quote. One insert performed by the portal-ingres.sql script, which was created by the IngresUtil class, fails because the single quote is escaped by an backslash
• If deploying the database schema by using an empty database, some index creations are causing warnings because of a too big width for ISAM indexes.

A portlet creation tutorial

The idea is to give an overview on Liferay's capabilities from a view of a Developer. Until now there is only the idea, but it may follow more later.

Attachments

• Hibernate including the fixed Ingres Dialect file
• portal-ingres.sql for Liferay 5.2.2
• portal-ingres.sql for Liferay 5.2.3

--David.Maier@*** 08:21, 7 September 2009 (CDT)

Update (to include tomcat 5, 6 and Ingres 10 consideration)

Installation

1. Download Liferay Portal from SourceForge.net :
   1. Portal/5.2.3/liferay-portal-tomcat-5.5-5.2.3.zip/download
   2. Portal/5.2.3/liferay-portal-tomcat-6.0-5.2.3.zip/download
2. Create an operating system user called liferay: useradd liferay (consult the useradd manpages for complete syntax)
3. Extract the just downloaded zip-file to $HOME by using the command unzip. However Liferay can be installed in any place. It must not be installed by user ingres.
4. Make sure Sun Java > 1.5 is installed (java –version), otherwise the Tomcat application server will not start

Configuration

1. Define and export environment variable CATALINA_HOME to where the root directory of tomcat is and JAVA_HOME (if it is not defined) to JAVA root directory. (for example export CATALINA_HOME=$HOME/liferay55/tomcat-5.5.27, and export JAVA_HOME=/usr/lib/jvm/jre-1.6.0-openjdk.x86_64)
2. Load Ingres environment variables (see ~ingres/loadLRenv.bash as outlined in the Ingres setup section)
3. For Tomcat 6.x as liferay run: cp $II_SYSTEM/ingres/lib/iijdbc.jar $CATALINA_HOME/lib/ext or if Tomcat 5.x as liferay run: cp $II_SYSTEM/ingres/lib/iijdbc.jar $CATALINA_HOME/common/lib/ext
4. Replace the hibernate3.jar file in $CATALINA_HOME/webapps/ROOT/WEB-INF/lib with the one which is attached to the previous article.
5. Edit the configuration file portal-ext.properties. There are several ways to address this and the procedure is identical regardless of the Tomcat server used. Make sure replace liferay_password with your liferay user’s password. The portal-ext.properties file is not in the Liferay bundle unless you build from source code.

   5.1 (preferred) Edit and build a new portal-ext.properties in $CATALINA_HOME/webapps/ROOT/WEB-INF/classes/

   For Ingres 10:

   jdbc.default.driverClassName=com.ingres.jdbc.IngresDriver

   jdbc.default.url=jdbc:ingres://localhost:LR7/lportal;send_intbool=true

   jdbc.default.username=liferay

   jdbc.default.password=liferay_password

   # Hibernate settings

   hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N'

   hibernate.dialect=org.hibernate.dialect.IngresDialect

   hibernate.isolation=2

   hibernate.show_sql=true

   # Liferay settings

   index.on.startup=true

   session.timeout.auto.extend=true

   For Ingres 9.x:

   jdbc.default.driverClassName=com.ingres.jdbc.IngresDriver

   jdbc.default.url=jdbc:ingres://localhost:LR7/lportal

   jdbc.default.username=liferay

   jdbc.default.password=liferay_password

   # Hibernate settings

   hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N'

   hibernate.dialect=org.hibernate.dialect.IngresDialect

   hibernate.isolation=2

   hibernate.show_sql=true

   # Liferay settings

   index.on.startup=true

   session.timeout.auto.extend=true

   5.2 Get the portal-ext.properties from the source code and, put it in $CATALINA_HOME/webapps/ROOT/WEB-INF/classes/ , edit it , comment the Hypersonic section, uncomment Ingres section and make the correct changes for Ingres like above in 5.1

   5.3 Make a copy in a temp area of $CATALINA_HOME/webapps/ROOT/WEB-INF/lib/portal-impl.jar and unpack it using jar xfv . Remove the portal-impl.jar file and edit portal.properties and make the changes for Ingres like above in 5.1. Then use jar cvf to re-create the portal-impl.jar and put it back into $CATALINA_HOME/webapps/ROOT/WEB-INF/lib/

6. To startup the install: $CATALINA_HOME/bin/startup.sh
7. To shutdown: $CATALINA_HOME/bin/shutdown.sh
8. To check the server status: $CATALINA_HOME/logs/catalina.out
9. To connect to the liferay: http://localhost:8080 (or use hostname or IP for the web address), you are there.

Armand.Pirvu@ingres.com 10:50, 7 September 2010 (CDT)