Rails - May 2006 - Concatenated key

Hi all,

I started playing a couple days ago with Rails and it find it very 
interesting.
But I''m a bit stuck with making it work with a concatenated primary
key.

I want to use my own names for the DB fields and I''m using SQL Server 
2005.

My tables look roughly like this:

CREATE TABLE USERS (
	USR_ID VARCHAR(25) NOT NULL PRIMARY KEY,
	USR_PASS VARCHAR(25)
)

CREATE TABLE SECTOR (
	SECT_ID TINYINT NOT NULL PRIMARY KEY,
	SECT_NAME VARCHAR(25)
)

CREATE TABLE PERMISSION (
	FK_USR_ID VARCHAR(25) NOT NULL,
	FK_SECT_ID TINYINT NOT NULL
)

ALTER TABLE PERMISSION
ADD CONSTRAINT PK_PERMISSION
PRIMARY KEY (FK_USR_ID,FK_SECT_ID)

And my models look like this:
class User < ActiveRecord::Base
	set_primary_key "USR_ID"

	def self.table_name() "USERS" end
	has_many :permission
end

class Sector < ActiveRecord::Base
	set_primary_key "SECT_ID"

	def self.table_name() "SECTOR" end
	has_many :permission
end

class Permission < ActiveRecord::Base
	set_primary_key "FK_USR_ID,FK_SECT_ID"

	def self.table_name() "PERMISSION" end
	belongs_to :User
	belongs_to :Sector
end

INSERTing permission data is pretty easy:
@perm = Permission.new("FK_USR_ID" => @user.id,
"FK_SECT_ID" =>
@sector.id)
@perm.save

However, SELECTing a permission doesn''t work:
---
@perm = Permission.find("FK_USR_ID" => @user.id,
"FK_SECT_ID" =>
@sector.id)
gives:
ArgumentError (Unknown key(s): FK_USR_ID, FK_SECT_ID):
   
c:/ruby/lib/ruby/gems/1.8/gems/activesupport-1.3.1/lib/active_support/core_ext/hash/keys.rb:48:in
`assert_valid_keys''
   
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:1323:in
`validate_find_options''
   
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib/active_record/base.rb:376:in
`find''
---
@perm = Permission.find(@user.id,@sector.id)
gives:
DBI::DatabaseError: Execute
 OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
 Incorrect syntax at '',''.
 HRESULT error code:0x80020009
 An exception occured.: SELECT * FROM PERMISSION WHERE 
(PERMISSION.FK_USR_ID,FK_SECT_ID IN (''gfk'',3)) 
---

Any idea on how to do it?

Thanks a lot,
GFK''s

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

On May 18, 2006, at 11:36 AM, Guillaume Filion wrote:
> I started playing a couple days ago with Rails and it find it very
> interesting.
> But I''m a bit stuck with making it work with a concatenated
primary
> key.
Hello! and welcome to Rails.

> I want to use my own names for the DB fields and I''m using SQL
Server
> 2005.
>
> My tables look roughly like this:
>
> CREATE TABLE USERS (
> 	USR_ID VARCHAR(25) NOT NULL PRIMARY KEY,
> 	USR_PASS VARCHAR(25)
> )
>
> CREATE TABLE SECTOR (
> 	SECT_ID TINYINT NOT NULL PRIMARY KEY,
> 	SECT_NAME VARCHAR(25)
> )
>
> CREATE TABLE PERMISSION (
> 	FK_USR_ID VARCHAR(25) NOT NULL,
> 	FK_SECT_ID TINYINT NOT NULL
> )
>
> ALTER TABLE PERMISSION
> ADD CONSTRAINT PK_PERMISSION
> PRIMARY KEY (FK_USR_ID,FK_SECT_ID)
>
> And my models look like this:
> class User < ActiveRecord::Base
> 	set_primary_key "USR_ID"
>
> 	def self.table_name() "USERS" end
> 	has_many :permission
> end
class User < ActiveRecord::Base
   self.table_name  = ''USERS''
   self.primary_key = ''USR_ID''

   has_and_belongs_to_many :sectors, :join_table =>  
''permission'', :foreign_key =>
''FK_USR_ID'', :association_foreign_key
=> ''FK_SECT_ID''
end

> class Sector < ActiveRecord::Base
> 	set_primary_key "SECT_ID"
>
> 	def self.table_name() "SECTOR" end
> 	has_many :permission
> end
class Sector < ActiveRecord::Base
   self.table_name  = ''SECTOR''
   self.primary_key = ''SECT_ID''

   has_and_belongs_to_many :users, :join_table =>  
''permission'', :foreign_key =>
''FK_SECT_ID'', :association_foreign_key
=> ''FK_USR_ID''
end

> class Permission < ActiveRecord::Base
> 	set_primary_key "FK_USR_ID,FK_SECT_ID"
>
> 	def self.table_name() "PERMISSION" end
> 	belongs_to :User
> 	belongs_to :Sector
> end
Compound primary keys are not supported. Add a surrogate key to the  
permission table if you want to represent it with an Active Record  
class. It will also allow you to use has_many :through, which is more  
flexible & expressive than has_and_belongs_to_many.

Best,
jeremy

Jeremy Kemper wrote:
> Compound primary keys are not supported. Add a surrogate key to the
> permission table if you want to represent it with an Active Record
> class. It will also allow you to use has_many :through, which is more
> flexible & expressive than has_and_belongs_to_many.
Thanks a lot Jeremy, it works now with a surrogate key.

It''s not as elegant as I would have hoped (I have to use find_by_sql 
instead of find) but it''s working well.

Thanks again,
GFK''s

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

On May 18, 2006, at 3:45 PM, Guilllaume Filion wrote:
> Jeremy Kemper wrote:
>> Compound primary keys are not supported. Add a surrogate key to the
>> permission table if you want to represent it with an Active Record
>> class. It will also allow you to use has_many :through, which is more
>> flexible & expressive than has_and_belongs_to_many.
>
> Thanks a lot Jeremy, it works now with a surrogate key.
>
> It''s not as elegant as I would have hoped (I have to use
find_by_sql
> instead of find) but it''s working well.
Ah, too bad - tou can almost always use find. Reply with the updated  
code and perhaps we can elegant it up :)

jeremy

I''m aware this isn''t always possible, but if you can change
your table
names and stuff, rails will be able to work things out for you. Your
USERS table should be all lower case (I''m not sure if that''s
required... anyone know for definite? but it is convention), and it''s
primary key should be ''id''. Same with your SECTORS table. If
you don''t
want to specify the table name for the join table, it should be called
sectors_users, but you''ll probably want to leave that as permission,
because it makes more sense. The foreign keys should then be
''user_id''
and ''sector_id''. I''ve been using the word should, but
of course, you
can do it how you want... it''s just that doing it this way requires
less code.

If you follow these conventions, your model can be as simple as:

class User < ActiveRecord::Base
       has_and_belongs_to_many :sectors, :join_table =>
''permission''
end

class Sector < ActiveRecord::Base
      has_and_belongs_to_many :users, join_table =>
''permission''
end

-Nathan

On 19/05/06, Jeremy Kemper <jeremy@bitsweat.net>
wrote:
>
> On May 18, 2006, at 3:45 PM, Guilllaume Filion wrote:
>
> > Jeremy Kemper wrote:
> >> Compound primary keys are not supported. Add a surrogate key to
the
> >> permission table if you want to represent it with an Active Record
> >> class. It will also allow you to use has_many :through, which is
more
> >> flexible & expressive than has_and_belongs_to_many.
> >
> > Thanks a lot Jeremy, it works now with a surrogate key.
> >
> > It''s not as elegant as I would have hoped (I have to use
find_by_sql
> > instead of find) but it''s working well.
>
> Ah, too bad - tou can almost always use find. Reply with the updated
> code and perhaps we can elegant it up :)
>
> jeremy
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Jeremy Kemper wrote:
> On May 18, 2006, at 3:45 PM, Guilllaume Filion wrote:
>> It''s not as elegant as I would have hoped (I have to use
find_by_sql
>> instead of find) but it''s working well.
> 
> Ah, too bad - tou can almost always use find. Reply with the updated
> code and perhaps we can elegant it up :)
Thanks for the offer. Let''s give it a try.

I''ve been playing in ruby/rails for 5 days, so I''m pretty sure
that you
can make it better... :-)

Let me explain quickly how it works:
list show a list of users (duh), clicking on a user gives the details 
page.
The details pages has a checkbox for each sector, checked if the user 
has access to that sector. To do this I''m using the following piece of 
(ugly) code:

<%= start_form_tag :action => ''modify'', :id =>
@User.id %>
<p><% @Sectors.each do |Sector| %>
<input type="checkbox" name="<%= Sector.SECT_NAME
%>"<%
@nb = Permission.count_by_sql("SELECT COUNT(*) FROM PERMISSION WHERE 
FK_USR_ID=''" + @User.id.to_s + "'' AND
FK_SECT_ID=" + Sector.id.to_s)
%><%= " checked" if (@nb == 1)
%>><label
for="<%=Sector.SECT_NAME%>"><%=Sector.SECT_NAME%></label>
<% end %></p>
<p><%= submit_tag "Modify" %></p>
<%= end_form_tag %>

The modify code is in the controller (and quite ugly too):

class UserController < ApplicationController
  after_filter :set_charset
  layout "standard-layout"

  def set_charset
    @headers["Content-Type"] ||= "text/html;
charset=iso-8859-1"
  end

  def list
    @Users = User.find_all
  end

  def details
    @User = User.find(@params["id"])
    @Sectors = Sector.find_all
  end

  def modify
    @User = User.find(@params["id"])
    @Sectors = Sector.find_all
    @Sectors.each do |Sector|
      if (@params[Sector.SECT_NAME]) then
        @nb = Permission.count_by_sql("SELECT COUNT(*) FROM PERMISSION 
WHERE FK_USR_ID=''" + @User.id.to_s + "'' AND
FK_SECT_ID=" +
Sector.id.to_s)
        if (@nb == 0) then
          @perm = Permission.new("FK_USR_ID" => @User.id,
"FK_SECT_ID"
=> Sector.id)
          @perm.save
        end
      else
        @nb = Permission.count_by_sql("SELECT COUNT(*) FROM PERMISSION 
WHERE FK_USR_ID=''" + @User.id.to_s + "'' AND
FK_SECT_ID=" +
Sector.id.to_s)
        if (@nb == 1) then
          Permission.find_by_sql("SELECT * FROM PERMISSION WHERE 
FK_USR_ID=''" + @User.id.to_s + "'' AND
FK_SECT_ID=" +
Sector.id.to_s).first.destroy
        end
      end
    end
    redirect_to :action => ''details'', :id => @User
  end
end

Thanks a lot!
GFK''s

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

unknown wrote:
> I''m aware this isn''t always possible, but if you can
change your table
> names and stuff, rails will be able to work things out for you. [...] 
> I''ve been using the word should, but of course, you
> can do it how you want... it''s just that doing it this way
requires
> less code.
> 
> If you follow these conventions, your model can be as simple as:
> 
> class User < ActiveRecord::Base
>        has_and_belongs_to_many :sectors, :join_table =>
''permission''
> end
> 
> class Sector < ActiveRecord::Base
>       has_and_belongs_to_many :users, join_table =>
''permission''
> end
I don''t know. I''m usually quite pedantic with my database
design and I
must admit that I don''t find the DB design proposed by rails especially
interesting. But you make a very good point by showing how simple the 
model would be. And since I''m learning rails I should be more open 
minded about its way of doing things.

Before I change my whole DB design, could you provide me with an example 
on how to create and find a permission using this new model?

Thanks a lot,
GFK''s

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

On May 19, 2006, at 8:24 AM, Guillaume Filion wrote:
> unknown wrote:
>> I''m aware this isn''t always possible, but if you can
change your
>> table
>> names and stuff, rails will be able to work things out for you. [...]
>> I''ve been using the word should, but of course, you
>> can do it how you want... it''s just that doing it this way
requires
>> less code.
>>
>> If you follow these conventions, your model can be as simple as:
>>
>> class User < ActiveRecord::Base
>>        has_and_belongs_to_many :sectors, :join_table =>
''permission''
>> end
>>
>> class Sector < ActiveRecord::Base
>>       has_and_belongs_to_many :users, join_table =>
''permission''
>> end
>
> I don''t know. I''m usually quite pedantic with my database
design and I
> must admit that I don''t find the DB design proposed by rails  
> especially
> interesting. But you make a very good point by showing how simple the
> model would be. And since I''m learning rails I should be more open
> minded about its way of doing things.
>
> Before I change my whole DB design, could you provide me with an  
> example
> on how to create and find a permission using this new model?
user = User.find(:first)
sector = Sector.find(:first)

# Permit
user.sectors << sector

# Deny
user.sectors.delete(sector)

# Permitted?
user.sectors.include?(sector)


I understand how the database conventions can feel abrasive, but the  
benefits are delightful and manyfold.

jeremy

Jeremy Kemper wrote:
> # Permit
> user.sectors << sector
> 
> # Deny
> user.sectors.delete(sector)
> 
> # Permitted?
> user.sectors.include?(sector)
Wow! Impressive. It''s certainly worth changing the field and table 
names.

The code is much better looking now:
def modify
	@user = User.find(@params["id"])
	Sector.find_all.each do |sector|
		if (@params[sector.SECT_NAME]) then
			@user.sectors << sector if (not @user.sectors.include?(sector))
		else
			@user.sectors.delete(sector) if (@user.sectors.include?(sector))
		end
	end
	redirect_to :action => ''details'', :id => @user
end

Many thanks for your help and have a good week-end,
GFK''s

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