SQLite3/AR failing to increment PK on habtm table

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

On May 24, 11:48 am, Phrogz [email protected] 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.

On May 24, 11:48 am, Phrogz [email protected] 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)

On May 24, 4:04 pm, Phrogz [email protected] wrote:

=> nil

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.

On May 25, 11:29 am, Phrogz [email protected] wrote:

On May 24, 4:04 pm, Phrogz [email protected] wrote:

On May 24, 11:48 am, Phrogz [email protected] 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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs