Yii Framework Official Guide Series Supplemental Edition 27--use database: Database migration

Note: Yii does not start to support database migration features until after version 1.1.6.

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 might want to add a new table; Or, after the application has been put into production, we might realize that we need to add an index to a column. It is important to track changes in these database structures (known as migrations) and to manipulate source code. If the source and database are out of sync, the system may be interrupted. For this reason, the YII framework provides a database migration tool to track the history of database migrations, 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 version control tool (e.g. SVN, GIT)
Doug updates and takes out a new migration from the version control tool
Doug applies a new migration to a locally developed version of the database

The YII framework supports database migrations through the YIIC Migrate command-line tool. This tool supports creating new migrations, applying/resuming/canceling migrations, and showing the migration history and new migrations.

Next, we'll describe how to use this tool.

Note: When migrating using the command line migration Tool, it is best to use YIIC (e.g. CD path/to/protected) under the application directory instead of the system directory. Make sure that you have the Protected\migrations folder and that it is writable. Also check that the database connection is configured in protected/config/console.php.

1. Create a migration

To create a new migration (such as creating a news table), we can run the following command:

YIIC Migrate Create <name>

The parameter name is mandatory and specifies a very brief description of the migration (e.g. create_news_table). As we'll 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 Path Protected/migrations, 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 s Afedown () {}    */}

Note that this class name, like the file name, is m<timestamp>_<name> mode, where <timestamp> represents the UTC timestamp (in the format YYMMDD_HHMMSS) when the migration was created, and <name > is obtained from the command's named parameters.

The up () method should contain code that implements the migration of the database, while the code contained in the Down () method is used to restore the actions in the up () method.

Sometimes, it is not possible to implement the actions in the down (). For example, if you delete a row of a table in the Up () method, you cannot recover from the down method. In this case, the migration is called irreversible, which means that we cannot roll back to the previous state of the database. In the generated code above, the down () method returns false to indicate that the migration is irreversible .

Info: Starting with version 1.1.7, if the up () or down () method returns False, all of the following migrations will be canceled. In version 1.1.6, you must throw an exception to cancel the migration below.

Let's 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 manipulate data and databases, for example, Cdbmigration::createtable creates a database table, and Cdbmigration::insert inserts a row of data. These methods all use the database connection returned by Cdbmigration::getdbconnection (), which is Yii::app ()->db by default.

Info: You may have noticed 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 elapsed time of execution and prints information about some method parameters.

You can also extend the methods of manipulating the database, such as:

Public function up () {    $sql = ' CREATE TABLE IF not ' EXISTS user (        ID INT not NULL PRIMARY KEY auto_increment, use        Rname varchar (+) NOT NULL,        password varchar (+) NOT NULL,        email varchar (+) 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 nature of transactional migration is supported from version 1.1.7 onwards.

In complex database migrations, we often want to ensure that each migration succeeds or fails, so that the database remains consistent and intact. In order to achieve this goal we can take advantage of database transactions.

We can explicitly open the database transaction and enclose other database-related code that contains the transaction, 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 get transactional 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, the database migration is turned on and then called Safeup () or Safedown (). If any errors occur with Safeup () and Safedown (), the transaction is rolled back to ensure that the database remains consistent and intact.

Note: Not all DBMS support transactions. And some DB queries cannot be placed in a transaction. In this case, you replace the UP () and down (). For MySQL, some SQL statements can 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 the other, according to the order of timestamps in the class name.

After using migration, the migration Tool writes a record in a data table tbl_migration-allowing the tool to identify which migration is applied. If the Tbl_migration table does not exist, the tool is automatically created in the database specified in the DB in the configuration file.

Sometimes we may point to applying one or several migrations. Then you can run the following command:

Yiic Migrate up 3

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

We can also migrate the database to a specific version using the following command:

YIIC Migrate to 101129_185401

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

4. Resume Migration

To recover the last or several applied migrations, we can run the following command:

YIIC migrate down [step]

Where the option step parameter specifies the number of migrations to be recovered. The default is 1, which means that the migration of the last app is resumed.

As we described earlier, not all migrations can be restored. Attempting to recover this migration throws an exception and stops the entire recovery process.

5. Redo the migration

A redo migration means that the first recovery is resumed and the specified migration is applied afterwards. This can be done by the following command:

YIIC migrate redo [step]

One of the optional step parameters specifies how many migrations to redo. The default is 1, which means to focus on the last migration.

6. Display migration Information

In addition to application and recovery migrations, the Migration Tool can also show the migration history and new migrations that are applied.

YIIC Migrate History [LIMIT]YIIC migrate new [limit]

The optional parameter limit specifies the number of migrations that the gram displays. If limit is not specified, all valid migrations will be displayed.

The first command shows the migration that has been applied, and the second command shows the migration that has not yet been applied.

7. Edit the migration history

Sometimes, we might want to edit the migration history to specify a migration version without applying and recovering the migration. This usually happens when developing a new migration. 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 simply edits the migration history table to the specified version without applying or resuming the migration.

8. Custom Migration Commands

There are several ways to customize the migration command.

Using command-line options

The migration command requires that you specify four options on the command line:

Interactive:boolean, specifies whether to perform migrations on an interactive mode. Defaults to True, meaning the user is prompted when performing a specific migration. Should the migrations be do in a background process.migrationPath:string, specifies the Direc Tory storing all migration class files. This must is specified in terms of a path alias, and the corresponding directory must exist. If not specified, it'll use the migrations sub-directory under the application base path.migrationTable:string, Specifi Es 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 being served as the code template for generating the Migration classes. This must is specified in terms of a path alIAS (e.g. application.migrations.template). If not set, an internal template would be used. Inside the template, the token {ClassName} is replaced with the actual migration class name.

To specify these options, you can do this using the migration command in the following format:

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

For example, if we want to migrate a forum module, its migration files are placed in the module's Migration folder, you can use the following command:

Yiic Migrate up--migrationpath=ext.forum.migrations

Note When you set Boolean options such as interactive, use the following method to pass in 1 or 0 to the command line:

YIIC Migrate--interactive=0

Configure global commands

command-line options allow us to quickly configure migration commands, but sometimes we may want to configure only one command at a time. For example, we might want to use a different table to save the migration history, or we want to use a custom migration template. We can do this by editing the configuration file for the console app, 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 requiring us to enter so many option information on the command line every time.

The above is the Yii Framework Official Guide Series supplement version 27--use database: Database migration content, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

