Materialized views

A materialized view a database view that is stored physically on disk. A matview behaves like a table, but it is not possible to add or delete rows. Unlike normal views, materialized views should be updated manually. Read more

SchemaPlus::MaterializedViews

SchemaPlus::MaterializedViews adds support for creating and dropping materialized views in ActiveRecord migrations, as well as querying views.

SchemaPlus::MaterializedViews has been developed using SchemaPlus::Views as a guideline.

Supported Databases

• PostgreSQL

Installation

As usual:

gem "schema_plus_materialized_views"                # in a Gemfile
gem.add_dependency "schema_plus_materialized_views" # in a .gemspec

Compatibility

SchemaPlus::MaterializedViews is tested on:

• ruby 2.3.0 with activerecord 4.2, using postgresql

Usage

Creating materialized views

In a migration, a view can be created using literal SQL:

create_materialized_view :uncommented_posts, "SELECT * FROM posts LEFT OUTER JOIN comments ON comments.post_id = posts.id WHERE comments.id IS NULL"

or using an object that responds to :to_sql, such as a relation:

create_materialized_view :posts_commented_by_staff,  Post.joins(comment: user).where(users: {role: 'staff'}).uniq

(It's of course a questionable idea for your migration files to depend on your model definitions. But you can if you want.)

SchemaPlus::MaterializedViews also arranges to include the create_materialized_view statements (with literal SQL) in the schema dump.

Dropping materialized views

In a migration:

drop_materialized_view :posts_commented_by_staff
drop_materialized_view :uncommented_posts, :if_exists => true

Adding indexes

Indexes can be used as normal table indexes.

In a migration:

add_index :posts_commented_by_staff, :post_id, unique: true

Using views

ActiveRecord models can be based on materialized views the same as ordinary tables. That is, for the above views you can define

class UncommentedPost < ActiveRecord::Base
end

class PostCommentedByStaff < ActiveRecord::Base
  table_name = "posts_commented_by_staff"
end

Querying views

You can look up the defined views analogously to looking up tables:

connection.tables # => array of table names [method provided by ActiveRecord]
connection.materialized_views  # => array of view names [method provided by SchemaPlus::Views]

Querying view definitions

You can look up the definition of a materialized view using

connection.materialized_view_definition(matview_name) # => returns SQL string

This returns just the body of the definition, i.e. the part after the CREATE MATERIALIZED VIEW 'name' AS command.

Schema::MaterializedViews stack

Wraps the connection.materialized_views method. Env contains:

Env Field	Description	Initialized
:matviews	The result of the lookup	[]
:connection	The current ActiveRecord connection	context
:query_name	Optional label for ActiveRecord logging	arg

The base implementation appends its results to env.matviews

Schema::MaterializedViewDefinition stack

Wraps the connection.materialized_view_definition method. Env contains:

Env Field	Description	Initialized
:connection	The current ActiveRecord connection	context
:matview_name	The materialized view to look up	arg
:query_name	Optional label for ActiveRecord logging	arg
:definition	The view definition SQL	nil

The base implementation looks up the definition of the view named env.matview_name and assigns the result to env.definition

Migration::CreateMaterializedView stack

Wraps the migration.create_materialized_view method. Env contains:

Env Field	Description	Initialized
:connection	The current ActiveRecord connection	context
:matview_name	The materialized view name	arg
:definition	The materialized view definition SQL	arg

The base implementation creates the view named env.matview_name using the definition in env.definition

Migration::DropMaterializedView stack

Wraps the migration.drop_materialized_view method. Env contains:

Env Field	Description	Initialized
:connection	The current ActiveRecord connection	context
:matview_name	The materialized view name	arg
:options	Drop materialized view options	arg

The base implementation drops the view named env.view_name

History

• 0.1.0 - Initial release, based on schema_plus_views 0.3.0

Development & Testing

Are you interested in contributing to SchemaPlus::MaterializedViews? Thanks! Please follow the standard protocol: fork, feature branch, develop, push, and issue pull request.

Some things to know about to help you develop and test:

• schema_dev: SchemaPlus::MaterializedViews uses schema_dev to facilitate running rspec tests on the matrix of ruby, activerecord, and database versions that the gem supports. To to run rspec locally on the full matrix, do:

    $ schema_dev bundle install
    $ schema_dev rspec
  

  You can also run on just one configuration at a time; For info, see schema_dev --help or the schema_dev README.

  The matrix of configurations is specified in schema_dev.yml in the project root.

• schema_plus_core: SchemaPlus::MaterializedViews uses the SchemaPlus::Core API that provides middleware callback stacks to make it easy to extend ActiveRecord's behavior. If that API is missing something you need for your contribution, please head over to schema_plus_core and open an issue or pull request.

• schema_monkey: SchemaPlus::MaterializedViews is implemented as a schema_monkey client, using schema_monkey's convention-based protocols for extending ActiveRecord and using middleware stacks.