CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecordschema
Jacek Wysocki
Jacek Wysocki

Reputation: 1090

Rails Postgresql multiple schemas and the same table name

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

In each schema I have table basic

This tables have relations:

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.

Upvotes: 7

Views: 5880

Answers (3)

Serhii Potapov
Serhii Potapov

Reputation: 3970

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.

Upvotes: 2

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95532

[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.)

Upvotes: 1

Ben Simpson
Ben Simpson

Reputation: 4049

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.

Upvotes: 1

Related Questions