Rightly or wrongly I get the impression that most RoR engineers ignore database views and would rather they didn’t exist. I’m neutral about db views but there are times when they have their place. We use them in Zico our online survey app for some of the survey stats and gamification we provide.

Last week I was baking some of this is to the app and realised whilst it is simple enough to map a model on to a db view by just treating the view like a table, it was a different matter when it came to testing. Important note: you can only “treat db views like a table” if it is a single-table view or your model is read-only].

The problem with testing views is that when you create your test database, Rails creates the view as a table in the test database. In most circumstances this means your test will fail as your view-now-table won’t have the data in it (the data is selected with a db view as opposed to inserted ). This fails both for test dbs created through migration and structure cloning. Annoying!

I thought of several ways around it (confession: including removing the tests) but looking harder saw that it was possible to copy the view to test db as a view with a bit of monkey-patching. This example only covers MySQL but the adapter classes are there for all the other dbs and once you know how to pull metadata from your db it’s pretty easy.

First … how Rails copies your database structure to test

There are two ways in which Rails copies the db structure over to test. It either runs the migrations on the test database (ignoring the execute sql ones that create db views — #fail) or it copies the structure from SQL (and in this case it turns views in to tables — #fail)

We are going to choose the second way i.e. to clone the database and change the cloning (for MySQL dbs) so that it includes views.

First off we need to tell Rails to clone the database from SQL and we do this by adding this config.active_record.schema_format = :sql to our application.rb file (or similar in Rails2)

You then use the rake db:test:clone_structure command to copy your database structure (i.e. not data these will be via your fixtures) from dev to test.

Next … how Rails maps models

As you might imagine, Rails gets the info need to based models on tables from the database metadata – i.e. info about the tables and columns amongst other things. Every RDBMS has this and you can read about MySQL metadata in the manual.

By default Rails doesn’t pull metadata info about db views (for good reason as unless a single-table view you won’t be able to create or update). However, if your only have the need for read-only models for your views then you can get Rails to do the hard-work for you rather than write the model by hand.

The metadata queries can be found in a set of classes that inherit from ConnectionAdapters::AbstractAdapter. The Rails3 adapter is called Mysql2Adapter and is in the mysql2 gem.

This class defines a method called structure_dump which queries the metadata tables and pulls back all matching tables. We need to re-open that class and override the method to pull back both tables and views.

I added the following to a new file that I put in the config/initializers folder (put in your environment in Rails 2)

support_db_views.rb

module ConnectionAdapters

class Mysql2Adapter < AbstractAdapter
# SCHEMA STATEMENTS ========
def structure_dump
if supports_views?
sql = “SHOW FULL TABLES WHERE Table_type IN (‘BASE TABLE’,’VIEW’)”
else
sql = “SHOW TABLES”
end
select_all(sql).inject("") do |structure, table|
view = table‘Table_type’ == “VIEW”
table.delete(’Table_type’)
if view
structure += select_one(“SHOW CREATE VIEW #{quote_table_name(table.to_a.first.last)}”)“Create View” + “;\n\n”
else
structure += select_one(“SHOW CREATE TABLE #{quote_table_name(table.to_a.first.last)}”)“Create Table” + “;\n\n”
end
end
end
end
end

Basically the method used to read through the metadata and pulled the structure definition (i.e. the CREATE statements) for tables and then collected these for running in to test. We have just changed the metadata query to include views as well as tables — sql = “SHOW FULL TABLES WHERE Table_type IN (‘BASE TABLE’,’VIEW’)” — and then when we loop through the objects, if we find a view we pull its create statement. Simple!

As we’re re-opening our abstract class we need to redefine the connection method that it provides. I won’t put the code in the post but you can download a copy here

Finally … was it worth it?!

As a rule I tend to avoid db views for new Rails apps in that it tends to make life a bit more difficult. There are good reasons to use db views though and if you want (or have) to do use them and you want to write full tests (which of course you do) then this isn’t a bad way to support it.