scenic-views / scenic Goto Github PK

View Code? Open in Web Editor NEW

3.3K 43.0 222.0 391 KB

Versioned database views for Rails

Home Page: https://thoughtbot.com/blog/announcing-scenic--versioned-database-views-for-rails

License: MIT License

Ruby 98.47% HTML 0.92% Shell 0.41% Dockerfile 0.20%

ruby sql rails schema postgres database activerecord hacktoberfest database-views

scenic's People

Contributors

Stargazers

Watchers

Forkers

geoffharcourt damirsvrtan robertomiranda paomar westonplatter punkrockindustries mclaros calebhearth jobseekerltd faucct devonestes twobyfourio phopu dixpac askl56 deanobarnett codemilan startweaving halkuon roelbondoc zfjoy520 matthewryanscott adzap nickmele willianvdv brightwheel neumachen craigsheen richddr bensheldon getkya urbanbound ajbdev jesus jbennett midu shiftcommerce symmetrically jaredbeck rennanoliveira unchartedcode aokiji rvanlieshout teoljungberg ristovskiv baylorrae y-yagi tarunjadhwani yannycastrillon kaileeagray kangkyu specialgem olleolleolle codebycliff pdebelak kattak2k natifyinc bivek xymbol benreyn pixielabs justindell caherrerapa voxel-web gillisd kannans cheerful mr-dxdy clickmechanic lucasmpb adamantish abunashir rikkipitt gmhawash ezo801 simi markolson ror-expert ljcooke substantial ahmedfadhil jeager mwallba enterstudio tanahiro sharipov-ru fujimura griffithind tom-rogers-home-access denstepa mtvillwock hacker0x01 derek-schaefer opternative salbertson sbiastoch smudge ejohnson442 dwillett juuh42dias

scenic's Issues

Raise friendly errors when features are used on versions of postgres that don't support them

Materialized views require PostgreSQL 9.3. Concurrent materialized view refreshes require PostgreSQL 9.4.

If those features are used on older versions of PostgreSQL, Postgres returns a syntax error. We should see if we can catch those specific messages and turn them into something a little more friendly that indicates the version of pg needs updating to use this feature.

This might be tricky or not feasible depending on the output format of the error messages.

Make create_view reversible

create view should be a reversible migration that uses drop_view.

scenic:view generator no longer reversible

At least once it's been updated once, scenic:view generator's auto-incrementing is preventing the files from being destroyed with rails destroy scenic:view [model].

dummy(master %) ❯ tree db
db
├── migrate
│   ├── 20140808140448_create_searches.rb
│   └── 20140808140452_update_searches_to_version_2.rb
├── schema.rb
├── searches
└── views
    ├── searches_v01.sql
    └── searches_v02.sql

3 directories, 5 files
dummy(master %) ❯ rails d scenic:view search
      remove  db/views/searches_v03.sql
      remove  db/migrate/20140808140517_update_searches_to_version_3.rb
dummy(master %) ❯ rails g scenic:view search
      create  db/views/searches_v03.sql
      create  db/migrate/20140808140739_update_searches_to_version_3.rb
dummy(master %) ❯ rails d scenic:view search
      remove  db/views/searches_v04.sql
      remove  db/migrate/20140808140747_update_searches_to_version_4.rb

uninitialized constant Ammeter::RSpec::Rails::GeneratorExampleGroup::ClassMethods::OutputCapturer

If I run bundle exec rake spec, I get four failures, all on Scenic::Generators specs. ammeter is included in the gemspec, so I'm not sure if there's something different about the authors' machines vs. my own that's causing the issue.

Failure/Error: run_generator ["current_customer"]
     NameError:
       uninitialized constant Ammeter::RSpec::Rails::GeneratorExampleGroup::ClassMethods::OutputCapturer
     # ./spec/generators/scenic/model/model_generator_spec.rb:20:in `block (2 levels) in <module:Generators>'

Better support for updating materialized views is needed

Updating a materialized view is tricky because to change the schema you must drop and then recreate it (as far as I know). This means you lose all indexes.

For 1.0 we decided to not support updating a materialized view so that we wouldn't unwittingly drop indexes on users. We need a better long term solution for this.

I can think of a couple of solutions:

When update_view is run for a materialized view, we capture all the indexes, then re-apply them, ignoring any errors that arise due to the indexes now being invalid (those should be dropped anyway).
We encourage people to write their materialized view index statements as SQL in their view definition file. This might just work right now - I've just not tested it. I'm not sure if we want to go this route though. It's not obvious that it's something you'd have to do.

Any other ideas?

Make it actually work

Looks like we're not hooking into the right place for migrations.

== 20140718194250 AddSearchesView: migrating ==================================
-- create_view(:searches)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

undefined method `create_view' for #<AddSearchesView:0x007fa24aa36cd8>/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:648:in `block in method_missing'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:621:in `block in say_with_time'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:621:in `say_with_time'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:641:in `method_missing'
/Users/caleb/code/scenic/spec/dummy/db/migrate/20140718194250_add_searches_view.rb:3:in `change'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:595:in `exec_migration'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:579:in `block (2 levels) in migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:578:in `block in migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:294:in `with_connection'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:577:in `migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:752:in `migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:992:in `block in execute_migration_in_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:1038:in `block in ddl_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `block in transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:209:in `within_new_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/transactions.rb:208:in `transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:1038:in `ddl_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:991:in `execute_migration_in_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:953:in `block in migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:949:in `each'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:949:in `migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:807:in `up'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:785:in `migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/railties/databases.rake:34:in `block (2 levels) in <top (required)>'
/Users/caleb/.rvm/gems/ruby-2.1.2@scenic/bin/ruby_executable_hooks:15:in `eval'
/Users/caleb/.rvm/gems/ruby-2.1.2@scenic/bin/ruby_executable_hooks:15:in `<main>'
NoMethodError: undefined method `create_view' for #<AddSearchesView:0x007fa24aa36cd8>
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:648:in `block in method_missing'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:621:in `block in say_with_time'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:621:in `say_with_time'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:641:in `method_missing'
/Users/caleb/code/scenic/spec/dummy/db/migrate/20140718194250_add_searches_view.rb:3:in `change'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:595:in `exec_migration'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:579:in `block (2 levels) in migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:578:in `block in migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:294:in `with_connection'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:577:in `migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:752:in `migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:992:in `block in execute_migration_in_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:1038:in `block in ddl_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `block in transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:209:in `within_new_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/transactions.rb:208:in `transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:1038:in `ddl_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:991:in `execute_migration_in_transaction'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:953:in `block in migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:949:in `each'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:949:in `migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:807:in `up'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/migration.rb:785:in `migrate'
/Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/activerecord-4.1.4/lib/active_record/railties/databases.rake:34:in `block (2 levels) in <top (required)>'
/Users/caleb/.rvm/gems/ruby-2.1.2@scenic/bin/ruby_executable_hooks:15:in `eval'
/Users/caleb/.rvm/gems/ruby-2.1.2@scenic/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

View generator should copy previous definition when it exists

Usually when editing a view you want to tweak what's already there. We should be copying the file rather than creating a blank one.

`rake spec` fails if database `dummy_test` is not present

It looks like another Rake task is required to build the test database if it's not already present.

NoMethodError for `create_view` in migrations

Because Rails no longer requires environment for db:schema:load, Scenic isn't required and the load view methods aren't defined.

rake environment db:schema:load is a workaround for this, but isn't ideal.

rails 3 support

For the unfortunate codebases below Rails 4, it would be nice to take scenic for a spin.

I'll make a pass at this unless someone has already written the code.

Support Materialized Views

We've discussed supporting materialized views. I'd like to target this for 0.3.0 (with 0.2.0 being a pretty minimal iteration on the generators we ended up shipping in 0.1.0), but we need to carefully think out how this should work. I see a couple of options:

create_view accepts a materialized option which changes the view creation to CREATE MATERIALZED VIEW. This is really simple conceptually, but and immediate downside springs to mind: We currently have no way to remember how a view was created. This means every update and drop of the materialized view (drop must be done with DROP MATERIALIZED VIEW...) would have to also include the materialized option or we'd have to look into the schema on every update and drop and use the appropriate syntax from what we derive there.
We add create_materialzed_view, drop_materialized_view, and update_materialzed_view as schema statements. This is likely a bit more work but it has the advantage that the schema statements themselves mirror the SQL statements made to create them.

I think I still favor the first option, and think it's worth pursuing that API to at least see what the code ends up looking like. It might make more sense to refactor to the database-specific adapter pattern beforehand so we can get a better idea of how much the mostly-proprietary materialized view (oracle supports it too but has different trailing options) would effect the implementation there.

Generate model, view definition, and migration

rails generate view searches

Creates Search model as subclass of AR::Base
Creates app/views/searches_v1.sql
creates db/migrate/..._create_searches.rb

Support Postgres versions older than 9.3

Right now, our query that returns the list of views to the schema dumper assumes that pg_matviews exists. It does not exist on versions of Postgres < 9.3. We should be able to use the support work happening in #110 to conditionally include materialized views.

Follow adapter pattern for database specific implementations

We should have an abstract interface that adheres to SQL standard and adapters that implement the proprietary bits. Our basic functionality is standard SQL with the exception of where we query for existing views.

What are your thoughts on the 'schema_plus_views' gem?

Hey all, let me know if this is not the right place to bring this up.

Like thoughtbot's scenic gem, the schema_plus_views gem gives the developer a way to create database views through the rails api and to capture those views in the app's schema file.

One interesting difference is in its Creating Views section. You can create views through the ActiveRecord api.

It gives the following example:

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

I can see an advantage and disadvantage. I can utilize the scopes and associations I've already defined on my models. I don't have to recreate them in a sql file. However, if scopes or where predicates change, two developers can have different views depending on when they run the migration.

What do you all think?

Stack level too deep on db:schema:dump

That's what I got after migrating first view.
The view is correct as it was working before, I installed gem to have the view dumped in schema.rb

$ rake --trace db:migrate
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:migrate
** Invoke db:_dump (first_time)
** Execute db:_dump
** Invoke db:schema:dump (first_time)
** Invoke environment
** Invoke db:load_config
** Execute db:schema:dump
rake aborted!
SystemStackError: stack level too deep
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/foreigner-1.7.2/lib/foreigner/schema_dumper.rb:44:in `tables_with_foreign_keys'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/scenic-1.1.1/lib/scenic/schema_dumper.rb:7:in `tables'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/foreigner-1.7.2/lib/foreigner/schema_dumper.rb:44:in `tables_with_foreign_keys'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/scenic-1.1.1/lib/scenic/schema_dumper.rb:7:in `tables'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/foreigner-1.7.2/lib/foreigner/schema_dumper.rb:44:in `tables_with_foreign_keys'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/scenic-1.1.1/lib/scenic/schema_dumper.rb:7:in `tables'

...

/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.1.12/lib/active_record/schema_dumper.rb:38:in `dump'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.1.12/lib/active_record/schema_dumper.rb:22:in `dump'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.1.12/lib/active_record/railties/databases.rake:231:in `block (4 levels) in <top (required)>'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.1.12/lib/active_record/railties/databases.rake:230:in `open'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.1.12/lib/active_record/railties/databases.rake:230:in `block (3 levels) in <top (required)>'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `call'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `block in execute'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `each'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `execute'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:172:in `invoke_with_call_chain'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:165:in `invoke'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.1.12/lib/active_record/railties/databases.rake:42:in `block (2 levels) in <top (required)>'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `call'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `block in execute'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `each'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `execute'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:172:in `invoke_with_call_chain'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:165:in `invoke'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.1.12/lib/active_record/railties/databases.rake:37:in `block (2 levels) in <top (required)>'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `call'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `block in execute'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `each'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `execute'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:172:in `invoke_with_call_chain'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:165:in `invoke'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:150:in `invoke_task'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `each'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `block in top_level'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:115:in `run_with_threads'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:100:in `top_level'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:78:in `block in run'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:176:in `standard_exception_handling'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:75:in `run'
/Users/sharq/Projects/rosemary-conley/bin/rake:8:in `<top (required)>'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activesupport-4.1.12/lib/active_support/dependencies.rb:241:in `load'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activesupport-4.1.12/lib/active_support/dependencies.rb:241:in `block in load'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activesupport-4.1.12/lib/active_support/dependencies.rb:232:in `load_dependency'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activesupport-4.1.12/lib/active_support/dependencies.rb:241:in `load'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
/Users/sharq/.rbenv/versions/2.2.3/lib/ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in `require'
-e:1:in `<main>'
Tasks: TOP => db:schema:dump

here's the view:

SELECT
  custom_foods.id AS personalizable_id,
  'CustomFood' AS personalizable_type,
  custom_foods.created_at AS created_at,
  custom_foods.deleted AS deleted,
  custom_foods.user_id AS user_id,
  custom_foods.title AS title
FROM custom_foods

UNION

SELECT
  saved_meals.id AS personalizable_id,
  'SavedMeal' AS personalizable_type,
  saved_meals.created_at AS created_at,
  saved_meals.deleted AS deleted,
  saved_meals.user_id AS user_id,
  saved_meals.title AS title
FROM saved_meals

and the migration:

class CreatePersonalFoods < ActiveRecord::Migration
  def up
    create_view :personal_foods

    add_index :saved_meals, [:user_id, :title]
    add_index :custom_foods, [:user_id, :title]
  end

  def down
    remove_index :saved_meals, [:user_id, :title]
    remove_index :custom_foods, [:user_id, :title]

    drop_view :personal_foods
  end
end

Generate view definition and migration for updates

rails generate view_update searches <= or the like

Creates app/views/searches_v2.sql, assuming only one preexisting version. (calculate next version)
creates db/migrate/..._update_searches_v2.rb

Add documentation for schema statements

We need rdoc or yarddoc.

Test against additional Rails versions, additional rubies

We currently only test against against Rails 4.2 with Ruby 2.1 and Ruby 2.2. I'd like to add Rails 4.1, Rails 5, and Ruby 2.3 to our matrix. This will obviously make it take longer to run tests on CI, but so be it.

Not sure if we should use Appraisal so we could run against multiple rails versions locally or if we should use travis gemfiles matrix.

Support concurrent materialized view refreshes

If you don't use REFRESH MATERIALIZED VIEW CONCURRENTLY then there's a chance your materialized view could be locked for selects while its refreshing. We can't just default to concurrent refreshes because in order to use CONCURRENTLY your materialized view has to have at least one unique index on a column.

I dont think we should detect whether CONCURRENTLY can be used on a view - we should just accept an option and do as we're told.

See: http://www.postgresql.org/docs/9.4/static/sql-refreshmaterializedview.html

Consider making `Scenic::Adapters::Postgres` public API

This has useful documentation in it that describes how the schema statements work in Postgres-specific terms. Additionally, we generate code that calls in to adapter.refresh_materialized_views, so clearly that is public API.

I think we should update our Schema statement docs to reference the adapter docs for specifics and then make the adapter public.

Thoughts @calebthompson?

create_view version: X, revert_to_version: y fails on rollback

We have 18 pre-existing views that we are moving to be managed by scenic instead of basically not being managed at all so I'm doing things a bit different than the docs. This may be the source of our issue, but i'll lay out the details.

Here is what i'm getting in the terminal when I run rake db:rollback:

== 20160217231514 MigrateAllViewsToScenic: reverting ==========================
-- drop_view(:call_in_history, {:version=>2, :revert_to_version=>1})
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

unknown keyword: version
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/scenic-1.2.0/lib/scenic/statements.rb:54:in `drop_view'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:665:in `block in method_missing'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:634:in `block in say_with_time'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:634:in `say_with_time'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:654:in `method_missing'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:499:in `block in revert'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:498:in `each'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:498:in `revert'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:606:in `exec_migration'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:592:in `block (2 levels) in migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:591:in `block in migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:590:in `migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:768:in `migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:998:in `block in execute_migration_in_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:1044:in `block in ddl_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `block in transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/transaction.rb:184:in `within_new_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:220:in `transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:1044:in `ddl_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:997:in `execute_migration_in_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:959:in `block in migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:955:in `each'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:955:in `migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:830:in `down'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:910:in `move'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:812:in `rollback'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/railties/databases.rake:128:in `block (2 levels) in <top (required)>'
ArgumentError: unknown keyword: version
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/scenic-1.2.0/lib/scenic/statements.rb:54:in `drop_view'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:665:in `block in method_missing'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:634:in `block in say_with_time'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:634:in `say_with_time'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:654:in `method_missing'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:499:in `block in revert'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:498:in `each'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:498:in `revert'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:606:in `exec_migration'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:592:in `block (2 levels) in migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:591:in `block in migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:590:in `migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:768:in `migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:998:in `block in execute_migration_in_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:1044:in `block in ddl_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `block in transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/transaction.rb:184:in `within_new_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in `transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:220:in `transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:1044:in `ddl_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:997:in `execute_migration_in_transaction'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:959:in `block in migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:955:in `each'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:955:in `migrate'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:830:in `down'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:910:in `move'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/migration.rb:812:in `rollback'
RAILS_ROOT/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.5.1/lib/active_record/railties/databases.rake:128:in `block (2 levels) in <top (required)>'
Tasks: TOP => db:rollback(See full trace by running task with --trace)

My migration is pretty simple, has a bunch of views with:

drop_view :view_name, revert_to_version: 1
create_view :view_name, version: 2, revert_to_version: 1

I'm going to work around this for now by making explicit up/down methods but wanted to report this in case you consider it a bug.

If you wonder why i'm not using update_view (which probably handles this correctly) that is due to the fact that our views depend on each other in a complicated tangle and so the drops/creates have to be in a certain order. Update view won't work for us.

Difficulty in seeing differences between versions - different way to handle that?

We're using Scenic in production now, and one of my co-workers brought up what might be a good point. We made a change to a view, and when he was reviewing the code on Github you can only see that a new file was created and you don't get a sense of what has changed between version 1 and version 2. He just diff'd the two versions to see what had changed, but having to leave Github to see a diff of the versions while reviewing code is sub-optimal.

I can't for the life of me think of a way to avoid this, but I wanted to mention it in case someone did have a brilliant idea about this since I think it would remove a non-trivial source of friction from the user experience.

Document How Postgres can't infer a view's primary key

@calebthompson: @derek We’re using Scenic. While inspecting with pg, the view has an `id` column, but `TheViewModel.first.id == nil` and `TheViewModel.find(1)` complains about not having a primary key

calebthompson [14:58] 
`self.primary_key = :id`

sean [14:58] 
Setting `self.primary_key = "id"` resolved the issue. Am I doing something wrong

calebthompson [14:58] 
Nope.

calebthompson [14:58]
Views can’t have primary keys in postgres.

calebthompson [14:58]
We could probably benefit from having that in the README.

Pretty format dumped sql

Right now we just dump the SQL string right into db/schema.rb. I think it'd be nice if we formatted the SQL for reading.

Create views in a different database schema

Often, we create views in a different database schema, so that they are logically isolated from the public schema and can have a different set of access rules (eg read_only access granted to a reporting user).

Is there any way to define the schema name when using scenic?

Materialized Concurrent views

If you create a materialized view, and then refresh that view concurrently, using the scenic public APIs, you'll get this error:

ERROR:  materialized view "v2_activities" has not been populated

Add a new version of a view

rake db:schema:load fails

My test database was out of sync after trying to revise a view migration. I tried to run rake db:test:load to fix the issue, and found the same issue happens if I try to run rake db:schema:load

-- add_index("weather_records", ["time"], {:name=>"index_weather_records_on_time", :using=>:btree})
   -> 0.0077s
-- add_index("weather_records", ["weather_location_id"], {:name=>"index_weather_records_on_weather_location_id", :using=>:btree})
   -> 0.0021s
-- create_view(:pg_stat_statements, {:sql_definition=>"       SELECT pg_stat_statements.userid,\n pg_stat_statements.dbid,\n pg_stat_statements.query,\n pg_stat_statements.calls,\n pg_stat_statements.total_time,\n pg_stat_statements.rows,\n pg_stat_statements.shared_blks_hit,\n pg_stat_statements.shared_blks_read,\n pg_stat_statements.shared_blks_dirtied,\n pg_stat_statements.shared_blks_written,\n pg_stat_statements.local_blks_hit,\n pg_stat_statements.local_blks_read,\n pg_stat_statements.local_blks_dirtied,\n pg_stat_statements.local_blks_written,\n pg_stat_statements.temp_blks_read,\n pg_stat_statements.temp_blks_written,\n pg_stat_statements.blk_read_time,\n pg_stat_statements.blk_write_time\nFROM pg_stat_statements() pg_stat_statements(userid, dbid, query, calls, total_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);\n"})
rake aborted!
ActiveRecord::StatementInvalid: PG::DuplicateTable: ERROR:  relation "pg_stat_statements" already exists
: CREATE VIEW pg_stat_statements AS        SELECT pg_stat_statements.userid,
 pg_stat_statements.dbid,
 pg_stat_statements.query,
 pg_stat_statements.calls,
 pg_stat_statements.total_time,
 pg_stat_statements.rows,
 pg_stat_statements.shared_blks_hit,
 pg_stat_statements.shared_blks_read,
 pg_stat_statements.shared_blks_dirtied,
 pg_stat_statements.shared_blks_written,
 pg_stat_statements.local_blks_hit,
 pg_stat_statements.local_blks_read,
 pg_stat_statements.local_blks_dirtied,
 pg_stat_statements.local_blks_written,
 pg_stat_statements.temp_blks_read,
 pg_stat_statements.temp_blks_written,
 pg_stat_statements.blk_read_time,
 pg_stat_statements.blk_write_time
FROM pg_stat_statements() pg_stat_statements(userid, dbid, query, calls, total_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time);
;
/Users/geoff/cortex/db/schema.rb:644:in `block in <top (required)>'
/Users/geoff/cortex/db/schema.rb:14:in `<top (required)>'
PG::DuplicateTable: ERROR:  relation "pg_stat_statements" already exists
/Users/geoff/cortex/db/schema.rb:644:in `block in <top (required)>'
/Users/geoff/cortex/db/schema.rb:14:in `<top (required)>'
Tasks: TOP => db:schema:load
(See full trace by running task with --trace)

Due to some migrations in my history that rely on gems no longer in my application and a large number of total migrations, I'd like to avoid having to edit old migrations and/or temporarily patch them any time I rebuild the db from scratch. Due to a Rails 4.1 bug with rolled-back, edited, and re-played migrations, db:test:load is still a Rake command I rely on occasionally to reset my test database.

Convert smoke tests to rspec

The tests in spec/smoke are difficult for me to work with and I'm familiar with them. I think we'll get better acceptance test coverage and encourage more people to get involved in the project if we move these to Ruby code.

We can still have these run as a separate suite so we have our fast tests and our acceptance tests, which all run on a full rake. See the Clearance test suite for an example.

Unpublished version

It seems that version 1.1.0 wasn't published on RubyGems.
https://rubygems.org/gems/scenic

Functions

This is more of a question than an issue.

I have the some problem with a couple of small functions in PostgreSQL that Scenic is designed to solve for views. Using scenic for my views makes perfect sense, but it's a shame that it doesn't handle functions the same way.

Could you see Scenic extending to things that aren't views (despite the fact that a scenic function or worse, a scenic procedure doesn't sound nearly as cool), or would you see that being a totally separate, somewhat similar project?

Views from postgis getting including in schema.rb

When using this gem with postgis, it adds the following views to schema.rb: geography_columns, geometry_columns, raster_columns, raster_overviews. This causes problems when running the tests because they try to create these views but they already exist.

Not sure what the best course of action is here but as a temporary work around I am going to try forking the repo and explicitly blacklisting those views to see if it fixes my problem. Please let me know if there is a better solution!

Document issues with `SELECT *` in views

I recently created a view that was meant to be the entirety of one table plus some aggregate data on each row. So something like this simplified, incomplete example:

SELECT
    posts.*,
    approved_comments.count AS approved_comments_count,
    deleted_comments.count AS deleted_comments_count,
FROM
    posts
LEFT JOIN (SELECT ... ) as approved_comments ON ...
LEFT JOIN (SELECT .. ) as deleted_comments ON ...

Then I created a model like so:

class PostWithStatistics < Post
end

This allows my PostWithStatistics to act as a Post model and lets me opt in to when I want the extra associated data. Everything works reasonably well until...

class AddFooToPosts < ActiveRecord::Migration
  def change
    add_column :posts, :foo, :string
  end
end

The posts table now has a new column and I'd expect my posts_with_statistics view to reflect that because I used SELECT *. Unfortunately, it doesn't work this way because when the view is created with SELECT *, Postgres bakes into the schema the actual column names represented by *. To pick up the new column I'd have to use update_view and pass the same version number that is already deployed.

For a similar reason, this migration would also be a problem

class RemoveBarFromPosts < ActiveRecord::Migration
  def change
    remove_column :posts, :bar, :string
  end
end

This migration will fail because Postgres knows that there's a view that explicitly lists the column bar as part of its schema. This dependency will prevent the column from being removed. The workaround is:

class RemoveBarFromPosts < ActiveRecord::Migration
  def up
    drop_view :posts_with_statistics
    remove_column :posts, :bar, :string
    create_view :posts_with_statistics
  end
end

I don't think there's anything we can do from the scenic perspective to handle this. I think we likely just need to document this (and potentially link to this issue for a full description).

Anyone have any other ideas?

Update Readme to drive usage through generators.

Add support for creating views in schemas other than `public`

We're starting to use Scenic in production at my company, and I needed to create a view in our database that was in a schema other than public. Specifically, we use schemas to segregate data by year, so we have tables like fy2014.schools, fy2015.schools and fy2016.schools.

The generator did a pretty good job, but I submitted a PR that would add more fluent support for this functionality in #124. Not sure if you want to actually include this feature or not, but I figured it couldn't hurt, and I'll probably use my patched version of the gem anyway unless these are merged into master, so I figured I'd submit it in case you wanted to go that direction.

Add a user-level test

Actually run commands on the command line, with no stubbing or anything, to ensure that things work all the way from the generator to the database.

Does not work in engines

bundle exec rails generate scenic:view

/home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/scenic-1.2.0/lib/generators/scenic/view/view_generator.rb:69:in `views_directory_path': undefined method `join' for nil:NilClass (NoMethodError)
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/scenic-1.2.0/lib/generators/scenic/view/view_generator.rb:14:in `create_views_directory'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/thor-0.19.1/lib/thor/command.rb:27:in `run'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/thor-0.19.1/lib/thor/invocation.rb:126:in `invoke_command'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `block in invoke_all'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `each'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `map'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `invoke_all'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/thor-0.19.1/lib/thor/group.rb:232:in `dispatch'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/thor-0.19.1/lib/thor/base.rb:440:in `start'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/railties-4.2.5.1/lib/rails/generators.rb:157:in `invoke'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/railties-4.2.5.1/lib/rails/commands/generate.rb:13:in `<top (required)>'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/railties-4.2.5.1/lib/rails/engine/commands.rb:19:in `require'
    from /home/alex/.rvm/gems/ruby-2.2.3@sexi/gems/railties-4.2.5.1/lib/rails/engine/commands.rb:19:in `<top (required)>'
    from bin/rails:12:in `require'
    from bin/rails:12:in `<main>'

Figure out how to dump to db/schema.rb

If we inline the current version of a view, we don't need to worry about a file being deleted accidentally.

ActiveRecord::StatementInvalid: PG::ConnectionBad: connection is closed when using db:schema:load

El Capitan
Rails 4.2.5
Postgres 9.4.5 from homebrew

Create a new rails project
Add the scenic gem and bundle
bundle exec rails g scenic:view hello_world
Add anything to the view (in this instance SELECT 'hello world';)
bundle exec rake db:create db:migrate works as expected
Subsequent calls to
bundle exec rake db:drop db:setup or bundle exec rake db:drop db:create db:schema:load fail with:

ActiveRecord::StatementInvalid: PG::ConnectionBad: connection is closed: CREATE VIEW "hello_worlds" AS       SELECT 'hello world';

However, subsequent bundle exec rake db:drop db:create db:migrate calls work.

Document local test setup

Maybe a rake task. Basically exactly what travis does for before_script.

rails g scenic:view topics_with_post_count begets topics_with_post_counts

Shouldn't pluralize.

How to limit database queries?

We are going to have potentially tens of thousands of records in our database, in all tables. If someone searches something generic that matches majority of those somehow, how do I prevent from loading thousands of records into memory upon the resulting view returning? I tried using limits and putting parenthese around each close that's unioned, but that messes up full text search.

mysql support

I'll make a pass at this unless someone has already written the code.

View dependencies in migration

I'm running into this issue when I do rake test db:schema:load it doesn't work and generate this error message: relation "recent_summaries" does not exist , possibly because this new view recent_decorated_summaries is calling recent_summaries view and recent_summaries view is also depend on other two views, while both views are generated in the same transactions when doing schema load. If I rollback that top migration (which is the one that generate the above error) and do rake test db:schema:load and then rake test db:migrate, it will work.

The only work-around I have is basically copy the two views recent_summaries depends on and paste them into thisrecent_decorated_summaries so there is less levels of dependencies.

Anyone had this issue before?

Test against edge rails

I don't know of any major changes to the API we are using/monkey patching, but if we are going to release 1.0 id like to know we don't have any lurking major breakages.

Error if db/views does not exist

/Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/scenic-0.2.0/lib/generators/scenic/view/view_generator.rb:35:in `open': No such file or directory @ dir_initialize - /Users/caleb/code/team/db/views (Errno::ENOENT)
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/scenic-0.2.0/lib/generators/scenic/view/view_generator.rb:35:in `entries'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/scenic-0.2.0/lib/generators/scenic/view/view_generator.rb:35:in `previous_version'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/scenic-0.2.0/lib/generators/scenic/view/view_generator.rb:41:in `version'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/scenic-0.2.0/lib/generators/scenic/view/view_generator.rb:64:in `definition'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/scenic-0.2.0/lib/generators/scenic/view/view_generator.rb:11:in `create_view_definition'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/command.rb:27:in `run'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:126:in `invoke_command'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `block in invoke_all'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `each'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `map'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `invoke_all'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/group.rb:234:in `dispatch'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:115:in `invoke'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/scenic-0.2.0/lib/generators/scenic/model/model_generator.rb:16:in `invoke_view_generator'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/command.rb:27:in `run'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:126:in `invoke_command'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `block in invoke_all'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `each'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `map'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/invocation.rb:133:in `invoke_all'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/group.rb:234:in `dispatch'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@global/gems/thor-0.19.1/lib/thor/base.rb:440:in `start'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/railties-4.1.5/lib/rails/generators.rb:157:in `invoke'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/railties-4.1.5/lib/rails/commands/generate.rb:11:in `<top (required)>'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/activesupport-4.1.5/lib/active_support/dependencies.rb:247:in `require'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/activesupport-4.1.5/lib/active_support/dependencies.rb:247:in `block in require'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/activesupport-4.1.5/lib/active_support/dependencies.rb:232:in `load_dependency'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/activesupport-4.1.5/lib/active_support/dependencies.rb:247:in `require'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/railties-4.1.5/lib/rails/commands/commands_tasks.rb:135:in `generate_or_destroy'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/railties-4.1.5/lib/rails/commands/commands_tasks.rb:51:in `generate'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/railties-4.1.5/lib/rails/commands/commands_tasks.rb:40:in `run_command!'
        from /Users/caleb/.rvm/gems/ruby-2.1.2@team/gems/railties-4.1.5/lib/rails/commands.rb:17:in `<top (required)>'
        from bin/rails:4:in `require'
        from bin/rails:4:in `<main>'

NoMethodError: undefined method `create_view' for ActiveRecord::Schema

I get this error when I was trying to run rake db:schema:load.

The schema.rb works fine with rake db:setup, but not with rake db:create db:schema:load.
Below is the stack trace I've got:

demo % rake db:create db:schema:load --trace
** Invoke db:create (first_time)
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:create
** Invoke db:schema:load (first_time)
** Invoke db:load_config
** Execute db:schema:load
-- enable_extension("plpgsql")
   -> 0.0233s
-- create_table("users", {:force=>:cascade})
   -> 0.0069s
-- create_view(:user_with_profiles, {:sql_definition=>"      SELECT users.name\n     FROM users;\n"})
rake aborted!
NoMethodError: undefined method `create_view' for #<ActiveRecord::Schema:0x007f9bb20710e8>
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/migration.rb:661:in `block in method_missing'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/migration.rb:632:in `block in say_with_time'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/2.2.0/benchmark.rb:288:in `measure'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/migration.rb:632:in `say_with_time'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/migration.rb:652:in `method_missing'
/private/tmp/demo/db/schema.rb:27:in `block in <top (required)>'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/schema.rb:41:in `instance_eval'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/schema.rb:41:in `define'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/schema.rb:61:in `define'
/private/tmp/demo/db/schema.rb:14:in `<top (required)>'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activesupport-4.2.2/lib/active_support/dependencies.rb:268:in `load'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activesupport-4.2.2/lib/active_support/dependencies.rb:268:in `block in load'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activesupport-4.2.2/lib/active_support/dependencies.rb:240:in `load_dependency'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activesupport-4.2.2/lib/active_support/dependencies.rb:268:in `load'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/tasks/database_tasks.rb:218:in `load_schema_for'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/tasks/database_tasks.rb:235:in `block in load_schema_current'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/tasks/database_tasks.rb:275:in `block in each_current_configuration'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/tasks/database_tasks.rb:274:in `each'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/tasks/database_tasks.rb:274:in `each_current_configuration'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/tasks/database_tasks.rb:234:in `load_schema_current'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/activerecord-4.2.2/lib/active_record/railties/databases.rake:244:in `block (3 levels) in <top (required)>'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `call'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:240:in `block in execute'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `each'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:235:in `execute'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/2.2.0/monitor.rb:211:in `mon_synchronize'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:172:in `invoke_with_call_chain'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/task.rb:165:in `invoke'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:150:in `invoke_task'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `each'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:106:in `block in top_level'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:115:in `run_with_threads'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:100:in `top_level'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:78:in `block in run'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:176:in `standard_exception_handling'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/lib/rake/application.rb:75:in `run'
/Users/derrick/.rbenv/versions/2.2.3/lib/ruby/gems/2.2.0/gems/rake-10.5.0/bin/rake:33:in `<top (required)>'
/Users/derrick/.rbenv/versions/2.2.3/bin/rake:23:in `load'
/Users/derrick/.rbenv/versions/2.2.3/bin/rake:23:in `<main>'
Tasks: TOP => db:schema:load

Limit not working

I can't use limits on the unioned clauses:

SELECT
  songs.id AS searchable_id,
  'Song' AS searchable_type,
  songs.title AS query
FROM songs
LIMIT 10

UNION

SELECT
  artists.id AS searchable_id,
  'Artist' AS searchable_type,
  artists.name AS query
FROM artists
LIMIT 10

Error:

rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::SyntaxError: ERROR:  syntax error at or near "UNION"
LINE 8: UNION
        ^
: CREATE VIEW "sitewide_searches" AS SELECT
  songs.id AS searchable_id,
  'Song' AS searchable_type,
  songs.title AS query
FROM songs
LIMIT 10

UNION

SELECT
  artists.id AS searchable_id,
  'Artist' AS searchable_type,
  artists.name AS query
FROM artists
LIMIT 10;

Cannot migrate to a materialized view from a normal view

It seems that there isn't an easy way to migrate from a normal database view to a materialized view. Since update_view generates a DROP MATERIALIZED VIEW view_name statement whenever :materialized is true, it will fail when view_name is a non-materialized view.

So if we'd like to support a way for users to migrate from a normal view to a materialized one, we'll either need to provide a second type of migration generation command or we'll need to add some logic into the existing one, checking for whether the view already exists as a non-materialized view.

You can see my demo repo where the issue exists here: https://github.com/adamnbowen/test-scenic/tree/materialized-migration. That repo is on Rails 5, but I don't see how the issue would be Rails 5 specific.

Note that in my test repo, you'll need to inject environment variables for your database username, password and port—you can see the config in config/database.yml for the appropriate variables to set.

The commands I ran to generate the migrations were rails g scenic:model ActiveUser and then rails g scenic:model ActiveUser --materialized, where a rake db:migrate on the first migration worked just fine, and the failure only happened after the second migration.

$ rails db:migrate
== 20160206232756 UpdateActiveUsersToVersion2: migrating ======================
-- update_view(:active_users, {:version=>2, :revert_to_version=>1, :materialized=>true})
rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::WrongObjectType: ERROR:  "active_users" is not a materialized view
HINT:  Use DROP VIEW to remove a view.
: DROP MATERIALIZED VIEW "active_users";/Users/adambowen/src/test-scenic/db/migrate/20160206232756_update_active_users_to_version_2.rb:3:in `change'
/Users/adambowen/src/test-scenic/bin/rails:9:in `require'
/Users/adambowen/src/test-scenic/bin/rails:9:in `<top (required)>'
/Users/adambowen/src/test-scenic/bin/spring:13:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
ActiveRecord::StatementInvalid: PG::WrongObjectType: ERROR:  "active_users" is not a materialized view
HINT:  Use DROP VIEW to remove a view.
: DROP MATERIALIZED VIEW "active_users";
/Users/adambowen/src/test-scenic/db/migrate/20160206232756_update_active_users_to_version_2.rb:3:in `change'
/Users/adambowen/src/test-scenic/bin/rails:9:in `require'
/Users/adambowen/src/test-scenic/bin/rails:9:in `<top (required)>'
/Users/adambowen/src/test-scenic/bin/spring:13:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
PG::WrongObjectType: ERROR:  "active_users" is not a materialized view
HINT:  Use DROP VIEW to remove a view.
/Users/adambowen/src/test-scenic/db/migrate/20160206232756_update_active_users_to_version_2.rb:3:in `change'
/Users/adambowen/src/test-scenic/bin/rails:9:in `require'
/Users/adambowen/src/test-scenic/bin/rails:9:in `<top (required)>'
/Users/adambowen/src/test-scenic/bin/spring:13:in `<top (required)>'
bin/rails:3:in `load'
bin/rails:3:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)