Connecting Your Reports to the World

Next, let's create the database to store the data:

mysqladmin create actor_schedule_development -u your_mysql_username -p

At this point, you've created a database for your application, but before you populate this database, let's connect the application to your database server. After that, you can use a Rails mechanism called migrations to create the database tables for you. Edit the config/database.yml file to read as shown in Listing 5-1.

Listing 5-1. Database Configuration File for the Web Report (config/database.yml)

development: 
  adapter: mysql
  database: actor_schedule_development
  username: your_user_name
  password: your_password
  host: localhost

Note that Rails creates three database connection settings by default, but this example uses only the development environment to keep things simple. The other environments are testing, used for automated testing, and production, used for deployment. Additionally, the default file includes a number of comments, which I've removed from the listing for the sake of brevity.

Next, you need to create a new migration.

Creating a Migration

Migrations are bits of Ruby code that control the structure of a database. Each migration represents a set of changes to a database. The first migration usually specifies the initial structure of a database, and each successive version represents a change of some kind--adding a column, setting a default value, renaming a table, and so forth. Migrations are designed to be cross-platform, so you can usually run the same migration across multiple databases. (Of course, if you use any database-specific features, the migration won't be cross-platform.) Migrations are versioned, so you can upgrade and downgrade them as you see fit. Rails also keeps track of the current version of your database, so if you have a number of migrations, Rails will run only the new migrations.

Create a new migration by using the following command:

ruby script/generate migration initial_schema

--------------------------------------------
  create  db/migrate
  create  db/migrate/001_initial_schema.rb
--------------------------------------------

This creates a skeleton migration. The initial db/migrate/001_initial_schema.rb file looks like this:

class InitialSchema < ActiveRecord::Migration
  def self.up
  end

  def self.down
  end
end

As you can see, it's a single class that inherits from ActiveRecord::Migration, and it has two class methods: up and down, which upgrade and downgrade the version of the database structure, respectively, when the migration is run. Fortunately, Rails creates the skeleton containing the class definition and method names, so you only need to fill out the migration. Let's do that now. Listing 5-2 shows the full migration class.

Listing 5-2. Application Schema (db/migrate/001_initial_schema.rb)

class InitialSchema < ActiveRecord::Migration
  def self.up
    create_table :actors do |t|
      t.column :name, :string, :length=>45
      t.column :phone, :string, :length=>13
    end
    create_table :projects do |t|
      t.column :name, :string, :length=>25
    end
    create_table :rooms do |t|
      t.column :name, :string, :length=>25
    end
    create_table :bookings do |t|
      t.column :actor_id, :integer
      t.column :room_id, :integer
      t.column :project_id, :integer
      t.column :booked_at, :datetime
    end
  end

  def self.down
    drop_table :actors
    drop_table :projects
    drop_table :rooms
    drop_table :bookings
  end
end

Save this as db/migrate/001_initial_schema.rb.

Next, run the migration to create the database structure that the migration describes, using the following command:

rake db:migrate

--------------------------------------------
(in /your_path/your_directory/actor_schedule) == InitialSchema: migrating ============================================
-- create_table(:actors)
   -> 0.0000s
-- create_table(:projects)
   -> 0.0160s
-- create_table(:rooms)
   -> 0.0000s
-- create_table(:bookings)
   -> 0.0000s
== InitialSchema: migrated (0.0160s) ============================================
--------------------------------------------

Note that this process also works in reverse. You can specify a version to migrate your database to using the VERSION=x option, and specifying 0 will revert your database. For example, the following command will undo the previous migration command:

rake db:migrate VERSION=0

--------------------------------------------
(in /your_path/your_directory/actor_schedule) == InitialSchema: reverting ============================================
-- drop_table(:actors)
   -> 0.0000s
-- drop_table(:projects)
   -> 0.0000s
-- drop_table(:rooms)
   -> 0.0000s
-- drop_table(:bookings)
   -> 0.0160s
== InitialSchema: reverted (0.0630s) ============================================

Note  The rake command has a whole host of other uses. It's similar to the make command, but it's written in pure Ruby and is used to perform various maintenance tasks for Rails applications. The particular rake task discussed here, db:migrate, uses migrations to either upgrade or downgrade a database. You can find a list of all of the tasks available for a Rails application by running the command rake -T.