Controlling MySQL on Unix

Problem

The standard Ruby database interfaces assume you e 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 thats what you need, you can use IO.popen interactively; see Recipe 23.1.

See Also

• Recipe 23.1, "Scripting an External Program"