# -- # 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