Rails Postgresql multiple schemas and the same table name

Question

I have two tables in two different schemas e.g. cases and events.

In each schema I have table basic

events.basic
cases.basic

This tables have relations:

events.basic has one cases.basic (cases.basic has many events.basic)

My attempts have failed:

file cases_basic.rb

class CasesBasic < ActiveRecord::Base
  set_table_name 'cases.basic'
  set_primary_key 'case_id'
  has_many :Events, :class_name => 'EventsBasic', :foreign_key => 'case_id'
end

file events_basic.rb

class EventsBasic < ActiveRecord::Base
  set_table_name 'events.basic'
  set_primary_key 'event_id'
  belongs_to :Case, :class_name => 'CasesBasic', :foreign_key => 'case_id'
end

Enviroment: Ruby 1.9.3, Rails 3.1.3, gem 'pg'

I Need answer for this questions:

how to handle this situation in Rails Active Record?
how to query this tables?
how to handle this situation in rake db:schema:dump

EDIT:

After changing belongs_to and has_many (like Catcall suggest) i have the same error

PGError: ERROR:  column basic.case_id does not exist
LINE 1: ...IN "cases"."basic" ON "cases"."basic"."case_id" = "events"."...
                                                             ^
: SELECT  "events"."basic".* FROM "events"."basic" INNER JOIN "cases"."basic" ON "cases"."basic"."case_id" = "events"."basic"."case_id" LIMIT 3

Rails generate bad SQL. I should be done using some aliases:

SELECT t1.* FROM "events"."basic" t1 INNER JOIN "cases"."basic" t2 ON t1."case_id" = t2."case_id" LIMIT 3

EDIT 2: Ok It was my f*** bug, i didn't add events.basic.case_id column and foreign key in my example database. It works!

Questions 1 AND 2 are working but we have question about rake db:schema:dump what about it? Rails generates models only for public schema.

I have so many tables and relations that i want to generate them.

Your :Events and :Case should probably be :events and :case but that probably won't fix it. — mu is too short
– mu is too short, Commented Dec 9, 2011 at 7:07
@muistooshort :Events and :Case looks like aliases and are not taken to SQL in this case — Jacek Wysocki
– Jacek Wysocki, Commented Dec 9, 2011 at 7:22
I'm a little confused about what the tables look like, what should the ON "cases"."basic"."case_id" = "events"."basic"."case_id" join condition look like? One of the tables doesn't have a case_id and that's a problem, how would you write that join condition if you were doing it by hand? — mu is too short
– mu is too short, Commented Dec 9, 2011 at 7:31
Try setting schema_search_path: public,events,cases in your databases.yaml, then try your rake db:schema:dump. — mu is too short
– mu is too short, Commented Dec 9, 2011 at 8:07
You get a schema.rb with everything in there but none of the table names include the PostgreSQL schema prefix, right? — mu is too short
– mu is too short, Commented Dec 9, 2011 at 17:26

Serhii Potapov · Accepted Answer · 2013-08-14 09:27:02Z

2

I would recommend using pg_power gem. It provides syntax for creating PostgreSQL schemas in migrations like this:

def change
  drop_schema 'demography'
  create_schema 'politics'
end

And also takes care about dumping schemas into schema.rb file correctly.

answered Aug 14, 2013 at 9:27

Serhii Potapov

4,0604 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mochatiger Over a year ago

Note: this gem is no longer needed, create_schema and drop_schema are part of Rails' PostgreSQL API. api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/…

Ben Simpson · Accepted Answer · 2011-12-08 10:30:54Z

1

Check out http://blog.jerodsanto.net/2011/07/building-multi-tenant-rails-apps-with-postgresql-schemas/

This describes how to configure a Rails application to use a Postgres database with multiple schemas. He likens the table lookup to the functionality of the Unix path, starting with specific locations, and falling back to general locations.

Once your schema paths are integrated, you can query these tables successfully. db:schema:dump will read the tables using the same schema precedence that your application prefers.

answered Dec 8, 2011 at 10:30

Ben Simpson

4,0591 gold badge20 silver badges10 bronze badges

1 Comment

Jacek Wysocki Over a year ago

Thanks for reply, but unfortunately it's not solving problem, author using here SET SEARCH_PATH TO schema1,schema2 approach which is great for simple database when you don't need relations between tables in different schemas, and it not solving multiple schema problem, you can't join tables with the same name. And i have 300 tables in my system, where many of them has same names.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-12-08 14:05:29Z

1

[Edit: after further reading, I don't think ActiveRecord supports multiple schemas well at all. But I could be wrong. I'll leave this answer here for the time being, although it's almost certainly wrong. (Conceptually it's right. But the people who built ActiveRecord probably didn't talk to any database people, because what could database people possible know?) It looks like IBM was working on this problem in 2008, but I don't see how that work ended.]

PostgreSQL doesn't have any trouble setting foreign key references to tables that have the same name in different schemas. Code like this

class CasesBasic < ActiveRecord::Base
  set_table_name 'cases.basic'
  set_primary_key 'case_id'
  has_many :Events, :class_name => 'EventsBasic', :foreign_key => 'case_id'
end

probably needs to be schema-qualified.

Now, it's not true that the table cases.basic "has many" events, is it? No, it "has many" events.basic. Carry that kind of change throughout your two classes, and let us know how that works. (No Rails here, or I'd test it for you.)

edited Dec 8, 2011 at 14:05

answered Dec 8, 2011 at 13:48

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

8 Comments

mu is too short Over a year ago

"But the people who built ActiveRecord probably didn't talk to any database people, because what could database people possible know?" DB people know about silly things like referential integrity (which Rails thinks is an application issue). Rails has a lot of good ideas in it, ActiveRecord isn't one of them.

mu is too short Over a year ago

And CHECK constraints, function indexes, triggers, and anything else that, say, MySQL3 didn't understand.

Ben Simpson Over a year ago

Does including/excluding the schemas you don't want to query from SEARCH_PATH not resolve the issue of table location within schemas? Also, can you fully qualify your table path by prefixing with the specific schema you want to query?

Mike Sherrill 'Cat Recall' Over a year ago

@BenSimpson: My understanding of the OP's situation is, no, search_path won't help, because each schema has tables with the same name. As in events.basic, cases.basic, other_things.basic. IMO, those aren't great table names, that's not really the point. My current understanding of Rails is that it doesn't support schema.table syntax. That's why I said my answer is almost certainly wrong.

Mike Sherrill 'Cat Recall' Over a year ago

@muistooshort: Domains (?). (Not a Rails expert.)

|

Collectives™ on Stack Overflow

Rails Postgresql multiple schemas and the same table name

3 Answers 3

1 Comment

1 Comment

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related