Create copy of db strucutre. Simple yii command

we moved here http://radzserg.com/2012/03/30/create-copy-of-db-strucutre-simple-yii-command/

Continue to publish some of components that I usually use in different projects. Today I'll tell you about how easily create copy of db structure from production scheme to test db.

As usual I don't care about test DB until you starts your test and you get smth like table {my_funny_new_table} doesn't exist or smth like undefined field for {my_funny_table}. And you remember that you haven't updated structure for test DB.

So I use this simple command in my yii projects (and similar in not yii projects)

<?php

/**
 * Create test db
 * @author radzserg
 *
 */
class CreateTestDbCommand extends CConsoleCommand
{
    public function run($args)
    {
        echo "Creating test DB\n\n";

        $mainConfig = require dirname(__FILE__) . '/../config/main.php';
        $testConfig = require dirname(__FILE__) . '/../config/test.php';

        $mainDbConfig = $mainConfig['components']['db'];
        $testDbConfig = $testConfig['components']['db'];

        $m = array();
        preg_match('~mysql:host=(.+);dbname=(.+)~is', $testDbConfig['connectionString'], $m);
        $testDbConfig['host'] = $m[1];
        $testDbConfig['dbname'] = $m[2];

        $m = array();
        preg_match('~mysql:host=(.+);dbname=(.+)~is', $mainDbConfig['connectionString'], $m);
        $mainDbConfig['host'] = $m[1];
        $mainDbConfig['dbname'] = $m[2];

        $dumpPath = ROOT_PATH . '/protected/data/schema.sql';

        $mysqlTestDbCreator = new MysqlTestDbCreater($mainDbConfig, $testDbConfig, $dumpPath);
        $mysqlTestDbCreator->run();

        echo "Testing Db was successfully created\n\n";
    }
}

/**
 * Create test db as copy of real db
 *
 */
class MysqlTestDbCreater
{
    private $_productionConfig = NULL;
    private $_testConfig = NULL;
    private $_dumpPath = NULL;

    public function __construct($productionConfig, $testConfig, $dumpPath)
    {
        $this->_productionConfig = $productionConfig;
        $this->_testConfig = $testConfig;
        $this->_dumpPath = $dumpPath;
    }

    public function run()
    {
        $this->_makeDump();
        $this->_createTestDb();
    }

    protected function _createTestDb()
    {
        $config = $this->_testConfig;
        $loginOptions = "--user={$config['username']} --password={$config['password']} "
            . "--host={$config['host']}";

        // truncate database
        $command = "echo \"SET FOREIGN_KEY_CHECKS = 0;\"; mysqldump {$loginOptions} --add-drop-table --no-data {$config['dbname']} | grep ^DROP";
        $delete_commands = array();
        $return = false;
        exec($command, $delete_commands, $return);
        mysql_connect($config['host'], $config['username'], $config['password']);
        mysql_select_db($config['dbname']);
        foreach ($delete_commands as $command) {
            mysql_query($command);
        }    
            
        $command = "mysql {$config['dbname']} {$loginOptions} < "  . $this->_dumpPath;
        
        $output = array();
        $return = false;
        exec($command, $output, $return);
        $output = implode("\n", $output);
        echo $output; 
    }

    protected function _makeDump()
    {
        $config = $this->_productionConfig;
        $loginOptions = "--user={$config['username']} --password={$config['password']} "
            . "--host={$config['host']} {$config['dbname']}";
        $command = "mysqldump $loginOptions --opt --no-data > " . $this->_dumpPath;
  
        $output = array();
        $return = false;
        exec($command, $output, $return);
        $output = implode("\n", $output);
  echo $output;
    }
} 

good testing