在Rails应用程序中的查询运行时更改表名 [英] Changing table name at query run time in a Rails application

问题描述

我在Apache + mod_passenger上有一个胖的多租户Rails应用，可从PostgreSQL表中输出产品价格，如下所示：

I have a fat multi-tenant Rails app on Apache + mod_passenger that outputs product prices from a PostgreSQL table as follows:

Table "public.products"
Column | Type
id     | bigint
name   | character varying(100)
price  | numeric(8,2)

然后在products.rb内部有...

Then inside products.rb I have...

class Product < PostgresDatabase
     self.table_name = "products"

     # ... yadda yadda

end

我想要的是以一种非常特定的方式对 products表进行分区，以便最终为每个租户提供诸如products_TENANT-ID之类的东西（基本上是主视图） products表，但这又是另一回事了），并可以像这样查询：

What I want is to partition the "products" table in a very specific way so that I end up with something like products_TENANT-ID for each tenant (basically views of the main products table but that's another story) and be able to query like this:

Products.for_tenant(TENANT-ID).where(:name => "My product")......

我认为我可以创建方法：

I figure I can just create a method:

class Product < PostgresDatabase
     self.table_name = "products"

     # ... yadda yadda
     def for_tenant(tid)
          self.table_name = "products_" + tid.to_s
          self
     end
end

但是会产生什么样的影响考虑到流量很大（每秒数千个请求），应用程序上有这个吗？我有什么想念的吗？我应该尝试其他策略吗？

But what kind of impact could this have on the application considering there's lots of traffic (thousands of requests per second)? Is there something I am missing? Should I try a different strategy?

非常感谢您的任何反馈/想法！

Thank you very much for any feedback/thoughts!

推荐答案

方法

def self.for_tenant(tid)
  self.table_name = "products_" + tid.to_s
  self
end

很有意义，但是有一个副作用：它更改 Product 类的表名。例如，以后在同一请求中使用此类时，例如：

makes sense, however, it has a side effect: it changes table name for Product class. When this class is used later in the same request, for example, in this way:

Product.where(name: "My other product") ...

表名称将不是产品

为了避免这种歧义并保持代码的清洁，您可以使用以前的 for_tenant 方法更改的值。使用另一种策略：

To avoid this ambiguity and keep code clean you can use another strategy:

1）定义一个模块，该模块包含租户分区的所有工作逻辑：

1) Define a module which holds all logic of work with tenant partitions:

# app/models/concerns/partitionable.rb

module Partitionable
  def self.included(base)
    base.class_eval do
      def self.tenant_model(tid)
        partition_suffix = "_#{tid}"

        table = "#{table_name}#{partition_suffix}"

        exists = connection.select_one("SELECT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = '#{table}')")
        unless exists['exists'] == 't' || exists['exists'] == true  # different versions of pg gem give different answers
          return self # returning original model class
        end

        class_name = "#{name}#{partition_suffix}"

        model_class = Class.new(self)

        model_class.define_singleton_method(:table_name) do
          table
        end

        model_class.define_singleton_method(:name) do
          class_name
        end

        model_class
      end
    end
  end
end

2）在您的模型类中包括此模块：

2) Include this module in your model class:

class Product < PostgresDatabase
  include Partitionable

  ...
end

3）按预期方式使用它：

3) Use it the same way as you intended:

Product.tenant_model(TENANT_ID).where(name: "My product")...

那里发生了什么：

方法 tenant_model（TENANT_ID）为ID为 TENANT_ID 的租户创建另一个模型类。此类的名称为 Product_< TENANT_ID> ，可与表 products_< TENANT_ID> 一起使用，并继承产品类。因此它可以像常规模型一样使用。而类 Product 本身仍然保持不变：其 table_name 仍然是 products 。

Method tenant_model(TENANT_ID) creates another model class for the tenant with ID TENANT_ID. This class has name Product_<TENANT_ID>, works with table products_<TENANT_ID> and inherits all methods of Product class. So it could be used like a regular model. And class Product itself remains untouched: its table_name is still products.

这篇关于在Rails应用程序中的查询运行时更改表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！