Compreg Database Setup

Introduction

Compound Registration can be configured to use the following database engines:

  • Oracle 11g or 12c or

  • MySQL 5.1 - 5.7

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>';

    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_bin;
  3. Ensure that the user has permission to connect to the database, and permission to create and populate tables.
    MySQL 5.5 - 5.7.5 example:

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

    For MySQL 5.7.6 and above, you must also include the REFERENCES permission:

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

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