if i have a model person and the sequence isnt called person_id_seq how do i tell rails what the sequence name is? _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 13.5.2005, at 18:48, Sean T Allen wrote:> if i have a model person and the sequence isnt called > > person_id_seq > > how do i tell rails what the sequence name is?AFAIK Rails doesn''t know anything about sequences. IIRC you can set the default value of a primary key field to be sequencename.nextval. //jarkko> > > > <sean.vcf>_______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On 5/13/05, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> wrote:> On 13.5.2005, at 18:48, Sean T Allen wrote: > > > if i have a model person and the sequence isnt called > > > > person_id_seq > > > > how do i tell rails what the sequence name is? > > AFAIK Rails doesn''t know anything about sequences. IIRC you can set the > default value of a primary key field to be sequencename.nextval. > > //jarkko > > > > > > > > > <sean.vcf>_______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > -- > Jarkko Laine > http://jlaine.net > http://odesign.fi > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > > >Actually the postgresql connection adapter DOES know about the sequences. It looks like there is no direct control over what the sequence name is. Take a look at /path/to/gems/activerecord-1.10.1/lib/active_record/connection_adapters/postgresql_adapter.rb It looks like the only method that uses this is the private #last_insert_id though, so it should be easy to override this behavior if you need to do so. Jason
On 5/13/05, Sean T Allen <sean-5W9FBhQXBOtBDgjK7y7TUQ@public.gmane.org> wrote:> how do i tell rails what the sequence name is? >The current adapter is unable to handle such circumstances. It probably could be modified but would most likely not be the quickest of fixes. As shown by the following code the last_insert_id function doesn''t get anything but the table_name and column_name of the PK. lib/active_record/connection_adapters/postgresql_adapter.rb def last_insert_id(table, column = "id") sequence_name = "#{table}_#{column || ''id''}_seq" @connection.exec("SELECT currval(''#{sequence_name}'')")[0][0].to_i end If you absolutely require this change and don''t mind a small speed hit - the best suggestion I would have for you is to take a diffirent approach then telling Rails what the sequence name is. I would look at modifying this function to lookup the primary key default value from the metadata tables - which would be something like nextval(sequence) - parse out the sequence name and then use that as sequence_name in the above function. Hope that makes some sense - it''s Friday :) -- John W Higgins wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
On 5/13/05, Sean T Allen <sean-5W9FBhQXBOtBDgjK7y7TUQ@public.gmane.org> wrote:> Yeah I found a patch on the website that works beautifully.I''m assuming you are referring to #1273 - just one word of caution - that patch may be a little dangerous in that it appears to modify code that is non Postgresql specific (fixtures.rb) without any concern for other adapters that use that shared code. This is fine if you are running only on Postgresql but is not very nice if you ever switch databases :) It also might have a difficult time in ever getting pushed into the main trunk as well (at least I hope it would if it is unfriendly to others). If on the other hand you don''t mind patching during upgrades and you are a Postgresql only shop - congrats on getting back on track.... -- John W Higgins wishdev-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
Jason Foreman wrote:>On 5/13/05, Jarkko Laine <jarkko-k1O+Gnc6WpmsTnJN9+BGXg@public.gmane.org> wrote: > > >>On 13.5.2005, at 18:48, Sean T Allen wrote: >> >> >> >>>if i have a model person and the sequence isnt called >>> >>>person_id_seq >>> >>>how do i tell rails what the sequence name is? >>> >>> >>AFAIK Rails doesn''t know anything about sequences. IIRC you can set the >>default value of a primary key field to be sequencename.nextval. >> >> >> >Actually the postgresql connection adapter DOES know about the >sequences. It looks like there is no direct control over what the >sequence name is. Take a look at >/path/to/gems/activerecord-1.10.1/lib/active_record/connection_adapters/postgresql_adapter.rb > >It looks like the only method that uses this is the private >#last_insert_id though, so it >should be easy to override this behavior if you need to do so. > > >Yeah I found a patch on the website that works beautifully. _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On Fri, May 13, 2005 at 11:01:58AM -0700, John Higgins wrote:> If you absolutely require this change and don''t mind a small speed hit > - the best suggestion I would have for you is to take a diffirent > approach then telling Rails what the sequence name is. I would look at > modifying this function to lookup the primary key default value from > the metadata tables - which would be something like nextval(sequence) > - parse out the sequence name and then use that as sequence_name in > the above function.That''s exactly what Class::DBI::Pg does in Perl. The query it uses is something like this: SELECT adsrc FROM ${catalog}pg_attrdef WHERE adrelid=(SELECT oid FROM ${catalog}pg_class WHERE relname=?) Then it uses this regex to pull out the name of the sequence. /^nextval\(''"?([^"'']+)"?''::text\)/ If you know you''re only dealing with newer versions of PostgreSQL, you can use the information schema instead: SELECT column_default FROM information_schema.columns WHERE table_name=? AND column_name=? -Dom
John Higgins wrote:>On 5/13/05, Sean T Allen <sean-5W9FBhQXBOtBDgjK7y7TUQ@public.gmane.org> wrote: > > >>Yeah I found a patch on the website that works beautifully. >> >> > >I''m assuming you are referring to #1273 - just one word of caution - >that patch may be a little dangerous in that it appears to modify code >that is non Postgresql specific (fixtures.rb) without any concern for >other adapters that use that shared code. This is fine if you are >running only on Postgresql but is not very nice if you ever switch >databases :) It also might have a difficult time in ever getting >pushed into the main trunk as well (at least I hope it would if it is >unfriendly to others). > >If on the other hand you don''t mind patching during upgrades and you >are a Postgresql only shop - congrats on getting back on track.... > >Yeah I saw that issue... I didnt patch the actual source... its in a file in lib that I load into the application that needs it... _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
John Higgins wrote:> On 5/13/05, Sean T Allen <sean-5W9FBhQXBOtBDgjK7y7TUQ@public.gmane.org> wrote: > >>how do i tell rails what the sequence name is? >> > > > The current adapter is unable to handle such circumstances. It > probably could be modified but would most likely not be the quickest > of fixes. As shown by the following code the last_insert_id function > doesn''t get anything but the table_name and column_name of the PK. > > lib/active_record/connection_adapters/postgresql_adapter.rb > > def last_insert_id(table, column = "id") > sequence_name = "#{table}_#{column || ''id''}_seq" > @connection.exec("SELECT currval(''#{sequence_name}'')")[0][0].to_i > end > > If you absolutely require this change and don''t mind a small speed hit > - the best suggestion I would have for you is to take a diffirent > approach then telling Rails what the sequence name is. I would look at > modifying this function to lookup the primary key default value from > the metadata tables - which would be something like nextval(sequence) > - parse out the sequence name and then use that as sequence_name in > the above function. > > Hope that makes some sense - it''s Friday :) > >What about schemas? About 6 months ago I ran into this issue with PHP and wanted to build a generic function for returning the last inserted. With Schemas, some fun stuff can happen. http://blog.planetargon.com/archives/29-PHP-pg_insert_id.html Just something to consider. def last_insert_id (schema = "public", table, column = "id") sequence_name = "#{schema}.#{table}_#{column || ''id''}_seq" ... end Even in this scenario, it''s not fool-proof. The query in the blog entry that I posted has worked in several environments where the sequence name isn''t standard (legacy systems). -Robby -- /*************************************** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | robby-/Lcn8Y7Ot69QmPsQ1CNsNQ@public.gmane.org * 503.351.4730 | blog.planetargon.com * PHP, Ruby, and PostgreSQL Development * http://www.robbyonrails.com/ ****************************************/