# --
# README.database - database description of OTRS
# Copyright (C) 2001-2011 OTRS AG, http://otrs.org/
# --
# $Id: README.database,v 1.21.2.1 2011/04/20 07:06:11 ub Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

Where can I find the database description files?
================================================

XML:
====
$HOME_OTRS/scripts/database/otrs-schema.xml (table definitions)
$HOME_OTRS/scripts/database/otrs-initial_insert.xml (initial inserts)

These XML description files for torque which generate the SQL for your
target database e. g. MySQL, PostgreSQL, DB2, Oracle, ...)

You will find all generated SQL files under "$HOME_OTRS/scripts/database/*.sql"

MySQL files:
----------------
$HOME_OTRS/scripts/database/otrs-schema.mysql.sql
$HOME_OTRS/scripts/database/otrs-initial_insert.mysql.sql
$HOME_OTRS/scripts/database/otrs-schema-post.mysql.sql

PostgreSQL files:
---------------------
$HOME_OTRS/scripts/database/otrs-schema.postgresql.sql
$HOME_OTRS/scripts/database/otrs-initial_insert.postgresql.sql
$HOME_OTRS/scripts/database/otrs-schema-post.postgresql.sql

Generate sql files:
--------------
In case you would need to transform the .xml file to .sql yourself, use:

a) table definitions
--------------------
shell> cat scripts/database/otrs-schema.xml | bin/otrs.xml2sql.pl -n otrs_own-schema -o /tmp/ -t postgresql -s 1

Then you will find

o /tmp/otrs_own-schema.postgresql.sql (create tables, uniques and indexes)
o /tmp/otrs_own-schema-post.postgresql.sql (create foreign keys to other tables)

b) initial inserts
------------------
shell> cat scripts/database/otrs-initial_insert.xml | bin/otrs.xml2sql.pl -n otrs_own-initial_insert -o /tmp/ -t postgresql

Then you will find

o /tmp/otrs_own-initial_insert.postgresql.sql (initial inserts)


Database Setup Example (MySQL):
===============================
Create OTRS database:
---------------------
shell> mysql -u root -p -e 'create database otrs charset utf8'

Create the OTRS tables:
-----------------------
shell> mysql -u root -p otrs < scripts/database/otrs-schema.mysql.sql

Insert initial data:
-------------------
shell> mysql -u root -p otrs < scripts/database/otrs-initial_insert.mysql.sql

Create foreign keys to other tables:
------------------------------------
shell> mysql -u root -p otrs < scripts/database/otrs-schema-post.mysql.sql

Create a user for the database:
-------------------------------
shell> mysql -u root -p -e 'GRANT ALL PRIVILEGES ON otrs.* TO otrs@localhost IDENTIFIED BY "some-pass" WITH GRANT OPTION;'

Reload the grant tables of your mysql-daemon:
---------------------------------------------
shell> mysql -u root -p -e 'FLUSH PRIVILEGES;'


Database Setup Example (PostgreSQL):
====================================
PostgreSQL usually creates a user called 'postgres'. You should change
to the postgres user, via su, and then execute these commands.
We assume that your server can do MD5 identification; this is not
the default on most linux distributions.

Please refer to your database manuals for details. You can find general
instructions, which might be slightly different for your specific linux
version, here: https://help.ubuntu.com/10.04/serverguide/C/postgresql.html

Create a user for the database:
-------------------------------
shell> createuser -D -P -S -R otrs

Create OTRS database:
---------------------
shell> createdb --encoding=utf8 --owner=otrs otrs

Create the OTRS tables:
-----------------------
shell> psql otrs < scripts/database/otrs-schema.postgresql.sql

Insert initial data:
-------------------
shell> psql otrs < scripts/database/otrs-initial_insert.postgresql.sql

Create foreign keys to other tables:
------------------------------------
shell> psql otrs < scripts/database/otrs-schema-post.postgresql.sql


OTRS database configuration:
============================
Now add the correct database credentials to OTRS. Edit this section in your
configuration file $OTRS_HOME/Kernel/Config.pm

[...]
# Database name
$Self->{Database} = 'otrs';

# Database user
$Self->{DatabaseUser} = 'otrs';

# Database password
$Self->{DatabasePw} = 'some-pass';
[...]

If you're using PostgreSQL you should remove the comment (#)
before this line:
#    $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};host=$Self->{DatabaseHost};";

Now save the file and run $OTRS_HOME/bin/otrs.CheckDB.pl to see if the connection
is succesful. If this is working, you can log in to OTRS using the web frontend.

EOF