...

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

...

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.

...

[...]
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;

...