/**
* Adapated from interconnect/it's search/replace script.
* Modified to use WordPress wpdb functions instead of PHP's native mysql/pdo functions.
*
* @link https://interconnectit.com/products/search-and-replace-for-wordpress-databases/
*
* @access public
* @param string $table The table to run the replacement on.
* @param string $search The string to replace.
* @param string $replace The string to replace with.
* @return array Collection of information gathered during the run.
*/
public function revisr_srdb($table, $search = '', $replace = '')
{
// Get a list of columns in this table.
$columns = array();
$fields = $this->wpdb->get_results('DESCRIBE ' . $table);
foreach ($fields as $column) {
$columns[$column->Field] = $column->Key == 'PRI' ? true : false;
}
$this->wpdb->flush();
// Count the number of rows we have in the table if large we'll split into blocks, This is a mod from Simon Wheatley
$this->wpdb->get_results('SELECT COUNT(*) FROM ' . $table);
$row_count = $this->wpdb->num_rows;
if ($row_count == 0) {
continue;
}
$page_size = 50000;
$pages = ceil($row_count / $page_size);
for ($page = 0; $page < $pages; $page++) {
$current_row = 0;
$start = $page * $page_size;
$end = $start + $page_size;
// Grab the content of the table.
$data = $this->wpdb->get_results("SELECT * FROM {$table} LIMIT {$start}, {$end}", ARRAY_A);
// Loop through the data.
foreach ($data as $row) {
$current_row++;
$update_sql = array();
$where_sql = array();
$upd = false;
foreach ($columns as $column => $primary_key) {
$edited_data = $data_to_fix = $row[$column];
// Run a search replace on the data that'll respect the serialisation.
$edited_data = $this->recursive_unserialize_replace($search, $replace, $data_to_fix);
// Something was changed
if ($edited_data != $data_to_fix) {
$update_sql[] = $column . ' = "' . $this->mysql_escape_mimic($edited_data) . '"';
$upd = true;
}
if ($primary_key) {
$where_sql[] = $column . ' = "' . $this->mysql_escape_mimic($data_to_fix) . '"';
}
}
if ($upd && !empty($where_sql)) {
$sql = 'UPDATE ' . $table . ' SET ' . implode(', ', $update_sql) . ' WHERE ' . implode(' AND ', array_filter($where_sql));
$result = $this->wpdb->query($sql);
if (!$result) {
$error_msg = sprintf(__('Error updating the table: %s.', 'revisr'), $table);
}
} elseif ($upd) {
$error_msg = sprintf(__('The table "%s" has no primary key. Manual change needed on row %s.', 'revisr'), $table, $current_row);
}
}
}
$this->wpdb->flush();
if (isset($error_msg)) {
Revisr_Admin::log($error_msg, 'error');
return false;
}
}
/**
* Adapated from interconnect/it's search/replace script.
*
* Modified to use WordPress wpdb functions instead of PHP's native mysql/pdo functions,
* and to be compatible with batch processing via AJAX.
*
* @link https://interconnectit.com/products/search-and-replace-for-wordpress-databases/
*
* @access public
* @param string $table The table to run the replacement on.
* @param int $page The page/block to begin the query on.
* @param array $args An associative array containing arguements for this run.
* @return array
*/
public function srdb($table, $page, $args)
{
// Load up the default settings for this chunk.
$table = esc_sql($table);
$current_page = absint($page);
$pages = $this->get_pages_in_table($table);
$done = false;
$args['search_for'] = str_replace('#BSR_BACKSLASH#', '\\', $args['search_for']);
$args['replace_with'] = str_replace('#BSR_BACKSLASH#', '\\', $args['replace_with']);
$table_report = array('change' => 0, 'updates' => 0, 'start' => microtime(true), 'end' => microtime(true), 'errors' => array(), 'skipped' => false);
// Get a list of columns in this table.
list($primary_key, $columns) = $this->get_columns($table);
// Bail out early if there isn't a primary key.
if (null === $primary_key) {
$table_report['skipped'] = true;
return array(true, $table_report);
}
$current_row = 0;
$start = $page * $this->page_size;
$end = $this->page_size;
// Grab the content of the table.
$data = $this->wpdb->get_results("SELECT * FROM {$table} LIMIT {$start}, {$end}", ARRAY_A);
// Loop through the data.
foreach ($data as $row) {
$current_row++;
$update_sql = array();
$where_sql = array();
$upd = false;
foreach ($columns as $column) {
$data_to_fix = $row[$column];
if ($column == $primary_key) {
$where_sql[] = $column . ' = "' . $this->mysql_escape_mimic($data_to_fix) . '"';
continue;
}
// Skip GUIDs by default.
if ('on' !== $args['replace_guids'] && 'guid' == $column) {
continue;
}
if ($this->wpdb->options === $table) {
// Skip any BSR options as they may contain the search field.
if (isset($should_skip) && true === $should_skip) {
$should_skip = false;
continue;
}
// If the Site URL needs to be updated, let's do that last.
if (isset($update_later) && true === $update_later) {
$update_later = false;
$edited_data = $this->recursive_unserialize_replace($args['search_for'], $args['replace_with'], $data_to_fix, false, $args['case_insensitive']);
if ($edited_data != $data_to_fix) {
$table_report['change']++;
$table_report['updates']++;
update_option('bsr_update_site_url', $edited_data);
continue;
}
}
if ('_transient_bsr_results' === $data_to_fix || 'bsr_profiles' === $data_to_fix || 'bsr_update_site_url' === $data_to_fix) {
$should_skip = true;
}
if ('siteurl' === $data_to_fix && $args['dry_run'] !== 'on') {
$update_later = true;
}
}
// Run a search replace on the data that'll respect the serialisation.
$edited_data = $this->recursive_unserialize_replace($args['search_for'], $args['replace_with'], $data_to_fix, false, $args['case_insensitive']);
// Something was changed
if ($edited_data != $data_to_fix) {
$update_sql[] = $column . ' = "' . $this->mysql_escape_mimic($edited_data) . '"';
$upd = true;
$table_report['change']++;
}
}
// Determine what to do with updates.
if ($args['dry_run'] === 'on') {
// Don't do anything if a dry run
} elseif ($upd && !empty($where_sql)) {
// If there are changes to make, run the query.
$sql = 'UPDATE ' . $table . ' SET ' . implode(', ', $update_sql) . ' WHERE ' . implode(' AND ', array_filter($where_sql));
$result = $this->wpdb->query($sql);
if (!$result) {
$table_report['errors'][] = sprintf(__('Error updating row: %d.', 'better-search-replace'), $current_row);
} else {
$table_report['updates']++;
}
}
}
// end row loop
if ($current_page >= $pages - 1) {
$done = true;
}
// Flush the results and return the report.
$table_report['end'] = microtime(true);
$this->wpdb->flush();
return array('table_complete' => $done, 'table_report' => $table_report);
}
/**
* Adapated from interconnect/it's search/replace script.
* Modified to use WordPress wpdb functions instead of PHP's native mysql/pdo functions.
*
* @link https://interconnectit.com/products/search-and-replace-for-wordpress-databases/
*
* @access public
* @param string $table The table to run the replacement on.
* @param string $search The string to replace.
* @param string $replace The string to replace with.
* @param boolean $replace_guids Whether to skip the GUID column
* @param boolean $dry_run Whether to run as a dry run
* @param boolean $case_insensitive If we should ignore case.
* @return array
*/
public function srdb($table, $search = '', $replace = '', $replace_guids, $dry_run, $case_insensitive)
{
$table = esc_sql($table);
$table_report = array('change' => 0, 'updates' => 0, 'start' => microtime(true), 'end' => microtime(true), 'errors' => array());
// Get a list of columns in this table.
$columns = array();
$fields = $this->wpdb->get_results('DESCRIBE ' . $table);
foreach ($fields as $column) {
$columns[$column->Field] = $column->Key == 'PRI' ? true : false;
}
$this->wpdb->flush();
// Count the number of rows we have in the table if large we'll split into blocks, This is a mod from Simon Wheatley
$row_count = $this->wpdb->get_var("SELECT COUNT(*) FROM {$table}");
if ($row_count == 0) {
return $table_report;
}
// Default to a lower page size for shared hosting/large DB's.
$page_size = get_option('bsr_page_size') ? get_option('bsr_page_size') : 20000;
$pages = ceil($row_count / $page_size);
for ($page = 0; $page < $pages; $page++) {
$current_row = 0;
$start = $page * $page_size;
$end = $page_size;
// Grab the content of the table.
$data = $this->wpdb->get_results("SELECT * FROM {$table} LIMIT {$start}, {$end}", ARRAY_A);
// Loop through the data.
foreach ($data as $row) {
$current_row++;
$update_sql = array();
$where_sql = array();
$upd = false;
foreach ($columns as $column => $primary_key) {
$data_to_fix = $row[$column];
// Skip GUIDs by default.
if (true !== $replace_guids && 'guid' == $column) {
continue;
}
// Run a search replace on the data that'll respect the serialisation.
$edited_data = $this->recursive_unserialize_replace($search, $replace, $data_to_fix, false, $case_insensitive);
// Something was changed
if ($edited_data != $data_to_fix) {
$update_sql[] = $column . ' = "' . $this->mysql_escape_mimic($edited_data) . '"';
$upd = true;
$this->report['change']++;
$table_report['change']++;
}
if ($primary_key) {
$where_sql[] = $column . ' = "' . $this->mysql_escape_mimic($data_to_fix) . '"';
}
}
// Determine what to do with updates.
if ($dry_run === true) {
// Don't do anything if a dry run
} elseif ($upd && !empty($where_sql)) {
// If there are changes to make, run the query.
$sql = 'UPDATE ' . $table . ' SET ' . implode(', ', $update_sql) . ' WHERE ' . implode(' AND ', array_filter($where_sql));
$result = $this->wpdb->query($sql);
if (!$result) {
$this->report['errors']++;
$table_report['errors'][] = sprintf(__('Error updating row: %d.', 'better-search-replace'), $current_row);
} else {
$this->report['updates']++;
$table_report['updates']++;
}
} elseif ($upd) {
$this->report['errors']++;
$table_report['errors'][] = sprintf(__('Row %d has no primary key, manual change needed.', 'better-search-replace'), $current_row);
}
}
}
// Flush the results and return the report.
$table_report['end'] = microtime(true);
$this->wpdb->flush();
return $table_report;
}
