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: 8.0.29
MariaDB: 10.8.3
Firebird: 2.5.9
PostgreSQL: 11.16, 12.11, 13.7, 14.4
Microsoft SQL Server: 2014, 2017, 2019
Oracle: 12c Standard Edition Release 12.2.0.1.0 - 64bit
DB2 v11.1.3.3

Hint

Allegra comes with JDBC drivers for MySQL MariaDB, Firebird, Postgres and MS SQL Server. If you want to work with other database systems, you should place an 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 <allegra_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 $ALLEGRA_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 table space 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 $ALLEGRA_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:ALLEGRA

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: <allegra_admin_password>
>Enter it again: <allegra_admin_password>
>Password: <postgres user password>
>CREATE USER
$createdb --owner=trackp --username=trackp
--password track "Allegra Database"
>Password:  <allegra_admin_password>
>CREATE DATABASE
>Password:  <allegra_admin_password>
...

Database Connection Configuration#

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

This location of this database connection file is defined by the $ALLEGRA_HOME environment variable or Java system variable (JAVA_OPTS="-DALLEGRA_HOME=...").

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/allegra-data/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.

Notes on Databases

Contents

Notes on Databases#

Supported Database systems#

MySQL and MariaDB#

Firebird#

MS SQL Server#

Oracle#

IBM DB2#

PostgreSQL#

Database Connection Configuration#