From Ingres Community Wiki

Contents 1 Service desk request tracking this work 2 Installation Guide 2.1 Notes 2.2 Required Packages and Software 2.3 Building and Installing the Driver 2.3.1 Linux/Mac OS X/UNIX 2.3.1.1 Short install 2.3.1.2 Long install 2.3.1.3 Troubleshooting 2.3.2 Windows 2.4 Configuring Apache and Ingres 2.5 Installing Drupal 3 Known Issues 4 Known Issues with modules 4.1 Issues with the Views Module 5 Known modules that work fine 5.1 Limitations 5.2 Work Arounds

Service desk request tracking this work

Please see service desk issue # 139341

Installation Guide

Notes

This guide is intended for audiences using Fedora or a similar operating system.

If you are using Windows, follow this full tutorial which is intended for newcomers to Ingres and Drupal.

Required Packages and Software

• Ingres
  • Make sure Ingres character set (II_CHARSET) is ISO88591 (the default)
• Apache 2
• PHP 5.x
  • Linux/Mac OS X/UNIX PHP 5 development package
• Additional PHP extensions mbstring and gd
• Install PHP using using one of:
  • Debian/Ubuntu Linux :

    sudo apt-get install apache2 apache2-dev php5 php5-dev php-gd

  • Redhat Linux :

    sudo yum install httpd httpd-devel php php-devel php-mbstring php-gd

  • openSUSE:

    sudo yast -i apache2 apache2-devel php5 php5-devel php5-mbstring php5-gd

  • Or download the source from http://php.net

Building and Installing the Driver

Linux/Mac OS X/UNIX

Short install

• Execute the following:

  pecl install ingres

Enter the value for II_SYSTEM when prompted.

Long install

(when the pecl command is not available)

• Setup the Ingres environment using the provided script(s):

# For the sh/bash/ksh shells:
source ~ingres/.ingIIbash
# For the csh/tcsh shells
source ~ingres/.ingIIcsh

• Download the PHP driver source code from http://pecl.php.net/get/ingres and untar/unzip it

wget http://pecl.php.net/get/ingres
tar xvzf ingres

• Enter the newly created directory and execute the following commands (Note this assumes you have the necessary PHP development tools installed):

phpize
# If $II_SYSTEM is defined
./configure
# Or If not you can provide it using the --with-ingres parameter
./configure --with-ingres=/path/to/ingres
make
sudo make install

• Edit php.ini, add the following line to the bottom of the file:

  extension=ingres.so

• Make sure memory_limit is set to at least 100M
  • This is potentially overkill, half of that is probably enough but better safe than sorry.

Troubleshooting

• If you see messages such as undefined symbol: clock_gettime in Unknown on line 0, you will need to add a line to config.m4

PHP_ADD_LIBRARY_WITH_PATH(rt, $II_LIB_DIR, INGRES_SHARED_LIBADD)

• If you're building against the geospatial branch of Ingres add this

PHP_ADD_LIBRARY_WITH_PATH(geos, $II_LIB_DIR, INGRES_SHARED_LIBADD)
PHP_ADD_LIBRARY_WITH_PATH(geos_c, $II_LIB_DIR, INGRES_SHARED_LIBADD)

• Afterwards run phpize and configure again.

Windows

• Download the latest driver bundle from ESD
  • The Zip archive is a collection of PHP DLLs for Ingres built for various combinations of PHP and Ingres.
• From the Zip archive pick the php_ingres.dll that matches your PHP/Ingres configuration
• Copy the selected php_ingres.dll to the location set for the extension_dir directive
  • Or run the following PHP to determine where your extensions are installed to:

    echo "<?php echo ini_get('extension_dir'); ?>" | php 

• Edit 'php.ini', add the following line to the bottom of the file:

  extension=php_ingres.dll

• Make sure memory_limit is set to at least 100M
  • This is potentially overkill, half of that is probably enough but better safe than sorry.

Configuring Apache and Ingres

• Add an apache user to Ingres, then create a database named drupal with the apache user
  • Note: If your apache installation uses a different user name, please adjust as needed
• Use cbf to (if you do export TERM_INGRES='konsole' before starting you should be able to use the F-keys):
  • enable 64k caches if not already enabled. (hint: it's under DBMS Server->cache menu - hit F7 if your terminal is set up correctly)
  • increase DBMS Server->cursor_limit to 200.
  • increase DBMS Server->system_max_locks to 1500
  • increase Locking System->per_tx_limit to 1500
• After the locking settings have been increased you might have to increase the shared memory max in Linux:
  1. echo 100000000 > /proc/sys/kernel/shmmax (this is temporary and will reset when rebooted)
  2. edit /etc/sysctl.conf and add or change kernel.shmmax = 100000000 (this makes the change permanent and take effect on reboot)
• Edit httpd.conf (/etc/httpd/conf/httpd.conf), add PassEnv II_SYSTEM LD_LIBRARY_PATH anywhere in the file
• Edit the httpd (/etc/init.d/httpd) startup script, add source /path/to/.ingIIsh somewhere at the top after first line
  • On Ubuntu add the source line to /etc/apache2/envvars instead of /etc/init.d/httpd
• .ingIIsh must be readable by the apache user
• Ensure that SELinux is disabled, otherwise it can cause problems
• Restart apache

Installing Drupal

• SVN Checkout (http://code.ingres.com/apps/drupal/trunk) or copy Drupal into Apache DocumentRoot (/var/www/html default on some systems)
• Change owner of drupal folder to apache:

  chown -R apache:apache /var/www/html/drupal

• Navigate in FireFox to http://localhost/drupal
• Now just follow the Drupal installation instructions, if everything was done right you should come across the database set up page and be able to choose Ingres

Known Issues

Below is a list of limitations of the current version of Drupal and also a list of work arounds that are in place to make Drupal core run properly on Ingres. There are three major issues that require new features in Ingres to make Drupal run with as few work arounds as possible.

1. LONG VARCHAR needs to be allowed in GROUP BY, ORDER BY, and WHERE clauses

  This issue is improved by support for 64K VARCHAR in Ingres 10.0.

1. The GREATEST SQL function needs to be implemented
   • This function would take an arbitrary number of numerical arguments, and return the largest of those arguments. This functionality exists in MySQL, PostgreSQL and Oracle

   This is expected in Ingres 10.0

1. Database object names longer than 32 characters. Currently the index names have to be truncated.

   This is expected in Ingres 10.0

Known Issues with modules

We are investigating issues with the views and cck modules. We expect work arounds/fixes to be in place in October 2009. Some of these issues are simply ingres not listed as one of the options in switch statements.

Issues with the Views Module

• There are database specific switch/case statements that need to be patched
• There are SQL functions used specific to PostgreSQL and MySQL, functionality Ingres doesn't have
  • FROM_UNIXTIME (MySQL) or FIELD::ABSTIME (PostgreSQL) takes an integer (Unix Epoch Time) and returns a DATE object
  • MySQL: DATE_FORMAT(DATE_OBJECT, 'FORMAT STRING') or PostgreSQL: TO_CHAR(DATE_OBJECT, 'FORMAT STRING') can return a specific string as per the format, e.g.: DATE_FORMAT(date, 'YYYYMMDD'), Ingres does not have equivalent functionality
  • If you name the result set returned from an SQL function, it cannot be referred to by name in GROUP/ORDER BY and WHERE. E.g.: "SELECT COUNT(*) AS columns ORDER BY columns" will not work
    • There are several work around hacks in Drupal pertaining to this issue

Known modules that work fine

location gmap

Limitations

• Version 2.2.0 or later of the Ingres PHP driver is required
• LONG VARCHAR is not allowed in GROUP BY or WHERE clauses, a case by case work around has been implemented for this
  • This workaround casts LONG VARCHAR to VARCHAR(32000) and if the field contains more than 32,000 characters the results will obviously be corrupted somewhat. Although this most likely does not apply to very many cases.
• The search module does not work due to a few reasons:
  • Installation issues, tries to create a unique constraint over columns declared WITH NULL
    • This has been worked around and is not really an issue anymore
  • Operation issues, uses an SQL function called GREATEST which takes an arbitrary list of numbers and returns the largest
• One of the localization module's index does not create properly because it is on a VARCHAR(16000) column

Work Arounds

• A column named "tid" is used, this is changed to t_id and affects functions db_fetch_array, db_fetch_object, and _db_query.
• If an aggregate function is used, it can't be in the ORDER BY clause by the name it is given, instead use numerical position. This affects function _db_query
• Columns parameters (actions), value (variable), and message and variables (watchdog) are LONG VARCHAR and are used in GROUP BY and WHERE, work around using preg_replace in _db_query
• Some modules use the POW function, named POWER in Ingres. This is rewritten in _db_query.
• When LOBs are used in update/insert queries, it has to be parametrized and this is done in db_query, _db_query.
• Some index names are longer than the max allowed 32 characters, they are hence truncated and assigned a unique name in _db_create_index_sql