Database Reference
In-Depth Information
Ruby API
The Ruby languagehas become very popular and can be used to create programs to access
a database. There are two MySQL modules for Ruby. The MySQL/Ruby module is built on
the MySQL C API. As such, it has the same functions in Ruby as the C API. This is a nice
feature if you already know the C API. The other module is the Ruby/MySQL module —
this pairing and reverse pairing of the names can be confusing. The Ruby/MySQL module
is written in Ruby and is included in Ruby on Rails. For the examples in this section, we
will use the former, the MySQL/Ruby module.
Installing and Preparing MySQL/Ruby
Before writing aRuby program to interface with MySQL, let's install the MySQL/Ruby
module, which uses the same functions as the MySQL C API. You can do this by using an
installation utilitylike
yum
on a Linux system. Execute the following from the command
line, while logged in as therootor some other administrative filesystem user:
yum install ruby ruby-mysql
If you can't use
yum
on your server, you can check MySQL's website
to download Ruby
modules
and to find instructions on installing them.
Once you have Ruby and the MySQL/Ruby module installed on your server, you can then
write and run a Ruby program to connect to MySQL and query the databases. Let's go
through a very simple program to do this. For this example program, we'll use thead-
min_backup@localhostuser account. We created this user account in
Username and Host
.
We will be selecting and inserting data in a database we'll call
server_admin
. One of
the tables in this database will be
backup_policies
. We'll then insert data into this
table related to our backup policies as a reference. We'll log information about the backups,
and other server information in that database.
To preparefor the program we're about to write, let's create the
server_admin
database
and the tables we need for it. Create the database and the
backup_policies
table by
executing the following SQL statements:
CREATE DATABASE
server_admin
;
CREATE TABLE
backup_policies
(
policy_id
INT
AUTO_INCREMENT
KEY
,
backup_name
VARCHAR
(
100
),
file_format_prefix
VARCHAR
(
25
),
frequency
ENUM
(
'daily'
,
'weekly'
),
days
ENUM
(
'first'
,
'every'
),
start_time
TIME
,