Alternative Databases for EF

Installing new EF Portal instance

Overview

After install the new EF Portal instance, before start the EnginFrame Portal you can configure to use other database than Derby.

Before the first startc EF Portal will populate the database with all structure needed.

In this article you will find how to change the default database service and how to add the respective JDBC connector that will allow EF Portal to connect in the database.

MySQL

To use MySQL instead of Derby, please follow these steps:

  1. To install and configure the DB , please follow the documentation at https://docs.aws.amazon.com/enginframe/latest/ag/section-installation.html#mysql-version
    Note: If you do not know how to setup MySQL, please check: https://dev.mysql.com/doc/refman/8.0/en/linux-installation.html
  2. Download the respective JBDC connector for your Linux distro at https://dev.mysql.com/downloads/connector/j/
    • Extract the file and copy the jar file to $EF_ROOT/[version]/enginframe/WEBAPP/WEB-INF/lib/
      Remeber to change $EF_ROOT to enginframe base path. And [version] to your EnginFrame version.
      Note: If you installed the .deb or .rpm, probably the jar will be stored in:
      /usr/share/java/mysql-connector-java.jar
      /usr/share/java/mysql-connector-j.jar
  3. Edit the $EF_ROOT/conf/enginframe.conf DERBY_CONFIGURED:
    DERBY_CONFIGURED="false"
  4. Edit the file $EF_ROOT/conf/enginframe/server.conf and set the following parameters:
    ef.db.url=jdbc\:mysql\://[your_db_server_hostname]\:3306/EnginFrameDB;
    ef.db.admin.name=enginframedb
    ef.db.admin.password=efdbpassword
  5. Restart the EnginFrame
    systemctl restart enginframe
  6. Then check the systemd logs:
    journalctl -u enginframe.service

Important: If you created a database with character set different of latin1, you need to fix that:
ALTER DATABASE EnginFrameDB CHARACTER SET=’latin1′

You can review this configuration with the command below (check character_set_database):

MariaDB [EnginFrameDB]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MariaDB

You can follow the same MySQL procedure.

To setup MariaDB, you can download and install with this script (for any Linux distro):

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash

PostgreSQL

To use PostgreSQL instead of Derby, please follow these steps:

  1. To install and configure PostgreSQL, follow https://www.postgresql.org/download/linux/ubuntu/ for Ubuntu based Linux distros or https://www.postgresql.org/download/linux/redhat/ for RedHat based Linux distros.
  2. Download the respective JBDC driver connector for your Linux distro at https://jdbc.postgresql.org/download/
    • Copy the jar file to $EF_ROOT/[version]/enginframe/WEBAPP/WEB-INF/lib/
      Remeber to change $EF_ROOT to enginframe base path. And [version] to your EnginFrame version.
    • Fix the permission:
      chown efadmin:efnobody $EF_ROOT/[version]/enginframe/WEBAPP/WEB-INF/lib/postgresql*.jar
  3. Edit the /opt/nisp/enginframe/conf/enginframe.conf DERBY_CONFIGURED:
    DERBY_CONFIGURED="false"
  4. Edit the file $EF_ROOT/conf/enginframe/server.conf and set the following parameters:
    ef.db.url=jdbc\:postgresql\://[your_db_server_hostname]\:5432/EnginFrameDB;
    ef.db.admin.name=enginframedb
    ef.db.admin.password=efdbpassword
  5. Restart the EnginFrame
    systemctl restart enginframe
  6. Then check the systemd logs:
    journalctl -u enginframe.service

Important: If you created a database with character set different of latin1, you need to fix that:
ALTER DATABASE EnginFrameDB CHARACTER SET=’latin1′

Microsoft SQL Server

To use SQL Server instead of Derby, please follow these steps:

  1. To install and configure SQL Server, follow https://www.microsoft.com/en-us/sql-server/sql-server-downloads steps.
  2. Download the respectove JBDC driver connect for your SQL Server version at https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server
    • Copy the jar file (mssql-jdbc-*.jre11.jar) to $EF_ROOT/[version]/enginframe/WEBAPP/WEB-INF/lib/
      Remeber to change $EF_ROOT to enginframe base path. And [version] to your EnginFrame version.
    • Fix the permission:
      chown efadmin:efnobody $EF_ROOT/[version]/enginframe/WEBAPP/WEB-INF/lib/mssql-jdbc-*.jre11.jar
  3. Edit the /opt/nisp/enginframe/conf/enginframe.conf DERBY_CONFIGURED:
    DERBY_CONFIGURED="false"
  4. Edit the file $EF_ROOT/conf/enginframe/server.conf and set the following parameters:
    ef.db.url=jdbc\:sqlserver\://[your_db_server_hostname]\:1433/EnginFrameDB;
    ef.db.admin.name=enginframedb
    ef.db.admin.password=efdbpassword
  5. Restart the EnginFrame
    systemctl restart enginframe
  6. Then check the systemd logs:
    journalctl -u enginframe.service