Topic: SOLVED how to encapsulate SQL code in a migration file

I am trying to run SQL code when running a migration in order to create the columns of a table. I want to use SQL rather than rails code because the data types are more specific in MySQL than in rails.

example:

  def self.up
   
    create_table :ingredients do |t|
            t.timestamps
    end
   
    execute %{
        ALTER TABLE ingredients DROP id;
        ALTER TABLE ingredients ADD id mediumint unsigned not null auto_increment key;
        ALTER TABLE ingredients ADD name varchar(100) not null;
           }

   end

This code gets refused at SQL line 2, line 1 is not executed. Please help me understand why. Thanks.

Last edited by carrot06 (2012-04-08 16:16:53)

Re: SOLVED how to encapsulate SQL code in a migration file

Try executing eachj statement individually
you drop a column called id then you add a column called id but the first drop has probably not yet been comited to the database.
Seems a very odd way to create a table? What's wrong with using normal migration syntax?

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: SOLVED how to encapsulate SQL code in a migration file

Thanks. It works.

To answer your question, I am using SQL to create a table rather than normal migration syntax, because I can specify the exact datatype in MySQL. For instance I can specify a char(2) in Mysql rather than simply a :string in ruby for all string types. If the database gets really big, it is more time efficient to use Char.

Let me know if this is incorrect.

Re: SOLVED how to encapsulate SQL code in a migration file

It's fine if you are going to only ever use MySQL as your database but it won;t port to postgres etc... which limits your hosting options

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)