cakephp / migrations

CakePHP database migrations plugin

Migrations plugin for CakePHP

This is a Database Migrations system for CakePHP.

The plugin consists of a CakePHP CLI wrapper for the Phinx migrations library.

This branch is for use with CakePHP 5.x. See version map for details.

Installation

You can install this plugin into your CakePHP application using Composer.

Run the following command

composer require cakephp/migrations

Configuration

You can load the plugin using the shell command:

bin/cake plugin load Migrations

Or you can manually add the loading statement in the src/Application.php file of your application:

public function bootstrap(): void
{
    parent::bootstrap();
    $this->addPlugin('Migrations');
}

Additionally, you will need to configure the default database configuration in your config/app.php file.

Documentation

Full documentation of the plugin can be found on the CakePHP Cookbook.

migrations's Issues

Missing ssl paramaters

When using ssl_ca parameters in default db configuration for cakephp, migrations doesn't pass those parameters to Phinx. This results in a error:

  There was a problem connecting to the database: SQLSTATE[28000] [1045] Access denied for user 'test_user'@'10.0.0.2' (using password: YES)

Example Test cakephp configuration:

       'default' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Mysql',
            'persistent' => false,
            'host' => '10.0.0.1',
            'username' => 'test_user',
            'password' => 'xxx',
            'database' => 'db_test',
            'ssl_ca' => '/var/www/test_project/certs/client_bundle_cert',
            'encoding' => 'utf8',
       ]

I was able to get to work by modifying ConfigurationTrait.php starting line 79 to this:

        $config = ConnectionManager::config($connection);
        $configArray = [
            'paths' => [
                'migrations' => $dir
            ],
            'environments' => [
                'default_migration_table' => $plugin . 'phinxlog',
                'default_database' => 'default',
                'default' => [
                    'adapter' => $this->getAdapterName($config['driver']),
                    'host' => isset($config['host']) ? $config['host'] : null,
                    'user' => isset($config['username']) ? $config['username'] : null,
                    'pass' => isset($config['password']) ? $config['password'] : null,
                    'port' => isset($config['port']) ? $config['port'] : null,
                    'name' => $config['database'],
                    'charset' => isset($config['encoding']) ? $config['encoding'] : null,
                ]
            ]
        ];
        if (isset($config['ssl_ca'])) {
                $configArray['environments']['default']['mysql_attr_ssl_ca'] = $config['ssl_ca'];
        }
        return $this->configuration = new Config($configArray);
    }

This not a complete solution, just a hack I am using. Would it be possible to add support to all ssl connections?

unsigned not a vaild column

"unsigned" is not a valid value for use with the table. You must use "signed" => false/true with phinx

Task class Template could not be found.

I am introducing the migrations plugin for the first time and hit the following exception.
Tracing the commit, you are changing the Templates and Tasks, I report the error. I hope this is the right place.

Environment: CentOS 6.5 over vagrant

===============
Welcome to CakePHP v3.0.0-beta3 Console
---------------------------------------------------------------
App : src
Path: /var/www/my_app/app/src/
---------------------------------------------------------------
Exception: Task class Template could not be found. in [/var/www/my_app/app/vendor/cakephp/cakephp/src/Console/TaskRegistry.php, line 66]
2014-12-21 02:04:50 Error: [Cake\Console\Exception\MissingTaskException] Task class Template could not be found.
Exception Attributes: array (
  'class' => 'Template',
  'plugin' => NULL,
)
Stack Trace:
#0 /var/www/my_app/app/vendor/cakephp/cakephp/src/Core/ObjectRegistry.php(84): Cake\Console\TaskRegistry->_throwMissingClassError('Template', NULL)
#1 /var/www/my_app/app/vendor/cakephp/cakephp/src/Console/Shell.php(423): Cake\Core\ObjectRegistry->load('Template', Array)
#2 /var/www/my_app/app/plugins/Migrations/src/Shell/Task/MigrationTask.php(122): Cake\Console\Shell->__get('Template')
#3 /var/www/my_app/app/plugins/Migrations/src/Shell/Task/MigrationTask.php(80): Migrations\Shell\Task\MigrationTask->bake('initial')
#4 [internal function]: Migrations\Shell\Task\MigrationTask->main('Initial')
#5 /var/www/my_app/app/vendor/cakephp/cakephp/src/Console/Shell.php(376): call_user_func_array(Array, Array)
#6 /var/www/my_app/app/vendor/cakephp/cakephp/src/Console/Shell.php(371): Cake\Console\Shell->runCommand(Array, false)
#7 /var/www/my_app/app/vendor/cakephp/cakephp/src/Console/ShellDispatcher.php(191): Cake\Console\Shell->runCommand(Array, true)
#8 /var/www/my_app/app/vendor/cakephp/cakephp/src/Console/ShellDispatcher.php(163): Cake\Console\ShellDispatcher->_dispatch()
#9 /var/www/my_app/app/vendor/cakephp/cakephp/src/Console/ShellDispatcher.php(117): Cake\Console\ShellDispatcher->dispatch()
#10 /var/www/my_app/app/bin/cake.php(20): Cake\Console\ShellDispatcher::run(Array)
#11 {main}

Migrations on more than one database

I'm trying to find a way to use migrations plugin to migrate more than one database, but i cannot find anything about here and in phinx documentation.

In my plugin i have one folder config/Migrations and works ok with a single default database. I'm also able to apply migrations on a different db using connection option. The problem is that i want to use two different migration subset for each database.

database1
- table A
- table B
- table C

database2
- table D
- table E

At this time the only way i found is to run two different migrations with two different connection, but this way i got:

database1
- table A
- table B
- table C
- table D
- table E

database2
- table A
- table B
- table C
- table D
- table E

so then i should run some script to remove tables in wrong db.. but this is a little tricky.. am i missing something? Or this is a limitation and always will be?

Exception when applying a baked migration snapshot

From @mamchenkov on March 23, 2015 10:29

Steps to reproduce

# Create a new CakePHP 3 project
composer create-project --prefer-dist cakephp/app app
cd app
mysql -u root -e 'create database cakephp_test'
# Update db details
vim config/app.php
# Create table with id column set to binary(36) for UUID use
mysql -u root cakephp_test -e 'create table foo( id binary(36) not null unique, created datetime, modified datetime, primary key (id))'
# Bake snapshot
./bin/cake bake migration_snapshot initial
# Drop table
mysql -u root cakephp_test -e 'drop table foo'
# Apply snapshot
./bin/cake migrations migrate

Expected result

Any baked migration should be usable. In this case, a table should be correctly created and no exception should be triggered.

Actual result

An exception is triggered, no table created:

$ ./bin/cake migrations migrate
Welcome to CakePHP v3.0.0 Console
---------------------------------------------------------------
App : src
Path: .../app/src/
---------------------------------------------------------------
using migration path .../app/config/Migrations
using environment default
using adapter mysql
using database cakephp_test

 == 20150323101149 Initial: migrating

  [PDOException]                                                                                                                
  SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'id' used in key specification without a key length 

migrate [-t|--target="..."] [-p|--plugin="..."] [-c|--connection="..."] [-s|--source="..."]

Additional Notes

• If I use char(36) as column type, everything works as expected.
• The idea to use binary instead of char came up after this StackOverflow thread

Copied from original issue: cakephp/cakephp#6147

Unexpected behavior after snapshot

I don't really know if it can be considered a bug or not, but I encountered an unexpected behavior while using migrations.

I took over a project that already had a database with tables inside it. Migrations were not used. When I took over I decided to start using migrations.
With the help of bake, I made a snapshot of the current state of the database.
I needed a new column in a table so I baked a migration. So far so good.
I then made the migration and that's where I stumbled upon the unexpected behavior : the call to bake migrations migrate triggered an error, saying that it could not create the tables since they already exist.

It was basically trying to migrate the snapshot.
I had to manually add the snapshot version to the phinxlog table for it to migrate only the changes needed (being the new column in a table).
After that everything went smoothly... As you see, not a bug per se. I hesitated reporting this, since it is rather normal that it does that.

Maybe the phinxlog table needs to be updated after a snapshot was baked ?

Missing date option from Phinx v. 0.4.5

Since an update today to Phinx v. 0.4.5, Phinx has added the date option to migrations.

Phinx\Console\Command\Migrate::configure():
->addOption('--date', '-d', InputOption::VALUE_REQUIRED, 'The date to migrate to')

The update makes migrations throw the InvalidArgumentException since the date option is not defined in the CakePHP wrapper.

With the line below added to the CakePHP wrapper, everythings working again.

Migrations\Command\Migrate::configure():
->addOption('--date', '-d', InputArgument::OPTIONAL, 'The date to migrate to')

InvalidArgumentException: The "template" option does not exist.

Hi guys,

I have a problem with the new cakephp 3 migrations plugin.

I am getting this error:
[InvalidArgumentException]
The "template" option does not exist.

After executing this in the project root dir on command line:
bin/cake migrations create Initial

Mulitple Simultaneous Database Connections are Not Supported?

I am working on an existing CakePHP 3.0 application which necessarily uses multiple databases.

We have had some problematic rollouts and I want to introduce migrations to improve our recovery speed with db rollbacks, so I need to write migrations that implicitly target the correct datasource connection.

The option seems to have been considered with 'bake migrate [...] -c' but the resulting migration file contains no reference to the datasource.

You can specify a connection in the Migrations shell with the -c option too, which does target the correct datasource, but having to supply a target per migration and move one migration at a time defeats the object, somewhat...

I could keep db specific migrations separate directories and run migrate on those files/connection, but this still makes rolling back to a specific point in time quite cumbersome.

Is this a bug/issue, as it seems, or am I missing something?

Decouple ConfigurationTrait from the shell?

I'm trying to re-use the migrations plugin inside an installer event so that the user can activate a plugin through a web interface and the app will run the migrations after that.

When I started I figured out that I can't really re-use the ConfigurationTrait because it depends on $this->input being available. But this will effectively couple the trait to a shell.

Latest release tags are missing bug fix for migration_snapshot

PR #89 fixes a bug that was causing migration_snapshot option to not work when invoked from bake when you also supplied --connection and --plugin.

Anyway, if you use the newest release tag for migrations, that bug fix isn't backported, so migration_snapshot fails when using the --connection and --plugin options.

If you use master the bug is fixed, but it generates a boilerplate migration snapshot file without any data (I assume because master needs some of the improvements in the release tag commits?).

Can the release tags be updated to include that bug fix?

Undefined class constant CREATION_INTERFACE

On CakePHP 3.0.1 over PHP 5.6.7, I encountered an unexpected error while using migrations:

% bin/cake migrations migrate

Welcome to CakePHP v3.0.1 Console
App : src
Path: /usr/home/cms/src/

PHP Fatal error: Undefined class constant 'CREATION_INTERFACE' in /usr/home/cms/vendor/cakephp/migrations/src/Command/Create.php on line 45

NOTE: CREATION_INTERFACE is missing

cake bake all fails on table phinxlogs

Hi,

when using this plugin bin/cake bake view all --force does not work anymore.

It seems that temporary tables seem to be the problem:

Exception: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'myschema.phinxlogs' doesn't exist in [.../app/vendor/cakephp/cakephp/src/Database/Schema/Collection.php, line 151]

Migration Seed for Postgres driver was get PDO Error (There is no active transaction for seed data)

I'm trying to create seed data from CakePHP 3 migration file, with Postgres as database driver. But i have got error message under console after run:

bin/cake migrations migrate

Here are my code:

<?php
use Phinx\Migration\AbstractMigration;
use Cake\ORM\TableRegistry;
use Cake\Utility\Security;

class CreateSeedUsers extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $modelName = 'Users';

        $data = [
            [
                'username' => 'admin', 
                'email' => '[email protected]', 
                'password' => Security::hash('123', 'sha1', true)
            ], 
            [
                'username' => 'seller', 
                'email' => '[email protected]', 
                'password' => Security::hash('123', 'sha1', true)
            ], 
            [
                'username' => 'member', 
                'email' => '[email protected]', 
                'password' => Security::hash('123', 'sha1', true)
            ]
        ];

        $table = TableRegistry::get($modelName);
        $entities = $table->newEntities($data);

        // In a controller.
        foreach ($entities as $entity) {
            // Save entity
            $table->save($entity);
        }   
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        if ($this->query("TRUNCATE users")) {
            echo " > Table has been initialized\n\n";
        } 
        else 
        {
            echo " > Warning, table failed to initialized!!!\n\n";
        }
    }
}

If i use mysql as database driver, its working good.

Someone please help me. Thank you.

PHPCS runs against non-plugin code

We should ignore dependencies etc, and it seems that the PHPCS is being run against stuff like bake...

bake migration_snapshot not working with --connection option anymore?

I built out migration snapshots for all of our plugins for an application were building a few weeks ago no problem. Recently we refactored one of them so much that I'm trying to build a new initial migration for it based on a snapshot.

The following command is no longer working. Example uses a plugin called 'Inventory' with connection 'inventory':

# running this from parent application
bin/cake bake migration_snapshot TestingMigration -p Inventory -c inventory

I get the following:

  [InvalidArgumentException]                                                                            
  There was a problem connecting to the database: SQLSTATE[28000] [1045] Access denied for user 'my_ap  
  p'@'localhost' (using password: YES)     

So it's attempting to use the default connection. The connection I'm trying to use does however exist, so it shouldn't be falling back.

I also noticed that it is able to connect, by enabling the mysql general_log I can see:

150528 14:26:24 526861 Connect  root@localhost on inventory
526861 Query    SET time_zone = '+0:00'
526861 Query    SET NAMES utf8
526861 Query    SHOW TABLES FROM `inventory`
526862 Connect  my_app@localhost on my_app
526862 Connect  Access denied for user 'my_app'@'localhost' (using password: YES)
526861 Quit 

So it seems that it's able to use the specified connection to run SHOW TABLES, but then it immediately goes back to the default connection after that and fails.

Am I doing something wrong here or has a bug been introduced recently?

When installing via composer, plugin location can not be found

PHP 5.5

When I install the plugin via composer I get the following error:

# cli
Exception: Plugin Migrations could not be found. 
in [/home/foo/cakephp-app/vendor/cakephp/cakephp/src/Core/Plugin.php, line 150]

# web
Make sure your plugin Migrations is 
in the /home/foo/cakephp-app/plugins/ directory and was loaded. 

I've tried declaring the namespace and path in composer.json e.g.

"autoload": {
        "psr-4": {
            "App\\": "src",
            "Migrations\\": "./vendor/cakephp/migrations/src"
        }
    },

...and dumping the autoload again, I've also tried defining the path option e.g.

# bootstrap.php
Plugin::load('Migrations', [
    "path" => ROOT . DS . 'vendor' . DS . 'cakephp' . DS . "migrations"
]);

Neither fix the issue. Any help would be much appreciated!

Shell Options

When running bin/cake migrations a list of options is printed like --no-ansi that don't seem to work. Is this a problem with Cakes Shell or with the MigrationsShell?

Either way, it is quiet confusion to get an error saying that the option "no-ansi" is unknown and another list of options is presented

I noticed that the 3.x migrations uses sphinx. May I ask the rationale behind choosing sphinx?

I enjoyed using the migrations for Cake 2.x

Sorry to see that the migrations for Cake 3.x is still at pre alpha.

Was surfing this when I realized the two outstanding issues have to do with sphinx. May I ask the rationale behind choosing sphinx?

Migrate fails with environment variables

I haven't looked into why this happens but it happened to me with a fresh install of cake.

// config.app.php
return [
    'default' => [
        // ...
        'username' => env('DB_USER') ?: 'root',
        'password' => env('DB_PASS') ?: 'password',
        'database' => env('DB_NAME') ?: 'db_name',
        // ...
    ]
];

# Apach Virtual Host config
SetEnv CAKE_DEBUG 0
SetEnv DB_USER new_username
SetEnv DB_PASS new_password
SetEnv DB_NAME internal

root@tssi:/srv/internal# bin/cake migrations migrate -c ipmanager -p IpManager

Welcome to CakePHP v3.0.10 Console
---------------------------------------------------------------
App : src
Path: /srv/internal/src/
---------------------------------------------------------------
using migration path /srv/internal/vendor/sdustinh/ipmanager/config/Migrations



  [InvalidArgumentException]
  There was a problem connecting to the database: SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: YES)



migrate [-t|--target TARGET] [-p|--plugin PLUGIN] [-c|--connection CONNECTION] [-s|--source SOURCE]

Note 'root'@'localhost' above instead of 'new_username'@'localhost',

The env variables are used correctly when the application connects to the database. This problem seems to be limited to the Migrations plugin. I can see the result of the environment variables in phpinfo() and getenv(). This might be an issue with the actual env() function but I was only able to get it to fail when using the Migrations plugin.

Update robmorgan/phinx

I tried to update the phinx versionof this plugin with composer but it stays at 0.3.5 (current version is 0.4.1). I found the version in the MigrationsShell:49
(define('PHINX_VERSION', (0 === strpos('@PHINX_VERSION@', '@PHINX_VERSION')) ? '0.3.5' : '@PHINX_VERSION@');)

Is there a easy way to update the plugin?

The datasource configuration "default" was not found.

I'm building multisite application with cakephp 3. I won't use default connection because each of the site will connect to it's own version of database. The database structure will be identical for each site. I want to use cakephp migration to automate create and alter processes.

I've created my own shell which executes 'bin/cake migrations migrate -c connection_name' command on each connection it finds.

Unfortunately, I receive the following error:
[Cake\Datasource\Exception\MissingDatasourceConfigException]
The datasource configuration "default" was not found.

It is caused by 'bootstrap' method in 'src/Configuration trait' at line 169.

Here is how I've solved it:

 public function bootstrap(InputInterface $input, OutputInterface $output)
    {
        parent::bootstrap($input, $output);

        $connectionStr = 'default';
        if ($this->input->getOption('connection')) {
            $connectionStr = $this->input->getOption('connection');
        }

        $connection = $this->getManager()->getEnvironment('default')->getAdapter()->getConnection();
        ConnectionManager::get($connectionStr)->driver()->connection($connection);
    }

I'm not sure if this is preferable solution.

Format option implementation

Phinx has an option to output the result of status command in json: format.
This option isn't recognized by the plugin:

...>bin\cake migrations help status


Welcome to CakePHP v3.0.2 Console
---------------------------------------------------------------
App : src
Path: ...\src\
---------------------------------------------------------------
Usage:
 status [-f|--format="..."] [-p|--plugin="..."] [-c|--connection="..."] [-s|--source="..."]

Options:
 --format (-f)         The output format: text or json. Defaults to text.
 --plugin (-p)         The plugin containing the migrations
 --connection (-c)     The datasource connection to use
 --source (-s)         The folder where migrations are in
 --help (-h)           Display this help message
 --quiet (-q)          Do not output any message
 --verbose (-v|vv|vvv) Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
 --version (-V)        Display this application version
 --ansi                Force ANSI output
 --no-ansi             Disable ANSI output
 --no-interaction (-n) Do not ask any interactive question

Help:
 prints a list of all migrations, along with their current status

Here the format option is present but when I type

...>bin\cake migrations status --format="json"

Error: Unknown option `format`
Usage:
cake migrations.migrations [options]

Options:

--help, -h            Display this help.
--verbose, -v         Enable verbose output.
--quiet, -q           Enable quiet output.
--plugin, -p
--target, -t
--connection, -c
--source, -s
--ansi
--no-ansi
--version, -V
--no-interaction, -n
--template, -t

Migration looking in the wrong folder for namespaced plugin.

I've tried to run migrations for my FileStorage plugin and got this message:

using migration path C:\xampp\htdocs\cake3/vendor/burzum/file-storage/config\Migrations

The plugin is in fact in /plugins/Burzum/FileStorage/ and loaded fine from there from the application itself.

how can Create an Initial Migration from an Existing Database?

i need to Create an Initial Migration from an Existing Database. Is it possible now?

Errors when trying to run (plugin) migration

Please see this screenshot:

https://www.dropbox.com/s/7dop4q5dbur2eo0/Screenshot%202014-10-15%2021.49.13.png?dl=0

First I run the migrations and the DB table it uses for the schema doesn't exist. It shows a SQL error but the table is created any way. Edit: This has been fixed by https://github.com/burzum/migrations-1/commit/ba462266496374a9b42ff7789ce61e4669c181fe

The PR fixes the first issue but I'm still getting this:

←[37;41m                                                                           ←[39;49m
←[37;41m  [PDOException]                                                           ←[39;49m
←[37;41m  SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'  ←[39;49m
←[37;41m                                                                           ←[39;49m

The table doesn't exist yet, nor does the column.

Here is my migration file:

<?php
use Phinx\Migration\AbstractMigration;

class InitialMigration extends AbstractMigration {

/**
 * Migrate Up.
 */
    public function up() {
        $this->table('statuses')
            ->addColumn('id', 'char', ['limit' => 36])
            ->addColumn('name', 'char', ['limit' => 255])
            ->addColumn('model', 'string', ['limit' => 128])
            ->create();
    }

/**
 * Migrate Down.
 */
    public function down() {
        $this->dropTable('statuses');
    }
}

create migrations updates from database like when init is used

This is a question / feature request.

Normally when I change my database tables, I do it in MySQL Workbench database schema, and than synchronize it to my actual database.

It would be nice to have a migration command what creates the migration, based on the used ALTER / CREATE / DROP statements, or by a database sync.

Migrations Events

Just idea.
How about to use before and after events in migrate and rollback actions.
For example:
After migration process i need to drop orm cahe. But sometimes other coders or me can forget to do this.
I think in this case event can drop cache after each migration process.

Running all migrations

Hey guys,

I have a plugin wich has dependencies, so when you install that plugin, you have to run multiple migrations (for every plugin once).
Is there a way to create a command wich will run every migration, so of the app, and all plugins?

Greetz

Feature or question: Running migrations via php (not a shell)?

Is it already possible to simply run existing migrations via php?

I'm working on an installer like shell and would like to run migrations from there, I have something like

$migrations->migrate();
$migrations->migrate('SomePlugin');

in mind. Without the need to lookup and load the migration files manually.

Collations in database

Hi!

Maybe it's just me, but I can't figure how to explicitly set a collation when creating tables or adding columns. Is it just choosing the database default?

Accoring to the phinx documentation at http://docs.phinx.org/en/latest/configuration.html?highlight=collation I should be able to set it in the configuration, but I don't see it in the vendor/cakephp/migrations/src/ConfigurationTrait.php.

Regards, Mikkel

CHAR type is not available with Phinx

Phinx is not able to create a CHAR(36) type column.

For example this migration creates a primary key as VARCHAR(36) type field, this is not supported as UUID key :

$terms = $this->table('terms', array('id' => false, 'primary_key' => array('id')));
$terms->addColumn('id', 'string', array('limit' => 36))
->save();

I have to manually change it.

Phinx doc - valid-column-types

Foreign keys in baked migrations

When baking a migration for a table containing foreign keys, the foreign keys don't end up in the migrations file. Seems like I'll have to add them manually for now, this would be a really good feature to have in bake.

Impossible to use with Sqlite adapter when not in memory

It seems to be impossible to run migrations on a Sqlite database not ran in memory because Phinx always adds a suffix to the database path, and Cake doesn't.

Also in this case there are a few undefined indexes

To reproduce

Create a datasource:

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Sqlite',
        'database' => ROOT . DS . 'project_management',
    ]
]

Generate a migration like the following one:

 public function change()
    {
        $projectsTable = $this->table('projects');
        $projectsTable->addColumn('name', 'string')
            ->addColumn('created', 'datetime')
            ->create();
    }

What happens

• There are a few undefined indexes such as "login", "host", "password" when running the shell, due to unchecked indexes in

  migrations/src/ConfigurationTrait.php

  Line 85 in f607c9e

  'user' => $config['login'],

  (in the case of Sqlite they are not needed for the datasource)
• Phinx applies migrations to an incorrect database file. In the example, it will write it to ROOT . DS . 'project_management.sqlite3' (because of https://github.com/robmorgan/phinx/blob/b71ccb15a32656067508b23aef1dfb726fff4ce3/src/Phinx/Db/Adapter/SQLiteAdapter.php#L72)

Expected

• No undefined indexes or notices displayed when running the shell
• Migrations must be applied to the file used by the CakePHP app

Table not generated with Sqlite and an id:uuid

It seems that the table is not created with an id uuid and Sqlite.
for example:

// after a composer create-project
// app.php
// [...]
 'default' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Sqlite',
            'persistent' => false,
            'database' => 'my_app.sqlite',
            'encoding' => 'utf8',
            'timezone' => 'UTC',
            'cacheMetadata' => true,
            'quoteIdentifiers' => false,
        ],

bin\cake bake migration CreateUsers id:uuid email:string username:string password:string created modified
bin\cake migrations migrate

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id'

I have a mysql 5.6 database with some bigint fields (also for storing internal ids). when baking my default schema (with php cake.php bake migration Initial -c default)

the following code is created:

    $table = $this->table('groups');
    $table
      ->addColumn('id', 'biginteger', [
        'limit' => '20', 
        'null' => '', 
        'default' => '', 
      ])
      ...

when running the migration with php cake.php migrations migrate the migration throws an error:

 SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'id' 

bake is trying to create a "id" field on his own, although phinx has already created the primary "id" key on it's own.

so i guess, bake should therefore call $this->table('groups', ['id' => false])

id automatically created forcing column already exists.

As you can tell I have been playing with the Migrations plugin today. Phinx by default creates an id as the primary unless overridden in the table function.
For example: $this->table('users', ['id' => false, 'primary_key' => ['user_id']]);

The current cake bake migration command will make an id column in the migration file causing an 'Column already exists' when using the migration file because the id is being defined twice in the process.

Baked snapshot don't include constraints

When generating a snapshot migration constraints are dropped. Given the following schema, I would have expected that the generated snapshot would have constraints:

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `description` text,
  `category_id` int(11) NOT NULL,
  `thumbnail` varchar(255) DEFAULT NULL,
  `large_image` varchar(255) DEFAULT NULL,
  `featured` tinyint(1) NOT NULL DEFAULT '0',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
);
CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Exception on seed data in table with composite PK

After run "migrations migrate" from shell:

[RuntimeException]                                                                                       
  Cannot insert row, some of the primary key values are missing. Got (, ), expecting (article_id, tag_id)

My migration:

use Cake\ORM\TableRegistry;
use Phinx\Migration\AbstractMigration;

class InitialMigration extends AbstractMigration
{
    public function change()
    {
         $this->table('articles_tags', ['id' => false, 'primary_key' => ['article_id', 'tag_id']])
            ->addColumn('article_id', 'integer')
            ->addForeignKey('article_id', 'articles', 'id')
            ->addColumn('tag_id', 'integer')
            ->addForeignKey('tag_id', 'tags', 'id')
            ->save();
         $articlesTags = TableRegistry::get('ArticlesTags');
         $articleTag = [
                'article_id' => 1,
                'tag_id' => 1,
     ];
         $articlesTags->save($articlesTags->newEntity($articleTag));
   }
}

NOTE: Articles/Tags has a record with id 1.

Required robmorgan/phinx cannot be loaded

When running a fresh install of CakePHP3 I get the following error.

[RuntimeException]
Could not load package cakephp/migrations in http://packagist.org: [UnexpectedValueException] Could not parse version constraint >=0.4.2 <1.0: Invalid version string "0.4.2 <1.0"

cakephp/migrations requires robmorgan/phinx, but the version string is incorrect (well, at least thats what the error says).

I am running the following command to install CakePHP: composer create-project --prefer-dist cakephp/app portfolio

Screenshot of the error:

Cake3 shell migration throws "Call to a member function getOption() on null"

I am running Cake 3.0.11 with Migrations plugin 2.0 and am trying to run a migration from within a Shell as described here in the book.

I hoped it would "just work" but it throws the following error.

PHP Fatal error:  Call to a member function getOption() on null in /home/vagrant/projects/api.ecloud.app/vendor/cakephp/migrations/src/ConfigurationTrait.php on line 60
PHP Stack trace:
PHP   1. {main}() /home/vagrant/projects/api.ecloud.app/bin/cake.php:0
PHP   2. Cake\Console\ShellDispatcher::run() /home/vagrant/projects/api.ecloud.app/bin/cake.php:33
PHP   3. Cake\Console\ShellDispatcher->dispatch() /home/vagrant/projects/api.ecloud.app/vendor/cakephp/cakephp/src/Console/ShellDispatcher.php:126
PHP   4. Cake\Console\ShellDispatcher->_dispatch() /home/vagrant/projects/api.ecloud.app/vendor/cakephp/cakephp/src/Console/ShellDispatcher.php:175
PHP   5. Cake\Console\Shell->runCommand() /home/vagrant/projects/api.ecloud.app/vendor/cakephp/cakephp/src/Console/ShellDispatcher.php:204
PHP   6. call_user_func_array:{/home/vagrant/projects/api.ecloud.app/vendor/cakephp/cakephp/src/Console/Shell.php:380}() /home/vagrant/projects/api.ecloud.app/vendor/cakephp/cakephp/src/Console/Shell.php:380
PHP   7. App\Shell\GenerateShell->all() /home/vagrant/projects/api.ecloud.app/vendor/cakephp/cakephp/src/Console/Shell.php:380
PHP   8. Migrations\Migrations->status() /home/vagrant/projects/api.ecloud.app/src/Shell/generateShell.php:77
PHP   9. Migrations\Migrations->run() /home/vagrant/projects/api.ecloud.app/vendor/cakephp/migrations/src/Migrations.php:84
PHP  10. Migrations\Migrations->getConfig() /home/vagrant/projects/api.ecloud.app/vendor/cakephp/migrations/src/Migrations.php:174

The code for my Shell below (search for run phinx migration):

<?php
namespace App\Shell;

use Cake\Console\ConsoleOptionParser;
use Cake\Console\Shell;
use Cake\Core\Configure;
use Cake\Core\Exception\Exception;
use Cake\Datasource\ConnectionManager;
use Cake\Log\Log;
use Cake\Utility\Hash;
use Migrations\Migrations;

/**
 * Shell class used to generate files.
 */
class GenerateShell extends Shell
{

    protected $systemTables = [
        'phinxlog'
    ];

    /**
     * Define available subcommands, arguments and options.
     *
     * @return parser
     */
    public function getOptionParser()
    {
        $parser = parent::getOptionParser();
        $parser->description([__('Generate files required for html /browse access.')]);
        $parser->addSubcommand('all', [
            'parser' => [
                'description' => [
                __("Generates Models, Views and (after removing existing).")
                ],
                'options' => [
                    'force' => [
                        'short' => 'f',
                        'help' => __('Required because this will wipe all existing changes.'),
                        'boolean' => true
                    ]
                ]
            ]
        ]);
        return $parser;
    }

    /**
     * Generates a fresh application set with new database migration and
     * and new View, Model and Controller files (for the Browse namespace).
     *
     * @return void
     */
    public function all()
    {
        // check conditions
        if (!Configure::read('debug')) {
            $this->error('This command only works in debug mode');
        }

        if (!$this->params['force']) {
            $this->error('This command requires --force. If you have made changes, save them now. All files will be regenerated.');
        }

        // Remove database tables
        $tables = array_merge($this->_getDatabaseTables());
        if (!$tables) {
            $this->out("Skipping table deletion... database contains no tables");
        } else {
            $this->out("Starting table deletion...");
            $this->_removeDatabaseTables($tables);
        }

        // run phinx migration
        $migrations = new Migrations();
        pr($migrations);
        $status = $migrations->status();
    }

    private function _removeDatabaseTables(array $tables)
    {
        $conn = ConnectionManager::get('default');
        foreach($tables as $table) {
            $stmt = $conn->execute("DROP TABLE $table");
            $this->out("Dropped database table $table");
        }
    }

    /**
     * Returns a list of all application database tables, including ones that
     * will break cake baking models (e.g. phinxlog).
     *
     * @return array List holding database table names
     * @throws \Exception
     */
    protected function _getDatabaseTables()
    {
        try {
            $conn = ConnectionManager::get('default');
            $stmt = $conn->execute('SHOW TABLES');
            $rows = Hash::extract($stmt->fetchall(), '{n}.{n}');
            return $rows;
        } catch (\Exception $e) {
            throw new \Exception("Error generating database list: " . $e->getMessage());
        }
        return $result;
    }
}

Thanks in advance for looking into this.

Running migrations from code?

Is there a way to run migrations from within my applications code? The use case is a plugin system that allows me to install / deinstall plugins and to trigger their migrations.

What I would like to do is something like this (Pseudocode!):

$migrations = new Migrations();
try {
    return $migrations->migrate('MyFancyPlugin');
} catch (Exception $e) {
    return false;
}

It would be nice if the migration would return a meaningful state. I think this problem was discussed before as well. The underlying migration lib by default was IMHO not properly done because it was built tightly coupled to a shell application. So the result you get back is some console output you would have to parse.

The ideal scenario would be to get a simple boolean back but have the ability to call something like getErrors() in the case of a failure to see the exact problem.

Is this already possible? If yes, it should be documented.

Issue with marking all as migrated

If you try to run Migrations mark_migrated 123 it will tell you that this is not a valid version.
But if you do Migrations mark_migrated * with the wildcard operator it will say

Migration successfully marked migrated !

But will just add 0 as version in the DB. Doing it again results in

An error occurred : SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'PRIMARY'

Now, while I think * was never really supported, I think the intuitive thing here would actually to support marking all as migrated.

Foreign keys referencing to non-existing tables when baking snapshot

When baking a snapshot, foreign keys are added correctly now. But there's a problem when migrating to the snapshot. Tables are added in alphabetical order. So in a lot of cases it'll add a foreign key for a not yet existing table, which leads to database errors:

  SQLSTATE[HY000]: General error: 1005 Can't create table 'foo.bar' (errno: 150)

Example of the way it's created:

$this->table('bar')
    ->addColumn('bar_id', 'integer')
    ->addForeignKey('foo_id', 'foo', 'id')
    ->create();

$table = $this->table('foo')
    ->create();

Possible error in Usage readme instructions

At Usage section, we have instruction to execute in console:

bin/cake migrations

But, at least for me (in different environments), the correct command is:

bin/cake Migrations.migrations

When I run the first one, the output is:

Exception: Shell class for "Migrations" could not be found. in [/vagrant/my_project/vendor/cakephp/cakephp/src/Console/ShellDispatcher.php, line 254]

Must use create or update when using change()

The current template (migration.ctp) is using a save() call. Which makes reversing not possible. Phinx documentation states:

When creating or updating tables inside a change() method you must use the Table create() and update() methods. Phinx cannot automatically determine whether a save() call is creating a new table or modifying an existing one.

Adding an index produces strange result

Steps to Reproduce:

Create a table with bake, then try to add an index with bake:

bin/cake bake migration -p Devices -c devices AddStatusIdIndexToDevices status_id:integer:index:status_id

Results in generated code:

<?php
use Phinx\Migration\AbstractMigration;

class AddStatusIdIndexToDevices extends AbstractMigration
{
    /**
     * Change Method.
     *
     * More information on this method is available here:
     * http://docs.phinx.org/en/latest/migrations.html#the-change-method
     * @return void
     */
    public function change()
    {
        $table = $this->table('devices');
        $table->addColumn('status_id', 'integer', [
            'default' => null,
            'limit' => 11,
            'null' => false,
        ]);
        $table->addIndex(["
        'status_id',
    "], [
            'name' => 'status_id',
            'unique' => false,
        ]);
        $table->update();
    }
}

Notice the first argument to addIndex() is ["'status_id',"] . What am I missing here? Is my syntax incorrect?

$ grep migrations composer.json 
        "cakephp/migrations": "~1.0",

Is this a problem with the migrations plugin or does it look like an issue with Phinx?

Configurable migrations

When using plugins there is sometimes the problem that you want to use UUIDs but the plugin has integer type ids and vice versa.

By looking at the migrations they extend the AbstractMigration class, we could add a CakeMigration class from which all migrations extend instead from AbstractMigration directly. That implements an initialize() method where settings from Configure could be read and used for something and other things could be done as well there.

'unsigned' is not a valid column option.

I have a mysql 5.6 database with some bigint fields (for storing facebook/google+ ids). when baking my default schema (with php cake.php bake migration Initial -c default)

the following code is created:

    $table = $this->table('groups');
    $table
      ->addColumn('id', 'biginteger', [
        'limit' => '20', 
        'unsigned' => '', 
        'null' => '', 
        'default' => '', 
      ])
      ...

when running the migration with php cake.php migrations migrate the migration throws an error:

[RuntimeException]                        
  'unsigned' is not a valid column option.

according to http://docs.phinx.org/en/latest/migrations.html#valid-column-options, "unsigned" is definitely not an valid column option :)

Data seeding shell

Just another idea.
How about to implement data seeding shell for this plugin.
Data can be stored in /config/Data in app or plugin folder.
I think all data must be in actual state for last migration.
Data seeding process cna't start if migration is not complete.
What do you think?