Adding a Missing Column

Let's say we decide that our participants table needs an additional column for storing the participant's blog URL. This means we'll need to add a column to the database table, and we'll need to make sure that the various maintenance pages are updated to add support for this new column. Let's alter the file db/create.sql, adding the blog_url column. Let's save this file as create2.sql as shown here:

drop table if exists participants;
create table participants (
  id int not null auto_increment,
  name varchar(100) not null,
  city text not null,
  blog_url varchar(200) not null,
  primary key (id)
);

When I first created this file, I added a drop table command at the top of it. This now allows us to create a new (empty) schema instance with the command:
c:/rails/work/student>mysql student <db/create2.sql

Obviously, this approach only works if there isn't important data already in the database table (as dropping the table wipes out the data it contains). That's fine during development, but in production we'd need to be careful.

The schema has changed, so our scaffold code is now out-of-date. As we've made no changes to the code, it's safe to regenerate it. Notice that the generate script prompts us when it's about to overwrite a file. We type Y to indicate that it can overwrite all files. The command is:
c:/rails/work/student>ruby script/generate scaffold Participant Admin

You can now enter the new data and the screen should look like this.

First Post | Previous | Next

---

Technorati Tags: Adding a Missing Column
Blogs linking to this article