Selectively exports wordpress tables replacing required string tokens also in serialized fields.

Rakefile

# more info and usage here: 
# http://sandropaganotti.com/2012/10/28/migrating-a-single-site-of-a-wordpress-multisite-instance/

require 'yaml'
require 'ostruct'
require 'mysql2'
require 'php_serialize'

namespace :util do 
  
  def sub_array(object, old_value, new_value)
    object.map{|v| process(v,old_value,new_value) } 
  end

  def sub_hash(object, old_value, new_value)
    Hash[object.map{|(k,v)| [k,process(v,old_value, new_value)]}]
  end

  def process(element, old_value, new_value)
    case element
      when Hash   then sub_hash(element, old_value, new_value)
      when Array  then sub_array(element, old_value, new_value)
      when String then element.gsub(old_value,new_value)
      else element
    end
  end

  task :dump do
    # load config files
    domains   = (ENV['domain'] || '').split(",")
    get_yaml  = lambda{|file_name| YAML::load(File.read(File.join(File.dirname(__FILE__),file_name)))};

    config    = OpenStruct.new(get_yaml.call('config.yml')[ 'dump' ])
    database  = OpenStruct.new(config.databases['origin'])
    transito  = OpenStruct.new(config.databases['transito'])
    backup    = File.join(File.dirname(__FILE__),'backup.sql')
    replace   = File.join(File.dirname(__FILE__),'replace.sql')

    raise "No domain selected, eg: domain=#{config.domain.keys.join(',')}" if domains.empty?

    # retrieve current substituituions
    subs = config.domain.keys.inject([]) do |arr, domain| 
      arr += (sub = config.domain[domain]['substitute']).keys.map do |key|
        [sub[key]['old'],sub[key]['new']]
      end
    end

    # dump the current database to a transit instance
    `#{config.mysqldump} --user=#{database.username} #{"password=#{database.password}" if database.password} --host=#{database.host} #{database.database} > #{backup}`
    `#{config.mysql} --user=#{transito.username} #{"password=#{transito.password}" if transito.password} --host=#{transito.host} #{transito.database} < #{backup}`

    # connect to the transit database
    client = Mysql2::Client.new(
      :host => transito.host, 
      :database => transito.database, 
      :username => transito.username, 
      :password => transito.password
    )

    # loop through all the tables making the substituitions
    all_tables = []

    client.query('SHOW TABLES').each do |tables|
      all_tables << (table = tables.values.first)
      key   = (tmp = client.query("SHOW INDEX FROM #{table}").find do |key|  
        key['Key_name'] == 'PRIMARY'
      end) ? tmp['Column_name'] : nil
      
      next if !key

      client.query("SELECT * FROM #{table}").each do |record|
        record.keys.each do |field_name|
          
          next unless record[field_name].is_a? String

          object = PHP.unserialize(record[field_name]) rescue next

          next if !( object.is_a?(Array) || object.is_a?(Hash) )
          next if object.empty? || (object.size == 1 && object.first == "")

          subs.each do |(old_value,new_value)|
            object = process(object, old_value, new_value)
          end

          object = PHP.serialize(object).force_encoding('UTF-8')

          if(object != record[field_name])
            puts "#{table}, #{key}[#{record[key]}]: #{field_name}"
            client.query "UPDATE #{table} SET #{field_name}='#{client.escape(object)}' WHERE #{key}=#{record[key]}"
          end

        end
      end
    end

    # define tables to ignore
    desired_tables = domains.inject([]) do |arr,domain|
      arr += config.domain[domain]['tables']
    end

    ignored_tables = all_tables.select do |table| 
      !desired_tables.include?(table) 
    end.map do |table|
      "--ignore-table=#{transito.database}.#{table}"
    end.join(" ")

    # instruct mysqldump to export only desired tables
    `#{config.mysqldump} #{ignored_tables} --user=#{transito.username} #{"password=#{transito.password}" if transito.password} --host=#{transito.host} #{transito.database} > #{replace}`

    # replace non-serialized string from the dump
    content = File.read(replace)
    subs.each do |(old_value,new_value)|
      content = content.gsub(old_value,new_value)
    end
    File.open(replace, 'w') { |file| file.write(content) }

    # done!, now replace contains the SQL

  end
  
end