Using Migrations with Legacy Schema

[linux user]

Hi All

Please excuse me if my questions sound silly.

I have developed a rails app with an existing legacy schema and now with changes in customer requirements, I need to change the schema. The legacy (schema) database has > 250K.

What I would like to know is

1. Is it possible to migrate the data along with migrating the schema if use Migrations? If it is possible can somebody point me to an example, because in all the migrations tutorials that I see they have self.up (to create tables) and self.down (to drop tables) but what about data?

2. If I use migrations I will have to change my model classes, how can I minimize the pains of keeping my model classes in sync with schema changes specially when I am using A) 'set_primary_key' etc. B) rake migrate VERSION=xx

3. When I write my unit test for testing models, how do I keep my tests in sync with rake migrate VERSION=xx? Can I migrate my fixtures along with schema changes?

4. I will need a to keep my test: database in sync with migrations and I am assuming that rake would take care of migrating the schema in test database before running the tests. Is my assumption correct?

TIA

-daya

[Steve Holder]

One of our rails applications was built on top of a legacy schema, and we haven't had any particular issues.  Even if you run into minor problems, migrations bring enough benefits that they're worth a little bit of annoyance.

On 9/7/06, linux user <fanof...@gmail.com> wrote:

What I would like to know is

1. Is it possible to migrate the data along with migrating the schema if use Migrations? If it is possible can somebody point me to an example, because in all the migrations tutorials that I see they have self.up (to create tables) and self.down (to drop tables) but what about data?

Yes.  You have a couple of options when updating data.  If you are updating many rows at once, the simplest way can be to just execute the appropriate SQL directly using 'execute'.  For example:

def self.up
  #make some table changes here...
  execute 'update mytable set ...'
end

However, keep in mind a migration is just a normal ruby source file, with some convenient rails stuff already set up for you.  This means you can execute whatever ruby code want as part of the migration.  For example:

def self.up
  User.transaction do
    User.find(:all).each {|u| u.new_attribute = 'some default value'; u.save!}
  end
end

will work just as well.  You'll probably want to make sure that you do all your updates (or even the whole migration) in a transaction, in case something goes wrong.  You should probably also take a look at the documentation for ActiveRecord::Migration for a discussion of issues such as using a model after you have changed its table in the migration.

2. If I use migrations I will have to change my model classes, how can I minimize the pains of keeping my model classes in sync with schema changes specially when I am using A) 'set_primary_key' etc. B) rake migrate VERSION=xx

3. When I write my unit test for testing models, how do I keep my tests in sync with rake migrate VERSION=xx? Can I migrate my fixtures along with schema changes?

What pains do you forsee in terms of keeping things in sync?  As long as you have everything under version control, run your tests regularly, and check in regularly, you should be able to assume that whenever you do an update the models and the migrations match up.  If you run tests without having migrated, its usually pretty obvious - you'll typically get a ton of database errors in your unit tests, in which case you can just rake db:migrate and then run the tests again.  This has not been an issue for us.
 

4. I will need a to keep my test: database in sync with migrations and I am assuming that rake would take care of migrating the schema in test database before running the tests. Is my assumption correct?

Yes, before you run your tests rake will automatically clone the structure of your development database to your test database.  As long as you run rake db:migrate to get your development database to reflect the structure you want, everything else should work nicely.


-Steve

[linux user]

On 9/7/06, Steve Holder <steve....@gmail.com> wrote:

One of our rails applications was built on top of a legacy schema, and we haven't had any particular issues.  Even if you run into minor problems, migrations bring enough benefits that they're worth a little bit of annoyance.

On 9/7/06, linux user < fanof...@gmail.com> wrote:

What I would like to know is

1. Is it possible to migrate the data along with migrating the schema if use Migrations? If it is possible can somebody point me to an example, because in all the migrations tutorials that I see they have self.up (to create tables) and self.down (to drop tables) but what about data?

Yes.  You have a couple of options when updating data.  If you are updating many rows at once, the simplest way can be to just execute the appropriate SQL directly using 'execute'.  For example:

def self.up
  #make some table changes here...
  execute 'update mytable set ...'
end

However, keep in mind a migration is just a normal ruby source file, with some convenient rails stuff already set up for you.  This means you can execute whatever ruby code want as part of the migration.  For example:

def self.up
  User.transaction do
    User.find(:all).each {|u| u.new_attribute = 'some default value'; u.save!}
  end
end

will work just as well.  You'll probably want to make sure that you do all your updates (or even the whole migration) in a transaction, in case something goes wrong.  You should probably also take a look at the documentation for ActiveRecord::Migration for a discussion of issues such as using a model after you have changed its table in the migration.

Steve 

Thanks for your reply. Your explainations are very helpful.

My problem is slightly more complicated by the fact that the I cannot and wouldn't be allowed to use DDLs to define the new schema. Instead the 
DBA would do it for me (they need work too :) ). So my scenario is an existing legacy schema say Schema1 and a new schema say Schema2. Now I don't know if I can use migrations to move data from Schema1 to Schema2 or say Schema5 (after some iterations). I guess I will have to manage two connections. I will google around and find something ..... in the mean while if can add some input it would be much appreciated.

2. If I use migrations I will have to change my model classes, how can I minimize the pains of keeping my model classes in sync with schema changes specially when I am using A) 'set_primary_key' etc. B) rake migrate VERSION=xx

3. When I write my unit test for testing models, how do I keep my tests in sync with rake migrate VERSION=xx? Can I migrate my fixtures along with schema changes?

What pains do you forsee in terms of keeping things in sync?  As long as you have everything under version control, run your tests regularly, and check in regularly, you should be able to assume that whenever you do an update the models and the migrations match up.  If you run tests without having migrated, its usually pretty obvious - you'll typically get a ton of database errors in your unit tests, in which case you can just rake db:migrate and then run the tests again.  This has not been an issue for us.

I guess most of these perceived pains are from lack for experience with migrations, I guess I will have more insight and less pain once I migrate my mind to migrations :)

thanks
-daya 

[Max Muermann]

<snip>

> new schema say Schema2. Now I don't know if I can use
> migrations to move data from Schema1 to Schema2 or say Schema5 (after some
> iterations). I guess I will have to manage two connections. I will google
> around and find something ..... in the mean while if can add some input it
> would be much appreciated.

There's a recipe in the Rails Recipes book about connecting to
multiple databases. Might be worth the investment.

Cheers,
Max