<?php
/**
 * ASHDumper.php
 *
 * PHP version >= 4.30
 *
 * @author    Aziz Hussain <azizsaleh@gmail.com>
 * @copyright GPL license
 * @license   http://www.gnu.org/copyleft/gpl.html
 * @link      http://www.azizsaleh.com
 */

/**
 * ASHDumper
 *
 * Threaded MySQL backup and restore. To use you must have mysql and mysqldump
 * commands in your environment variables. You also need access to the proc_open function.
 * 
 * Usage (run php ASHDumper.php [--help]?(>=5.3) for more info)
 * 
 * To backup a database:
 * php ASHDumper.php -b yes -h localhost -u root -pPassword -o c:\abspath\to\output\dir
 * PHP >= 5.3
 * php ASHDumper.php --backup -h localhost -u root -pPassword -o c:\abspath\to\output\dir
 *
 * To restore a database:
 * php ASHDumper.php -r yes -h localhost -u root -pPassword -o c:\abspath\to\sql\dir
 * PHP >= 5.3
 * php ASHDumper.php --restore -h localhost -u root -pPassword -o c:\abspath\to\sql\dir
 *
 * To restore a database creating the database:
 * php ASHDumper.php -r yes -c yes -h localhost -u root -pPassword -o c:\abspath\to\sql\dir
 * PHP >= 5.3
 * php ASHDumper.php --restore --create -h localhost -u root -pPassword -o c:\abspath\to\sql\
 * 
 * @author    Aziz Hussain <azizsaleh@gmail.com>
 * @copyright GPL license
 * @license   http://www.gnu.org/copyleft/gpl.html
 * @link      http://www.azizsaleh.com
 */
class ASHDumper
{
	/**
	 * Load options, show help if needed
	 * and run restore/backup as specified
	 *
	 * @return void
	 */
	public static function load()
	{
		// Get options
		if (phpversion() >= 5.3) {
			$options = getopt('h:u:p:d:o:c:r:b:', array(
			'help', 'create', 'backup', 'restore'
			));
		} else {
			$options = getopt('h:u:p:d:o:c:r:b:');
		}
		// help?
		if (isset($options['help'])) {
			self::showHelp();
			exit();
		}
		
		// Options with defaults
		$fields = array(
			'h'	=> array('hostName', 'localhost'),
			'u'	=> array('userName', 'root'),
			'p'	=> array('password', ''),
			'o'	=> array('outputDir', dirname(__FILE__)),
		);
		
		// Holder for Db info
		$dbInfo = array();
		
		// Load optional values
		foreach ($fields as $opt => $keys) {
			if (isset($options[$opt])) {
				$dbInfo[$keys[0]] = $options[$opt];
			}
			if (empty($dbInfo[$keys[0]])) {
				$dbInfo[$keys[0]] = $keys[1];
			}		
		}

		$dbInfo['outputDir'] = rtrim($dbInfo['outputDir'], '/\\') . 
			DIRECTORY_SEPARATOR;

		// No database specified
		if (empty($options['d'])) {
			self::showHelp();
			return;
		} else {
			$dbInfo['database'] = $options['d'];
		}
		
		if (isset($options['r']) || isset($options['restore'])) {
			// Restore DB
			self::restore($dbInfo, $options);
			return;
		}
		
		if (isset($options['b'])  || isset($options['backup'])) {
			// Backup DB
			self::backup($dbInfo);
			return;
		}
		
		self::showHelp();
		return;
	}
	
	/**
	 * Show Help Message
	 *
	 * @return void
	 */
	public static function showHelp()
	{
		echo PHP_EOL . '-------------' . PHP_EOL . 'ASHDumper.php' . PHP_EOL .
		'--------------' . PHP_EOL . 
		'Use this script to backup/restore your database' . 
		' at a table level.' . PHP_EOL . ' Each table will get its own process' . 
		' to get backed up and restored' . PHP_EOL . PHP_EOL . 
		'------------' . PHP_EOL . 'REQUIREMENTS' . PHP_EOL .
		'------------' . PHP_EOL . 
		' - You need to have proc_open Enabled' . PHP_EOL .
		' - You need to have access to mysql/mysqldump' . PHP_EOL .
		' - Output directory must exist and must be writable by you' . 
		PHP_EOL . PHP_EOL .
		'--------' . PHP_EOL . 'OPTIONS' . PHP_EOL . 
		'--------' . PHP_EOL . 
		'-h Host Name of MySQL' . PHP_EOL . 
		'-u User Name of MySQL' . PHP_EOL . 
		'-p Password of MySQL' . PHP_EOL . 
		'-d Database Name of MySQL' . PHP_EOL . 
		'-o Folder of where to store SQL files (backup) ' . 
		'or located (restore)' . PHP_EOL . PHP_EOL .
		'------' . PHP_EOL . 'USAGE' . PHP_EOL . 
		'------' . PHP_EOL . 
		'To backup a database:' . PHP_EOL . 
		'php ASHDumper.php -b yes -h localhost -u root -pPassword ' . 
		'-o c:\abspath\to\output\dir' . PHP_EOL . 
		'[PHP >= 5.3] php ASHDumper.php --backup -h localhost -u root -pPassword ' . 
		'-o c:\abspath\to\output\dir' . PHP_EOL . PHP_EOL . 
		PHP_EOL . 'To restore a database (DB Must Exist):' . PHP_EOL . 
		'php ASHDumper.php -r yes -h localhost -u root -pPassword -o ' . 
		'c:\abspath\to\sql\dir'. PHP_EOL . 
		'php ASHDumper.php --restore -h localhost -u root -pPassword -o ' . 
		'[PHP >= 5.3] c:\abspath\to\sql\dir'. PHP_EOL . PHP_EOL .		
		
		PHP_EOL . 'To restore a database (Create DB If It Does Not Exist):' . PHP_EOL . 
		'php ASHDumper.php -r yes -c yes -h localhost -u root -pPassword -o ' . 
		'c:\abspath\to\sql\dir'. PHP_EOL .
		'[PHP >= 5.3] php ASHDumper.php --restore --create -h localhost -u root -pPassword -o ' . 
		'c:\abspath\to\sql\dir'. PHP_EOL;;

		return;
	}
	
	/**
	 * Restore a DB
	 * 
	 * @param	array	$dbInfo
	 *					userName	MySQL Username
	 *					hostName	MySQL Host Name
	 *					password	MySQL User Password
	 *					outputDir	Directory to write SQL files to
	 * @param	array	$options	Options Param
	 *
	 * @return void
	 */
	public static function restore($dbInfo, $options)
	{
		// Create Database if it does not exist
		if (isset($options['c']) || isset($options['create'])) {
			self::createIfNotExist($dbInfo);
		}

		// Get files to restore
		$tableList = array();
		$files = glob($dbInfo['outputDir'] . '*.sql', GLOB_BRACE);
		foreach ($files as $file) {
			$tableList[] = str_replace('.sql', '', basename($file));
		}
		
		// Command
		$command = "mysql -u {$dbInfo['userName']} -p{$dbInfo['password']} " . 
		"-h {$dbInfo['hostName']} {$dbInfo['database']} < {$dbInfo['outputDir']}%s.sql";

		self::runWorkers($command, $tableList);
		
		return;
	}
	
	/**
	 * Backup a DB
	 * 
	 * @param	array	$dbInfo
	 *					userName	MySQL Username
	 *					hostName	MySQL Host Name
	 *					password	MySQL User Password
	 *					outputDir	Directory to read files from
	 *
	 * @return void
	 */
	public static function backup($dbInfo)
	{
		// Get table list
		$tableList = self::getTables($dbInfo);
		
		// Command
		$command = "mysqldump -u {$dbInfo['userName']} -p{$dbInfo['password']} " . 
		"-h {$dbInfo['hostName']} {$dbInfo['database']} %s > {$dbInfo['outputDir']}%s.sql";

		self::runWorkers($command, $tableList);
		
		return;
	}
	
	/**
	 * Get list of tables from DB, used in the backup method
	 *
	 * @param	array	$dbInfo
	 *					userName	MySQL Username
	 *					hostName	MySQL Host Name
	 *					password	MySQL User Password
	 *
	 * @return 	array
	 */
	public static function getTables($dbInfo)
	{
		$tables = array();
		try {
			// Connect to db
			$dsn = "mysql:dbname={$dbInfo['database']};host={$dbInfo['hostName']}";
			$db = new Pdo($dsn, $dbInfo['userName'], $dbInfo['password']);
			
			$result = $db->query("show tables");
			while ($row = $result->fetch(PDO::FETCH_NUM)) {
				$tables[] = $row[0];
			}
		} catch (PDOException $e) {
			// Any errors, show them to user
			echo 'Failed: ' . $e->getMessage();
			exit();
		}
		
		return $tables;
	}
	
	/**
	 * Create database if it does not exist
	 *
	 * @param	array	$dbInfo
	 *					userName	MySQL Username
	 *					hostName	MySQL Host Name
	 *					password	MySQL User Password
	 *					database	Database Name
	 *
	 * @return 	void
	 */
	public static function createIfNotExist($dbInfo)
	{
		try {
			// Connect to db
			$dsn = "mysql:host={$dbInfo['hostName']}";
			$db = new Pdo($dsn, $dbInfo['userName'], $dbInfo['password']);
			// Create database
			$db->query("CREATE DATABASE IF NOT EXISTS `{$dbInfo['database']}`");
		} catch (PDOException $e) {
			// Any errors, show them to user
			echo 'Failed: ' . $e->getMessage();
			exit();
		}

		return;
	}
	
	/**
	 * Run a worker for each table
	 *
	 * @param	string	$jobCommand		Command to execute
	 * @param	array	$tables			List of tables to process
	 *
	 * @return void
	 */
	public static function runWorkers($jobCommand, $tables)
	{
		// presets
		$threads		= array();
		$resultStream	= array();
		$tableRealtion	= array();
		$options		= array(1 => array('pipe', 'w'));
		
		// Counts
		$tableCount		= count($tables);
		$doneCount		= 0;
		
		// Start workers
		while (true) {
			// Any tables left to do?
			if (count($tables) > 0) {

				$table = array_shift($tables);
				// Construct the process command with process ID & current db to use
				$command = sprintf($jobCommand, $table, $table);

				$pipes = array();

				// open thread
				$threads[]			= proc_open($command, $options, $pipes);
				$resultStream[]		= $pipes;
				$tableRealtion[]	= $table;

				// If this thread started
				if (end($threads) == false) {
					// If it fails, close the thread & pipe
					$closeCount = count($threads)-1;
					unset($threads[$closeCount]);
					unset($resultStream[$closeCount]);
					unset($tableRealtion[$closeCount]);
					
					// Put table back in if failed
					array_unshift($tables, $table);
				}
			} else if (count($threads) <= 0) {
				break;
			}
        
			foreach($threads as $sub => $thisThread) {
				// Get the status
				$status = proc_get_status($thisThread);
				// If its not running or stopped, close it & get the results
				if ($status['running'] != 'true' || $status['signaled'] == 'true') {
					$doneCount++;
					$results = stream_get_contents($resultStream[$sub][1]);
					
					// Any errors
					if (!empty($results)) {
						echo 'Error processing table ' . $tableRealtion[$sub] . 
						': ' . $results . PHP_EOL;
					} else {
						echo 'Completed Table: ' . $tableRealtion[$sub] . PHP_EOL;
					}
					
					echo ($tableCount - $doneCount) . ' Tables Remaining' . PHP_EOL . PHP_EOL;

					// Close the pipe & threads					
					fclose($resultStream[$sub][1]);
					unset($threads[$sub]);
					unset($resultStream[$sub]);
					unset($tableRealtion[$sub]);
				}
			}
		}
	}
}

ASHDumper::load();