systems:yii2:migrations [Audina Documentation]

Yii 2 Migrations

Visit documentation Yii 2.0 Guide: Migrations

Create Migration

In Yii, at the command line: $ yii migrate/create <name>

For example, to create a migration of customer data:

$ yii migrate/create create_customer_table

This generates a file m150904_181255_create_customer_table.php in [src]/migrations.

<?php
 
use yii\db\Schema;
use yii\db\Migration;
 
defined('DS') or define('DS', DIRECTORY_SEPARATOR);  // set const if not defined
 
class m150904_181255_create_customer_table extends Migration
{
    //public function init()
    //{
    //    $this->db = 'db2';
    //    parent::init();
    //}
 
    public function up()
    {
        $this->create_table_customer();
        $this->import_customers_from_CSV();
    }
 
    public function down()
    {
        //echo "m150904_181255_create_customer_table cannot be reverted.\n";
        //return false;
 
        //$this->delete('user', ['not in', 'id', [1, 2, 3]]);   // delete all records, except with id 1, 2, 3
        //return true;
 
        $connection = Yii::$app->db;                  // get connection
        $dbSchema   = $connection->schema;            // or $connection->getSchema();
        $allTables  = $dbSchema->getTableNames();     // returns array of tbl schema's
 
        // Available Tables: must be listed in reverse creation order,
        // and dropping tables with foreign keys first.
        //$targetTables = ["evaluation", "issue", "customer", "sales_history", ];  
        $targetTables = ["customer"];
 
        echo "\nAvailable Tables: \n- ".implode("\n- ", $allTables)."\n";
        echo "\nTarget Tables: \n- ".implode("\n- ", $targetTables)."\n\n";
 
        foreach($targetTables as $curTable) {
            echo "Checking table: {$curTable}\n";
            if (in_array($curTable, $allTables)) {
                //if ($curTable == 'company_address')
                //{
                //    $this->dropForeignKey('company_address_company_id', $curTable);
                //}
                $this->truncateTable($curTable);   // delete all records
                $this->dropTable($curTable);       // drop table
            }
        }
        return true;
    }
 
    /*
    // Use safeUp/safeDown to run migration code within a transaction
    public function safeUp()
    {
    }
 
    public function safeDown()
    {
    }
    */
 
    private function create_table_customer()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            // http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB AUTO_INCREMENT=1';
        }
 
        //CREATE TABLE `customer` (
        //    `id` INT(11) NOT NULL AUTO_INCREMENT,
        //    `hcode`        VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `ship_to`      VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `contact`      VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `company_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `address1`     VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `address2`     VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `city`         VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `state_prov`   VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `postal_code`  VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `country`      VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `phone`        VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `phone_ext`    VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `fax`          VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `fax_ext`      VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `email`        VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `website`      VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `hours`        TEXT NULL COLLATE 'utf8_unicode_ci',
        //    `notes`        TEXT NULL COLLATE 'utf8_unicode_ci',
        //    `status`       SMALLINT(6) NOT NULL DEFAULT '1',
        //    `created`      DATETIME NOT NULL,
        //    `updated`      DATETIME NOT NULL,
        //    PRIMARY KEY (`id`)
        //)
        //COLLATE='utf8_unicode_ci'
        //ENGINE=InnoDB
        //AUTO_INCREMENT=1;
 
        $this->createTable('customer', [
            'id'           => Schema::TYPE_PK,
            'hcode'        => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'ship_to'      => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'contact'      => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'company_name' => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'address1'     => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'address2'     => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'city'         => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'state_prov'   => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'postal_code'  => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'country'      => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'phone'        => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'phone_ext'    => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'fax'          => Schema::TYPE_STRING   . '(50) NULL DEFAULT NULL',
            'fax_ext'      => Schema::TYPE_STRING   . '(50) NULL DEFAULT NULL',
            'email'        => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'website'      => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'hours'        => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
            'notes'        => Schema::TYPE_TEXT     . ' NULL',
            'status'       => Schema::TYPE_BOOLEAN  . ' NOT NULL DEFAULT 1',
            'created'      => Schema::TYPE_DATETIME . ' NULL DEFAULT NULL',
            'modified'     => Schema::TYPE_DATETIME . ' NULL DEFAULT NULL',
        ], $tableOptions);
    }
 
    private function import_customers_from_CSV()
    {
        // open the source data file
        $dataFilename = Yii::$app->basePath . DS . 'migrations' . DS . 'data' . DS . 'customer-data.rpt';
        if (!file_exists($dataFilename))
        {
            echo "Missing data file: [" . $dataFilename . "]\n";
            return false;
        }
 
        $dataFile = fopen($dataFilename, "r") or die("Unable to open file!");
        while(!feof($dataFile)) {
            // grab one line from file
            $data = fgets($dataFile);
 
            // parse data
            $hcode           = strtoupper($this->getCleanField($data, 0, 10));  
            $ship_to         = strtoupper($this->getCleanField($data, 10, 18));  
            $company_name    = $this->getCleanField($data, 28, 39);  
            $address1        = $this->getCleanField($data, 67, 39);
            $address2        = $this->getCleanField($data, 106, 37);
            $city            = $this->getCleanField($data, 143, 20);
            $postal_code     = $this->getCleanField($data, 163, 11);
            $state_prov      = $this->getCleanField($data, 174, 7);
            $country         = $this->getCleanField($data, 181, 9);
            $phone           = $this->getCleanField($data, 190, 13);
            $phone_ext       = $this->getCleanField($data, 203, 13);
            $fax             = $this->getCleanField($data, 216, 13);
            $fax_ext         = $this->getCleanField($data, 229, 13);
            $contact         = $this->getCleanField($data, 242, 33);
 
            // concatenate data
            //$address = $this->getConcatString([$address1, $address2, $address3, $address4]);
            //$notes   = $this->getConcatString([$notes1, $notes2, $notes3]);
 
            if (stripos(strtoupper($company_name), 'CUSTNAME')!== false || 
                stripos($company_name, '--------')!== false     || 
                empty($company_name) )
            {
                continue;  // Invalid record.  Do not insert this record.
            }
 
            // insert data as a row
            $this->insert('customer', [
                'hcode'          => $hcode,
                'ship_to'        => $ship_to,
                'contact'        => $contact,
                'company_name'   => $company_name,
                'address1'       => $address1,
                'address2'       => $address2,
                'city'           => $city,
                'state_prov'     => $state_prov,
                'postal_code'    => $postal_code,
                'country'        => $country,
                'phone'          => $phone,
                'phone_ext'      => $phone_ext,
                'fax'            => $fax,
                'fax_ext'        => $fax_ext,
            ]); 
 
            // UPDATE customer SET phone='' WHERE phone='() -'
            $this->update('customer', ['phone' => ''],     ['phone' => '() -']);
            $this->update('customer', ['phone_ext' => ''], ['phone_ext' => '() -']);
            $this->update('customer', ['fax' => ''],       ['fax' => '() -']);
            $this->update('customer', ['fax_ext' => ''],   ['fax_ext' => '() -']);
        }
        fclose($dataFile);
    }
 
    private function getCleanField($srcData, $startIndex, $length, $defaultValue="")
    {
        $field = trim(substr($srcData, $startIndex, $length));
        $field = ($field != 'NULL' ? $field : '');
        $field = ($field != '\N'   ? $field : '');
        $field = ($field != ''     ? $field : $defaultValue);
        return $field;
    }
 
    private function getCleanStr($srcData, $defaultValue="")
    {
        $field = trim($srcData);
        $field = ($field != 'NULL' ? $field   : '');
        $field = ($field != '\N'   ? $field   : '');
        $field = ($field != ''     ? $field   : $defaultValue);
        return $field;
    }
 
    private function getConcatString($srcArray)
    {
        // concatenate data
        $dstArray = array();
        foreach($srcArray as $val)
        {
            if (!empty($val)) {  $dstArray[] = $val; }
        }
        $dstString = implode(", ", $dstArray);
        return $dstString;
    }
}

You can run and undo the migration with these commands:

# Run any/all migrations:
$ yii migrate/up           # apply all available new migrations
$ yii migrate/down         # revert the most recently applied migration
 
# Run specific migration
$ yii migrate/to m150101_185401_create_news_table  
$ yii migrate/down 1       # revert the most recently applied migration
 
# Run migration for modules (eg. ERP)
$ yii migrate/to m180205_101010_create_tables --migrationPath=@app/modules/erp/migrations

New Notation

Another example but using the new schema notation:

<?php
 
use yii\db\Migration;
 
class m160601_220000_skeleton extends Migration
{
    private $useExtendedUserProfile = false;  // set it to your needs
 
    public function up()
    {
        // Create tables with foreign keys last
        $this->create_table_entry();
        $this->create_table_product_hash();
 
        if ($this->useExtendedUserProfile) {
            $this->alter_user_table();
        }
    }
 
    public function down()
    {
        $this->drop_tables();
 
        return true;
    }
 
    public function drop_tables()
    {
        $connection = Yii::$app->db;                  // get connection
        $dbSchema   = $connection->schema;            // or $connection->getSchema();
        $allTables  = $dbSchema->getTableNames();     // returns array of tbl schema's
 
        // Available Tables: must be listed in reverse creation order,
        // and dropping tables with foreign keys first.
        //$targetTables= ["product_hash", "entry"];
        $targetTables= [
            $this->db->schema->getRawTableName(app\models\ProductHash::tableName()),
            $this->db->schema->getRawTableName(app\models\Entry::tableName()),
        ];
 
        echo "\nAvailable Tables: \n- ".implode("\n- ", $allTables)."\n";
        echo "\nTarget Tables: \n- ".implode("\n- ", $targetTables)."\n\n";
 
        foreach($targetTables as $curTable) {
            echo "Checking table: {$curTable}\n";
            if (in_array($curTable, $allTables)) {
                if ($curTable == $this->db->schema->getRawTableName(app\models\Entry::tableName())) {  // entry
                    //$this->dropForeignKey('FK_name', $curTable);
                }
                if ($curTable == $this->db->schema->getRawTableName(app\models\ProductHash::tableName())) {  // product_hash
                    //$this->dropForeignKey('FK_name', $curTable);
                }
                $this->truncateTable($curTable);   // delete all records
                $this->dropTable($curTable);       // drop table
            }
 
            if ($this->useExtendedUserProfile) {
                // Restore any original table changes
                $this->restore_user_table();
            }
        }
    }
 
    private function alter_user_table()
    {
        $this->addColumn('{{%user}}', 'address1', $this->string());
        $this->addColumn('{{%user}}', 'address2', $this->string());
        $this->addColumn('{{%user}}', 'city', $this->string());
        $this->addColumn('{{%user}}', 'state_prov', $this->string());
        $this->addColumn('{{%user}}', 'postal_code', $this->string());
        $this->addColumn('{{%user}}', 'country', $this->string());
        // $this->addColumn('{{%user}}', 'company_name', $this->string());
        // $this->addColumn('{{%user}}', 'organization_name', $this->string());
        // $this->addColumn('{{%user}}', 'school_name', $this->string());
        // $this->addColumn('{{%user}}', 'job_title', $this->string());
        $this->addColumn('{{%user}}', 'receive_newsletter', $this->tinyInteger()->notNull()->defaultValue(1));
    }
 
    private function restore_user_table()
    {
        $this->dropColumn('{{%user}}', 'address1');
        $this->dropColumn('{{%user}}', 'address2');
        $this->dropColumn('{{%user}}', 'city');
        $this->dropColumn('{{%user}}', 'state_prov');
        $this->dropColumn('{{%user}}', 'postal_code');
        $this->dropColumn('{{%user}}', 'country');
        $this->dropColumn('{{%user}}', 'company_name');
        $this->dropColumn('{{%user}}', 'job_title');
        $this->dropColumn('{{%user}}', 'receive_newsletter');
    }
 
    private function create_table_entry()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            // http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB AUTO_INCREMENT=1';
        }
 
        // CREATE TABLE `entry` (
        //    `id`                INT(11) NOT NULL AUTO_INCREMENT,
        //    `serial_number`     VARCHAR(50) NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
        //    `order_number`      VARCHAR(50) NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
        //    `brand`             VARCHAR(50) NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
        //    `product_code`      VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `product_data_hash` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `product_data`      TEXT NULL COLLATE 'utf8_unicode_ci',
        //    `workstation`       VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
        //    `status`            INT(11) NOT NULL DEFAULT '1',
        //    `created_at`        DATETIME NULL DEFAULT NULL,
        //    PRIMARY KEY (`id`)
        // )
        // COLLATE='utf8_unicode_ci'
        // ENGINE=InnoDB
        // AUTO_INCREMENT=1;
 
        $this->createTable('{{%entry}}', [
            'id'                => $this->primaryKey(),
            'serial_number'     => $this->string(50)->defaultValue('0'),
            'order_number'      => $this->string(50)->defaultValue('0'),
            'brand'             => $this->string(50)->defaultValue('Audina'),
            'product_code'      => $this->string(),
            'product_data_hash' => $this->string(),
            'product_data'      => $this->text(),
            'workstation'       => $this->string(),
            'status'            => $this->boolean()->notNull()->defaultValue('1'),
            'created_at'        => $this->dateTime(),
        ], $tableOptions);
 
        // insert data as a row
        $this->insert('{{%entry}}', [
            "serial_number"     => "15169630", 
            "order_number"      => "0001", 
            "brand"             => "Audina", 
            "product_code"      => "prod_BTE6P", 
            "product_data_hash" => "",
            "product_data"      => "<data>No Data Yet</data>",
            "workstation"       => "swdev-laptop", 
            "status"            => 1, 
            "created_at"        => "2016-06-21 16:30:03"
        ]);
    }
 
    private function create_table_product_hash()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            // http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB AUTO_INCREMENT=1';
        }
 
        // CREATE TABLE `product_hash` (
        //    `id`              INT(11) NOT NULL AUTO_INCREMENT,
        //    `program_name`    text COLLATE utf8_unicode_ci NOT NULL,
        //    `program_value`   int(11) NOT NULL,
        //    `program_version` text COLLATE utf8_unicode_ci NOT NULL,
        //    `hash`            varchar(32) COLLATE utf8_unicode_ci NOT NULL
        //    PRIMARY KEY (`id`)
        // )
        // COLLATE='utf8_unicode_ci'
        // ENGINE=InnoDB
        // AUTO_INCREMENT=1;
 
        $this->createTable('{{%product_hash}}', [
            'id'              => $this->primaryKey(),
            'program_name'    => $this->text()->notNull(),
            'program_value'   => $this->integer()->notNull(),
            'program_version' => $this->text()->notNull(),
            'hash'            => $this->string()->notNull(),
        ], $tableOptions);
 
        // insert data as a row
        //$this->insert('{{%product_hash}}', [
        //    "program_name"    => "ProductITE1", 
        //    "program_value"   => "10", 
        //    "program_version" => "1.1", 
        //    "hash"            => "e2f1f348359c5e68a6d30f785abfff21",
        //]);
 
        // table name, column names, column values
        $db = Yii::$app->getDb();
        $db->createCommand()->batchInsert('{{%product_hash}}', 
            ['id', 'program_name', 'program_value', 'program_version', 'hash'], 
            [
                [1, 'ProductITE1', 10, '1.1', 'e2f1f348359c5e68a6d30f785abfff21'],
                [2, 'ProductITE2', 11, '1.1', '90e24bc6b4361bce562e3f1a839c5bd7'],
                [3, 'ProductITE3', 12, '1.1', ''],
                [4, 'ProductITE4', 13, '1.1', ''],
                [5, 'ProductOTETest', 0, '1.0', ''],
                [6, 'ProductOTE1', 1, '1.0', ''],
                [7, 'ProductOTE2', 2, '1.0', ''],
                [8, 'ProductOTE3', 3, '1.0', ''],
                [9, 'ProductOTE4', 4, '1.0', ''],
                [14, 'ProductITETest', 9, '1.1', '']
            ]
        )->execute();
    }
 
    /*
    // Use safeUp/safeDown to run migration code within a transaction
    public function safeUp()
    {
    }
 
    public function safeDown()
    {
    }
    */
}

For more details: http://www.yiiframework.com/doc-2.0/yii-db-schemabuildertrait.html

Field Notation (Traditional vs New Notation)

These are the most common field notation.

Method 1:

$this->createTable('{{%customer}}', [
    'id'           => Schema::TYPE_PK,
    'contact'      => Schema::TYPE_STRING   . ' NULL DEFAULT NULL',
    'fax'          => Schema::TYPE_STRING   . '(50) NULL DEFAULT NULL',
    'notes'        => Schema::TYPE_TEXT     . ' NULL',
    'status'       => Schema::TYPE_BOOLEAN  . ' NOT NULL DEFAULT 1',
    'created_at'   => Schema::TYPE_DATETIME . ' NULL DEFAULT NULL',
    'created_by'   => Schema::TYPE_INTEGER  . ' UNSIGNED NULL DEFAULT NULL',
], $tableOptions);

Method 2:

$this->createTable('{{%customer}}', [
    'id'         => $this->primaryKey(),
    'contact'    => $this->string(),
    'fax'        => $this->string(50)->defaultValue('0'),
    'notes'      => $this->text(),
    'status'     => $this->boolean()->notNull()->defaultValue('1'),
    'created_at' => $this->dateTime(),
    'created_by' => $this->integer()->unsigned(),
], $tableOptions);

Schema Traits

Possible schema traits:

Scheme Trait	Description
bigInteger()	Creates a bigint column.
bigPrimaryKey()	Creates a big primary key column.
binary()	Creates a binary column.
boolean()	Creates a boolean column.
char()	Creates a char column.
date()	Creates a date column.
dateTime()	Creates a datetime column.
decimal()	Creates a decimal column.
double()	Creates a double column.
float()	Creates a float column.
integer()	Creates an integer column.
“MEDIUMTEXT”	Creates a mediumtext column.
money()	Creates a money column.
primaryKey()	Creates a primary key column.
smallInteger()	Creates a smallint column.
string()	Creates a string column.
text()	Creates a text column.
time()	Creates a time column.
timestamp()	Creates a timestamp column.
json()	Creates a JSON values column.
tinyInteger()	Creates tinyint column.

For more details, see Yii 2.0 Schema Builder Trait.

Adding Index or Foreign Keys

Adding index and foreign keys after creating a table:

// Foreign Key:
// ALTER TABLE [dbo].[contact] 
//    ADD CONSTRAINT `FK_contact_customer_id` 
//        FOREIGN KEY (`customer_id`) 
//        REFERENCES `customer` (`id`) 
//        ON UPDATE CASCADE ON DELETE CASCADE;
// Usage: $this->addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
$this->addForeignKey(
  'FK_contact_customer_id', '{{%contact}}', 'customer_id', '{{%customer}}', 'id', 'CASCADE', 'CASCADE'
);
 
// Index:
// ALTER TABLE [dbo].[contact] 
//    ADD INDEX `FK_contact_external_customer_id` (`external_customer_id`);
// Usage:  $this->createIndex($name, $table, $columns, $unique = false)
$this->createIndex('FK_contact_external_customer_id', '{{%contact}}', 'external_customer_id');

Operations

Insert

// Insert data as a row
$this->insert('{{%entry}}', [
    "serial_number"     => "15169630", 
    "order_number"      => "0001", 
    "brand"             => "Acme", 
    "product_code"      => "prod_Radiator", 
    "product_data_hash" => "",
    "product_data"      => "<data>No Data Yet</data>",
    "workstation"       => "POS-laptop", 
    "status"            => 1, 
    "created_at"        => "2016-06-21 16:30:03"
]);

Or batch insert:

// USAGE: batchInsert(table_name, column_names, column_values)
$db = Yii::$app->getDb();
$db->createCommand()->batchInsert('{{%product_hash}}', 
    ['id', 'program_name', 'program_value', 'program_version', 'hash'], 
    [
        [1, 'ProductITE1', 10, '1.1', 'e2f1f348359c5e68a6d30f785abfff21'],
        [2, 'ProductITE2', 11, '1.1', '90e24bc6b4361bce562e3f1a839c5bd7'],
        [3, 'ProductITE3', 12, '1.1', ''],
    ]
)->execute();

Update

// Add reference_code to all records
foreach((new Query)->from('price')->each() as $price) {
    $this->update('{{%price}}', ['reference_code' => $price->item_code], ['id' => $price->id]);
}

Delete

// Delete records with a specific reference code
foreach((new Query)->from('price')->each() as $price) {
  // DELETE price WHERE reference_code = 'partUnknown', id = :price_id
  $this->delete('{{%price}}', ['reference_code' => 'partUnknown', 'id' => $price->id]);
}

Create SQL View

Instead of viewing all entry records, you can create a view with a subset of them. For example, if we want all the entry records for brand 'Acme', we would create the 'acme_entry' view as follows:

private function create_view_acme_entry()
{
    // VIEW `acme_entry`  AS  
    //    SELECT `entry`.`id` AS `id`,
    //           `entry`.`order_number` AS `order_number`,
    //           `entry`.`serial_number` AS `serial_number`,
    //           `entry`.`brand` AS `brand`,
    //           `entry`.`product_code` AS `product_code`,
    //           `entry`.`product_data_hash` AS `product_data_hash`,
    //           `entry`.`product_data` AS `product_data`,
    //           `entry`.`workstation` AS `workstation`,
    //           `entry`.`program_version` AS `program_version`,
    //           `entry`.`created_at` AS `created_at`,
    //           `entry`.`updated_at` AS `updated_at` 
    //    FROM `entry` WHERE (`entry`.`brand` = 'Acme') ;
 
    $db = Yii::$app->getDb();
    $db->createCommand()->createView('acme_entry', (new \yii\db\Query())
        ->select([
            'id', 'order_number', 'serial_number', 'brand', 'product_code', 
            'product_data_hash', 'product_data', 'workstation', 
            'program_version', 'created_at', 'updated_at'
        ])
        ->from('entry')
        ->where(['brand' => 'Acme'])
    )->execute();
}

Create SQL Trigger

private function create_trigger_entry_table()
{
    // Create trigger:
    // DELIMITER $$
    // CREATE TRIGGER `entry_BeforeInsert` BEFORE INSERT ON `entry` 
    // FOR EACH ROW BEGIN
    //    /* Fix Version */
    //    IF (NEW.brand = 'Acme' AND NEW.program_version is null) THEN 
    //        SET NEW.program_version = '4.0';
    //    END IF;
    // END;
    // $$
    // DELIMITER ;
    //$createTriggerSql  = "CREATE TRIGGER `{{%entry}}_BeforeInsert` BEFORE INSERT ON {{%entry}} \n";
    $createTriggerSql  = "CREATE TRIGGER `".trim(app\models\Entry::tableName(), "`")."_BeforeInsert` BEFORE INSERT ON {{%entry}} \n";
    $createTriggerSql .= "FOR EACH ROW BEGIN \n";
    $createTriggerSql .= "    IF (NEW.brand = 'Acme' AND NEW.program_version is null) THEN \n";
    $createTriggerSql .= "        SET NEW.program_version = '4.0'; \n";
    $createTriggerSql .= "    END IF; \n";
    $createTriggerSql .= "END; \n";
 
    $this->execute('DROP TRIGGER /*!50032 IF EXISTS */ `'.trim(app\models\Entry::tableName(), "`").'_BeforeInsert`');
    $this->execute($createTriggerSql);
}

Configuring ControllerMap

For migration settings, add the following to the @app/config/console.php file

<?php
//...
 
$config = [
    //...
    'controllerMap' => [
        //---------------------------------------
        // Common migration for app and modules
        //---------------------------------------
        // Eg: yii migrate/create 'app\\migrations\\createUserTable'
        //---------------------------------------
        // Migrate with namespaces
        //'migrate' => [
        //    'class' => 'yii\console\controllers\MigrateController',
        //    'migrationPath' => null, // disable non-namespaced migrations if app\migrations is listed below
        //    'migrationNamespaces' => [
        //        'app\migrations',         // Common migrations for the whole application
        //        'modules\erp\migrations', // Migrations for the ERP module
        //    ],
        //],
        // Migrate with simply migration path
        'migrate' => [
            'class' => 'yii\console\controllers\MigrateController',
            'migrationPath' => [
                '@app/migrations',             // Common migrations for the whole application
                '@app/modules/erp/migrations', // Migrations for the ERP module
            ],
            'useTablePrefix' => true,
        ],
        //---------------------------------------------------
        // Specific migrations (in lieu of common migration)
        //---------------------------------------------------
        // Eg: $ yii migrate-app
        //     $ yii migrate-module-erp
        //     $ yii migrate-rbac
        //---------------------------------------------------
        // // Common migrations for the whole application
        // 'migrate-app' => [
        //     'class' => 'yii\console\controllers\MigrateController',
        //     'migrationNamespaces' => ['app\migrations'],
        //     'migrationTable'      => 'migration_app',
        //     'migrationPath'       => null,
        // ],
        // // Migrations for the ERP module
        // 'migrate-module-erp' => [
        //     'class' => 'yii\console\controllers\MigrateController',
        //     'migrationNamespaces' => ['modules\erp\migrations'],
        //     'migrationTable'      => 'migration_module',
        //     'migrationPath'       => null,
        // ],
        // // Migrations for the specific extension
        // 'migrate-rbac' => [
        //     'class' => 'yii\console\controllers\MigrateController',
        //     'migrationPath'  => '@yii/rbac/migrations',
        //     'migrationTable' => 'migration_rbac',
        // ],
    ],
    'params' => $params,
];
 
//...