Forum: Ruby on Rails Testing and SQL views.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Tim U. (Guest)
on 2009-04-17 05:01
(Received via mailing list)
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?
Phlip (Guest)
on 2009-04-17 08:15
(Received via mailing list)
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.
Tim U. (Guest)
on 2009-04-17 16:43
(Received via mailing list)
>
>
> Name the fixtures.yml file after the actual target table, not the view.
>
>
I'll give that a shot. Thanks...
Harold A. Giménez Ch. (Guest)
on 2009-04-17 16:45
(Received via mailing list)
What DBMS are you using? The alternative is a "materialized view".
Tim U. (Guest)
on 2009-04-17 16:58
(Received via mailing list)
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).
Harold A. Giménez Ch. (Guest)
on 2009-04-17 17:22
(Received via mailing list)
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-crea...
."

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"...
Michael S. (Guest)
on 2009-04-17 17:53
(Received via mailing list)
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/
Tim U. (Guest)
on 2009-04-18 02:24
(Received via mailing list)
>
> 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.
Tim U. (Guest)
on 2009-04-20 05:41
(Received via mailing list)
> 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?
Michael S. (Guest)
on 2009-04-20 12:51
(Received via mailing list)
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/
Tim U. (Guest)
on 2009-04-20 16:18
(Received via mailing list)
>
>
> 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.
Michael S. (Guest)
on 2009-04-20 18:02
(Received via mailing list)
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/
This topic is locked and can not be replied to.