Mon Oct 31 12:43:33 CET 2005

Author: steffen

Update of /kolabrepository/server/kolab-horde-fbview/kolab-horde-fbview/fbview/scripts/db
In directory doto:/tmp/cvs-serv18388/kolab-horde-fbview/kolab-horde-fbview/fbview/scripts/db

Added Files:
	README README.ORACLE auth.sql auth_initial_user.sql 
	datatree.sql datatree_mysql.sql log.sql muvfs.sql 
	mysql_create.sql mysql_drop.sql oracle_create.sql 
	oracle_create_alternate.sql pgsql_create.sql pgsql_drop.sql 
	prefs.sql sessionhandler.sql sessionhandler_pgsql.sql 
	sessionhandler_sapdb.sql sybase_create.sql token.sql vfs.sql 
	vfs_oracle.sql vfs_pgsql.sql 
Log Message:
Fbview in separate package

--- NEW FILE: README ---
Horde Database Installation Guide                    horde/scripts/db/README
=--------------------------------------------------------------------------=

$Horde: horde/scripts/db/README,v 1.9 2003/03/07 18:10:44 bjn Exp $

Purpose of this file
~~~~~~~~~~~~~~~~~~~~
This file will hopefully ease the installation and configuration of your
database for use with Horde.  The scope of the document deals primarily
with using your database with Horde and should not be considered a
resource for general database tasks.

Requirements for Horde
~~~~~~~~~~~~~~~~~~~~~~
Horde currently supports 4 databases:

    - MySQL         http://www.mysql.com/
    - PostgreSQL    http://www.postgresql.org/
    - Oracle        http://www.oracle.com/
    - Sybase        http://www.sybase.com/

MySQL and PostgreSQL are regularly used by the development team, and will
work with no problems.  Oracle and Sybase were reported working in the
previous version of Horde/IMP by some of our users, and are assumed to
still work... but that hasn't been verified recently.

Before You Begin
~~~~~~~~~~~~~~~~
You will need to have your database preconfigured and working on your
system before you even get to this stage.  For help with this, please
consult your database specific help files.

Before using any of the scripts listed below, please look over them to
ensure that any system-specific settings (or personal choices) are set
to your desired settings (eg. 'username', etc.).

Please NOTE that the database configuration steps listed below MUST
match what is configured within Horde.

Specifically, if you change the database name, user, or password while
configuring the database (below), you will need to make the same changes
to your Horde configuration files as well.

MySQL
~~~~~
The mysql_create.sql script will create a database called "horde",
and a user called "horde" (with password "horde") that has full
rights to the database.  *Note that with MySQL, PEAR DB emulates
sequences by automatically creating extra tables ending in _seq,
so the MySQL "horde" user must have CREATE privilege on the
"horde" database.

This script needs to be run as the MySQL root user (which is NOT
the same as the UNIX/Linux root user):

$ mysql --user=root --password=<MySQL-root-passwd> < mysql_create.sql

After running this script, it's a good idea to restart your MySQL
server:

$ mysqladmin --user=root --password=<MySQL-root-passwd> reload

To check that this is working, the following command should connect
without errors:

$ mysql --user=horde --password=horde horde

Type "quit" to quit MySQL.

If you need to uninstall the database, you can do so with:

$ mysql --user=root --password=<MySQL-root-passwd> < mysql_drop.sql

This will delete all your user data!

PostgreSQL
~~~~~~~~~~
# su - postgres  (or whatever your database runs as... usually postgres)
$ psql -d template1 -f pgsql_create.sql

This script will create a "horde" database and a "horde" user.  You
should set a password for this user with this command (if you didn't
uncomment the one in pgsql_create.sql):

$ psql -qc "ALTER USER horde WITH PASSWORD 'pass';" template1 postgres

Then create the tables within the "horde" database:

$ psql -d horde -f auth.sql
$ psql -d horde -f prefs.sql
$ psql -d horde -f category.sql

These scripts will also grant the "horde" user the necessary privileges to
each of the tables.

Oracle
~~~~~~
Run 'sqlplus'. Login, then [we need instructions for creating the
database and user here].

That should be it.  Restart Oracle just to be sure, and try it.  Then
create the tables within the "horde" database:

[Also need those instructions here.]

Sybase
~~~~~~
To create the database, from 'isql' run:

    create database horde

Next, you should (recommended) create a login to use with this database.
The 'isql' command for this is:

    sp_addlogin <username> <password>

Next you need to change the ownership of the database to this new user.  The
'isql' command for this is:

    sp_changedbowner <username>

After that, you just need to set up your tables within the database.  To do
that, just run this command from the command line:

    isql -i auth.sql -U<username> -P<password>
    isql -i prefs.sql -U<username> -P<password>

Additional Notes
~~~~~~~~~~~~~~~~
This is by far not a complete HOWTO. This is just a compiled list of what
people have contributed so far. If you have tips, ideas, suggestions or
anything else that you think could help others in setting up their database
for use with Horde, please let us know.  <dev at lists.horde.org>

--- NEW FILE: README.ORACLE ---
FIRST OF ALL:
This is not my first documentation! but my first in english.
Feel free to contact me at torsten at flammiger.org if you have 
any suggestions or problems related to this docs

All i will write about Oracle with Horde is based on Oracle >= 8i
though it should work with 9i too. 
( Cant test it 'cause my test pc only has 256mb of ram! too poor
for 9i ! )

I think (i'm sure) 'Up to date' available distributions
are capable to run Oracle. I have tested it on
Slackware 7.1/8.0 (8.1.6) and have running it on Gentoo 1.2 (8.1.7.4)
without any Problems. Some distributions are certified like SuSE or 
RedHat. On Debian it should also run.

Furthermore i'll assume, that no "normal" user is going to install
or is in need to install an Oracle DB (of course there are some freaks ;)
so knowledge about setting up and maintain an Oracle
DB is indispensable! Maybe this is the reason why no Admin
has ever posted any docs about getting Horde work with Oracle

NOTE: all docs are available from www.oracle.com for free (AFAIK)

Now let's begin:

Case 1) 
I'll assume that you do not have an instance running and therefore
are about to install a fresh instance "horde" .

--	check and if needed change shared memory parameters
--	create at least the dba group
--	create your oracle user, name it as you like
	make sure your ora-user is in the group dba
--	su - <your fresh oracle user>
-- 	set up all the appropriate environment variables for oracle
	take a close look at the install howto provided by oracle
--	mount the CD (if it is your source media)
--	run the script runInstaller - better to have X11 installed ;)
	Attention: If you are about to install Oracle 9i
	do NOT run the installer from directly from your CD!
	like cd /cdrom and then start it by typing ./runInstaller
	you won't be able to unmount the CD later on but you have to
	because 9i is a 3CD-Set
--	follow the instructions given by the installation programm
	at the point where you are asked for an installation method
	choose "user-defined". This ist especially VERY IMPORTENT if you
	are about to install 8i since you have to patch the installation
	BEFORE you create your database! If you not choose "user-defined"
	the program "dbassist" will automatically start at the end of
	the installation process and will hang and/or fail.
--	at the end of your installation you have to configure your listener.ora,
	tnsnames.ora and your sqlnet.ora This is all done by the
	installation programm. 
--	Now: if you use 8.1.7 get the patch glibc-2.1.3-stubs.tar.gz from Oracle. 
	It's at the same page like the db-source. Install as described in README.stub
	(it's in the package so first untar it somewhere else or use 'mc' )
--	Now create your database. Run "dbassist"
	It's now on you to decide how big the initial tablespaces are, how many
	control files you like, of course how it's name will be and what
	option you plan to use like InterMedia. It's Your Decision !
	(BTW: the defaults will work and are very time consuming ;)
	At the end let the dbasist program save the configuration. You can choose
	location and name. Now you can close dbassist.
--	change to the location where you stored the scripts and take a short look
	at least into oraclerun.sh and oraclerun1.sh. If all is OK create the database:
	run ./oraclerun.sh. 
	Sit back take a cup of coffee and hope all will be good ;)

	If you choosed the InterMedia option the creation process will take
	about 2 and a half hour (on my 500MHz-P3 :-). Without Java it will take
	about 1 hour. (this both ist short - 9i takes as min. twice that time !)

	NOTE: choosing InterMedia will give you enhanced possibilities for storing
	and manipulating media files. Look at http://intermedia.as-dataservice.de/
	for an example...

	ANOTHER NOTE: if you installed 8.1.7 and afterwards applied an up to date
	patchset (like 8.1.7.4) you MUST repatch your installation with the 
	glibc-stubs patch or your DB won't run

	PROBLEMS ?: Look at www.orafaq.com for your answer

All went fine? Good, lets go ahead and create a new tablespace called horde

--	The database should run after it was succesfully created. Login as user system
	or sys (like you prefer). The passwords where written to you on STDOUT.
	Please change them! I know databases running for years storing important data
	and still have the default password !!
	(manager for system and change_on_install for sys)

--	sqlplus sys/change_on_install@<your SID>
	if you properly set the ORACLE_SID environment variable you can ommit the
	trailing @<your sid> when you are logged on to the machine which is running the DB.

--	create the tablespace for horde
	sql> CREATE TABLESPACE HORDE
              LOGGING
              DATAFILE '<YOUR PATH HERE>/HORDE.dbf' SIZE <AS YOU LIKE>M;

	This is indeed very simple but it works and nothing has to be in uppercase
	letters. After a few seconds or minutes - depends on how many megabytes
	you gave to that tablespace - a new datafile has been created.

--	We can now create a user with default tablespace HORDE, lets name him horde:

	CREATE USER HORDE PROFILE DEFAULT IDENTIFIED BY <your password here> 
        TABLESPACE HORDE TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON TEMP
	QUOTA UNLIMITED ON HORDE ACCOUNT UNLOCK;
	GRANT CONNECT TO HORDE;

--	commit your work: > commit;
	and log out
--	login as user horde: sqlplus horde/<password>@<your sid> and create the tables
	!<path to horde>/scripts/db/oracle_create.sql and commit your aktion: > commit;
	the tables will be created in the default tablespace for your horde user

OK now you can configure Horde to use your instance but after a reboot Oracle
will not start automaticly. That is what we are going to configure now.

--	logout of oracle, login as user sys or <your oracle user> 
	and shutdown your instance: (hope your oracle user is in the group dba -
	if not he can't shut down your instance)
	> connect internal
	> shutdown immediate
	and logout...

--	test the script "dbstart" in $ORACLE_HOME/bin If the DB does not start
	you have to edit it or create a link from $ORACLE_HOME/dbs/init<sid-name>.ora
	to $ORACLE_HOME/admin/<sid name>/pfile/init<sid-name>.ora
	( dbstart will look for your sid-files in subdir "dbs" but these are stored
	under $ORACLE_HOME/admin/<sid name>/pfile/ - this maybe is a bug or a
	feature in the oracle installer )
	test "dbstart" again - now it should work, i hope

--	edit the file /etc/oratab
	Change the line
	<sid-name>:<your Oracle Home - Full qualified path>:N   to
	<sid-name>:<your Oracle Home - Full qualified path>:Y

--	now change or create a startup script which is executed at boot time. It should contain
	something like this:

	su - <your oracle user> -c "<hour path here>/bin/lsnrctl start"
	su - <your oracle user> -c "<your path here>/bin/dbstart"

	and of course your instance should be stopped a regular way. Place this
	in the appropriate script which is executed at shutdown time

	su - <your oracle user> -c "<hour path here>/bin/lsnrctl stop"
	su - <your oracle user> -c "<your path here>/bin/dbshut"

--	test it

End of Case 1)

Case 2)

Problem: You have an allready running oracle database and would like
to add a new instance. Solution is quiet easy.
For now, the new database' name will be --> horde

Before i start i'll make some assumptions:
(PLEASE CHANGE THE PATHNAMES AS NECESSARY )

1.	$ORACLE_HOME=/home/oracle
2.	$ORACLE_BASE = $ORACLE_HOME
3.	Your existing Database is installed under $ORACLE_HOME/oradata/<your instance name>
4.	Your existing Trace/Dump/pfile/bdump... - directories are installed under
	 $ORACLE_HOME/admin/<your instance name>/

	Therefore your new horde database files will go into another directory:
	$ORACLE_HOME/oradata/horde/

	The Trace/Dump/pfile/bdump... - dirs go to:
	$ORACLE_HOME/admin/horde/

	The inithorde.ora file in $ORACLE_HOME/dbs should be a link to:
	$ORACLE_HOME/admin/horde/pfile/init.ora

Lets begin:

--	create the directories for your new db (as your ORACLE user):
	#> mkdir -p $ORACLE_HOME/oradata/horde/archive
	#> mkdir -p $ORACLE_HOME/admin/horde/adhoc \
		    $ORACLE_HOME/admin/horde/bdump \
		    $ORACLE_HOME/admin/horde/cdump \
		    $ORACLE_HOME/admin/horde/create \
		    $ORACLE_HOME/admin/horde/exp \
		    $ORACLE_HOME/admin/horde/pfile \
		    $ORACLE_HOME/admin/horde/udump

--	copy your existing init.ora file from $ORACLE_HOME/admin/<existing instance>/pfile/
	to $ORACLE_HOME/admin/horde/pfile/ and make the appropriate changes in it
	CHANGE AT LEAST THE PATHNAMES !!!

--	make the link from $ORACLE_HOME/dbs/inithorde.ora to
	$ORACLE_HOME/admin/horde/pfile/init.ora

	#> ln -s $ORACLE_HOME/admin/horde/pfile/init.ora $ORACLE_HOME/dbs/inithorde.ora

--	set ORACLE_SID to horde
	#> ORACLE_SID=horde

--	login and startup the instance
	#> sqlplus /nolog
	sql> startup pfile=/home/oracle/admin/horde/pfile/init.ora nomount

--	create the database from create_horde_db.sql - you should of course have saved it somwhere ;)
	sql> !/path/to/create_horde_db.sql

	as template i took the script from
	http://www.adp-gmbh.ch/ora/admin/creatingdbmanually.html#samplecreatedatabase
	and made some minor changes.

--	create the data dictionary, etc.
	sql> !/home/oracle/rdbms/admin/catalog.sql
	sql> !/home/oracle/rdbms/admin/catproc.sql
	sql> !/home/oracle/rdbms/admin/caths.sql
	sql> commit;
	sql> exit

--	now add a new line to /etc/oratab

	old ( only 1 instance ):
	<your installed instance name>:/home/oracle:Y

	new ( with the new one ):
	<your installed instance name>:/home/oracle:Y
	horde:/home/oracle:Y

--	add the new instance to your tnsnames.ora
	use the java gui net8assist or copy and paste the statements by yourself and
	make the changes as needed

--	after all, the scripts dbstart und dbshut will then automaticly start and shutdown all
	instances at once

Creating the Tablespace and required user is the same as  described above

I hope that there are not so many spelling mistakes ( my native language is german ) 
and that this will be helpfull for someone

End of Case 2)

--- NEW FILE: auth.sql ---
-- $Horde: horde/scripts/db/auth.sql,v 1.10 2003/07/14 16:33:07 mdjukic Exp $

CREATE TABLE horde_users (
    user_uid   VARCHAR(255) NOT NULL,
    user_pass  VARCHAR(32) NOT NULL,

    PRIMARY KEY (user_uid)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_users TO horde;

--- NEW FILE: auth_initial_user.sql ---
-- $Horde: horde/scripts/db/auth_initial_user.sql,v 1.1 2003/03/11 19:20:18 chuck Exp $
--
-- This script will create an initial user in a horde_users table. The
-- password being used is 'admin', which you should change
-- IMMEDIATELY.

INSERT INTO horde_users (user_uid, user_pass) VALUES ('admin', '21232f297a57a5a743894a0e4a801fc3');

--- NEW FILE: datatree.sql ---
-- $Horde: horde/scripts/db/datatree.sql,v 1.1 2004/03/18 20:25:10 jan Exp $

CREATE TABLE horde_datatree (
    datatree_id INT NOT NULL,
    group_uid VARCHAR(255) NOT NULL,
    user_uid VARCHAR(255) NOT NULL,
    datatree_name VARCHAR(255) NOT NULL,
    datatree_parents VARCHAR(255) NOT NULL,
    datatree_order INT,
-- There is no portable way to do this apparently. If your db doesn't allow varchars
-- greater than 255 characters, then maybe it allows TEXT columns, so try the second
-- line.
    datatree_data VARCHAR(2048),
--  datatree_data TEXT,
    datatree_serialized SMALLINT DEFAULT 0 NOT NULL,
    datatree_updated TIMESTAMP,

    PRIMARY KEY (datatree_id)
);

CREATE INDEX datatree_datatree_name_idx ON horde_datatree (datatree_name);
CREATE INDEX datatree_group_idx ON horde_datatree (group_uid);
CREATE INDEX datatree_user_idx ON horde_datatree (user_uid);
CREATE INDEX datatree_order_idx ON horde_datatree (datatree_order);
CREATE INDEX datatree_serialized_idx ON horde_datatree (datatree_serialized);

CREATE TABLE horde_datatree_attributes (
    datatree_id INT NOT NULL,
    attribute_name VARCHAR(255) NOT NULL,
    attribute_key VARCHAR(255),
    attribute_value TEXT
);

CREATE INDEX datatree_attribute_idx ON horde_datatree_attributes (datatree_id);
CREATE INDEX datatree_attribute_name_idx ON horde_datatree_attributes (attribute_name);
CREATE INDEX datatree_attribute_key_idx ON horde_datatree_attributes (attribute_key);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_datatree TO horde;
GRANT SELECT, INSERT, UPDATE, DELETE ON horde_datatree_attributes TO horde;

--- NEW FILE: datatree_mysql.sql ---
-- $Horde: horde/scripts/db/datatree_mysql.sql,v 1.3 2004/05/22 12:02:23 mdjukic Exp $

CREATE TABLE horde_datatree (
       datatree_id INT NOT NULL,
       group_uid VARCHAR(255) NOT NULL,
       user_uid VARCHAR(255) NOT NULL,
       datatree_name VARCHAR(255) NOT NULL,
       datatree_parents VARCHAR(255) NOT NULL,
       datatree_order INT,
       datatree_data TEXT,
       datatree_serialized SMALLINT DEFAULT 0 NOT NULL,
       datatree_updated TIMESTAMP,
       PRIMARY KEY (datatree_id)
);

CREATE INDEX datatree_datatree_name_idx ON horde_datatree (datatree_name);
CREATE INDEX datatree_group_idx ON horde_datatree (group_uid);
CREATE INDEX datatree_user_idx ON horde_datatree (user_uid);
CREATE INDEX datatree_serialized_idx ON horde_datatree (datatree_serialized);

CREATE TABLE horde_datatree_attributes (
    datatree_id INT NOT NULL,
    attribute_name VARCHAR(255) NOT NULL,
    attribute_key VARCHAR(255) DEFAULT '' NOT NULL,
    attribute_value TEXT
);

CREATE INDEX datatree_attribute_idx ON horde_datatree_attributes (datatree_id);
CREATE INDEX datatree_attribute_name_idx ON horde_datatree_attributes (attribute_name);
CREATE INDEX datatree_attribute_key_idx ON horde_datatree_attributes (attribute_key);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_datatree TO horde at localhost;
GRANT SELECT, INSERT, UPDATE, DELETE ON horde_datatree_attributes TO horde at localhost;

--- NEW FILE: log.sql ---
-- $Horde: horde/scripts/db/log.sql,v 1.3 2003/07/28 13:40:39 chuck Exp $

CREATE TABLE horde_log (
    id          INT NOT NULL,
    logtime     TIMESTAMP NOT NULL,
    ident       CHAR(16) NOT NULL,
    priority    INT NOT NULL,
    -- For DBs that don't support the TEXT field type:
    -- message  VARCHAR(2048),
    message     TEXT,
    PRIMARY KEY (id)
);

GRANT INSERT ON horde_log TO horde;

--- NEW FILE: muvfs.sql ---
-- $Horde: horde/scripts/db/muvfs.sql,v 1.2 2002/09/02 10:39:06 jan Exp $

CREATE TABLE horde_muvfs (
    vfs_id        BIGINT NOT NULL,
    vfs_type      SMALLINT NOT NULL,
    vfs_path      VARCHAR(255) NOT NULL,
    vfs_name      VARCHAR(255) NOT NULL,
    vfs_modified  BIGINT NOT NULL,
    vfs_owner     VARCHAR(255) NOT NULL,
    vfs_perms     SMALLINT NOT NULL,
    vfs_data      LONGBLOB,
-- Or, on some DBMS systems:
--  vfs_data      IMAGE,
    PRIMARY KEY   (vfs_id)
);

CREATE INDEX vfs_path_idx ON horde_muvfs (vfs_path);
CREATE INDEX vfs_name_idx ON horde_muvfs (vfs_name);

--- NEW FILE: mysql_create.sql ---
-- $Horde: horde/scripts/db/mysql_create.sql,v 1.18 2004/03/18 20:25:10 jan Exp $
--
-- If you are installing Horde for the first time, you can simply
-- direct this file to mysql as STDIN:
--
-- $ mysql --user=root --password=<MySQL-root-password> < mysql_create.sql
--
-- If you are upgrading from a previous version, you will need to comment
-- out the the user creation steps below, as well as the schemas for any
-- tables that already exist.
--
-- If you choose to grant permissions manually, note that with MySQL, PEAR DB
-- emulates sequences by automatically creating extra tables ending in _seq,
-- so the MySQL "horde" user must have CREATE privilege on the "horde"
-- database.
--
-- If you are upgrading from Horde 1.x, the Horde tables you have from
-- that version are no longer used; you may wish to either delete those
-- tables or simply recreate the database anew.

USE mysql;

REPLACE INTO user (host, user, password)
    VALUES (
        'localhost',
        'horde',
-- IMPORTANT: Change this password!
        PASSWORD('horde')
    );

REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv,
                 delete_priv, create_priv, drop_priv)
    VALUES (
        'localhost',
        'horde',
        'horde',
        'Y', 'Y', 'Y', 'Y',
        'Y', 'Y'
    );

FLUSH PRIVILEGES;

-- MySQL 3.23.x appears to have "CREATE DATABASE IF NOT EXISTS" and
-- "CREATE TABLE IF NOT EXISTS" which would be a nice way to handle
-- reinstalls gracefully (someday).  For now, use mysql_drop.sql first
-- to avoid CREATE errors.

CREATE DATABASE horde;

USE horde;

CREATE TABLE horde_users (
    user_uid       VARCHAR(255) NOT NULL,
    user_pass      VARCHAR(32) NOT NULL,
    PRIMARY KEY (user_uid)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_users TO horde at localhost;

CREATE TABLE horde_prefs (
    pref_uid        VARCHAR(200) NOT NULL,
    pref_scope      VARCHAR(16) NOT NULL DEFAULT '',
    pref_name       VARCHAR(32) NOT NULL,
    pref_value      LONGTEXT NULL,

    PRIMARY KEY (pref_uid, pref_scope, pref_name)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_prefs TO horde at localhost;

CREATE TABLE horde_datatree (
       datatree_id INT NOT NULL,
       group_uid VARCHAR(255) NOT NULL,
       user_uid VARCHAR(255) NOT NULL,
       datatree_name VARCHAR(255) NOT NULL,
       datatree_parents VARCHAR(255) NOT NULL,
       datatree_order INT,
       datatree_data TEXT,
       datatree_serialized SMALLINT DEFAULT 0 NOT NULL,
       datatree_updated TIMESTAMP,
       PRIMARY KEY (datatree_id)
);

CREATE INDEX datatree_datatree_name_idx ON horde_datatree (datatree_name);
CREATE INDEX datatree_group_idx ON horde_datatree (group_uid);
CREATE INDEX datatree_user_idx ON horde_datatree (user_uid);
CREATE INDEX datatree_serialized_idx ON horde_datatree (datatree_serialized);

CREATE TABLE horde_datatree_attributes (
    datatree_id INT NOT NULL,
    attribute_name VARCHAR(255) NOT NULL,
    attribute_key VARCHAR(255) DEFAULT '' NOT NULL,
    attribute_value TEXT
);

CREATE INDEX datatree_attribute_idx ON horde_datatree_attributes (datatree_id);
CREATE INDEX datatree_attribute_name_idx ON horde_datatree_attributes (attribute_name);
CREATE INDEX datatree_attribute_key_idx ON horde_datatree_attributes (attribute_key);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_datatree TO horde at localhost;
GRANT SELECT, INSERT, UPDATE, DELETE ON horde_datatree_attributes TO horde at localhost;

FLUSH PRIVILEGES;

-- Done!

--- NEW FILE: mysql_drop.sql ---
# $Horde: horde/scripts/db/mysql_drop.sql,v 1.3 2002/09/25 22:56:28 jan Exp $
#
# You can simply direct this file to mysql as STDIN:
#
# $ mysql --user=root --password=<MySQL-root-password> < mysql_drop.sql

USE mysql;

DELETE FROM user WHERE user LIKE 'horde%';

DELETE FROM db WHERE user LIKE 'horde%';

DROP DATABASE IF EXISTS horde;

FLUSH PRIVILEGES;

# Done!

--- NEW FILE: oracle_create.sql ---
set doc off

/**

Oracle Table Creation Scripts.

$Horde: horde/scripts/db/oracle_create.sql,v 1.4 2004/03/18 20:25:11 jan Exp $

@author Miguel Ward <mward at aluar.com.ar>

This sql creates the Horde SQL tables 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']['phptype'] = 'oci8';
$conf['prefs']['params']['hostspec'] = 'database_name';
$conf['prefs']['params']['username'] = 'horde';
$conf['prefs']['params']['password'] = '*******';
$conf['prefs']['params']['database'] = '';
$conf['prefs']['params']['table'] = 'horde_prefs';

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

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 (usually nobody or
www-data) 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

YOU MUST CUSTOMIZE THESE VALUES TO BE APPROPRIATE TO YOUR INSTALLATION

You can include these variables in the user's local .profile or in
/etc/profile, etc. 
Obviously you must have Oracle installed on this machine AND you must
have compiled Apache/PHP with Oracle (you included --with-oci8 in the
build arguments for PHP, or uncommented the oci8 extension in
php.ini).

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 we include none it will use
the default tablespace values for the user creating these tables. Same
with the indexes (in theory these should use a different tablespace).

There is 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 circumvent this
limitation with a fair amount of work. The PEAR distribution of April
2002 does not include this workaround thus the limitation.

*/

rem conn horde/&horde_password at database

/**

This is the Horde users table, needed only if you are using SQL
authentication. Note that passowrds in this table need to be
md5-encoded.

*/

CREATE TABLE horde_users (
    user_uid    VARCHAR2(255) NOT NULL,
    user_pass   VARCHAR2(32) NOT NULL,

    PRIMARY KEY (user_uid)
);

/**

This is the Horde preferences table, holding all of the user-specific
options for every Horde user.

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).

We 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 you have to use
VARCHAR2(2000) which is the 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,
--  See above notes on CLOBs.
    pref_value      CLOB,

    PRIMARY KEY (pref_uid, pref_scope, pref_name)
);

/*

The DataTree tables are used for holding hierarchical data such as
Groups, Permissions, and data for some Horde applications.

*/

CREATE TABLE horde_datatree (
    datatree_id NUMBER(16) NOT NULL,
    group_uid VARCHAR2(255) NOT NULL,
    user_uid VARCHAR2(255) NOT NULL,
    datatree_name VARCHAR2(255) NOT NULL,
    datatree_parents VARCHAR2(255) NOT NULL,
    datatree_order NUMBER(16),
--  See above notes on CLOBs.
    datatree_data CLOB,
    datatree_serialized NUMBER(8) DEFAULT 0 NOT NULL,
    datatree_updated DATE,

    PRIMARY KEY (datatree_id)
);

CREATE INDEX datatree_datatree_name_idx ON horde_datatree (datatree_name);
CREATE INDEX datatree_group_idx ON horde_datatree (group_uid);
CREATE INDEX datatree_user_idx ON horde_datatree (user_uid);
CREATE INDEX datatree_order_idx ON horde_datatree (datatree_order);
CREATE INDEX datatree_serialized_idx ON horde_datatree (datatree_serialized);

/**

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

Required for local SQL-based address books.

*/

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

--- NEW FILE: oracle_create_alternate.sql ---
CREATE DATABASE HORDE
    CONTROLFILE REUSE
    LOGFILE  '/home/oracle/oradata/horde/redo01.log'   SIZE 1M,
                  '/home/oracle/oradata/horde/redo02.log'   SIZE 1M,
                  '/home/oracle/oradata/horde/redo03.log'   SIZE 1M
    DATAFILE '/home/oracle/oradata/horde/system01.dbf' SIZE 10M
      AUTOEXTEND ON
      NEXT 10M MAXSIZE 512M
    ARCHIVELOG
    CHARACTER SET WE8ISO8859P1
    NATIONAL CHARACTER SET WE8ISO8859P1;

-- Create a (temporary) rollback segment in the system talbespace
CREATE ROLLBACK SEGMENT rb_temp STORAGE (INITIAL 100 k NEXT 250 k);

-- Alter temporary rollback segment online before proceding
ALTER ROLLBACK SEGMENT rb_temp ONLINE;

-- Create additional tablespaces ...
-- RBS: For rollback segments
-- USERs: Create user sets this as the default tablespace
-- TEMP: Create user sets this as the temporary tablespace
CREATE TABLESPACE rbs
    DATAFILE '/home/oracle/oradata/horde/rbs01.dbf' SIZE 5M AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE users
    DATAFILE '/home/oracle/oradata/horde/users01.dbf' SIZE 3M AUTOEXTEND ON
      NEXT 5M MAXSIZE 15M;
CREATE TABLESPACE temp
    DATAFILE '/home/oracle/oradata/horde/temp01.dbf' SIZE 2M AUTOEXTEND ON
      NEXT 5M MAXSIZE 150M;

-- Create rollback segments.
CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb2 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb3 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;
CREATE ROLLBACK SEGMENT rb4 STORAGE(INITIAL 50K NEXT 250K)
  tablespace rbs;

-- Bring new rollback segments online and drop the temporary system one
ALTER ROLLBACK SEGMENT rb1 ONLINE;
ALTER ROLLBACK SEGMENT rb2 ONLINE;
ALTER ROLLBACK SEGMENT rb3 ONLINE;
ALTER ROLLBACK SEGMENT rb4 ONLINE;

ALTER ROLLBACK SEGMENT rb_temp OFFLINE;
DROP ROLLBACK SEGMENT rb_temp ;

--- NEW FILE: pgsql_create.sql ---
-- $Horde: horde/scripts/db/pgsql_create.sql,v 1.4 2002/06/13 13:21:25 bjn Exp $
--
-- Uncomment the ALTER line below, and change the password.  Then run as:
--
-- $ psql -d template1 -f pgsql_create.sql

CREATE DATABASE horde;

CREATE USER horde;

-- ALTER USER horde WITH PASSWORD 'pass';

--- NEW FILE: pgsql_drop.sql ---
-- $Horde: horde/scripts/db/pgsql_drop.sql,v 1.2 2001/12/31 03:10:02 jon Exp $
--
-- Run using:
--
-- $ psql -f pgsql_drop.sql

DROP USER horde;

DROP DATABASE horde;

--- NEW FILE: prefs.sql ---
-- $Horde: horde/scripts/db/prefs.sql,v 1.8 2003/07/14 16:33:08 mdjukic Exp $

CREATE TABLE horde_prefs (
    pref_uid        VARCHAR(255) NOT NULL,
    pref_scope      VARCHAR(16) NOT NULL DEFAULT '',
    pref_name       VARCHAR(32) NOT NULL,
    pref_value      TEXT,

    PRIMARY KEY (pref_uid, pref_scope, pref_name)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_prefs TO horde;

--- NEW FILE: sessionhandler.sql ---
-- $Horde: horde/scripts/db/sessionhandler.sql,v 1.4 2003/07/14 16:33:08 mdjukic Exp $

CREATE TABLE horde_sessionhandler (
    session_id             VARCHAR(32) NOT NULL,
    session_lastmodified   INT NOT NULL,
    session_data           LONGBLOB,
-- Or, on some DBMS systems:
--  session_data           IMAGE,

    PRIMARY KEY (session_id)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_sessionhandler TO horde;

--- NEW FILE: sessionhandler_pgsql.sql ---
-- $Horde: horde/scripts/db/sessionhandler_pgsql.sql,v 1.1 2003/12/16 04:24:49 chuck Exp $

CREATE TABLE horde_sessionhandler (
    session_id             VARCHAR(32) NOT NULL,
    session_lastmodified   INT NOT NULL,
    session_data           TEXT,
    PRIMARY KEY (session_id)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_sessionhandler TO horde;
--- NEW FILE: sessionhandler_sapdb.sql ---
-- $Horde: horde/scripts/db/sessionhandler_sapdb.sql,v 1.1 2002/09/08 22:59:15 mikec Exp $

CREATE TABLE horde_sessionhandler (
    session_id             VARCHAR(32) NOT NULL,
    session_lastmodified   INT NOT NULL,
    session_data           LONG BYTE,
    PRIMARY KEY (session_id)
)

--- NEW FILE: sybase_create.sql ---
-- horde tables definitions : sql script

-- 01/22/2003 - F. Helly <francois.helly at wanadoo.fr>

-- command line syntax :  isql -ihorde_sybase.sql

-- warning : use nvarchar only if you need unicode encoding for some strings

use horde
go

DROP TABLE horde_datatree
go

DROP TABLE horde_prefs
go

DROP TABLE horde_users
go

DROP TABLE horde_sessionhandler
go

-- DROP TABLE horde_datatree_seq
-- go

-- DROP TABLE horde_tokens
-- go

-- DROP TABLE horde_vfs
-- go

-- DROP TABLE horde_muvfs
-- go

CREATE TABLE horde_users (
  user_uid varchar(256) NOT NULL,
  user_pass varchar(32) NOT NULL,
  PRIMARY KEY  (user_uid)
) 
go

CREATE TABLE horde_datatree (
  datatree_id numeric(11,0) IDENTITY NOT NULL,
  group_uid varchar(256) NOT NULL,
  user_uid varchar(256) NOT NULL,
  datatree_name varchar(256) NOT NULL,
  datatree_parents varchar(256) NULL,
  datatree_data text NULL,
  datatree_serialized smallint DEFAULT 0 NOT NULL,
  datatree_updated timestamp,
  PRIMARY KEY  (datatree_id),
  FOREIGN KEY (user_uid)
    REFERENCES horde_users(user_uid)
 ) 
go

CREATE TABLE horde_prefs (
  pref_uid varchar(256) NOT NULL,
  pref_scope varchar(16) NOT NULL,
  pref_name varchar(32) NOT NULL,
  pref_value text NULL,
  PRIMARY KEY  (pref_uid,pref_scope,pref_name)
)
go 

CREATE TABLE horde_sessionhandler (
  session_id varchar(32) NOT NULL,
  session_lastmodified numeric(11,0) NOT NULL,
  session_data image NULL,
  PRIMARY KEY  (session_id)
) 
go

-- CREATE TABLE horde_datatree_seq (
--   id numeric(10,0) IDENTITY NOT NULL,
--   PRIMARY KEY  (id)
-- ) 
-- go

-- CREATE TABLE horde_tokens (
--   token_address varchar(8) NOT NULL,
--   token_id varchar(32) NOT NULL,
--   token_timestamp numeric(20,0) NOT NULL,
--   PRIMARY KEY  (token_address,token_id)
-- ) 
-- go

-- CREATE TABLE horde_vfs (
--   vfs_id numeric(20,0) NOT NULL,
--   vfs_type numeric(8,0) NOT NULL,
--   vfs_path varchar(256) NOT NULL,
--   vfs_name nvarchar(256) NOT NULL,
--   vfs_modified numeric(20,0) NOT NULL,
--   vfs_owner varchar(256) NOT NULL,
--   vfs_data image NULL,
--   PRIMARY KEY  (vfs_id)
-- ) 
-- go

-- CREATE TABLE horde_muvfs (
--   vfs_id  numeric(20,0) NOT NULL,
--   vfs_type      numeric(8,0) NOT NULL,
--   vfs_path      varchar(256) NOT NULL,
--   vfs_name      varchar(256) NOT NULL,
--   vfs_modified  numeric(8,0) NOT NULL,
--   vfs_owner     varchar(256) NOT NULL,
--   vfs_perms     smallint NOT NULL,
--   vfs_data      image NULL,
--   PRIMARY KEY   (vfs_id)
--   )
-- go

CREATE INDEX datatree_datatree_name_idx ON horde_datatree (datatree_name)
go

CREATE INDEX datatree_group_idx ON horde_datatree (group_uid)
go

CREATE INDEX datatree_user_idx ON horde_datatree (user_uid)
go

CREATE INDEX datatree_serialized_idx ON horde_datatree (datatree_serialized)
go

-- CREATE INDEX vfs_path_idx ON horde_vfs (vfs_path)
-- go

-- CREATE INDEX vfs_name_idx ON horde_vfs (vfs_name)
-- go

-- CREATE INDEX vfs_path_idx ON horde_muvfs (vfs_path)
-- go

-- CREATE INDEX vfs_name_idx ON horde_muvfs (vfs_name)
-- go

grant select, insert, delete, update on editor to horde
go
grant select, insert, delete, update on host to horde
go
grant select, insert, delete, update on dbase to horde
go
grant select, insert, delete, update on site to horde
go
grant select, insert, delete, update on connection to horde
go
grant select, insert, delete, update on horde_datatree to horde
go
grant select, insert, delete, update on horde_prefs to horde
go
grant select, insert, delete, update on horde_sessionhandler to horde
go

-- grant select, insert, delete, update on horde_datatree_seq to horde
-- go
-- grant select, insert, delete, update on horde_tokens to horde
-- go
-- grant select, insert, delete, update on horde_vfs to horde
-- go
-- grant select, insert, delete, update on horde_muvfs to horde
-- go

-- add you admin_user_uid and admin_user_pass

-- insert into horde_users values ('your_admin_user_uid', 'your_admin_user_pass_md5_encrypted')
-- go

--- NEW FILE: token.sql ---
-- $Horde: horde/scripts/db/token.sql,v 1.3 2003/07/14 16:33:08 mdjukic Exp $

CREATE TABLE horde_tokens (
    token_address    VARCHAR(8) NOT NULL,
    token_id         VARCHAR(32) NOT NULL,
    token_timestamp  BIGINT NOT NULL,

    PRIMARY KEY (token_address, token_id)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_tokens TO horde;

--- NEW FILE: vfs.sql ---
-- $Horde: horde/scripts/db/vfs.sql,v 1.7 2003/07/14 16:41:43 marcus Exp $

CREATE TABLE horde_vfs (
    vfs_id        BIGINT NOT NULL,
    vfs_type      SMALLINT NOT NULL,
    vfs_path      VARCHAR(255) NOT NULL,
    vfs_name      VARCHAR(255) NOT NULL,
    vfs_modified  BIGINT NOT NULL,
    vfs_owner     VARCHAR(255) NOT NULL,
    vfs_data      LONGBLOB,
-- Or, on some DBMS systems:
--  vfs_data      IMAGE,
-- Or, on PostgreSQL systems (bytea is not currently supported; we use bin2hex):
--  vfs_data      TEXT,
    PRIMARY KEY   (vfs_id)
);

CREATE INDEX vfs_path_idx ON horde_vfs (vfs_path);
CREATE INDEX vfs_name_idx ON horde_vfs (vfs_name);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_vfs TO horde;

--- NEW FILE: vfs_oracle.sql ---
-- $Horde: horde/scripts/db/vfs_oracle.sql,v 1.1 2002/10/02 14:36:08 chuck Exp $

CREATE TABLE horde_vfs (
    vfs_id        NUMBER(16) NOT NULL,
    vfs_type      NUMBER(8) NOT NULL,
    vfs_path      VARCHAR2(255),
    vfs_name      VARCHAR2(255) NOT NULL,
    vfs_modified  NUMBER(16) NOT NULL,
    vfs_owner     VARCHAR2(255) NOT NULL,
    vfs_data      BLOB,

    PRIMARY KEY   (vfs_id)
);

CREATE INDEX vfs_path_idx ON horde_vfs (vfs_path);
CREATE INDEX vfs_name_idx ON horde_vfs (vfs_name);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_vfs TO horde;

--- NEW FILE: vfs_pgsql.sql ---
-- $Horde: horde/scripts/db/vfs_pgsql.sql,v 1.2 2003/12/16 04:25:53 chuck Exp $

CREATE TABLE horde_vfs (
    vfs_id        BIGINT NOT NULL,
    vfs_type      SMALLINT NOT NULL,
    vfs_path      VARCHAR(255) NOT NULL,
    vfs_name      VARCHAR(255) NOT NULL,
    vfs_modified  BIGINT NOT NULL,
    vfs_owner     VARCHAR(255) NOT NULL,
    vfs_data      TEXT,

    PRIMARY KEY   (vfs_id)
);

CREATE INDEX vfs_path_idx ON horde_vfs (vfs_path);
CREATE INDEX vfs_name_idx ON horde_vfs (vfs_name);

GRANT SELECT, INSERT, UPDATE, DELETE ON horde_vfs TO horde;