Tuesday, July 11, 2006

Rails and Database Tables

We shall now create a web interface that lets us maintain a student database - create new student records, edit existing student records, delete unwanted ones, and so on. We'll develop this application in small iterations. We'll start off by creating a new Rails application. This is where we'll be doing all our work. Next, we'll create a database to hold our information.

Open a command window and navigate to our folder 'work' where the path on my machine is c:/rails/work Switch to the work folder and use the rails command to create an application called student, as follows.
c:/rails/work>rails student

Create the database student
In a command window type:
c:/rails/work/student>mysql -uroot
You should now get a mysql prompt. Next at the mysql prompt, type as follows:
mysql>create database student;
It will respond with:
Query OK, 1 row affected (0.00 sec)
Next, on the mysql prompt, type:
mysql>grant all on student.* to 'root'@'localhost';
Then type:
mysql>exit

Let's write the Data Definition Language (DDL) for creating the student table in MySQL. Your application student has a sub folder db where we shall store this file create.sql
drop table if exists participants;
create table participants (
id int not null auto_increment,
name varchar(100) not null,
city text not null,
primary key (id)
);
Rails assumes that every table it handles has as its primary key an integer column called id Internally, Rails uses the value in this column to keep track of the data it has loaded from the database and to link between data in different tables.

Now use the mysql client to execute the DDL and create the table in our student database. Type:
c:/rails/work/student>mysql student <db/create.sql
This would have created our participants table.

Rails Naming Convention
Rails assumes that:
  • database table names, like variable names, have lowercase letters and underscores between the words.
  • table names are always plural.
  • files are named in lowercase with underscores.
Rails uses this knowledge of naming conventions to convert names automatically. For example, your application might contain a model class that handles line items. You'd define the class using the Ruby naming convention, calling it LineItem. From this name, Rails would automatically deduce the following.
  • That the corresponding database table will be called line_items. That's the class name, converted to lowercase, with underscores between the words and pluralized.
  • Rails would also know to look for the class definition in a file called line_item.rb (in the app/models directory).
Rails controllers have additional naming conventions. If our application has a store controller, then the following happens.
  • Rails assumes the class is called StoreController and that it's in a file named store_controller.rb in the app/controllers directory.
  • It also assumes there's a helper module named StoreHelper in the file store_helper.rb located in the app/helpers directory.
  • It will look for view templates for this controller in the app/views/store directory.
  • It will by default take the output of these views and wrap them in the layout template contained in store.rhtml or store.rxml in the directory app/views/layouts.
Rails helps by giving you most of the configuration for free if you follow the standard conventions.

First Post | Previous | Next



Technorati Tags:
Blogs linking to this article

8 Comments:

Blogger raj said...

Next to that .... here we go......

C:\ruby\work\student>mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.23.47-nt

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

mysql> show databases;
+--------------+
| Database |
+--------------+
| blog |
| cookbook |
| firstproject |
| mysql |
| quiz |
| student |
| test |
| testproj |
+--------------+
8 rows in set (0.00 sec)

mysql> use student;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| participant |
+-------------------+
1 row in set (0.01 sec)

mysql> desc participant;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(100) | | | | |
| city | text | | | | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

4:06 PM  
Blogger Anish Betawadkar said...

Thanks Raj that helps.

6:01 PM  
Blogger Scot Gardner said...

c:/rails/work/student>mysql student <db/create.sql

should read:

c:/rails/work/student>mysql -uroot student <db/create.sql

7:49 PM  
Blogger SatishTalim said...

Scot, I am not much of a databse guy. WHat I wrote in the blog, worked for me. Is the -uroot necessary?

6:30 AM  
Blogger Gaurav V Bagga said...

in case i want to override the conventions then how to do that

10:22 AM  
Blogger SatishTalim said...

Gaurav, you can override all of these conventions using the appropriate declarations in your Rails classes.

10:25 AM  
Blogger Nitin Asati said...

Hi,

if you want to override -

Primary key convention : if your primary key field name is not "id" then use set_primary_key "id_field_name" in model of that db table

To use another name for model specify the model name in controller using -
model : model_name

If you want to pluralization put off use following in environment.rb(in config folder)
ActiveRecord::Base.pluralize_table_names = false

10:53 AM  
Blogger Ashish Kulkarni said...

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

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

mysql> create database student;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on student.* to 'root'@'localhost';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on student.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> connect student
Connection id: 19
Current database: student

mysql> show tables;
Empty set (0.00 sec)

mysql> drop table if exists participants;
create table participants (
id int not null auto_increment,
name varchar(100) not null,
city text not null,
primary key (id));
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.19 sec)

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| participants |
+-------------------+
1 row in set (0.00 sec)

mysql> desc participants;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| city | text | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

5:51 PM  

Post a Comment

Links to this post:

Create a Link

<< Home