Database Creation

Creating Database in MySQL 5.x

Creating the Database

1. In the command prompt, open MySQL:

   mysql -u root -p
   

2. In MySQL, enter the following command to create a database:

   create database COMMERCEDB character set utf8;
   

   When starting fresh with Elastic Path 7.5.0, it is recommended you name the database schema COMMERCEDB. This will make configuring Liquibase simpler, as this has been set as the default.

3. Enter the following command to create a user that has complete access to COMMERCEDB:

   grant all privileges on COMMERCEDB.* to ep@'localhost' identified by 'ep';
   

   ep/ep is the default username/password combination used by Elastic Path.

Table Name Case Sensitivity

On Unix systems, table names in SQL (Structured Query Language) queries may be case sensitive. To turn off case sensitivity, refer to the MySQL reference manual. Most cases this means adding the line lower_case_table_names under the section [mysqld] in /etc/mysql/my.cnf file.

Setting Transaction Isolation Level

7.5.1

This setting is available as of version 7.5.1.

You must change the transaction isolation level of MySQL database from the current default setting REPEATABLE_READ to READ_COMMITTED to avoid optimistic locks.

To change the level, add transaction_isolation=READ-COMMITTED line under the section [mysqld] in the /etc/mysql/my.cnf file and restart the database server.

Creating the Database in Oracle 11g

Creating a Database User Account

In Oracle databases, a database schema is mapped to a user account, so we only need to create a user account for the Elastic Path system.

1. Start the SQL*Plus database client by entering the following command:

   sqlplus /nolog
   

2. Connect to the database as the SYSuser:

   conn / as sysdba
   

3. In SQL*Plus, create a database user account.

   create user COMMERCEDB identified by ep;
   

   When starting fresh with Elastic Path 7.5.0, it is recommended you use the name/password combination of COMMERCEDB/ep. This will make configuring Liquibase simpler, as this has been set as the default.

4. Grant the user appropriate permissions.

   grant connect, resource, dba to COMMERCEDB;
   

5. Exit SQL*Plus by running: exit

Creating the Database in Oracle 12c

Creating a Pluggable Database

New in Oracle 12c is the concept of pluggable databases. We will create one along with a user account for the Elastic Path system.

1. Open the DBCA Utility:

   cd $ORACLE_HOME/bin && ./dbca
   

2. Delete the default orcl database:

   • Select "Delete Database" and click "Next"
   • Select orcl database (if not already selected) and click "Next"
   • Skip "Management Options" by clicking "Next", and then finally, click "Finish"

3. Re-create the database orcl and specify a pluggable database name of eppdb:

   • Rerun the DBCA Utility

   • Select "Create Database" and click "Next"

   • Fill in the following fields and use the default values for other fields not specified:

     Property	Value
     Global Database Name	orcl
     Administrative Password	<password>
     Create as Container Database	Checked ON
     Pluggable Database Name	eppdb

   • Click "Next" and then "Finish"

4. You can connect to the pluggable db, eppdb, by its service name eppdb and using a slash in the URL like:

   jdbc:oracle:thin:@<ip>:1521/eppdb
   

Pushing Database Changes

Elastic Path recommends using the Data Population Command Line Interface (CLI) to push schema changes and catalog data updates to the production database. This tool is built in the extensions/data-population/ext-data-population-cli project.

The deployment package created by the extensions/packager/ext-deployment-package project contains the Data Population CLI as well as all the Liquibase change sets and the Import/Export data sets needed for deployment. For each target database, the following configuration files are required:

Property	Description
database.properties	Defines database connection properties and the data population environment name itself for this environment.
data-population.properties	Defines properties required by the Data Population tool for execution in this environment; e.g. Liquibase contexts to activate for this environment.
filtering.properties	Defines the values used when overriding property placeholders in data files for this environment.

Further reading

• Data Population - Overview
• Data Population - Deployment Models
• Data Population - Command Line Interface (CLI)