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:

1. how to handle this situation in Rails Active Record?
2. how to query this tables?
3. 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.

Answer 1

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.

Answer 2

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.