Ruby on Rails
TutorialStepOne (Version #181)

<— Tutorial | TutorialStepTwo —>

  • Note that this tutorial only works for Rails 2.0.1 and greater. Rails 1.2.6 or lower will not work fully with this tutorial *

Getting started with databases

I’m assuming that you have already got a project open, so I shall skip that part. Be aware that to use MySQL databases with Rails you need to start your project with the command:

rails -d mysql ProjectName

You then need to edit the database.yml file (in any text editor) to properly link to your database etc. This file can be found in the ProjectName\config\ directory. You will be presented with something that looks like:

development:
  adapter: mysql
  encoding: utf8
  database: ProjectName_development
  username: root
  password: your_password_here
  host: localhost (modify if needed)

Once all this is set up, you just need to run the command:

rake db:create

Bam, your blank database is created for you to add things to. What you now need to do is run the command:

ruby script/generate migration add_table

The migration file has been created in the ProjectName\db\migrate directory, with the name 001_add_table.rb. You can now tell this file how to setup your database tables ready for your application to run from, and is a much easier way of handling how your database works. Open up your migration file (ProjectName\db\migrate\001_add_table.rb) and add your requirements as needed.

For example:

class AddTable < ActiveRecord::Migration
	def self.up
create_table :people do |t|
      # id is created for us.
      t.column :name,    :string, :limit => 50, :null => false
      t.column :street1, :string, :limit => 70, :null => false
      t.column :street2, :string, :limit => 70, :null => false
      t.column :city,    :string, :limit => 70, :null => false
      t.column :state,   :string, :limit => 2,  :null => false
      t.column :zip,     :string, :limit => 10, :null => false 
    end
  end

def self.down
drop_table :people
end

end

Note: an ID table in the database is automatically created, so you do not need to define one unless you really want to.

The valid column types are – binary, boolean, date, datetime, decimal, float, integer, string, time, and timestamp. And the valid options are – limit, default, and null. So :null => false means column is NOT_NULL. Easy to figure out, I’m sure.

Run the command:

rake db:migrate

Now you have all the tables you need. Easy as that. For further reading on migrations, go to UnderstandingMigrations

Note for Windows Users

If you have MySQL 4.1, you will most likely want to change the server’s security properties to allow the old password methods. Edit the my.ini in C:\Program Files\MySQL\MySQL Server 4.1

If this doesn’t work, after changing the my.ini, add a new mysql user, and update database.yml to use the new user and password. (see mysql bug #847)



    
	
  1. Use old password encryption method (needed for 4.0 and older clients).
    
old_passwords

Note: It seems like the Windows installation of RoR 1.0 has the reverse problem: it doesn’t support the old-style passwords. So it might be a good idea to skip this tip.

Note: MySQL Administrator 1.1.6 locks in User Administration. See workaround here:
http://bugs.mysql.com/bug.php?id=17879

GUI Database Tools

While you can do everything covered here using the mysql command-line tool, you may prefer to use a GUI client. See the list below:

<— Tutorial | TutorialStepTwo —>

Other languages:

  • Comment: Should there be “alter” in the list of privileges?

<— Tutorial | TutorialStepTwo —>

  • Note that this tutorial only works for Rails 2.0.1 and greater. Rails 1.2.6 or lower will not work fully with this tutorial *

Getting started with databases

I’m assuming that you have already got a project open, so I shall skip that part. Be aware that to use MySQL databases with Rails you need to start your project with the command:

rails -d mysql ProjectName

You then need to edit the database.yml file (in any text editor) to properly link to your database etc. This file can be found in the ProjectName\config\ directory. You will be presented with something that looks like:

development:
  adapter: mysql
  encoding: utf8
  database: ProjectName_development
  username: root
  password: your_password_here
  host: localhost (modify if needed)

Once all this is set up, you just need to run the command:

rake db:create

Bam, your blank database is created for you to add things to. What you now need to do is run the command:

ruby script/generate migration add_table

The migration file has been created in the ProjectName\db\migrate directory, with the name 001_add_table.rb. You can now tell this file how to setup your database tables ready for your application to run from, and is a much easier way of handling how your database works. Open up your migration file (ProjectName\db\migrate\001_add_table.rb) and add your requirements as needed.

For example:

class AddTable < ActiveRecord::Migration
	def self.up
create_table :people do |t|
      # id is created for us.
      t.column :name,    :string, :limit => 50, :null => false
      t.column :street1, :string, :limit => 70, :null => false
      t.column :street2, :string, :limit => 70, :null => false
      t.column :city,    :string, :limit => 70, :null => false
      t.column :state,   :string, :limit => 2,  :null => false
      t.column :zip,     :string, :limit => 10, :null => false 
    end
  end

def self.down
drop_table :people
end

end

Note: an ID table in the database is automatically created, so you do not need to define one unless you really want to.

The valid column types are – binary, boolean, date, datetime, decimal, float, integer, string, time, and timestamp. And the valid options are – limit, default, and null. So :null => false means column is NOT_NULL. Easy to figure out, I’m sure.

Run the command:

rake db:migrate

Now you have all the tables you need. Easy as that. For further reading on migrations, go to UnderstandingMigrations

Note for Windows Users

If you have MySQL 4.1, you will most likely want to change the server’s security properties to allow the old password methods. Edit the my.ini in C:\Program Files\MySQL\MySQL Server 4.1

If this doesn’t work, after changing the my.ini, add a new mysql user, and update database.yml to use the new user and password. (see mysql bug #847)



    
	
  1. Use old password encryption method (needed for 4.0 and older clients).
    
old_passwords

Note: It seems like the Windows installation of RoR 1.0 has the reverse problem: it doesn’t support the old-style passwords. So it might be a good idea to skip this tip.

Note: MySQL Administrator 1.1.6 locks in User Administration. See workaround here:
http://bugs.mysql.com/bug.php?id=17879

GUI Database Tools

While you can do everything covered here using the mysql command-line tool, you may prefer to use a GUI client. See the list below:

<— Tutorial | TutorialStepTwo —>

Other languages:

  • Comment: Should there be “alter” in the list of privileges?
Created on April 23, 2008 08:05 by Anonymous Coward (68.183.172.167)
Rollback | Forward in time (5 more) | See current | Back in time (180 more)