Migrations: only one table per migration file?

Hi all

I have a new migration file created that adds some tables and fills one
with some data:

create_table :sound_styles do |t|
t.column :name, :string, :null => false
end

create_table :show_types do |t|
t.column :name, :string, :null => false
end

create_table :countries do |t|
t.column :name, :string, :null => false
end
ActionView::Helpers::FormOptionsHelper::COUNTRIES.each do |c|
Country.create :name => c # Fill in some content
end

So far so good… but I experienced that when the code, that fills in
the countries into the table throws an exception, then the whole
migration is only particularly succeeded, what means, that the stuff,
that succeeded, remains in the DB, and the rest just doesn’t happen.

This is very annoying, because I have to correct the DB schema by hand
(the down method is also not reliable because maybe it relies on some
stuff in the up method that failed before).

My question: why is the migration not reverted to the previous state
when an exception occurs? I know, this is maybe very difficult to do
because of all the possible random coding stuff that’s likely to be in
the up method…

But how can I avoid such problems in the future? Should I only create a
single table per migration, or something like that? Or shouldn’t I do
custom coding stuff in the migrations file as I did it now with the
countries?

Thanks for help.
Joshua

Joshua M. wrote:

t.column :name, :string, :null => false
the countries into the table throws an exception, then the whole
the up method…

But how can I avoid such problems in the future? Should I only create a
single table per migration, or something like that? Or shouldn’t I do
custom coding stuff in the migrations file as I did it now with the
countries?

Thanks for help.
Joshua

Personally, I prefer to keep it to one change per migration… but that
said, since a migration is only Ruby + Rails code, can’t you put in a
transaction block? I have no idea if transaction blocks will prevent
the problem you mention… but hopefully someone else will be able to
answer that.

But, like I said, I prefer to keep it to one change per migration. I’m
not sure what the ‘best practice’ is, though…

Cheers
Mohit.

Hey Joshua,

DDL[1](create, alter, drop) cannot be held under transactions via any
database I know. For this reason it’s almost impossible to place the
DDL under a failure - rails would have to reverse the .up. If you
properly code your .down, you should be able to use it to correct the
migration.

DML[2] (updates, inserts and such should be able to be rolled back).
I think (those more rails-fluent can confirm) that migrations operate
under a transaction. So any DML you run should be rolled back upon
exception.

cheers,
Jodi
[1] Data Definition Language - http://en.wikipedia.org/wiki/DML
[2] Data Manipulation Language - http://en.wikipedia.org/wiki/
Data_Definition_Language

Thanks so far, guys. About the 1-table-per-migration-file-thingie:

When I think about the fact that newer versions of Rails automatically
create a migration file when using script/generate model xxx then I
guess it’s assumed to use only one table per migration file. :slight_smile:

I don’t subscribe to the migration per table approach. Unless it’s
initial table creation - and even then you’re dealing with foreign
keys, et.c

After that you’re migrating for functional (or bug) release - which
can,and usually does effect many tables.

Jodi

This sounds interesting. I’ll maybe check that out tomorrow or so.

On 8/16/06, Jodi S. [email protected] wrote:

Hey Joshua,

DDL[1](create, alter, drop) cannot be held under transactions via any
database I know.

I’m not sure about other databases, but with PostgreSQL most DDL
statements are transaction safe:

test=> BEGIN;
BEGIN
test=> \d blah
Did not find any relation named “blah”.
test=> CREATE TABLE blah (id INT);
CREATE TABLE
test=> \d blah
Table “public.blah”
Column | Type | Modifiers
--------±--------±----------
id | integer |

test=> ROLLBACK;
ROLLBACK
test=> \d blah
Did not find any relation named “blah”.
test=> BEGIN;
BEGIN
test=> \d division
Table “public.division”
Column | Type | Modifiers
--------±----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘division_id_seq’::regclass)
name | character varying(30) | not null
Indexes:
“division_pkey” PRIMARY KEY, btree (id)
“division_name_key” UNIQUE, btree (name)

test=> DROP TABLE division;
DROP TABLE
test=> \d division
Did not find any relation named “division”.
test=> ROLLBACK;
ROLLBACK
test=> \d division
Table “public.division”
Column | Type | Modifiers
--------±----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘division_id_seq’::regclass)
name | character varying(30) | not null
Indexes:
“division_pkey” PRIMARY KEY, btree (id)
“division_name_key” UNIQUE, btree (name)

test=> BEGIN;
BEGIN
test=> ALTER TABLE division ADD COLUMN blah INT;
ALTER TABLE
test=> \d division
Table “public.division”
Column | Type | Modifiers
--------±----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘division_id_seq’::regclass)
name | character varying(30) | not null
blah | integer |
Indexes:
“division_pkey” PRIMARY KEY, btree (id)
“division_name_key” UNIQUE, btree (name)

test=> ROLLBACK;
ROLLBACK
test=> \d division
Table “public.division”
Column | Type | Modifiers
--------±----------------------±------------------------------------------------------
id | integer | not null default
nextval(‘division_id_seq’::regclass)
name | character varying(30) | not null
Indexes:
“division_pkey” PRIMARY KEY, btree (id)
“division_name_key” UNIQUE, btree (name)

On Wednesday 16 August 2006 22:11, Jeremy E. wrote:

On 8/16/06, Jodi S. [email protected] wrote:

Hey Joshua,

DDL[1](create, alter, drop) cannot be held under transactions via
any database I know.

I’m not sure about other databases, but with PostgreSQL most DDL
statements are transaction safe:
[snip]

And there’s plugin taking advantage of this: transactional_migrations.
http://www.redhillconsulting.com.au/rails_plugins.html#transactional_migrations

What I’m wondering about is where this is documented. I’ve looked for it
in the PostgreSQL docs, but was unable to find any notice that said
that or which DDL statements are transactional.

Michael


Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/

On 16/08/06, Jodi S. [email protected] wrote:

Hey Joshua,

DDL[1](create, alter, drop) cannot be held under transactions via any
database I know.

PostgreSQL can do it. Any DDL or DML is fully transactable (due to the
way it handles transaction isolation).

And there’s plugin taking advantage of this: transactional_migrations.
http://www.redhillconsulting.com.au/rails_plugins.html#transactional_migrations

I just wrote Simon Harris (Red Hill Consulting) an email asking if this
plugin is also working with MySQL4/5. I’ll let you know when I get an
answer.

Just to add something in–I found that if you have a problem with your
migration, instead of going in and changing to db back by hand, you can
just
set the schema version to the one you just created (the one with the
errors)
and then do rake migrate with the previous (working) working version

Say you’ve created a new migration, and it’s version 5, but when you run
rake migrate, you have errors. Just do the following to revert back to
version 4:

$ ruby script/runner ‘ActiveRecord::Base.connection.execute(“UPDATE
schema_info SET version = 5”)’
…some output…
$ rake migrate VERSION=4
…some output (runs the self.down of version 5)…

Maybe that will help a bit?

In my experience, PostgreSQL properly handled DDL transactions when I
ran
under a Linux distribution, but not under Windows. Did anyone else have
this experience? Just throwing this out to save some frustration if it
doesn’t seem to work for you.

Kyle S. wrote:

Just to add something in–I found that if you have a problem with your
migration, instead of going in and changing to db back by hand, you can
just
set the schema version to the one you just created (the one with the
errors)
and then do rake migrate with the previous (working) working version

Say you’ve created a new migration, and it’s version 5, but when you run
rake migrate, you have errors. Just do the following to revert back to
version 4:

$ ruby script/runner ‘ActiveRecord::Base.connection.execute(“UPDATE
schema_info SET version = 5”)’
…some output…
$ rake migrate VERSION=4
…some output (runs the self.down of version 5)…

Maybe that will help a bit?

Since that runs the .down in your migration, it will usually fail too,
depending on why your .up failed. For example, if .up fails while adding
a new column to a table, the .down will fail while trying to remove it,
because it does not exist…very frustrating indeed.

PostgreSQL seems to be handling DDL transactions fine for us on Windows.
For reference, we’re running 8.1.4.

To add, my experience with mysql, interbase (firebird) and oracle
didn’t address them - although I’ve used them over a number of years,
and my most recent experiences are with oracle and mysql.

Through some reading it looks like sqlite supports them.

Another thought comes to mind : table creates (and drops) are a
special kind of DML - For example you can create tables in oracle,
mysql, interbase (probably most others) through table inserts (table
and detail column rows) - given this you could do table creates in
mysql under transaction support.

good luck.
Jodi

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs