Fully qualified table names and models

[shapiro2]

There is a need for RMRE to be able to create tables and models that reside in multiple schemas (eg postgres) or multiple databases (eg Sybase). The underlying database allows joins between such tables and the ability to get models that can describe all the tables would be a nice feature.

Postgres tables have the full name as "schema.tablename"
Sybase tables have the full name as "db.owner.tablename"

For example, in Postgres, we could have two schemas, x and y, with the following tables.
x.resources
y.jobs

The models generated should have the fully qualified table names in them, and the model class names have the schema name as part of it

So the resultant model files might be

x_resources.rb
class X_Resource < ActiveRecord::Base
self.table_name = 'x.resources'
has_many :jobs, :class_name => 'Y_Job'
end

y_jobs.rb
class Y_Job < ActiveRecord::Base
self.table_name = 'y.jobs'
belongs_to :resource, :class_name => 'X_Resource', :foreign_key => :resource_id
end

This becomes especially important when the schemas contain tables with the same name in both schemas. Moreover, while it is possible to hand code the models (as I did in the above models), when the schemas contains hundreds of tables with complex relationships (that may change over time), hand coding and maintaining the models is something to be avoided.

Database design and creation is often done external to rails, with tools designed for this purpose, so it would not be reasonable to expect to do this within rails. Hence a tool like RMRE for such databases becomes very important.

Adding the support for table namespaces (schema.table or database.owner.table) would make this tool really robust.

[shapiro2]

It would be great if the models could be in namespaces. If all the models could be in a top-level namespace (eg a module called DB), and then for each part of the table name, a sub-module, etc

For example, the model for y.jobs might be something like

module DB
    module Y
         class Job < ActiveRecord::Base
    end
end

[bosko]

Setting top level namespace can be achieved through new configuration option. Basically it is not bound to this issue and can be implemented separately.

Do you mean that second level module should be named after schema name and all tables from one schema should be within that namespace?

[shapiro2]

How do I set the top level namespace with a configuration option?

And, yes, I mean a second level namespace for the schema.

---

From: Boško Ivaniševiæ [notifications@github.com]
Sent: Wednesday, May 22, 2013 10:55 AM
To: bosko/rmre
Cc: Shapiro, Michael
Subject: {Disarmed} Re: [rmre] Fully qualified table names and models (#16)

Setting top level namespace can be achieved through new configuration option. Basically it is not bound to this issue and can be implemented separately.

Do you mean that second level module should be named after schema name and all tables from one schema should be within that namespace?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/16#issuecomment-18288231.

[bosko]

No, you cannot set it now. It is not implemented. My comment was just conclusion that it can be implemented independently of PostgreSQL schema support.

[shapiro2]

OK, I understand. Do you think the second-level namespace is possible?

---

From: Boško Ivaniševiæ [notifications@github.com]
Sent: Wednesday, May 22, 2013 12:01 PM
To: bosko/rmre
Cc: Shapiro, Michael
Subject: {Disarmed} Re: [rmre] Fully qualified table names and models (#16)

No, you cannot set it now. It is not implemented. My comment was just conclusion that it can be implemented independently of PostgreSQL schema support.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/16#issuecomment-18292544.

[bosko]

Since it has to be implemented it should be possible although I would also put it as a configuration option.

[shapiro2]

I coded some models using modules. If I put the models in the correct folders with the correct names, it works

app/models/db/x/job.rb

module DB
 module X
  class Job < ActiveRecord::Base
    self.table_name = 'x.jobs'
    belongs_to :resource, :class_name => '::DB::Y::Resource', :foreign_key => :resource_id    
  end
 end
end

app/models/db/y/resource.rb

module DB
 module Y
   class Resource < ActiveRecord::Base
     self.table_name = 'y.resources'
     has_many :jobs, :class_name => '::DB::X::Job'    
  end
 end
end

app/controllers/jobs_controller.rb

class JobsController < ApplicationController
  def all
    @jobs=DB::X::Job.all
    render :json => @jobs
  end
end