I have few database views which are exposed as REST API end points. The current implementation is where once a view is added we add Rails code like

1. Add new route
2. Add Controller and other code to query the view which returns json response

The downside of this approach is we need to add code to the app every time we add a database view, sometimes this is also not instantaneous which is another problem.

Is there a way, probably by using Meta Programming or something else we are able to query the database to get the list of views and generate the necessary routes and code to return a valid response.

Below is the relevant part of the code

namespace :api do
  namespace :v1 do
    [
      'leases',
      # a new will go in here
    ]
  end
end

class Api::V1::LeasesController < Api::V1::ApplicationController
  api :GET, '/leases', "Retrieve paginated well month data"
  param :authentication_token, String, "Token for authentication"

  def index
    result = Api::Lease.ransack(params[:q]).result.page(params[:page]).per(10000)
    render json: result.to_json
  end
end


class Api::Lease < ExternalRecord
  self.table_name = 'View_Leases' #View in External Postgres server
end

Thanks.

CodePudding user response：

Is there a way, probably by using Meta Programming or something else we are able to query the database to get the list of views and generate the necessary routes and code to return a valid response.

Yes, but the actual implementation depends on the database in use. On Postgres you can get a list of the views by querying pg_catalog.pg_views:

pg_views = Arel::Table.new('pg_catalog.pg_views')
query = pg_views.project(:schemaname, :viewname)
                .where(
                  pg_views[:schemaname].in('pg_catalog', 'information_schema').not
                )

result = ActiveRecord::Base.connection.execute(query)
# ... 

But a framework change is in order here. Does a view necissarily need to correspond to its own route or could you create a better RESTful design?

If you are for example listing by year/month you could easily setup a single route which covers it:

namespace :api do
  namespace :v1 do
    resources :leases do
      get '/leases/by_month/:year/:month', as: :by_month, action: :by_month
    end
  end
end

Can you setup a model with metaprogramming?

Absolutely. Classes in Ruby are first-class objects and you can create them with Class.new:

# Setup a base class for shared behavior 
class ApplicationView < ActiveRecord::Base
  self.abstract_class = true
end

str = 'Foo'
model = Class.new(ApplicationView) do |klass|
  # do your model specific thing here...
end

# Classes get their name by being assigned to a constant
ApplicationView.const_set(str, model)
ApplicationView::Foo.all # SELECT * FROM foos;  

ActiveRecord and ActiveModel don't really like anonymous classes (classes that are not assigned to a constant) since they do a bunch of assumptions based on the class name. Here we are nesting the constants in ApplicationView simply to avoid namespace crashes.

Another methods thats sometimes used in libary code is to create a string containing the code to define the class and eval it. ​

You can also setup a single model that queries different tables/views.

Can you setup controllers and views (as in MVC) with metaprogramming?

Yes. But you shouldn't need it. You can simply create generic controllers that can handle a variety of models. Remember that the idea that a controller corresponds to a single model is just a convention that applies in trivial cases.

CodePudding user response：

You can make arbitrary SQL queries though a given connection

namespace :v1 do
  resources :views, only: :show
end


class ViewsController
  def show
    table_name = "View_#{params[:id].titleize}"
    offset = (params[:page].to_i || 1) * 10_000 - 10_000
    limit = 10_000
    sql = "SELECT #{table_name}.* FROM #{table_name} OFFSET #{offset} LIMIT #{limit};"
    data = ActiveRecord::Base.connection.execute(sql)
    render json: data.to_json
  end
end

but then forget about using ransack. Ransack needs a model and it relies on the inferred column types to convert the query to a series of where, join etc on the model itself.

Of course, you should protect yourself against SQL injection if you make your own SQL strings.

If you want to retain the full power of querying, you might better put something like Hasura between your app and your views, and transform the incoming query into a GraphQL request to hasura (which will handle the rest for you).