Can i do an sql update in a rails migration?

Rails
1

In order to do a single table inheritance, i added a new field called
‘type’, and set the value of it to either “Course” or “Lesson”, based on
another field called “is_course”, which is boolean.

I set up a callback to set the value of type whenever a record is saved,
and i set all the previously existing records values for ‘type’ with an
SQL update, via the rails helper update_all, eg

Item.update_all(‘type = “Lesson”’) #is_course defaults to 0, ie Lesson
is default
Item.update_all(‘type = “Course”’, ‘is_course = 1’)

That all worked fine, no problems. But it occurred to me that if the db
migration version is rolled back and forward again, ie the ‘type’ column
is removed and then re-added, then the values will be lost. Is there
any way to write the update into the same migration that adds the
column, so that it will be run again if the column has to be re-created?

2

Max W. wrote:

In order to do a single table inheritance, i added a new field called
‘type’, and set the value of it to either “Course” or “Lesson”, based on
another field called “is_course”, which is boolean.

I set up a callback to set the value of type whenever a record is saved,
and i set all the previously existing records values for ‘type’ with an
SQL update, via the rails helper update_all, eg

Item.update_all(‘type = “Lesson”’) #is_course defaults to 0, ie Lesson
is default
Item.update_all(‘type = “Course”’, ‘is_course = 1’)

That all worked fine, no problems. But it occurred to me that if the db
migration version is rolled back and forward again, ie the ‘type’ column
is removed and then re-added, then the values will be lost. Is there
any way to write the update into the same migration that adds the
column, so that it will be run again if the column has to be re-created?

Never mind, I’m an idiot. I just wrote the update_all call straight
into the migration and it works. Don’t know why i didn’t think to try
that before asking…

3

To answer your original question, you can use exec to run arbitrary
sql statements. SQLite does not allow the modification of a column
once it’s added to a table, so if you must change the definition of a
column (like change it from a varchar to a text), you have to do all
of the raw sql of creating a temp table, copying the data, dropping
the table, recreating the table, copying the data back, and then
dropping the temp table. I do all of that with exec.

Peace,
Phillip