(For an up-to-date version see http://nfs-scm.gb.local/nostromo/module-database-migration/blob/master/README.md)

Database migration module for Nostromo framework.

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.

Installation

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

Run composer require nostromo/module-database-migration ~6
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:

20190403_clear_api_logs.sql
20190404_added_indexes.sql
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.

Attention with branches

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:

20190403_clear_api_logs.sql
20190404_added_indexes.sql
~~20190405_merged_from_other_branch.sql~~
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.

Additional migration behavior

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.

Migration hook examples

InitApplication.php

Place the following code into your InitApplication.php to check/run migration updates whenever your website is visited.

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.

Update.php

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` (us_benutzername) VALUES ('username');
UPDATE `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;

Database Migration