SUMMARY
Rails is generating SQL for a habtm insert that isn''t properly
incrementing the primary key on my habtm table.
DETAILS
I have a Story model like this:
class Story < ActiveRecord::Base
has_and_belongs_to_many :products
end
with a habtm table created named "products_stories". Here''s
the schema
reported for that table by the db (sqlite3):
sqlite> .schema products_stories
CREATE TABLE products_stories ("id" INTEGER PRIMARY KEY NOT NULL,
"product_id" integer NOT NULL, "story_id" integer NOT NULL);
In one of my controllers I create and update lots of stories at once.
The code looks like this:
SloppyCSV.parse( params[''csv''], :headers=>true ){
|story_row|
# ...create a story instance here and populate it
unless story.save
errors << story.errors
end
story.product_ids = story_row[ ''product_ids'' ] &&
story_row[ ''product_ids'' ].split(''|'').map{
|s| s.to_i }
story.save
}
The first story it hits that has a product id properly inserts a row
in the habtm join table:
INSERT INTO products_stories ("product_id", "story_id",
"id") VALUES
(1, 10, 1)
However, when it hits the next story, however, it tries to insert a
row with a conflicting id in the table:
INSERT INTO products_stories ("product_id", "story_id",
"id") VALUES
(1, 11, 1)
Why is rails/sqlite3 not properly incrementing the id?
ENVIRONMENT
This is on Mac OS X 10.4.9 on an Intel-based MacBook Pro, with a self-
compiled install of Ruby 1.8.6, self-compiled version of SQLite3, and
gem installs of rails and sqlite3-ruby.
Slim2:/ phrogz$ uname -a
Darwin Slim2.local 8.9.1 Darwin Kernel Version 8.9.1: Thu Feb 22
20:55:00 PST 2007; root:xnu-792.18.15~1/RELEASE_I386 i386 i386
Slim2:/ phrogz$ which ruby
/usr/local/bin/ruby
Slim2:/ phrogz$ ruby -v
ruby 1.8.6 (2007-03-13 patchlevel 0) [i686-darwin8.9.1]
Slim2:/ phrogz$ rails -v
Rails 1.2.3
Slim2:/ phrogz$ sqlite3 -version
3.3.17
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
On May 24, 11:48 am, Phrogz <g...-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org> wrote:> SUMMARY > Rails is generating SQL for a habtm insert that isn''t properly > incrementing the primary key on my habtm table.Forgot to mention - also have installed SWIG before building the sqlite3-ruby gem. This problem also rears its head on my Windows box, so it''s not that funky SWIG thing. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On May 24, 11:48 am, Phrogz <g...-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org> wrote:> SUMMARY > Rails is generating SQL for a habtm insert that isn''t properly > incrementing the primary key on my habtm table.Here''s a simpler example than the original post: Slim2:~/Desktop/StoryWeave phrogz$ script/console Loading development environment.>> s1,s2,*_ = Story.find(:all); nil=> nil>> s1.product_ids,s2.product_ids = [],[]=> [[], []]>> Story.connection.execute( ''select * from products_stories'' )=> []>> s1.product_ids = [1]=> [1]>> Story.connection.execute( ''select * from products_stories'' )=> [{0=>"1", 1=>"1", "product_id"=>"1", 2=>"1", "story_id"=>"1", "id"=>"1"}]>> s2.product_ids = [1]ActiveRecord::StatementInvalid: SQLite3::SQLException: SQL logic error or missing database: INSERT INTO products_stories ("product_id", "story_id", "id") VALUES (1, 2, 1) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On May 24, 4:04 pm, Phrogz <g...-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org> wrote:> On May 24, 11:48 am, Phrogz <g...-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org> wrote: > > > SUMMARY > > Rails is generating SQL for a habtm insert that isn''t properly > > incrementing the primary key on my habtm table. > > Here''s a simpler example than the original post: > > Slim2:~/Desktop/StoryWeave phrogz$ script/console > Loading development environment.>> s1,s2,*_ = Story.find(:all); nil > => nil > >> s1.product_ids,s2.product_ids = [],[] > => [[], []] > >> Story.connection.execute( ''select * from products_stories'' ) > => [] > >> s1.product_ids = [1] > => [1] > >> Story.connection.execute( ''select * from products_stories'' ) > > => [{0=>"1", 1=>"1", "product_id"=>"1", 2=>"1", "story_id"=>"1", > "id"=>"1"}]>> s2.product_ids = [1] > > ActiveRecord::StatementInvalid: SQLite3::SQLException: SQL logic error > or missing database: INSERT INTO products_stories ("product_id", > "story_id", "id") VALUES (1, 2, 1)Sorry for the bump, but...can anyone shed any light on this? I''m about to have to rewrite everything not to use habtm because I can''t get the join table to insert properly. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On May 25, 11:29 am, Phrogz <g...-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org> wrote:> On May 24, 4:04 pm, Phrogz <g...-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org> wrote: > > On May 24, 11:48 am, Phrogz <g...-XtLdkLkwz3ZWk0Htik3J/w@public.gmane.org> wrote: > > > > SUMMARY > > > Rails is generating SQL for a habtm insert that isn''t properly > > > incrementing the primary key on my habtm table. > Sorry for the bump, but...can anyone shed any light on this? I''m about > to have to rewrite everything not to use habtm because I can''t get the > join table to insert properly.My apologies for the noise. The problem is simply that I should not have an ID column in the habtm tables involved. Removing this column fixed it all. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---