bizley/yii2-migration

Yii 2 Migration Creator And Updater

Yii 2 Migration

Migration Creator And Updater

In a perfect world you prepare migration files for your database first so you can run it when ready. But since this is not a perfect world, sometimes you start with a database already set - with this package you can easily create a migration file based on your DB schema with one console command.

Furthermore, when your DB is updated later on you can generate a migration file updating the schema to the current state. The package is comparing it with the migration history:

1. History of applied migrations is scanned to gather all modifications made to the table.
2. Virtual table schema is prepared and compared with the current table schema.
3. Differences are generated as an update migration.
4. In case of the migration history not keeping information about the table, a creating migration is generated.

Installation

Run console command

composer require --dev bizley/migration

Or add the package to your composer.json file:

{
    "require-dev": {
        "bizley/migration": "^4.0"
    }
}

then run composer update.

Configuration

Add the following in your configuration file (preferably console configuration file):

[
    'controllerMap' => [
        'migration' => [
            'class' => 'bizley\migration\controllers\MigrationController',
        ],
    ],
]

Additional options are available as following (with their default values):

[
    'controllerMap' => [
        'migration' => [
            'class' => 'bizley\migration\controllers\MigrationController',
            'migrationPath' => '@app/migrations', // Directory storing the migration classes
            'migrationNamespace' => null, // Full migration namespace
            'useTablePrefix' => true, // Whether the table names generated should consider the $tablePrefix setting of the DB connection
            'onlyShow' => false, // Whether to only display changes instead of generating update migration
            'fixHistory' => false, // Whether to add generated migration to migration history
            'skipMigrations' => [], // List of migrations from the history table that should be skipped during the update process
            'excludeTables' => [], // List of database tables that should be skipped for actions with "*"
            'fileMode' => null, // Permission to be set for newly generated migration files
            'fileOwnership' => null, // User and/or group ownership to be set for newly generated migration files
            'leeway' => 0, // Leeway in seconds to apply to a starting timestamp when generating migration
        ],
    ],
]

Usage

The following console command are available (assuming you named the controller migration like in the example above):

• List all the tables in the database:

  php yii migration
  

  or

  php yii migration/list
  

• Generate migration to create DB table table_name:

  php yii migration/create "table_name"
  

• Generate migration to update DB table table_name:

  php yii migration/update "table_name"
  

  To generate migrations for all the tables in the database at once (except the excluded ones) use asterisk (*):

  php yii migration/create "*"
  php yii migration/update "*"
  

  You can generate multiple migrations for many tables at once by separating the names with comma:

  php yii migration/create "table_name1,table_name2,table_name3"
  

  You can provide an asterisk as a part of table name to use all tables matching the pattern:

  php yii migration/update "prefix_*"
  

  Creating multiple table migrations at once forces the proper migration order based on the presence of the foreign keys. When tables are cross-referenced the additional foreign keys migration is generated at the end of default generation.

• Extract SQL statements of migration migration_name (UP) New in 4.4.0:

  php yii migration/sql "migration_name"
  

• Extract SQL statements of migration migration_name (DOWN) New in 4.4.0:

  php yii migration/sql "migration_name" "down"
  

Command line parameters

command	alias	description
migrationPath	mp	Directory (one or more) storing the migration classes.
migrationNamespace	mn	Namespace (one or more) in case of generating a namespaced migration.
useTablePrefix	tp	Whether the generated table names should consider the tablePrefix setting of the DB connection.
migrationTable	mt	Name of the table for keeping the applied migration information.
onlyShow	os	Whether to only display changes instead of generating an update migration.
generalSchema	gs	Whether to use the general column schema instead of the database specific one (see [1] below).
fixHistory	fh	Whether to add a migration history entry when the migration is generated.
skipMigrations		List of migrations from the history table that should be skipped during the update process (see [2] below).
excludeTables		List of tables that should be skipped.
experimental	ex	Whether to run in the experimental mode (see [3] below).
fileMode	fm	Generated file mode to be changed using chmod.
fileOwnership	fo	Generated file ownership to be changed using chown/chgrp.
leeway	lw	The leeway in seconds to apply to a starting timestamp when generating migration, so it can be saved with a later date

[1] Remember that with different database types, general column schemas may be generated with different length.

MySQL examples:

Column varchar(255)
generalSchema=false: $this->string(255)
generalSchema=true: $this->string()

Column int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
generalSchema=false: $this->integer(11)->notNull()->append('AUTO_INCREMENT PRIMARY KEY')
generalSchema=true: $this->primaryKey()

When column size is different from DBMS' default, it's kept:
Column varchar(45)
generalSchema=false: $this->string(45)
generalSchema=true: $this->string(45)

[2] Here you can place the migrations containing actions that cannot be covered by the extractor, i.e. when there is a migration setting the RBAC hierarchy with the authManager component. Such actions should be kept in a separated migration and placed on this list to prevent them from being run during the extraction process.

[3] This mode allows using a raw SQL column definition for the migration updater (i.e. ['column' => 'varchar(255)'] instead of ['column' => $this->string()]). Since the generating process in this mode depends on the individual DBMS syntax the results might not be correct. All help improving this mode is more than welcome.

Important information about RENAMING tables or columns

When you rename a table or a column, remember to generate appropriate migration manually, otherwise this extension will not generate an updating migration (in case of the table) or will generate a migration with the command to drop the original column and add a renamed one (in case of the column). This is happening because yii2-migration can only compare two states of the table without the knowledge of how one state turned into another. While the very result of the migration renaming the column and the one dropping it and adding another is the same in terms of structure, the latter makes you lose data.

Once you add a renaming migration to the history, it's being tracked by the extension.

Migrating from v2 or v3 to v4

See Migrating to version 4.0 section.

Notes

This extension should work with all database types supported in Yii 2 core:

• CUBRID (9.3.x and higher)
• MS SQL Server (2008 and above)
• MySQL (4.1.x, 5.x, 8.x)
• Oracle
• PostgreSQL (9.x and above)
• SQLite (2/3)

Only history of the migrations extending yii\db\Migration class can be properly scanned, and only changes applied with default yii\db\Migration methods can be recognised (except for execute(), addCommentOnTable(), and dropCommentFromTable() methods). Changes made to the table's data (like insert(), upsert(), delete(), truncate(), etc.) are not tracked.

Updating migrations process requires for the methods createTable(), addColumn(), and alterColumn() to provide changes in columns definition in the form of an instance of yii\db\ColumnSchemaBuilder (like $this->string() instead of 'varchar(255)').

The new 4.4.0 feature with extracting SQL statements from the existing migration supports all methods available in yii\db\Migration.

Tests

Tests for MySQL, PostgreSQL, and SQLite are provided. Database configuration is stored in tests/config.php (you can override it by creating config.local.php file there).
Docker Compose file for setting up the databases is stored in tests/docker.

Previous versions

These versions are not developed anymore but still available for all poor souls that are stuck with EOL PHP. Some of the newest features may not be available there.

version constraint	PHP requirements	Yii requirements
^3.6	>= 7.1	>= 2.0.15.1
^2.9	< 7.1	2.0.13 to track non-unique indexes, 2.0.14 to handle TINYINT and JSON type columns.

yii2-migration's Issues

Multiple DBs

Hi, I am trying to use your extension but, when I run migrategen or migrategen/list it throws an error because I don't have a db config since I have many (db_a, db_b,... db_x).
If I try to specify --db=db_x I see: Error: Unknown option: --db.

So, how can I specify the db config I want to use when listing, creating, etc?

Thank you.

--edit
I know I can specify that in the configs for your extension, but, is there anyway to specify for each command? Otherwise it would be unusable to me (or to anyone having multiple dbs). Thank you again.

removePKAppend removing too much

TableColumn::removePKAppend() should return original append and not set it to null when there is no primary key info there.

PHP Fatal error:

Hi,

Thank you for great extension. But it gives me error...find below, would appreciate any workarounds, thanks

Generating create migration for table 'actions' ...PHP Fatal error: Cannot use yii\base\Object as Object because 'Object' is a special class name i n C:\xampp\htdocs\bialab\vendor\bizley\migration\table\TableStructure.php on line 6

Size for columns is not respected

Similar to #29
Version: 3.5.0

Table schema:

`updated_by` int(10) unsigned DEFAULT NULL,
`subject` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,

Generated migration is:

'updated_by' => $this->integer()->unsigned(),
'subject' => $this->string()->notNull(),

should be:

'updated_by' => $this->integer(10)->unsigned(),
'subject' => $this->string(128)->notNull(),

Yii2 provide this data in:
Yii::$app->db->getSchema()->tableSchemas[15]->getColumn('created_at')->size

Can you support this?

CURRENT_TIMESTAMP issue

the timestamp row with default value CURRENT_TIMESTAMP generated incorrect, migration script also not identify for example timestamp(3) value (timestamp with microseconds is available since mysql 5.6.4 afaik)

'timestamp' => $this->timestamp()->notNull()->defaultValue('CURRENT_TIMESTAMP(3)'),

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'timestamp'
The SQL being executed was: CREATE TABLE `test` (
	`timestamp` timestamp(0) NOT NULL DEFAULT 'CURRENT_TIMESTAMP(3)'
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB'

must be

'timestamp' => $this->timestamp(3)->notNull()->defaultExpression('CURRENT_TIMESTAMP(3)'),

my command line arguments are:

php yii migration/create-all --generalSchema=0

Please get a look

Add unique index generating

Generator is not generating unique index.

Default value for json/jsonb column leads to crash

Hello.

Yii converts default value for json/jsonb to array and seems like yii2-migration doesn't expects this.

Log:

PHP Notice 'yii\base\ErrorException' with message 'Array to string conversion'

in vendor\bizley\migration\src\table\TableColumn.php:149

Quick & dirty way to fix it is to add elseif to TableColumn.php:149:

elseif (is_array($this->default)) {
    $this->definition[] = "defaultValue('" . $this->escapeQuotes(json_encode($this->default)) . "')";
} 

Though probably array used in yii2 not only for json/jsonb, but also for other column types, so maybe there is better way to fix it.

P.S. Also there is issue with default value {} (object), yii2 convert it to php empty array [] and json_encode convert it to [], so it becomes an array instead of object. There is no difference when you use json in PHP only, but for other languages (and in database) that could be a problem. Though that's already beyond control of this extension.

Table prefix

I have a prefix of the tables 'tablePrefix' => 'fd0_' (connection settings), when creating a new migration through this module, $this-> createTable('{{%fd0_user}}' is created, and when used, the table fd0_fd0_user is created.
I can use useTablePrefix = 0, but this is not a solution.
Is it possible to delete the prefix from the migration to get $this-> createTable ('{{% user}}'?

У меня есть префикс таблиц 'tablePrefix' => 'fd0_' (настройках коннекта), при создании новой миграции через этот модуль, создаётся $this->createTable('{{%fd0_user}}', и при применении, создаётся таблица fd0_fd0_user. 
Я могу использовать useTablePrefix=0, но это не решение проблемы.
Есть возможность удалять префикс из миграции, чтобы получить $this->createTable('{{%user}}' ?

json support

Hi
we're using postgres and json/jsonb field types.
recently Yii got json field type support, but seems it's not supported in the project.
For now i've quickly hacked this the next way:
diff.patch.txt

table had json field with default value '{}'::json and check "field::text <> '[]'::text"

Unsigned integer as 10 chars length

Hi, just tiny case...
is it possible to generate unsigned integer field with 10 chars length? In example, this:
'updated_at' => $this->integer(10)->unsigned()->notNull(),
instead of this:
'updated_at' => $this->integer()->unsigned()->notNull(),

Because unsigned int's take 10 chars, not 11 (default).

Error when using namespace

yii migrate/create-all -h -n="frontend\\migrations"

after run command, i change table and update

yii migrate/update-all -h -n="frontend\\migrations"

Exception throw:

Yii 2 Migration Generator Tool v2.1.2

 > Are you sure you want to potentially generate 16 migrations? [y]es / [n]o [n] y
 > Generating update migration for table 'asset_bundle' ...Exception 'Error' with message 'Class 'CreateUserTable' not found'

in \vendor\bizley\migration\src\Updater.php:187

Migrate into SQL Server giving Prefix tha cause issue in GII

I'm trying to migrate into SQL SERVER 2008, it successfully create table in SQL Server. But all the table has prefix name equal to username that used to login into SQL Server. It's causing an issue when trying to Generate CRUD using GII. The generator can't read all the table, so I can't generate CRUD.
I've tried to execute yii migrate/create tablename --useTablePrefix=0 but it also didn't work.

How do I can migrate create table into SQL Server without giving prefix into table name?

Wrong AUTO_INCREMENTs when generating update migrations

When a table has a (composite) primary key (in my case both WITHOUT AUTO_INCREMENT) the update and update-all command appends AUTO_INCREMENT to each property which is in the key (the create and create-all command don't do that). so if you have a table with a (composite) key it is generated correctly by the create command: ie. $this->addPrimaryKey('PRIMARY', '{{%table}}', ['property_a,'property_b']); but if you run the update command afterwards it somehow generates the following wrong statements which add the AUTO_INCREMENT to each property in the (composite) key:
$this->alterColumn('{{%table}}', 'property_a', $this->...->append('AUTO_INCREMENT')); and
$this->alterColumn('{{%table}}', 'property_b', $this->...->append('AUTO_INCREMENT'));

this also happens if a property was generated by the create command with $this->...->append('PRIMARY KEY'). then the update command generates a $this->...->append('AUTO_INCREMENT')

it seems the updater assumes that every key should be auto_increment, which is wrong.

PS: happening with mysql.

Doesn't create Indexes

The migrations don't contain Indexes even though they're in my database.
Looks like the code in:
https://github.com/bizley/yii2-migration/blob/master/src/views/create_migration.php
only supports uniqueIndexes and not regular Indexes

Strings length

Hi
before anything thanks for this good and clear extension.

I have problem with char and varchar types that has length in DB.
after generate , in migration class , strings not has length.

my field:
title varchar(45) DEFAULT NULL,

generated:
'title' => $this->string(),

must generated:
'title' => $this->string(45),

MY DB: MySql version 5.7.24
Yii Version : 2.0.15.1
PHP version: 7.1

Thanks

Option to exclude table from mass actions

This should affect create-all and update-all. Idea emerged from #47

Not working when there's manual commands

Hi,

I'm having a lot of problems when calling migration/update because of some commands that I've ran on old migrations. For example:

Yii::$app->db->createCommand("UPDATE some_table SET child_id = id")->execute();

I cannot just throw those commands away, unfortunately.

Is there some workaround to make your migration/update work in that case? One good solution would be using a kind of 'ignore' annotation that tells de parser to ignore the line just below it. Like this:

/* @ignore */
Yii::$app->db->createCommand("UPDATE some_table SET child_id = id")->execute();

This would be enough. By now I'm commenting and uncommenting those lines to make it work .

By the way, good job with this extension. Very helpful indeed.

Return non-zero error code if migration creation failed

3.0.0 / Yii 2.1 / PHP 7

I'm planning to release v3.0.0 in near future that will split this repo into 3.* and 2.* branches. The 3.0.0 version will push requirements to PHP 7 and Yii 2.0.14 (or higher) and will allow me to drop some old code that is required right now because of PHP 5.4 compatibility that Yii forces.

Branch 2.* will still be supported, although I'm not sure for how long.

Column uniqueness added with index is not detected during update

Updater can not detect unique column when comparing virtual schema with real one.

create and update actions silently handle missing tables

Maybe throw some kind of error when the table we're creating/updating a migration for does not exist?

Lacking tinyint support

The lack of support for tinyint that was introduced in Yii 2.0.14 results in an improper column definition:

'flag' => $this->unsigned()->notNull()->defaultValue('0'),

instead of:

'flag' => $this->tinyInteger(1)->unsigned()->notNull()->defaultValue('0'),

Since $this is a Migration object, the ColumnSchemaBuilder methods that follow (e.g. unsigned()) result in an error.

Seems that when encountering an unexpected column type, either the column should be skipped altogether or use a default column generator method such as?

'foo' => $this->integer()->comment('ERROR: Unexpected column type: foobar'),

Perhaps better to address in a separate issue.

Bug in beforeAction

Expects parameter 1 to be string, array given

yii2-migration/src/controllers/MigrationController.php

Line 305 in 49ab8cd

$namespace = FileHelper::normalizePath($this->migrationNamespace, '\\');

Specified key was too long; max key length is 767 bytes

When I run the migrations I get this message:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

The error could be fixed using utf8 (that have greater limit) then utf8mb4 in:

if ($this->db->driverName === 'mysql') {
            $tableOptions = 'CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB';
        }

Default value "" (empty string) or empty array treated as null (no default value)

Hello.
Column in database has "" (empty string) as default value, but yii2-migration treat it as null (as if there is no default value specified).

Example table (SQL):

CREATE TABLE test (
	id serial NOT NULL,
	"name" varchar(255) NOT NULL DEFAULT '',
	CONSTRAINT test_pkey PRIMARY KEY (id)
);

Generated code:

'name' => $this->string()->notNull(),

Expected generated code:

'name' => $this->string()->notNull()->defaultValue(''),

I'm using PostgreSQL. The issue itself is not database specific, but IIRC, MySQL automatically gives default value "" (empty string) to not null varchar column. In contrast, in PostgreSQL you have to specify that you want "" (empty string) to be default (if you don't do this PostgreSQL will raise an error if you save it without specifying value for that column).

That's important when you have not null column but the column itself is optional. Without default value in database, you have to specify "" in model every time you save new model. There is a reason why column is not null and not nullable.

So, moving to the cause of issue. Changing Generator.php:186

'default' => $column->defaultValue ?: null,

to

'default' => $column->defaultValue,

resolves the issue.
But as I see in history, it was intentionally changed. Is there a reason for this?

P.S. Same thing happens with empty array for json/jsonb column. But json/jsonb also has another issue #50 .

update-all command throw exception : Trying to get property 'db' of non-object

i use composer to install this library, everything ok, i can list, create, create-all but i cannot use update-all, it will prompt me "Trying to get property 'db' of non-object"

Handling indexes

Since Yii 2.0.13 indexes data is stored by the framework so it's time to add it in the extension.

DBMS that does not support column length triggers unnecessary update

Need to detect column not supporting parentheses based on schema typeMap and ignore values provided in migration for that column.

update action generates migrations that are potentially unapplicable on top for existing migrations

I'm on mysql 5.7 and Yii2 2.0.15.1

Here is the part of my Yii config related to this extension:

        'controllerMap' => [
            'migration-creator' => [
                'class' => 'bizley\migration\controllers\MigrationController',
                'generalSchema' => 0
            ],
        ]

My problem is that when I create a "create table" migration and then try to create an "update table" migration for the same table (after making sure the "create table" migration is applied), the update migration would contain alter statements that are already in the create migration. The most disturbing part is, in case the create migration has a primary key declaration, that update migration would be unapplicable, because it tires to create an additional primary key.

Here is the minimal example to reproduce the issue:

Here is a create migration generated with yii migration-creator/create cache:

<?php

use yii\db\Migration;

class m180630_235851_create_table_cache extends Migration
{
    public function up()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
        }

        $this->createTable('{{%cache}}', [
            'id' => $this->char(128)->notNull()->append('PRIMARY KEY'),
            'expire' => $this->integer(11),
            'data' => $this->binary(),
        ], $tableOptions);

    }

    public function down()
    {
        $this->dropTable('{{%cache}}');
    }
}

Here is how my database looks when I load nothing but that migration with yii migrate/up:

Then, here is an update migration generated with yii migration-creator/update cache:

<?php

use yii\db\Migration;

class m180701_000023_update_table_cache extends Migration
{
    public function up()
    {
        $this->alterColumn('{{%cache}}', 'id', $this->char(128)->notNull()->append('PRIMARY KEY'));
        $this->alterColumn('{{%cache}}', 'expire', $this->integer(11));
    }

    public function down()
    {
        echo "m180701_000023_update_table_cache cannot be reverted.\n";
        return false;
    }
}

When I apply it with yii migration/up after generating it, here is what I get:

$ bin/docker/yii migrate/up
Yii Migration Tool (based on Yii v2.0.15.1)

Total 1 new migration to be applied:
        m180701_002353_update_table_cache

Apply the above migration? (yes|no) [no]:yes
*** applying m180701_002353_update_table_cache
    > alter column id in table {{%cache}} to char(128) NOT NULL PRIMARY KEY ...Error: SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined
The SQL being executed was: ALTER TABLE `cache` CHANGE `id` `id` char(128) NOT NULL PRIMARY KEY

MIssing new lines in create_foreign_keys file

Hi, a tiny bug?

Version: 3.6.2

After migration/create from few tables with foreign keys I got file m191028_115306_07_create_foreign_keys.php without new line between addForeignKey() call.

Is:

$this->addForeignKey('event_ibfk_3', '{{%event}}', 'created_by', '{{%user}}', 'user_id', 'SET NULL', 'CASCADE');        $this->addForeignKey('event_ibfk_4', '{{%event}}', 'updated_by', '{{%user}}', 'user_id', 'SET NULL', 'CASCADE');

Should be:

$this->addForeignKey('event_ibfk_3', '{{%event}}', 'created_by', '{{%user}}', 'user_id', 'SET NULL', 'CASCADE');
$this->addForeignKey('event_ibfk_4', '{{%event}}', 'updated_by', '{{%user}}', 'user_id', 'SET NULL', 'CASCADE');

Exception 'TypeError' with message 'Argument 1 passed to MigrationController::preparePathDirectory() must be of the type string, array given

I'm getting an error

# php yii migration/create-all
Exception 'TypeError' with message 'Argument 1 passed to bizley\migration\controllers\MigrationController::preparePathDirectory() must be of the type string, array given, called in /basic/vendor/bizley/migration/src/controllers/MigrationController.php on line 295'

in /basic/vendor/bizley/migration/src/controllers/MigrationController.php:321

Stack trace:
#0 /basic/vendor/bizley/migration/src/controllers/MigrationController.php(295): bizley\migration\controllers\MigrationController->preparePathDirectory(Array)
#1 /basic/vendor/yiisoft/yii2/base/Controller.php(155): bizley\migration\controllers\MigrationController->beforeAction(Object(yii\base\InlineAction))
#2 /basic/vendor/yiisoft/yii2/console/Controller.php(148): yii\base\Controller->runAction('create-all', Array)
#3 /basic/vendor/yiisoft/yii2/base/Module.php(528): yii\console\Controller->runAction('create-all', Array)
#4 /basic/vendor/yiisoft/yii2/console/Application.php(180): yii\base\Module->runAction('migration/creat...', Array)
#5 /basic/vendor/yiisoft/yii2/console/Application.php(147): yii\console\Application->runAction('migration/creat...', Array)
#6 /basic/vendor/yiisoft/yii2/base/Application.php(386): yii\console\Application->handleRequest(Object(yii\console\Request))
#7 /basic/yii(19): yii\base\Application->run()
#8 {main}

config

      'migration' => [
            'class' => 'bizley\migration\controllers\MigrationController',
            'migrationPath' => [
                '@console/migrations',               
                '@yii/rbac/migrations',
            ]
        ],

---------

---

One migration file

Would be nice to generate one migration file. (Generate migrations to create all DB tables into one file)

yii migrate error

Hi !

I have table

CREATE TABLE `blog_category`
(
    `id` Int NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор записи',
    `parent_id` Int COMMENT 'Идентификатор родительского элемента',
    `name` Varchar(64) NOT NULL COMMENT 'Наименование категории',
    `tags` Json COMMENT 'Тэги',
    PRIMARY KEY (`id`)
)
COMMENT = 'Категории статей';

CREATE INDEX `parent_name` ON `blog_category` (`parent_id`,`name`) COMMENT 'По родителю в алфавитном порядке';

And generated migration is:

class m180317_090027_create_table_blog_category extends Migration
{
    public function safeUp()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
        }

        $this->createTable('{{%blog_category}}', [
            'id' => $this->integer(11)->notNull()->comment('Идентификатор записи')->append('AUTO_INCREMENT PRIMARY KEY'),
            'parent_id' => $this->integer(11)->comment('Идентификатор родительского элемента'),
            'name' => $this->string(64)->notNull()->comment('Наименование категории'),
            'tags' => $this->comment('Тэги'),
        ], $tableOptions);
    }

    public function safeDown()
    {
        $this->dropTable('{{%blog_category}}');
    }
}

When I run "yii migrate", I see:

_Apply the above migrations? (yes|no) [no]: applying m180317_090027_create_table_blog_category
Exception: Calling unknown method: m180317_090027_create_table_blog_category::comment() (E:\www\doreme.spb.ru\vendor\yiisoft\yii2\base\Component.php:300)
#0 E:\www\doreme.spb.ru\console\migrations\m180317_090027_create_table_blog_category.php(18): yii\base\Component->_call('comment', Array)
#1 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\db\Migration.php(114): m180317_090027_create_table_blog_category->safeUp()
#2 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\console\controllers\BaseMigrateController.php(725): yii\db\Migration->up()
#3 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\console\controllers\BaseMigrateController.php(199): yii\console\controllers\BaseMigrateController->migrateUp('m180317_090027...')
#4 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#5 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#6 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#7 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\console\Controller.php(148): yii\base\Controller->runAction('', Array)
#8 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\base\Module.php(528): yii\console\Controller->runAction('', Array)
#9 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\console\Application.php(180): yii\base\Module->runAction('migrate', Array)
#10 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\console\Application.php(147): yii\console\Application->runAction('migrate', Array)
#11 E:\www\doreme.spb.ru\vendor\yiisoft\yii2\base\Application.php(386): yii\console\Application->handleRequest(Object(yii\console\Request))
#12 E:\www\doreme.spb.ru\yii(23): yii\base\Application->run()
#13 {main}
failed to apply m180317_090027_create_table_blog_category (time: 0.007s)

WHY ?!

Missing 'after' in migration/create (column order not respected)

Hi,
migration/update ignores column order in generated code - missing after() method.

$this->addColumn('{{%address}}', 'post', $this->string());
$this->addColumn('{{%address}}', 'commune', $this->string());

Decimal type field wrong convert

MySQL:
decimal(10,0)

After migration:
$this->decimal(10,)

What about Views?

Is there is a way for automate the generation of VIEWS, my DB have 3 VIEWS and the extension reads them as tables, so when i run the migrations on a fresh DB i wont have the views but instead will have tables with the same names...

ENUM types generated as STRING

Hello, i have found that ENUM type columns are strings after migration

initial SQL

CREATE TABLE `users` (
  `id` mediumint(3) UNSIGNED NOT NULL,
  `fullname` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
  `country` varchar(3) CHARACTER SET utf8 DEFAULT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `auth_key` varchar(32) NOT NULL,
  `token` varchar(32) NOT NULL,
  `email` varchar(150) NOT NULL,
  `role` enum('author','admin') NOT NULL DEFAULT 'author',
  `subscription` tinyint(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'subscribe to news',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

after running command

php yii migration/create users -g=0

migration created

$this->createTable('{{%users}}', [
            'id' => $this->integer(3)->unsigned()->notNull()->append('AUTO_INCREMENT PRIMARY KEY'),
            'fullname' => $this->string(40),
            'country' => $this->string(3),
            'username' => $this->string(30)->notNull(),
            'password' => $this->string(60)->notNull(),
            'auth_key' => $this->string(32)->notNull(),
            'token' => $this->string(32)->notNull(),
            'email' => $this->string(150)->notNull(),
            'role' => $this->string(255)->notNull()->defaultValue('author'),
            'subscription' => $this->tinyInteger(1)->unsigned()->notNull()->defaultValue('0')->comment('subscribe to news'),
            'created_at' => $this->timestamp()->notNull()->defaultExpression('CURRENT_TIMESTAMP'),
        ], $tableOptions);

so you can see that role column have type string after generating migration.

Change MySQL table options in config

MySQL table options is hardcoded with CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB

Quotes in database comments & defaults not properly escaped

A column with a comment that contains a single quote (apostrophe) is not escaped resulting in:

'first_name' => $this->string(255)->comment('User's first name'),
'last_name' => $this->string(255)->comment('User's last name'),

Obviously, this should look like:

'first_name' => $this->string(255)->comment('User\'s first name'),
'last_name' => $this->string(255)->comment('User\'s last name'),

Issue With PHP 7.2

Cannot use yii\base\Object as Object because 'Object' is a special class name in /vendor/bizley/migration/table/TableStructure.php on line 6

--migration Lookup option

The standard command with the --migration Lookup option does not work. Command yii migrate --migrationLookup=@ ... ends with error: Error: Unknown option: --migrationLookup. We have to temporarily disable Your module.

Managing non-ColumnSchemaBuilder provided columns and special DBMS altering cases

1. Yii 2 migrations encourage to use shortcut methods from SchemaBuilderTrait but it's still possible to use plain text descriptions that were in use before Yii 2.0.6. Question is if that should be handled as well by this extension because right now is not. I need to investigate.

2. Apparently PostgreSQL column altering way is different from other DBMS and for example requires special statement to change default value or doesn't allow changing column type and default value at once (in single ALTER COLUMN statement). Am I right here?
   Yii detects special statements and only decodes column type if there are none. This extension definitely needs to do the same.

3. There is related to above problem bug issued in Yii - when altering column using new column definition with more than a type like:

    $this->alterColumn('table', 'column', $this->string()->defaultValue(1));
   

   PostgreSQL throws error. It would be nice for generated statement to be correct in that case. I'll take a look at this as well.

PHP Fatal error: Cannot use yii\base\Object

Cancel

Issue with version 3.0.4

When default value in table is number, php returns an error.

Argument 1 passed to bizley\migration\table\TableColumn::escapeQuotes() must be of the type string, integer given, called in \vendor\bizley\migration\src\table\TableColumn.php on line 126

For me, the solution was to convert value ​​from int to string in this line of code. :)

Size for char fields is not respected

When I e.g. have a field with type char(32), in the migration generated the code for that field is just $this->addColumn(...)->char(), with no size specified, which results in size 1 field to be created on migration application.

Not working with switch statement in db.config

After run php yii migration/list
I get an error

Yii 2 Migration Generator Tool v3.0.5

Exception 'yii\db\Exception' with message 'SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known. '

in ...path\vendor\yiisoft\yii2\db\Connection.php:624

My DbConfig have a switch statement in there

<?php
// DB CONFIG
switch ($_SERVER['HTTP_HOST']):
    case 'test.loc':
        return [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=partner',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8'
        ];
        break;
    default:
        return [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=host;dbname=dbnamehost',
            'username' => 'username',
            'password' => 'pass',
            'charset' => 'utf8'
        ];
        break;
endswitch;

So after removing a swtich statement, everything works fine, but it's not an solution

ON UPDATE CURRENT_TIMESTAMP

" created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP"

Generated migration:
"'created_at' => $this->timestamp()->defaultExpression('CURRENT_TIMESTAMP'),
'updated_at' => $this->timestamp(),"

There are no defaultExpression for second field.
This is bug or expression "ON UPDATE CURRENT_TIMESTAMP" is not universal, and its appliable only to mysql DBs?

Thank you.

errno: 150 "Foreign key constraint is incorrectly formed"

I think there is a bug. After I run php yii migration/create-all, the tables are created in alphabetical order. However, the foreign key constraints are added inside the table creation which is in the line after the this->createTable something like this:

$this->createTable('foo', [ 'id' => $this->primaryKey() ]); // whatever
$this->addForeignKey('fk_app_assignee_user-id', '{{%appointment_assignee}}', 'user_id', '{{%users}}', 'id', 'RESTRICT', 'RESTRICT');

where table appointment_assignee is created and trying to add a foreign key constraint referencing the users table but it doesn't exist yet.

I believe one Laravel migration generator tool solves this by first creating migration files for create table commands, then creating migration files for foreign key constraint commands afterwards.

Generating alter column when not needed and autocomplete primary issue

Hi.

Looks like migration update with -g=0 generates the same columns alter code over and over with tinyInteger or smallInteger.

Also, still with -g=0 parameter, it generates double autoincremet primary key string when the first time migration generate was in g=1 mode, string like this

$this->integer(11)->notNull()->append('AUTO_INCREMENT PRIMARY KEY AUTO_INCREMENT PRIMARY KEY')