Revision: 69769
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at September 1, 2015 02:07 by ramirog89
Initial Code
<?php /** * Script para sincronizar tablas **/ function compareTables( $tableToCompare, $tableOriginal ) { $comparsionResult = array( 'columnNotExist' => array(), 'differentDataType' => array() ); foreach ($tableToCompare as $columnName => $columnType) { if (!isset($tableOriginal[$columnName])) { // no existe la columna en la tabla array_push( $comparsionResult['columnNotExist'], $columnName ); } else { // existe la columna, pero el tipo de datos es diferente if ($tableOriginal[$columnName] != $columnType) { array_push( $comparsionResult['differentDataType'], array($columnName => $columnType) ); } } } return $comparsionResult; } class Database { protected $_cnx; protected $_database; protected $_result; protected $_queryResource; protected $_sql; public function __construct( $host, $user, $pass, $database ) { $this->_connect($host, $user, $pass, $database); } public function getSchema() { $tables = $this->_getTables(); $schemaTables = array(); foreach ($tables as $table) { $schemaTables[$table] = $this->_getTableSchema( $table ); } return $schemaTables; } public function createTable($table) { $this->_sql = 'CREATE TABLE '; return $this->_query(); } public function alterTable() {} private function _getTables() { $this->_sql = 'show tables'; return $this->_query() ->_fetch(); } private function _getTableSchema($table) { $this->_sql = "SELECT column_name,data_type FROM information_schema.columns WHERE table_schema = '" . $this->_database . "' AND table_name = '" . $table . "' ORDER BY column_name"; return $this->_query() ->_fetchSchema(); } private function _query() { $this->_queryResource = mysql_query( $this->_sql, $this->_cnx ); return $this; } private function _fetchSchema() { $result = array(); while ($rs = mysql_fetch_array($this->_queryResource)) { $result[$rs['column_name']] = $rs['data_type']; } return $result; } private function _fetch() { $result = array(); while ($rs = mysql_fetch_array($this->_queryResource)) { array_push($result, $rs[0]); } return $result; } private function _connect($host, $user, $pass, $database) { $this->_database = $database; $this->_cnx =& mysql_connect($host, $user, $pass) or die('No se pudo conectar al host ' . $host); mysql_select_db($database); } } //*************************************\\ // \\ // SE INICIA EL SCRIPT ACA... \\ // \\ //*************************************\\ /** Conecto las bases **/ $qaDatabase = new Database( 'domain', 'user', '****', 'database' ); $liveDatabase = new Database( 'domain', 'user', '****', 'database' ); // mysqldump --lock-tables=false -hlive-latam-01.811.mtvi.com -ubrazilsvsmtvlamw -p mtvbrazilservices > mtvbrazilbackup.sql /** Obtengo la estructura de la base de datos **/ $schemaQaTables = $qaDatabase->getSchema(); $schemaLiveTables = $liveDatabase->getSchema(); // Inicializo la memoria para generar un resultado $scriptSincronizationResult = array( 'missingTables' => array(), 'tableDiffs' => array() ); /** Comparación de esquema de Tablas de Live a QA **/ foreach ($schemaQaTables as $tableName => $columns) { if (!isset($schemaLiveTables[$tableName])) { // Si la tabla de QA no está en Live, la creamos /*if ($qaDatabase->createTable($schemaQaTables[$tableName])) { $scriptSincronizationResult['created'] = array($tableName); }*/ array_push($scriptSincronizationResult['missingTables'], $tableName); } else { // Si existe, comparamos las tablas $scriptSincronizationResult['tableDiffs'][$tableName] = compareTables( $schemaLiveTables[$tableName], $schemaQaTables[$tableName], $tableName ); } } //*************************************\\ // \\ // OUTPUT DEL SCRIPT ACA... \\ // \\ //*************************************\\ echo "<pre>"; var_dump($scriptSincronizationResult); exit; ?>
Initial URL
Initial Description
comparte structure databases
Initial Title
compare database structure
Initial Tags
Initial Language
PHP