Bake migration_diff/migration_snapshot includes View as table

[challgren]

When baking a migration_diff or migration_snapshot with a database that has a view table, bake includes the view as a table. I would expect it to not include the view or include the view as a raw SQL query.

[saeideng]

can you explain more ?

[challgren]

Ok for example we have a database called test, we create 1 table

CREATE TABLE `test`.`tester` (
  `id` INT NOT NULL,
  `username` VARCHAR(45) NULL DEFAULT NULL,
  `email` VARCHAR(45) NULL DEFAULT NULL,
  `created` DATETIME NULL DEFAULT NULL,
  `modified` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`));

Then create a SQL view on that table

CREATE VIEW `test`.`new_view` AS
    SELECT 
        `test`.`tester`.`username` AS `username`
    FROM
        `test`.`tester`

Now run bin/cake bake migration_snapshot and the new_view, view will be baked as a table instead of being ignored.

This is the migration that is baked

<?php
use Migrations\AbstractMigration;

class Initial extends AbstractMigration
{
    public function up()
    {

        $this->table('new_view', ['id' => false])
            ->addColumn('username', 'string', [
                'default' => null,
                'limit' => 45,
                'null' => true,
            ])
            ->create();

        $this->table('tester')
            ->addColumn('username', 'string', [
                'default' => null,
                'limit' => 45,
                'null' => true,
            ])
            ->addColumn('email', 'string', [
                'default' => null,
                'limit' => 45,
                'null' => true,
            ])
            ->addColumn('created', 'datetime', [
                'default' => null,
                'limit' => null,
                'null' => true,
            ])
            ->addColumn('modified', 'datetime', [
                'default' => null,
                'limit' => null,
                'null' => true,
            ])
            ->create();
    }

    public function down()
    {
        $this->table('new_view')->drop()->save();
        $this->table('tester')->drop()->save();
    }
}

As you can see the migration has a $this->table('new_view')->create(); which is incorrect as it should be ignored or converted to raw SQL to be used.

[dereuromark]

How does the schema data (as array etc) look like for view vs table? Is this "seeable" by the Cake ORM schema reading? Or how would it know the difference?

[challgren]

Actually it looks like SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW'; is the better option. It seems maybe https://github.com/cakephp/cakephp/blob/master/src/Database/Schema/MysqlSchema.php#L37 is causing the issue. Since in the case of migrations we really don't care about views/know how to bake them properly. To get the true tables it would be better to use SHOW FULL TABLES IN test WHERE TABLE_TYPE NOT LIKE 'VIEW'; This may require work on both cakephp/migrations and cakephp/cakephp

[challgren]

Changing MysqlSchema.php#L37 to return ['SHOW FULL TABLES IN ' . $this->_driver->quoteIdentifier($config['database']) . ' WHERE TABLE_TYPE NOT LIKE \'VIEW\'', []]; fixes the issue and results in a correctly baked migration

<?php
use Migrations\AbstractMigration;

class Initial extends AbstractMigration
{
    public function up()
    {

        $this->table('tester')
            ->addColumn('username', 'string', [
                'default' => null,
                'limit' => 45,
                'null' => true,
            ])
            ->addColumn('email', 'string', [
                'default' => null,
                'limit' => 45,
                'null' => true,
            ])
            ->addColumn('created', 'datetime', [
                'default' => null,
                'limit' => null,
                'null' => true,
            ])
            ->addColumn('modified', 'datetime', [
                'default' => null,
                'limit' => null,
                'null' => true,
            ])
            ->create();
    }

    public function down()
    {
        $this->table('tester')->drop()->save();
    }
}

I guess the solution would be to have cakephp/cakephp implement a listTablesSqlWithoutViews($config) in MysqlSchema.php

[challgren]

Related https://github.com/cakephp/cakephp/issues/10847