Database setup

Database schema

Preconditions

These are the preconditions for creating the schema:

• There is a database service available.
• Both the database and database users are created.

Example for the user creation in MariaDB

CREATE DATABASE IF NOT EXISTS nevisadapt;
CREATE USER 'nevisadapt'@'%' IDENTIFIED BY 'yourSecurePassword';
GRANT ALL PRIVILEGES ON nevisadapt.* To 'nevisadapt'@'%' IDENTIFIED BY 'yourSecurePassword';

Creating and maintaining the schema

After the installation of nevisAdapt, it is possible to create and maintain the database schema. For this, you can use, for example, Flyway, an open source database migration tool.

By default, nevisAdapt uses the database connection configured in the file /var/opt/nevisadapt/conf/nevisadapt.properties. However, you can also configure the parameters with the command line. See the sample command below:

[root@nevisdetect-dev rpm]# /opt/nevisadapt/flyway/bin/flyway.sh info
Flyway 4.1.2 by Boxfuse

Database: jdbc:oracle:thin:@oravml11.zh.adnovum.ch:41010:DCHNEV01 (Oracle 11.2)

+-----------+---------+----------------------+------+---------------------+---------+
| Category  | Version | Description          | Type | Installed on        | State   |
+-----------+---------+----------------------+------+---------------------+---------+
| Versioned | 1       | nevisAdapt Schema    | SQL  | 2019-01-23 10:48:05 | Success |
| Versioned | 2       | Geolocation Extended | SQL  | 2020-10-01 12:42:20 | Success |
+-----------+---------+----------------------+------+---------------------+---------+

Flyway supports the following commands:

Name	Description
migrate	Migrates the database.
clean	Drops all objects in the configured schemas.
info	Prints the details and status information about all the migrations.
validate	Validates the applied migrations against the ones available on the classpath.
baseline	Baselines an existing database, excluding all migrations up to and including the baseline version.
repair	Repairs the schema history table.

Supported databases

nevisAdapt supports the following databases:

• Oracle
• MariaDB

To load the data types for the Flyway script, perform the following steps:

1. Determine the database vendor from the JDBC URL (it should contain either the string jdbc:oracle or jdbc:mariadb).
2. Depending on the database vendor, load the data types for the Flyway script from either the flyway_oracle.properties file or the flyway_mariadb.properties file.

MariaDB

If you use MariaDB, do the following:

• Set the parameter useMysqlMetadata to "true" in your JDBC connection URL. For example: jdbc:mariadb://127.0.0.1:3306/nevisadapt?useMysqlMetadata=true
• Set the attribute lower_case_table_names to "1" in the MySQL settings (my.cnf).

Oracle

If you use an Oracle database, either copy or create a link to the JDBC drivers in the /var/opt/nevisadapt/libs directory.