Step 4: Initializing Database

Warning:

If you are upgrading from a previous version of iObeya, follow the procedure in the Upgrading your iObeya platform.

Warning:

If an error occurs when you run during the database initialization, please stop and contact our Support team.

Starting with iObeya version  4.20, Liquibase is used to version control the iObeya database schema:

• Liquibase is an open-source database-independent library for tracking, managing, and applying database schema changes.

• It provides a way to apply changes to the database in a consistent and trackable manner.

In this article, learn how to use Liquibase to initialize the iObeya database schema.

About Liquibase directory structure

In the iObeya packages you have downloaded, you find a Liquibase directory that contains the following files and directories:

• iobeya-sql-changelog.jar (the Liquibase changesets package containing all the changes to initialize the iObeya database)

• mysql

  • mysql-connector-java.jar (the JDBC driver for MySQL)

  • liquibase.properties (Liquibase configuration file preconfigured for MySQL)

• mariadb

  • mariadb-java-client.jar (the JDBC driver for MariaDB)

  • liquibase.properties (Liquibase configuration file preconfigured for MariaDB)

• oracle

  • ojdbc8.jar (the JDBC driver for Oracle)

  • liquibase.properties (Liquibase configuration file preconfigured for Oracle)

Configuring Liquibase

You need to edit the preconfigured liquibase.properties file corresponding to your database server to provide the information that Liquibase needs to connect.

Warning:

Even if it is possible, it is strongly recommended that you do not include authentication information in your liquibase.properties file. Instead, use instead command-line arguments to provide sensitive credentials using environment variables for instance.

From the root directory of the iObeya packages:

1. Open the liquibase directory.

2. Open the directory corresponding to your database server (e.g mariadb, mysql, oracle).

3. Edit the liquibase.properties file.

4. Change the liquibase.command.url: provide the URL and port of your database server.

5. If your database MariaDB or MySQL you need to set the variable createDatabaseIfNotExist to true at the end of the connection url.  

   Warning:

   Do not forget to set createDatabaseIfNotExist to true. During the database initialization procedure, Liquibase will create a dedicated database named iobeya. If you want to change the database name during the process, you need to adapt accordingly the database name in the URL defined in the liquibase.properties file.

Initializing the database

To initialize your database:

1. Go into the Liquibase directory of the iObeya packages.

2. Run the CLI commands corresponding to your database server.

For each supported database, you find:

• procedure to initialize the iObeya database with our default settings:

  • MySQL: Initializing the database with default settings

  • MariaDB: Initializing the database with default settings

• advanced procedure allowing to change the database settings.

  • MySQL: Initializing the database with custom settings

  • MariaDB: Initializing the database with custom settings

Note:

Open a terminal window in the liquibase directory.

MySQL: Initializing the database with default settings

If you want to keep the default iObeya database settings, use the following command and replace <ROOT_USERNAME> and <ROOT_PASSWORD> with the ROOT username and password of your MySQL server.

java -jar iobeya-sql-changelog.jar --defaultsFile=mysql/liquibase.properties --username=<ROOT_USERNAME> --password=<ROOT_PASSWORD> update

Liquibase is going to execute the changesets required to initialize the iObeya database schema.

At the end of the process, you should see the following message.

Liquibase command 'update' was executed successfully.

MySQL: Initializing the database with custom settings

If you want to modify the default iObeya database settings (database name, username, or password), use the following command. Replace <ROOT_USERNAME> and <ROOT_PASSWORD> with the database ROOT username and password of your MySQL server, and provide the values for <DATABASE_NAME>, <DATABASE_USERNAME>, and <DATABASE_PASSWORD>.

java -Ddb.username=<DATABASE_USERNAME> -Ddb.default.password=<DATABASE_PASSWORD> -Ddb.name=<DATABASE_NAME> -jar iobeya-sql-changelog.jar --defaultsFile=mysql/liquibase.properties --username=<ROOT_USERNAME> --password=<ROOT_PASSWORD> update

Liquibase is going to execute the changesets required to initialize the iObeya database schema.

At the end of the process, you should see the following message:

Liquibase command 'update' was executed successfully.

MariaDB: Initializing the database with default settings

If you want to keep the default iObeya database settings, use the following command and replace <ROOT_USERNAME> and <ROOT_PASSWORD> with the ROOT username and password of your MariaDB server.

java -jar iobeya-sql-changelog.jar --defaultsFile=mariadb/liquibase.properties --username=<ROOT_USERNAME> --password=<ROOT_PASSWORD> update

Liquibase is going to execute the changesets required to initialize the iObeya database schema.

At the end of the process, you should see the following message:

Liquibase command 'update' was executed successfully.

MariaDB: Initializing the database with custom settings

If you want to modify the default iObeya database settings (database name, username, or password), use the following command. Replace <ROOT_USERNAME> and <ROOT_PASSWORD> with the database ROOT username and password of your MariaDB server, and provide the values for <DATABASE_NAME>, <DATABASE_USERNAME>, and <DATABASE_PASSWORD>.

java -Ddb.username=<DATABASE_USERNAME> -Ddb.default.password=<DATABASE_PASSWORD> -Ddb.name=<DATABASE_NAME> -jar iobeya-sql-changelog.jar --defaultsFile=mariadb/liquibase.properties --username=<ROOT_USERNAME> --password=<ROOT_PASSWORD> update

Liquibase is going to execute the changesets required to initialize the iObeya database schema.

At the end of the process, you should see the following message:

Liquibase command 'update' was executed successfully.