> > add_foreign_key(:employees, :companies, :options => 'deferrable') > > What is the effect of the code above in such scenario if a user wants to > run the code on MySql or MS SQL Server? >
Good point. You should bring this up as a foreigner issue and do a pull request to add common options to its adapters like: https://github.com/matthuhiggins/foreigner/blob/master/lib/foreigner/connection_adapters/postgresql_adapter.rb and oracle and whatever else supports similar options. However, I don't think you always would want to specify deferrable, and if you specify a deferrable option, and it isn't supported then should it just ignore it? Then to be DB agnostic you'd have to include all relevant options for each DB, and maybe some share the same name that you wouldn't want to use for others, so then you'd end up with something like (maybe): add_foreign_key(:employees, :companies, :pg_specific_options => [:deferrable, :some_other_pg_option], :oracle_specific_options => [:some_oracle_option]) but that isn't much different than putting logic in the migration like: case ActiveRecord::Base.connection.adapter_name when 'PostgreSQL', 'Oracle' add_foreign_key(:employees, :companies, :options => 'deferrable') when 'MySQL' else raise "Database adapter not supported. Please file issue at (url to your github repo issues)" end but, I still see your point. It would be nice for sure to not have to worry about it. On Thursday, November 29, 2012 6:42:05 AM UTC-5, Rodrigo Rosenfeld Rosas wrote: > > Em 29-11-2012 09:21, Gary Weaver escreveu: > > Rodrigo, > > We're using postgres with structure.sql and a legacy DB that has plenty of > constraints (fkey, unique) and in the Rails app using that schema for new > migrations we're using foreigner ( > https://github.com/matthuhiggins/foreigner ) to add new fkeys, and iirc > using add_index for some unique constraints (a la > http://stackoverflow.com/a/3370333/178651 ). I've read mixed things about > using deferrable for everything, but it might be appropriate in some apps. > > AR supports using execute to add/remove constraints, etc. also, and > foreigner supports deferrable via specifying new constraints like: > > add_foreign_key(:employees, :companies, :options => 'deferrable') > > Rails create_table I think may be the only place that doesn't allow custom > SQL for constraint definition, and I got schooled on that yesterday when > trying to allow a spot for custom SQL within the parenths of the generated > SQL for create table. :) > > I agree that Rails could be more constraint-friendly, but I think you can > accomplish what you are trying to do with what is there? > > > I really don't understand this argument. You could really just get rid of > all DSL in migrations and keep only "execute" with no loss of > functionality, right? You could as well just use SQL everywhere in your > application instead of relying on any kind of ORM, right? > > Here is another common usage example from another popular gem that would > have problem when "add_index [:parent_id, :position], unique:true" is used: > > acts_as_list scope: :parent (see meaning here: > https://github.com/swanandp/acts_as_list/blob/master/lib/acts_as_list/active_record/acts/list.rb#L20 > ) > > https://github.com/swanandp/acts_as_list/blob/master/lib/acts_as_list/active_record/acts/list.rb#L179(problematic > implementation when add_index is used with unique: true) > > I don't understand why AR migrations insists that foreign keys are not > much important to be included by default. There have always been external > gems to add support DSL to foreign keys but some of them are no longer > maintained after a while. This is the main problem in my opinion. You rely > on another gem, like foreigner, and then some years later when a new AR is > released at some point you don't get more support from that gem. I don't > recall the name right now but there was a quite popular gem in Rails 1 era > to add foreign keys constraints to AR migrations DSL that is no longer > maintained. > > If the Rails community can't convince itself about the importance of basic > things in ACID databases like transactions, foreign keys and other > constraints than I think I'm out of luck with regards to deferrable > constraints... :( (yes, when I talk about transactions I mean the huge > amount of Rails applications out there running MySql with MyISAM engine, > that used to be the default one until recently in 5.5 when the InnoDB is > now the default one). > > When developing open-source applications (like Redmine, Gitorious, etc) > you usually want to support as many database vendors as possible. So, what > happens if some application like this uses your proposed solution? > > add_foreign_key(:employees, :companies, :options => 'deferrable') > > What is the effect of the code above in such scenario if a user wants to > run the code on MySql or MS SQL Server? > > > On Thursday, November 29, 2012 5:59:11 AM UTC-5, Rodrigo Rosenfeld Rosas > wrote: >> >> I think I'm late at the party and lost a lot happening here yesterday... >> >> At least it is good to know what core considers a good fit or not for AR >> Migrations. Since constraints are a fundamental piece in my projects I get >> the warning "you should avoid AR at all, including AR migrations". >> Currently it is not a big deal for me as I don't plan to support any other >> database vendor than PG so I can keep using "execute" in my migrations but >> I thought that maybe some OSS projects could benefit from some built-in >> support in AR migrations when using a popular plugin like this one: >> >> >> https://github.com/collectiveidea/awesome_nested_set/blob/master/lib/awesome_nested_set/awesome_nested_set.rb#L655 >> >> I don't really believe the method above will always work in PG, for >> example, if some OSS project has created an index like: >> >> add_index :some_table, [:parent_id, :lft], unique: true >> add_index :some_table, [:parent_id, :rgt], unique: true >> >> The Rails community still doesn't seem to be mature enough yet with >> regards to database constraints, which is unfortunate, so I believe most >> people using awesome_nested_set (I never used, just took some popular gem >> as an example) don't have the indices above in their migration. And if it >> happens for an OSS project to have an unique index like above it is likely >> that it won't always work in PG unless the OSS project uses a custom >> "add_unique_index" that would be portable among database vendors to include >> the deferrable constraint instead for databases like PG. >> >> I just suggested that unique constraints/indices should be deferrable by >> default to avoid the problem above, which seems to be a very common >> use-case. Otherwise the message that comes to me is "if you're not doing >> MySql, get away from AR. we just pretend to support PG, but if you need >> first-class support, you should be really considering a more robust ORM, >> like Sequel". >> >> Em 28-11-2012 17:15, Rafael Mendonça França escreveu: >> >> I don't think this is a good fit for the core. >> >> You are able to use SQL statements inside your migrations, so we >> already support this. >> >> Rafael Mendonça França >> http://twitter.com/rafaelfranca >> https://github.com/rafaelfranca >> >> >> -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Core" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/rubyonrails-core/-/YhSw7VXH1kEJ. > To post to this group, send email to rubyonra...@googlegroups.com<javascript:> > . > To unsubscribe from this group, send email to > rubyonrails-co...@googlegroups.com <javascript:>. > For more options, visit this group at > http://groups.google.com/group/rubyonrails-core?hl=en. > > > -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/hvbxC9UkucAJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
