Posted By

ramirog89 on 09/01/15

Tagged

Versions (?)

Last Edited at 09/01/15 02:07am

Statistics

Viewed 151 times

Favorited by 0 user(s)

Related snippets

compare database structure

/ Published in: PHP

comparte structure databases

Highlighted TextExpand

<?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;

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.