Database setup

    Introduction

    Compound Registration is supported with:

    • Oracle 19c
    • PostgreSQL 15
    • MySQL 5.7

    Compound Registration is compatible with:

    • Oracle 19c, 21c and XE
    • PostgreSQL 15
    • MySQL 5.7

    {info} Oracle 19c and XE compatibility is available since CompReg version 20.8 and LTS-Fermium.

    {info} PostgreSQL compatibility is available since CompReg version 23.16

    To prepare your database for installation you need to execute the following steps:

    Step 1: Configure your database

    1. Create a database user (e.g. regsys_user ) that Compound Registration will use to connect to the database server.

      MySQL example:

      CREATE USER 'regsys_user'@'<YOUR_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';

      PostgreSQL example:

      CREATE USER regsys_user WITH PASSWORD '<PASSWORD>';

      Oracle example:

      create user regsys_user identified by <PASSWORD> default tablespace <TABLESPACE_NAME> quota unlimited on <TABLESPACE_NAME>;

      Usually, when you create a user in Oracle, Oracle will create a 'schema' automatically.

    2. Create an empty database (e.g. regsys ), that uses UTF-8 character set (or AL32UTF8, the Oracle equivalent of UTF-8)

      MySQL example:

      CREATE DATABASE regsys CHARACTER SET utf8 COLLATE utf8_unicode_ci;

      PostgreSQL example:

      CREATE DATABASE regsys;

    3. Ensure that the user has permission to connect to the database, and permission to create and populate tables.

      MySQL example:

      GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX,LOCK TABLES on regsys.* TO 'regsys_user'@'<SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;

      PostgreSQL example:

      Connect to the database created in step #2.

      CREATE SCHEMA regsys;
ALTER DATABASE regsys SET SEARCH_PATH TO regsys;
GRANT ALL ON SCHEMA regsys TO regsys_user;

      Oracle example:

      grant connect to regsys_user;
grant create table to regsys_user;
grant create sequence to regsys_user;
grant resource to regsys_user;

    Step 2: Copy JDBC driver to application server

    A JDBC driver is a software component enabling a Java application to interact with a database. To connect with individual databases, JDBC requires drivers for each database. These libraries cannot be packed with the Compound Registrations system, you have to copy this library to the application server.

    1. Download the JDBC driver

    For MySQL users:

    Download the recommended MySQL driver from http://dev.mysql.com/downloads/connector/j

    You can download either the .tar.gz or the.zip file by selecting the 'Platform Independent' option. Extract the jar for the driver (e.g. mysql-connector-java-5.x.x-bin.jar) from the archive.

    For PostgreSQL users:

    Download the PostgreSQL driver for Java 8 from https://jdbc.postgresql.org/download/

    For Oracle users:

    Depending on your Oracle version, download the jar file from this website: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

    2. Copy the driver to the application server

    Once you've got the proper .jar JDBC driver (e.g. mysql-connector-java-5.1.24.jar or postgresql-42.7.1.jar or ojdbc6-11.2.0.4.jar ), copy to your $TOMCAT_HOME/lib folder.

    3. Restart application server

    After you copied the JDBC driver, restart your Tomcat, so the changes will take effect.