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.

Ultimo

Your :Events and :Case should probably be :events and :case but that probably won't fix it. – Negligible

@muistooshort :Events and :Case looks like aliases and are not taken to SQL in this case – Ultimo

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? – Negligible

Try setting schema_search_path: public,events,cases in your databases.yaml, then try your rake db:schema:dump. – Negligible

@muistooshort it generates schema.rb but content of is some kind of massacre :) – Ultimo

You get a schema.rb with everything in there but none of the table names include the PostgreSQL schema prefix, right? – Negligible

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.

Confiscatory

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

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.

Wait

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. – Ultimo

Answer 3

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

Histogenesis

"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. – Negligible

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

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? – Wait

@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. – Inchmeal

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

Domains? No, I don't think so. Rails really wants to treat the database as a dumb data store so we get to see round two of all the ugly PHP/MySQL style abuses (look at serialize if you feel like swearing a bit). Of course, I'm a natural born heretic... – Negligible

@Catcall thanks for idea, but unfortunately it didn't help, i've attached Error from postgresql to my question, error is the same as before changes. – Ultimo

@muistooshort You're right, rails like many PHP frameworks came from MySQL background, and unfortunately Postgresql is treated like simple SQLite database – Ultimo