set doc off

/*

Version 1.0 -  Miguel Ward - mward@aluar.com.ar - June 20th, 2002

This sql creates the tables required by horde 2.x/imp 3.x/turba 1.x in an
Oracle 8.x database. Should work with Oracle 9.x (and Oracle7 using varchar2).

Once the tables are created you have to complete following steps:

1) Edit /usr/local/horde/config/horde.php and modify/include:

// Preference System Settings

// What preferences driver should we use? Valid values are 'none'
// (meaning use system defaults and don't save any user preferences),
// 'session' (preferences only persist during the login), 'ldap',
// and 'sql'.

$conf['prefs']['driver'] = 'sql';

// Any parameters that the preferences driver needs. This includes
// database or ldap server, username/password to connect with, etc.

$conf['prefs']['params'] = array();

$conf['prefs']['params']['phptype'] = 'oci8';
$conf['prefs']['params']['hostspec'] = 'database_name';
$conf['prefs']['params']['username'] = 'horde';
$conf['prefs']['params']['password'] = 'horde_password';
$conf['prefs']['params']['database'] = '';
$conf['prefs']['params']['table'] = 'horde_prefs';

Where 'database_name' is the database name as defined in tnsnames.ora to
which you wish to connect.

2) Make similar changes in the configuration file belonging to turba (IF
you wish to save 'My Addressbook' in Oracle):

vi /usr/local/horde/turba/config/sources.php

(see above).

3) Make sure that the user that starts up Apache has the following
environment variables defined:

ORACLE_HOME=/home/oracle/OraHome1                      ; export ORACLE_HOME
ORA_NLS=/home/oracle/OraHome1/ocommon/nls/admin/data   ; export ORA_NLS
ORA_NLS33=/home/oracle/OraHome1/ocommon/nls/admin/data ; export ORA_NLS33
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH      ; export LD_LIBRARY_PATH

These values are for my installation. CORRECT FOR YOURS.

You can include these variables in the user's local .profile or in
/etc/profile, etc.

Obviously you have Oracle installed on this machine AND you compiled
Apache/php with oracle (you included --with-oci8 in php).

4) Make sure you have latest PEAR instalation inside your php library.
Specifically the file /usr/local/lib/php/DB.php and the directory
associated with it must be dated after April 2002 (php 4.2.1 is ok).

IF you have an older version of php OR you overwrote the php installation
with the PEAR version 4.1.0 found at the imp website everything will seem
to work ok but the 'Options' you save in imp will not appear next time you
connect.

5) No grants are necessary since we connect as the owner of the tables.
If you wish you can adapt the creation of tables to include tablespace and
storage information. Since I include none it will use the default tablespace
values for the user creating these tables. Same with the indexes (in theory
should use another tablespace...).

Certainly no need to shut down and start up the database!

6) It is important to note that no column can have more than 4000 bytes
(whilst in MySQL there is no such limit), this could cause problems when
saving long 'signatures' or many identities (which are all stored in only
one record). You will see an 'ORA-01704: string literal too long' in
/tmp/horde.log and a 'DB Error: unknown error' at line 297 on the screen.

This is an Oracle limitation. Php/Pear could in theory circunvent this
limitation with a fair amount of work. The Pear distribution of April 2002
does not include this workaround thus the limitation.

Horde/imp tables based on /usr/local/horde/scripts/db/mysql_create.sql


*/

rem conn horde/&horde_password@database

CREATE TABLE horde_users (
    user_uid       varchar2(255) not null,
    user_pass      varchar2(32) not null,
    primary key (user_uid)
);

/*

This is the main table used by imp, horde and turba to keep all the user's
preferences as defined implicitly or explicitly under 'Options'.

pref_uid   is the username (appended with @realm if specified in servers.php)
pref_scope is either imp, horde or turba
pref_name  is the name of the variable to save
pref_value is the value saved (can be very long)

In MySQL 'pref_value' is defined as a TEXT column which is equivalent to a
clob in Oracle. Unfortunately one still gets an 'ORA-01704: string literal
too long' and Oracle's solution is to split message in pieces (which
imp/horde/pear/php do not do at the present time).  

I will, just the same, use a clob column so that longer columns can be
supported when Oracle fixes the limitation or php/pear include a
workaround.

If still using Oracle 7 this should work but have to use varchar2(2000) which
is limit imposed by said version.

*/

CREATE TABLE horde_prefs (
    pref_uid        char(255) not null,
    pref_scope      char(16)  not null,
    pref_name       char(32) not null,
--  In mysql it's declared as TEXT. I could choose between a 
--   varchar2(<= 4000) or a clob (<= 4 giga). See above
--  pref_value      varchar2(4000),
    pref_value      clob,
    primary key (pref_uid, pref_scope, pref_name)
);

/*

Unable to discover who uses the following two tables...

*/

CREATE TABLE horde_categories (
       category_id number(16) not null,
       group_uid VARCHAR2(255) not null,
       user_uid VARCHAR2(255),
       category_name VARCHAR2(255) not null,
--     category_data varchar2(4000) null,
       category_data clob,
       category_serialized number(8) DEFAULT 0 not null,
       category_updated DATE,
       PRIMARY KEY (category_id)
);

CREATE INDEX category_category_name_idx ON horde_categories (category_name);
CREATE INDEX category_group_idx ON horde_categories (group_uid);
CREATE INDEX category_user_idx ON horde_categories (user_uid);
CREATE INDEX category_serialized_idx ON horde_categories (category_serialized);

CREATE TABLE horde_categories_categories (
       category_id_parent number(16) not null,
       category_id_child number(16) not null,
       PRIMARY KEY (category_id_parent, category_id_child)
);

/*

Turba table as defined in /usr/local/horde/turba/scripts/drivers/turba.sql

Could be required for local address book.

*/

CREATE TABLE turba_objects (
        object_id varchar2(32) NOT NULL,
        owner_id varchar2(255) NOT NULL,
        object_name varchar2(255),
        object_alias varchar2(32),
        object_email varchar2(255),
        object_homeAddress varchar2(255),
        object_workAddress varchar2(255),
        object_homePhone varchar2(25),
        object_workPhone varchar2(25),
        object_cellPhone varchar2(25),
        object_fax varchar2(25),
        object_title varchar2(32),
        object_company varchar2(32),
        object_notes varchar2(4000),
    PRIMARY KEY(object_id)
);

exit
