README

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.

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:

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

openstore / schema-core

Maintainers

Details