Created
September 17, 2013 12:38
-
-
Save vdubyna/6593793 to your computer and use it in GitHub Desktop.
Magento migrate tables tool
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /** | |
| * @author [email protected] | |
| * | |
| * Script to migrate data between databases | |
| * Migration uses delete strategy | |
| * Drop table on destination database, | |
| * create new one based on the data from source database | |
| * insert data from source database | |
| * | |
| * Configuration file is required | |
| * Example 'migrate.json': | |
| * | |
| { | |
| "source": { | |
| "hostname": "localhost", | |
| "username": "root", | |
| "password": "", | |
| "database": "stage", | |
| "prefix": "" | |
| }, | |
| "destination": { | |
| "hostname": "localhost", | |
| "username": "root", | |
| "password": "", | |
| "database": "prod", | |
| "prefix": "" | |
| }, | |
| "tables_to_migrate": [ | |
| "catalog_category_entity" | |
| ], | |
| "tables_to_drop": [ | |
| "catalog_category_flat_store_1", | |
| "catalog_category_flat_store_2", | |
| "catalog_category_flat_store_3", | |
| "catalog_product_flat_1", | |
| "catalog_product_flat_2", | |
| "catalog_product_flat_3" | |
| ] | |
| } | |
| * | |
| */ | |
| // Execute migration | |
| $migration = new Db_Migration_Action('migrate.json'); | |
| $migration->run(); | |
| /** | |
| * Class Db_Migration_Resource | |
| */ | |
| class Db_Migration_Resource | |
| { | |
| const TYPE_DESTINATION = 'destination'; | |
| const TYPE_SOURCE = 'source'; | |
| /** | |
| * destination Database resource | |
| * | |
| * @var resource | |
| */ | |
| protected $_destination; | |
| /** | |
| * source Database resource | |
| * | |
| * @var resource | |
| */ | |
| protected $_source; | |
| /** | |
| * Config | |
| * | |
| * @var array | |
| */ | |
| protected $_config = array(); | |
| /** | |
| * Set connection | |
| * | |
| * @param array $config | |
| * @param string $type | |
| * | |
| * @throws Exception | |
| * @return Db_Migration_Resource | |
| */ | |
| public function setConnection(array $config, $type) | |
| { | |
| if ($type == self::TYPE_DESTINATION) { | |
| $connection = & $this->_destination; | |
| } elseif ($type == self::TYPE_SOURCE) { | |
| $connection = & $this->_source; | |
| } else { | |
| throw new Exception('Unknown connection type'); | |
| } | |
| $required = array('hostname', 'username', 'password', 'database', 'prefix'); | |
| foreach ($required as $field) { | |
| if (!array_key_exists($field, $config)) { | |
| throw new Exception(sprintf('Please specify %s for %s database connection', $field, $type)); | |
| } | |
| } | |
| if (!$connection = @mysql_connect($config['hostname'], $config['username'], $config['password'], true)) { | |
| throw new Exception(sprintf( | |
| '%s database connection error: #%d %s', ucfirst($type), mysql_errno(), mysql_error() | |
| )); | |
| } | |
| if (!@mysql_select_db($config['database'], $connection)) { | |
| throw new Exception(sprintf( | |
| 'Cannot select %s database (%s): #%d, %s', $type, $config['database'], | |
| mysql_errno(), mysql_error() | |
| )); | |
| } | |
| mysql_query('SET NAMES utf8', $connection); | |
| $this->_config[$type] = $config; | |
| return $this; | |
| } | |
| /** | |
| * Check exists connections | |
| * | |
| * @throws Exception | |
| * @return bool | |
| */ | |
| protected function _checkConnection() | |
| { | |
| if (is_null($this->_destination)) { | |
| throw new Exception(sprintf('Invalid %s database connection', self::TYPE_DESTINATION)); | |
| } | |
| if (is_null($this->_source)) { | |
| throw new Exception(sprintf('Invalid %s database connection', self::TYPE_SOURCE)); | |
| } | |
| return true; | |
| } | |
| /** | |
| * Retrieve table name | |
| * | |
| * @param string $table | |
| * @param string $type | |
| * | |
| * @return string | |
| */ | |
| public function getTable($table, $type) | |
| { | |
| $prefix = $this->_config[$type]['prefix']; | |
| return $prefix . $table; | |
| } | |
| /** | |
| * Retrieve connection resource | |
| * | |
| * @param string $type | |
| * | |
| * @throws Exception | |
| * @return resource | |
| */ | |
| protected function _getConnection($type) | |
| { | |
| if ($type == self::TYPE_DESTINATION) { | |
| return $this->_destination; | |
| } elseif ($type == self::TYPE_SOURCE) { | |
| return $this->_source; | |
| } else { | |
| throw new Exception(sprintf('Unknown connection type "%s"', $type)); | |
| } | |
| } | |
| /** | |
| * Check connection type | |
| * | |
| * @param string $type | |
| * | |
| * @throws Exception | |
| * @return bool | |
| */ | |
| protected function _checkType($type) | |
| { | |
| if ($type == self::TYPE_DESTINATION) { | |
| return true; | |
| } elseif ($type == self::TYPE_SOURCE) { | |
| return true; | |
| } else { | |
| throw new Exception(sprintf('Unknown connection type "%s"', $type)); | |
| } | |
| } | |
| /** | |
| * Check exists table | |
| * | |
| * @param string $table | |
| * @param string $type | |
| * | |
| * @return bool | |
| */ | |
| public function tableExists($table, $type) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| $sql = $this->_quote("SHOW TABLES LIKE ?", $this->getTable($table, $type)); | |
| $res = mysql_query($sql, $this->_getConnection($type)); | |
| if (!mysql_fetch_row($res)) { | |
| return false; | |
| } | |
| return true; | |
| } | |
| /** | |
| * Simple quote SQL statement | |
| * supported ? or %[type] sprintf format | |
| * | |
| * @param string $statement | |
| * @param array $bind | |
| * | |
| * @return string | |
| */ | |
| protected function _quote($statement, $bind = array()) | |
| { | |
| $statement = str_replace('?', '%s', $statement); | |
| if (!is_array($bind)) { | |
| $bind = array($bind); | |
| } | |
| foreach ($bind as $k => $v) { | |
| if (is_numeric($v)) { | |
| $bind[$k] = $v; | |
| } elseif (is_null($v)) { | |
| $bind[$k] = 'NULL'; | |
| } else { | |
| $bind[$k] = "'" . mysql_escape_string($v) . "'"; | |
| } | |
| } | |
| return vsprintf($statement, $bind); | |
| } | |
| /** | |
| * Apply to Database needed settings | |
| * | |
| * @param string $type | |
| * | |
| * @return Db_Migration_Resource | |
| */ | |
| public function start($type) | |
| { | |
| $this->sqlQuery("SET FOREIGN_KEY_CHECKS = 0", $type); | |
| return $this; | |
| } | |
| /** | |
| * Return old settings to database (applied in start method) | |
| * | |
| * @param string $type | |
| * | |
| * @return Db_Migration_Resource | |
| */ | |
| public function finish($type) | |
| { | |
| $this->sqlQuery("SET FOREIGN_KEY_CHECKS = 1", $type); | |
| return $this; | |
| } | |
| /** | |
| * Begin transaction | |
| * | |
| * @param string $type | |
| * | |
| * @return Db_Migration_Resource | |
| */ | |
| public function begin($type) | |
| { | |
| $this->sqlQuery('START TRANSACTION', $type); | |
| return $this; | |
| } | |
| /** | |
| * Commit transaction | |
| * | |
| * @param string $type | |
| * | |
| * @return Db_Migration_Resource | |
| */ | |
| public function commit($type) | |
| { | |
| $this->sqlQuery('COMMIT', $type); | |
| return $this; | |
| } | |
| /** | |
| * Rollback transaction | |
| * | |
| * @param string $type | |
| * | |
| * @return Db_Migration_Resource | |
| */ | |
| public function rollback($type) | |
| { | |
| $this->sqlQuery('ROLLBACK', $type); | |
| return $this; | |
| } | |
| /** | |
| * Retrieve table properties as array | |
| * fields, keys, constraints, engine, charset, create | |
| * | |
| * @param string $table | |
| * @param string $type | |
| * | |
| * @return array | |
| */ | |
| public function getTableProperties($table, $type) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| if (!$this->tableExists($table, $type)) { | |
| return false; | |
| } | |
| $tableName = $this->getTable($table, $type); | |
| $prefix = $this->_config[$type]['prefix']; | |
| $tableProp = array( | |
| 'fields' => array(), | |
| 'keys' => array(), | |
| 'constraints' => array(), | |
| 'engine' => 'MYISAM', | |
| 'charset' => 'utf8', | |
| 'collate' => null, | |
| 'create_sql' => null, | |
| 'drop_sql' => null, | |
| 'migrate_sql' => null, | |
| ); | |
| // collect fields | |
| $sql = "SHOW FULL COLUMNS FROM `{$tableName}`"; | |
| $res = mysql_query($sql, $this->_getConnection($type)); | |
| while ($row = mysql_fetch_row($res)) { | |
| $tableProp['fields'][$row[0]] = array( | |
| 'type' => $row[1], | |
| 'is_null' => strtoupper($row[3]) == 'YES' ? true : false, | |
| 'default' => $row[5], | |
| 'extra' => $row[6], | |
| 'collation' => $row[2], | |
| ); | |
| } | |
| // create sql | |
| $sql = "SHOW CREATE TABLE `{$tableName}`"; | |
| $res = mysql_query($sql, $this->_getConnection($type)); | |
| $row = mysql_fetch_row($res); | |
| $tableProp['create_sql'] = $row[1]; | |
| // collect keys | |
| $regExp = '#(PRIMARY|UNIQUE|FULLTEXT|FOREIGN)?\sKEY (`[^`]+` )?(\([^\)]+\))#'; | |
| $matches = array(); | |
| preg_match_all($regExp, $tableProp['create_sql'], $matches, PREG_SET_ORDER); | |
| foreach ($matches as $match) { | |
| if (isset($match[1]) && $match[1] == 'PRIMARY') { | |
| $keyName = 'PRIMARY'; | |
| } elseif (isset($match[1]) && $match[1] == 'FOREIGN') { | |
| continue; | |
| } else { | |
| $keyName = substr($match[2], 1, -2); | |
| } | |
| $fields = $fieldsMatches = array(); | |
| preg_match_all("#`([^`]+)`#", $match[3], $fieldsMatches, PREG_SET_ORDER); | |
| foreach ($fieldsMatches as $field) { | |
| $fields[] = $field[1]; | |
| } | |
| $tableProp['keys'][strtoupper($keyName)] = array( | |
| 'type' => !empty($match[1]) ? $match[1] : 'INDEX', | |
| 'name' => $keyName, | |
| 'fields' => $fields | |
| ); | |
| } | |
| // collect CONSTRAINT | |
| $regExp = '#,\s+CONSTRAINT `([^`]*)` FOREIGN KEY \(`([^`]*)`\) ' | |
| . 'REFERENCES (`[^`]*\.)?`([^`]*)` \(`([^`]*)`\)' | |
| . '( ON DELETE (RESTRICT|CASCADE|SET NULL|NO ACTION))?' | |
| . '( ON UPDATE (RESTRICT|CASCADE|SET NULL|NO ACTION))?#'; | |
| $matches = array(); | |
| preg_match_all($regExp, $tableProp['create_sql'], $matches, PREG_SET_ORDER); | |
| foreach ($matches as $match) { | |
| $tableProp['constraints'][strtoupper($match[1])] = array( | |
| 'fk_name' => strtoupper($match[1]), | |
| 'ref_db' => isset($match[3]) ? $match[3] : null, | |
| 'pri_table' => $table, | |
| 'pri_field' => $match[2], | |
| 'ref_table' => substr($match[4], strlen($prefix)), | |
| 'ref_field' => $match[5], | |
| 'on_delete' => isset($match[6]) ? $match[7] : '', | |
| 'on_update' => isset($match[8]) ? $match[9] : '' | |
| ); | |
| } | |
| // engine | |
| $regExp = "#(ENGINE|TYPE)=" | |
| . "(MEMORY|HEAP|INNODB|MYISAM|ISAM|BLACKHOLE|BDB|BERKELEYDB|MRG_MYISAM|ARCHIVE|CSV|EXAMPLE)" | |
| . "#i"; | |
| $match = array(); | |
| if (preg_match($regExp, $tableProp['create_sql'], $match)) { | |
| $tableProp['engine'] = strtoupper($match[2]); | |
| } | |
| //charset | |
| $regExp = "#DEFAULT CHARSET=([a-z0-9]+)( COLLATE=([a-z0-9_]+))?#i"; | |
| $match = array(); | |
| if (preg_match($regExp, $tableProp['create_sql'], $match)) { | |
| $tableProp['charset'] = strtolower($match[1]); | |
| if (isset($match[3])) { | |
| $tableProp['collate'] = $match[3]; | |
| } | |
| } | |
| // drop | |
| $tableProp['drop_sql'] = "DROP TABLE {$tableName}"; | |
| return $tableProp; | |
| } | |
| public function getTables($type) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| $prefix = $this->_config[$type]['prefix']; | |
| $tables = array(); | |
| $sql = 'SHOW TABLES'; | |
| $res = mysql_query($sql, $this->_getConnection($type)); | |
| while ($row = mysql_fetch_row($res)) { | |
| $tableName = substr($row[0], strlen($prefix)); | |
| $tables[$tableName] = $this->getTableProperties($tableName, $type); | |
| } | |
| return $tables; | |
| } | |
| /** | |
| * Add constraint | |
| * | |
| * @param array $config | |
| * @param string $type | |
| * | |
| * @throws Exception | |
| * @return Db_Migration_Resource | |
| */ | |
| public function addConstraint(array $config, $type) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| $required = array('fk_name', 'pri_table', 'pri_field', 'ref_table', 'ref_field', 'on_update', 'on_delete'); | |
| foreach ($required as $field) { | |
| if (!array_key_exists($field, $config)) { | |
| throw new Exception(sprintf( | |
| 'Cannot create CONSTRAINT: invalid required config parameter "%s"', $field | |
| )); | |
| } | |
| } | |
| if ($config['on_delete'] == '' || strtoupper($config['on_delete']) == 'CASCADE' | |
| || strtoupper($config['on_delete']) == 'RESTRICT' | |
| ) { | |
| $sql = "DELETE `p`.* FROM `{$this->getTable($config['pri_table'], $type)}` AS `p`" | |
| . " LEFT JOIN `{$this->getTable($config['ref_table'], $type)}` AS `r`" | |
| . " ON `p`.`{$config['pri_field']}` = `r`.`{$config['ref_field']}`" | |
| . " WHERE `p`.`{$config['pri_field']}` IS NOT NULL" | |
| . " AND `r`.`{$config['ref_field']}` IS NULL"; | |
| $this->sqlQuery($sql, $type); | |
| } elseif (strtoupper($config['on_delete']) == 'SET NULL') { | |
| $sql = "UPDATE `{$this->getTable($config['pri_table'], $type)}` AS `p`" | |
| . " LEFT JOIN `{$this->getTable($config['ref_table'], $type)}` AS `r`" | |
| . " ON `p`.`{$config['pri_field']}` = `r`.`{$config['ref_field']}`" | |
| . " SET `p`.`{$config['pri_field']}`=NULL" | |
| . " WHERE `p`.`{$config['pri_field']}` IS NOT NULL" | |
| . " AND `r`.`{$config['ref_field']}` IS NULL"; | |
| $this->sqlQuery($sql, $type); | |
| } | |
| $sql = "ALTER TABLE `{$this->getTable($config['pri_table'], $type)}`" | |
| . " ADD CONSTRAINT `{$config['fk_name']}`" | |
| . " FOREIGN KEY (`{$config['pri_field']}`)" | |
| . " REFERENCES `{$this->getTable($config['ref_table'], $type)}`" | |
| . " (`{$config['ref_field']}`)"; | |
| if (!empty($config['on_delete'])) { | |
| $sql .= ' ON DELETE ' . strtoupper($config['on_delete']); | |
| } | |
| if (!empty($config['on_update'])) { | |
| $sql .= ' ON UPDATE ' . strtoupper($config['on_update']); | |
| } | |
| $this->sqlQuery($sql, $type); | |
| return $this; | |
| } | |
| /** | |
| * Drop Foreign Key from table | |
| * | |
| * @param string $table | |
| * @param string $foreignKey | |
| * @param string $type | |
| * | |
| * @return $this | |
| */ | |
| public function dropConstraint($table, $foreignKey, $type) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| $sql = "ALTER TABLE `{$table}` DROP FOREIGN KEY `{$foreignKey}`"; | |
| $this->sqlQuery($sql, $type); | |
| return $this; | |
| } | |
| /** | |
| * Add column to table | |
| * | |
| * @param string $table | |
| * @param string $column | |
| * @param array $config | |
| * @param string $type | |
| * @param string|false|null $after | |
| * | |
| * @throws Exception | |
| * @return $this | |
| */ | |
| public function addColumn($table, $column, array $config, $type, $after = null) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| if (!$this->tableExists($table, $type)) { | |
| return $this; | |
| } | |
| $required = array('type', 'is_null', 'default'); | |
| foreach ($required as $field) { | |
| if (!array_key_exists($field, $config)) { | |
| throw new Exception(sprintf('Cannot create COLUMN: invalid required config parameter "%s"', $field)); | |
| } | |
| } | |
| $sql = "ALTER TABLE `{$this->getTable($table, $type)}` ADD COLUMN `{$column}`" | |
| . " {$config['type']}" | |
| . ($config['is_null'] ? "" : " NOT NULL") | |
| . ($config['default'] ? " DEFAULT '{$config['default']}'" : "") | |
| . (!empty($config['extra']) ? " {$config['extra']}" : ""); | |
| if ($after === false) { | |
| $sql .= " FIRST"; | |
| } elseif (!is_null($after)) { | |
| $sql .= " AFTER `{$after}`"; | |
| } | |
| $this->sqlQuery($sql, $type); | |
| return $this; | |
| } | |
| /** | |
| * Add primary|unique|fulltext|index to table | |
| * | |
| * @param string $table | |
| * @param array $config | |
| * @param string $type | |
| * | |
| * @throws Exception | |
| * @return Db_Migration_Resource | |
| */ | |
| public function addKey($table, array $config, $type) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| if (!$this->tableExists($table, $type)) { | |
| return $this; | |
| } | |
| $required = array('type', 'name', 'fields'); | |
| foreach ($required as $field) { | |
| if (!array_key_exists($field, $config)) { | |
| throw new Exception(sprintf('Cannot create KEY: invalid required config parameter "%s"', $field)); | |
| } | |
| } | |
| switch (strtolower($config['type'])) { | |
| case 'primary': | |
| $condition = "PRIMARY KEY"; | |
| break; | |
| case 'unique': | |
| $condition = "UNIQUE `{$config['name']}`"; | |
| break; | |
| case 'fulltext': | |
| $condition = "FULLTEXT `{$config['name']}`"; | |
| break; | |
| default: | |
| $condition = "INDEX `{$config['name']}`"; | |
| break; | |
| } | |
| if (!is_array($config['fields'])) { | |
| $config['fields'] = array($config['fields']); | |
| } | |
| $sql = "ALTER TABLE `{$this->getTable($table, $type)}` ADD {$condition}" | |
| . " (`" . join("`,`", $config['fields']) . "`)"; | |
| $this->sqlQuery($sql, $type); | |
| return $this; | |
| } | |
| /** | |
| * Change table storage engine | |
| * | |
| * @param string $table | |
| * @param string $engine | |
| * @param string $type | |
| * | |
| * @return Db_Migration_Resource | |
| */ | |
| public function changeTableEngine($table, $type, $engine) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| $sql = "ALTER TABLE `{$this->getTable($table, $type)}` ENGINE={$engine}"; | |
| $this->sqlQuery($sql, $type); | |
| return $this; | |
| } | |
| /** | |
| * Change table storage engine | |
| * | |
| * @param string $table | |
| * @param string $type | |
| * @param string $charset | |
| * @param null $collate | |
| * | |
| * @return Db_Migration_Resource | |
| */ | |
| public function changeTableCharset($table, $type, $charset, $collate = null) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| $sql = "ALTER TABLE `{$this->getTable($table, $type)}` DEFAULT CHARACTER SET={$charset}"; | |
| if ($collate) { | |
| $sql .= " COLLATE {$collate}"; | |
| } | |
| $this->sqlQuery($sql, $type); | |
| return $this; | |
| } | |
| /** | |
| * Migrate data from source to destination | |
| * | |
| */ | |
| public function migrateTable($tableName) | |
| { | |
| $this->_checkConnection(); | |
| // check if host is the same | |
| if ($this->_config['source']['hostname'] != $this->_config['destination']['hostname']) { | |
| throw new Exception("Source and Destination are not located at the same db host. Data could not be migrated"); | |
| } | |
| // migrate | |
| $destinationName = $this->_config['destination']['database'] | |
| . "." . $this->getTable($tableName, Db_Migration_Resource::TYPE_DESTINATION); | |
| $sourceName = $this->_config['source']['database'] | |
| . "." . $this->getTable($tableName, Db_Migration_Resource::TYPE_SOURCE); | |
| $this->sqlQuery( | |
| "INSERT INTO {$destinationName} SELECT * FROM {$sourceName}", Db_Migration_Resource::TYPE_SOURCE | |
| ); | |
| return $this; | |
| } | |
| /** | |
| * Run SQL query | |
| * | |
| * @param string $sql | |
| * @param string $type | |
| * | |
| * @throws Exception | |
| * @return resource | |
| */ | |
| public function sqlQuery($sql, $type) | |
| { | |
| $this->_checkConnection(); | |
| $this->_checkType($type); | |
| if (!$res = @mysql_query($sql, $this->_getConnection($type))) { | |
| throw new Exception(sprintf( | |
| "Error #%d: %s on SQL: %s", | |
| mysql_errno($this->_getConnection($type)), | |
| mysql_error($this->_getConnection($type)), | |
| $sql | |
| )); | |
| } | |
| return $res; | |
| } | |
| /** | |
| * Retrieve previous key from array by key | |
| * | |
| * @param array $array | |
| * @param mixed $key | |
| * | |
| * @return mixed | |
| */ | |
| public function arrayPrevKey(array $array, $key) | |
| { | |
| $prev = false; | |
| foreach ($array as $k => $v) { | |
| if ($k == $key) { | |
| return $prev; | |
| } | |
| $prev = $k; | |
| } | |
| } | |
| /** | |
| * Retrieve next key from array by key | |
| * | |
| * @param array $array | |
| * @param mixed $key | |
| * | |
| * @return mixed | |
| */ | |
| public function arrayNextKey(array $array, $key) | |
| { | |
| $next = false; | |
| foreach ($array as $k => $v) { | |
| if ($next === true) { | |
| return $k; | |
| } | |
| if ($k == $key) { | |
| $next = true; | |
| } | |
| } | |
| return false; | |
| } | |
| } | |
| /** | |
| * @property array success | |
| * @property array error | |
| */ | |
| class Db_Migration_Action | |
| { | |
| /** | |
| * Repair Database Tool object | |
| * | |
| * @var Db_Migration_Resource | |
| */ | |
| protected $_resource; | |
| protected $_config; | |
| public $success = array(); | |
| public $error = array(); | |
| /** | |
| * @param \Db_Migration_Resource $resource | |
| */ | |
| public function setResource($resource) | |
| { | |
| $this->_resource = $resource; | |
| } | |
| /** | |
| * @return \Db_Migration_Resource | |
| */ | |
| public function getResource() | |
| { | |
| return $this->_resource; | |
| } | |
| public function __construct($configFile) | |
| { | |
| if (!file_exists($configFile)) { | |
| exit("Migration configuration file \"migrate.json\" should exist"); | |
| } | |
| $this->_config = json_decode(file_get_contents($configFile), true); | |
| $resource = new Db_Migration_Resource(); | |
| $resource->setConnection($this->_config['source'], Db_Migration_Resource::TYPE_SOURCE); | |
| $resource->setConnection($this->_config['destination'], Db_Migration_Resource::TYPE_DESTINATION); | |
| $this->setResource($resource); | |
| } | |
| /** | |
| * Show Repair Database Page | |
| * | |
| * @return Db_Migration_Action | |
| */ | |
| public function repairAction() | |
| { | |
| $actionList = array( | |
| 'charset' => array(), | |
| 'engine' => array(), | |
| 'column' => array(), | |
| 'index' => array(), | |
| 'table' => array(), | |
| 'invalid_fk' => array(), | |
| 'constraint' => array() | |
| ); | |
| $sourceTables = $this->_resource->getTables(Db_Migration_Resource::TYPE_SOURCE); | |
| $destinationTables = $this->_resource->getTables(Db_Migration_Resource::TYPE_DESTINATION); | |
| // collect action list | |
| foreach ($sourceTables as $table => $tableProp) { | |
| if (!isset($destinationTables[$table])) { | |
| $actionList['table'][] = array( | |
| 'msg' => sprintf('Add missing table "%s"', $table), | |
| 'sql' => $tableProp['create_sql'] | |
| ); | |
| } else { | |
| // check charset | |
| if ($tableProp['charset'] != $destinationTables[$table]['charset']) { | |
| $actionList['charset'][] = array( | |
| 'msg' => sprintf( | |
| 'Change charset on table "%s" from %s to %s', | |
| $table, | |
| $destinationTables[$table]['charset'], | |
| $tableProp['charset'] | |
| ), | |
| 'table' => $table, | |
| 'charset' => $tableProp['charset'], | |
| 'collate' => $tableProp['collate'] | |
| ); | |
| } | |
| // check storage | |
| if ($tableProp['engine'] != $destinationTables[$table]['engine']) { | |
| $actionList['engine'][] = array( | |
| 'msg' => sprintf( | |
| 'Change storage engine type on table "%s" from %s to %s', | |
| $table, | |
| $destinationTables[$table]['engine'], | |
| $tableProp['engine'] | |
| ), | |
| 'table' => $table, | |
| 'engine' => $tableProp['engine'] | |
| ); | |
| } | |
| // validate columns | |
| $fieldList = array_diff_key($tableProp['fields'], $destinationTables[$table]['fields']); | |
| if ($fieldList) { | |
| $fieldActionList = array(); | |
| foreach ($fieldList as $fieldKey => $fieldProp) { | |
| $afterField = $this->_resource->arrayPrevKey($tableProp['fields'], $fieldKey); | |
| $fieldActionList[] = array( | |
| 'column' => $fieldKey, | |
| 'config' => $fieldProp, | |
| 'after' => $afterField | |
| ); | |
| } | |
| $actionList['column'][] = array( | |
| 'msg' => sprintf( | |
| 'Add missing field(s) "%s" to table "%s"', | |
| join(', ', array_keys($fieldList)), | |
| $table | |
| ), | |
| 'table' => $table, | |
| 'action' => $fieldActionList | |
| ); | |
| } | |
| //validate indexes | |
| $keyList = array_diff_key($tableProp['keys'], $destinationTables[$table]['keys']); | |
| if ($keyList) { | |
| $keyActionList = array(); | |
| foreach ($keyList as $keyProp) { | |
| $keyActionList[] = array( | |
| 'config' => $keyProp | |
| ); | |
| } | |
| $actionList['index'][] = array( | |
| 'msg' => sprintf( | |
| 'Add missing index(es) "%s" to table "%s"', | |
| join(', ', array_keys($keyList)), | |
| $table | |
| ), | |
| 'table' => $table, | |
| 'action' => $keyActionList | |
| ); | |
| } | |
| $constraintList = array_diff_key($tableProp['constraints'], $destinationTables[$table]['constraints']); | |
| foreach ($destinationTables[$table]['constraints'] as $fk => $fkProp) { | |
| if ($fkProp['ref_db']) { | |
| $actionList['invalid_fk'][] = array( | |
| 'msg' => sprintf( | |
| 'Remove invalid foreign key(s) "%s" from table "%s"', | |
| join(', ', array_keys($constraintList)), | |
| $table | |
| ), | |
| 'table' => $table, | |
| 'constraint' => $fkProp['fk_name'] | |
| ); | |
| unset($destinationTables[$table]['constraints'][$fk]); | |
| } | |
| } | |
| // validate foreign keys | |
| if ($constraintList) { | |
| $constraintActionList = array(); | |
| foreach ($constraintList as $constraintConfig) { | |
| $constraintActionList[] = array( | |
| 'config' => $constraintConfig | |
| ); | |
| } | |
| $actionList['constraint'][] = array( | |
| 'msg' => sprintf( | |
| 'Add missing foreign key(s) "%s" to table "%s"', | |
| join(', ', array_keys($constraintList)), | |
| $table | |
| ), | |
| 'table' => $table, | |
| 'action' => $constraintActionList | |
| ); | |
| } | |
| } | |
| } | |
| $type = Db_Migration_Resource::TYPE_DESTINATION; | |
| $this->_resource->start($type); | |
| foreach ($actionList['charset'] as $actionProp) { | |
| $this->_resource->begin($type); | |
| try { | |
| $this->_resource->changeTableCharset( | |
| $actionProp['table'], $type, $actionProp['charset'], $actionProp['collate'] | |
| ); | |
| $this->_resource->commit($type); | |
| $this->success[] = $actionProp['msg']; | |
| } catch (Exception $e) { | |
| $this->_resource->rollback($type); | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| foreach ($actionList['engine'] as $actionProp) { | |
| $this->_resource->begin($type); | |
| try { | |
| $this->_resource->changeTableEngine($actionProp['table'], $type, $actionProp['engine']); | |
| $this->_resource->commit($type); | |
| $this->success[] = $actionProp['msg']; | |
| } catch (Exception $e) { | |
| $this->_resource->rollback($type); | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| foreach ($actionList['column'] as $actionProp) { | |
| $this->_resource->begin($type); | |
| try { | |
| foreach ($actionProp['action'] as $action) { | |
| $this->_resource->addColumn( | |
| $actionProp['table'], $action['column'], $action['config'], $type, $action['after'] | |
| ); | |
| } | |
| $this->_resource->commit($type); | |
| $this->success[] = $actionProp['msg']; | |
| } catch (Exception $e) { | |
| $this->_resource->rollback($type); | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| foreach ($actionList['index'] as $actionProp) { | |
| $this->_resource->begin($type); | |
| try { | |
| foreach ($actionProp['action'] as $action) { | |
| $this->_resource->addKey($actionProp['table'], $action['config'], $type); | |
| } | |
| $this->_resource->commit($type); | |
| $this->success[] = $actionProp['msg']; | |
| } catch (Exception $e) { | |
| $this->_resource->rollback($type); | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| foreach ($actionList['table'] as $actionProp) { | |
| $this->_resource->begin($type); | |
| try { | |
| $this->_resource->sqlQuery($actionProp['sql'], $type); | |
| $this->_resource->commit($type); | |
| $this->success[] = $actionProp['msg']; | |
| } catch (Exception $e) { | |
| $this->_resource->rollback($type); | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| foreach ($actionList['invalid_fk'] as $actionProp) { | |
| $this->_resource->begin($type); | |
| try { | |
| $this->_resource->dropConstraint($actionProp['table'], $actionProp['constraint'], $type); | |
| $this->_resource->commit($type); | |
| $this->success[] = $actionProp['msg']; | |
| } catch (Exception $e) { | |
| $this->_resource->rollback($type); | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| foreach ($actionList['constraint'] as $actionProp) { | |
| $this->_resource->begin($type); | |
| try { | |
| foreach ($actionProp['action'] as $action) { | |
| $this->_resource->addConstraint($action['config'], $type); | |
| } | |
| $this->_resource->commit($type); | |
| $this->success[] = $actionProp['msg']; | |
| } catch (Exception $e) { | |
| $this->_resource->rollback($type); | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| $this->_resource->finish($type); | |
| return $this; | |
| } | |
| /** | |
| * Migrate Tables | |
| */ | |
| public function migrateAction() | |
| { | |
| $tablesToMigrate = $this->_config['tables_to_migrate']; | |
| $sourceTables = $this->_resource->getTables(Db_Migration_Resource::TYPE_SOURCE); | |
| $type = Db_Migration_Resource::TYPE_DESTINATION; | |
| $this->_resource->start($type); | |
| foreach ($tablesToMigrate as $tableName) { | |
| //$this->_resource->begin($type); | |
| try { | |
| // @Todo instead of delete we have to do rename to tmp table and then remove if success and rollback if not | |
| // drop | |
| $this->_resource->sqlQuery($sourceTables[$tableName]['drop_sql'], Db_Migration_Resource::TYPE_DESTINATION); | |
| $this->success[] = "Table: {$tableName} was deleted."; | |
| } catch (Exception $e) { | |
| $this->error[] = $e->getMessage(); | |
| } | |
| try { | |
| // create | |
| $destinationName = $this->_config['destination']['database'] | |
| . "." . $this->_resource->getTable($tableName, Db_Migration_Resource::TYPE_DESTINATION); | |
| $sourceName = $this->_config['source']['database'] | |
| . "." . $this->_resource->getTable($tableName, Db_Migration_Resource::TYPE_SOURCE); | |
| $sql = "CREATE TABLE {$destinationName} LIKE {$sourceName}"; | |
| $this->_resource->sqlQuery($sql, Db_Migration_Resource::TYPE_DESTINATION); | |
| $this->success[] = "Table: {$tableName} was created."; | |
| } catch (Exception $e) { | |
| $this->error[] = $e->getMessage(); | |
| } | |
| try { | |
| $this->_resource->migrateTable($tableName); | |
| //$this->_resource->commit($type); | |
| $this->success[] = "Table: {$tableName} was migrated."; | |
| } catch (Exception $e) { | |
| //$this->_resource->rollback($type); | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| $this->_resource->finish($type); | |
| return $this; | |
| } | |
| /** | |
| * Remove index table | |
| * Example Flat tables | |
| * | |
| * @param $tableList | |
| */ | |
| public function dropTables($tableList) | |
| { | |
| foreach ($tableList as $table) { | |
| try { | |
| $this->_resource->sqlQuery("DROP TABLE {$table}", Db_Migration_Resource::TYPE_DESTINATION); | |
| $this->success[] = "Table: {$table} was deleted."; | |
| } catch (Exception $e) { | |
| $this->error[] = $e->getMessage(); | |
| } | |
| } | |
| } | |
| /** | |
| * Run action | |
| * | |
| * @return Db_Migration_Action | |
| */ | |
| public function run() | |
| { | |
| $this->migrateAction(); | |
| $this->repairAction(); | |
| $this->dropTables($this->_config['tables_to_drop']); | |
| // Show logs | |
| array_map(function ($msg) { echo $msg . PHP_EOL; }, $this->error); | |
| array_map(function ($msg) { echo $msg . PHP_EOL; }, $this->success); | |
| echo PHP_EOL . "Please run reindex through shell script" . PHP_EOL; | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment