= Yii 2 Migrations =

  * Visit documentation [[http://www.yiiframework.com/doc-2.0/guide-db-migrations.html|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:  
<code bash>$ yii migrate/create create_customer_table</code>

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

<code php>
<?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;
    }
}
</code>  

You can run and undo the migration with these commands:
<code bash>
# 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
</code>

== New Notation ==

Another example but using the new schema notation:
<code php>
<?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()
    {
    }
    */
}
</code>

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:
<code php>
$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);

</code>

Method 2:
<code php>
$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);
</code>

== 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 [[http://www.yiiframework.com/doc-2.0/yii-db-schemabuildertrait.html|Yii 2.0 Schema Builder Trait]].

== Adding Index or Foreign Keys ==

Adding index and foreign keys after creating a table:
<code php>
// 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');
</code>


== Operations ==

=== Insert ===
<code php>
// 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"
]);
</code>

Or batch insert:
<code php>
// 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();
</code>

=== Update ===
<code php>
// 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]);
}
</code>

=== Delete ===
<code php>
// 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]);
}
</code>


=== 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:
<code php>
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();
}
</code>

=== Create SQL Trigger ===
<code php>
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);
}
</code>
== Configuring ControllerMap ==
For migration settings, add the following to the ''@app/config/console.php'' file
<code php>
<?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,
];

//...
</code>