At times it is useful to easily be able to copy the content of the production database to the database running on your local development machine. In my case I use mysql on the production server and sqlite on my MacBook Pro. Unfortunately the use of different database servers makes this task a bit more tricky.
With a bit of googling I found a bit of code here and there which I combined and modified and turned into the following snippet of code to be dropped into your Capistrano deploy.rb file.
desc "Copy production database to development database"
task :update_dev_db, :roles => :db do
db = YAML::load(ERB.new(IO.read(File.join(File.dirname(FILE), 'database.yml'))).result)['production']
filename = “#{db[‘database’]}_dump.#{Time.now.strftime ‘%Y%m%dT%:%H%M%S’}.sql”
remote_path = “#{current_path}/tmp/#{filename}”
local_path = “tmp/#{filename}”
on_rollback { run “rm #{remote_path}” }
run “mysqldump -u #{db[‘username’]} –password=#{db[‘password’]} –skip-opt #{db[‘database’]} –complete-insert=true –skip-quote-names –no-create-info > #{remote_path}” do |ch, stream, data|
puts data
end
get remote_path, local_path
converted = File.read(local_path).gsub(/\’/, ‘\’\”)
File.open(“#{local_path}_converted”, ‘w’) {|f| f.write(converted)}
system “sqlite3 db/dev.db < #{local_path}_converted”
system “rm #{local_path}”
system “rm #{local_path}_converted”
run “rm #{remote_path}”
end
There is a small problem with \n (new line) characters that on the production server get evaluated as they should. However, on the development machine after the data has been exported from mysql and imported into the sqlite database the \n characters will show as \n on the screen. But for me this is good enough for now.