Recipe 13.7. Controlling MySQL on Unix
Problem
The standard Ruby database interfaces assume you're connecting to a preexisting database, and that you already have access to this database. You want to create and administer
MySQL databases from within Ruby.
Solution
Sam Ruby came up with an elegant solution to this problem. The mysql method defined below opens up a pipe to a MySQL client program and sends SQL input to it:
def mysql(opts, stream)
IO.popen("mysql #{opts}", 'w') { |io| io.puts stream }
end
You can use this technique to create, delete, and administer MySQL databases:
mysql '-u root -p[password]', <<-end
drop database if exists website_db;
create database website_db;
grant all on website_db.* to #{`id -un`.strip}@localhost;
end
Discussion
This solution looks so elegant because of the <<-end declaration, which allows you to end the string the same way you end a code block.
One shortcoming of this solution is that the IO.popen call opens up a one-way communication with the MySQL client. This makes it difficult to call SQL commands and get the results back. If that's what you need, you can use IO.popen interactively; see Recipe 23.1.
See Also
| 
