Close
Glad You're Ready. Let's Get Started!

Let us know how we can contact you.

Thank you!

We'll respond shortly.

LABS
Rails and SQL Views – Part 2 – Migrations

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.

Comments
  1. Dan Moore says:

    Thanks for the walkthrough and examples in this and the previous post. It’s a running contender for dealing with time-sensitive data in our application and query optimization.

  2. Matt Huggins says:

    Thanks for sharing! I was familiar with the schema_plus gem a tiny bit, but didn’t know it provides create/drop for views. Add your bonus method for reading the SQL from a separate file, and this is a great solution! :)

  3. Travis Jeppson says:

    Where did you add your view_sql statement re-opening the ActiveRecord class? I’m new to rails and I’m just looking for a good location to add that definition so that it will be called for any migration w/out me having to add a require statement.

  4. Chad says:

    Just came across this as I started to see the need for using View to speed up performance, especially. Next I will be dealing with Materialized View, but that’s a discussion for another time.

    Three questions regarding your post:

    1) This was posted March 3, 2013… is there an update on how you are dealing with view as of today? I’d love to learn more about that, especially things may have changed over time.

    2) How do you create timestamped views in a most convenient way (like rails g migration … ) ?

    3) Where do you store the Migration.class_env / view_sql file?

    Thanks a bunch!

Post a Comment

Your Information (Name required. Email address will not be displayed with comment.)

* Copy This Password *

* Type Or Paste Password Here *