cap db:pull

gistfile1.rb

namespace :db do
  desc "Download the a full dump of the production database into the development database"
  task :pull do
    config = YAML::load(File.open(File.join('config', 'database.yml')))

    dump_database_remotely(config['production'], remote_file)

    compress_backup_remotely(remote_file)

    download_backup(remote_file(:compressed), local_file(:compressed))

    inflate_backup(local_file(:compressed))

    import_database(config['development'], local_file)
    
    cleanup_remotely(remote_file(:compressed))

    cleanup_locally(local_file(:uncompressed), local_file(:compressed))
  end
  
  def remote_file(state = :uncompressed)
    File.join("#{current_path}", 'tmp', filename(state))
  end
  
  def local_file(state = :uncompressed)
    "tmp/" + filename(state)
  end
  
  def filename(state)
    state == :compressed ? 'db-backup.sql.gz' : 'db-backup.sql'
  end

  def dump_database_remotely(config, file)
    run "mysqldump #{mysql_connection_string(config)} > #{file}"
  end
  
  def compress_backup_remotely(file)
    run "gzip -f #{file}"
  end
  
  def download_backup(remote_file, local_file)
    download remote_file, local_file
  end
  
  def inflate_backup(file)
    logger.debug("Inflating #{file}")
    system "gunzip -f #{file}"
  end
  
  def import_database(config, file)
    command = "mysql #{mysql_connection_string(config)} < #{file}"
    logger.debug("Importing #{file} with `#{command}`")

    system command
  end
  
  def cleanup_remotely(*files)
    files.each { |file| run "rm #{file}" }
  end
  
  def cleanup_locally(*files)
    files.each do |file|
      system "rm -f #{file}"
      logger.debug("Deleted #{file}")
    end
  end
  
  def mysql_connection_string(config)
    string = " #{config['database']}"
    string << " -h#{config['host']}" if config['host']
    string << " -u#{config['username']}" if config['username']
    string << " -p#{config['password']}" if config['password']

    string
  end
end

Current thoughts:

• Am I re-inventing the wheel with the mysql dump stuff? Especially constructing the connection string
• Passing in the :compressed symbol seems quite ugly
• Be nice to get this into objects - maybe RemoteDbBackup and LocalDbBackup, inheriting from DbBackup?

... and more to come, no doubt!

Useful idea.

Saves the user connecting to the live production db, which is we all know always end in utter failure at some point - the ideal would be a read only user, but often account/budget restrictions mean that can't always happen.

Can't think what options would be useful at the mo on mysqldump, but having that configurable might be useful also.

Ian