rake replaces my view with a table

Hi rails-folks,

I have a non-UPDATE-able MySQL view which contains aggregate data for another table (AVG, STDDEV for 3 columns). This view is used as the basis for a model, "Accountingstat". The view is created in a migration which simply "execute"s the sql required to create it.

If I run:

  RAILS_ENV=test rake db:migrate

The view is created as a view, which I can verify by doing:

  > SHOW CREATE VIEW resultsviewer_test.accountingstats;     (view data omitted)

If I run

  rake

and then do the same command:

  > SHOW CREATE VIEW resultsviewer_test.accountingstats;     ERROR 1347 (HY000): 'resultsviewer_test.accountingstats' is not VIEW

  > SHOW CREATE TABLE resultsviewer_test.accountingstats;     (table with exactly the same structure as the view, but not a view)

This table is empty, so my tests fail.

One very inelegant way to address this is to load data into the _table_ with a fixture. However, since on first creation this will presumably be a view, which is not updateable, I think the test will fail if I do try to insert data with a fixture. Additionally, I'm hoping to be able to use the model unit test to test the db logic (as I'm at least 80% novice).

Any thoughts on how I can get my test to run with a view?

Matthew

Hello again rails people,

After some investigation I've had to stop working on solving this issue and instead fall back on using a fixture to load data into the "table" that rake so nicely replaces my view with.

For the benefit of the googlers, here is some of what I've learned in my investigation:

rake drops all the tables in the test database and recreates them using schema.db. schema.db is re-generated by rake prior to this action, so modifying it isn't useful.

The "correct" way to handle this would be to extend activerecord to be able to create and manage views and not just tables. See http://activewarehouse.rubyforge.org/rails_sql_views/rdoc/ for documentation on rails_sql_views, which seems to do this for MySQL and PostgreSQL. Once I have time I'll evaluate the gem and see if it will fit my needs.

A work around, for those more knowledgeable than I, would be to override some of the methods which rake calls to do its database work. Executing "rake test:units --trace" will show you to some degree what is going on with the database.

Matthew

ps. I'm not sure what keywords to include to make this easy to find - "activerecord" "sql" and "view" aren't that useful, but perhaps together: "activerecord sql view". Having a hard-to-search for problem makes it much more difficult to find a solution.