...

This module automatically keeps the database up to date with help of SQL files which are tracked by this module.
Therefore, if the SQL files are tracked with a VCS, the database level is bound to the VCS revision.

...

To set up database migration in your project, follow the following steps:

• Run composer require nostromo/module-database-migration

  ~6

  :dev-nostromo-8

• Activate migrations by hooking the PHP code DatabaseMigrationController::runUpdate(); where you want it. Two good examples are shown below.

• Put your migration files into ./src/migrations/ (Environment::get()->migrationDir). Migration files are simple SQL files which hold semicolon separated SQL statements. Example below.

Consistency

Migration file names

Your database migration files need to have an ascending order regarding their file names.
The reason for this is that it is assumed that your migration files build up on their ancestors.

New migration files therefore need to have a lower sorting than old ones.
Else they will be ignored. For example, a meaningful file name scheme would be:

1. 20190403_clear_api_logs.sql

2. 20190404_added_indexes.sql

3. 20190406_my_new_sql_file.sql

The database migration will keep track of which migration files were executed, and how many SQL statements of
each file were executed. The statistics are saved to the database itself into __database_migration.

...

The behavior described above brings a problem when you are working with several branches and add migration files in
parallel.
If you have already run the last migration update of your branch, the migration file that was created first in
another branch will be ignored. Like so:

1. 20190403_clear_api_logs.sql

2. 20190404_added_indexes.sql

3. 20190405_merged_from_other_branch.sql

4. 20190406_file_from_my_branch.sql

You will not be warned about this. However, if you did not run the migration update, there will no problem.

...

The database migration module will check the last migration file for new statements - even if it was already executed.
This is a handy feature if you are a developer and want to add SQL statements to your last migration file which is not
yet committed in your VCS.

...

Application::event()->addListener(
    FrameworkEvents::ROUTING_BEFORE,
    function (\NewFrontiers\Framework\Events\FrameworkEvent $event) {
        NewFrontiers\Modules\DatabaseMigration\DatabaseMigrationController::runUpdate();
    });

Additionally, you may want to add DatabaseMigrationController::runUpdate(); to your bootstrapTests.php too, so
that your test database gets migrated as well.

...

If there is an Update.php in your project - especially for Docker projects - it is recommended to put the update command
right into the Update.php. This way, migration updates are checked/run whenever the docker container gets startet. This has the benefit of not
slowing down your website.

[...]
echo "Updating database structures...\n";
$controller->forwardEngineerAllModules();

NewFrontiers\Modules\DatabaseMigration\DatabaseMigrationController::runUpdate();
[...]

Migration files

...

Put your migration files into ./src/migrations/ (Environment::get()->migrationDir). Migration files are simple SQL files consisting of SQL statements. The statements have to be separated with semicolons.

Please make sure your SQL statements are as generic as can be, so that your customer database and your test database
can get migrated without problems.

Migration file example

INSERT INTO user`user` (us_benutzername) VALUES ('username');
UPDATE user `user` SET us_aktiv = 1 WHERE us_benutzername = 'username';

Migration file commands

In version 6.5.6, new SQL comment based commands were added, so that it is possible to write different SQL statements for each database management system. Available statements are:

• --switch engine;

• --case ______;

• --end;

It is very important to put semicolons behind each statement. Else they will act as regular comments.

The following example should explain the whole concept:

[...]
--switch engine;
  --case sqlite;
    UPDATE `user` SET us_aktiv = 1 WHERE us_benutzername = 'username';
  --case mysql;
    UPDATE `user` SET us_aktiv = 2 WHERE us_benutzername = 'username';
--end;
[...]

(The indention is not necessary.)

Troubleshooting (working with comments)

At the moment, it is not use comments before a command statement. It will result in ignoring the statements. Example:

[...]
/* This will cause an error. */
--switch engine;
[...]
/* Appending a semicolon after the comment will fix it. */;
--switch engine;