Connecting Your Reports to the World

Now that you have a Rails application that can connect to your database and a database structure, you need to fill the database with some data. You can use the SQL code in Listing 5-3 to do just that.

Listing 5-3. Sample Data for the Actor Scheduling Application (actor_schedule_data.sql)

DELETE FROM actors;
DELETE FROM projects;
DELETE FROM rooms;
DELETE FROM bookings;

INSERT INTO actors (id, name) VALUES
                   (1, 'Jim Thompson'); INSERT INTO actors (id, name) VALUES
                   (2, 'Becky Leuser'); INSERT INTO actors (id, name) VALUES
                   (3, 'Elizabeth Berube'); INSERT INTO actors (id, name) VALUES
                   (4, 'Dave Guuseman'); INSERT INTO actors (id, name) VALUES
                   (5, 'Tom Jimson');

INSERT INTO projects (id, name) VALUES
                     (1, 'Turbo Bowling Intro Sequence');
INSERT INTO projects (id, name) VALUES
                     (2, 'Seven for Dinner Win Game Sequence');
INSERT INTO projects (id, name) VALUES
                     (3, 'Seven for Dinner Lost Game Sequence');

INSERT INTO rooms (id, name) VALUES
                  (1, 'L120, Little Hall'); INSERT INTO rooms (id, name) VALUES
                  (2, 'L112, Little Hall'); INSERT INTO rooms (id, name) VALUES
                  (3, 'M120, Tech Center');

INSERT INTO bookings (actor_id, room_id, project_id, booked_at) VALUES
                     (1,1,2, DATE_ADD( NOW(), INTERVAL 3 HOUR ));
INSERT INTO bookings (actor_id, room_id, project_id, booked_at) VALUES
                     (1,1,3, DATE_ADD( NOW(), INTERVAL 4 HOUR ));
INSERT INTO bookings (actor_id, room_id, project_id, booked_at) VALUES
                     (3,2,2, DATE_ADD( NOW(), INTERVAL 1 DAY ));
INSERT INTO bookings (actor_id, room_id, project_id, booked_at) VALUES
                     (2,3,1, DATE_ADD( NOW(), INTERVAL 5 HOUR ));

Save Listing 5-3 as actor_schedule_data.sql. In the listing, note the use of the DATE_ADD function to create relative times, rather than hard-coding them, so the times will always be in the future. Additionally, note the DELETE FROM statements at the beginning of Listing 5-3. These clear the database before the new data is inserted. For example, this lets you modify the data and then rerun this SQL script to have a fresh copy of your data.

Tip  You can also use migration to insert data, but the advantage of inserting your data in a separate SQL file is that it keeps the structure and the data separate. If you had two separate deployments, with two separate databases, you could use the same migrations on both.

You can run the SQL in Listing 5-3 and populate the database using the following command:

mysql -u my_mysql_username -p actor_schedule_development < actor_schedule_data.sql

Creating the Models for the Web Report

The models will represent your tables and the relationships between them. You can create the models using the following commands:

ruby script/generate model actor

--------------------------------------------
  exists  app/models/
  exists  test/unit/
  exists  test/fixtures/
  create  app/models/actor.rb
  create  test/unit/actors_test.rb
  create  test/fixtures/actor.yml
  exists  db/migrate
  create  db/migrate/002_create_actors.rb
--------------------------------------------

ruby script/generate model project

--------------------------------------------
  exists  app/models/
  exists  test/unit/
  exists  test/fixtures/
  create  app/models/project.rb
  create  test/unit/project_test.rb
  create  test/fixtures/project.yml
  exists  db/migrate
  create  db/migrate/003_create_projects.rb
--------------------------------------------

ruby script/generate model room

--------------------------------------------
  exists  app/models/
  exists  test/unit/
  exists  test/fixtures/
  create  app/models/room.rb
  create  test/unit/room_test.rb
  create  test/fixtures/room.yml
  exists  db/migrate
  create  db/migrate/004_create_rooms.rb
--------------------------------------------

ruby script/generate model booking

--------------------------------------------
  exists  app/models/
  exists  test/unit/
  exists  test/fixtures/
  create  app/models/booking.rb
  create  test/unit/booking_test.rb
  create  test/fixtures/booking.yml
  exists  db/migrate
  create  db/migrate/005_create_bookings.rb
--------------------------------------------

Each of these commands creates a number of files. You now have files named after the tables in the app/models directory, and those are the files you will edit. The generate model command also creates unit tests and fixtures, as well as a migration for each table. You can safely delete the migrations, since you already created all of the tables in your initial migration. The unit test and fixture files are for unit testing; visit the Rails site (http://rubyonrails.org) for information about their use.

First, edit the app/models/actor.rb file so it looks like Listing 5-4.

Listing 5-4. Actor Model (app/models/actor.rb)

clas s Actor < ActiveRecord::Base
  has_many :bookings
end

Save the modified model file. Then edit app/models/booking.rb to look like Listing 5-5.

Listing 5-5. Booking Model (app/models/booking.rb)

class Booking < ActiveRecord::Base 
  belongs_to :actor
  belongs_to :project
  belongs_to :room
end

The actor and booking models contain all of the relationships you will use in this example. The has_many relationship between the actor and booking tables lets you retrieve all of the booking objects for each actor, and the various belongs_to relationships in the booking model allow you to retrieve the details of each booking. You could also add has_many :bookings relationships to the room and project models, but since you won't use them in the controller, they are omitted.

At this point, you have a database with data in it and a Rails application with a few models. Of course, models are just one-third of an MVC application, so let's create a controller next.

Please check back tomorrow for the conclusion to this article.