<?php
/**
 *	pluginbuddy_dbreplace Class
 *
 *	Handles replacement of data in a table/database, text or serialized. A database connection should be initialized before instantiation.
 *	
 *	Version: 1.0.0
 *	Author: Dustin Bolton
 *	Author URI: http://dustinbolton.com/
 *
 *	@param		$status_callback		object		Optional object containing the status() function for reporting back information.
 *	@return		null
 *
 */
if (!class_exists("pluginbuddy_dbreplace")) {
	class pluginbuddy_dbreplace {
		var $_version = '1.0';
		
		var $startTime;
		var $timeWiggleRoom;
		var $maxExecutionTime;
		
		const MAX_ROWS_PER_SELECT = 500;
		
		
		/**
		 *	__construct()
		 *	
		 *	Default constructor. Sets up optional status() function class if applicable.
		 *	
		 *	@param		reference	&$status_callback		[optional] Reference to the class containing the status() function for status updates.
		 *	@return		null
		 *
		 */
		function __construct( $startTime = '', $timeWiggleRoom = 1, $maxExecutionTime = 30 ) {
			if ( '' == $startTime ) {
				$this->startTime = microtime( true );
			} else {
				$this->startTime = $startTime;
			}
			$this->timeWiggleRoom = $timeWiggleRoom;
			$this->maxExecutionTime = $maxExecutionTime;
		}
		
		
		
		/**
		 *	text()
		 *	
		 *	Replaces text within a table by specifying the table, rows to replace within and the old and new value(s).
		 *	
		 *	@param		string		$table		Table to replace text in.
		 *	@param		mixed		$olds		Old value(s) to find for replacement. May be a string or array of values.
		 *	@param		mixed		$news		New value(s) to be replaced with. May be a string or array. If array there must be the same number of values as $olds.
		 *	@param		mixed		$rows		Table row(s) to replace within. May be an array of tables.
		 *	@return		null
		 *
		 */
		public function text( $table, $olds, $news, $rows ) {
			$rows_sql = array();
			
			if ( !is_array( $olds ) ) {
				$olds = array( $olds );
			}
			if ( !is_array( $news ) ) {
				$news = array( $news );
			}
			if ( !is_array( $rows ) ) {
				$rows = array( $rows );
			}
			
			// Prevent trying to replace data with the same data for performance.
			$this->remove_matching_array_elements( $olds, $news );
			
			foreach ( $rows as $row ) {
				$i = 0;
				foreach ( $olds as $old ) {
					$rows_sql[] = $row . " = replace( {$row}, '{$old}', '{$news[$i]}')";
					$i++;
				}
			}
			
			global $wpdb;
			$wpdb->query( "UPDATE `{$table}` SET " . implode( ',', $rows_sql ) . ";" );
			
			return true;
			
		} // End text().
		
		
		/**
		 *	serialized()
		 *	
		 *	Replaces serialized text within a table by specifying the table, rows to replace within and the old and new value(s).
		 *	
		 *	@param		string		$table		Table to replace text in.
		 *	@param		mixed		$olds		Old value(s) to find for replacement. May be a string or array of values.
		 *	@param		mixed		$news		New value(s) to be replaced with. May be a string or array. If array there must be the same number of values as $olds.
		 *	@param		mixed		$rows		Table row(s) to replace within. May be an array of tables.
		 *	@param		int			$rows_start	Row to start at. Used for resuming.
		 *	@return		null
		 *
		 */
		public function serialized( $table, $olds, $news, $rows, $rows_start = '' ) {
			if ( !is_array( $olds ) ) {
				$olds = array( $olds );
			}
			if ( !is_array( $news ) ) {
				$news = array( $news );
			}
			if ( !is_array( $rows ) ) {
				$rows = array( $rows );
			}
			
			global $wpdb;
			
			// Get the total row count for this table
			$total_rows = 0;
			$tables_status = $wpdb->get_results( "SHOW TABLE STATUS", ARRAY_A );
			
			foreach ( $tables_status as $table_status ) {
			
				if ( $table === $table_status[ 'Name' ] ) {
				
					// Fix up row count and average row length for InnoDB engine which returns inaccurate
					// (and changing) values for these
					if ( 'InnoDB' === $table_status[ 'Engine' ] ) {
						if ( false !== ( $count = $wpdb->get_var( "SELECT COUNT(1) FROM `{$table_status['Name']}`" ) ) ) {
							$table_status[ 'Rows' ] = $count;
						}
					}
			
					$total_rows = $table_status[ 'Rows' ];
				
				}
			
			}
			
			// Prevent trying to replace data with the same data for performance.
			$this->remove_matching_array_elements( $olds, $news );
			$key_results = $wpdb->get_results( "show keys from {$table} WHERE Key_name='PRIMARY';", ARRAY_A );
			if ( $key_results === false ) {
				pb_backupbuddy::status( 'details', 'Table `' . $table . '` does not exist; skipping migration of this table.' );
				return;
			}
			
			// No primary key found; unsafe to edit this table. @since 2.2.32.
			if ( count( $key_results ) == 0 ) {
				pb_backupbuddy::status( 'message', 'Error #9029: Warning only! Table `'.  $table .'` does not contain a primary key; BackupBuddy cannot safely modify the contents of this table. Skipping migration of this table. (serialized()).' );
				return true;
			}
			
			$primary_key = $key_results[0]['Column_name'];
			unset( $key_result );
			
			$updated = false; // Was something in DB updated?
			$rows_remain = true; // More rows remaining / aka another query for more rows needed.
			if ( '' == $rows_start ) {
				$rows_start = 0;
			}
			pb_backupbuddy::status( 'details', 'Finding rows in table `' . $table . '` to update.' );
			while ( true === $rows_remain ) { // Keep looping through rows until none remain. Looping through like this to limit memory usage as wpdb classes loads all results into memory.
				$rowsResult = $wpdb->get_results( "SELECT `" . implode( '`,`', $rows ) . "`,`{$primary_key}` FROM `{$table}` LIMIT " . $rows_start . ',' . self::MAX_ROWS_PER_SELECT );
				$rowsCount = count( $rowsResult );

				// Provide an update on progress
				if ( 0 === $rowsCount ) {
					pb_backupbuddy::status( 'details', 'Table: `'. $table . '` - processing ' . $rowsCount . ' rows ( of ' . $total_rows . ' )' );				
				} else {
					pb_backupbuddy::status( 'details', 'Table: `'. $table . '` - processing ' . $rowsCount . ' rows ( Rows ' . $rows_start . '-' . ( $rows_start + $rowsCount - 1 ) . ' of ' . $total_rows . ' )' );	
				}
				
				$rows_start += self::MAX_ROWS_PER_SELECT; // Next loop we will begin at this offset.
				if ( ( 0 == $rowsCount ) || ( $rowsCount < self::MAX_ROWS_PER_SELECT ) ) {
					$rows_remain = false;
				}
				
				foreach( $rowsResult as $row ) {
					$needs_update = false;
					$sql_update = array();
					
					foreach( $row as $column => $value ) {
						if ( $column != $primary_key ) {
							if ( false !== ( $edited_data = $this->replace_maybe_serialized( $value, $olds, $news ) ) ) { // Data changed.
								$needs_update = true;
								$sql_update[] = $column . "= '" . mysql_real_escape_string( $edited_data ) . "'";
							}
						} else {
							$primary_key_value = $value;
						}
					}
					
					if ( $needs_update === true ) {
						$updated = true;
						$wpdb->query( "UPDATE `{$table}` SET " . implode( ',', $sql_update ) . " WHERE `{$primary_key}` = '{$primary_key_value}' LIMIT 1" );
					}
				}
				unset( $rowsResult );
				
				// See how we are doing on time. Trigger chunking if needed.
				if ( ( ( microtime( true ) - $this->startTime ) + $this->timeWiggleRoom ) >= $this->maxExecutionTime ) {
					return array( $rows_start );
				}
			}
			
			if ( $updated === true ) {
				pb_backupbuddy::status( 'details', 'Updated serialized data in table `' . $table . '`.' );
			} else {
				pb_backupbuddy::status( 'details', 'Nothing found to update in table `' . $table . '`.' );
			}
			return true;
		} // End serialized().
		
		
		/**
		 *	replace_maybe_serialized()
		 *	
		 *	Replaces possibly serialized (or non-serialized) text if a change is needed. Returns false if there was no change.
		 *  Note: As of BB v3.2.x supports double serialized data.
		 *	
		 *	@param		string		$table		Text (possibly serialized) to update.
		 *	@param		mixed		$olds		Text to search for to replace. May be an array of strings to search for.
		 *	@param		mixed		$news		New value(s) to be replaced with. May be a string or array. If array there must be the same number of values as $olds.
		 *	@return		mixed					Returns modified string data if serialized data was replaced. False if no change was made.
		 *
		 */
		function replace_maybe_serialized( $data, $olds, $news ) {
			if ( !is_array( $olds ) ) {
				$olds = array( $olds );
			}
			if ( !is_array( $news ) ) {
				$news = array( $news );
			}
			
			$type = '';
			$unserialized = false; // first assume not serialized data
			if ( is_serialized( $data ) ) { // check if this is serialized data
				$unserialized = @unserialize( $data ); // unserialise - if false is returned we won't try to process it as serialised.
			}
			if ( $unserialized !== false ) { // Serialized data.
				$type = 'serialized';
				
				$double_serialized = false;
				if ( is_serialized( $unserialized ) ) { // double-serialized data (opposite of a double rainbow). Some plugins seem to double-serialize for some unknown wacky reason...
					$unserialized = @unserialize( $unserialized ); // unserialise - if false is returned we won't try to process it as serialised.
					$double_serialized = true;
				}
				
				$i = 0;
				foreach ( $olds as $old ) {
					$this->recursive_array_replace( $old, $news[$i], $unserialized );
					$i++;
				}
				
				$edited_data = serialize( $unserialized );
				if ( true === $double_serialized ) {
					$edited_data = serialize( $edited_data );
				}
				
			}	else { // Non-serialized data.
				$type = 'text';
				$edited_data = $data;
				$i = 0;
				foreach ( $olds as $old ) {
					$edited_data =str_ireplace( $old, $news[$i], $edited_data );
					$i++;
				}
			}
			
			// Return the results.
			if ( $data != $edited_data ) {
				return $edited_data;
			} else {
				return false;
			}
		} // End replace_maybe_serialized().
		
		
		/**
		 *	bruteforce_table()
		 *
		 *	!!! HANDLES SERIALIZED DATA !!!!
		 *	Replaces text, serialized or not, within the entire table. Bruteforce method iterates through every row & column in the entire table and replaces if needed.
		 *	
		 *	@param		string		$table		Text (possibly serialized) to update.
		 *	@param		mixed		$olds		Text to search for to replace. May be an array of strings to search for.
		 *	@param		mixed		$news		New value(s) to be replaced with. May be a string or array. If array there must be the same number of values as $olds.
		 *	@return		int						Number of rows changed.
		 *
		 */
		function bruteforce_table( $table, $olds, $news, $rows_start = '' ) {
			pb_backupbuddy::status( 'message', 'Starting brute force data migration for table `' . $table . '`...' );
			if ( !is_array( $olds ) ) {
				$olds = array( $olds );
			}
			if ( !is_array( $news ) ) {
				$news = array( $news );
			}
			
			$count_items_checked = 0;
			$count_items_changed = 0;
			
			global $wpdb;
			
			// Get the total row count for this table
			$total_rows = 0;
			$tables_status = $wpdb->get_results( "SHOW TABLE STATUS", ARRAY_A );
			
			foreach ( $tables_status as $table_status ) {
			
				if ( $table === $table_status[ 'Name' ] ) {
				
					// Fix up row count and average row length for InnoDB engine which returns inaccurate
					// (and changing) values for these
					if ( 'InnoDB' === $table_status[ 'Engine' ] ) {
						if ( false !== ( $count = $wpdb->get_var( "SELECT COUNT(1) FROM `{$table_status['Name']}`" ) ) ) {
							$table_status[ 'Rows' ] = $count;
						}
					}
			
					$total_rows = $table_status[ 'Rows' ];
				
				}
			
			}
			
			$fields = $wpdb->get_results( "DESCRIBE `{$table}`", ARRAY_A );
			$index_fields = '';  // Reset fields for each table.
			$column_name = '';
			$table_index = '';
			$i = 0;
			
			$found_primary_key = false;
			
			foreach( $fields as $field ) {
				$column_name[$i++] = $field['Field'];
				if ( $field['Key'] == 'PRI' ) {
					$table_index[$i] = true;
					$found_primary_key = true;
				}
			}
			
			// Skips migration of this table if there is no primary key. Modifying on any other key is not safe. mysql automatically returns a PRIMARY if a UNIQUE non-primary is found according to http://dev.mysql.com/doc/refman/5.1/en/create-table.html  @since 2.2.32.
			if ( $found_primary_key === false ) {
				pb_backupbuddy::status( 'warning', 'Error #9029b: Warning only! Table `' . $table . '` does not contain a primary key; BackupBuddy cannot safely modify the contents of this table. Skipping migration of this table. (bruteforce_table()).' );
				return true;
			}
			
			$row_loop = 0;

			$rows_remain = true; // More rows remaining / aka another query for more rows needed.
			if ( '' == $rows_start ) {
				$rows_start = 0;
			}

			while ( true === $rows_remain ) { // Keep looping through rows until none remain. Looping through like this to limit memory usage as wpdb classes loads all results into memory.

				$data = $wpdb->get_results( "SELECT * FROM `{$table}` LIMIT {$rows_start}," . self::MAX_ROWS_PER_SELECT, ARRAY_A );
				if ( false === $data ) {
					pb_backupbuddy::status( 'error', 'ERROR #44545343 ... SQL ERROR: ' . mysql_error() );
				}
				
				// Provide an update on progress
				$rowsCount = count( $data );
				if ( 0 === $rowsCount ) {
					pb_backupbuddy::status( 'details', 'Table: `'. $table . '` - processing ' . $rowsCount . ' rows ( of ' . $total_rows . ' )' );				
				} else {
					pb_backupbuddy::status( 'details', 'Table: `'. $table . '` - processing ' . $rowsCount . ' rows ( Rows ' . $rows_start . '-' . ( $rows_start + $rowsCount - 1 ) . ' of ' . $total_rows . ' )' );	
				}
				
				$rows_start += self::MAX_ROWS_PER_SELECT; // Next loop we will begin at this offset.
				if ( ( 0 == $rowsCount ) || ( $rowsCount < self::MAX_ROWS_PER_SELECT ) ) {
					$rows_remain = false;
				}
		
				foreach( $data as $row ) {
					$need_to_update = false;
					$UPDATE_SQL = 'UPDATE `' . $table . '` SET ';
					$WHERE_SQL = ' WHERE ';
				
					$j = 0;
					foreach ( $column_name as $current_column ) {
						$j++;
						$count_items_checked++;
					
						$data_to_fix = $row[$current_column];
						if ( false !== ( $edited_data = $this->replace_maybe_serialized( $data_to_fix, $olds, $news ) ) ) { // no change needed
							$count_items_changed++;
							if ( $need_to_update != false ) { // If this isn't our first time here, we need to add a comma.
								$UPDATE_SQL = $UPDATE_SQL . ',';
							}
							$UPDATE_SQL = $UPDATE_SQL . ' ' . $current_column . ' = "' . mysql_real_escape_string( $edited_data ) . '"';
							$need_to_update = true; // Only set if we need to update - avoids wasted UPDATE statements.
						}
					
						if ( isset( $table_index[$j] ) ) {
							$WHERE_SQL = $WHERE_SQL . '`' . $current_column . '` = "' . $row[$current_column] . '" AND ';
						}
					}
				
					if ( $need_to_update ) {
						$WHERE_SQL = substr( $WHERE_SQL , 0, -4 ); // Strip off the excess AND - the easiest way to code this without extra flags, etc.
						$UPDATE_SQL = $UPDATE_SQL . $WHERE_SQL;
						$result = $wpdb->query( $UPDATE_SQL );
						if ( false === $result ) {
							pb_backupbuddy::status( 'error', 'ERROR: mysql error updating db: ' . mysql_error() . '. SQL Query: ' . htmlentities( $UPDATE_SQL ) );
						} 
					}
					
				}

				unset( $data );

				// See how we are doing on time. Trigger chunking if needed.
				if ( ( ( microtime( true ) - $this->startTime ) + $this->timeWiggleRoom ) >= $this->maxExecutionTime ) {
					return array( $rows_start );
				}
			}
			
			pb_backupbuddy::status( 'message', 'Brute force data migration for table `' . $table . '` complete. Checked ' . $count_items_checked . ' items; ' . $count_items_changed . ' changed.' );
			
			return true;
		} // End bruteforce_table().
		
		
		/**
		 *	recursive_array_replace()
		 *	
		 *	Recursively replace text in an array, stepping through arrays within arrays as needed.
		 *	
		 *	@param		string		$find		Text to find.
		 *	@param		string		$replace	Text to replace found text with.
		 *	@param		reference	&$data		Pass the variable to change the data within.
		 *	@return		boolean					Always true currently.
		 *
		 */
		public function recursive_array_replace( $find, $replace, &$data ) {
			if ( is_array( $data ) ) {
				foreach ( $data as $key => $value ) {
					if ( is_array( $value ) ) {
						$this->recursive_array_replace( $find, $replace, $data[$key] );
					} else {
						// Have to check if it's string to ensure no switching to string for booleans/numbers/nulls - don't need any nasty conversions.
						if ( is_string( $value ) ) $data[$key] = str_replace( $find, $replace, $value );
					}
				}
			} else {
				if ( is_string( $data ) ) $data = str_replace( $find, $replace, $data );
			}
		}
		
		
		/**
		 * Check value to find if it was serialized.
		 *
		 * If $data is not an string, then returned value will always be false.
		 * Serialized data is always a string.
		 *
		 * Courtesy WordPress; since WordPress 2.0.5.
		 *
		 * @param mixed $data Value to check to see if was serialized.
		 * @return bool False if not serialized and true if it was.
		 */
		function is_serialized( $data ) {
			// if it isn't a string, it isn't serialized
			if ( ! is_string( $data ) )
				return false;
			$data = trim( $data );
		 	if ( 'N;' == $data )
				return true;
			$length = strlen( $data );
			if ( $length < 4 )
				return false;
			if ( ':' !== $data[1] )
				return false;
			$lastc = $data[$length-1];
			if ( ';' !== $lastc && '}' !== $lastc )
				return false;
			$token = $data[0];
			switch ( $token ) {
				case 's' :
					if ( '"' !== $data[$length-2] )
						return false;
				case 'a' :
				case 'O' :
					return (bool) preg_match( "/^{$token}:[0-9]+:/s", $data );
				case 'b' :
				case 'i' :
				case 'd' :
					return (bool) preg_match( "/^{$token}:[0-9.E-]+;\$/", $data );
			}
			return false;
		}
		
		
		/**
		 *	remove_matching_array_elements()
		 *	
		 *	Removes identical elements (same index and value) from both arrays where they match.
		 *
		 *	Ex:
		 *		// Before:
		 *		$a = array( 'apple', 'banana', 'carrot' );
		 *		$b = array( 'apple', 'beef', 'cucumber' );
		 *		remove_matching_array_elements( $a, $b );
		 *		// After:
		 *		$a = array( 'banana', 'carrot' );
		 *		$b = array( 'beef', 'cucumber' );
		 *	
		 *	@param		array		&$a		First array to compare with second. (reference)
		 *	@param		array		&$b		Second array to compare with first. (reference)
		 *	@return		null				Arrays passed are updated as they are passed by reference.
		 *
		 */
		function remove_matching_array_elements( &$a, &$b ) {
			$sizeof = sizeof( $a );
			for( $i=0; $i < $sizeof; $i++ ) {
				if ( $a[$i] == $b[$i] ) {
					unset( $a[$i] );
					unset( $b[$i] );
				}
			}
			
			$a = array_merge( $a ); // Reset numbering of keys.
			$b = array_merge( $b ); // Reset numbering of keys.
		}
		
		
	} // end pluginbuddy_dbreplace class.
}
?>