Rails - Aug 2006 - tests with mysql 5.0 views and triggers

I''ve realized with a shock that when I run tests on my app the db 
cloning code in test setup does NOT copy views or triggers into the test 
db. Since views (and to a lesser extent) triggers are central to 
portions of my app this is a problem. I can work around it by doing the 
copy of views and triggers manually. This works reasonably easily 
because the cloning code does not seem to remove views and triggers that 
are already in the test db. As long as I am not changing these often I 
can accept this form of maintenance. However, it would be much better to 
have the cloning code understand views and triggers. I''ve seen efforts 
to use migrations to keep the test db in synch with the development db. 
However, the current use of migrations to handle changes to the 
development db and then cloning at test time seems to make sense.

It looks like that could be accomplished without too much difficulty by 
changes in structure_dump in mysql_adapter.rb and to supporting 
routines. I''m tempted to try it but this clearly pushes the limits of
my
abilities and knowlege. It would take some time.

Is there anybody already doing this? Are similar efforts needed for 
postgres et.al.?

mitch

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

Mitch, how are you using database views in your application?  Are you
using through Active Record or outside of Active Record?  If inside
Active Record, what did you do to adapt rails to use your view?

Thx,
- dan m


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

unknown wrote:
> Mitch, how are you using database views in your application?  Are you
> using through Active Record or outside of Active Record?  If inside
> Active Record, what did you do to adapt rails to use your view?
> 
> Thx,
> - dan m
Dan, I cheat. ;)

I have altered mysql_adapter.rb from:
      # SCHEMA STATEMENTS =======================================
      def structure_dump #:nodoc:
        if supports_views?
          sql = "SHOW FULL TABLES WHERE Table_type = ''BASE
TABLE''"
        else
          sql = "SHOW TABLES"
        end
to:
      # SCHEMA STATEMENTS =======================================
      def structure_dump #:nodoc:
        if supports_views?
          sql = "SHOW TABLES"
        else
          sql = "SHOW TABLES"
        end

The effect is to make rails think a mysql view is a table. This is a bit 
dangerous as the views I am interested in are not updatable but Rails 
would assume they are. However, as I am using the views for read only 
purposes, I can avoid problems. The views allow me to do such things as 
easily list the results of a search for studies that meet certain 
criteris where the studies belong to folders that belong to patients and 
I wish to sort the result set on patient name and folder location 
showing the human readable patient and folder location names and not 
just the id fields. The view for this:
            CREATE VIEW exp_studies
            AS
              SELECT s.id, s.txtid,
                     p.lname, p.fname, p.mi,
                     o.sname AS office, f.txtid AS folder,
                     c.name AS category, s.studied_on
              FROM studies s, folders f, patients p, locations o, 
categories c
              WHERE s.folder_id = f.id
                AND f.patient_id = p.id
                AND f.office_id = o.id
                AND s.category_id = c.id
              ORDER BY p.lname, p.fname, p.mi, o.sname, f.txtid;
I just create a new model and controller for this ''table''. The
code for
displaying the result is then dead simple. I couldn''t find any way to
do
this cleanly wiithout the use of a view. The logic of my app is made 
easy to understand and to maintain. I do have to ''fix''
ActiveRecord
every time it is updated, however! ;(

I''d like to see Rails officially modified to support Views for this 
purpose. However, posts from DHH in various locations make me think I 
shouldn''t hold my breath. I''ll just have to documant the heck
out of my
apps that use Views and/or the patched ActiveRecord.

Mitch

BTW. I don''t find many places where I need to do this. If, however, I 
use even one view the patch to ActiveRecord affects all Rails apps on 
the box.

-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---