Yii Framework official guide series New version 27-use database: database migration

Note: yii supports the database migration feature only after version 1.1.6. Like the source code, the database structure continues to grow as we develop and maintain database-driven applications. for example, during development, we may want to add a new one...

Note: yii supports the database migration feature only after version 1.1.6.

Like the source code, the database structure 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 an index needs to be added to a column. tracking the changes in these database structures (called migration) is just as important as operating the source code. if the source code is not synchronized with the database, the system may be interrupted. For this reason, the Yii Framework provides database migration tools to track database migration history, migrate new applications, or restore old ones.

The following steps show how to use database migration during development:
Tim adds a new migration (e.g. create a new table)
Tim submits a new version migration tool (e.g. SVN, GIT)
Doug updates and retrieves a new migration from the version control tool
Doug application migration to a locally developed database

The Yii Framework supports database migration through the yiic migrate command line tool. this tool supports creating new migration, application/recovery/cancellation migration, and displaying the migration history and new migration.

Next, we will describe how to use this tool.

Note: it is best to use the yiic (e.g. cd path/to/protected) instead of. make sure you have the protected \ migrations folder and it is writable. also check in protected/config/console. whether the database connection is configured in php.

1. create a migration

To create a new migration (for example, creating a news table), run the following command:

yiic migrate create 
 

The parameter name is required and specifies a brief description of the migration (e.g. create_news_table). as we will show below, the name parameter is part of the PHP class name. 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 protected/migrations path, which 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 that the class name and file name are both m _ Mode, where It indicates the UTC timestamp (format: yymmdd_hhmmss) when the migration is created. Is obtained from the command name parameters.

The up () method should contain the code for database migration, while the down () method contains the code for restoring the operations in the up () method.

Sometimes, operations in down () are impossible. For example, if you delete a row of a table in the up () method, you cannot restore the row in the down method. In this case, 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 the following migration will be canceled. In version 1.1.6, an exception must be thrown to cancel the following migration.

Let's use an example to demonstrate how to create a news table migration.

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 creates a database table, and CDbMigration: insert inserts a row of data. These methods use the database connection returned by CDbMigration: getDbConnection (). The default value is Yii: app ()-> db.

Info: you may notice that the database methods provided by CDbMigration are similar to those in CDbCommand. Indeed, they are basically the same, except that the CDbMigration method calculates the time consumed for execution and prints information about some method parameters.

You can also extend the database operation methods, such:

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: transaction migration features are supported starting from version 1.1.7.

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

We can explicitly start database transactions and attach code related to other databases that contain transactions, 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 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 the migration, it will enable the database migration and then call safeUp () or safeDown (). if any errors occur between safeUp () and safeDown (), the transaction will be rolled back to ensure database consistency and integrity.

Note: not all DBMS support transactions. some DB queries cannot be placed in transactions. in this case, you can implement up () and down () instead. for MySQL, some SQL statements may cause conflicts.

3. use migration

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

yiic migrate

This command displays a list of all new migrations. if you are sure to use migration, it will run the up () method in each new migration class, one after another, in the order of timestamps in the class name.

After migration is used, the migration tool will write a record in a data table tbl_migration-allow the tool to identify which migration is applied. if the tbl_migration table does not exist, the tool will automatically create it in the database specified by the db in the configuration file.

Sometimes, we may point to one or more application migrations. you can run the following command:

yiic migrate up 3

This command will run three new migrations. the value 3 of the table value will allow us to change the number of migrations to be applied.

You can also use the following command to migrate the database to a specified version:

yiic migrate to 101129_185401

That is, we use the timestamp in the database migration name to specify the version of the database we want to migrate. If there are multiple migrations between the last application database migration and the specified migration, all these migrations will be applied. if the specified migration has already been used, all subsequent migration will be restored.

4. restore migration

To restore the last or several migrated applications, run the following command:

yiic migrate down [step]

The option step parameter specifies the number of migration to be restored. the default value is 1, which means that the migration of the last application is restored.

As we described earlier, not all migration can be recovered. attempting to recover this migration will throw an exception and stop the entire recovery process.

5. redo migration

Redo migration means that the migration specified by the application will be restored for the first time and then completed. this can be achieved through the following command:

yiic migrate redo [step]

The optional step parameter specifies how many migrations are redone. the default value is 1, which means that the last migration is redone.

6. display migration information

In addition to application and recovery migration, the migration tool also displays the migration history and the new migration of the application.

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

The optional parameter limit specifies the number of migration records displayed by the gram. If limit is not specified, all valid migration will be displayed.

The first command shows that the application has been migrated, while the second command shows that the application has not been migrated.

7. edit the migration history

Sometimes, we may want to edit the migration history to specify the migration version when there is no application or the migration is restored. this usually happens when a new migration is developed. we use the following command to achieve this goal.

yiic migrate mark 101129_185401

This command is very similar to the yiic migrate to command, but it only edits the migration history table to the specified version without applying or restoring the migration.

8. Custom migration commands

There are multiple methods to customize migration commands.

Use command line options

The migration command must specify four options in the command line:

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.

To specify these options, run the migration command in the following format:

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

For example, if you want to migrate a Forum module, all its migration files are placed in the migration folder of the module, you can use the following command:

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

Note that when you set a boolean option such as interactive, use the following method to pass 1 or 0 to the command line:

yiic migrate --interactive=0

Configure global commands

The command line option allows us to quickly configure the migration command, but sometimes we may want to configure only one command. for example, we may want to use different tables to save the migration history, or we want to use a custom migration template. You can edit the configuration file of the console application as follows:

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

Now, if we run the migration command, the above configuration will take effect without entering so many options in the command line every time.

The above is the version 27 of the Yii Framework official guide series-database usage: database migration content. For more information, see The PHP Chinese website (www.php1.cn )!