Rails and SQL Views – Part 2 – Migrations

March 2, 2013 Dirk Kelly

Last week I introduced that my pair and I have started using SQL Views more often in our project. This week I was going to discuss finer points on implementation, thankfully I found Josh Davis’ has_one view post at HashRocket.

The rocket example I discussed covered an activity log of all the rocket’s states RocketActivity, with a custom view being used to return the most recent of these RocketCurrentActivity. One of a few solutions to this in SQL is a sub select on the same activities table.

CREATE VIEW rocket_current_activities AS
  SELECT
    rocket_activities.status     AS status

A few weeks down the way we’re going to have some change in requirements, this is where we would drop the existing view a recreate it with the new SQL.

Getting started with migrations is helped immensely by lomba/schema_plus a gem which alongside index and foreign key improvements, also gives you a method for creating SQL views, which it then stores in you schema.rb.

class RocketActivityViewFixes < ActiveRecord::Migration
  def change
    create_view :rocket_activities, "SELECT * FROM...", force: true
  end

Unfortunately views can get long and in-depth, can change multiple times before hitting any one environment and contain important information about available columns and data types. We needed more than a one line truncation of what a view is at any point in time.

To solve this we started to store copies of our sql and store them under db/views/name_of_view/timestamp_name_of_view.sql, and call them out something like this.

class RocketActivityViewFixes < ActiveRecord::Migration
  def change
    create_view :rocket_activities, view_sql('20130215155853', 'rocket_activities')
  end

The view_sql method is rather simple, and just an example of how you could structure your query files.

ActiveRecord::Migration.class_eval do
  def view_sql(timestamp,view)
    File.read(Rails.root.join("db/views/#{view}/#{timestamp}_#{view}.sql"))
  end

As simple as this is, if you can start to drop your data joining into the dbms without impacting on support for teams and multiple environments, you’re going to feel a lot less pain making the move to SQL views.

If this doesn’t convince you that views are the bees knees, maybe the next time I get around to posting about how we use views to power our search indexes will be exciting to you. Once again, stay tuned.

About the Author

Biography

More Content by Dirk Kelly
Previous
Tracker Ecosystem: Tracker Tracker – cross project visibility and panel customization
Tracker Ecosystem: Tracker Tracker – cross project visibility and panel customization

Tracker Tracker is an open source web app that allows you to see and work with stories from across multiple...

Next
BDD starts with a conversation
BDD starts with a conversation

Let’s talk about cargo culting. When I started doing BDD, I cargo-culted it. I basically had no idea what I...

Enter curious. Exit smarter.

Learn More