Behold my rake tasks to dump, and then reload, the contents of your database – all in highly compatible schema.rb and YAML formats. A mere rake dump_utf will create two files in /db/dump/ : firstly, an independent schema dump (doesn’t touch your proper one) and secondly a YAML file which is essentially a giant serialised hash of your DB. Running rake load_utf will import schema.rb and then all your data. And unlike every other script of this type I’ve seen around the net, it actually works, and is unicode safe.
Note that load_utf is extremely destructive and will write straight over your DB without asking further permission. However, if you haven’t run dump_utf it won’t find its files anyway, so not to worry.
Thanks to Tobias Luetke whose blog post was the starting point for this script, although there’s nothing left of it but the SQL Query now.
Needless to say, a great use of this tool is if you’re changing databases. Simply run dump_utf, modify database.yml to point to your new DB, then run load_utf – done.
Oh and I wouldn’t run it if your DB is too big, since it stores it all in memory. I may change that. And it doesn’t handle multiple databases either, I want to change that too ..
require 'Ya2YAML' task :dump_utf => :environment do sql = "SELECT * FROM %s" skip_tables = ["schema_info"] dir = RAILS_ROOT + '/db/dump' FileUtils.mkdir_p(dir) FileUtils.chdir(dir) ActiveRecord::Base.establish_connection puts "Dumping Schema..." File.open("structure.rb", "w+") do |file| ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, file) end giant_hash = {} # we're gonna put EVERYTHING in here! (ActiveRecord::Base.connection.tables - skip_tables).each do |table_name| giant_hash[table_name] = ActiveRecord::Base.connection.select_all(sql % table_name) puts "Reading #{table_name}..." end puts "Writing file..." File.open("backup.yml", 'w+') do |file| file.write giant_hash.ya2yaml end puts "Finished!" end task :load_utf => :environment do dir = RAILS_ROOT + '/db/dump/' FileUtils.chdir(dir) puts "loading schema..." file = "structure.rb" load(file) puts "done! now loading data ..." content_file = YAML.load_file(dir + "backup.yml") content_file.keys.each do |table_name| print "loading #{table_name}" content_file[table_name].each do |record| ActiveRecord::Base.connection.execute "INSERT INTO #{table_name} (#{record.keys.join(",")}) VALUES (#{record.values.collect { |value| ActiveRecord::Base.connection.quote(value) }.join(",")})", 'Insert Record' print "." end puts end puts "Finished!" end
Tags: database, dump, load, utf8