This article describes the method of yii2.0 database migration. Share it for your reference, as follows:
Create a migration
Use the following command to create a new migration:
yii migrate/create <name>
The function of the required parameter name is to give a brief description of the new migration. For example, if this migration is used to add fields to multiple databases (assuming each database has a news table), then you can use the name addColumn_news (customized by this name) and run the following command:
yii migrate/create addColumn_news
Notice:Because the name parameter is used to generate a portion of the migrated class name, the parameter should contain only letters, numbers, and underscores.
The above command will create a new PHP class file named m150101_185401_addColumn_news.php in the @app/migrations directory. This file contains the following code, which is used to declare a migration class m150101_185401_addColumn_news, and is accompanied by a code framework:
<?php
use yii\db\Schema;
use yii\db\Migration;
class m150101_185401_addColumn_news extends Migration
{
//createDbs This method is to get the database object to returnprivate function createDbs(){
$dbs = [];
$dbs_info =\Yii::$app->params['db'];
foreach($dbs_info as $k=>$v){
$dbs[$k] = \Yii::createObject($v);
}
return $dbs;
}
//up() This method is to add fields such as name, nickname, age, sex, site_id to the news table of different databases.public function up()
{
$dbs = $this->createDbs();
foreach($dbs as $v){ //《---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- $this->db=$v;
$this->addColumn('{{%news}}','name','varchar(20)');
$this->addColumn('{{%news}}','nickname','varchar(20)');
$this->addColumn('{{%news}}','age','int(3)');
$this->addColumn('{{%news}}','sex','int(1)');
$this->addColumn('{{%news}}','site_id','int(5)');
}
}
//down() The method is opposite to the up() method, which means to delete the fieldpublic function down()
{
$dbs = $this->createDbs();
foreach($dbs as $v){
$this->db=$v;
$this->dropColumn('{{%news}}','name','varchar(20)');
$this->dropColumn('{{%news}}','nickname','varchar(20)');
$this->dropColumn('{{%news}}','age','int(3)');
$this->dropColumn('{{%news}}','sex','int(1)');
$this->dropColumn('{{%news}}','site_id','int(5)');
}
}
}
Each database migration is defined as a PHP class inherited from yii\db\Migration. The class name is automatically generated in the format of m<YYMMDD_HHMMSS>_<Name>, where
<YYMMDD_HHMMSS> refers to the UTC time when the creation migration command is executed.
<Name> is the same as the name parameter value you took when executing the command.
In the migration class, you should write code to change the database structure in the up() method. You may also need to write code in the down() method to recover the changes made by the up() method. When you upgrade the database through migration, the up() method will be called, and conversely, down() will be called. The following code shows how to create a news table by migrating classes:
use yii\db\Schema;
use yii\db\Migration;
class m150101_185401_create_news_table extends \yii\db\Migration
{
public function up()
{
$this->createTable('news', [
'id' => Schema::TYPE_PK,
'title' => Schema::TYPE_STRING . ' NOT NULL',
'content' => Schema::TYPE_TEXT,
]);
}
public function down()
{
$this->dropTable('news');
}
}
Notice:Not all migrations are recoverable. For example, if the up() method deletes a row of data in the table, this will not be restored by the down() method. Sometimes, you may just be too lazy to execute the down() method because it is not so generic when it comes to restoring database migrations. In this case, you should return false in the down() method to indicate that the migration cannot be restored.
How to access the database
The migration base class yii\db\Migration provides a complete set of methods to access and operate databases. You may find that the naming of these methods is very similar to the DAO method provided by the yii\db\Command class. For example, the yii\db\Migration::createTable() method can create a new table, which is exactly the same as the function of yii\db\Command::createTable().
The advantage of using the methods provided by yii\db\Migration is that you no longer need to explicitly create yii\db\Command instances, and when executing each method, some useful information will be displayed to tell us whether the database operations have been completed, how long it took them to complete them, etc.
Here is a list of all these database access methods:
yii\db\Migration::execute(): Execute a SQL statement
yii\db\Migration::insert(): Insert a single line of data
yii\db\Migration::batchInsert(): Insert multiple rows of data
yii\db\Migration::update(): Update data
yii\db\Migration::delete(): Delete data
yii\db\Migration::createTable(): Create table
yii\db\Migration::renameTable(): Rename table name
yii\db\Migration::dropTable(): Delete a table
yii\db\Migration::truncateTable(): Clear all data in the table
yii\db\Migration::addColumn(): Add a field
yii\db\Migration::renameColumn(): Rename field name
yii\db\Migration::dropColumn(): Delete a field
yii\db\Migration::alterColumn(): Modify the field
yii\db\Migration::addPrimaryKey(): Add a primary key
yii\db\Migration::dropPrimaryKey(): Delete a primary key
yii\db\Migration::addForeignKey(): Add a foreign key
yii\db\Migration::dropForeignKey(): Delete a foreign key
yii\db\Migration::createIndex(): Create an index
yii\db\Migration::dropIndex(): Delete an index
Submit migration
In order to upgrade the database to the latest structure, you should submit all new migrations using the following command:
yii migrate
This command lists all uncommitted migrations so far. If you are sure you need to submit these migrations, it will run the up() or safeUp() method in each new migration class one after another in the order of timestamps in the class name. If any of the migrations fails, the command will exit and stop the remaining migrations that have not been executed.
For each successfully submitted migration, this command inserts a record containing the application's successful submission of migration in a database table called migration. This record will help the migration tool determine which migration has been submitted and which have not been submitted.
hint:The migration tool will automatically create a migration table in the database, which is specified in the yii\console\controllers\MigrateController::db option of this command. By default, it is specified by db application component.
Sometimes, you may only need to submit one or a few migrations, and you can use this command to specify the number of pieces you need to perform instead of performing all available migrations. For example, the following command will attempt to commit the first three available migrations:
yii migrate 3
You can also specify a specific migration, using the migrate/to command in the following format to specify which migration the database should submit:
yii migrate/to 150101_185401 # using timestamp to specify the migrationyii migrate/to "2015-01-01 18:54:01" # using a string that can be parsed by strtotime()yii migrate/to m150101_185401_create_news_table # using full nameyii migrate/to 1392853618 # using UNIX timestamp using UNIX timestamp
If there are uncommitted migrations before specifying the migration to be submitted, these uncommitted migrations will be committed first before performing the specified migration.
If the migrations submitted by the specified have been committed before, those migrations after that will be restored.
Restore and migration
You can use the following command to restore one or more of the comments to be submitted to the migration:
yii migrate/down # revert the most recently applied migrationyii migrate/down 3 # revert the most 3 recently applied migrations
Notice:Not all migrations can be restored. Attempting to restore such migrations may result in an error or even terminate all restore processes.
Rework migration
Redo migration means to restore the specified migration first and then submit it again. As shown below:
yii migrate/redo # redo the last applied migrationyii migrate/redo 3 # redo the last 3 applied migrations
Notice:If a migration cannot be restored, you will not be able to redo it.
List migrations
You can use the following command to list those submitted or not yet submitted migrations:
yii migrate/history # Show the last 10 submissionsyii migrate/history 5 # Show the last 5 submissionsyii migrate/history all # Show all submitted migrationsyii migrate/new # Show the first 10 migrations that have not been submitted yetyii migrate/new 5 # Show the first 5 migrations that have not been submitted yetyii migrate/new all # Show all migrations that have not been submitted yet
Modify the migration history
Sometimes you may need to simply mark your database has been upgraded to a specific migration, rather than actually committing or restoring the migration. This often happens when you manually change a specific state of the database without wanting the corresponding migration to be repeatedly submitted. Then you can use the following command to achieve your goal:
yii migrate/mark 150101_185401 # Use timestamps to specify migrationyii migrate/mark "2015-01-01 18:54:01" # Use a string that can be parsed by strtotime()yii migrate/mark m150101_185401_create_news_table # Use full nameyii migrate/mark 1392853618 # Use UNIX timestamps
This command will add or delete some rows of data in the migration table to indicate that the database has been submitted to a specified migration. No migrations will be committed or restored during execution of this command.
Custom migration
There are many ways to customize migration commands.
Use command line options
The migration command comes with several command line options that can be customized with customization of its behavior:
interactive: boolean (default is true), specifying whether to run the migration in interactive mode. When set to true, the user is prompted before the command performs certain operations. If you want to execute the command in the background, then you should set it to false.
migrationPath: string (the default value is @app/migrations), specifying the directory where all migration class files are stored. This option can be a path to a directory or a path alias. It should be noted that the specified directory must exist, otherwise an error will be triggered.
migrationTable: string (default is migration), specifying the name of the database table used to store migration history information. If this table does not exist, the migration command will automatically create the table. Of course you can also use such a field structure: version
varchar(255) primary key, apply_time integer to create this table manually.
db: string (default is db), specifying the ID of the database application component. It refers to the database that will be migrated by this command.
templateFile: string (defaults to @yii/views/), specifying the template file path of the production migration framework code class file. This option can be specified using file path or path alias. This template file is a PHP script that can use the predefined variable $className to get the name of the migration class.
The following example shows us how to use these options:
For example, if we need to migrate a forum module and the migration file is placed in the migrations directory under the module, then we can use the following command:
# Migrate in non-interactive mode in forum moduleyii migrate --migrationPath=@app/modules/forum/migrations --interactive=0
Global configuration commands
It will be annoying to repeatedly enter some of the same parameters when running the migration command. At this time, you can choose to configure the global configuration in the application configuration, once and for all:
return [
'controllerMap' => [
'migrate' => [
'class' => 'yii\console\controllers\MigrateController',
'migrationTable' => 'backend_migration',
],
],
];
As configured as shown above, the backend_migration table will be used to record the migration history each time the migration command is run. You no longer need to specify this historical record table through the migrationTable command line parameter.
Migrate multiple databases
By default, migrations will be submitted to the same database defined by the db application component. If you need to submit to a different database, you can specify the db command line option as shown below.
yii migrate --db=db2
The above command will submit the migration to the db2 database.
Occasionally, there are limited times when you need to commit some to migrate to one database and others to another database. To achieve this, you should specify the ID of the database component you need when implementing a migration class, as shown below:
use yii\db\Schema;
use yii\db\Migration;
class m150101_185401_create_news_table extends Migration
{
public function init()
{
$this->db = 'db2';
parent::init();
}
}
Even if you use the db command line option to specify another different database, the above migration will still be submitted to db2. It should be noted that the historical information of the migration will still be recorded in the database specified by the db command line option.
If multiple migrations use the same database, it is recommended that you create a migration base class containing the above init() code. Then each migration class inherits this base class.
hint:In addition to setting it in the yii\db\Migration::db parameter, you can also operate on different databases by creating new database connections in the migration class. Then use the DAO method to operate different databases through these connections.
Another strategy that allows you to migrate multiple databases is to store the migration to different directories, and then you can migrate different databases separately through the following commands:
yii migrate --migrationPath=@app/migrations/db1 --db=db1 yii migrate --migrationPath=@app/migrations/db2 --db=db2 ...
The first command will submit the migration under the @app/migrations/db1 directory to the db1 database, the second command will submit the migration under the @app/migrations/db2 to the db2 database, and so on.
For more information about Yii, readers who are interested in this site can view the topic:Yii framework introduction and common techniques summary》、《Summary of excellent development framework for php》、《Basic tutorial on getting started with smarty templates》、《PHP object-oriented programming tutorial》、《Summary of usage of php strings》、《PHP+mysql database operation tutorial"and"Summary of common database operation techniques for php》
I hope that this article will be helpful to everyone's PHP programming based on the Yii framework.