Location: PHPKode > scripts > DB Version Manager > lib/db_updater.php
<?php
/**
* DB version manager
*
* Copyright (c) 2011 Przemek Berezowski (hide@address.com)
* All rights reserved.
*
* @category	  	 Library
* @package  	 DBVersionManager
* @copyright     Copyright (c) 2011 Przemek Berezowski (hide@address.com)
* @version       0.9
* @license       New BSD License
*/


require_once('tools.php');

/**
 * Class DbUpdater
 * @author pberezowski
 *
 */
class DbUpdater {
	
	const OUTPUT_DIR = 'output';
	
	/**
	 * Messages holder
	 * @var string
	 */
	public $updateMessage = array();
	
	/**
	 * Config reference
	 * @var ConfigManager
	 */
	private $config;
	
	/**
	 * Database connection holder
	 * @var PDO
	 */
	private $connection;
	
	/**
	 * List of sql commands to execute
	 * @var array
	 */
	private $commands = array();
	
	/**
	* List of executed files during updating 
	*/
	private $executedFiles = array();
	
	public function __construct(ConfigManager $config) {
		$this->config = $config;
	}
	
	/**
	 * Reads the files from sqlFiles directory and compare 
	 * it with already executed files
	 * @return array
	 */
	public function getFilesToExecute() {
		$sqlFiles = Tools::readDir($this->config->getSqlPath());
		$execFiles = $this->getExecutedFiles();
		$diff = array_diff($sqlFiles, $execFiles);
		return $diff;
	}
	
	/**
	 * Perform operations to update database from files
	 */
	public function updateDb() {
		//read the sql files
		$sqlFiles = $this->getFilesToExecute();
		if (count($sqlFiles) == 0) {
			$this->updateMessage[] = 'Db is up to date';
			return;
		}
		$this->connect();
		$this->doUpdate($sqlFiles);
		$this->disconnect();
	}
	
	/**
	 * Reads the command string, explode it to separete commands 
	 * and wrtie this commands to files 
	 * 
	 * @param string $commandString
	 */
	public function prepareFiles($commandString) {
		//if the PHP adds magic quotes, add stripslashes
		//it might be a security issue :(
		//@TODO - think about it. 
		if (get_magic_quotes_gpc()) {
			$commandString = stripslashes($commandString);
		}
		$cArray = $this->extractCommands($commandString);
		foreach($cArray as $key => $command) {
			if (empty($command)) {
				continue;
			}
			
			$fName = $this->config->getSqlPath().'/'.date('YmdHis').'_'.$this->config->getName().'_'.$key.'.sql';
			file_put_contents($fName, $command);
		}
	}
	
	/**
	 * 
	 * Conects to database
	 */
	protected function connect() {
		$this->connection = new PDO($this->config->getDsn(), $this->config->getDbUser(), $this->config->getDbPass());
		$this->updateMessage[] = 'Conected to db'; 
		//TODO - check if conection not failed
	}
	
	/**
	 * Disconects from database
	 */
	protected function disconnect() {
		$this->connection = null;
		$this->updateMessage[] = 'Disconnected';
	}
	
	/**
	 * Perform sql queries from files
	 * @param array $fileList 
	 */
	protected function doUpdate($fileList) {
		
		foreach($fileList as $file) {
			$path = $this->config->getSqlPath().'/'.$file;
			//explode posibble commands from sql file
			$commands = $this->extractCommands(file_get_contents($path));
			$this->updateMessage[] = 'Procesing file: '.$file;
			$success = true;
			$i = 0;
			
			if ($this->config->getSqlUseTransactions()) {
				$this->connection->beginTransaction();
			}
			
			foreach ($commands as $command) {
				$i++;
				
				$command = trim($command);
				
				if (empty($command)) {
					continue;
				}
				if ($this->connection->exec($command) === false) {
					$this->updateMessage[] = 'Command: '.$command.' in file '.$file.' failed';
					$success = false;
					break;
				}
				$this->updateMessage[] = 'Command no '.$i.' from '.$file.' executed';
			}
			
			if (!$success) {
				$err = $this->connection->errorInfo();
				$this->updateMessage[] = $err[2];
				if ($this->config->getSqlUseTransactions()) {
					$this->connection->rollBack();
					$this->updateMessage[] = 'Rollback changes. Transaction failed error in file '.$file;
				}
				break;
			} else {
				if ($this->config->getSqlUseTransactions()) {
					$this->connection->commit();
				}
			}
			$this->executedFiles[] = $file;
		}
		if (!$success) {
			$this->updateMessage[] = 'There are error in sql commands. DB is not up to date';
		} else {
			$this->updateMessage[] = 'Db is up to date';
		}
		
		
		$this->writeOutput();
		
	}
	
	/**
	 * Explodes string with sql commands to array
	 * @param string $commandString
	 * @return array list of commands
	 */
	protected function extractCommands($commandString) {
		//regex found at http://www.dev-explorer.com/articles/multiple-mysql-queries
		$reg = "/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/";
		$x = explode('-|||-', preg_replace($reg, '-|||-', $commandString));
		return $x; 
	}
	
	/**
	 * Return already executed files
	 */
	protected function getExecutedFiles() {
		$executedInfoFile = Tools::getProjectPath().'/'.self::OUTPUT_DIR.'/'.$this->config->getName().'.xml';
		if (!file_exists($executedInfoFile)) {
			return array();	
		}
		
		$xml = Tools::readXml($executedInfoFile);
		$xPath = "//file[@name]";
		$res = $xml->xpath($xPath);
		
		$ret = array();

		foreach ($res as $element) {
			$attr = $element->attributes();
			$ret[] = (string)$attr['name'];
		}
		return $ret;
	}
	
	/**
	 * Save executed file names to the output xml
	 */
	protected function writeOutput() {
		$path = Tools::getProjectPath().'/'.self::OUTPUT_DIR.'/'.$this->config->getName().'.xml';
		if (!file_exists($path)) {
			$this->createOutputFile();
		}
		
		$xml = Tools::readXml($path);
		foreach ($this->executedFiles as $fName) {
			$chl = $xml->addChild('file');
			$chl->addAttribute('name', $fName);
		}
		
		$xml['version'] = date('Ymd-His');
		file_put_contents($path, $xml->asXML());
		
	}
	
	/**
	 * Creates output file
	 */
	protected function createOutputFile() {
		$path = Tools::getProjectPath().'/'.self::OUTPUT_DIR.'/'.$this->config->getName().'.xml';
		$version = date('Ymd-His');
		$initXML = '<?xml version="1.0" encoding="UTF-8"?><commands version="'.$version.'"></commands>';
		$xml = simplexml_load_string($initXML);
		file_put_contents($path, $xml->asXML());
	}
	
} //end class



class UpdaterException extends Exception {}
Return current item: DB Version Manager