Create and Configure a Database for Identity Services

To create a database for Identity Services, follow these steps:

Create a database for idconfig with a custom name along with prefix and suffix. Prefix and suffix are optional. For example, database name is <prefix>idconfig<suffix>.

Note: For Oracle databases a schema name should be in capital letters.

Note: For Oracle, create necessary tablespaces and Users before proceeding. Refer to Prerequisites for Volt MX Foundry with Oracle.

Important: In addition to the grants, add the following grants while creating an Oracle DB:
  – grant create any table to idconfig;
  – grant create any index to idconfig;
  – grant alter any table to idconfig;

The following is a sample query for creating a database in MSSQL:

CREATE DATABASE idconfig;

The following is a sample query for creating a database in MySQL:

CREATE DATABASE< DBNAME >DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

For Foundry version V9.2.2.0 or greater:

The following is a sample query for creating a database in MySQL:

CREATE DATABASE< DBNAME >DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The following details are required for Flyway configuration:

Schema name for Identity: idconfig
Placeholders for Identity Services:


    # Volt MX Identity Services Database, replace the following placeholders 
    # in SQL migrations for your database flyway.placeholders.VOLTMX_AUTH_LOG_LOCATION=C:VoltMXFoundry/logs
    flyway.placeholders.VOLTMX_AUTH_LOG_OPTION=logfile  
    flyway.placeholders.VOLTMX_AUTH_CONFIG=idconfig  
    flyway.placeholders.VOLTMX_AUTH_GLOBAL_DB=idconfig (This placeholder is applicable only for MySQL)
    flyway.placeholders.VOLTMX_AUTH_LOG_LEVEL=INFO
    flyway.placeholders.VOLTMX_MFINSTALLER_VERSION=<MF_version format: 8.x.0_GA>
    flyway.placeholders.VOLTMX_MFINSTALLER_TYPE=<enter the Volt MX Foundry installation type 
    such as production, non-production, or development>

Tablespace Placeholders for Oracle:

Product Name	Tablespace Placeholders for Oracle
Identity or Auth	AUTH_DATA_TABLESPACE, AUTH_INDEX_TABLESPACE, AUTH_LOB_TABLESPACE

SQL files paths for Identity Services in VoltMXFoundry_Plugins folder:

Path for SQL files in the VoltMXFoundry_Plugins folder	Database	Component
\VoltMXFoundry_Plugins\scripts\javamigrations-auth\com\voltmx\auth\migrations\mysql	MySQL	Identity
\VoltMXFoundry_Plugins\scripts\javamigrations-auth\com\voltmx\auth\migrations\oracle	Oracle
\VoltMXFoundry_Plugins\scripts\javamigrations-auth\com\voltmx\auth\migrations\sqlserver	SQL Server

Execute all SQL scripts by using the steps provided at Configuring Flyway Command-line Tool.

Click here to view the Identity schema diagram

Since the structure of flyway has changed from Flyway 3.2.1 to Flyway 4.0.3 in Volt MX Foundry installer, execute the following statements to make the schema_version table compatible with Flyway 4.0.3.

Oracle:
drop index "schema_version_ir_idx";
drop index "schema_version_vr_idx";
ALTER TABLE "schema_version" DROP constraint "schema_version_pk" drop index;
ALTER TABLE "schema_version" DROP COLUMN "version_rank";
ALTER TABLE "schema_version" modify("version" null);
ALTER TABLE "schema_version" add constraint "schema_version_pk" primary key("installed_rank");

MySQL:
ALTER TABLE schema_version DROP INDEX schema_version_vr_idx;
ALTER TABLE schema_version DROP INDEX schema_version_ir_idx;
ALTER TABLE schema_version DROP PRIMARY KEY;
ALTER TABLE schema_version DROP COLUMN version_rank;
ALTER TABLE schema_version CHANGE version version VARCHAR(50);
ALTER TABLE schema_version ADD PRIMARY KEY (installed_rank);

SQL Server:
DROP INDEX schema_version_ir_idx ON dbo.schema_version
GO
DROP INDEX schema_version_vr_idx ON dbo.schema_version
GO
ALTER TABLE dbo.schema_version DROP CONSTRAINT schema_version_pk
GO
ALTER TABLE dbo.schema_version DROP COLUMN version_rank
GO
ALTER TABLE dbo.schema_version ADD CONSTRAINT schema_version_pk PRIMARY KEY CLUSTERED (installed_rank)
GO
ALTER TABLE dbo.schema_version ALTER COLUMN version nvarchar(50) NULL
GO