SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist Pixelateur's Avatar
    Join Date
    Feb 2006
    Location
    Hamburg, Germany
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Creating Tables using Rails Methods

    How do you guys normally create database tables when working in Rails? According to the Pickaxe book it's best to put all the Data Definition Language (DDL) into .sql files and then run them on the command line.

    Create the table:
    Code:
    create table contents (
    	id			int			not null auto_increment,
    	keyword		varchar(100)	not null,
    	english		text			not null,
    	german		text			not null,
    	primary key (id)	
    	);
    Populate it with some data:
    Code:
    insert into contents values(null, 'page1_title', 'Welcome', 'Willkommen');
    insert into contents values(' ', 'page2_title', 'Products', 'Produkte');
    insert into contents values(' ', 'page3_title', 'Contact', 'Kontakt');
    
    insert into contents values(' ', 'page1_heading', 'Hello guys!', 'Hallo Leute!');
    insert into contents values(' ', 'page2_heading', 'Our Products', 'Unsere Produkte');
    insert into contents values(' ', 'page3_heading', 'Contact Us', 'Kontaktieren Sie uns');
    Now I've heard that in later versions of Rails (or Ruby?) this can be done much more easily using methods like these:
    Code:
    create_table "ingredients" do |t|
    t.column "recipe_id", :integer
    t.column "name", :string
    t.column "quantity", :integer
    end
    But what I don't get is WHERE I have to put these methods and how to execute them...

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    The pickaxe book would be out of date with current Rails methods I'd guess. You should definitely be using migrations.

    How to use migrations:

    First you use the Rails generator to create your migration. You can generate just the migration, or you can do it by generating a model, which will also give you a migration file.

    Code:
    ./script/generate migration add_ingredients
    This will create something like 001_add_ingredients.rb in the /db/migrate folder of your app.

    Then you create the table like so:
    Code:
    class AddIngredients < ActiveRecord::Migration
    
    def self.up
      create_table "ingredients" do |t|
        t.column "recipe_id", :integer
        t.column "name", :string
        t.column "quantity", :integer
      end
    end
    
    def self.down
      drop_table "ingredients"
    end
    
    end
    The self.up method runs when you run the database migration via rake. Self.down runs when you tear down the database.

    If you need to update the table later (say, adding a user_id column to track which recipe belongs to a user), you would generate another migration to add the column:

    Code:
    ./script/generate migration add_user_id_to_ingredients
    Then you'd add your column in /db/migrate/002_add_user_id_to_ingredients.rb:
    Code:
    class AddUserIdToIngredients < ActiveRecord::Migration
    
    def self.up
      add_column "ingredients", "user_id", :integer
      #optional, but it could help depending on your site
      add_index "ingredients", "user_id"
    end
    
    def self.down
    
    end
    
    end
    Don't think of each migration file as a table, think of it as a change to the data model. You can create more than one table in each migration, or you can just add/remove columns, indices, etc.

    Migrations act as version control for your database changes and they're extremely useful.

  3. #3
    SitePoint Evangelist Pixelateur's Avatar
    Join Date
    Feb 2006
    Location
    Hamburg, Germany
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, this is the best tutorial on migrations that I've come across so far. And I've been googling a lot... :-)
    But suppose I want to add this user_id column later in the process. Why would I add a new migration rather than just change the initial one to something like so:

    Code:
    class AddIngredients < ActiveRecord::Migration
    
    def self.up
      create_table "ingredients" do |t|
        t.column "recipe_id", :integer
        t.column "name", :string
        t.column "quantity", :integer
        t.column "user_id", :integer
      end
    end
    
    def self.down
      drop_table "ingredients"
    end
    
    end

  4. #4
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,236
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    That can be bad. Imagine this scenario:

    I download version 0.1 of your app, which uses only the first migration (since you didn't have a user model in that version). I then download version 0.3 later that adds user functionality. My database has already gotten to version 1, so when I run rake to migrate the database it will skip the first file and go to the first one it hasn't used. When I try to run the app, I'll get errors all over the place if the associated user is required to post. Why? Because that user_id column isn't in my database even though I ran the migration. To fix the app I have to start from scratch with the database, and I lose all the data that was in there before*.

    *Well, you can make a different database and use SQL to migrate your data over or something, but the point is you just wasted a lot of time that could have been avoided by creating a new file.

  5. #5
    SitePoint Evangelist Pixelateur's Avatar
    Join Date
    Feb 2006
    Location
    Hamburg, Germany
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I just ran the migrate script and it seems to work. Just one more thing: The ID column, will it be generated by default. Because in Rails every table needs an ID column, no?

  6. #6
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes and no. The id column will be generated by default. Every table in Rails *except* has_and_belongs_to_many link tables (fogot the real name) needs an id column. You have to delete the (auto generated) id column of these tables in your migrations because there will be problems if you don't. With the current REST trend you are more likely to use has_many :through so you don't have this problem.

  7. #7
    SitePoint Evangelist Pixelateur's Avatar
    Join Date
    Feb 2006
    Location
    Hamburg, Germany
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hm, ok. I just ran the migrate script and everything looks fine. The ID column was created automatically :-) Thanks for your warning.

  8. #8
    SitePoint Evangelist Pixelateur's Avatar
    Join Date
    Feb 2006
    Location
    Hamburg, Germany
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And how can I populate the tables with some data? Do I need an .sql file to do this? Or is there a Rails method?

  9. #9
    SitePoint Enthusiast kyko's Avatar
    Join Date
    May 2006
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    migrations can populate the DB with rows but schema.rb can't for some reason. so I usually put any row related things in an sql file but I don't know if that is how you should do it. To have really nice video tutorial there is on at rubyonrails.com in the screen casts.
    Stop Global &nbsp;


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •