## 2.3. Database and UTF-8 configuration

### 2.3.1. MySQL

OLAT is UTF-8 capable and needs an UTF-8 capable database. Running MySQL with UTF-8 support (Introduces with MySQL 4.1) has shown to give a considerable performance hit (sometimes to a magnitude of 10). To avoid this performance hit, the MySQL driver mode conversion is a good alternative, although specific to MySQL. On our reference system, we are running MySQL in Latin1 (not UTF-8) and set the MySQL driver to translate to UTF-8. With this setup, the MySQL driver transparently translates between UTF-8 (OLAT) and Latin1 (MySQL DB). You'll need to configure the MySQL driver with the following parameters:

- useOldUTF8Behavior=true

- useUnicode=true

- characterEncoding=UTF-8

The JDBC URL for such a setup would look like this:

    jdbc:mysql://my.server.com:3306/olat?useOldUTF8Behavior=true&useUnicode=true&characterEncoding=UTF-8

NOTE: This is specific to the MySQL driver. Other drivers similarly may support driver-based translation but we suggest to use native UTF-8 with any database other than MySQL.

### 2.3.2. Postgres

There is a known installation that runs OLAT with Postgres. Thanks to Sebastian Hennebrueder (usenet@laliluna.de) for patches and testing for making OLAT Postgres compatible!

Olat is tested with PostgreSQL version 8.0.3. You can download PostgreSQL at http://www.postgresql.org. Create a new database 'olat' with a user and a group 'olat' and the password of your choice. We tested olat with unicode encoding but you may also try other encodings (e.g. Latin1) . If you are a developer and want to execute the jUnit tests, you also need an additional test-database. E.g. 'testdb' would be a good database name. For most users it is fine to just generate the 'olat' database. Below we show the steps setting up the database using the psql client, which is installed with PostgreSQL by default. You can find it in the PostgreSQL_HOME\bin directory. We will process the following steps:

    1. create a database
    2. create a user olat
    3. create a group olat and add the user to the group
    4. grant access to the database for the group olat

    psql -U admindbuser template1;
    #input password
    # create database

    CREATE DATABASE testdb
    WITH OWNER = replace_with_your_admin_user_eg_pgsql
    ENCODING = 'UNICODE'
    TABLESPACE = pg_default;

    # connect to testdb
    \connect testdb
    #setup user, group and privileges

    CREATE GROUP olat;
    CREATE USER olat PASSWORD 'olat' VALID UNTIL 'infinity';
    ALTER GROUP olat ADD USER olat;
    GRANT ALL ON SCHEMA public TO olat;

Finally you'll need to setup the necessary database tables for OLAT. We prepared the setup for MySQL and for PostgreSQL. Using MySQL use the following ant task: 'ant dbsetup' will do the job for you. Be careful with that target on a productive system since you will loose all your data! Using PostgreSQL use the ant task: 'ant dbsetup_postgreSQL' The ant tasks creates the tables for the database you configured in the build.properties. It will use the sql script setupPostgreSQL.sql in the directory OLAT_HOME/database. This task does not delete any tables like the MySQL task. When you want to delete all the tables you can use the sql script deletePostgreSQL.sql in the same directory.

### 2.3.3. Other databases

OLAT uses Hibernate as a database abstraction layer, so virtually any database can be used. We recommend using MySQL however. OLAT is well tested in conjunction with MySQL and there is only experience using Postgres as well. Make sure your database supports UTF-8.

Before you start you should make yourself familiar with the Hibernate framework. Search in the docu if your database is supported and jot down the name of the dialect for your database.

Follow this quick guide to use OLAT with another database:

1. Install driver for database XYZ

   - Put the jdbc-jar of your database in olat3/webapp/WEB-INF/lib

   - Modify in your olat3/build.properties the properties db.jdbc.driver, db.jdbc.jar and db.jdbc.url

   - Modify in your olat3/webapp/WEB-INF/olat_config.xml.in the hibernate database dialect in the database module. (modify the .in file, ant config-all will overwrite the .out and the .xml file.

2. Configure OLAT: run ant config-all

3. Generate the database for your database

   - Run the class olat3/webapp/WEB-INF/classes/org/olat/persistence/DatabaseSetup using the argument "net.sf.hibernate.dialect.XYZDatabaseDialect createScript"

   - Check the generated database file located at olat3/database/o_database.sql. Maybe you need to adjust some things. E.g. we use the MySQL datatype "text" explicitly. This can be replaces with "blob" or something similar.

4. Start OLAT.....

5. If successful, send us your feedback, your scripts, your How-To pages...

On later upgrades the changes provide in the database/alter_x_to_y.sql must be reviewed and modified each time.

### 2.3.4. JUnit tests

If you have created the separate junit database you can run the junit tests. The make sense if you use an other database than the tested or do change code on manager classes accessing the database. To run the tests run manually the following class: org/olat/tests/AllTests.java with eclipse and the following attribut. attribut -Dolatdir=/your/path/to/olat3. Make shure you have changed all the necessary properties in the build.propertie file and runned 'ant config-all'