Rails doesn't recreate mysql views in the test database, even when config.active_record.schema_format = :sql

Question

We have some mysql Views in our development and test databases that were created via an execute(sql) statement in a migration. Rails' default schema.rb creates these views as tables. When config.active_record.schema_format is set to :sql, these views are not created at all.

Is there a setting to ensure these views are re-created in the test db?

If not, can anyone suggest a workaround?

NB, the show create table for this view is something like:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `sales_reports` AS select ...

and the view is included in show tables

After investigation, it looks like activerecord does this intentionally.

active_record/connection_adapters/mysql_adapter.rb

Is there a good reason for this?

I know it doesnt answer your question but i found [sql views](https://github.com/aeden/rails_sql_views) gem to be a good alternative... — alexcepoi, Mar 03 '11 at 15:15

score 8 · Answer 1 · edited May 23 '17 at 12:09

Be sure to read this section of the guide.

Ensure in the application block in config/application.rb:

config.active_record.schema_format = :sql

Then you can use this Rake task to dump the schema, though it should be dumped/updated whenever you do a migrate/etc. because of that setting above:

rake db:structure:dump

Your dumped structure should be in db/structure.sql. It should look like a dump schema file from your DB, i.e. not including data, with the exception of migration data that will be put at the end (at least for postgres).

When using config.active_record.schema_format = :sql your db/schema.rb is not updated by default whenever you do a migration, because db/schema.rb is not meant to be a full schema SQL dump. However, some tools like IntelliJ Rubymine and IDea with the Ruby plugin like that file to be there, so in your Rakefile add these (as mentioned here):

Rake::Task["db:migrate"].enhance do
  if ActiveRecord::Base.schema_format == :sql
    Rake::Task["db:schema:dump"].invoke
  end
end

Rake::Task["db:rollback"].enhance do
  if ActiveRecord::Base.schema_format == :sql
    Rake::Task["db:schema:dump"].invoke
  end
end

When the test DB is recreated by Rails, it will use db/structure.sql as a base if using config.active_record.schema_format = :sql. If you rollback or make changes to the DB externally and redump using the command above or doing migrate, etc., it will also update db/structure.sql (and db/schema.rb with those tasks above, though db/schema.rb doesn't fully encompass the info from the schema dump).

Making the above changes still doesn't seem to cause the MySQL views to be written to the `structure.sql`; so they remain unavailable to the testing database (observed with Rails 3.2.22, ruby 2.3.4p301) — Everett, Sep 20 '18 at 17:43
This solution worked for me: http://weavingcode.blogspot.com/2011/12/mysql-views-and-rails-easy-way.html — Everett, Sep 20 '18 at 17:50

Rails doesn't recreate mysql views in the test database, even when config.active_record.schema_format = :sql

1 Answers1