Tests with mysql 5.0 views and triggers

Rails
1

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

2

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
3

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. :wink:

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.