<?php/*** @version $Id: cb.sql.upgrader.php 444 2008-03-05 02:25:39Z beat $* @package Community Builder* @subpackage cb.sql.upgrader.php* @author Beat* @copyright (C) 2007 Beat and Lightning MultiCom SA, 1009 Pully, Switzerland* @license Lightning Proprietary. See licence. Allowed for free use within CB and for CB plugins.*/// no direct accessif ( ! ( defined( '_VALID_CB' ) || defined( '_JEXEC' ) || defined( '_VALID_MOS' ) ) ) { die( 'Direct Access to this location is not allowed.' ); }// cbimport('cb.xml.simplexml'); needed to use this./** * CB SQL versioning / upgrading functions: *  * WARNING: * This new library is experimental work in progress and should not be used directly by plugins and 3pds, * as it is subject to change without notice, and is not part of current CB API. *  * @access private *  */class CBSQLupgrader {	/** Database	 * @var CBdatabase */	var $_db				=	null;	var $_silentTestLogs	=	true;	var $_logs				=	array();	var $_errors			=	array();	var $_logsIndex			=	0;	var $_dryRun			=	false;	/**	 * Constructor	 *	 * @param  CBdatabase     $db	 * @param  boolean        $silentTestLogs  TRUE: Silent on successful tests	 * @return CBSQLupgrader	 */	function CBSQLupgrader( &$db, $silentTestLogs = true ) {		$this->_db								=&	$db;		$this->_silentTestLogs					=	$silentTestLogs;	}	/**	 * Sets if SQL tables changing queries should not be run (for a dryrun)	 *	 * @param  boolean  $dryRun  FALSE (default): tables are changed, TRUE: Dryrunning	 */	function setDryRun( $dryRun ) {		$this->_dryRun							=	$dryRun;	}	/**	 * LOGS OF ACTIONS AND OF ERRORS:	 */	/**	 * Records error with details (details here is SQL query)	 * @access private	 *	 * @param  string  $error	 * @param  string  $info	 */	function _setError( $error, $info = null) {		$this->_errors[++$this->_logsIndex]		=	array( $error, $info );	}	/**	 * Returns all errors logged	 *	 * @param  string|boolean  $implode         False: returns full array, if string: imploding string	 * @param  string|boolean  $detailsImplode  False: no details, otherwise imploding string	 * @return string|array	 */	function getErrors( $implode = "\n", $detailsImplode = false ) {		if ( $implode === false) {			return $this->_errors;		} else {			$errors								=	array();			if ( $detailsImplode ) {				foreach ( $this->_errors as $errInfo ) {					$errors[]					=	implode( $detailsImplode, $errInfo );				}			} else {				foreach ( $this->_errors as $errInfo ) {					$errors[]					=	$errInfo[0];				}			}			return implode( $implode, $errors );		}	}	/**	 * Records logs with details (details here are SQL queries ( ";\n"-separated )	 * @access private	 *	 * @param  string  $log	 * @param  string  $info	 * @param  string  $type  'ok': successful check, 'change': successful change	 */	function _setLog( $log, $info = null, $type ) {		if ( ( $type != 'ok' ) || ! $this->_silentTestLogs ) {			$this->_logs[++$this->_logsIndex]	=	array( $log, $info );		}	}	/**	 * Returns all logs logged	 *	 * @param  string|boolean  $implode         False: returns full array, if string: imploding string	 * @param  string|boolean  $detailsImplode  False: no details, otherwise imploding string	 * @return string|array	 */	function getLogs( $implode = "\n", $detailsImplode = false ) {		if ( $implode === false) {			return $this->_logs;		} else {			$logs								=	array();			if ( $detailsImplode ) {				foreach ( $this->_logs as $logInfo ) {					$logs[]						=	implode( $detailsImplode, $logInfo );				}			} else {				foreach ( $this->_logs as $logInfo ) {					$logs[]						=	$logInfo[0];				}			}			return implode( $implode, $logs );		}	}	/**	 * SQL ACCESS FUNCTIONS:	 */	/**	 * Checks if a given table exists in the database	 * @access private	 *	 * @param  string  $tableName  Name of table	 * @return boolean	 */	function checkTableExists( $tableName ) {		$allTables								=	$this->_db->getTableList();		return ( in_array( $tableName, $allTables ) );	}	/**	 * Checks if table exists in database and returns all fields of table.	 * Otherwise returns boolean false.	 * @access private	 *	 * @param  string  $tableName  Name of table	 * @return array|boolean       Array of SHOW COLUMNS FROM ... in SQL or boolean FALSE	 */	function getAllTableColumns( $tableName ) {		if ( $this->checkTableExists( $tableName ) ) {			$fields								=	$this->_db->getTableFields( $tableName, false );			if ( isset( $fields[$tableName] ) ) {				return $fields[$tableName];			}		}		return false;	}	/**	 * Returns all indexes of the table	 * @access private	 *	 * @param  string  $tableName  Name of table	 * @return array               Array of SHOW INDEX FROM ... in SQL	 */	function getAllTableIndexes( $tableName ) {		$sortedIndex							=	array();		$idx									=	$this->_db->getTableIndex( $tableName );		if ( is_array( $idx ) ) {			foreach ( $idx as $k => $n ) {				$sortedIndex[$n->Key_name][$n->Seq_in_index]	=	array(	'name'		=>	$n->Column_name,																			'size'		=>	$n->Sub_part,																			'ordering'	=>	$n->Collation,																			'type'		=>	( $n->Key_name == 'PRIMARY' ? 'primary' : ( $n->Non_unique == 0 ? 'unique' : '' ) ),																			'using'		=>	( array_key_exists( 'Index_type', $n ) ? strtolower( $n->Index_type ) : ( $n->Comment == 'FULLTEXT' ? 'fulltext' : '' ) )	// mysql <4.0.2 support																		 );			}		}		return $sortedIndex;	}	/**	 * COLUMNS CHECKS:	 */	/**	 * Checks if a column exists and has the type of the parameters below:	 * @access private	 *	 * @param  string              $tableName        Name of table (for error strings)	 * @param  array               $allColumns       From $this->getAllTableColumns( $table )	 * @param  CBSimpleXMLElement  $column           Column to check	 * @param  string              $colNamePrefix    Prefix to add to all column names	 * @param  boolean             $change           TRUE: only true/false check type, FALSE: logs success and if mismatch, error details	 * @return boolean             TRUE: identical (no check on indexes), FALSE: errors are in $this->getErrors()	 */	function checkColumnExistsType( $tableName, &$allColumns, &$column, $colNamePrefix, $change ) {		$colName								=	$this->_prefixedName( $column, $colNamePrefix );		if ( isset( $allColumns[$colName] ) ) {			if ( ! cbStartOfStringMatch( $column->attributes( 'type' ), 'sql:' ) ) {				$this->_setError( sprintf( 'Table %s Column %s type is %s instead of being prefixed by "sql:"', $tableName, $colName, $column->attributes( 'type' ) ) );				return false;			}			if ( $column->attributes( 'strict' ) === 'false' ) {				$this->_setLog( sprintf( 'Table %s Column %s exists but is not of strict type, so not checked.', $tableName, $colName ), null, 'ok' );				return true;			}			$type								=	substr( $column->attributes( 'type' ), 4 );			$shouldbeType						=	$type . ( $column->attributes( 'unsigned' ) === 'true' ? ' unsigned' : '' );			if ( $allColumns[$colName]->Type !== $shouldbeType ) {				if ( $change === false ) {					$this->_setError( sprintf( 'Table %s Column %s type is %s instead of %s', $tableName, $colName, $allColumns[$colName]->Type, $shouldbeType ) );				}				return false;			}			if ( ( $column->attributes( 'null' ) === 'true' ) !== ( $allColumns[$colName]->Null == 'YES' ) ) {		//if ( $column->attributes( 'null' ) !== null ): no attribute NULL means NOT NULL				if ( $change === false ) {					$this->_setError( sprintf( 'Table %s Column %s NULL attribute is %s instead of %s', $tableName, $colName, $allColumns[$colName]->Null, ( $column->attributes( 'null' ) === 'true' ? 'YES' : 'NO') ) );				}				return false;			}			// BLOB and TEXT columns cannot have DEFAULT values. http://dev.mysql.com/doc/refman/5.0/en/blob.html			$defaultValuePossible				=	! in_array( $type, array( 'text', 'blob', 'tinytext', 'mediumtext', 'longtext', 'tinyblob', 'mediumblob', 'longblob' ) );			// autoincremented columns don't care for default values:			$autoIncrementedColumn				=	! in_array( $column->attributes( 'auto_increment' ), array( null, '', 'false' ), true );			if ( $defaultValuePossible && ! $autoIncrementedColumn ) {				if ( $column->attributes( 'default' ) === null ) {					if ( $column->attributes( 'null' ) === 'true' ) {						$shouldbeDefault			=	array( null );					} else {						$shouldbeDefault			=	$this->defaultValuesOfTypes( $this->mysqlToXmlsql( $column->attributes( 'type' ) ) );					}				} else {					$shouldbeDefault				=	( $column->attributes( 'default' ) === 'NULL' ? array( null ) : array( $column->attributes( 'default' ) ) );				}				if ( ! in_array( $allColumns[$colName]->Default, $shouldbeDefault, true ) ) {					if ( $change === false ) {						$this->_setError( sprintf( 'Table %s Column %s DEFAULT is %s instead of %s', $tableName, $colName, $this->displayNull( $allColumns[$colName]->Default ), $column->attributes( 'default' ) ) );					}					return false;				}			}			$shouldbeExtra						=	( $autoIncrementedColumn ? 'auto_increment' : '' );			if ( $allColumns[$colName]->Extra !== $shouldbeExtra ) {				if ( $change === false ) {					$this->_setError( sprintf( 'Table %s Column %s AUTO_INCREMENT attribute is "%s" instead of "%s"', $tableName, $colName, $allColumns[$colName]->Extra, $shouldbeExtra ) );				}				return false;			}			$this->_setLog( sprintf( 'Table %s Column %s structure is up-to-date.', $tableName, $colName ), null, 'ok' );			return true;		}		if ( $change === false ) {			$this->_setError( sprintf( 'Table %s Column %s does not exist', $tableName, $colName ), null );		}		return false;	}	/**	 * Utility to display NULL for nulls and quotations.	 *	 * @param unknown_type $val	 * @return unknown	 */	function displayNull( $val ) {		if ( $val === null ) {			return 'NULL';		} elseif ( is_numeric( $val ) ) {			return $val;		} else {			return "'" . $val . "'";		}	}	/**	 * Checks if a column exists and has the type of the parameters below:	 * @access private	 *	 * @param  string              $tableName       Name of table (for error strings)	 * @param  array               $allColumns      From $this->getAllTableColumns( $table )	 * @param  CBSimpleXMLElement  $columns         Columns to check array of string  Name of columns which are allowed to (should) exist	 * @param  string              $colNamePrefix    Prefix to add to all column names	 * @param  boolean             $drop            TRUE If drops unneeded columns or not	 * @return boolean             TRUE: no other columns exist, FALSE: errors are in $this->getErrors()	 */	function checkOtherColumnsExist( $tableName, &$allColumns, &$columns, $colNamePrefix, $drop = false ) {		$isMatching								=	false;		if ( $columns->name() == 'columns' ) {			$isMatching							=	true;			foreach ( array_keys( $allColumns ) as $existingColumnName ) {				if ( ! $this->_inXmlChildrenAttribute( $existingColumnName, $columns, 'column', 'name', $colNamePrefix ) ) {					if ( $drop ) {						if ( ! $this->dropColumn( $tableName, $existingColumnName ) ) {							$isMatching			=	false;						}					} else {						$isMatching				=	false;						$this->_setError( sprintf( 'Table %s Column %s exists but should not exist', $tableName, $existingColumnName ), null );					}				}			}			if ( $isMatching && ! $drop ) {				$this->_setLog( sprintf( 'Table %s has no unneeded columns.', $tableName ), null, 'ok' );			}		}		return $isMatching;	}	/**	 * INDEXES CHECKS:	 */	/**	 * Checks if an index exists and has the type of the parameters below:	 * @access private	 *	 * @param  string              $tableName        Name of table (for error strings)	 * @param  array               $allIndexes       From $this->getAllTableIndexes( $table )	 * @param  CBSimpleXMLElement  $index            Index to check	 * @param  string              $colNamePrefix    Prefix to add to all column names	 * @param  boolean             $change           TRUE: only true/false check type, FALSE: logs success and if mismatch, error details	 * @return boolean             TRUE: identical, FALSE: errors are in $this->getErrors()	 */	function checkIndexExistsType( $tableName, &$allIndexes, &$index, $colNamePrefix, $change ) {