pmacs/scenic_oracle_enhanced_adapter

OracleEnhanced adapter for Scenic gem.

ScenicOracleEnhancedAdapter

This gem provides an adapter for the Scenic gem for use with the Oracle Enhanced Adapter.

Installation

Add this line to your application's Gemfile:

gem 'scenic_oracle_enhanced_adapter'

And then execute:

$ bundle

Then add a Scenic initializer (or modify your existing initializer) to make Scenic use this adapter:

# config/initializers/scenic.rb

Scenic.configure do |config|
  config.database = Scenic::Adapters::OracleEnhanced.new
end

Switching from rails_sql_views to Scenic

In Oracle 12, replacing a view has issues when the view refers to objects outside its own schema; use update_view, which will drop and create. The replace_view method can still be used for views referring to objects in their own schema.

1. Run bundle exec rails g scenic:view <full_view_name> (if you use a prefix for the view, include it).
2. Move the view's SQL definition from the original migration into the newly-created file in db/views.
3. In the original migration, delete any drop_view call.
4. Copy the create_view or update_view line from the scenic migration and substitute it for the old create_view method and block.
5. Delete the scenic migration file
6. For the down method, copy the update_view line into the down (replacing any drop_ and create_view methods) and make the version: argument to the same number as the revert_to_version: argument (e.g. update_view :complex_data_view, version: 2, revert_to_version: 1 becomes update_view :complex_data_view, version: 1, revert_to_version: 1)

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/PMACS/scenic_oracle_enhanced_adapter.

scenic_oracle_enhanced_adapter's Issues

Problem with the schema dumper

After I successfully run up one view-creating migration with this gem, a subsequent run of bundle exec rake db:migrate fails. The trace is below.

[barendt@webdev4 fu]$ bundle exec rake db:migrate
WARNING: You are sending real email in a non-production environment. You should specify the :test delivery_method for action_mailer or register an action_mailer interceptor like PmacsAppResources::MailInterceptor.
rake aborted!
ActiveRecord::ConnectionAdapters::OracleEnhancedConnectionException: "DESC ." failed; does it exist?
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.8.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:217:in `rescue in describe'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.8.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:212:in `describe'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.8.2/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:616:in `indexes'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.8.2/lib/active_record/connection_adapters/oracle_enhanced/schema_dumper.rb:56:in `indexes'
/home/barendt/.gem/ruby/2.3.0/gems/scenic-1.4.0/lib/scenic/schema_dumper.rb:18:in `block in views'
/home/barendt/.gem/ruby/2.3.0/gems/scenic-1.4.0/lib/scenic/schema_dumper.rb:16:in `each'
/home/barendt/.gem/ruby/2.3.0/gems/scenic-1.4.0/lib/scenic/schema_dumper.rb:16:in `views'
/home/barendt/.gem/ruby/2.3.0/gems/scenic-1.4.0/lib/scenic/schema_dumper.rb:8:in `tables'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/schema_dumper.rb:37:in `dump'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/schema_dumper.rb:21:in `dump'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:241:in `block (4 levels) in <top (required)>'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:240:in `open'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:240:in `block (3 levels) in <top (required)>'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:66:in `block (2 levels) in <top (required)>'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:59:in `block (2 levels) in <top (required)>'
/home/barendt/.gem/ruby/2.3.0/gems/rake-12.1.0/exe/rake:27:in `<top (required)>'
/usr/local/rbenv/versions/2.4.1/bin/bundle:22:in `load'
/usr/local/rbenv/versions/2.4.1/bin/bundle:22:in `<main>'
OCIError: ORA-04043: object "." does not exist
metadata.c:175:in oci8lib_240.so
/home/barendt/.gem/ruby/2.3.0/gems/ruby-oci8-2.2.3/lib/oci8/metadata.rb:2053:in `block in describe_table'
/home/barendt/.gem/ruby/2.3.0/gems/ruby-oci8-2.2.3/lib/oci8/metadata.rb:2052:in `times'
/home/barendt/.gem/ruby/2.3.0/gems/ruby-oci8-2.2.3/lib/oci8/metadata.rb:2052:in `describe_table'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.8.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:355:in `describe'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.8.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:214:in `describe'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.8.2/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:616:in `indexes'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-oracle_enhanced-adapter-1.8.2/lib/active_record/connection_adapters/oracle_enhanced/schema_dumper.rb:56:in `indexes'
/home/barendt/.gem/ruby/2.3.0/gems/scenic-1.4.0/lib/scenic/schema_dumper.rb:18:in `block in views'
/home/barendt/.gem/ruby/2.3.0/gems/scenic-1.4.0/lib/scenic/schema_dumper.rb:16:in `each'
/home/barendt/.gem/ruby/2.3.0/gems/scenic-1.4.0/lib/scenic/schema_dumper.rb:16:in `views'
/home/barendt/.gem/ruby/2.3.0/gems/scenic-1.4.0/lib/scenic/schema_dumper.rb:8:in `tables'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/schema_dumper.rb:37:in `dump'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/schema_dumper.rb:21:in `dump'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:241:in `block (4 levels) in <top (required)>'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:240:in `open'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:240:in `block (3 levels) in <top (required)>'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:66:in `block (2 levels) in <top (required)>'
/home/barendt/.gem/ruby/2.3.0/gems/activerecord-5.1.4/lib/active_record/railties/databases.rake:59:in `block (2 levels) in <top (required)>'
/home/barendt/.gem/ruby/2.3.0/gems/rake-12.1.0/exe/rake:27:in `<top (required)>'
/usr/local/rbenv/versions/2.4.1/bin/bundle:22:in `load'
/usr/local/rbenv/versions/2.4.1/bin/bundle:22:in `<main>'
Tasks: TOP => db:schema:dump
(See full trace by running task with --trace)

drop_view Fails With Parameters

Problem

When rolling back a migration that uses change instead of up and down methods, it is common to see a line like this:

create_view :my_schema, sql_definition: view_sql

The sql_definition parameter, which is needed for create_view is not needed for drop_view, when this migration is being reversed. At present, this parameter is still being passes, throwing an error.

Solution

Ignore the sql_definition parameter if it is passed to drop_view.

Create reference document comparing old/new migration methods

Many PMACS applications will likely move from using the pmacs-rails_sql_views gem to (scenic + this gem). Can we create a document that compares the old migration methods to the new ones exposed by this gem?

Specifically, developers may wish to upgrade their old migration files when upgrading to this gem. Can we give examples of old pmacs-rails_sql_views migrations and new scenic migrations which would produce identical views?

Two corollaries:

1. Should such a document live in the gem or should we keep that on the PMACS wiki?
2. Should we link to this document in our 5.1 -> 5.2 Rails upgrade notes.

Fix schema dumping when the view has spaces in it

When the name of a view has spaces in it, the oracle_identifier method in Scenic::Adapters::OracleEnhanced::Views will quote the view's name such that the indexes method of the Oracle Enhanced Adapter won't work properly.

Solving this will require looking at all of the places where the name of the new Scenic::View object is used to determine if, and how, it should be quoted.

Update the README with unsupported feature

I looked through the adapter really quickly and noticed there's definitely some features not supported (index reapplication, for example). I think those would be worth calling out in your README.

See comment here:

scenic-views/scenic#264 (comment)

Correct refresh_materialized_view

I think the refresh_materialized_view method in this adapter is wrong.

UserCourse.refresh

I think the correct way to refresh a MV in Oracle is something like so:

BEGIN
DBMS_SNAPSHOT.REFRESH('MVIEW_NAME');
END;

no_data option parameter is required in create_materialized_view

Hello esteemed colleagues,

The scenic gem has

Scenic.database.create_materialized_view(
  name,
  sql_definition,
  no_data: no_data(materialized),
)

but we only have two parameters:

scenic_oracle_enhanced_adapter/lib/scenic/adapters/oracle_enhanced.rb

Line 33 in ab43cce

def create_materialized_view(name, sql_definition)

"Add no_data option to materialized views" is the commit that added the new parameter: scenic-views/scenic@a21c476

So when we try to create a new materialized view, the following happens:

-- create_view(:wp_mv_dw_proposals, {:materialized=>true})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

wrong number of arguments (given 3, expected 2)
/home/bthoman/.gem/ruby/2.6.0/gems/scenic_oracle_enhanced_adapter-0.0.2/lib/scenic/adapters/oracle_enhanced.rb:33:in `create_materialized_view'
/home/bthoman/.gem/ruby/2.6.0/gems/scenic-1.5.1/lib/scenic/statements.rb:40:in `create_view'

Maybe we can look at this on infrastructure hackathon day?

replace_view signature does not match that of scenic gem

https://github.com/scenic-views/scenic/blob/master/lib/scenic/statements.rb#L135

No sql_definition param is accepted.

Create a refresh_schedule Option For Materialized Views

When using pmacs-rails_sql_views, we were able to pass a refresh_schedule option to the create_materialized_view method. This was a convenient way to tell your MVs, at creation time, when to refresh themselves. This is no long available with scenic, but it would be a nice option to build in.

Considerations

• Perhaps this is too specific and we should instead add a general options parameter that we pass raw SQL into.
• It could be a bad idea to add a MV-only option like refresh_schedule to a general view method like create_view.