/** * Taken partially from phpMyAdmin and partially from * Alain Wolf, Zurich - Switzerland * Website: http://restkultur.ch/personal/wolf/scripts/db_backup/ * Modified by Scott Merrill (http://www.skippy.net/) * to use the WordPress $wpdb object * * @param string $table * @param string $db_version * * @return mixed */ function export_table($table, $db_version = '') { global $wpdb; $this->set_time_limit(); $this->set_post_data(); if (empty($this->form_data)) { $this->form_data = $this->parse_migration_form_data($this->state_data['form_data']); } $temp_prefix = isset($this->state_data['temp_prefix']) ? $this->state_data['temp_prefix'] : $this->temp_prefix; $table_structure = $wpdb->get_results('DESCRIBE ' . $this->backquote($table)); if (!$table_structure) { $this->error = __('Failed to retrieve table structure, please ensure your database is online. (#125)', 'wp-migrate-db'); return false; } $table_name = $table; $target_table_name = apply_filters('wpmdb_target_table_name', $table_name, $this->form_data['action'], $this->state_data['stage']); $table_name = $target_table_name; if ('savefile' !== $this->form_data['action'] && 'backup' !== $this->state_data['stage']) { $table_name = $temp_prefix . $table; } $current_row = -1; if (!empty($this->state_data['current_row'])) { $temp_current_row = trim($this->state_data['current_row']); if (!empty($temp_current_row)) { $current_row = (int) $temp_current_row; } } if ($current_row == -1) { // Don't stow data until after `wpmdb_create_table_query` filter is applied as mysql_compat_filter() can return an error $stow = ''; // Add SQL statement to drop existing table if ($this->form_data['action'] == 'savefile' || $this->state_data['stage'] == 'backup') { $stow .= "\n\n"; $stow .= "#\n"; $stow .= '# ' . sprintf(__('Delete any existing table %s', 'wp-migrate-db'), $this->backquote($table_name)) . "\n"; $stow .= "#\n"; $stow .= "\n"; } $stow .= 'DROP TABLE IF EXISTS ' . $this->backquote($table_name) . ";\n"; // Table structure // Comment in SQL-file if ($this->form_data['action'] == 'savefile' || $this->state_data['stage'] == 'backup') { $stow .= "\n\n"; $stow .= "#\n"; $stow .= '# ' . sprintf(__('Table structure of table %s', 'wp-migrate-db'), $this->backquote($table_name)) . "\n"; $stow .= "#\n"; $stow .= "\n"; } $create_table = $wpdb->get_results('SHOW CREATE TABLE ' . $this->backquote($table), ARRAY_N); if (false === $create_table) { $this->error = __('Failed to generate the create table query, please ensure your database is online. (#126)', 'wp-migrate-db'); return false; } $create_table[0][1] = str_replace('CREATE TABLE `' . $table . '`', 'CREATE TABLE `' . $table_name . '`', $create_table[0][1]); $create_table[0][1] = str_replace('TYPE=', 'ENGINE=', $create_table[0][1]); $alter_table_query = ''; $create_table[0][1] = $this->process_sql_constraint($create_table[0][1], $target_table_name, $alter_table_query); $create_table[0][1] = apply_filters('wpmdb_create_table_query', $create_table[0][1], $table_name, $db_version, $this->form_data['action'], $this->state_data['stage']); $stow .= $create_table[0][1] . ";\n"; $this->stow($stow); if (!empty($alter_table_query)) { $alter_table_name = $this->get_alter_table_name(); $insert = sprintf("INSERT INTO %s ( `query` ) VALUES ( '%s' );\n", $this->backquote($alter_table_name), esc_sql($alter_table_query)); if ($this->form_data['action'] == 'savefile' || $this->state_data['stage'] == 'backup') { $process_chunk_result = $this->process_chunk($insert); if (true !== $process_chunk_result) { $result = $this->end_ajax($process_chunk_result); return $result; } } else { $this->stow($insert); } } $alter_data_queries = array(); $alter_data_queries = apply_filters('wpmdb_alter_data_queries', $alter_data_queries, $table_name, $this->form_data['action'], $this->state_data['stage']); if (!empty($alter_data_queries)) { $alter_table_name = $this->get_alter_table_name(); $insert = ''; foreach ($alter_data_queries as $alter_data_query) { $insert .= sprintf("INSERT INTO %s ( `query` ) VALUES ( '%s' );\n", $this->backquote($alter_table_name), esc_sql($alter_data_query)); } if ('savefile' == $this->form_data['action'] || 'backup' == $this->state_data['stage']) { $process_chunk_result = $this->process_chunk($insert); if (true !== $process_chunk_result) { $result = $this->end_ajax($process_chunk_result); return $result; } } else { $this->stow($insert); } } // Comment in SQL-file if ($this->form_data['action'] == 'savefile' || $this->state_data['stage'] == 'backup') { $this->stow("\n\n"); $this->stow("#\n"); $this->stow('# ' . sprintf(__('Data contents of table %s', 'wp-migrate-db'), $this->backquote($table_name)) . "\n"); $this->stow("#\n"); } } // $defs = mysql defaults, looks up the default for that particular column, used later on to prevent empty inserts values for that column // $ints = holds a list of the possible integer types so as to not wrap them in quotation marks later in the insert statements $defs = array(); $ints = array(); $bins = array(); $bits = array(); foreach ($table_structure as $struct) { if (0 === strpos($struct->Type, 'tinyint') || 0 === strpos(strtolower($struct->Type), 'smallint') || 0 === strpos(strtolower($struct->Type), 'mediumint') || 0 === strpos(strtolower($struct->Type), 'int') || 0 === strpos(strtolower($struct->Type), 'bigint')) { $defs[strtolower($struct->Field)] = null === $struct->Default ? 'NULL' : $struct->Default; $ints[strtolower($struct->Field)] = '1'; } elseif (0 === strpos($struct->Type, 'binary')) { $bins[strtolower($struct->Field)] = '1'; } elseif (0 === strpos($struct->Type, 'bit')) { $bits[strtolower($struct->Field)] = '1'; } } // Batch by $row_inc $row_inc = $this->rows_per_segment; $row_start = 0; if ($current_row != -1) { $row_start = $current_row; } $this->row_tracker = $row_start; // \x08\\x09, not required $multibyte_search = array("", "\n", "\r", ""); $multibyte_replace = array('\\0', '\\n', '\\r', '\\Z'); $query_size = 0; $this->primary_keys = array(); $field_set = array(); $use_primary_keys = true; foreach ($table_structure as $col) { $field_set[] = $this->backquote($col->Field); if ($col->Key == 'PRI' && true == $use_primary_keys) { if (false === strpos($col->Type, 'int')) { $use_primary_keys = false; $this->primary_keys = array(); continue; } $this->primary_keys[$col->Field] = 0; } } $first_select = true; if (!empty($this->state_data['primary_keys'])) { $this->state_data['primary_keys'] = trim($this->state_data['primary_keys']); if (!empty($this->state_data['primary_keys']) && is_serialized($this->state_data['primary_keys'])) { $this->primary_keys = unserialize(stripslashes($this->state_data['primary_keys'])); $first_select = false; } } $fields = implode(', ', $field_set); $insert_buffer = $insert_query_template = 'INSERT INTO ' . $this->backquote($table_name) . ' ( ' . $fields . ") VALUES\n"; do { $join = array(); $where = 'WHERE 1=1'; $order_by = ''; // We need ORDER BY here because with LIMIT, sometimes it will return // the same results from the previous query and we'll have duplicate insert statements if ('backup' != $this->state_data['stage'] && false === empty($this->form_data['exclude_spam'])) { if ($this->table_is('comments', $table)) { $where .= ' AND comment_approved != "spam"'; } elseif ($this->table_is('commentmeta', $table)) { $tables = $this->get_ms_compat_table_names(array('commentmeta', 'comments'), $table); $join[] = sprintf('INNER JOIN %1$s ON %1$s.comment_ID = %2$s.comment_id', $this->backquote($tables['comments_table']), $this->backquote($tables['commentmeta_table'])); $where .= sprintf(' AND %1$s.comment_approved != \'spam\'', $this->backquote($tables['comments_table'])); } } if ('backup' != $this->state_data['stage'] && isset($this->form_data['exclude_post_types']) && !empty($this->form_data['select_post_types'])) { $post_types = '\'' . implode('\', \'', $this->form_data['select_post_types']) . '\''; if ($this->table_is('posts', $table)) { $where .= ' AND `post_type` NOT IN ( ' . $post_types . ' )'; } elseif ($this->table_is('postmeta', $table)) { $tables = $this->get_ms_compat_table_names(array('postmeta', 'posts'), $table); $join[] = sprintf('INNER JOIN %1$s ON %1$s.ID = %2$s.post_id', $this->backquote($tables['posts_table']), $this->backquote($tables['postmeta_table'])); $where .= sprintf(' AND %1$s.post_type NOT IN ( ' . $post_types . ' )', $this->backquote($tables['posts_table'])); } elseif ($this->table_is('comments', $table)) { $tables = $this->get_ms_compat_table_names(array('comments', 'posts'), $table); $join[] = sprintf('INNER JOIN %1$s ON %1$s.ID = %2$s.comment_post_ID', $this->backquote($tables['posts_table']), $this->backquote($tables['comments_table'])); $where .= sprintf(' AND %1$s.post_type NOT IN ( ' . $post_types . ' )', $this->backquote($tables['posts_table'])); } elseif ($this->table_is('commentmeta', $table)) { $tables = $this->get_ms_compat_table_names(array('commentmeta', 'posts', 'comments'), $table); $join[] = sprintf('INNER JOIN %1$s ON %1$s.comment_ID = %2$s.comment_id', $this->backquote($tables['comments_table']), $this->backquote($tables['commentmeta_table'])); $join[] = sprintf('INNER JOIN %2$s ON %2$s.ID = %1$s.comment_post_ID', $this->backquote($tables['comments_table']), $this->backquote($tables['posts_table'])); $where .= sprintf(' AND %1$s.post_type NOT IN ( ' . $post_types . ' )', $this->backquote($tables['posts_table'])); } } if ('backup' != $this->state_data['stage'] && true === apply_filters('wpmdb_exclude_transients', true) && isset($this->form_data['exclude_transients']) && '1' === $this->form_data['exclude_transients'] && ($this->table_is('options', $table) || isset($wpdb->sitemeta) && $wpdb->sitemeta == $table)) { $col_name = 'option_name'; if (isset($wpdb->sitemeta) && $wpdb->sitemeta == $table) { $col_name = 'meta_key'; } $where .= " AND `{$col_name}` NOT LIKE '\\_transient\\_%' AND `{$col_name}` NOT LIKE '\\_site\\_transient\\_%'"; } // don't export/migrate wpmdb specific option rows unless we're performing a backup if ('backup' != $this->state_data['stage'] && ($this->table_is('options', $table) || isset($wpdb->sitemeta) && $wpdb->sitemeta == $table)) { $col_name = 'option_name'; if (isset($wpdb->sitemeta) && $wpdb->sitemeta == $table) { $col_name = 'meta_key'; } $where .= " AND `{$col_name}` != 'wpmdb_settings'"; $where .= " AND `{$col_name}` != 'wpmdb_error_log'"; $where .= " AND `{$col_name}` != 'wpmdb_schema_version'"; $where .= " AND `{$col_name}` NOT LIKE 'wpmdb_state_%'"; } $limit = "LIMIT {$row_start}, {$row_inc}"; if (!empty($this->primary_keys)) { $primary_keys_keys = array_keys($this->primary_keys); $primary_keys_keys = array_map(array($this, 'backquote'), $primary_keys_keys); $order_by = 'ORDER BY ' . implode(',', $primary_keys_keys); $limit = "LIMIT {$row_inc}"; if (false == $first_select) { $where .= ' AND '; $temp_primary_keys = $this->primary_keys; $primary_key_count = count($temp_primary_keys); // build a list of clauses, iteratively reducing the number of fields compared in the compound key // e.g. (a = 1 AND b = 2 AND c > 3) OR (a = 1 AND b > 2) OR (a > 1) $clauses = array(); for ($j = 0; $j < $primary_key_count; $j++) { // build a subclause for each field in the compound index $subclauses = array(); $i = 0; foreach ($temp_primary_keys as $primary_key => $value) { // only the last field in the key should be different in this subclause $operator = count($temp_primary_keys) - 1 == $i ? '>' : '='; $subclauses[] = sprintf('%s %s %s', $this->backquote($primary_key), $operator, $wpdb->prepare('%s', $value)); ++$i; } // remove last field from array to reduce fields in next clause array_pop($temp_primary_keys); // join subclauses into a single clause // NB: AND needs to be wrapped in () as it has higher precedence than OR $clauses[] = '( ' . implode(' AND ', $subclauses) . ' )'; } // join clauses into a single clause // NB: OR needs to be wrapped in () as it has lower precedence than AND $where .= '( ' . implode(' OR ', $clauses) . ' )'; } $first_select = false; } $sel = $this->backquote($table) . '.*'; if (!empty($bins)) { foreach ($bins as $key => $bin) { $hex_key = strtolower($key) . '__hex'; $sel .= ', HEX(' . $this->backquote($key) . ') as ' . $this->backquote($hex_key); } } if (!empty($bits)) { foreach ($bits as $key => $bit) { $bit_key = strtolower($key) . '__bit'; $sel .= ', ' . $this->backquote($key) . '+0 as ' . $this->backquote($bit_key); } } $join = implode(' ', array_unique($join)); $join = apply_filters('wpmdb_rows_join', $join, $table); $where = apply_filters('wpmdb_rows_where', $where, $table); $order_by = apply_filters('wpmdb_rows_order_by', $order_by, $table); $limit = apply_filters('wpmdb_rows_limit', $limit, $table); $sql = 'SELECT ' . $sel . ' FROM ' . $this->backquote($table) . " {$join} {$where} {$order_by} {$limit}"; $sql = apply_filters('wpmdb_rows_sql', $sql, $table); $table_data = $wpdb->get_results($sql); if ($table_data) { $to_search = isset($this->find_replace_pairs['replace_old']) ? $this->find_replace_pairs['replace_old'] : ''; $to_replace = isset($this->find_replace_pairs['replace_new']) ? $this->find_replace_pairs['replace_new'] : ''; $replacer = new WPMDB_Replace(array('table' => $table, 'search' => $to_search, 'replace' => $to_replace, 'intent' => $this->state_data['intent'], 'base_domain' => $this->get_domain_replace(), 'site_domain' => $this->get_domain_current_site(), 'wpmdb' => $this)); foreach ($table_data as $row) { $skip_row = false; if (!apply_filters('wpmdb_table_row', $row, $table, $this->form_data['action'], $this->state_data['stage'])) { $skip_row = true; } if (!$skip_row) { $replacer->set_row($row); $values = array(); foreach ($row as $key => $value) { $replacer->set_column($key); if (isset($ints[strtolower($key)]) && $ints[strtolower($key)]) { // make sure there are no blank spots in the insert syntax, // yet try to avoid quotation marks around integers $value = null === $value || '' === $value ? $defs[strtolower($key)] : $value; $values[] = '' === $value ? "''" : $value; continue; } if (null === $value) { $values[] = 'NULL'; continue; } // If we have binary data, substitute in hex encoded version and remove hex encoded version from row. $hex_key = strtolower($key) . '__hex'; if (isset($bins[strtolower($key)]) && $bins[strtolower($key)] && isset($row->{$hex_key})) { $value = "UNHEX('" . $row->{$hex_key} . "')"; $values[] = $value; unset($row->{$hex_key}); continue; } // If we have bit data, substitute in properly bit encoded version. $bit_key = strtolower($key) . '__bit'; if (isset($bits[strtolower($key)]) && $bits[strtolower($key)] && isset($row->{$bit_key})) { $value = "b'" . $row->{$bit_key} . "'"; $values[] = $value; unset($row->{$bit_key}); continue; } if (is_multisite() && 'path' == $key && $this->state_data['stage'] != 'backup' && ($wpdb->site == $table || $wpdb->blogs == $table)) { $old_path_current_site = $this->get_path_current_site(); $new_path_current_site = ''; if (!empty($this->state_data['path_current_site'])) { $new_path_current_site = $this->state_data['path_current_site']; } elseif (!empty($this->form_data['replace_new'][1])) { $new_path_current_site = $this->get_path_from_url($this->form_data['replace_new'][1]); } $new_path_current_site = apply_filters('wpmdb_new_path_current_site', $new_path_current_site); if (!empty($new_path_current_site) && $old_path_current_site != $new_path_current_site) { $pos = strpos($value, $old_path_current_site); $value = substr_replace($value, $new_path_current_site, $pos, strlen($old_path_current_site)); } } if (is_multisite() && 'domain' == $key && $this->state_data['stage'] != 'backup' && ($wpdb->site == $table || $wpdb->blogs == $table)) { if (!empty($this->state_data['domain_current_site'])) { $main_domain_replace = $this->state_data['domain_current_site']; } elseif (!empty($this->form_data['replace_new'][1])) { $url = $this->parse_url($this->form_data['replace_new'][1]); $main_domain_replace = $url['host']; } $domain_replaces = array(); $main_domain_find = sprintf('/%s/', preg_quote($this->get_domain_current_site(), '/')); if (isset($main_domain_replace)) { $domain_replaces[$main_domain_find] = $main_domain_replace; } $domain_replaces = apply_filters('wpmdb_domain_replaces', $domain_replaces); $value = preg_replace(array_keys($domain_replaces), array_values($domain_replaces), $value); } if ('guid' != $key || false === empty($this->form_data['replace_guids']) && $this->table_is('posts', $table)) { if ($this->state_data['stage'] != 'backup') { $value = $replacer->recursive_unserialize_replace($value); } } $value = $this->sql_addslashes($value); $value = str_replace($multibyte_search, $multibyte_replace, $value); $values[] = "'" . $value . "'"; } $insert_line = '(' . implode(', ', $values) . '),'; $insert_line .= "\n"; } else { $insert_line = ''; } if (strlen($this->current_chunk) + strlen($insert_line) + strlen($insert_buffer) + 30 > $this->maximum_chunk_size) { if ($insert_buffer == $insert_query_template) { $insert_buffer .= $insert_line; ++$this->row_tracker; if (!empty($this->primary_keys)) { foreach ($this->primary_keys as $primary_key => $value) { $this->primary_keys[$primary_key] = $row->{$primary_key}; } } } $insert_buffer = rtrim($insert_buffer, "\n,"); $insert_buffer .= " ;\n"; $this->stow($insert_buffer); $insert_buffer = $insert_query_template; $query_size = 0; return $this->transfer_chunk(); } if ($query_size + strlen($insert_line) > $this->max_insert_string_len && $insert_buffer != $insert_query_template) { $insert_buffer = rtrim($insert_buffer, "\n,"); $insert_buffer .= " ;\n"; $this->stow($insert_buffer); $insert_buffer = $insert_query_template; $query_size = 0; } $insert_buffer .= $insert_line; $query_size += strlen($insert_line); ++$this->row_tracker; if (!empty($this->primary_keys)) { foreach ($this->primary_keys as $primary_key => $value) { $this->primary_keys[$primary_key] = $row->{$primary_key}; } } } $row_start += $row_inc; if ($insert_buffer != $insert_query_template) { $insert_buffer = rtrim($insert_buffer, "\n,"); $insert_buffer .= " ;\n"; $this->stow($insert_buffer); $insert_buffer = $insert_query_template; $query_size = 0; } } } while (count($table_data) > 0); // Create footer/closing comment in SQL-file if ('savefile' == $this->form_data['action'] || 'backup' == $this->state_data['stage']) { $this->stow("\n"); $this->stow("#\n"); $this->stow('# ' . sprintf(__('End of data contents of table %s', 'wp-migrate-db'), $this->backquote($table_name)) . "\n"); $this->stow("# --------------------------------------------------------\n"); $this->stow("\n"); if ($this->state_data['last_table'] == '1') { $this->stow("#\n"); $this->stow("# Add constraints back in and apply any alter data queries.\n"); $this->stow("#\n\n"); $this->stow($this->get_alter_queries()); $alter_table_name = $this->get_alter_table_name(); $wpdb->query('DROP TABLE IF EXISTS ' . $this->backquote($alter_table_name) . ';'); if ('backup' == $this->state_data['stage']) { // Re-create our table to store 'ALTER' queries so we don't get duplicates. $create_alter_table_query = $this->get_create_alter_table_query(); $process_chunk_result = $this->process_chunk($create_alter_table_query); if (true !== $process_chunk_result) { $result = $this->end_ajax($process_chunk_result); return $result; } } } } $this->row_tracker = -1; return $this->transfer_chunk(); }
/** * This walks every table in the db that was selected and then * walks every row and column replacing all occurences of a string with another. * * @param string $search What we want to replace * @param string $replace What we want to replace it with. * @param array $tables The tables we want to look at. * * @return array Collection of information gathered during the run. */ public function replacer($search = '', $replace = '', $tables = array()) { global $wpdb; $time = microtime(); $report = array('tables' => 0, 'rows' => 0, 'change' => 0, 'updates' => 0, 'start' => $time, 'end' => $time, 'errors' => array(), 'table_reports' => array()); $table_report = array('rows' => 0, 'change' => 0, 'changes' => array(), 'updates' => 0, 'start' => $time, 'end' => $time, 'errors' => array()); //$tables = array('wp_posts'); /* @TODO Need to get table name from form*/ // if no tables selected assume all if (empty($tables)) { $all_tables = $this->get_tables(); $tables = $all_tables; } if (is_array($tables) && !empty($tables)) { foreach ($tables as $key => $table) { $encoding = $this->get_table_character_set($table); switch ($encoding) { // Tables encoded with this work for me only when I set names to utf8. I don't trust this in the wild so I'm going to avoid. case 'utf16': case 'utf32': $encoding = 'utf8'; $this->log_error("The table \"{$table}\" is encoded using \"{$encoding}\" which is currently unsupported."); continue; break; default: // @TODO need to handle this break; } $report['tables']++; // get primary key and columns list($primary_key, $columns) = $this->get_columns($table); if ($primary_key === null) { $this->log_error("The table \"{$table}\" has no primary key. Changes will have to be made manually."); continue; } // create new table report instance $new_table_report = $table_report; $new_table_report['start'] = microtime(); // 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->getRowCount($table); $page_size = $this->page_size; $pages = ceil($row_count / $page_size); for ($page = 0; $page < $pages; $page++) { $start = $page * $page_size; // Grab the content of the table $data = $wpdb->get_results(sprintf('SELECT * FROM `%s` LIMIT %d, %d', $table, $start, $page_size), ARRAY_A); if (!$data) { $this->log_error('No record found'); } foreach ($data as $row) { $report['rows']++; // Increment the row counter $new_table_report['rows']++; $update_sql = array(); $where_sql = array(); $update = false; foreach ($columns as $column) { $edited_data = $data_to_fix = $row[$column]; if ($primary_key == $column) { $where_sql[] = "`{$column}` = " . $data_to_fix; continue; } // Run a search replace on the data that'll respect the serialisation. $args = array('table' => $table, 'search' => $search, 'replace' => $replace, 'intent' => '', 'base_domain' => '', 'site_domain' => '', 'wpmdb' => $this); $wpmdb = new WPMDB_Replace($args); //get WPMDB_Replace class object $edited_data = $wpmdb->recursive_unserialize_replace($data_to_fix); // Something was changed if ($edited_data != $data_to_fix) { $report['change']++; $new_table_report['change']++; if ($new_table_report['change'] <= $this->report_change_num) { $new_table_report['changes'][] = array('row' => $new_table_report['rows'], 'column' => $column, 'from' => utf8_encode($data_to_fix), 'to' => utf8_encode($edited_data)); } $update_sql[] = "`{$column}` = " . "'" . $edited_data . "'"; $update = true; } } if ($update && !empty($where_sql)) { $sql = 'UPDATE ' . $table . ' SET ' . implode(', ', $update_sql) . ' WHERE ' . implode(' AND ', array_filter($where_sql)); $result = $this->db_update($sql); if (!is_int($result) && !$result) { $this->log_error('No record found'); } else { $report['updates']++; $new_table_report['updates']++; } } } $wpdb->flush(); } $new_table_report['end'] = microtime(); // store table report in main $report['table_reports'][$table] = $new_table_report; // log result $this->log_error('search_replace_table_end for table: ' . $table . " Table Report :" . $new_table_report); } } $report['end'] = microtime(); return $report; }