v3qg2q
Last Updated: March 12, 2016
·
12.84K
· aimfeld
7c26f3d9b53b938d47df84f24b5c1561

Complex database migrations with PHP/MySQL using Phinx

Until now, I used dbdeploy to do DB migrations with MySQL statements. However, MySQL is very inconvenient to perform more complex migration steps, e.g. if you want to migrate not only the table schema, but also the data. Consider the following migration where values are be encoded into a json array:

Before migration (table user_links):
Picture

After migration (table token_links):
Picture

Luckily, there's a new amazing DB migration tool called phinx. It supports PHP, MySQL, composer , and is easy to integrate with phing.

Here's how I performed the above migration (shitty formatting because of little space):

use Phinx\Migration\AbstractMigration;

/** */
class UserLinksToTokenLinks extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->execute("
CREATE TABLE IF NOT EXISTS `token_links` (
  `linkID` INT(11) unsigned NOT NULL AUTO_INCREMENT,
  `token` VARCHAR(100) NOT NULL,
  `actionPlugin` VARCHAR(32) NOT NULL,
  `actionParams` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`linkID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
        ");

        // userLinks to tokenLinks 
        $userLinks = $this->fetchAll('SELECT * FROM user_links');
        foreach ($userLinks as $userLink) {
            $actionPlugin = ucfirst($userLink['action']);
            $actionParams = array();
            $actionParams['UserID'] = $userLink['userID'];

            if (isset($userLink['userSurveyID'])) {
                $actionParams['UserSurveyID'] = $userLink['userSurveyID'];
            }
            $jsonParamString = json_encode($actionParams);

            $this->execute("
INSERT INTO `token_links` (`linkID`, `token`, `actionPlugin`, `actionParams`) VALUES
({$userLink['userLinkID']}, '{$userLink['token']}', '$actionPlugin', '$jsonParamString');
            ");
        }


        $this->dropTable('user_links');
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        $this->execute("
DROP TABLE IF EXISTS `user_links`;
CREATE TABLE IF NOT EXISTS `user_links` (
  `userLinkID` INT(11) unsigned NOT NULL AUTO_INCREMENT,
  `token` VARCHAR(100) NOT NULL,
  `userID` INT(11) unsigned NOT NULL,
  `userSurveyID` INT(11) unsigned DEFAULT NULL,
  `action` VARCHAR(16) NOT NULL,
  PRIMARY KEY (`userLinkID`),
  UNIQUE KEY `token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
        ");

        // userLinks to tokenLinks 
        $tokenLinks = $this->fetchAll('SELECT * FROM token_links');
        foreach ($tokenLinks as $tokenLink) {
            $actionParams = json_decode($tokenLink['actionParams'], true);
            $action = strtolower($tokenLink['actionPlugin']);
            $userSurveyID = isset($actionParams['UserSurveyID']) ? 
                $actionParams['UserSurveyID'] : 'NULL';

            $this->execute("
INSERT INTO `user_links` (`userLinkID`, `token`, `userID`, `userSurveyID`, `action`) VALUES
({$tokenLink['linkID']}, '{$tokenLink['token']}', {$actionParams['UserID']}, $userSurveyID, '$action');
            ");
        }


        $this->dropTable('token_links');
    }
}
Say Thanks
Respond

1 Response
Add your response

27354

Thanks for your sharing, nice tool to db migration... Can you please help me out.. This is working fine with terminal creating tables and seeding data but I was to see some output on the browser from this code, is it possible to config it for var_dump.

over 1 year ago ·