Testing and SQL views


#1

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?


#2

Tim U. 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.


#3

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

I’ll give that a shot. Thanks…


#4

On Sat, Apr 18, 2009 at 12:43 AM, Harold A. Giménez Ch. <
removed_email_address@domain.invalid> wrote:

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).


#5

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


#6

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
http://www.postgresql.org/docs/8.2/static/sql-createrule.html
.”

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”…


#7

On Friday 17 April 2009, Tim U. wrote:

On Sat, Apr 18, 2009 at 12:43 AM, Harold A. Giménez Ch. <

removed_email_address@domain.invalid> wrote:

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

postgres.

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


Michael S.
mailto:removed_email_address@domain.invalid
http://www.schuerig.de/michael/


#8

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?


#9

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.

I’ll take a look at this next.


#10

On Monday 20 April 2009, Tim U. wrote:

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


Michael S.
mailto:removed_email_address@domain.invalid
http://www.schuerig.de/michael/


#11

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.


#12

On Monday 20 April 2009, Tim U. wrote:

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.

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

Michael

Michael S.
mailto:removed_email_address@domain.invalid
http://www.schuerig.de/michael/