Rake replaces my view with a table


#1

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:

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

If I run

rake

and then do the same command:

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

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

Matthew Hall
removed_email_address@domain.invalid Try not to read it as “hamtheallwet”.


#2

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.

On 3/26/07, Matthew removed_email_address@domain.invalid wrote:

presumably be a view, which is not updateable, I think the test will
Matthew Hall
removed_email_address@domain.invalid Try not to read it as “hamtheallwet”.

Matthew Hall
removed_email_address@domain.invalid Try not to read it as “hamtheallwet”.