Postgres?

[tilthouse]

Anyone using postgres with this plug in? At first glance it appears
that having no ID column in user_roles causes has_role to break:

alex.has_role 'admin'
ActiveRecord::StatementInvalid: PGError: ERROR: column "id" does not
exist
LINE 1: ....915339', 1, 2, '2009-08-19 16:50:16.915339') RETURNING
"id"
^
: INSERT INTO "roles_users" ("updated_at", "role_id", "user_id",
"created_at") VALUES('2009-08-19 16:50:16.915339', 1, 2, '2009-08-19
16:50:16.915339') RETURNING "id"
from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.3/lib/
active_record/connection_adapters/abstract_adapter.rb:212:in `log'
from /Library/Ruby/Gems/1.8/gems/activerecord-2.3.3/lib/
active_record/connection_adapters/postgresql_adapter.rb:538:in
`execute'

Is perhaps the RETURNING "id" just something that always happens with
the Postgres adapter?

If so, is there any harm in having an id primary key in UserRoles?

AB

[Rick]

I'm not sure you've quite identified the problem. Here's an example
from my system, using restful-authentication and simple_access_control
plugins. The pattern here is that the models User and Role habtm each
other using the roles_users table.

Note the use of the :id => false option in the migration file. This
option causes the created table to have only role_id:integer and
user_id:integer fields - no id:integer (PK) field is created in this
table.

/auth_auth 598 > script/about
About your application's environment
Ruby version 1.8.7 (powerpc-darwin9.7.0)
RubyGems version 1.3.4
Rack version 1.0
Rails version 2.3.3
Active Record version 2.3.3
Action Pack version 2.3.3
Active Resource version 2.3.3
Action Mailer version 2.3.3
Active Support version 2.3.3
Application root /Users/rick/auth_auth
Environment development
Database adapter postgresql
Database schema version 20090726145142

/auth_auth 599 > /opt/local/pgsql/bin/postgres --version
postgres (PostgreSQL) 8.3.7

/auth_auth 600 > gem list pg
*** LOCAL GEMS ***
pg (0.8.0)

/auth_auth 607 > cat db/migrate/20090726145142_role_users.rb
class CreateRoleUsers < ActiveRecord::Migration
def self.up
create_table :roles_users, :id => false do |t|
t.integer :role_id
t.integer :user_id
end
admin_user = User.create(:login => 'admin',
:email => 'bultac...@gmail.com',
:password =>
'XXXXXXX', :password_confirmation => 'XXXXXXX')
admin_user.state = 'active'
admin_role = Role.find_by_title('admin')
admin_user.roles << admin_role
admin_user.save
end

def self.down
User.find_by_login('admin').destroy
drop_table :roles_users
end
end

/auth_auth 615 > rake db:migrate VERSION=20090726145142
(in /Users/rick/auth_auth)
== CreateRoleUsers: migrating
================================================
-- create_table(:roles_users, {:id=>false})
-> 0.0111s
== CreateRoleUsers: migrated (2.6230s)
=======================================

/auth_auth 616 > tail -38 log/development.log
SQL (0.6ms) SET client_min_messages TO 'panic'
SQL (0.4ms) SET client_min_messages TO 'notice'
SQL (1.9ms) SELECT version FROM schema_migrations
SQL (0.9ms) SELECT version FROM schema_migrations
Migrating to CreatePages (20090726144936)
Migrating to CreateUsers (20090726145043)
Migrating to CreateRoles (20090726145133)
Migrating to CreateRoleUsers (20090726145142)
SQL (0.5ms) BEGIN
SQL (8.3ms) CREATE TABLE "roles_users" ("role_id" integer,
"user_id" integer)
User Load (3.9ms) SELECT "users".id FROM "users" WHERE
("users"."login" = E'admin') LIMIT 1
User Load (1.2ms) SELECT "users".id FROM "users" WHERE
("users"."email" = E'bultac...@gmail.com') LIMIT 1
SQL (105.9ms) INSERT INTO "users" ("name", "salt", "created_at",
"activated_at", "crypted_password", "remember_token_expires_at",
"updated_at", "deleted_at", "activation_code", "remember_token",
"login", "email", "state") VALUES(E'',
E'b34e23378ba251c78ad17f8f589879334a65f0cc', '2009-08-24
11:18:26.518094', NULL, E'aaa44154dbba7fd199b3b2a2cb8f4cabc0c316bf',
NULL, '2009-08-24 11:18:26.518094', NULL, NULL, NULL, E'admin',
E'bultac...@gmail.com', E'passive') RETURNING "id"
Sent mail to bultac...@gmail.com

Date: Mon, 24 Aug 2009 07:18:27 -0400
From: ADMINEMAIL
To: bultac...@gmail.com
Subject: [engine.local:3000] Please activate your new account
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8

Your account has been created.

Username: admin
Password: ******

Visit this url to activate your account:

http://engine.local:3000/activate/
Role Load (1.9ms) SELECT * FROM "roles" WHERE ("roles"."title" =
E'admin') LIMIT 1
SQL (1.8ms) INSERT INTO "roles_users" ("role_id", "user_id")
VALUES (1, 1)
User Load (2.1ms) SELECT "users".id FROM "users" WHERE
("users"."login" = E'admin' AND "users".id <> 1) LIMIT 1
User Load (1.4ms) SELECT "users".id FROM "users" WHERE
("users"."email" = E'bultac...@gmail.com' AND "users".id <> 1)
LIMIT 1
User Update (2.2ms) UPDATE "users" SET "updated_at" = '2009-08-24
11:18:29.027456', "state" = E'active' WHERE "id" = 1
SQL (1.0ms) INSERT INTO schema_migrations (version) VALUES
('20090726145142')
SQL (6.4ms) COMMIT
SQL (1.0ms) SELECT version FROM schema_migrations

/auth_auth 616 > script/console
Loading development environment (Rails 2.3.3)
>> user = User.find_by_login('admin')
=> #<User id: 1, login: "admin", name: "", email:
"bultac...@gmail.com", crypted_password:
"aaa44154dbba7fd199b3b2a2cb8f4cabc0c316bf", salt:
"b34e23378ba251c78ad17f8f589879334a65f0cc", created_at: "2009-08-24
11:18:26", updated_at: "2009-08-24 11:18:29", remember_token: nil,
remember_token_expires_at: nil, activation_code: nil, activated_at:
nil, state: "active", deleted_at: nil>
>> user.roles.find_by_title('admin')
=> #<Role id: 1, title: "admin">
>> exit


So, you can see from this example that a normal join table without
an :id (PK) field does, in fact, work with postgresql.

HTH