Thursday, July 13, 2006

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:
Blogs linking to this article

2 Comments:

Blogger Ashish Kulkarni said...

C:\rails>mysql -u root -p student
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28 to server version: 5.0.22-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists participants;
Query OK, 0 rows affected (0.14 sec)

mysql> 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));
Query OK, 0 rows affected (0.13 sec)

mysql> exit
Bye

C:\rails>ruby script/generate scaffold Participant admin
exists app/controllers/
exists app/helpers/
exists app/views/admin
exists test/functional/
dependency model
exists app/models/
exists test/unit/
exists test/fixtures/
identical app/models/participant.rb
identical test/unit/participant_test.rb
identical test/fixtures/participants.yml
overwrite app/views/admin/_form.rhtml? [Ynaq] Y
force app/views/admin/_form.rhtml
identical app/views/admin/list.rhtml
identical app/views/admin/show.rhtml
identical app/views/admin/new.rhtml
identical app/views/admin/edit.rhtml
identical app/controllers/admin_controller.rb
identical test/functional/admin_controller_test.rb
identical app/helpers/admin_helper.rb
identical app/views/layouts/admin.rhtml
identical public/stylesheets/scaffold.css

6:10 PM  
Blogger Ashish Kulkarni said...

I suppose, we could also do an "alter table...add column..." to add a missing column without wiping out the entire database.

6:10 PM  

Post a Comment

<< Home