CSV export with Sequel and FasterCSV

.gitignore

*.sql
*.sh
*.swp

data.rb

require 'sequel'
require 'csv'
require 'trollop'

opts = Trollop::options(ARGV) do
  opt :tables, "Comma-separated tables to copy", :type => :string
  opt :skip, "Comma-separated tables to skip", :type => :string
end

connection = Sequel.connect(ARGV.shift)
connection.convert_invalid_date_time = nil

opts[:tables] = opts[:tables] ? opts[:tables].split(',').map(&:to_sym) : connection.tables
opts[:skip] = opts[:skip].to_s.split(',').map(&:to_sym)

variables = connection["SHOW VARIABLES LIKE 'character\\_set\\_%'"].all
variables.select! { |hash| hash[:Value] !~ /^(utf8|binary)$/ }
raise "Some variables are not UTF-8: #{variables.inspect}" unless variables.empty?

opts[:tables].reject! { |table| opts[:skip].include? table }
opts[:tables].each do |table|
  STDERR.puts table

  puts <<-SQL
TRUNCATE TABLE #{table};
COPY #{table} FROM STDIN (FORMAT csv, DELIMITER ',', HEADER);
  SQL

  CSV do |csv|
    csv << (columns = connection[table].columns)
    connection[table].each do |row|
      csv << columns.map { |column| row[column] }.map { |value| value.respond_to?(:gsub) ? value.gsub("\0", "") : value }
    end
  end
  
  puts "\\."
end

expected.rb

require 'sequel'

connection = Sequel.connect(ARGV.shift)
connection.tables.each do |table|
  puts "SELECT '#{table}' AS table, #{connection[table].count} AS mysql, COUNT(*) AS postgres FROM #{table};"
end

sequences.rb

require 'sequel'

# http://cmar.me/2011/03/03/mysql-to-postgres-id-column-sequences/

connection = Sequel.connect(ARGV.shift)
connection.tables.each do |table|
  connection["DESCRIBE #{table}"].all.each do |description|
    if description[:Extra] =~ /auto_increment/
      puts "CREATE SEQUENCE #{table}_#{description[:Field]}_seq;"
      puts "ALTER TABLE #{table} ALTER COLUMN #{description[:Field]} SET DEFAULT nextval('#{table}_#{description[:Field]}_seq');"

      maximum = connection["SELECT MAX(#{description[:Field]}) AS max FROM #{table}"].first[:max]
      puts "SELECT setval('#{table}_#{description[:Field]}_seq', #{maximum + 1});" if maximum
    end
  end
end