Openstore Schema Core

openstore-schema-core provides a basic database schema used in the openstore project. It consists of around 80 tables specifically designed to deal with an online catalog.

Disclaimer: this is a work-in-progress !

Warning: the openstore-schema-core project relies on doctrine2 to facilitate schema generation. No work have been done to make entities useful through doctrine as openstore use zend-db for database access. In other words, schema generation works well but there's work to do if you need using doctrine for data access (fix naming of relationship, make repositories...).

Requirements

• PHP 7.1+
• MySQL or MariaDB with InnoDB

Features

• Automatic schema generation from doctrine entities.
• Support for painless schema migrations.
• Provides special triggers, procedures and functions (see extras).
• Raw sql generation. (see generated sql)

Install

Clone the project

$ git clone https://github.com/belgattitude/openstore-schema-core.git

Alternatively you can install the project through composer composer require openstore/schema-core

Setup

Configuration

Make a copy of the distribution config file and edit your database parameters:

$ cp ./config/openstore-schema-core.config.php.dist ./config/openstore-schema-core.config.php
# Edit database parameters
$ vi ./config/openstore-schema-core.config.php

Database

First ensure your database server is supported and enable support for utf8mb4 charset.

Database	Storage engine	File format	Large prefix
MySQL 5.6+	INNODB engine	Barracuda	On
Mariadb 10+	INNODB/XTRADB	Barracuda	On

Note: to be able to work with utf8mb4check that the following parameters are present in the mysql configuration file (my.cnf):

• innodb_file_per_table=1
• innodb_file_format=barracuda (default for MariaDb 10.2.2+, removed in 10.3)
• innodb_large_prefix=1 (default for Mariadb 10.2.2+, removed in 10.3)

Then create a new database:

$ mysql -e "CREATE DATABASE openstore_test CHARACTER SET='utf8mb4' COLLATE='utf8mb4_unicode_ci';" -u {USER} -p

Commands

You can list the registered commands by executing:

$ ./bin/openstore-schema-core

Practically, use the following commands for:

Command	Description
openstore:schema:create	Create a schema on a new empty database.
openstore:schema:recreate-extra	Recreate all triggers, procedures, functions...
openstore:schema:update	Apply schema migration on an existing database.

NOTE: use the option --dump-sql to display the DDL instead of applying onto the database. This is particularly useful to show migration needed over a production database, for example:

$ ./bin/openstore-schema-core openstore:schema:update --dump-sql

Will show the migrations that have to be applied on the current database.

FAQ

Caution: backup and restore recipes described below have not been for real production usage. Always be sure of what you're doing.

Backup a database

With mysqldump, good to backup schema (ddl) and data (inserts) in separated files, it allows to restore the data on a fresh database. As an example:

# 1. Backup of the schema (ddl: create tabes, routines...)
$ mysqldump -u {USER} -p --no-data --triggers --events --routines --default-character-set=utf8mb4 {DATABASE} > /{PATH}/{DATABASE}.schema.sql
# 2. Backup of the data (sql: inserts)
$ mysqldump -u {USER} -p --no-create-info --skip-triggers --complete-insert --disable-keys --default-character-set=utf8mb4 --lock-tables {DATABASE} > /{PATH}/{DATABASE}.data.sql 

Restore data on an newly created schema

First perform a backup with mysqldump as illustrated above, then create a new schema:

# 1. Generate the latest openstore schema
$ ./bin/openstore-schema-core openstore:schema:create --dump-sql > /{PATH}/openstore.schema.sql
# 2. Create a new database that will hold it  
$ mysql -e "create database {NEW_DATABASE} CHARSET='utf8mb4' COLLATE='utf8mb4_unicode_ci'" -u{USER} -p
# 3. Apply the latest generated schema on your newly created database
$ mysql -u {USER} -p {NEW_DATABASE} < /{PATH}/openstore.schema.sql
# 4. Restore the 'data' backup of your old database in the new database.
$ mysql -u {USER} -p {NEW_DATABASE} < /{PATH}/{BACKUP_FILE}.data.sql

Move/rename database

After having restored the database in the new schema, you might want to move/rename it.

For development or small database, you can use mysqldump:

# 1. Create the new database (a new empty one) 
$ mysql -e "create database {NEW_DATABASE} CHARSET='utf8mb4' COLLATE='utf8mb4_unicode_ci'" -u{USER} -p
# 2. Complete backup of your database 
$ mysqldump -u {USER} -p --routines --events --triggers --default-character-set=utf8mb4 {DATABASE} > /{PATH}/{DATABASE}.all.sql
# 3. Restore the complete backup in the new database
$ mysql -u {USER} -p {NEW_DATABASE} < /{PATH}/{DATABASE}.all.sql      

You can also (unix)-pipe the two last commands

Future

• Start working on repositories.
• Document tables and conventions.
• Add doctrine validate to CI (fix wrong relations names).

Notes

Unicode

From version 0.40.0, openstore-schema-core defaults to utf8mb4 charset by default.

If you don't have the possibility to set mysql server variables (i.e. on a continuous integration server...) you can fall back to the generated compatibility scripts.

Compressing tables

Optional, to reduce disk usage you can change the compression format of the following tables:

ALTER TABLE product_translation ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ALTER TABLE product_category_translation ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

ALTER TABLE sale_order_line ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ALTER TABLE sale_order ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Contributing

Contributions are welcome, fork the repository and make a pull request.

Tips for contributors

Be sure to execute code style check before commit:

$ composer cs-check

You can also fix the code style issues automatically:

$ composer cs-fix

Don't forget to regenerate regenerate the sql doc:

$ composer build