Skip to content

Instantly share code, notes, and snippets.

@sandropaganotti-zz
Created October 28, 2012 14:49
Show Gist options
  • Save sandropaganotti-zz/3968799 to your computer and use it in GitHub Desktop.
Save sandropaganotti-zz/3968799 to your computer and use it in GitHub Desktop.
Selectively exports wordpress tables replacing required string tokens also in serialized fields.
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment