Database setup

Preconditions

These are the preconditions for creating the database 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  | 2023-11-11 11:39:54 | Success |
| Versioned | 2       | Geolocation Extended        | SQL  | 2023-11-11 11:39:54 | Success |
| Versioned | 3       | Session Trusted             | SQL  | 2023-11-11 11:39:54 | Success |
| Versioned | 4       | Browser Data                | SQL  | 2023-11-11 11:39:54 | Success |
| Versioned | 5       | Observation Reporting       | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 6       | User Device                 | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 7       | Remember Me                 | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 8       | Risk Events                 | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 9       | Aggregate User Observations | SQL  | 2023-11-11 11:39:55 | Success |
| Versioned | 10      | Optimize Statistics         | SQL  | 2023-11-11 11:39:55 | 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:

• MariaDB
• Oracle
• PostgreSQL

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.

PostgreSQL

If you use PostgreSQL with Flyway, the Flyway schema history table name has to be in lower case.