openstore/schema-core


README

PHP Version Build Status Latest Stable Version Total Downloads License

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 !

product_erd_image

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):

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