# --
# README.database - database description of OTRS
# Copyright (C) 2001-2007 OTRS GmbH, http://otrs.org/
# --
# $Id: README.database,v 1.17 2007/05/21 14:19:55 martin Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (GPL). If you
# did not receive this file, see http://www.gnu.org/licenses/gpl.txt.
# --

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

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

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

From OTRS 2.0 on we moved from the torque project to our own xml2sql (
based on Kernel/System/DB/*.pm driver).

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

MySQL sql 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 sql 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

Own transform:
--------------
In case you transform the .xml file to .sql, use:

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

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/xml2sql.pl -n otrs_own -o /tmp/ -t postgresql

Then you will find

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


Initial insert file:
====================
$HOME_OTRS/scripts/database/otrs-initial_insert.*.sql contains all needed
standard values. At first use the otrs-schema.*.sql and the insert this file.


DB - Setup Example (MySQL):
===========================
Create OTRS database:
---------------------
shell> mysql -u root -p -e 'create database otrs'
(if you want to use utf8, use this one)
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 an database user:
-----------------------
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> mysqladmin -u root -p reload

**************************************************************
*                                                            *
* Change the DB-Settings (host, database, user and password) *
*                                                            *
*   $OTRS_HOME/Kernel/Config.pm                              *
*   [...]                                                    *
*   # Database                                               *
*   # (The database name.)                                   *
*   $Self->{Database} = 'otrs';                              *
*                                                            *
*   # DatabaseUser                                           *
*   # (The database user.)                                   *
*   $Self->{DatabaseUser} = 'otrs';                          *
*                                                            *
*   # DatabasePw                                             *
*   # (The password of database user.)                       *
*   $Self->{DatabasePw} = 'some-pass';                       *
*   [...]                                                    *
*                                                            *
**************************************************************

EOF