Rails Postgresql多个模式和相同的表名

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)

events.basic有一个case.basic(cases.basic有很多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'

环境: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?

如何在Rails Active Record中处理这种情况?

how to query this tables?

如何查询这个表?

how to handle this situation in rake db:schema:dump

如何在rake db:schema:dump中处理这种情况

EDIT:

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

更改belongs_to和has_many后(如Catcall建议)我有同样的错误

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:

Rails生成错误的SQL。我应该使用一些别名:

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

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!

编辑2:确定这是我的f ***错误,我没有在我的示例数据库中添加events.basic.case_id列和外键。有用!

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.

问题1和2正在运行,但我们对rake db:schema:dump有什么疑问? Rails仅为公共模式生成模型。

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

我有很多表和关系,我想生成它们。

3 个解决方案

#1

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

查看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.

这描述了如何配置Rails应用程序以使用具有多个模式的Postgres数据库。他将表查找比作Unix路径的功能,从特定位置开始,然后回退到一般位置。

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.

集成架构路径后,您可以成功查询这些表。 db:schema:dump将使用应用程序喜欢的相同模式优先级读取表。

#2

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

[编辑:经过进一步阅读,我认为ActiveRecord根本不支持多种模式。但我可能是错的。我暂时将这个答案留在这里,虽然这几乎肯定是错的。 (从概念上来说是正确的。但构建ActiveRecord的人可能没有与任何数据库人员交谈,因为数据库人员可能知道什么?)看起来IBM在2008年正在研究这个问题,但我不知道怎么回事工作结束了。]

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

PostgreSQL在为不同模式中具有相同名称的表设置外键引用时没有任何问题。像这样的代码

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

现在,表case.basic“有很多”事件并不是真的,是吗?不,它“有很多”事件。基本。在整个两个班级中进行这种改变,让我们知道它是如何工作的。 (这里没有Rails,或者我会为你测试它。)

#3

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

我建议使用pg_power gem。它提供了在迁移中创建PostgreSQL模式的语法,如下所示:

def change
  drop_schema 'demography'
  create_schema 'politics'
end

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

并且还要正确地将模式转储到schema.rb文件中。

#1