Rails - Dec 2006 - Strange HABTM behavior against SQL Server (AR 1.14.4)

All,

Win XP
SQL Server 2000
Rails 1.1.6 (AR 1.14.4)

I have two objects (Job and Warning) in a HABTM relationship.  Below are
the pertinent sections of the model files and the schema for the join
table.

job.rb:
class Job < ActiveRecord::Base
  has_and_belongs_to_many :warnings, :foreign_key =>
''JobReferenceNumber''
  set_table_name  :JobData
  set_primary_key :JobReferenceNumber

warning.rb:
class Warning < ActiveRecord::Base
  has_and_belongs_to_many :jobs, :association_foreign_key =>
''JobReferenceNumber''

jobs_warnings table migration:
create_table :jobs_warnings do |t|
  t.column :JobReferenceNumber, :integer, :null => false
  t.column :warning_id, :integer, :null => false
end

What I find in both my app. and ruby script/console is the following:

If I run the following three statements against a saved Job object j
with JobReferenceNumber 550, and an _empty_ jobs_warnings table:

j.warnings << Warning.find(3)
j.warnings << Warning.find(2)
j.warnings << Warning.find(1)

I end up seeing rows in the jobs_warnings table with

id: 3
JobReferenceNumber: 550
warning_id: 3

id: 2
JobReferenceNumber: 550
warning_id: 2

id: 1
JobReferenceNumber: 550
warning_id: 1

This is wrong.  The id value is equal to the warning_id value.  The id
columns in jobs_warnings should start at 1 since id is an identity
column.  And, since there are three distinct warnings, the 4th insert
fails with a duplicate key failure.

Here is a piece of the development log from running my app:
SQL (0.080000)   SET IDENTITY_INSERT jobs_warnings ON
SQL (0.080000)   SELECT @@IDENTITY AS Ident
SQL (0.080000)   INSERT INTO jobs_warnings ([JobReferenceNumber], [id],
[warning_id]) VALUES (551, 3, 3)

I believe the id from the warnings table is being selected and then used
as the id value for the join table.  Is this a bug in the SQL Server
driver when using HABTM relationships with non-standard table names?

Does anyone know of any problems with HABTM against SQL Server that
would explain these erroneous join table INSERTS?

Thanks,
Wes

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 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
-~----------~----~----~----~------~----~------~--~---

Wes Gamble wrote:
> Does anyone know of any problems with HABTM against SQL Server that
> would explain these erroneous join table INSERTS?
Yup...I ran into the exact same thing a while back.  It''s a
"Feature" or
so I was told.  I was also told I was wrong in my approach to solving my 
issue.  I didn''t agree so I removed the ID field from the join table 
(You don''t really need it.) and my stuff works fine.  Your mileage may 
vary.

Enjoy!

MudDawg

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 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
-~----------~----~----~----~------~----~------~--~---

Steve Keener wrote:
> Yup...I ran into the exact same thing a while back.  It''s a
"Feature" or
> so I was told.  I was also told I was wrong in my approach to solving my 
> issue.  I didn''t agree so I removed the ID field from the join
table
> (You don''t really need it.) and my stuff works fine.  Your mileage
may
> vary.
> 
Steve,

Thanks for this.  Something strange - in the case I outlined above, the 
"first" of the two joined tables (table "JobData" for class
"job") is a
legacy table, and the second follows the Rails convention.  I have 
another pair of tables where the "first" table uses the
"normal" Rails
convention and the second one is the legacy table, and that works just 
fine.

I have also noticed that for some reason, you need to use strings 
instead of symbols for the values of :foreign_key and 
:association_foreign_key.  I wonder if that''s SQL Server specific as 
well.

Glad to know I''m not crazy.

Wes

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 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
-~----------~----~----~----~------~----~------~--~---

I decided to go the has_many :though route, so I created a join model 
named JobsWarning and then set up the relationships like so:

Job (job.rb):
  has_many :jobs_warnings, :foreign_key => :JobReferenceNumber, 
:dependent => :delete_all
  has_many :warnings, :through => :jobs_warnings

Warning (warning.rb):
  has_many :jobs_warnings
  has_many :jobs, :through => :jobs_warnings

JobsWarning (jobs_warning.rb):
  belongs_to :job, :foreign_key => :JobReferenceNumber
  belongs_to :warning

Given that my job object is saved,  when I do job.warnings << 
Warning.find(x), I expect that records will automatically be saved (like 
they are for HABTM).  But these join records are not automatically 
saved.

Is this correct?

Thanks,
Wes

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 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
-~----------~----~----~----~------~----~------~--~---

Well, this is more explicit, but it seems to work (code is in job.rb):

self.jobs_warnings.clear
self.jobs_warnings.create(:JobReferenceNumber => 
self.JobReferenceNumber,
                          :warning_id => 
Warning.find_by_title("Javascript").id)

whereas self.jobs << Warning.find(x) simply adds the warning object to 
the collection in memory.

Wes

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
 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
-~----------~----~----~----~------~----~------~--~---