Yii Framework Official Guide Series Supplement 27 - Working with Databases: Database Migration

Note: Yii only supports the database migration feature from version 1.1.6 onwards.

Like the source code, the structure of the database continues to grow as we develop and maintain database-driven applications. For example, during development, we may want to add a new table; or after the application is put into production , we may realize that we need to add an index on a certain column. Tracking these changes to the database structure (called migrations) is as important as operating the source code. If the source code and the database are out of sync, the system may be interrupted. It is for this reason that the Yii framework provides database migration tools in order to track database migration history, apply new migrations, or restore old migrations.

The following steps show how to use database migrations during development:
Tim adds a new migration (e.g. create a new table)
Tim submits a new migration to the version control tool (e.g. SVN, GIT)
Doug updates and takes out a new migration from the version control tool
Doug Apply new migrations to a local development version of the database

The Yii framework supports database migration through the yiic migrate command line tool. This tool supports creating new migrations, applying/reverting/cancelling migrations, and displaying migrations Historical and new migrations.

Next, we will describe how to use this tool.

Note: When migrating using the command line migration tool it is best to use the yiic in the application directory (e.g. cd path/to/protected) instead of the system directory. Make sure you have the protected\migrations folder and it It is writable. Also check whether the database connection is configured in protected/config/console.php.

1. Create a migration

If we want to create a new migration (for example, create a news table), we can run the following command:

yiic migrate create <name>

The parameter name is required. Specifies a very brief description about this migration (e.g. create_news_table). As we will show below, the name parameter is part of the PHP class name. And it can only contain letters, numbers and underscores.

yiic migrate create create_news_table

The above command will create a new file named m101129_185401_create_news_table.php under the path protected/migrations. The file contains the following code:

class m101129_185401_create_news_table extends CDbMigration
{
    public function up(){}

    public function down()
    {
        echo "m101129_185401_create_news_table does not support migration down.\n";
        return false;
    }

    /*
    // implement safeUp/safeDown instead if transaction is needed
    public function safeUp(){}

    public function safeDown(){}
    */
}

Note the class name and file name The same, both are m_ mode, where represents the UTC timestamp when the migration was created (in the format of yymmdd_hhmmss), and is obtained from the named parameter of the command.

The up() method should contain the code to implement database migration, while the down() method contains code to restore the operations in the up() method.

Sometimes, it is impossible to implement the operations in down(). For example, if a row of the table is deleted in the up() method, it cannot be restored in the down method. In this case, the migration is called irreversible, which means we cannot roll back to the previous state of the database. In the code generated above, the down() method returns false to indicate that the migration is irreversible.

Info: Starting from version 1.1.7, if the up() or down() method returns false, all migrations below will be cancelled. In version 1.1.6, an exception must be thrown to cancel the following migration.

Let us use an example to show the migration of creating a news table.

class m101129_185401_create_news_table extends CDbMigration
{
    public function up()
    {
        $this->createTable('tbl_news', array(
            'id' => 'pk',
            'title' => 'string NOT NULL',
            'content' => 'text',
        ));
    }

    public function down()
    {
        $this->dropTable('tbl_news');
    }
}

The base class CDbMigration provides a series of methods to operate data and databases. For example, CDbMigration::createTable will create In the database table, CDbMigration::insert will insert a row of data. These methods all use the database connection returned by CDbMigration::getDbConnection(), which defaults to Yii::app()->db.

Info: You may notice that the database methods provided by CDbMigration are very similar to those in CDbCommand. Indeed, they are basically the same, except that the CDbMigration method calculates the time it takes to execute and prints some information about the method parameters.

You can also extend the method of operating the database, such as:

public function up()
{
    $sql = "CREATE TABLE IF NOT EXISTS user(
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(32) NOT NULL,
        password VARCHAR(32) NOT NULL,
        email VARCHAR(32) NOT NULL
    ) ENGINE=MyISAM";
    $this->createTableBySql('user',$sql);
}
public function createTableBySql($table,$sql){
    echo " > create table $table ...";
    $time=microtime(true);
    $this->getDbConnection()->createCommand($sql)->execute();
    echo " done (time: ".sprintf('%.3f', microtime(true)-$time)."s)\n";
}

2. Transaction migration

Info: The feature of transaction migration is supported starting from version 1.1.7.

In complex database migrations, we often want to ensure that each migration is successful or failed so that the database maintains consistency and integrity. To achieve this goal we can make use of database transactions.

We can explicitly enable database transactions and attach other database-related transaction-containing code, for example:

class m101129_185401_create_news_table extends CDbMigration
{
    public function up()
    {
        $transaction=$this->getDbConnection()->beginTransaction();
        try
        {
            $this->createTable('tbl_news', array(
                'id' => 'pk',
                'title' => 'string NOT NULL',
                'content' => 'text',
            ));
            $transaction->commit();
        }catch(Exception $e){
            echo "Exception: ".$e->getMessage()."\n";
            $transaction->rollback();
            return false;
        }
    }

    // ...similar code for down()
}

However, a simpler The way to obtain transaction support is to implement the safeUp() method to replace up(), and safeDown() to replace 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 executes migration, it will start database migration and then call safeUp() or safeDown(). If any errors occur in safeUp() and safeDown(), the transaction will be rolled back to ensure that the database maintains consistency and integrity.

Note: Not all DBMS support this Transaction. And some DB queries cannot be placed in a transaction. In this case, you must implement up() and down() instead. For MySQL, some SQL statements will cause conflicts.

3 .Using Migrations

To use all valid new migrations (i.e., make the local database up-to-date), run the following command:

yiic migrate

这个命令会显示所有新迁移的列表. 如果你确定使用迁移, 它将会在每一个新的迁移类中运行up()方法, 一个接着一个, 按照类名中的时间戳的顺序.

在使用迁移之后, 迁移工具会在一个数据表tbl_migration中写一条记录——允许工具识别应用了哪一个迁移. 如果tbl_migration表不存在 ，工具会在配置文件中db指定的数据库中自动创建。

有时候, 我们可能指向应用一条或者几条迁移. 那么可以运行如下命令:

yiic migrate up 3

这个命令会运行3个新的迁移. 该表value的值3将允许我们改变将要被应用的迁移的数目。

我们还可以通过如下命令迁移数据库到一个指定的版本:

yiic migrate to 101129_185401

也就是我们使用数据库迁移名中的时间戳部分来指定我们想要迁移到的数据库的版本。如果在最后应用的数据库迁移和指定的迁移之间有多个迁移, 所有这些迁移都会被应用. 如果指定迁移已经使用过了, 所有之后应用的迁移都会恢复。

4. 恢复迁移

想要恢复最后一个或几个已应用的迁移，我们可以运行如下命令：

yiic migrate down [step]

其中选项 step 参数指定了要恢复的迁移的数目. 默认是1, 意味着恢复最后一个应用的迁移.

正如我们之前所描述的, 不是所有的迁移都能恢复. 尝试恢复这种迁移会抛出异常并停止整个恢复进程。

5. 重做迁移

重做迁移意味着第一次恢复并且之后应用指定的迁移. 这个可以通过如下命令来实现:

yiic migrate redo [step]

其中可选的step参数指定了重做多少个迁移 . 默认是1, 意味着重做最后一个迁移.

6. 显示迁移信息

除了应用和恢复迁移之外, 迁移工具还可以显示迁移历史和被应用的新迁移。

yiic migrate history [limit]
yiic migrate new [limit]

其中可选的参数 limit 指定克显示的迁移的数目。如果limit没有被指定，所有的有效迁移都会被显示。

第一个命令显示已经被应用的迁移, 而第二个命令显示还没被应用的迁移。

7. 编辑迁移历史

有时候, 我们可能想要在没有应用和恢复相应迁移的时候编辑迁移历史来指定迁移版本. 这通常发生在开发一个新的迁移的时候. 我们使用下面的命令来实现这一目标.

yiic migrate mark 101129_185401

这个命令和yiic migrate to命令非常类似, 但它仅仅只是编辑迁移历史表到指定版本而没有应用或者恢复迁移。

8. 自定义迁移命令

有多种方式来自定义迁移命令。

使用命令行选项

迁移命令需要在命令行中指定四个选项:

interactive: boolean, specifies whether to perform migrations in an interactive mode. Defaults to true, meaning the user will be prompted when performing a 
specific migration. You may set this to false should the migrations be done in a background process.

migrationPath: string, specifies the directory storing all migration class files. This must be specified in terms of a path alias, and the corresponding 
directory must exist. If not specified, it will use the migrations sub-directory under the application base path.

migrationTable: string, specifies the name of the database table for storing migration history information. It defaults to tbl_migration. The table 
structure is version varchar(255) primary key, apply_time integer.

connectionID: string, specifies the ID of the database application component. Defaults to 'db'.

templateFile: string, specifies the path of the file to be served as the code template for generating the migration classes. This must be specified in terms of a 
path alias (e.g. application.migrations.template). If not set, an internal template will be used. Inside the template, the token {ClassName} will be replaced 
with the actual migration class name.

想要指定这些选项, 使用如下格式的迁移命令执行即可：

yiic migrate up --option1=value1 --option2=value2 ...

例如, 如果我们想要迁移一个论坛模块，它的迁移文件都放在模块的迁移文件夹中，可以使用如下命令:

yiic migrate up --migrationPath=ext.forum.migrations

注意在你设置布尔选项如interactive的时候，使用如下方式传入1或者0到命令行:

yiic migrate --interactive=0

配置全局命令

命令行选项允许我们快速配置迁移命令, 但有时候我们可能想要只配置一次命令. 例如, 我们可能想要使用不同的表来保存迁移历史, 或者我们想要使用自定义的迁移模板。我们可以通过编辑控制台应用的配置文件来实现，如下所示:

return array(
    ......
    'commandMap'=>array(
        'migrate'=>array(
            'class'=>'system.cli.commands.MigrateCommand',
            'migrationPath'=>'application.migrations',
            'migrationTable'=>'tbl_migration',
            'connectionID'=>'db',
            'templateFile'=>'application.migrations.template',
        ),
        ......
    ),
    ......
);

现在如果我们运行迁移命令，上述配置将会生效，而不需要我们每一次在命令行中都输入那么多选项信息。

以上就是Yii框架官方指南系列增补版27——使用数据库：数据库迁移的内容，更多相关内容请关注PHP中文网（www.php.cn）！