set :gateway, "your.server.com"
set :web, "flute"
set :mysql_host, "oboe"
set :mysql, "mysql -uroot -prootpassword"
role :xen0, gateway
role :nsp, gateway

role :app, web
role :db, mysql_host

set(:dbpasswd) do
  Capistrano::CLI.ui.ask "Enter database password :"
end

set(:dbname) do
  Capistrano::CLI.ui.ask "Enter application name:"
end

desc "list databases on host"
task :list_databases, :roles => :db do
  run "echo \"SHOW DATABASES;\" | #{mysql}"
end

desc "list database users"
task :list_users, :roles => :db do
  run "echo \"USE mysql; SELECT User FROM user;\" | #{mysql}"
end

desc "create a database user and their database"
task :create_database, :roles => :db do
  run "echo \"CREATE DATABASE #{dbname};\
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER \
    ON #{dbname}.*\
    TO #{dbname}@'%'\
    IDENTIFIED BY '#{dbpasswd}';\" > file.sql"

  run "#{mysql} < file.sql"
end

desc "drop a database and remove the user"
task :drop_database, :roles => :db do
  run "echo \"DROP DATABASE #{dbname};\
        use mysql;\
        DELETE FROM user WHERE User = '#{dbname}' LIMIT 1;\" > file.sql"

  run "#{mysql} < file.sql"
end