yii2.0 Database Migration Tutorial "multiple databases synchronizing data simultaneously" _php instance

This paper describes the method of yii2.0 database migration. Share to everyone for your reference, specific as follows:

Create a migration

Use the following command to create a new migration:

Yii migrate/create <name>

The required parameter name functions as a brief description of the new migration. For example, if this migration is used to add fields to the same table for multiple databases (assuming that each database has a news table), you can use the name Addcolumn_news (the name customization) and run the following command:

Yii migrate/create Addcolumn_news

Note: because the name parameter is used to generate part 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 under the @app/migrations directory. The file contains the following code that declares a migration class m150101_185401_addcolumn_news with 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 return the private function Createdbs () {$dbs = [
  ];
  $dbs _info =\yii:: $app->params[' db '];
  foreach ($dbs _info as $k => $v) {$dbs [$k] = \yii::createobject ($v);
return $dbs;
  //up () The method is to add name,nickname,age,sex,site_id fields to the news table for different databases public function up () {$dbs = $this->createdbs ();
    foreach ($dbs as $v) {//------traverse the lecture field and add it to a different database $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 () This method is the opposite of the up () method, which is the meaning of the Delete field public 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 that inherits from Yii\db\migration. The name of the class is automatically generated in m<yymmdd_hhmmss>_<name> format, where

<YYMMDD_HHMMSS> refers to the UTC time at which the migration command was created.

<Name> the same Name parameter value as you carry out the command.

In the migration class, you should write code that changes the structure of the database in the Up () method. You may also need to write code in the Down () method to restore changes made by the up () method. When you upgrade the database through migration, the up () method will be invoked, and conversely, down () will be invoked. The following code shows how to create a news table by migrating a class:

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 ');
  }

Note: Not all migrations are recoverable. For example, if the up () method deletes a row of data in a table, this data cannot be recovered by the down () method. Sometimes, you may just be too lazy to execute the down () method because it is not so common in restoring database migrations. In this case, you should return false in the down () method to indicate that the migration is unrecoverable.

Ways to access a database

The migrated base class Yii\db\migration provides a complete set of methods for accessing and manipulating the database. You may find that the naming of these methods is 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 identical to the functionality of yii\db\command::createtable ().

The advantage of using the method provided by Yii\db\migration is that you do not need to explicitly create a Yii\db\command instance, and that every method is executed with some useful information to tell us whether the database operation has been completed, And how long they took to complete these operations, and so on.

The following is a list of all these database access methods:

Yii\db\migration::execute (): Executes an SQL statement
Yii\db\migration::insert (): Inserting a single row of data
Yii\db\migration::batchinsert (): Inserting multiple rows of data
Yii\db\migration::update (): Updating data
Yii\db\migration::d elete (): Deleting data
Yii\db\migration::createtable (): Creating a Table
Yii\db\migration::renametable (): Renaming table names
Yii\db\migration::d roptable (): Delete a table
Yii\db\migration::truncatetable (): Empty all data in the table
Yii\db\migration::addcolumn (): Add a field
Yii\db\migration::renamecolumn (): Renaming field names
Yii\db\migration::d ropcolumn (): Deleting a field
Yii\db\migration::altercolumn (): Modifying fields
Yii\db\migration::addprimarykey (): Add a primary key
Yii\db\migration::d ropprimarykey (): Delete a primary key
Yii\db\migration::addforeignkey (): Add a foreign key
Yii\db\migration::d ropforeignkey (): Delete a foreign key
Yii\db\migration::createindex (): Create an index
Yii\db\migration::d ropindex (): Deleting an index

Submit Migration

To upgrade the database to the latest structure, you should use the following command to submit all new migrations:

Yii Migrate

This command lists all uncommitted migrations to date. If you are sure that you need to submit these migrations, it will run the up () or Safeup () method of each new migration class in the order of the timestamp in the class name. If any of the migration submissions fail, the command exits and stops the remaining migrated that have not yet been performed.

For each migration that is successfully committed, this command inserts a record of the application's successful commit migration in a database table called migration that will help the migration tool determine which migrations have been committed and which have not been committed.

tip: The Migration Tool will automatically create a migration table in the database that is specified in the Yii\console\controllers\migratecontroller::d b option of the command. By default, is specified by the 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 bars you want to execute instead of performing all the 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 migration uses timestamps to specify migration
yii migrate/to "2015-01-01 18:5 4:01 "       # Using a string that can is parsed by Strtotime () uses a string to be parsed by Strtotime ()
yii migrate/to m150101_185401_ Create_news_table  # using full name to use the
migrate/to 1392853618             # Use UNIX timestamp using the UNIX time stamp

If there are uncommitted migrations before the migration that is specified to be submitted, these uncommitted migrations will be submitted before the specified migration is performed.

If the migrated specified commits have been submitted before, then those migrations after them will be restored.

Restore migration

You can use the following command to restore one or more of the comments submitted by the migration:

Yii Migrate/down   # Revert the most recently applied migration restore last submitted migration
Yii Migrate/down 3  # revert the M OST 3 recently applied migrations restores the most recent three submitted migrations

Note: Not all migrations can be restored. Attempting to restore such a migration could result in an error or even terminate all of the restore processes.

Redo Migration

Redo migration means that the specified migration is restored first and then submitted again. As shown below:

Yii Migrate/redo    # Redo the last applied migration redo recently submitted migration
Yii Migrate/redo 3   # Redo the 3 applied mi Grations redo the last three submitted migrations

Note: If a migration cannot be restored, then you will not be able to redo it.

List migration

You can use the following command to list those submitted or uncommitted migrations:

Yii migrate/history   # Show last 10 submitted migrations
Yii migrate/history 5  # Show last 5 submitted migrations
Yii migrate/history All # Show all committed migrations
yii migrate/new     # shows the first 10 uncommitted migrations
yii migrate/new 5    # shows the first 5 uncommitted migrated
yii migrate/ New   All # shows all migrated that have not yet been submitted

Modify migration History

Sometimes you may need to simply mark your database as being upgraded to a specific migration, rather than actually committing or restoring a migration. This often happens when you manually change a particular state of the database without the need for a corresponding migration to be submitted repeatedly. Then you can use the following command to achieve your goal:

Yii Migrate/mark 150101_185401           # Use timestamps to specify migration
yii migrate/mark "2015-01-01 18:54:01"       # Use one can be strtotime () Parsed string
yii migrate/mark m150101_185401_create_news_table  # using full name
yii migrate/mark 1392853618             # using UNIX Time stamp

The command adds or deletes a few rows of data from the migration table to indicate that the database has been committed to a specified migration. No migration will be committed or restored during the execution of this command.

Custom migration

There are many ways to customize the migration command.

Using Command line Options

The migration command comes with several command-line options that you can use to customize its behavior:

Interactive:boolean (the default value is True) to specify 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, you should set it to false.

Migrationpath:string (the default value is @app/migrations), specifies the directory where all migrated class files are stored. This option can be either a directory path or a path alias. Note that the specified directory must be selected, or an error will be triggered.

Migrationtable:string (default is migration), specifies the name of the database table used to store migration history information. If this table does not exist, the migration command will automatically create this 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 (the default is db), specifies the ID of the database application component. It refers to the database that will be migrated by the command.

Templatefile:string (defaults to @yii/views/migration.php) specifies the template file path for the production Migration Framework code class file. This option can be specified by using a file path, or by using a path alias. The template file is a PHP script that can use predefined variable $className to get the name of the migrated class.

The following examples show us how to use these options:

For example, if we need to migrate a forum module, which is placed in the Migrations directory under that module, then we can use the following command:

# Migrate in non-interactive mode in the Forum module
yii migrate--migrationpath= @app/modules/forum/migrations--interactive=0

Global configuration Command

It's annoying to have to repeat the same parameters every time you run the migration command, so you can choose to configure the application configuration globally, once and for all:

return ['
  controllermap ' => ['
    migrate ' => ['
      class ' => ' yii\console\controllers\ Migratecontroller ',
      ' migrationtable ' => ' backend_migration ',
    ],],]
;

As shown in the configuration, the Backend_migration table will be used to record migration history each time the migration command is run. You no longer need to specify this history table by migrationtable command-line arguments.

Migrating multiple databases

By default, migrations are submitted to the same database as defined by DB application component. If you need to submit to a different database, you can specify the DB command-line option as follows,

Yii Migrate--DB=DB2

The above command will submit the migration to the DB2 database.

Occasionally a limited time you need to submit some migrated to a database, while others are submitted to another database. To achieve this, you should specify the ID of the database component you want to use when implementing a migration class, as follows:

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 specify a different database using the DB command-line option, the migration will be submitted to the DB2. It should be noted that the historical information for the migration is still logged to the database specified by the DB command-line option.

If you have multiple migrations that use the same database, it is recommended that you create a migrated base class that contains the above init () code. Then each migration class inherits the base class.

tip: You can also manipulate different databases by creating a new database connection in the migration class, in addition to setting the yii\db\migration::d b parameter. These connections then use the DAO method to manipulate the different databases.

Another strategy that allows you to migrate multiple databases is to store the migration in a different directory, and then you can migrate to different databases by using the following command:

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, and the second command will submit the migration under @app/MIGRATIONS/DB2 to the DB2 database, and so on.

For more information on YII-related content, readers who are interested in this site can view the topics: Introduction to YII Framework and summary of common skills, "Summary of PHP Excellent development framework", "Smarty Template Introductory Course", "Introduction to PHP object-oriented programming", "PHP string" Summary of Usage , "Php+mysql Database operation Introduction Tutorial" and "PHP common database Operation Skills Summary"

I hope this article will help you with the PHP program design based on the YII framework.