Yii Migration Example With Safe Up

Yii 1.1 Tips, Snippets and Tutorials
1 

public function safeUp()

    {

        $this->execute("SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;");

        $this->execute("SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;");

        $this->execute("SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';");

        $this->execute('CREATE SCHEMA IF NOT EXISTS `sms-log` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;');


        $this->createTable('tbl_user_login', array(

                'uuid' => "CHAR(36) NOT NULL",

                'username' => "VARCHAR(225) NOT NULL COMMENT 'username or email address for login'",

                'password' => "TEXT NOT NULL COMMENT 'encrypted key password'",

                'created_date' => "DATETIME NOT NULL COMMENT 'user account created on date'",

                'mobile' => "INT(12) NOT NULL COMMENT 'mobile number for mobile confirmation and to send notification'",

                'is_active' => "BIT(1) NOT NULL COMMENT 'to check whether confirm their details or not'",

                'blocked' => "BIT(1) NULL COMMENT 'if user try more then 5 time to login with wrong details'",

                'bocked_time' => "DATETIME NULL COMMENT 'store user blocked time'",

                'failed_login_attempts' => "INT(1) NULL COMMENT 'failed login attempt counting'",

                'PRIMARY KEY (`uuid`)',

            ), 'ENGINE=InnoDB');


        $this->execute("SET SQL_MODE=@OLD_SQL_MODE;");

        $this->execute("SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;");

        $this->execute("SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;");

    }
2

read this article:

http://www.yiiframework.com/doc/guide/1.1/en/database.migration#transactional-migrations

Transaction support is to implement the safeUp() method instead of up(), and safeDown() instead of down(). For example,

class m101129_185401_create_news_table extends CDbMigration

{

public function safeUp()


{


    $this->createTable('tbl_news', array(


        'id' => 'pk',


        'title' => 'string NOT NULL',


        'content' => 'text',


    ));


}





public function safeDown()


{


    $this->dropTable('tbl_news');


}

}

When Yii performs the migration, it will start a DB transaction and then call safeUp() or safeDown(). If any DB error occurs in safeUp() or safeDown(), the transaction will be rolled back, thus ensuring the database remain in a good shape.

3

I have tried safeUp() — it doesn’t work.

	public function safeUp()
	{
		$table = '{{products}}';
		$column = 'color_set';
		$type = "SET('white', 'lightgray', 'gray', 'darkgray', 'black', "
			. "'red', 'orange', 'gold', 'yellow', 'chartreuse', 'green', "
			. "'aquamarine', 'cyan', 'dodgerblue', 'blue', 'mediumpurple', "
			. "'purple', 'magenta', 'deeppink', 'crimson', 'darkred', "
			. "'chocolate', 'olive', 'darkgreen', 'teal', 'darkblue', "
			. "'indigo', 'darkmagenta', 'beige', 'pink')";

		$time = microtime(true);
		$this->addColumn($table, $column, $type);

		$pairs = [
			"שחור" => "black",
			"אפור" => "gray",
			"אדום \\ לבן" => "red, white",
                        // skipped
		];

		foreach ($pairs as $hebrew => $english) {
			$this->update(
				$table,
				[ $column => $english ],
				'color = :color',
				[ ':color' => $hebrew ],
			);
		}
	}

This migration adds a column and then tries to update it. When errors appeared, migration are terminated but transaction not rolled back, new column remains in the table.

These operations was performed on InnoDB table in MySQL 5.7. I suppose that this engine supports transactions.

UPDATE: I’ve fixed that script, it performs queries without errors but I had to replace safeUp() with up() because I’ve got an exception after last query have been executed:

Exception: There is no active transaction