Create and Configure a Database for Accounts

To create a database for Volt MX Accounts, follow these steps:

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

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

CREATE DATABASE mfaccountsdb;

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:

To create mfaccountsdb:

The following is a sample query for creating a database in MySQL: CREATE DATABASE <DBNAME> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note: For Oracle databases a schema name should be in capital letters.
Find the word mfaccountsdb in SQL files located in the accounts scripts and replace it with MFACCOUNTSDB.

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

Important: If you have created an accounts database with a custom name (for example, <prefix>mfaccountsdb<suffix>), prepend the statement use <prefix>mfaccountsdb<suffix> in all SQL scripts in the beginning of every file in the \VoltMXFoundry_Plugins\scripts\accountsdbmigration folder.

The following details are required for Flyway configuration:

Schema name for accounts: mfaccountsdb
Placeholders for Volt MX AccountsDB:


# For Volt MX Accounts Services Database, replace the following placeholders
# in SQL migrations for your database
flyway.placeholders.VOLTMX_ACCOUNTS_LOG_LOCATION= < INSTLL_DIR>/logs
flyway.placeholders.WAAS_BASE_URL=< PUBLIC_URL>/workspace
flyway.placeholders.VOLTMX_ACCOUNTS_LOG_LEVEL=INFO
flyway.placeholders.VOLTMX_ACCOUNTS_LOG_OPTION=logfile
flyway.placeholders.COM_PROTOCOL=http
flyway.placeholders.DISP_APP_SERVER=
flyway.placeholders.DISP_DB_CHOICE=
flyway.placeholders.DISP_INSTALLATION_CHOICE=Manual
flyway.placeholders.DISP_INSTALL_MODE=Single
flyway.placeholders.ONE_BOX_SETUP=yes
flyway.placeholders.PREINSTALL_DB_NAME_LIST=
flyway.placeholders.MF_INSTALLER_VERSION=
flyway.placeholders.SELECTED_COMP=
flyway.placeholders.USER_INSTALL_DIR=/root/MF73m


flyway.placeholders.VOLTMX_ACCOUNTS_LOGGER_JNDI= same as < accounts schema jndi, 
for example: 
      - Tomcat             : java:comp/env/jdbc/voltmxaccounts
      - JBoss              : java:/voltmxaccounts

flyway.placeholders.MF_INSTALLER_VERSION=< enter Volt MX Foundry version number,
for example: 7.2.0.0>

flyway.placeholders.DISP_INSTALLATION_CHOICE=< enter Volt MX Foundry installation choice, 
for example: NEW or Upgrade>

flyway.placeholders.SELECTED_COMP=< enter Volt MX Foundry components to be installed such as Console Services,Identity Services, Integration Services, Engagement Services>

flyway.placeholders.DISP_APP_SERVER=< enter application server details 
such as Tomcat 7.0.70, JBoss 6.4, or WebSphere 8.5.5.5>

flyway.placeholders.DISP_INSTALL_MODE=< enter install mode, 
for example: Standalone or Domain>

flyway.placeholders.COM_PROTOCOL=< enter protocol type, 
for example: http or https>

flyway.placeholders.DISP_DB_CHOICE=< enter db type, 
for example: MySQL, SqlServer, or Oracle>

flyway.placeholders.PREINSTALL_DB_NAME_LIST=< enter database names, 
for example: VOLTMXMFACCOUNTSDB1, VOLTMXADMINDB1,VOLTMXMFCONSOLEDB,
VOLTMXMFREPORTSDB1,VOLTMXIDGLOBALDB,VOLTMXIDCONFIGDB,VOLTMXVPNSDB>

flyway.placeholders.USER_INSTALL_DIR=< enter install folder path. 
Provide appropriate value for manual installation>

flyway.placeholders.ONE_BOX_SETUP=true for development or false in case production>





Tablespace Placeholders for Oracle:



Product Name
Tablespace Placeholders for Oracle




Accounts
ACCOUNTS_DATA_TABLESPACE, ACCOUNTS_INDEX_TABLESPACE, ACCOUNTS_LOB_TABLESPACE





SQL files paths for Volt MX Accounts DB in VoltMXFoundry_Plugins folder:



Click here for more details



Path for SQL files in the QuantumFabric_Plugins folder
Database
Component


\VoltMXFoundry_Plugins\scripts\accountsdbmigration
MySQL
Accounts


\VoltMXFoundry_Plugins\scripts\migrations-accounts-oracle
Oracle


\VoltMXFoundry_Plugins\scripts\migrations-accounts-sqlserver
SQL Server





Execute all SQL scripts by using the steps provided at Configuring Flyway Command-line Tool.
Click here to view the Accounts 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



In flyway conf, set flyway.baselineOnMigrate=true
set flyway.validateOnMigrate=false

Path for SQL files in the QuantumFabric_Plugins folder	Database	Component
\VoltMXFoundry_Plugins\scripts\accountsdbmigration	MySQL	Accounts
\VoltMXFoundry_Plugins\scripts\migrations-accounts-oracle	Oracle
\VoltMXFoundry_Plugins\scripts\migrations-accounts-sqlserver	SQL Server