Where should data migrations go?


#1

I recall problems with putting data migrations directly into a
database migration file.
Where should I do them?

I need to run a few SQL queries on the database, and then delete a few
unnecessary things.
Something like this pseudo example:

#get ids of old softwares
old_ids = SELECT id FROM softwares WHERE name IN (‘foo’, ‘bar’);
good_id = SELECT id FROM softwares WHERE name = ‘Good’;

#get directories using old softwares
SELECT id, software_id FROM directories WHERE software_id IN
(old_ids.join(’,’));

#update these directories with new software
UPDATE directories SET software_id = good_id WHERE software_id IN
(old_ids.join(’,’));

#delete old softwares
DELETE from softwares WHERE id IN (old_ids.join(’,’));

There is no change to the database schema itself, should this be a
throw-away rake task or is can it be done as a “DATA ONLY” migration?


Anthony E.
408-656-2473
http://anthony.ettinger.name


#2

How about just a small script that you run with “script/runner” ?


#3

I do “data only” migrations all the time, especially for reference
values and such. What kind of problems do you encounter? The one
caveat could be that you may have to define the model classes involved
inside your migration. For example:

class Software < ActiveRecord::Base; end

and in your self.up, simply use it as you would normally, ie:

Software.find_all_by_name([‘foo’,‘bar’]).each do |s|

do some updates and saves with s

end

hope that helps.

-H


#4

hi its seems like you have done a hard work on it. I have got lots of
information from your post. Really appreciate your work.!! It was
describe very nicely keep us doing good work…
www.dealsourcedirect.com/toy-story-lamp.html


#5

Anthony E. wrote in post #749437:

I recall problems with putting data migrations directly into a
database migration file.

What you recall are problems with putting seed data into migration
files. Migrations are for changing the database schema only.

Sometimes, however, changing the schema involves moving data around to
fit the new schema. Those operations should go in the same migration
that changes the schema.

Where should I do them?

I need to run a few SQL queries on the database, and then delete a few
unnecessary things.
Something like this pseudo example:

#get ids of old softwares
old_ids = SELECT id FROM softwares WHERE name IN (‘foo’, ‘bar’);
good_id = SELECT id FROM softwares WHERE name = ‘Good’;

#get directories using old softwares
SELECT id, software_id FROM directories WHERE software_id IN
(old_ids.join(’,’));

#update these directories with new software
UPDATE directories SET software_id = good_id WHERE software_id IN
(old_ids.join(’,’));

#delete old softwares
DELETE from softwares WHERE id IN (old_ids.join(’,’));

There is no change to the database schema itself, should this be a
throw-away rake task or is can it be done as a “DATA ONLY” migration?

That should be a rake task or something, if I understand correctly what
you’re doing. It should not be a migration.


Anthony E.
408-656-2473
http://anthony.ettinger.name

Best,

Marnen Laibow-Koser
http://www.marnen.org
removed_email_address@domain.invalid