Running generated migrations with foreign key references on empty database fails

[chibuzoro]

Questions should go to https://forum.phalconphp.com
Documentation issues should go to https://github.com/phalcon/docs/issues

Expected and Actual Behavior

Describe what you are trying to achieve and what goes wrong.

I dumped my SQL to the database (Postgres 9.4) and used devtools to generate ts-based migration files stored in db afterwards. All went well. However, after dropping the database and creating afresh, then attempting to run the same migrations generated by devtools (just simulating a fresh deployment), the migration fails with an error :

ERROR: SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "public.client" does not exist"

I observed, the migrations may have been generated in a random order and so in an attempt to run on a fresh environment, the foreign table referenced may not have been created. Thus leading to errors.

References to similar issue:

1. https://stackoverflow.com/questions/15524455/workflow-migrations-phalcon
2. https://forum.phalconphp.com/discussion/17791/db-migration-with-foreign-keys

Expected behaviour:
Generate migration files from db with reference to defined foreign relationships, ensuring referenced tables get created first in the hierarchy.

Provide output if related

`ERROR: SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "public.client" does not exist" `

Provide minimal script to reproduce the issue

Files listed in the order they were generated by phalcon devtools

 <?php

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

/**
 * Class AccesstokenMigration_1532019814860526
 */
class AccesstokenMigration_1532019814860526 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     */
    public function morph()
    {
        $this->morphTable('accessToken', [
                'columns'    => [
                    new Column(
                        'token',
                        [
                            'type'    => Column::TYPE_VARCHAR,
                            'notNull' => TRUE,
                            'size'    => 80,
                            'first'   => TRUE,
                        ]
                    ),
                    new Column(
                        'clientId',
                        [
                            'type'  => Column::TYPE_VARCHAR,
                            'size'  => 80,
                            'after' => 'token',
                        ]
                    ),
                    new Column(
                        'userId',
                        [
                            'type'  => Column::TYPE_VARCHAR,
                            'size'  => 80,
                            'after' => 'clientId',
                        ]
                    ),
                    
                ],
                'indexes'    => [
                    new Index('accessToken_pkey', ['token'], NULL),
                ],
                'references' => [
                    new Reference(
                        'fk_accessToken_client_1',
                        [
                            'referencedTable'   => 'client',
                            'referencedSchema'  => 'public',
                            'columns'           => ['clientId'],
                            'referencedColumns' => ['id'],
                            'onUpdate'          => 'NO ACTION',
                            'onDelete'          => 'NO ACTION',
                        ]
                    ),
                ],
            ]
        );
    }


    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {

    }


    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {
        $this->getConnection()->dropTable('accessToken');

    }

}

<?php

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

/**
 * Class ClientMigration_1532019814860526
 */
class ClientMigration_1532019814860526 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     */
    public function morph()
    {
        $this->morphTable('client', [
                'columns'    => [
                    new Column(
                        'id',
                        [
                            'type'    => Column::TYPE_VARCHAR,
                            'notNull' => TRUE,
                            'size'    => 80,
                            'first'   => TRUE,
                        ]
                    ),
                    new Column(
                        'name',
                        [
                            'type'    => Column::TYPE_VARCHAR,
                            'notNull' => TRUE,
                            'size'    => 80,
                            'first'   => TRUE,
                        ]
                    ),
                ],
                'indexes'    => [
                    new Index('client_pkey', ['id'], NULL),
                ],
            ]
        );
    }


    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {

    }


    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {
        $this->getConnection()->dropTable('client');
    }

}

Details

• System info and versions (if possible): (phalcon info)
  Environment:
  OS: Linux edcef3d10fee 4.9.93-linuxkit-aufs Easier installation on Linux and OSX phalcon-devtools#1 SMP Wed Jun 6 16:55:56 UTC 2018 x86_64
  PHP Version: 7.2.7-1+ubuntu16.04.1+deb.sury.org+1
  PHP SAPI: cli
  PHP Bin: /usr/bin/php7.2
  PHP Extension Dir: /usr/lib/php/20170718
  PHP Bin Dir: /usr/bin
  Loaded PHP config: /etc/php/7.2/cli/php.ini
  Versions:
  Phalcon DevTools Version: 3.4.0
  Phalcon Version: 3.4.0
  AdminLTE Version: 2.3.6

• Phalcon Framework version: (php --ri phalcon)
  phalcon

Web framework delivered as a C-extension for PHP
phalcon => enabled
Author => Phalcon Team and contributors
Version => 3.4.0
Build Date => Jun 6 2018 04:54:09
Powered by Zephir => Version 0.10.10-d1b4cc68d9

Directive => Local Value => Master Value
phalcon.db.escape_identifiers => On => On
phalcon.db.force_casting => Off => Off
phalcon.orm.events => On => On
phalcon.orm.virtual_foreign_keys => On => On
phalcon.orm.column_renaming => On => On
phalcon.orm.not_null_validations => On => On
phalcon.orm.exception_on_failed_save => Off => Off
phalcon.orm.enable_literals => On => On
phalcon.orm.late_state_binding => Off => Off
phalcon.orm.enable_implicit_joins => On => On
phalcon.orm.cast_on_hydrate => Off => Off
phalcon.orm.ignore_unknown_columns => Off => Off
phalcon.orm.update_snapshot_on_save => On => On
phalcon.orm.disable_assign_setters => Off => Off

• PHP Version: (php -v)

PHP 7.2.7-1+ubuntu16.04.1+deb.sury.org+1 (cli) (built: Jun 22 2018 08:44:50) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
with Xdebug v2.7.0alpha1, Copyright (c) 2002-2018, by Derick Rethans
with Zend OPcache v7.2.7-1+ubuntu16.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

• Operating System:
  MacOs High Sierra - running docker instance
• Server: Nginx | Apache | Other
  Nginx
• Other related info (Database, table schema):
  Postgres

[Jeckerson]

In your case, add queries SET FOREIGN_KEY_CHECKS=? before and after morph()

Example:

public function morph()
{
    $this->getConnection()->execute('SET FOREIGN_KEY_CHECKS=0;');

    $this->morphTable('accessToken', [...]);

    $this->getConnection()->execute('SET FOREIGN_KEY_CHECKS=1;');
}

[Jeckerson]

Closing in favour of #11