Skip to content

Instantly share code, notes, and snippets.

@clayrichardson
Created February 1, 2014 04:11
Show Gist options
  • Save clayrichardson/8747824 to your computer and use it in GitHub Desktop.
Save clayrichardson/8747824 to your computer and use it in GitHub Desktop.
Script to migrate phabricator database prefixes
require 'ostruct'
require 'optparse'
require 'awesome_print'
$stdout.sync = true
options = {}
OptionParser.new do |opts|
opts.banner = "Usage: change_phabricator_prefix.rb [options]"
opts.on("-h", "--help", "Show this message") do
puts opts
exit
end
opts.on("-m", "--host HOST", "MySQL host to connect to") do |option|
options[:host] = option;
end
opts.on("-u", "--user USER", "MySQL user to connect as") do |option|
options[:user] = option;
end
opts.on("-p", "--password PASSWORD", "MySQL password to authenticate with") do |option|
options[:password] = option;
end
opts.on("-o", "--old_prefix PREFIX", "Old database prefix") do |option|
options[:old_prefix] = option;
end
opts.on("-n", "--new_prefix PREFIX", "New database prefix") do |option|
options[:new_prefix] = option;
end
end.parse!
required_options = [
:host,
:user,
:old_prefix,
:new_prefix
]
required_options.each do |required_option|
if not options[required_option]
raise OptionParser::MissingArgument, "You are missing #{option} argument, see help for more information."
end
end
def mysql_query(opts = {})
options = opts[:options]
command = opts[:command]
verbose = opts[:verbose] ? '-vvv' : nil
host = options[:host]
user = options[:user]
password = options[:password]
mysql_command = "mysql #{verbose} -h #{host} -u #{user} -p#{password}"
command = "#{mysql_command} -N -e '#{command}'"
puts "running mysql command: #{command}"
query_results = `#{command}`.split("\n")
puts "result was: #{query_results}"
return query_results
end
def get_databases(opts = {})
options = opts[:options]
old_prefix = options[:old_prefix]
databases_command = %Q{
select table_schema, table_name
from information_schema.tables
where table_schema like \"#{old_prefix}%\";
}
databases = {}
results = mysql_query(:command => databases_command, :options => options)
results.each do |row|
row = row.split(' ')
if databases.has_key?(row[0])
databases[row[0]][:tables].push(row[1])
else
databases[row[0]] = {
:name => row[0],
:tables => [],
}
databases[row[0]][:tables].push(row[1])
end
end
return databases
end
def rename_databases(opts = {})
options = opts[:options]
databases = opts[:databases]
old_prefix = options[:old_prefix]
new_prefix = options[:new_prefix]
databases.each do |name, database|
new_db_name = database[:name].gsub(/^#{old_prefix}/, new_prefix)
if new_db_name.length > 64
raise "Oh fuck, #{new_db_name} is too long"
end
mysql_query(
:command => "create database if not exists `#{new_db_name}` DEFAULT CHARACTER SET utf8;",
:options => options
)
end
databases.each do |name, database|
puts "#{database[:name]}: #{database[:tables].count} tables"
new_db_name = database[:name].gsub(/^#{old_prefix}/, new_prefix)
if new_db_name.length > 64
raise "Oh fuck, #{new_db_name} is too long"
end
database[:tables].each do |table|
puts "moving #{database[:name]}.#{table} to #{new_db_name}.#{table}"
rename_query = "RENAME TABLE `#{database[:name]}`.`#{table}` to `#{new_db_name}`.`#{table}`;"
puts "rename_query: #{rename_query}"
mysql_query(
:command => rename_query,
:verbose => true,
:options => options
)
end
puts 'checking old database tables.'
table_query = "select count(*) from information_schema.tables where table_schema = \"#{database[:name]}\";"
table_results = mysql_query(:command => table_query, :options => options)
if 0 == table_results[0].to_i
puts "no more tables exist in database #{database[:name]}, dropping."
drop_query = "drop database `#{database[:name]}`;"
mysql_query(
:command => drop_query,
:verbose => true,
:options => options
)
else
puts "for some reason, there are still tables in database: #{database[:name]}"
end
end
end
databases = get_databases(:options => options)
awesome_print(databases)
rename_databases(
:databases => databases,
:options => options,
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment