schemaplus/schema_plus_views

Provides support for creating and dropping views in ActiveRecord migrations. Also support for querying views.

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.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

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.

• :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

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')

Dropping views

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

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 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

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.

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

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

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::ViewDefinition stack

Wraps the connection.view_full_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
:view_type	The view type symbol.	:view`

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

• 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

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 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.

schema_plus_views's Issues

Table not added to schema under circumstances to be determined [edit 2016-06-16]

ACTUAL BEHAVIOR
without schema_plus_views gem

create a migration to create table users if you don't have one
or
create any test migration for a different table (not users) if you already have users
rake db:migrate
create users... added to schema (or still there if it was already there)
create a migration for usersx view
rake db:migrate
users table still in schema
usersx view is not added to schema (standard rake behavior)

add schema_plus_views gem to gemfile
bundle
create any test migration for a different table
rake db:migrate
create view usersx... added to schema as expected with gem
create users table... not in schema

EXPECTED BEHAVIOR
create view usersx in schema
create table users in schema

How to exclude postgis views when run db:setup command

I am using postgis, when I setup by schme.rb will throwing following error:

ActiveRecord::StatementInvalid: PG::DependentObjectsStillExist: ERROR:  cannot drop view raster_columns because extension postgis requires it

I do not wanna to drop the postgis views

Views should follow tables in schema.rb

This is a great gem, I use it all the time.

When schema.rb is created, the SQL view is inserted in alpha order. This means that if there is a later table in the schema.rb file which the view depends on, rake db:reset will fail as the dependent object is not created when the create_view is called.

The workaround is to manually edit the schema.rb file after any migrations are run, and move the views down.

Any suggestions on how to automate this? It could be further complicated if views depend on views, but if all the views we added to schema.rb after all the tables, it would solve 90% of the problems.

Create Views in database with each column on a separate line shows up with /n in schema

In the database schema, the view looks like this:

CREATE OR REPLACE VIEW usersx AS
SELECT users.id,
users.client_id,
users.username,
users.firstname

With the schema_plus_views gem, the schema.rb looks like this:

CREATE OR REPLACE VIEW usersx AS
SELECT users.id,\n users.client_id,\n users.usernam\n, users.firstname\n ...

postgresql
rake 10.4.2
rails 4.2.0
ruby 2.2.1p85
osx yosemite

[PG] Created View in Wrong Schema

Currently the view's are being created in the schema that matches my username, and not in the public schema where the rest of my rails data is location.

to_json

just for my information. why do you use method to_json in this place?

class View < KeyStruct[:name, :definition]
   def assemble(stream)
        heredelim = "END_VIEW_#{name.upcase}"
        stream.puts "  create_view #{name.to_json}, <<-#{heredelim}, :force => true\n"
        definition.split("\n").each { |line| stream.puts line.to_json[1...-1] + "\n" }
        stream.puts "  #{heredelim}\n\n"
    end
end

if view contains fields from an json column in pg table in schema db i get unicode lines like that

 ((details -\u003e\u003e 'cost'::text))::numeric(10,2) AS cost

db:rollback with view recreates the view

Any plan to update this gem to be compatible with rails 5.2?

Hi guys,
just wondering if you guys are thinking to update this gem to be compatible with rails 5.2?

Sent a PR to do so but no luck with the tests: #17

Any help here is appreciated

thanks

Unnecessary modules within ActiveRecord namespace

If project doesn't have mysql2 loaded it creates Mysql2Adapter as a module and causes issues

>> ActiveRecord::ConnectionAdapters::Mysql2Adapter
SchemaPlus::Views::ActiveRecord::ConnectionAdapters::Mysql2Adapter

In projects that have mysql2 it works correctly:

>> ActiveRecord::ConnectionAdapters::Mysql2Adapter
ActiveRecord::ConnectionAdapters::Mysql2Adapter < ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter

Any plans to support materialized views?

Would that be very difficult or not work for some reason?

Views with multi-schema joins in postgres

I have an issue when defining views with joins between two postgres schemas (e.g. public and client_a).

Suppose we have this view definition:

CREATE VIEW my_view AS SELECT plans.name, clients.id
FROM clients 
JOIN public.plans ON plans.id = clients.plan_id

Then then the following SQL is stored in db/schema.rb (notice missing public prefix for plans):

SELECT plans.name, clients.id
FROM clients 
JOIN plans ON plans.id = clients.plan_id

I'm using this in combination with the apartment gem which loads schema.rb file for every tenant it creates. It also creates all tables for every schema/tenant, even if they are only used in the public schema.

The query to get the views:

schema_plus_views/lib/schema_plus/views/active_record/connection_adapters/postgresql_adapter.rb

Lines 19 to 22 in 1d04d8e

	SELECT pg_get_viewdef(oid)
	FROM pg_class
	WHERE relkind = 'v'
	AND relname = '#{view_name}'

Not sure how to proceed yet. The views generated by migrations are correct.
Basically I want the "exluded model tables" from apartment to be prefixed with public in the db/schema.rb while the others use search_path.