[Doctrine migrations] in-depth analysis of database migration Components IV: Integrated diff mode Migration component

Scenarios and Benefits

Once you are familiar with the Symfony framework, you have a deep sense of the power of the framework-integrated ORM component Doctrine2, and the accompanying data migration is also very convenient. Doctrine2 uses the doctrine Dbal component to migrate data in a way that compares the table structure in the code to the table structure in the actual database. This is more accurate and convenient than the way the previous version was managed.

The Symfony framework is supported by its own ORM component, but many projects do not use the ORM function, or have their own ORM components, how to integrate the migration of diff mode? Let's finish the task below.

SOURCE parsing

In the study of component source code, a class was found: Doctrine\DBAL\Migrations\Provider\SchemaDiffProvider The getSqlDiffToMigrate method is the key to our writing the diff script.

/**   *   @param    schema   $fromSchema   *   @param    schema   $toSchema   *   @return   string[]   */ public  function  getsqldifftomigrate ( Schema  $fromSchema      Schema  $toSchema   { return   $fromSchema ->getmigratetosql (  $toSchema    $this ->platform}  

The method compares the differences between the old data structure and the new data structure by passing in the two input parameters (Fromschema and Toschema), and finally returns the related SQL statement. Then we have to get the table structure in the existing database, as well as the structure of the table in the code, we can realize the data migration of diff mode.

Getting the table structure of an existing database can be createFromSchema obtained directly from the above method.

Then we need to write the data structure to the code, and then get the real-time table structure from the Code, and finally diff.

Writing a diff script

First create a diff file in the project and give the EXECUTE permission. After that, the script is written according to the ideas above, and the following is the complete code example:

#!/usr/bin/env PHP<?Phprequire_once ' vendor/autoload.php '; UseDoctrine\Dbal\dbalexception; UseDoctrine\Dbal\drivermanager; UseDoctrine\Dbal\migrations\provider\schemadiffprovider; UseDoctrine\Dbal\schema\mysqlschemamanager; UseDoctrine\Dbal\schema\schema;//Read database configuration information$db _config=include ' config/db.php ';$db _params=[    ' Driver '=' Pdo_mysql ',    ' Host '=$db _config[' Host '],    ' Port '=$db _config[' Port '],    ' dbname '=$db _config[' dbname '],    ' user '=$db _config[' user '],    ' Password '=$db _config[' Password '],];Try{$connection= Drivermanager::getconnection($db _params);}Catch (Dbalexception$e){Echo $e->getmessage().Php_eol;    Exit;}//Get database table structure$schema _manager=NewMysqlschemamanager($connection);$SDP=NewSchemadiffprovider($schema _manager, $schema _manager->getdatabaseplatform());$from _schema=$SDP->createfromschema();//Get the table structure written in the script$to _schema=NewSchema();$schema _class_list=Glob(__dir__.'/db/tables/*.php ');foreach ($schema _class_list  as $file){require_once $file;    $class='\\DB\\Tables\\'.basename($file, '. php ');    if (class_exists($class)){$cls=New $class;        if (method_exists($cls, ' Up ')){$cls->up($to _schema);}    }}//Compare, Output results$diff=$SDP->getsqldifftomigrate($from _schema, $to _schema);if ($diff){foreach ($diff  as $sql){Echo $sql.'; '.Php_eol;}}

Use of diff mode migration

After that, all the data tables need to be in the root directory in the Db/tables directory to create a corresponding table structure script (any file name), below, we use the Test_user table example:

Create the db/tables/testuser.php file and write the code:

<?phpnamespaceDb\tables; UseDoctrine\Dbal\schema\schema;classtestuser{ Public functionUp(Schema$schema): void {$table=$schema->createtable(' Test_user ');        $table->addcolumn(' id ', ' Integer ')->setunsigned(true)->setautoincrement(true);        $table->addcolumn(' name ', ' String ')->setlength( -)->setcomment(' User name ');        $table->addcolumn(' age ', ' Integer ')->setunsigned(true)->setdefault(0)->setcomment(' age ');        $table->addcolumn(' Sex ', ' String ')->setlength(2)->setdefault("')->setcomment(' Sex ');        $table->addcolumn(' Is_del ', ' Boolean ')->setdefault(false)->setcomment(' Delete ');        $table->setprimarykey([' id '])->addindex([' Is_del ']);}}

Then execute the command in the root directory ./diff and you will see the SQL information for the output.

Add blacklist filter

To this point the diff mode of migration has been able to use, but we found that if the existing projects in the middle of the migration function, the use of the diff command will be existing tables are deleted, and sometimes there are other tables do not need to be managed, we need to filter out the tables. Now let's add the Blacklist feature.

Before you compare, add the following code:

//Filter blacklist$black _list=[' Migration_versions ', ' test1 ', ' Test2 ', ' Test3 '];foreach ($black _list  as $black _table){$from _schema->hastable($black _table)&&$from _schema->droptable($black _table);    $to _schema->hastable($black _table)&&$to _schema->droptable($black _table);}

When the diff command is executed, the $black_list table inside the variable array is filtered out, and of course the blacklist variable can be completely replaced with other configuration files and can be customized in any way.

Scripting enhancements

It is best to further enhance the diff script so that it can execute SQL directly.

Modify the final comparison code to the following:

//Compare, Output results$diff=$SDP->getsqldifftomigrate($from _schema, $to _schema);if (Empty($diff)){Echo ' No need to update the schema. '.Php_eol;}ElseIf ($diff){$statement="';    foreach ($diff  as $sql){$statement.=$sql;        Echo $sql.'; '.Php_eol;}Echo ' Do you want to execute these sqls? (y/n) ';    $flag=Trim(fgets(STDIN));    if ($flag===' Y '){$connection->begintransaction();        Try{$connection->executeupdate($statement);            $connection->commit();}Catch (\Exception $e){Try{$connection->rollback();}Catch (Connectionexception$e){Echo $e->getmessage();                Exit;}Echo $e->getmessage().Php_eol;            Exit;}Echo ' SQL executed successfully '.Php_eol;}}

Now, when you execute the diff command, you will be prompted whether you need to execute SQL (default does not execute).

Conclusion

Now, the diff mode of data migration is already more perfect integration and project, but the diff method is also flawed, that is, the data can not be managed, such as sometimes the menu or permissions need to synchronize data. So the two ways to choose according to their own project situation.

All of the code in this series can be found in the code base link at the end of the article, and each commit corresponds to each article, allowing the reader to correspond to the article and the code.

Now that the series is over, I hope this series of articles will help you with the data migration component, thank you for your reading, and wish to make your valuable comments.

In my code base you can view the detailed code of this article, welcome to star.

[Doctrine migrations] in-depth analysis of database migration Components IV: Integrated diff mode Migration component