Testing and SQL views.

The tests want to execute “delete * from TABLENAME” and this causes problems when the table is actually a view.

Is there an easy way to deal with this issue?

Tim Uckun wrote:

The tests want to execute "delete * from TABLENAME" and this causes problems when the table is actually a view.

Is there an easy way to deal with this issue?

Name the fixtures.yml file after the actual target table, not the view.

Name the fixtures.yml file after the actual target table, not the view.

I’ll give that a shot. Thanks…

What DBMS are you using? The alternative is a “materialized view”.

What DBMS are you using? The alternative is a “materialized view”.

postgres.

Name the fixtures.yml file after the actual target table, not the view.

I’ll give that a shot. Thanks…

This didn’t work. There are no fixtures with the names of the views and the test suite still tries to run a delete from the views.

I can’t believe rails has no conception of views. Must be the mysql heritage (I know mysql has views now).

I use postgres, mostly. You want to implement rules. From the create view docs:
“Currently, views are read only: the system will not allow an insert,
update, or delete on a view. You can get the effect of an updatable
view by creating rules that rewrite inserts, etc. on the view into
appropriate actions on other tables. For more information see CREATE RULE.”

You are expecting Rails to take care of something that it should not be concerned/aware of. Create the “CRUD” rules on your postgres views, and as far as rails is concerned, they are just tables, or even better, “a thing where active record persists data”…

Have a look at

http://github.com/aeden/rails_sql_views/tree/master

and my heavily hacked version

http://github.com/mschuerig/rails_sql_views/tree/master

My version works with Rails 2.3.2, loads only the adapters that are
really needed, and adds ActiveRecord::View as an abstract superclass for
views that are mostly based on another model. AR::View can clone
association definitions from the model to the view, although I haven't
implemented all kinds yet. The existing functionality works on
PostgreSQL, regarding the others, I'm not sure right now.

Michael

Have a look at

http://github.com/aeden/rails_sql_views/tree/master

and my heavily hacked version

I looked at this. It didn’t work in my setup.

http://github.com/mschuerig/rails_sql_views/tree/master

My version works with Rails 2.3.2, loads only the adapters that are

really needed, and adds ActiveRecord::View as an abstract superclass for

views that are mostly based on another model. AR::View can clone

association definitions from the model to the view, although I haven’t

implemented all kinds yet. The existing functionality works on

PostgreSQL, regarding the others, I’m not sure right now.

I’ll take a look at this next.

http://github.com/aeden/rails_sql_views/tree/master

and my heavily hacked version

http://github.com/mschuerig/rails_sql_views/tree/master

My version works with Rails 2.3.2, loads only the adapters that are

really needed, and adds ActiveRecord::View as an abstract superclass for

views that are mostly based on another model. AR::View can clone

association definitions from the model to the view, although I haven’t

implemented all kinds yet. The existing functionality works on

PostgreSQL, regarding the others, I’m not sure right now.

Michael I have downloaded your code and it does work a lot better.

Although it fixed the original problem now it presents another problem Either the rails framework or the postgres adapter attempts to change the triggers in the “table” using ALTER TABLE which causes an error.

Do I have to make my models which derive from view inherit from AR:View?

> http://github.com/aeden/rails_sql_views/tree/master
>
> and my heavily hacked version
>
> http://github.com/mschuerig/rails_sql_views/tree/master
>
> My version works with Rails 2.3.2, loads only the adapters that are
> really needed, and adds ActiveRecord::View as an abstract
> superclass for views that are mostly based on another model.
> AR::View can clone association definitions from the model to the
> view, although I haven't implemented all kinds yet. The existing
> functionality works on PostgreSQL, regarding the others, I'm not
> sure right now.

Michael I have downloaded your code and it does work a lot better.

Although it fixed the original problem now it presents another
problem Either the rails framework or the postgres adapter attempts
to change the triggers in the "table" using ALTER TABLE which causes
an error.

The only code I can see that affects triggers is in the original
PostgreSQLAdapter#disable_referential_integrity method. It disables
triggers during a block and re-enables them afterwards. As far as I
remember, this is used to load fixtures while temporarily suspending the
triggers. As fixture files are loaded one by one without regard for
foreign keys, the temporarily violated constraints would cause an
exception. I don't think the behavior you're seeing is related to views

Do I have to make my models which derive from view inherit from
AR:View?

No, that's just for convenience if you like to re-use associations from
other models.

Michael

The only code I can see that affects triggers is in the original

PostgreSQLAdapter#disable_referential_integrity method. It disables

triggers during a block and re-enables them afterwards. As far as I

remember, this is used to load fixtures while temporarily suspending the

triggers. As fixture files are loaded one by one without regard for

foreign keys, the temporarily violated constraints would cause an

exception. I don’t think the behavior you’re seeing is related to views

Yes that’s where the problem occurs. That code iterates over the tables collection. The problem is that you have alias_method_chain on tables which returns the views as well as the tables and of course the code fails when it hits a view.

I commented out the alias method chain line and it works. I hope I didn’t break something else.

Do I have to make my models which derive from view inherit from

AR:View? is

No, that’s just for convenience if you like to re-use associations from

other models.

Ah. Ok thanks.

Indeed, that had escaped my tests. I've pushed an updated version
version to github.

Michael