PlainView

PlainView adds another tool to your Rails migrations toolkit create_view (and drop_view of course).
which will allow you to harness the power of database views in your application.

Installation

as for now this is only available as a plugin, install as follows:

script/plugin install git://github.com/eladmeidar/PlainViews.git

Usage

create a migration

First you’ll have to generate a migration

script/generate migration create_users_and_views

Than, on self.up you can use the create_view helper:

class TestView < ActiveRecord::Migration

  def self.up

    create_view :v_people do |t|

      t.base_model :user

      t.select :select => ‘id, name’, :conditions => {:name => ’elad’}

      t.use_security_mode :definer

      t.use_algorithm :merge

      t.use_check_option :cascaded

    end

  end
def self.down
drop_view :v_people
end
end

select – required, select is the actual select statement, you can either use ActiveRecord#find conventions or specify a plain test query.
base_mode this is used only if you use the ActiveRecord conventions on the select statement, otherwise it is not required
use_security – i don’t know if this is supported on all databases, but in MySQL you can choose either :merge or :temptable.
use_algorithm – again, don’t know if this is a cross DBMS option but on MySQL you can choose between :definer or :invoker
check_option – same deal, on MySQL you can choose between :cascaded and :local
use_raw_sql – if you use this, all other options are not taken into consideration, use this to use raw sql for your view just like AR’s @find_by_sql*

creating a model

based on the name you chose to your view, you can create a model based on the existing rails naming conventions,
you can always name it whatever you want and specify the view name in set_table_name.

Note that the view model inherits from ActiveRecord::View and not from ActiveRecord::Base:

class ThisIsAView < ActiveRecord::View
  set_table_name 'special_view_name'
end

By default you can’t add/remove/update records on the view since not all DBMS support that. you can easily override the readonly method if you fill like it.

Database support

I investigated a bit, and the basic usage (select / base_model) should run successfully on:

MySQL
OCI
Oracle
Postgresql
SQLite
SQLServer

although i was unable to actually test it on all of them, help is appreciated here :)

Tests

Run rake. select the db you want to test by using an env var

> DB=mysql rake

don’t forget to update the plugin’s database.yml in test/config

eladmeidar/PlainViews