Rails - Aug 2006 - 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

-- 
Posted via http://www.ruby-forum.com/.

Joshua Muheim wrote:
> 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
>
>   
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

On 16-Aug-06, at 1:45 PM, Joshua Muheim wrote:
> 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
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

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. :-)

-- 
Posted via http://www.ruby-forum.com/.

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

On 16-Aug-06, at 2:09 PM, Joshua Muheim wrote:
> 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. :-)
>
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails

On 8/16/06, Jodi Showers <jodi@nnovation.ca>
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)

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

-- 
Posted via http://www.ruby-forum.com/.

On Wednesday 16 August 2006 22:11, Jeremy Evans wrote:
> On 8/16/06, Jodi Showers <jodi@nnovation.ca> 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 Schuerig
mailto:michael@schuerig.de
http://www.schuerig.de/michael/

> 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.

-- 
Posted via http://www.ruby-forum.com/.

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?

On 8/16/06, Joshua Muheim <forum@josh.ch> wrote:
>
> > 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.
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060816/c3601df6/attachment-0001.html

On 16/08/06, Jodi Showers <jodi@nnovation.ca>
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).

-- 
Rasputin :: Jack of All Trades - Master of Nuns
http://number9.hellooperator.net/

Kyle Slattery 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.

-- 
Posted via http://www.ruby-forum.com/.

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.

On 8/17/06, Dick Davies <rasputnik@gmail.com>
wrote:
>
> On 16/08/06, Jodi Showers <jodi@nnovation.ca> 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).
>
> --
> Rasputin :: Jack of All Trades - Master of Nuns
> http://number9.hellooperator.net/
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060817/1836d374/attachment.html

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

On 8/17/06, Eden Brandeis <ebrandeis@gmail.com>
wrote:
>
> 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.
>
>
> On 8/17/06, Dick Davies <rasputnik@gmail.com> wrote:
> >
> > On 16/08/06, Jodi Showers <jodi@nnovation.ca> 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).
> >
> > --
> > Rasputin :: Jack of All Trades - Master of Nuns
> > http://number9.hellooperator.net/
> > _______________________________________________
> > Rails mailing list
> > Rails@lists.rubyonrails.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
>
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060817/29ce1bb3/attachment-0001.html

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

On 17-Aug-06, at 3:18 PM, Eden Brandeis wrote:
> 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.
>
> On 8/17/06, Dick Davies <rasputnik@gmail.com> wrote:
> On 16/08/06, Jodi Showers <jodi@nnovation.ca> 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).
>
> --
> Rasputin :: Jack of All Trades - Master of Nuns
> http://number9.hellooperator.net/
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060817/73819c68/attachment.html