Script to migrate phabricator database prefixes

change_phabricator_prefix.rb

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,
)