SchemaPlus::Views

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

SchemaPlus::Views is part of the SchemaPlus family of Ruby on Rails extension gems.

As usual:

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

SchemaPlus::Views is tested on:

• ruby 2.5 with activerecord 5.2, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.5 with activerecord 6.0, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.5 with activerecord 6.1, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.7 with activerecord 5.2, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.7 with activerecord 6.0, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.7 with activerecord 6.1, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 2.7 with activerecord 7.0, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.0 with activerecord 6.0, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.0 with activerecord 6.1, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.0 with activerecord 7.0, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.1 with activerecord 6.0, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.1 with activerecord 6.1, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12
• ruby 3.1 with activerecord 7.0, using mysql2, sqlite3, postgresql:9.6, postgresql:10, postgresql:11 or postgresql:12

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

create_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_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.)

Additional options can be provided:

• :force => true if there's an existing view with the given name, deletes it first. Note that this could fail if another view depends on it.

• :allow_replace => true will use the command "CREATE OR REPLACE" when creating the view, for seamlessly redefining the view even if other views depend on it. It's only supported by MySQL and PostgreSQL, and each has some limitations on when a view can be replaced; see their docs for details.

• :materialized => true will create a materialized view instead of a standard view. This view caches its contents on disk and must be refreshed to update its contents. It is only supported on PostgreSQL. Further, allow_replace is not supported on materialized views.

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

Materialized views persist their data when created and must be manually refreshed to see new data. Further materialized views can have indexes defined on them.

create_view :posts_commented_by_staff, <<~SQL, materialized: true
SELECT * FROM posts LEFT OUTER JOIN comments ON comments.post_id = posts.id WHERE comments.id IS NULL
SQL

add_index :posts_commented_by_staff, :category
add_index :posts_commented_by_staff, :token, unique: true

To refresh a materialized view run the refresh_view connection command.

ActiveRecord::Base.connection.refresh_view('posts_commented_by_staff')

In a migration:

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

# materialized views
drop_view :posts_commented_by_staff, materialized: true
drop_view :uncommented_posts, :if_exists => true, materialized: true

ActiveRecord models can be based on 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

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

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

Notes:

1. For PostgreSQL, connection.views suppresses views prefixed with pg_ as those are presumed to be internal. Also it suppresses the "postgis" specifically named tables
   • geography_columns
   • geometry_columns
   • raster_columns
   • raster_overviews

You can look up the definition of a view using

connection.view_definition(view_name) # => returns SQL string

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

You can also lookup the type of view (regular or materialized) using

connection.view_type(view_name) # => returns a Symbol, either :view or :materialized

All the methods defined by SchemaPlus::Views provide middleware stacks, in case you need to do any custom filtering, rewriting, triggering, or whatever. For info on how to use middleware stacks, see the READMEs of schema_monkey and schema_plus_core.

Wraps the connection.view_full_definition method. Env contains:

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

Wraps the migration.create_view method. Env contains:

The base implementation creates the view named env.view_name using the definition in env.definition with options in env.options

Wraps the migration.drop_view method. Env contains:

The base implementation drops the view named env.view_name using the options in env.options

• 1.0.1 - Add AR 6.1, 7.0 and Ruby 3.1 support
• 1.0.0 - Add ruby 3 and AR 6.0, drop Ruy < 2.5
• 0.4.1 - Corrected spacing around dumped schema
• 0.4.0 - Added support for Rails 5.2 and materialized views in PostgreSQL
• 0.3.1 - Upgrade schema_plus_core and schema_dev dependencies
• 0.3.0
  • Added middleware stacks
  • Bug fix: view_definition: strip white space from result (postgresql)
• 0.2.3 - Remove unnecessary escaping in dump; use single-quote heredoc
• 0.2.2 - Prettier dumps: use heredoc for definition string
• 0.2.1 - Fix db:rollback
• 0.2.0 - Added :allow_replace option (thanks to @hcarver)
• 0.1.0 - Initial release, extracted from schema_plus 1.x

Are you interested in contributing to SchemaPlus::Views? 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::Views uses schema_dev to facilitate running rspec tests on the matrix of ruby, activerecord, and database versions that the gem supports, both locally and on github actions

  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::Views 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::Views is implemented as a schema_monkey client, using schema_monkey's convention-based protocols for extending ActiveRecord and using middleware stacks.