Joshua Muheim
2006-Aug-16 17:45 UTC
[Rails] 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/.
Mohit Sindhwani
2006-Aug-16 17:57 UTC
[Rails] Migrations: only one table per migration file?
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
Joshua Muheim
2006-Aug-16 18:09 UTC
[Rails] Re: Migrations: only one table per migration file?
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/.
Jodi Showers
2006-Aug-16 18:16 UTC
[Rails] Re: Migrations: only one table per migration file?
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)
Joshua Muheim
2006-Aug-16 20:14 UTC
[Rails] Re: Migrations: only one table per migration file?
This sounds interesting. I''ll maybe check that out tomorrow or so. -- Posted via http://www.ruby-forum.com/.
Michael Schuerig
2006-Aug-16 20:37 UTC
[Rails] Re: Migrations: only one table per migration file?
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/
Joshua Muheim
2006-Aug-16 21:26 UTC
[Rails] Re: Migrations: only one table per migration file?
> And there''s plugin taking advantage of this: transactional_migrations. > http://www.redhillconsulting.com.au/rails_plugins.html#transactional_migrationsI 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/.
Kyle Slattery
2006-Aug-16 22:36 UTC
[Rails] Re: Migrations: only one table per migration file?
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/.
Eden Brandeis
2006-Aug-17 19:19 UTC
[Rails] Migrations: only one table per migration file?
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