Notes on Databases

This chapter describes how to set up an empty database for Allegra for the officially supported database systems.

Supported Database systems

Allegra has been tested with the following database systems:

  • MySQL: 5.7.14

  • MySQL: 8.0.16

  • MariaDB: 10.3.13

  • Firebird: 2.5.9

  • PostgreSQL: 11

  • Microsoft SQL server: 14.0.1000.169

  • Oracle: 12c Standard Edition Release 12.2.0.1.0 - 64bit

  • DB2 v11.1.3.3

Hint

Allegra comes with JDBC drivers for MySQL 5 and 8, Firebird, Postgres and MS SQL Server. If you want to work with other database systems, you should place the appropriate JDBC driver in the $TOMCAT_HOME/lib directory.

MySQL and MariaDB

Create a database using the command line client mysql. Use InnoDB as the database engine and configure utf8mb4 as the default encoding.

$ mysql -uroot -p
mysql> create database allegra default character set utf8mb4;
mysql> create user allegra@localhost identified by 'tissi';
mysql> grant all privileges on allegra.* to allegra@localhost;
mysql> flush privileges;

The database should now be up and running.

If you want to use the built-in backup feature of Allegra, you must configure your MySQL instance not to use backslash escapes. In file my.ini (under Windows) or my.cnf (under Unix) add the following entry:

sql-mode=''NO_BACKSLASH_ESCAPES''

If a mode entry already exists, add the named mode separated by a comma mode separated by a comma to the already existing modes.

Firebird

The software can be obtained from http://www.firebirdsql.org/en/downloads. Select the desired server binary for your operating system (super server architecture). The instructions below for Unix assume that you are using the Linux rpm.

You can find some helpful tools at http://www.ibphoenix.com. A nice tool to manage the database can be found at http://www.flamerobin.org.

Check if the database server is ready:

netstat -an | grep 3050

should give a row containing LISTEN.

Change the default SYSDBA password from MASTERKEY to your own. Do not forget this password!

/opt/interbase/bin/gsec -modify sysdba -user sysdba
-password MASTERKEY -pw <new_SYSDBA_password>

Add a new user with the name trackp and a password:

/opt/interbase/bin/gsec -add trackp -user sysdba
-password <new_SYSDBA_password>
-pw <trackplus_admin_password>

The database is created from scratch using the isql command line utility (don’t type the isql prompts) from where you extracted your Allegra database package:

/opt/interbase/bin/isql -u trackp -p tissi
isql> create database "/opt/interbase/db/allegra.gdb";
isql> quit;

Make sure that the owner and permissions are set correctly:

chown -R firebird /opt/interbase/db
chmod 700 /opt/interbase/db

In order to access the database via FlameRobin, you have to register it in this tool. The file path, which must be entered, is seen from the server (e.g. /opt/interbase/db/allegra.gdb). The alias has no meaning.

MS SQL Server

Allegra works well with MS SQL Server. Allegra uses the jTDS JDBC driver to connect to the database server. The correct setting of the character set and sorting must be observed, so that non-ANSI characters are properly stored and retrieved. This setting should be explicitly set when creating the database.

It is not recommended to use the default settings of the database server. Create the database with the Enterprise Manager. Make sure that you use the correct sort set, when you set up the database. It is helpful to add a new user who will be used as the owner of the database.

The database should now be ready to use. The MS SQL Server JDBC driver must be enabled in the $TRACKPLUS_HOME/Torque.properties file. You have to select the correct character set.

Example configuration for a German MS SQL Server standard installation:

# 4) Microsoft SQL Server
torque.database.track.adapter=mssql
torque.dsfactory.track.connection.driver = net.sourceforge.jtds.jdbc.Driver
torque.dsfactory.track.connection.url = jdbc:jtds:sqlserver://localhost/allegra;charset=CP1252

Example configuration for a standard UTF-8 MS SQL Server installation with instance like Sqlserver\INSTANCEX:

# 4) Microsoft SQL Server
torque.database.track.adapter=mssql
torque.dsfactory.track.connection.driver = net.sourceforge.jtds.jdbc.Driver
torque.dsfactory.track.connection.url =
        jdbc:jtds:sqlserver://sqlserver/track;charset=UTF8;instance=INSTANCEX

Oracle

Allegra has been tested to work with Oracle 12 databases. This document does not cover how to set up a database in Oracle. Your database administrator must provide you in your schema with all privileges to create and modify objects.

You must obtain an appropriate JDBC driver for your Oracle server from the official Oracle download site and install it under $TOMCAT_HOME/lib.

The default maximum CLOB size is 4000 characters. If you do not change this, your long text fields in Allegra will be limited to this size.

IBM DB2

Allegra supports IBM’s DB2 database. Here are some Notes on how to proceed.

Install DB2 and create a new database (e.g. named ALLEGRA). The name of the schema should be the same as the database user name that you will use later to connect the Allegra software with the database. You should have enough table ranges. The default is 4k, which you should increase to 32K. If the tablespace is not sufficient, the scripts will not run.

You must obtain a suitable JDBC driver for your DB2 server from the official IBM download site and install it under $TOMCAT_HOME/lib.

Modify the $TRACKPLUS_HOME/Torque.properties, to map it to db2. Comment out all other similar entries and activate the following entries:

torque.dsfactory.track.connection.user=yourDB2Username
torque.dsfactory.track.connection.password=yourDB2Password

#DB2
torque.database.track.adapter=db2app
torque.dsfactory.track.connection.driver=
    COM.ibm.db2.jdbc.app.DB2Driver
torque.dsfactory.track.connection.url=jdbc:db2:TRACK

PostgreSQL

Allegra supports PostgreSQL databases. Here are some hints, to run an Allegra database on a PostgreSQL server.

$createuser --username=postgres --password -d -P -A
--sysid=501 trackp
>Enter password for new user: <trackplus_admin_password>
>Enter it again: <trackplus_admin_password>
>Password: <postgres user password>
>CREATE USER
$createdb --owner=trackp --username=trackp
--password track "Allegra Database"
>Password:  <trackplus_admin_password>
>CREATE DATABASE
>Password:  <trackplus_admin_password>
...

Database Connection Configuration

You need to connect the database you created to the Allegra application. This is done using entries in the $TRACKPLUS_HOME/Torque.properties file.

This database connection file is searched for in the following locations in this order:

  1. In file webapps/Torque.properties.<context name> in the webapps directory of the container. Example: webapps/Torque.properties.allegra-demo. This allows to run multiple Allegra instances in the same container.

  2. In file $TRACKPLUS\_BOOT/Torque.properties, where $TRACKPLUS_BOOT is an environment variable.

  3. In file $TRACKPLUS_BOOT/Torque.properties where $TRACKPLUS_BOOT is a Java system property (JAVA_OPTS="-DTRACKPLUS\_BOOT=...").

  4. In file webapps/<context name>/WEB-INF/Torque.properties.

The following is an excerpt from a Torque.properties file.

# !!! THIS USER WITH THIS PASSWORD HAS TO BE KNOWN BY YOUR
#      DATABASE SYSTEM !!!

torque.dsfactory.track.connection.user = trackp
torque.dsfactory.track.connection.password = tissi
# 1) Firebird on Windows systems
torque.database.track.adapter=firebird
torque.dsfactory.track.connection.driver = org.firebirdsql.jdbc.FBDriver
torque.dsfactory.track.connection.url =
    jdbc:firebirdsql://localhost/D:/Data/trackdata/allegra.gdb
# 2) Firebird on Unix
...

The user name and password must match the entries you have set up for the database. The JDBC driver and the connection URL depend on your database name and type. There is for each supported database type an example in the standard Torque.properties file.

After changing Torque.properties you have to restart your Tomcat.