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.
Installation
As usual:
gem "schema_plus_views" # in a Gemfile
gem.add_dependency "schema_plus_views" # in a .gemspec
Compatibility
SchemaPlus::Views is tested on:
- ruby 2.1.5 with activerecord 4.2, using mysql2, sqlite3 or postgresql
Usage
Creating views
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.
SchemaPlus::Views also arranges to include the create_view
statements (with literal SQL) in the schema dump.
Dropping views
In a migration:
drop_view :posts_commented_by_staff
drop_view :uncommented_posts, :if_exists => true
Using views
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
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.views # => array of view names [method provided by SchemaPlus::Views]
Notes:
-
For Mysql and SQLite3, ActiveRecord's
connection.tables
method would return views as well as tables; SchemaPlus::Views normalizes them to return only tables. -
For PostgreSQL,
connection.views
suppresses views prefixed withpg_
as those are presumed to be internal.
Querying view definitions
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.
Customization API: Middleware Stacks
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.
Schema::Views
stack
Wraps the connection.views
method. Env contains:
Env Field | Description | Initialized |
---|---|---|
:views |
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.views
Schema::ViewDefinition
stack
Wraps the connection.view_definition
method. Env contains:
Env Field | Description | Initialized |
---|---|---|
:connection |
The current ActiveRecord connection | context |
:view_name |
The 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.view_name
and assigns the result to env.definition
Migration::CreateView
stack
Wraps the migration.create_view
method. Env contains:
Env Field | Description | Initialized |
---|---|---|
:connection |
The current ActiveRecord connection | context |
:view_name |
The view name | arg |
:definition |
The view definition SQL | arg |
:options |
Create view options | arg |
The base implementation creates the view named env.view_name
using the
definition in env.definition
with options in env.options
Migration::DropView
stack
Wraps the migration.drop_view
method. Env contains:
Env Field | Description | Initialized |
---|---|---|
:connection |
The current ActiveRecord connection | context |
:view_name |
The view name | arg |
:options |
Drop view options | arg |
The base implementation drops the view named env.view_name
using the
options in env.options
History
- 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
Development & Testing
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 travis-ci
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.